1-MySQL - 主从复制
目录
主从复制架构演变
基础架构：不需要第三方软件支持
- 1主1从
- 1主多从
- 双主架构
- 多级主从
- 循环复制
- MGR
单活多活：https://zhuanlan.zhihu.com/p/30494653
高可用(MHA,Master High Availability)架构：需要第三方软件支持
- 单活架构：
- Keep Alive + 2主+1从(MMM)
- Keep Alive + 2主
- MHA：三节点1主2从
- TMHA：1主1从
- 多活架构：
- NDB Cluster：MySQL提供的，收费.....
- InnoDB Cluster
- PXC(percona xtradb cluster)
- MGC(MariaDB Calera Cluster)
高性能架构：
- 读写分离：360 Atlas、Alibaba Cobar、ProxySQL(Percona)、MySQL Router、Maxscale、Mycat(开源)
- 分布式：DBLE、Alibaba TDDL、百度 Heisenberg、58同城 Oceanus、Google Vitess、OneProxy、DRDS、InnoDB Cluster
主从复制
-
基于二进制日志复制的
-
主库的修改操作会记录二进制日志
-
从库会请求新的二进制日志并回放，最终达到数据同步的目的
-
主从复制的核心功能：处理数据库物理损坏
- 物理损坏：一般都是操作系统层次的损坏
- 磁盘坏块
- 文件/文件系统
- 逻辑损坏，数据库内部发生的问题，可以通过备份进行恢复
- drop、delete、truncate
- 主从复制为何不能处理逻辑损坏，因为主从复制主要是基于二进制日志的，比如你在主库中drop掉一个库，它会被记录到二进制日志中，然后从库同步回放二进制日志，然后从库中也drop掉了这个库.......
- 物理损坏：一般都是操作系统层次的损坏
-
主从复制的作用：
- 辅助备份，处理物理损坏
- 从主从复制可以延申出：高可用、高性能、分布式的架构
主从复制的前提
搭建主从复制前提：
- 至少有两台及以上的数据库实例，各实例的server_id、server_uuid（GTID中要求）不能相同
- 主库需要开启二进制日志
- 主库中需要建立专用的主从复制用户，具有"replication slave"权限
- 还有两种情况需要注意：
- 主库运行很久后，才搭建从库，那么就必须先把主库全备，然后恢复到从库，然后后续再进行主从复制。
- 主从库是一起搭建的新的环境，那就直接进行主从复制就完了。
- change mater to，建立主从关系。
- start slave，开启专用的复制线程：
- 主库binlog_dump_thread
- 从库slave_io_threading，slave_sql_thread
接下来，我们来徒手搭建一个主从复制环境。
搭建主从复制环境
centos7.9 + mysql5.7.20
单机多实例搭建参考：https://www.cnblogs.com/Neeo/articles/13527500.html
1. 准备数据库实例
你可以参考上面的连接进行多实例环境搭建。
而我这里的单机多实例环境，硬件是4核4G。
主库：10.0.0.201:3307；从库：10.0.0.201:3308。且两个环境都是新的。
/opt/software/mysql # MySQL安装位置
[root@cs ~]# ll /data/mysql/3307/
total 12
drwxr-xr-x. 2 mysql mysql 6 May 9 17:41 backup
drwxr-xr-x. 5 mysql mysql 147 May 9 19:07 data # 主库的数据目录
drwxr-xr-x. 2 mysql mysql 6 May 9 17:41 logs # 主库的log日志所在目录，将来的binlog数据就在这个目录内
-rw-r--r--. 1 mysql mysql 205 May 9 17:45 my.cnf # 主库3307的配置文件
-rw-r-----. 1 mysql mysql 7804 May 9 19:07 mysql.log
[root@cs ~]# ll /data/mysql/3308/
total 12
drwxr-xr-x. 2 mysql mysql 6 May 9 17:41 backup
drwxr-xr-x. 5 mysql mysql 147 May 9 19:07 data # 从库的数据目录
drwxr-xr-x. 2 mysql mysql 6 May 9 17:41 logs
-rw-r--r--. 1 mysql mysql 205 May 9 17:45 my.cnf # 从库3308的配置文件
-rw-r-----. 1 mysql mysql 7876 May 9 19:07 mysql.log
而且两个实例都运行正常。
2. 主库开启二进制日志
[root@cs ~]# mkdir -p /data/mysql/3307/logs/binlog
[root@cs ~]# vim /data/mysql/3307/my.cnf
[root@cs ~]# chown -R mysql:mysql /data/mysql/3307/*
[root@cs ~]# cat /data/mysql/3307/my.cnf
[mysqld]
basedir=/opt/software/mysql
datadir=/data/mysql/3307/data
socket=/data/mysql/3307/mysql.sock
log_error=/data/mysql/3307/mysql.log
port=3307
server_id=7
log_bin=/data/mysql/3307/logs/binlog/mysql-bin
[client]
socket=/data/mysql/3307/mysql.sock
[root@cs ~]# systemctl restart mysqld3307.service
[root@cs ~]# ll /data/mysql/3307/logs/binlog/
total 8
-rw-r-----. 1 mysql mysql 154 May 9 19:48 mysql-bin.000001
-rw-r-----. 1 mysql mysql 46 May 9 19:48 mysql-bin.index
3. 主库中创建专用的复制用户
-- mysql -uroot -p123 -S /data/mysql/3307/mysql.sock
grant replication slave on *.* to rs@'%' identified by '123';
flush privileges;
4. 主库全备，然后恢复到从库
虽然主从环境都是全新的，但我这里模拟一下主库运行了好久之后，才做主从复制的情况。
所以，要先全备主库数据：
# 模拟数据变更
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3307/mysql.sock
create database t1 charset utf8;
use t1
create table a1(id int);
insert into a1 values(1),(2),(3);
# 使用mysqldump进行全备
[root@cs ~]# mysqldump -uroot -p123 -S /data/mysql/3307/mysql.sock -A -E -R --triggers --master-data=2 --single-transaction >/data/mysql/3307/backup/full.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@cs ~]# ls /data/mysql/3307/backup/
full.sql
# 别忘了full.sql中记录的重要信息，下面建立主从关系时会用到
[root@cs ~]# head -n 30 /data/mysql/3307/backup/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=737;
主库备份成功，我们需要将全备数据恢复到从库中去：
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source /data/mysql/3307/backup/full.sql
.....
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| t1 |
+--------------------+
5 rows in set (0.00 sec)
mysql> select * from t1.a1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
OK，数据恢复从库成功。
5. 建立主从关系
在从库中通过帮助信息，找到我们需要的几个字段，然后根据full.sql中保存的position号和当前使用的binlog文件进行字段值的修改：
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
mysql> help change master to # 下面的参数可以用help查看
CHANGE MASTER TO
MASTER_HOST='10.0.0.201',
MASTER_USER='rs',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=737,
MASTER_CONNECT_RETRY=10;
各参数：
MASTER_HOST：主库的IP。MASTER_USER：主库中专门为复制创建的用户名。MASTER_PASSWORD：主库中专门为复制创建的用户名的密码，密码为空则保留空字符串即可。MASTER_PORT：主库监听的端口号。MASTER_LOG_FILE：主库备份时的binlog文件。MASTER_LOG_POS：主库备份文件保存的position号。MASTER_CONNECT_RETRY：如果主从库之间网络环境较差，导致主从失联，那么从库将每隔一段时间尝试重新连接主库，一共尝试10次，该参数可以省略不写。
各参数配置后：就可以在从库执行了：
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.201',
-> MASTER_USER='rs',
-> MASTER_PASSWORD='123',
-> MASTER_PORT=3307,
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=737,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
6. 开启专用的复制线程
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
OK，主从复制环境搭建完毕!!!是不是很简单!!
7. 监控主从复制状态
在从库中操作：
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.201
Master_User: rs
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 737
Relay_Log_File: cs-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 737
Relay_Log_Space: 524
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_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: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 7
Master_UUID: 618d2520-b0ab-11eb-afc8-000c295ead38
Master_Info_File: /data/mysql/3308/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
重要的参数，我们先做了解，后续用到再细说：
# 主库相关的信息监控
Master_Host: 10.0.0.201 # 主库的IP
Master_User: rs # 主库的专用复制用户名
Master_Port: 3307 # 主库的port
Connect_Retry: 10 # 连接失败，重连次数
Master_Log_File: mysql-bin.000001 # 主库当前使用的binlog文件
Read_Master_Log_Pos: 737 # 主库备份时的position号，这个值告诉我们当前从库数据复制到哪个位置点了，随着主库的数据变更，该值也会跟着变化
# 从库中继日志的应用状态
Relay_Log_File: cs-relay-bin.000002 # 从库接收到主库的日志后，会暂存到该参数指定的文件中
Relay_Log_Pos: 320 # 当前从库同步的position号
# 从库复制线程有关状态，运行状态和报错信息
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
# 过滤复制有关的状态
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
# 主从延时相关状态(非人为)
Seconds_Behind_Master: 0
# 延时从库有关状态(人为配置)
SQL_Delay: 0
SQL_Remaining_Delay: NULL
# GTID复制有关的状态
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
如，我们在主库中做个操作，来看从库的Read_Master_Log_Pos值是否跟着变更：
# 先在主库中操作，模拟数据变更
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3307/mysql.sock
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 737 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> insert into t1.a1 values(4),(5),(6);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 994 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
来看从库的主从状态：
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.201
Master_User: rs
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 994
Relay_Log_File: cs-relay-bin.000002
Relay_Log_Pos: 577
可以看到Read_Master_Log_Pos的确跟主库的position保持一致。
当然，你可以在主库中执行下面的命令来查看此时连接主库的从库列表：
show slave hosts;
主从复制原理
主从复制过程中涉及到的文件
- 主库：
[root@cs ~]# ll /data/mysql/3307/logs/binlog/
total 8
-rw-r-----. 1 mysql mysql 994 May 9 20:36 mysql-bin.000001 # 二进制日志
-rw-r-----. 1 mysql mysql 46 May 9 20:07 mysql-bin.index
- 从库：
[root@cs ~]# ll /data/mysql/3308/data/
total 122940
-rw-r-----. 1 mysql mysql 56 May 9 17:46 auto.cnf
-rw-r-----. 1 mysql mysql 5 May 9 20:04 cs.pid
-rw-r-----. 1 mysql mysql 204 May 9 20:24 cs-relay-bin.000001 # 以主机名开头的中继日志文件
-rw-r-----. 1 mysql mysql 577 May 9 20:36 cs-relay-bin.000002
-rw-r-----. 1 mysql mysql 44 May 9 20:24 cs-relay-bin.index
-rw-r-----. 1 mysql mysql 357 May 9 19:07 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 May 9 20:36 ibdata1
-rw-r-----. 1 mysql mysql 50331648 May 9 20:36 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 May 9 17:46 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 May 9 20:04 ibtmp1
-rw-r-----. 1 mysql mysql 118 May 9 21:43 master.info # 主库信息
drwxr-x---. 2 mysql mysql 4096 May 9 20:08 mysql
drwxr-x---. 2 mysql mysql 8192 May 9 17:46 performance_schema
-rw-r-----. 1 mysql mysql 56 May 9 20:36 relay-log.info # 中继日志信息
drwxr-x---. 2 mysql mysql 8192 May 9 17:46 sys
drwxr-x---. 2 mysql mysql 48 May 9 20:08 t1
主从复制中涉及到的线程
- 主库：
binlog_dump_thread:dump线程
- 从库：
slave_io_thread: io线程
slave_sql_thread:sql线程
图说主从复制原理
如上图，当在从库中执行CHANGE MASTER TO后，从库会把相关参数保存到master.info中。
当从库执行start slave后，先开启slave_io_thread和slave_sql_thread：
-
slave_io_thread干了哪些事儿：- 读取
master.info文件中的信息，然后根据其配置信息去连接了主库；主库的连接层接收到从库的连接请求并验证合法性，然后开启一个binlog_dump_thread与从库的slave_io_thread建立了连接通道(它们之间会交换master.info中的相关参数，如position号)，另外，主库的binlog_dump_thread会一直存活，可以在主库中通过show processlist;命令查看。 - 而主库的
binlog_dump_thread会根据slave_io_thread发来的position号等信息，去binlog中"看看"有没有新的变更日志，如果有的话，就以事件的形式发送给slave_io_thread。另外，主库的binlog_dump_thread会一直监控binlog，如果有新的日志产生，就会通过通道发送给从库的slave_io_thread。 - 而从库的
slave_io_thread在接收到主库binlog_dump_thread发来的新的变更日志后，先在"TCP/IP缓存"中进行缓存，会给主库返回一个ACK确认信息，主库的binlog_dump_thread确认后，主库工作完成了。 - 而"TCP/IP缓存"中的binlog最后会落盘到中继日志
relay-log.0000x文件中。 - 此时，从库的
slave_io_thread的任务暂告一段落。
- 读取
-
从库的
slave_sql_thread干了哪些事儿：slave_sql_thread首先读取relay-log.info，当然，首次读取relay-log.info为空，那就意味着从头开始回放relay-log.0000x中继日志的binlog日志，进行数据恢复。- 当
relay-log.0000x回放完毕后，会将当前最新的position号：- 更新到
master.info文件中，这意味着slave_io_thread下一次跟主库请求日志时，只要最新的position号后产生的binlog日志。 - 更新到
relay-log.info文件中，这意味着slave_sql_thread在下次回放数据时，也只需要回放最新的position号后的binlog日志即可。
- 更新到
- 一次回放结束，
slave_sql_thread线程的工作也暂告一段落了。
-
这样，通过主从库的相关线程和相关文件的配合，完成主从复制过程。
-
另外，从库的purge线程(后台线程)会自动将回放后的relay-log清理掉。
接下来，我们来研究下，在生产环境中，经常出现的一些主从复制问题。
主从异常
主从异常可以分为两部分：
- 主从故障
- 主从延时
主从故障
主从故障很明显的一个特点就是slave_io_thread和slave_sql_thread罢工了.....
我们分别来看。
slave_io_thread故障
我们来分析下slave_io_thread可能的罢工原因。
连接主库失败
- ip、port配置有问题
- 用户名、密码配置有问题
- 网络、防火墙问题
- 遇到了主库的连接数的上限限制，或者主库压根没运行或者宕机
当连接主库失败：
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
mysql> show slave status \G
Slave_IO_Running: Connecting # 连接主库失败都报这个错误
如果主库连接数达到上限，Slave_IO_Running还是connecting error，但如果你手动连接主库，会遇到 Too many connections
我们来试一下。
首先停了主从库，然后编辑主库的配置文件：
[root@cs ~]# systemctl stop mysqld3307.service
[root@cs ~]# systemctl stop mysqld3308.service
[root@cs ~]# vim /data/mysql/3307/my.cnf
[root@cs ~]# cat /data/mysql/3307/my.cnf
[mysqld]
basedir=/opt/software/mysql
datadir=/data/mysql/3307/data
socket=/data/mysql/3307/mysql.sock
log_error=/data/mysql/3307/mysql.log
port=3307
server_id=7
log_bin=/data/mysql/3307/logs/binlog/mysql-bin
max_connections=1
[client]
socket=/data/mysql/3307/mysql.sock
注意，当max_connections值为1时，它允许最多允许有两个连接。
现在，启动主库，然后我使用xshell打开了三个窗口，进行访问登录，前两个都没事，第三个窗口访问时，就遇到了问题：
[root@cs ~]# systemctl start mysqld3307.service
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1040 (HY000): Too many connections
此时，再启动从库，你会发现，它连不上主库了：
[root@cs ~]# systemctl start mysqld3308.service
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_Running: Connecting
Last_IO_Errno: 1040
Last_IO_Error: error connecting to master 'rs@10.0.0.201:3307' - retry-time: 10 retries: 2
Last_SQL_Errno: 0
Last_SQL_Error:
虽然它连不上主库，但Slave_IO_Running值还是Connecting。
OK，效果演示出来了，记得恢复配置文件，然后重启主从库。
请求和接收二进制日志出现问题
如果主库的二进制日志被误删或者出现问题，从库就会报错。我们来演示下主库二进制出现问题的现象。
在binlog部分说过，主从复制中，禁止使用reset master，因为使用改命令，主从环境必崩，来来来，我们来搞一下。
前提，我现在的主从环境是好的：
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.201
Master_User: rs
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 154
Relay_Log_File: cs-relay-bin.000007
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
那么，在主库中执行：
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3306/mysql.sock
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1017 |
| mysql-bin.000002 | 177 |
| mysql-bin.000003 | 154 |
+------------------+-----------+
3 rows in set (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
现在，你再去从库看主从状态：
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.0.0.201
Master_User: rs
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 154
Relay_Log_File: cs-relay-bin.000007
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 784
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'could not find next log; the first event 'mysql-bin.000001' at 994, the last event read from '/data/mysql/3307/logs/binlog/mysql-bin.000003' at 154, the last byte read from '/data/mysql/3307/logs/binlog/mysql-bin.000003' at 154.'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 7
Master_UUID: 618d2520-b0ab-11eb-afc8-000c295ead38
Master_Info_File: /data/mysql/3308/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 210509 23:42:13
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
其中有两个重要的参数值得我们注意：
Slave_IO_Running: No
Last_IO_Errno: 1236
Last_IO_Error: # 一行太长，我手动换个行
Got fatal error 1236 from master when reading data from binary log:
'could not find next log; the first event 'mysql-bin.000001' at 994,
the last event read from '/data/mysql/3307/logs/binlog/mysql-bin.000003' at 154,
the last byte read from '/data/mysql/3307/logs/binlog/mysql-bin.000003' at 154.'
报错的意思是，我(从库)最后记录的事件是主库二进制日志的3号文件的154位置，最初的事件位置是主库二进制日志1号文件的994位置(本篇搭建主从复制环境部分，的确是从994开始的，往上翻翻看)；但你(主库)现在的的二进制日志是1号文件的154位置。我(从库)现在该怎么办？只好罢工了.........
效果演示出来了，但问题也来了，reset master导致的主从环境崩了后，怎么恢复？？？？说来就麻烦了，只能重新搭建主从环境了。
思路是还是先全备主库，然后从头搞从库的环境了。
- 将从库恢复如初：
[root@cs ~]# netstat -lnp|grep 330
tcp6 0 0 :::3307 :::* LISTEN 3927/mysqld
tcp6 0 0 :::3308 :::* LISTEN 4109/mysqld
unix 2 [ ACC ] STREAM LISTENING 60394 3927/mysqld /data/mysql/3307/mysql.sock
unix 2 [ ACC ] STREAM LISTENING 82107 4109/mysqld /data/mysql/3308/mysql.sock
[root@cs ~]# systemctl stop mysqld3308.service
[root@cs ~]# netstat -lnp|grep 330
tcp6 0 0 :::3307 :::* LISTEN 3927/mysqld
unix 2 [ ACC ] STREAM LISTENING 60394 3927/mysqld /data/mysql/3307/mysql.sock
[root@cs ~]# rm -rf /data/mysql/3308/data/*
[root@cs ~]# chown -R mysql:mysql /data/mysql/3308/
[root@cs ~]# mysqld --initialize-insecure --user=mysql --datadir=/data/mysql/3308/data --basedir=/opt/software/mysql
2021-05-09T16:44:38.084331Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-05-09T16:44:38.230792Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-05-09T16:44:38.255635Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-05-09T16:44:38.316152Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: d8055c72-b0e5-11eb-b08e-000c295ead38.
2021-05-09T16:44:38.318269Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-05-09T16:44:38.318705Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@cs ~]# systemctl start mysqld3308.service
[root@cs ~]# netstat -lnp|grep 330
tcp6 0 0 :::3307 :::* LISTEN 3927/mysqld
tcp6 0 0 :::3308 :::* LISTEN 4321/mysqld
unix 2 [ ACC ] STREAM LISTENING 60394 3927/mysqld /data/mysql/3307/mysql.sock
unix 2 [ ACC ] STREAM LISTENING 76946 4321/mysqld /data/mysql/3308/mysql.sock
[root@cs ~]# mysql -uroot -p -S /data/mysql/3308/mysql.sock
Enter password: # 默认密码为空，直接回车
mysql> show slave status;
Empty set (0.00 sec)
mysql> grant all on *.* to root@'localhost' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all on *.* to root@'%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status;
Empty set (0.00 sec)
- 全备主库数据，这里主库的二进制日志功能已经开启了，专用复制用户也创建好了，所以，直接全备数据，然后恢复到从库：
[root@cs ~]# mysqldump -uroot -p123 -S /data/mysql/3307/mysql.sock -A -E -R --triggers --master-data=2 --single-transaction >/data/mysql/3307/backup/full.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@cs ~]# ls /data/mysql/3307/backup/
full.sql
[root@cs ~]# head -n 30 /data/mysql/3307/backup/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source /data/mysql/3307/backup/full.sql
....
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| t1 |
+--------------------+
5 rows in set (0.00 sec)
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
数据恢复从库成功。
- 建立主从关系，在从库中执行
CHANGE MASTER TO，并修改相应的字段值：
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.201',
-> MASTER_USER='rs',
-> MASTER_PASSWORD='123',
-> MASTER_PORT=3307,
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=154,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
- 开启专用复制线程，并查看主从复制状态是否恢复：
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.201
Master_User: rs
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: cs-relay-bin.000003
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 737
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_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: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 7
Master_UUID: 618d2520-b0ab-11eb-afc8-000c295ead38
Master_Info_File: /data/mysql/3308/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
OK了。一切恢复如初......
其他从库可用命令：
stop slave; # 停止主从复制
reset slave all; # 清空master.info
slave_sql_thread故障
导致slave_sql_thread罢工的可能原因
- 中继日志或者relay-log.info文件损坏。
- 综合来说，大部分原因都是从库发生写入操作，导致出现
slave_sql_thread罢工：- 版本差异、参数设定不同，如数据类型不同，sql_mode也可能有影响。
- 要创建的数据库对象已存在；要删除或者修改的对象不存在。
- DM语句有问题，比如表定义有问题，约束问题。
我们来模拟一个slave_sql_thread故障。
首先，保证主从环境是好的。
场景，开发人员误连接到了从库，然后进行了一系列的操作，如创建了一个数据库：
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
# 主从环境是好的
mysql> show slave status \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
# 某个开发人员创建个了数据库
mysql> create database s1;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| s1 |
| sys |
| t1 |
+--------------------+
6 rows in set (0.01 sec)
# 此时的主从环境还没问题
mysql> show slave status \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
某一天，这个开发人员，终于连接到了正确的数据库，也就是主库，他就发现之前创建的s1数据库没了，就有手动创建了一个：
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3307/mysql.sock
mysql> create database s1 charset utf8;
Query OK, 1 row affected (0.00 sec)
这个时候，从库已经坏了：
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
mysql> show slave status \G
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1007
Last_Error: Error 'Can't create database 's1'; database exists' on query. Default database: 's1'. Query: 'create database s1 charset utf8'
Last_SQL_Errno: 1007
Last_SQL_Error: Error 'Can't create database 's1'; database exists' on query. Default database: 's1'. Query: 'create database s1 charset utf8'
从库报错说s1已经存在了，那么从主库同步过来的创建s1的语句就无法执行了。
虽然此时的主从复制出现了问题，但开发人员才不管这些，人家现在连主库，操作什么的都没问题。但我们身为管理员，却要处理这个问题，该怎么搞呢？当然了，没有什么是重新搭建主从环境解决不了的.....
MySQL也提供了两种解决方案。
方案1：即以从库为准，比如遇到重复的SQL，就跳过，不再执行了，拿到咱们的这个例子中来，就是从库发现自己这里已经有了s1数据库，那么就不再执行主库的建库语句了。
在从库中执行：
stop slave;
set global sql_slave_skip_counter = 1;
start slave;
方案2：在从库的配置文件中，写入当遇到哪些报错，就直接跳过。
vim /data/mysql/3308/my.cnf
[mysqld]
slave-skip-errors = 1032,1062,1007
1007:对象已存在
1032:无法执行DML
1062:主键冲突,或约束冲突
方案虽然有了，但是上面两种操作都存在隐患，最安全的做法还是重新构建主从。另外，主从环境下，我们要把握一个原则，一切以主库为主。
那么既然说了存在隐患，我们就把这个隐患暴露出来吧。
现在，由于主从已经坏了，我们直接在从库中执行：
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> set global sql_slave_skip_counter = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
是不是很好使，立马就好了！！但马上问题就来了，现在我们在主库中向s1库中创建个表，然后插入一些数据：
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3307/mysql.sock
mysql> use s1
Database changed
mysql> create table a1(id int, name varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into a1 values(1,"Anthony"),(2, "张开");
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from a1;
+------+---------+
| id | name |
+------+---------+
| 1 | Anthony |
| 2 | 张开 |
+------+---------+
2 rows in set (0.00 sec)
完事之后，你再看从库的主从复制状态：
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
mysql> show slave status \G
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1677
Last_Error: Column 1 of table 's1.a1' cannot be converted from type 'varchar(60(bytes))' to type 'varchar(20(bytes) latin1)'
Last_SQL_Errno: 1677
Last_SQL_Error: Column 1 of table 's1.a1' cannot be converted from type 'varchar(60(bytes))' to type 'varchar(20(bytes) latin1)'
报错说什么类型错误，原因是什么呢？
-- 从库建库语句，默认字符集是latin1
create database s1;
-- 主库见库语句，默认字符集是utf8
create database s1 charset utf8;
两个库的字符集都不同，所以，从库无法写入，报错了。
得了， 现在，只能重新搭主从环境了。
# 1. 初始化从库环境
[root@cs ~]# systemctl stop mysqld3308.service
[root@cs ~]# netstat -lnp|grep 330 # 确保从库彻底关闭了
[root@cs ~]# rm -rf /data/mysql/3308/data/*
[root@cs ~]# chown -R mysql:mysql /data/mysql/3308/
[root@cs ~]# mysqld --initialize-insecure --user=mysql --datadir=/data/mysql/3308/data --basedir=/opt/software/mysql
[root@cs ~]# systemctl start mysqld3308.service
[root@cs ~]# mysql -uroot -p -S /data/mysql/3308/mysql.sock
show slave status;
grant all on *.* to root@'localhost' identified by '123';
grant all on *.* to root@'%' identified by '123';
flush privileges;
show slave status;
# 2. 主库全备，并恢复到从库中
[root@cs ~]# mysqldump -uroot -p123 -S /data/mysql/3307/mysql.sock -A -E -R --triggers --master-data=2 --single-transaction >/data/mysql/3307/backup/full.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@cs ~]# head -n 30 /data/mysql/3307/backup/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=768;
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
set sql_log_bin=0;
source /data/mysql/3307/backup/full.sql
show databases;
set sql_log_bin=1;
# 3. 建立主从复制关系，开启专用线程，进行主从同步,然后查看是否恢复
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
CHANGE MASTER TO
MASTER_HOST='10.0.0.201',
MASTER_USER='rs',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=768,
MASTER_CONNECT_RETRY=10;
start slave;
mysql> show slave status \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> select * from s1.a1;
+------+---------+
| id | name |
+------+---------+
| 1 | Anthony |
| 2 | 张开 |
+------+---------+
2 rows in set (0.01 sec)
OK，主从环境又好了。
但这天天的徒手搭建主从环境，遇到问题就徒手重构主从环境.....说实话，我吐了......
所以呐，有没有一劳永逸的解决办法呢？当然后，不过也是相对的。
方案1：可以将从库设置为只读库：
vim /data/mysql/3308/my.cnf
[mysqld]
read_only=1
但上面的操作，只能限制普通用户，管理员....还是限制不了，所以，还是要从用户权限下手啊，别瞎给人管理员账号来登录你的数据库。
方案2：加中间件，所有的数据库请求，先走中间件，然后中间件将数据变更得请求都转发给主库，查询请求都转发给从库。这个方案稍微好点。但是，你防不住人家管理员直接绕过中间件直联数据库啊......所以，还是抓用户权限吧!!!!
说了一堆，还是人的问题!!!!!!!
主从延时
所谓主从延时，也就是主库发生了数据变更后，从库要经过一定延时后，才进行同步。
可能导致主从延时的延时的原因。
外部因素
- 网络环境导致、主从库所在的服务器的硬件性能差异
- MySQL版本存在问题、相关参数设置不一致导致
主库因素
- 二进制日志落盘不及时，由
sync_binlog参数控制，默认该值等于1，即事务提交后立即写入到二进制日志中。
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3307/mysql.sock
mysql> select @@sync_binlog;
+---------------+
| @@sync_binlog |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
- 在5.7之前(5.6，5.5)，传统的(CR，classic replication )主从复制模式中，
binlog_dump_thread以事件为单位，串行的传送二进制日志。- 主库并行事务量大，会并行的产生二进制日志，但依然是串行传送二进制日志导致主从延时高。
- 主库有大事物产生，由于是串行传送日志，它会阻塞后续的事务。
- MySQL5.6，在开启GTID功能后，可以使用GC(group commit)机制，进行并行的传输日志，提高了主从复制效率，降低延时。
- MySQL5.7开始，有了匿名的GTID，所以，无需开启GTID功能也能实现了GC(group commit)机制，进一步降低了主从的延时性，当然，这里也建议手动开启GTID。
上面的使用GTID解决串行日志传输问题。
但是大事务怎么办？只能将大事务尽可能的拆分为多个小事物，尽可能的减少主从延时。
从库因素
这里只讨论从库的细节，而不展开硬件的相关影响。
一般的，在传统的(RC,classic replication)主从复制模式中，默认的，从库只有一个SQL线程进行回放事务SQL。
也因此，SQL线程往往是导致主从延时的一个重要因素。
- 主库如果并行发送日志，且事务量又比较大，而从库只有一个SQL线程处理回放SQL，这结果也就不言而喻，压根处理不过来。
- 主库发生大事物导致了阻塞后续的所有事务的运行，也是一个因素。
如何解决呢？就是并行复制。
MySQL5.6版本，开启GTID功能之后，加入从库多线程SQL的特性。但该功能还是有些缺陷，只能针对不同库(基于database级别)下的事务进行并行回放。
MySQL5.7版本，开启GTID功能之后，在从库的SQL线程方面，加入逻辑时钟(logical_clock)，又结合binlog的seq_no机制，真正的实现了基于事务级别的并行回放。这种技术被官方称为enhanced multi-threaded slave（简称MTS）。
延时从库
延时从库是一种特殊的从库，人为的配置从库和主库的延时时间。
为什么需要延时从库？
- 误操作时，能更快的恢复数据。普通的主从复制模式下，当主库有误操作，从库也会同步进行误操作，这个时候你就要根据全备和二进制日志进行恢复了。但有了延时从库，我们可以在误操作前停下从库，进行数据恢复，这样无疑大大加快了数据恢复的时间。
- 可以把延时从库当作一个备份节点。
- 可以把延时从库当作一些问题、现象、案例的研究对象。如果要在主库上进行研究，风险比较大，所以，延时从库就是一个很好的替代品了。
延时从库的延时，主要说的是SQL线程的延时，而io线程正常执行，这样的话，最新的日志会保存到中继日志(relay-log.0000x)中，在必要的时候，去中继日志中截取GTID或者position号，进行数据恢复。
一般企业生产环境建议延时3~6个小时。具体也要看公司的DBA对于故障的反应时间，如果延时时间设置太短，DBA还没反应过来，延时从库就同步了误操作的相关日志.....那不是白瞎了么....
配置延时从库
还在原来的主从复制环境基础上进行配置。
此时我的主从复制环境是好的：
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
而配置延时从库也非常简单，就一个主要参数，就是延时时间，单位是秒。
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
stop slave;
CHANGE MASTER TO MASTER_DELAY=300;
start slave;
show slave status \G
mysql> show slave status \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Master_UUID: 618d2520-b0ab-11eb-afc8-000c295ead38
Master_Info_File: /data/mysql/3308/data/master.info
SQL_Delay: 300
SQL_Remaining_Delay: NULL
配置完成后，主库的数据变更操作，都会正常的发送到了从库的中继日志上，只不过从库SQL线程会在5分钟之后进行回放SQL。
基于延时从库的故障恢复
首先确认，延时从库和主库都是可用的。
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
mysql> show slave status \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Master_Info_File: /data/mysql/3308/data/master.info
SQL_Delay: 300
SQL_Remaining_Delay: NULL
1. 主库模拟故障
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3307/mysql.sock
create database t2 charset utf8;
use t2
create table a1(id int);
insert into a1 values(1),(2),(3);
drop database t2;
2. 发现故障，立即停止从库的SQL线程
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3307/mysql.sock
stop slave sql_thread;
3. 根据当前使用的relaylog，确定起点和终点
起点，根据下面示例，当SQL线程停止时，Relay_Log_Pos记录了最后一次回放的position号，也可以通过relay-log.info文件确认，的确是320。且当前的relaylog是cs-relay-bin.000002。
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
mysql> show slave status \G
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1502
Relay_Log_File: cs-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
[root@cs ~]# cat /data/mysql/3308/data/relay-log.info
7
./cs-relay-bin.000002
320
mysql-bin.000002
768
300
0
1
终点：
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
mysql> show relaylog events in 'cs-relay-bin.000002';
+---------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------------+-----+----------------+-----------+-------------+---------------------------------------+
| cs-relay-bin.000002 | 4 | Format_desc | 8 | 123 | Server ver: 5.7.20, Binlog ver: 4 |
| cs-relay-bin.000002 | 123 | Previous_gtids | 8 | 154 | |
| cs-relay-bin.000002 | 154 | Rotate | 7 | 0 | mysql-bin.000002;pos=768 |
| cs-relay-bin.000002 | 201 | Format_desc | 7 | 0 | Server ver: 5.7.20-log, Binlog ver: 4 |
| cs-relay-bin.000002 | 320 | Anonymous_Gtid | 7 | 833 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| cs-relay-bin.000002 | 385 | Query | 7 | 934 | create database t2 charset utf8 |
| cs-relay-bin.000002 | 486 | Anonymous_Gtid | 7 | 999 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| cs-relay-bin.000002 | 551 | Query | 7 | 1092 | use `t2`; create table a1(id int) |
| cs-relay-bin.000002 | 644 | Anonymous_Gtid | 7 | 1157 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| cs-relay-bin.000002 | 709 | Query | 7 | 1227 | BEGIN |
| cs-relay-bin.000002 | 779 | Table_map | 7 | 1270 | table_id: 254 (t2.a1) |
| cs-relay-bin.000002 | 822 | Write_rows | 7 | 1320 | table_id: 254 flags: STMT_END_F |
| cs-relay-bin.000002 | 872 | Xid | 7 | 1351 | COMMIT /* xid=559 */ |
| cs-relay-bin.000002 | 903 | Anonymous_Gtid | 7 | 1416 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| cs-relay-bin.000002 | 968 | Query | 7 | 1502 | drop database t2 |
+---------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)
这里的终点只需要看Pos栏即可，每行Pos对应的是主库的position号，但这里我们不关注这个。
根据事件确认删库前的Pos值是903，这样起点和终点都有了。
但由于从库没有GTID，所以还需要根据position号进行截取日志：
[root@cs ~]# mysqlbinlog --start-position=320 --stop-position=903 /data/mysql/3308/data/cs-relay-bin.000002 >/tmp/relay.log
[root@cs ~]# ls /tmp/re*
/tmp/relay.log
4. 主库恢复数据
从库的relaylog中导出了需要恢复的SQL日志。只需要导到主库中即可。
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3307/mysql.sock
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source /tmp/relay.log
.....
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.01 sec)
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source /tmp/relay.log
.....
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.01 sec)
现在，从库和主库的数据应该都一致了，可以继续让从库进行主从复制了：
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.201
Master_User: rs
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 2085
Relay_Log_File: cs-relay-bin.000004
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Master_Info_File: /data/mysql/3308/data/master.info
SQL_Delay: 300
SQL_Remaining_Delay: NULL
因为我们同时在主从库中都进行了数据恢复，所以就算此时从库有报错产生，比如库表已存在之类的，你可以：
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
stop slave;
set global sql_slave_skip_counter = 1;
start slave;
直接跳过这个错误，因为这个时候我们主库和从库数据一致(手动恢复的)，且主库也没有新的数据变更，直至主从环境正常为止。
延时从库的数据恢复演示完毕后，我们将主从恢复到之前主从复制环境，即不在设置延时从库：
# 获取主库的position号和当前使用的二进制日志，方便从库好恢复
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3307/mysql.sock
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 2085 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 然后从新配置从库
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
stop slave;
reset slave all;
CHANGE MASTER TO
MASTER_HOST='10.0.0.201',
MASTER_USER='rs',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=2085,
MASTER_CONNECT_RETRY=10;
start slave;
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.201
Master_User: rs
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 2085
Relay_Log_File: cs-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
SQL_Delay: 0
SQL_Remaining_Delay: NULL
半同步复制
想要理解半同步复制，我们首先来简单了解下异步、全同步复制的概念。
异步复制(Asynchronous replication)
MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并成功处理，如果从库意外崩了，导致有些日志还没有来得及写入到中继日志中，这就会出现一些问题，最终可能会造成主从数据一致性的问题。
全同步复制（Fully synchronous replication）
指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。
半同步复制（Semisynchronous replication）
介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制(异步复制接受主库的事件先放入TCP/IP缓存，就开始给主库返回了)，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。
感觉半同步复制很厉害的样子，我们就来再展开介绍下：
- 半同步复制主要解决了主从数据不一致的问题。
- MySQL5.5出现，存在缺陷。
- MySQL5.6得到加强。
- MySQL5.7就比较完善了，本以为麻雀飞上枝头变凤凰，没想到......
其实，半同步复制再怎么增强，从安全、性能等角度来说，它并不很强，导致用的人不多......
所以MySQL5.7.17以后就发布了一个无损复制(MGR)，它完美解决了主从数据一致性的问题。也因此半同步复制就常当作反面教材，体现出MGR模式是多么的优秀....半同步复制就立马不香了！！！
复制过滤
复制过滤通常应用于：
- 主库的指定库或者指定表要单独复制到指定从库。
- 从库只复制主库中的制定库或者指定表。
MySQL提供两种复制顾虑的配置方式。
在主库上进行控制
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 2085 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
Binlog_Do_DB # 白名单
Binlog_Ignore_DB # 黑名单
在从库上进行控制配置(推荐，用的较多)
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
mysql> show slave status \G
# 指定库，用的最多
Replicate_Do_DB:
Replicate_Ignore_DB:
# 指定表
Replicate_Do_Table:
Replicate_Ignore_Table:
# 模糊表，比如以user开头的表
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
这里我们来演示下过滤复制在从库上的配置。
首先，我此时的主从是好的：
mysql> show slave status \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
修改从库的配置文件：
[root@cs ~]# vim /data/mysql/3308/my.cnf
[mysqld]
basedir=/opt/software/mysql
datadir=/data/mysql/3308/data
socket=/data/mysql/3308/mysql.sock
log_error=/data/mysql/3308/mysql.log
port=3308
server_id=8
#注意，配置文件中的属性必须都是小写形式，每行只能写一个库，多个库的话就写多行
replicate_do_db=user1
replicate_do_db=user2
[client]
socket=/data/mysql/3308/mysql.sock
[root@cs ~]# systemctl restart mysqld3308.service
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
mysql> show slave status \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: user1,user2
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| s1 |
| sys |
| t1 |
| t2 |
+--------------------+
7 rows in set (0.00 sec)
重启之后，当前从库只能同步user1和user2两个数据库了。且，主从状态中也能体现出来。
现在，我们在主库中创建三个库，观察从库的同步情况。
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3307/mysql.sock
create database user1 charset utf8;
create database user2 charset utf8;
create database user3 charset utf8;
[root@cs ~]# mysql -uroot -p123 -S /data/mysql/3308/mysql.sock
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| s1 |
| sys |
| t1 |
| t2 |
| user1 |
| user2 |
+--------------------+
9 rows in set (0.00 sec)
此时，从库中，只同步了user1和user2两个数据的变更操作。
GTID Replication
GTID介绍
GTID是 MySQL 5.6 版本引入的一个有关于主从复制的重大改进，相对于之前版本基于Binlog文件+Position的主从复制，基于GTID的主从复制，数据一致性更高，主从数据复制更健壮，主从切换、故障切换不易出错，很少需要人为介入处理。，并在MySQL5.7中得到增强，也推荐在5.7及以上版本中开启该功能。
GTID(Global Transaction ID)是对于一个已提交的事务的全局唯一的编号。它不仅在主从复制中是全局唯一，包括在MySQL的集群中也是全局唯一的。
GTID有两部组成：
server_uuid:tranaction_id
618d2520-b0ab-11eb-afc8-000c295ead38:18
核心特性：
- 全局唯一，具备幂等性
- 趋势递增，方便必要的时候继续范围截取。
GITD配置中的核心参数：
gtid-mode=on # 启用gtid
enforce-gtid-consistency=true # 强制gtid一致性
log-slave-updates=1 # salve更新是否计入日志
参考上图，来理解log-slave-updates=1参数的用途，b节点是a节点的从库，但同时也是c节点的主库。当遇到这样的一个情况：
- b和c节点都需要同步a节点的数据变更。
- 如果b节点在回放事件时，不往本地刷写日志，那么c节点就同步不到数据。所以
log-slave-updates=1参数的意义不言而喻了。
配置过程
我们这里准备一主两从的主从环境：
环境准备
准备三台虚拟机
我这三台虚拟机都是新安装好的多实例，环境还是干净的。
下面关于三台虚拟机的基本配置。
db01：
[root@db01 ~]# hostnamectl set-hostname db01
[root@db01 ~]# vim /etc/sysconfig/network-scripts/ifcfg-eth0
# 去掉UUID
#UUID=ed848b09-d61d-432d-811f-2ae62c7470a0
IPADDR=10.0.0.204
[root@db01 ~]# systemctl restart network
[root@db01 ~]# vim /etc/hosts
10.0.0.204 db01
[root@db01 ~]# ping www.baidu.com
[root@db01 ~]# reboot now
db02：
[root@db02 ~]# hostnamectl set-hostname db02
[root@db02 ~]# vim /etc/sysconfig/network-scripts/ifcfg-eth0
# 去掉UUID
#UUID=ed848b09-d61d-432d-811f-2ae62c7470a0
IPADDR=10.0.0.205
[root@db02 ~]# systemctl restart network
[root@db02 ~]# vim /etc/hosts
10.0.0.205 db02
[root@db02 ~]# ping www.baidu.com
[root@db02 ~]# reboot now
db03：
[root@db03 ~]# hostnamectl set-hostname db03
[root@db03 ~]# vim /etc/sysconfig/network-scripts/ifcfg-eth0
# 去掉UUID
#UUID=ed848b09-d61d-432d-811f-2ae62c7470a0
IPADDR=10.0.0.206
[root@db03 ~]# systemctl restart network
[root@db03 ~]# vim /etc/hosts
10.0.0.205 db03
[root@db03 ~]# ping www.baidu.com
[root@db03 ~]# reboot now
配置三台MySQL的配置文件
db01:
cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/opt/software/mysql
datadir=/data/mysql/3306/data
server_id=204
port=3306
socket=/tmp/mysql.sock
secure-file-priv=/tmp
log_bin=/data/mysql/3306/logs/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
socket=/tmp/mysql.sock
prompt=db01 [\\d]>
user=root
password=123
EOF
# ------------------------ 创建binlog目录，然后启动MySQL服务 ----------
> /data/mysql/3306/data/auto.cnf
mkdir -p /data/mysql/3306/logs/binlog
chown -R mysql:mysql /data/mysql/3306/logs/binlog
systemctl start mysqld
mysql -uroot -p123 -e "select @@server_id"
db02:
cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/opt/software/mysql
datadir=/data/mysql/3306/data
server_id=205
port=3306
socket=/tmp/mysql.sock
secure-file-priv=/tmp
log_bin=/data/mysql/3306/logs/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
socket=/tmp/mysql.sock
prompt=db02 [\\d]>
user=root
password=123
EOF
# ------------------------ 创建binlog目录，然后启动MySQL服务 ----------
> /data/mysql/3306/data/auto.cnf
mkdir -p /data/mysql/3306/logs/binlog
chown -R mysql:mysql /data/mysql/3306/logs/binlog
systemctl start mysqld
mysql -uroot -p123 -e "select @@server_id"
db03:
cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/opt/software/mysql
datadir=/data/mysql/3306/data
server_id=206
port=3306
socket=/tmp/mysql.sock
secure-file-priv=/tmp
log_bin=/data/mysql/3306/logs/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
socket=/tmp/mysql.sock
prompt=db03 [\\d]>
user=root
password=123
EOF
# ------------------------ 创建binlog目录，然后启动MySQL服务 ----------
> /data/mysql/3306/data/auto.cnf
mkdir -p /data/mysql/3306/logs/binlog
chown -R mysql:mysql /data/mysql/3306/logs/binlog
systemctl start mysqld
mysql -uroot -p123 -e "select @@server_id"
注意，由于我的三台虚拟机是克隆出来的，而每个虚拟机中的MySQL都是配置好的，所以三台MySQL的uuid是一样的，但这样在后续的构建主从时，会报错：
Last_IO_Error: Fatal error:
The slave I/O thread stops because master and slave have equal MySQL server UUIDs;
these UUIDs must be different for replication to work.
所以，上面在每台MySQL服务启动之前，手动清空各自的uuid所在文件，当MySQL服务重启后，会自动在重新创建出来，达到uuid不重复的目的。
构建主从环境
首先，由于我们是新环境，就免去全备主库这一步了。
1. 在主库db01创建专用复制用户
[root@db01 ~]# mysql -uroot -p123
grant replication slave on *.* to rs@'%' identified by '123';
2. 在两个从库db02,db03构建主从关系
分别在db02和db03节点，执行change master to语句，完事之后，查看主从状态是否正常：
CHANGE MASTER TO
MASTER_HOST='10.0.0.204',
MASTER_USER='rs',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;
start slave;
show slave status \G
[root@db02 ~]# mysql -uroot -p123
db02 [(none)]>show slave status \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@db03 ~]# mysql -uroot -p123
db03 [(none)]>show slave status \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
OK，主从环境构建成功。
GTID复制和普通复制的区别
在主从复制环境中，主库发生过的事务，在全局都是由唯一的GTID记录的，对于故障切换来说，更加方便。
在change master to时，不再需要binlog文件名和position号，而是根据主库的position自动识别读取。
在复制过程中，从库不再依赖master.info文件，而是直接读取最后一个relaylog的GITD号。
还有需要在配置文件中配置的三个参数：
gtid-mode=on # 启用gtid
enforce-gtid-consistency=true # 强制gtid一致性
log-slave-updates=1 # salve更新是否计入日志
注入空事务
如果遇到不小心对从库进行误写入操作，比如现在从库中创建一个数据库，然后再主库中创建一个同样的数据库，那么此时的主从复制就会报错。报错的大意是再重放事务时，发现(从库)已经存在了该数据库，那么这个事务也就无法执行。怎么解决呢？
MySQL提供了解决方式：
stop slave;
set gtid_next='bbcbb587-b0aa-11eb-a2ce-000c295ead38:1';
begin;commit;
set gtid_next='AUTOMATIC';
首先停掉主从复制；然后定位到报错的那个GITD位置，为当前GTID注入一个空事务，目的是为了让GTID不中断。
也可以理解为通过注入空事务，跳过报错的GTID，当然，最好的解决方案是重新构建主从环境。
来演示下相关过程，当然，此时主从环境是好的。
db02，首先在从库中创建了一个数据库：
[root@db02 ~]# mysql -uroot -p123
create database t1;
db01，接下来，在主库中也做了同样的操作：
[root@db01 ~]# mysql -uroot -p123
create database t1;
完事之后，你会发现db02的主从环故障了：
[root@db02 ~]# mysql -uroot -p123
db02 [(none)]>show slave status \G
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1007
Last_Error: Error 'Can't create database 't1'; database exists' on query. Default database: 't1'. Query: 'create database t1'
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1007
Last_SQL_Error: Error 'Can't create database 't1'; database exists' on query. Default database: 't1'. Query: 'create database t1'
Retrieved_Gtid_Set: bbcbb587-b0aa-11eb-a2ce-000c295ead38:1,
ce532bca-b1ad-11eb-a297-000c298ffc47:1
Executed_Gtid_Set: bbcbb587-b0aa-11eb-a2ce-000c295ead38:1,
ef2baccd-b1ad-11eb-a0fb-000c294bb55c:1
Auto_Position: 1
这个参数需要引起注意：
Retrieved_Gtid_Set: bbcbb587-b0aa-11eb-a2ce-000c295ead38:1,
ce532bca-b1ad-11eb-a297-000c298ffc47:1
Executed_Gtid_Set: bbcbb587-b0aa-11eb-a2ce-000c295ead38:1,
ef2baccd-b1ad-11eb-a0fb-000c294bb55c:1
# 上面的参数指的是请求的ce532bca-b1ad-11eb-a297-000c298ffc47:1，故障了，它跟主库的gtid一致
db01 [(none)]>show binlog events in 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 204 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| mysql-bin.000002 | 123 | Previous_gtids | 204 | 194 | bbcbb587-b0aa-11eb-a2ce-000c295ead38:1 |
| mysql-bin.000002 | 194 | Gtid | 204 | 259 | SET @@SESSION.GTID_NEXT= 'ce532bca-b1ad-11eb-a297-000c298ffc47:1' |
| mysql-bin.000002 | 259 | Query | 204 | 347 | create database t1 |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
4 rows in set (0.00 sec)
按照MySQL提供的解决办法，就是为当前这个执行失败的GTID注入一个空事务，目的是跳过当前GITD，但又不至于让GTID中断：
stop slave;
set gtid_next='ce532bca-b1ad-11eb-a297-000c298ffc47:1';
begin;commit;
set gtid_next='AUTOMATIC';
start slave;
show slave status \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
ok了。
但这么做是存在一些安全隐患的，最好的办法还是重构主从环境。
我这里将db02的从库恢复如初。
stop slave;
drop database t1;
reset master;
reset slave all;
CHANGE MASTER TO
MASTER_HOST='10.0.0.204',
MASTER_USER='rs',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;
start slave;
show slave status \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
show databases; -- 现在新创建的t1库就是从新从主库同步过来的。
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| t1 |
+--------------------+
5 rows in set (0.00 sec)
由于我这里的主库中没啥数据，所以，可以直接这么操作，但如果主库的数据量特别大的话，就需要先做个全备后再构建主从了。
that's all,see also: 老男孩-标杆班级-MySQL-lesson08-主从复制基础 | MySQL半同步复制 | 老男孩-标杆班级-MySQL-lesson9--主从复制高级进阶
