主从优点

避免数据库单点故障：主服务器实时、异步复制数据到从服务器，当主数据库宕机时，可在从数据库中选择一个升级为主服务器，从而防止数据库单点故障。
提高査询效率：根据系统数据库访问特点，可以使用主数据库进行数据的插入、删除及更新等写操作，而从数据库则专门用来进行数据査询操作，从而将査询操作分担到不同的从服务器以提高数据库访问效率。

docker 安装

可以参考我之前的一篇文章：应用容器引擎Docker(一)：Docker安装 - 掘金 (juejin.cn)

前期规划

地址	主从	名称	版本	server_id
127.0.0.1:3311	主	master	mysql:8.0.28	1
127.0.0.1:3312	从	slave1	mysql:8.0.28	2
127.0.0.1:3313	从	slave2	mysql:8.0.28	3

主从配置需要注意的点

(1)主从服务器操作系统版本和位数一致；

(2) Master 和 Slave 数据库的版本要一致；

(3) Master 和 Slave 数据库中的数据要一致；

(4) Master 开启二进制日志，Master 和 Slave 的 server_id 在局域网内必须唯一；

创建容器数据卷

创建docker容器挂载的数据卷

master

mkdir -p /mydata/mysql-cluster/master/conf
mkdir -p /mydata/mysql-cluster/master/data
mkdir -p /mydata/mysql-cluster/master/logs

slave1

mkdir -p /mydata/mysql-cluster/slave1/conf
mkdir -p /mydata/mysql-cluster/slave1/data
mkdir -p /mydata/mysql-cluster/slave1/logs

slave2

mkdir -p /mydata/mysql-cluster/slave2/conf
mkdir -p /mydata/mysql-cluster/slave2/data
mkdir -p /mydata/mysql-cluster/slave2/logs

注：创建数据存放目录：data，配置存放目录：conf。日志存放目录：logs

容器数据卷是什么？

容器的持久化
容器间继承 + 共享数据卷就是目录或文件，存在于一个或多个容器中，由docker挂载到容器中，但不属于联合文件系统，因此能够绕过Union File System提供一些用于持续存储或共享数据的特性。

卷的设计的目的就是数据的持久化，完全独立于容器的生存周期，因此Docker不会在容器删除时删除其挂载的数据卷。

特点：

1：数据卷可以在容器之间共享或重用数据
2：卷中的更改可以直接生效
3：数据卷中的更改不会包含在镜像的更新中
4：数据卷的生命令周期默认会一直存在，即使容器被删除。

配置my.cnf文件

master

创建配置文件：vi /mydata/mysql-cluster/master/conf/my.cnf

复制如下内容：

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
# 设置所有的默认字符集
character-set-server=utf8mb4
# 设置字符序
collation-server=utf8mb4_unicode_ci
#此处是忽略客户端的字符集，使用服务器的设置，即是客户端连接请求字符集无效，都用服务端定义的
skip-character-set-client-handshake
# 禁止域名解析
skip-name-resolve
# 导入导出不做限制
secure_file_priv =

# 服务器唯一ID，默认是1
server_id=1
# 启用二进制日志
log-bin=mysql-bin
#指定relay-log日志名称 查看：show variables like "%relay%"
relay-log = relay-log
# 主库可读可写
read-only=0
# 最大连接数
# max_connections=500
# 设置默认时区
default-time_zone='+8:00'
# 0:区分大小写
# 1:不区分大小写
lower_case_table_names=1
## 主从复制的格式（mixed,statement,row，默认格式是 statement）
binlog_format=mixed

# 要同步的数据库
binlog-do-db=demo_ds_0
binlog-do-db=demo_ds_1
# 忽略同步数据库
binlog-ignore-db=mysql
binlog-ignore-db=sys
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema

slave1

创建配置文件：vi /mydata/mysql-cluster/slave1/conf/my.cnf

复制如下内容：

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
# 设置所有的默认字符集
character-set-server=utf8mb4
# 设置字符序
collation-server=utf8mb4_unicode_ci
#此处是忽略客户端的字符集，使用服务器的设置，即是客户端连接请求字符集无效，都用服务端定义的
skip-character-set-client-handshake
# 禁止域名解析
skip-name-resolve
# 导入导出不做限制
secure_file_priv =

# 服务器唯一ID，默认是1，从库1为2
server_id=2
# 启用二进制日志
log-bin=mysql-bin
#指定relay-log日志名称 查看：show variables like "%relay%"
relay-log = relay-log
# 从库只读
read-only=1
# 最大连接数
# max_connections=500
# 设置默认时区
default-time_zone='+8:00'
# 0:区分大小写
# 1:不区分大小写
lower_case_table_names=1
## 主从复制的格式（mixed,statement,row，默认格式是 statement）
binlog_format=mixed

# 要同步的数据库
binlog-do-db=demo_ds_0
binlog-do-db=demo_ds_1
# 忽略同步数据库
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

slave2

创建配置文件：vi /mydata/mysql-cluster/slave2/conf/my.cnf

复制如下内容：

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
# 设置所有的默认字符集
character-set-server=utf8mb4
# 设置字符序
collation-server=utf8mb4_unicode_ci
#此处是忽略客户端的字符集，使用服务器的设置，即是客户端连接请求字符集无效，都用服务端定义的
skip-character-set-client-handshake
# 禁止域名解析
skip-name-resolve
# 导入导出不做限制
secure_file_priv =

# 服务器唯一ID，默认是1，从库2为3
server_id=3
# 启用二进制日志
log-bin=mysql-bin
#指定relay-log日志名称 查看：show variables like "%relay%"
relay-log = relay-log
# 从库只读
read-only=1
# 最大连接数
# max_connections=500
# 设置默认时区
default-time_zone='+8:00'
# 0:区分大小写
# 1:不区分大小写
lower_case_table_names=1
## 主从复制的格式（mixed,statement,row，默认格式是 statement）
binlog_format=mixed

# 同步的数据库
binlog-do-db=demo_ds_0
binlog-do-db=demo_ds_1
# 忽略同步数据库
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

参数解析

写在主库配置文件里参数（对所有从库都有效）
- binlog_do_db=库名列表 //允许同步的库
- binlog_ignore_db=库名列表 //不允许同步的库
写在从库配置文件里参数（只针对从库本机有效）
- replicate_do_db=库名列表 //指定只同步的库
- replicate_ignore_db=库名列表 //指定不同步的库
三个控制mysql client的字符集
- character_set_client
- character_set_connection
- character_set_results
设置数据库的默认字符集
- character_set_database
设置以上所有的默认字符集
- character_set_server
utf8mb4是MySQL5.5.3版本之后支持的字符集，如果你需要使用这个字符集，前提条件是你的MySQL版本必须 >= 5.5.3
查看mysql字符集：show variables like '%char%'
init_connect
- 1、配置init_connect参数时必须使用super用户来配置，普通用户是没有相应的权限的；
- 2、init_connect参数中的隐式执行的SQL只针对普通用户对super用户来说无效，所以在平时在使用MySQL的过程中就应该区分管理好MySQL的账户权限。
关于skip_name_resolve参数的总结 - iVictor - 博客园 (cnblogs.com)
从mysql 8.0开始，mysql默认的CHARSET已经不再是Latin1了，改为了utf8mb4（参考链接），并且默认的COLLATE也改为了utf8mb4_0900_ai_ci。utf8mb4_0900_ai_ci大体上就是unicode的进一步细分，0900指代unicode比较算法的编号（ Unicode Collation Algorithm version），ai表示accent insensitive（发音无关），例如e, è, é, ê 和 ë是一视同仁的。相关参考链接1，相关参考链接2

创建mysql容器

mysql镜像仓库地址 | Docker Hub

master

docker run -p 3311:3306 --name my-mysql-master \
-v /mydata/mysql-cluster/master/conf:/etc/mysql \
-v /mydata/mysql-cluster/master/logs:/var/log/mysql \
-v /mydata/mysql-cluster/master/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:8.0.28

查看后面10行的运行日志: docker logs -f -t --tail 10 my-mysql-master

slave1

docker run -p 3312:3306 --name my-mysql-slave1 \
-v /mydata/mysql-cluster/slave1/conf:/etc/mysql \
-v /mydata/mysql-cluster/slave1/logs:/var/log/mysql \
-v /mydata/mysql-cluster/slave1/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=root  \
-d mysql:8.0.28

slave2

docker run -p 3313:3306 --name my-mysql-slave2 \
-v /mydata/mysql-cluster/slave2/conf:/etc/mysql \
-v /mydata/mysql-cluster/slave2/logs:/var/log/mysql \
-v /mydata/mysql-cluster/slave2/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=root  \
-d mysql:8.0.28

参数解析

-p 3311:3306：容器的3306映射外部服务器的3311
--name my-mysql-master ：该容器名为：my-mysql-master
–restart=always：当Docker重启时，容器会自动跟着启动。
–privileged=true：容器内的root拥有真正root权限，否则容器内root只是外部普通用户权限
-v /mydata/mysql-cluster/slave2/logs:/var/log/mysql ：映射日志文件
-v /mydata/mysql-cluster/slave2/data:/var/lib/mysql ：映射数据目录
-v /mydata/mysql-cluster/slave2/conf:/etc/mysql ：映射配置文件
-e MYSQL_ROOT_PASSWORD=root ：映射mysql root用户的密码
-d mysql:8.0.28 以后台方式启动指定版本

远程访问配置

master和slave2和slave2下面操作一样的

进入容器 docker exec -it my-mysql-master /bin/bash
mysql 登录 mysql -u root -p
创建用户 create user 'ljw'@'%' identified by 'root';
给予远程用户所有表所有权限 grant all privileges on \*.\* to 'ljw'@'%' with grant option;

with grant option表示它具有grant权限。

查看是否有远程登录权限 select user,host,Grant_priv from user;
更改加密规则(可选操作) ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
刷新权限 flush privileges;

设置同步

创建测试语句

在master，slave1，slave2中预先创建数据库demo_ds_0，并预先创建test表，或者把主库数据的完全备份拷贝到从库执行恢复

CREATE TABLE `test` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

把主库数据的完全备份拷贝到从库（同步前要保持数据一致）

导出主库数据

[root@iZbp1bunl8t8qf63wqsy0iZ mysql-cluster]# docker exec -it my-mysql-master /bin/bash
root@3abc17925d1a:/# mysqldump -uroot -proot demo_ds_0 > /home/demo_ds_0.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
root@3abc17925d1a:/# ls /home/
demo_ds_0.sql
root@3abc17925d1a:/# exit
exit
[root@iZbp1bunl8t8qf63wqsy0iZ mysql-cluster]#  docker cp my-mysql-master:/home/demo_ds_0.sql /mydata/mysql-cluster/master
[root@iZbp1bunl8t8qf63wqsy0iZ mysql-cluster]# 
/mydata/mysql-cluster/master

导入数据到从库slave1和slave2

[root@iZbp1bunl8t8qf63wqsy0iZ master]# docker cp  /mydata/mysql-cluster/master/demo_ds_0.sql my-mysql-slave1:/home
[root@iZbp1bunl8t8qf63wqsy0iZ master]# docker exec -it my-mysql-slave1 /bin/bash
root@a53ea2fb4208:/# ls /home
demo_ds_0.sql
root@a53ea2fb4208:/# 
root@a53ea2fb4208:/# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| demo_ds_0          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> use  demo_ds_0
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>  source /home/demo_ds_0.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

查看master状态

当运行主从数据库无报错后就可以查看master状态

master中查看：SHOW MASTER STATUS

从库设置 master 的信息

slave1和slave2分别执行如下

change REPLICATION SOURCE to SOURCE_HOST='127.0.0.1',SOURCE_PORT=3311,SOURCE_USER='root',SOURCE_PASSWORD='root',SOURCE_LOG_FILE='mysql-bin.000004',SOURCE_LOG_POS=927

change REPLICATION SOURCE命令介绍

从库开始同步

在两个从数据库中分布执行：start slave;

查看同步状态

查看线程的情况：show processlist

在两个从数据库中分布执行查看情况：show slave status;

这里显示yes就是开启了同步状态，如果Slave_IO_Running,Slave_SQL_Running都不是yes，则不能同步，要关闭同步：stop slave;，并且保持master的表结构和数据与slave的结构和数据一致，重新查看SHOW MASTER STATUS信息，并重新在slave中设置 master 的信息，重新再开始同步。
Last_IO_Errno,Last_IO_Error,Last_SQL_Errno,Last_SQL_Error字段可以查看不能同步的原因
当都是yes，我们尝试在master中插入一条记录，观察slave的数据变化和slave同步状态。

INSERT INTO demo_ds_0.test
(id, name)
VALUES(3, 'hello');

binlog三种格式

mysql复制主要有三种方式：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。

STATEMENT模式（SBR）

每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

ROW模式（RBR）

不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

MIXED模式（MBR）

以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

启动容器出现问题

iptables问题

docker: Error response from daemon: driver failed programming external connectivity on endpoint my-mysql-master (b54146ce2090e21146a7c840735a3f0495dbc98858a4668230f9acf2c99a8ec8):  (iptables failed: iptables --wait -t nat -A DOCKER -p tcp -d 0/0 --dport 3311 -j DNAT --to-destination 172.17.0.6:3306 ! -i docker0: iptables: No chain/target/match by that name.
 (exit status 1)).

由于重启防火墙或docker注册的iptables链掉失报错

解决办法

既然是firewalld重启导致，而docker重启又会将其注册iptables链回来那么我们只需要将其重启docker容器即可解决该问题 systemctl restart docker

再次查询器docker的链iptables -L或询iptables -t nat -nL

docker出现Error response from daemon: driver failed programming external connectivity on endpoint解决方法_码农研究僧的博客-CSDN博客

secure_file_priv问题

Failed to access directory for --secure-file-priv. Please make sure that directory exists and is accessible by MySQL Server. Supplied value : /var/lib/mysql-files

启动mysql报错误那是因为MYSQL新特性secure_file_priv对读写文件的影响。

secure_file_priv = : 为空表示不对mysqld 的导入或导出做限制
secure_file_priv =NULL ：为NULL表示限制mysqld不允许导入或导出
secure_file_priv =/var/lib/mysql-files ：表示限制mysqld 的导入或导出只能发生在/var/lib/mysql-files/目录下（子目录也不行）

解决办法

不做限制
- windows下：修改my.ini 在[mysqld]内加入：secure_file_priv=
- linux下：修改my.cnf 在[mysqld]内加入：secure_file_priv=

lower_case_table_names问题

Different lower_case_table_names settings for server ('1') and data dictionary ('0')

Linux中mysql默认是区分大小写的：lower-case-table-names=0

解决办法

官方解析

After initialization, is is not allowed to change this setting.So "lower_case_table_names" needs to be set together with --initialize .

翻译：mysql初始化后，不允许更改此设置。因此“lower_case_table_names”需要与 --initialize 一起设置。

初始化以后生成的数据不就是挂载的/mydata/mysql-cluster/master/data目录中嘛，在这里我是做测试的环境，所有我直接rm -rf /mydata/mysql-cluster/master/data/*，(删除数据前记得备份)。然后重新启动初始化。

Public Key Retrieval is not allowed问题

远程登录包错：

Public Key Retrieval is not allowed

在用dbeaver连接 MySQL 8.0 时重提示： Public Key Retrieval is not allowed

最简单的解决方法是在连接后面添加 allowPublicKeyRetrieval=true

如果用户使用了 sha256_password 认证，密码在传输过程中必须使用 TLS 协议保护，但是如果 RSA 公钥不可用，可以使用服务器提供的公钥；可以在连接中通过 ServerRSAPublicKeyFile 指定服务器的 RSA 公钥，或者AllowPublicKeyRetrieval=True参数以允许客户端从服务器获取公钥；但是需要注意的是 AllowPublicKeyRetrieval=True可能会导致恶意的代理通过中间人攻击(MITM)获取到明文密码，所以默认是关闭的，必须显式开启

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '你的密码';

flush privileges;

然后在my.cnf中把default-authentication-plugin=mysql_native_password前的#去掉;

重启mysql服务

mysql🚀一主多从架构搭建

主从优点

docker 安装

前期规划

创建容器数据卷

master

slave1

slave2

容器数据卷是什么？

配置my.cnf文件

master

slave1

slave2

参数解析

创建mysql容器

master

slave1

slave2

参数解析

远程访问配置

设置同步

创建测试语句

把主库数据的完全备份拷贝到从库（同步前要保持数据一致）

查看master状态

从库设置 master 的信息

从库开始同步

查看同步状态

binlog三种格式

STATEMENT模式（SBR）

ROW模式（RBR）

MIXED模式（MBR）

启动容器出现问题

iptables问题

解决办法

secure_file_priv问题

解决办法

lower_case_table_names问题

解决办法

Public Key Retrieval is not allowed问题