MySQL数据库复制

MySQL数据库复制

MySQL作为非常流行的数据库，支撑它如此出彩的因素主要有两个，InnoDB存储引擎和复制。InnoDB存储引擎支持事务、行级别锁、MVCC。基于此，MySQL能实现高并发。而复制可以将主库的数据同步到另一个实例，基于此我们可以实现读写分离和数据容灾。

一、复制的原理

MySQL主从复制的核心原理是通过二进制日志（binlog）实现的，熟悉Oracle的同学会问：MySQL不是有redo log吗？为什么不直接基于redo log来实现呢？
实际上redo log是InnoDB存储引擎独有的，作为一个支持可插拨存储引擎的数据库，MySQL不仅支持InnoDB存储引擎，还支持MyISAM、CSV、Memory等存储引擎，对于这些存储引擎的操作同样需要持久化到binlog中。

MySQL的复制主要涉及以下3个线程：

• 主库 binlog dump 线程
• 从库 I/O 线程
• 从库 SQL 线程

MySQL的复制其运作流程大致如下：

1. 从库执行完START SLAVE命令后，会创建两个线程：I/O线程和SQL线程。
2. I/O线程会建立一个到主库的连接，相应地，主库会创建一个binlog dump线程来响应这个连
3. 接的请求。此时，对于主库来说，从库的1/O线程就是一个普通的客户端。
4. I/O线程首先告诉主库应该从何处开始发送二进制日志事件。
5. 主库的binlog dump线程开始从指定位置点读取二进制日志事件，并发送给I/O线程。
6. I/O线程接收到二进制日志事件后，会将其写入relay log。
7. SQL线程读取relay log中的二进制日志事件，然后进行重放。

二、复制的搭建

实验版本为 MySQL 8.0.41，部署MySQL异步复制，机器环境如下, 需要分别提前安装数据库软件以及初始化好数据库实例，可以参考我之前的《MySQL数据库安装》的文章

角色	IP
主库	192.168.50.121
从库	192.168.50.122

1.编辑配置文件

主库编辑/etc/my.cnf文件

[mysqld]
basedir = /usr/local/mysql
datadir = /data/mysql/3306/data
socket = /data/mysql/3306/data/mysql.sock
server-id = 1
log-bin = mysql-bing
user = mysql
port = 3306
log-error = /data/mysql/3306/data/mysqld.err
log-timestamps = system

从库/etc/my.cnf文件

[mysqld]
basedir = /usr/local/mysql
datadir = /data/mysql/3306/data
socket = /data/mysql/3306/data/mysql.sock
server-id = 2
log-bin = mysql-bing
user = mysql
port = 3306
log-error = /data/mysql/3306/data/mysqld.err
log-timestamps = system

这里给出的只是搭建复制最简单的参数:

• log-bin 开启 binlog , 这里的mysql-bin是binlog文件的前缀(8.0之后的版本可以不用配置该数,但会习惯性的进行设置)
• server-id 是服务端ID, 在一个复制组内必需全局唯一,有效值为 1~2^32^-1

注意事项:

• server-id 支持在线调整,但开启binlog 需要重启实例
• 在MySQL8.0之前,binlog默认是关闭的,不显示设置log-bin参数,则不开启binlog。而在MySQL8.0中,binlog默认是开启的,如果要关才binlog,需要显示设置skip_log_bin 或 disable_log_bin。

2.在主库上创建复制的用户

在MySQL 8.0 之前可以直接使用第二第命令会隐式创建用户,但官方不推荐这种方式。

create user 'repl'@'192.168.50.122' identified by 'test123';grant replication slave on *.* to 'repl'@'192.168.50.122';

创建用户后在从库环境验证帐号是否能正常登陆

mysql -urepl -ptest123 -h 192.168.50.121

3.获取主库的备份

这里使用mysqldump工具进行备份,备份集通过scp命令拷贝到从库

mysqldump -uroot -ptest123 -S /data/mysql/3306/data/mysql.sock --single-transaction --source-data=2 -E -R --triggers -A > full_backup.sqlscp  full_backup.sql 192.168.50.122:/data

从MySQL 8.0.26开始,开始使用 --source-data 代替之前版本的 --master-data 否则备份文件中会写入以下信息，影响到后续的导入。

WARNING:–master-data is deprecated and will be removed in a future version.use --source-data instead.

4.基于从库的恢复

在从库对备份文件进行导入

mysql -uroot -ptest123 -S /data/mysql/3306/data/mysql.sock < /data/full_backup.sql

5.建立主从复制

在从库执行 CHANGE MASTER TO命令创建主从关系

change master to
master_host='192.168.50.121',
master_port=3306,
master_user='repl',
master_password='test123',
master_log_file='mysql-bing.000001',
master_log_pos=717;

参数具体含义：

参数	作用
master_host	主库的主机信息，可以为主机名或IP
master_port	主库端口，若不指定则默认为3306
master_use	复制的用户名
master_password	复制用户的密码
master_log_file	从库I/O线程启动时，应该从主库的哪个binlog由这个参数决定
master_log_pos	I/O线程确定binlog后,从哪个位置开始读取二进制日志事件.

我们在备份的时候通过 mysqldump 指定的 --source-data=2 (旧版本用--master-data=2) 他会将备份时的binlog位置信息记录到备份文件中,内容如下

--
-- Position to start replication or point-in-time recovery from
---- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bing.000001', MASTER_LOG_POS=717;

当从库执行完CHANGE MASTER TO命令后，复制的相关信息会保存在两张表中：mysqL.slave_master.info和mysql.slave_relay_.log_info

mysql.slave_master_.info主要记录了两类信息：

• 主库的连接信息，包括主库的IP、端口、复制用户和密码;
• I/O线程读取的主库binlog的位置点信息。

注意，这个位置点不是实时更新的，它的更新频率与sync_master_.info参数有关。该参数默认为10000,即I/O线程每写入10000个事务会更新mysql.slave_.master_info一次。

mysql.slave_relay_log_info主要记录了以下信息：

• SQL线程重放relay log的位置点信息。

事务每次提交时都会更新mysqL.slave._relay._log_info

这两张表是MySQL5.6引入的。在此之前复制的相关信息保存在master.info(对应mysql.slave_master.info表)和relay-log.info(对应mysql.slave_.relay_.log_info表)文件中，这两个文件默认位于从库的数据目录下。复制的相关信息是保存在文件还是系统表中由master_info_repository参数决定，其中，FILE代表文件，TABLE代表系统表。从MySQL8.0.2开始，该参数的默认值由FILE调整为TABLE。

6.开启主从复制

在从库上执行如下以下命令

mysql> start slave;

7.查看主从复制状态

重点关注 Slave_IO_Running和 Slave_SQL_Running 的结果均为YES代表主从复制搭建成功

mysql> show slave status\G;
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 192.168.50.121Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bing.000001Read_Master_Log_Pos: 717Relay_Log_File: mysql02-relay-bin.000003Relay_Log_Pos: 327Relay_Master_Log_File: mysql-bing.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 717Relay_Log_Space: 696Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 1Master_UUID: cdc9afd5-fcd8-11ef-9d33-000c29ea0d0aMaster_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position: 0Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:Master_public_key_path:Get_master_public_key: 0Network_Namespace:1 row in set, 1 warning (0.00 sec)

在上面使用 SHOW SLAVE STATUS 命令时,提示了warning通过 show warnings 可以看到,SHOW SLAVE STATUS 已被弃用, 代用SHOW REPLICA STATUS替代。

• 实际上在MySQL 8.0.22 开始 start slave 、stop slave 、show slave staus 、show slave hosts 、reset slave 命令都补弃用了,取而代之的是 start replica、stop rplica 、show replica status、show replicas、reset replica。
• 从MySQL8.O.23开始，CHANGE MASTER TO命令被弃用，取而代之的是CHANGE REPLICATION SOURCE TO
• 从MySQL8.0.26开始，标识符（如系统参数、状态变量）中的MASTER、SLAVE和MTS(multithreadedslave的缩写)将分别被SOURCE、REPLICA和MTA(multithreaded applier的缩写)替换，这一点需要注意。

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                           |
+---------+------+-------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'SHOW SLAVE STATUS' is deprecated and will be removed in a future release. Please use SHOW REPLICA STATUS instead |
+---------+------+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

在主库上执行 show processlist 命令，可以看到 ID为23 连接对应的即是 binlog dump线程。

mysql> show processlist\G;*************************** 3. row ***************************Id: 23User: replHost: 192.168.50.122:52016db: NULL
Command: Binlog DumpTime: 152State: Source has sent all binlog to replica; waiting for more updatesInfo: NULL

在从库是执行 show processlist 命令, 5和6 的连接分别为 I/O线程和SQL线程

mysql>  show processlist\G;
*************************** 1. row ***************************Id: 5User: system userHost: connecting hostdb: NULL
Command: ConnectTime: 226State: Waiting for source to send eventInfo: NULL
*************************** 2. row ***************************Id: 6User: system userHost:db: NULL
Command: QueryTime: 226State: Replica has read all relay log; waiting for more updatesInfo: NULL

至此一个简单的异步复制环境搭建完毕。