mysql🚀一主多从架构搭建

站长

2023年09月01日 04:45 · 阅读数 17

主从优点

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

mysql🚀一主多从架构搭建

docker 安装

前期规划

地址	主从	名称	版本	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

mysql🚀一主多从架构搭建

从库设置 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;

mysql🚀一主多从架构搭建

这里显示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链掉失报错 mysql🚀一主多从架构搭建

解决办法

既然是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

mysql🚀一主多从架构搭建

如果用户使用了 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服务