徒手搭建主从复制
1. 介绍
依赖于二进制日志的，“实时”备份的一个多节点架构。
2. 主从复制的前提（搭建主从复制）
2.1 至少两个实例
2.2 不同的server_id
2.3 主库需要开启二进制日志
2.4 主库需要授权一个专用复制用户
2.5 主库数据备份
2.6 开启专用复制线程
3. 搭建主从复制
3.1 启动多实例
多实例的环境准备请参考Day02文章
systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3309
3.2 检查server_id
mysql -S /data/3307/mysql.sock -e "select @@server_id;"
mysql -S /data/3308/mysql.sock -e "select @@server_id;"
mysql -S /data/3309/mysql.sock -e "select @@server_id;"
3.3 检查3307（主库）的二进制日志情况
[root@db01 /data]#
mysql -S /data/3307/mysql.sock -e "show variables like '%log_bin%';"
+---------------------------------+----------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------+
| log_bin | ON |
| log_bin_basename | /data/3307/mysql-bin |
| log_bin_index | /data/3307/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+----------------------------+
3.4 主库创建复制用户
[root@db01 ~]# mysql -S /data/3307/mysql.sock
mysql> grant replication slave on *.* to repl@'10.0.0.%' identified by '123';
3.5 进行主库数据备份
[root@db01 ~]# mysqldump -S /data/3307/mysql.sock -A --master-data=2 -R -E --triggers --single-transaction >/tmp/full.sql
3.6 恢复数据到从库(3308)
[root@db01 /data]# mysql -S /data/3308/mysql.sock
mysql> set sql_log_bin=0;
mysql> source /tmp/full.sql;
3.7 告知从库复制的信息
mysql> help change master to
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;
#然后运行
3.8 启动复制线程
mysql> start slave;
3.9 如果 change master to 信息输入错误，怎么办？
#删除主从信息
mysql> stop slave;
mysql> reset slave all;
#重新执行 3.7
3.10 查看主从信息
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 444
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 320
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: 444
Relay_Log_Space: 526
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: 01cce940-8e8f-11e9-badd-000c29c21dbb
Master_Info_File: /data/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)
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
4. 主从复制工作过程
4.1 名词认识
文件
主库：binlog
从库：
relay-log 中继日志
master.info 主库信息文件
relay-log.info 中继日志应用信息
线程
主库： binlog_dump_thread 二进制日志投递线程
[root@db01 /data]#
mysql -S /data/3307/mysql.sock -e "show processlist"
+----+------+------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+------------+------+-------------+------+---------------------------------------------------------------+------------------+
| 7 | repl | db01:39534 | NULL | Binlog Dump | 1894 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 8 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+------------+------+-------------+------+---------------------------------------------------------------+------------------+
从库：
IO_Thread 从库IO线程，请求和接受binlog
SQL_Thread 从库的SQL线程，回放日志
4.2 工作原理
image.png
（1）从库执行 change master to 语句，会立即将主库信息（ip，port，user，password等）记录到master.info中。
（2）从库执行 start slave 语句，会立即生成IO_T和SQL_T。
（3）IO_T 读取 master.info 文件，获取主库信息
（4）IO_T 连接主库，主库会立即分配一个 DUMP_T，进行交互
（5）IO_T 根据 master.info 的二进制日志信息，向主库的DUMP_T进行请求最新的binlog
（6）主库DUMP_T 经过查询，如果发现有新的，截取并返回给从库的IO_T
（7）从库IO_T会收到binlog，存储在TCP/IP缓存中，在网络底层返回ACK
（8）从库IO_T会更新master.info，重置binlog位置点信息
（9）从库IO_T会将binlog写入到relay-log中
（10）从库SQL_T，读取relay-log.info文件，获取上次执行过的位置点
（11）SQL_T按照位置点往下执行relay-log日志
（12）执行完成后，重新更新relay-log.info
（13）relay-log定期自动清理的功能
细节：
主库发生了新的信息修改，更新二进制日志完成后，会发送一个“信号”给Dump_T，Dump_T会通知给IO_T线程。
5. 主从复制监控及故障处理
5.1 主从监控
主库：
mysql> mysql> show processlist;
+----+------+------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+------------+------+-------------+------+---------------------------------------------------------------+------------------+
| 7 | repl | db01:39534 | NULL | Binlog Dump | 5831 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 9 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+------------+------+-------------+------+---------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
从库：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 444
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 320
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: 444
Relay_Log_Space: 526
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: 01cce940-8e8f-11e9-badd-000c29c21dbb
Master_Info_File: /data/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.info
Master_Host: 10.0.0.51 #主库的IP
Master_User: repl #复制用户名
Master_Port: 3307 #主库的端口
Connect_Retry: 10 #断连之后重试次数
Master_Log_File: mysql-bin.000002 #已经获取到的binlog的文件名
Read_Master_Log_Pos: 444 #已经获取到的binlog的位置号
从库的relaylog的信息：relay-log.info
Relay_Log_File: db01-relay-bin.000002
#从库已经运行过的relaylog的文件名
Relay_Log_Pos: 320
#从库已经运行过的relaylog的位置号
从库复制线程工作状态：
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:
从库延时主库的时间：
Seconds_Behind_Master: 0 #从库延时主库的时间（秒为单位）
从库线程报错详细信息：
Last_IO_Errno: 0 #IO报错的号码
Last_IO_Error: #IO报错的具体信息
Last_SQL_Errno: 0 #SQL报错的号码
Last_SQL_Error: #SQL线程报错的具体原因
延时从库
SQL_Delay: 0 #延时从库设定的时间，防止误操作
SQL_Remaining_Delay: NULL #延时操作的剩余时间
GTID复制信息
Retrieved_Gtid_Set: #接收到的GTID的个数
Executed_Gtid_Set: #执行的GTID的个数
5.2 主从故障的分析及处理
查看相关的状态：
从库复制线程的工作状态
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
从库线程报错详细信息：
Last_IO_Errno: 0 #IO报错的号码
Last_IO_Error: #IO报错的具体信息
Last_SQL_Errno: 0 #SQL报错的号码
Last_SQL_Error: #SQL线程报错的具体原因
5.2.1 IO线程故障
（1）连接主库连接不上
connecting 相关报错信息
###### 原因：
网络不通
防火墙
IP不对
port不对
用户不对
密码不对
skip_name_resolve
连接数上限
##### 处理思路：
手工连接查看报错信息：
mysql -urepl -pxxx -h10.0.0.51 -P3307
如何处理？
stop slave;
reset slave all;
change master to
start slave;
（2）请求新的binlog
IO 线程 No 的状态分析
原因一：日志名不对
从库信息：
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 444
对比备份的位置号。
原因二：日志损坏，日志不连续
演示：
主库：
mysql -S /data/3307/mysql.sock
mysql> flush logs;
mysql> flush logs;
mysql> flush logs;
mysql> flush logs;
mysql> flush logs;
mysql> reset master;
mysql -S /data/3307/mysql.sock
mysql> create database dd;
mysql> create database dd1;
mysql> create database dd2;
从库处理：
mysql -S /data/3308/mysql.sock
mysql> stop slave;
mysql> reset slave all ;
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10;
start slave;
（3）写relaylog
（4）更新master.info
（5）server_id重复
5.2.2 SQL线程故障
原因一：
读 relay-log.info
读 relay-log，并执行日志
更新 relay-log.info
以上文件损坏，最好是重新构建主从
原因二：
为什么一条SQL语句执行不成功？
1. 主从数据库版本差异较大
2. 主从数据库配置参数不一致（例如：sql_mode等）
3. 想要创建的对象已经存在
4. 想要删除或修改的对象不存在
5. 主键冲突
6. DML语句不符合表定义及约束时
归根结底是从库进行了写入。
解决方法一：（不推荐使用）
stop slave;
set global sql_slave_skip_counter = 1;
/etc/my.cnf
slave-skip-errors = 1032,1062,1007
解决方法二：万全的解决
1. 做成从库只读
2. 通过中间件，做成读写分离的架构
6. 主从延时原因分析
从库延时主库的时间：
Seconds_Behind_Master: 0 #从库延时主库的时间（秒为单位）
6.1 主库方面：
日志写入不及时
mysql> select @@sync_binlog;
+---------------+
| @@sync_binlog |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
主库并发业务较高
"分布式" 架构
从库太多
级联主从
对于Classic Replication：
主库是有能力并发运行事务的，但是在Dump_T传输日志的时候，是以事件为单元传输日志的，
所以导致事务的传输工作是串行方式的，这时在主库TPS很高时，会产生比较大的主从延时。
怎么处理：
group commit
从 5.6 开始加入了GTID，在复制时，可以将原来串行的传输模式变成并行的。
除了GTID支持，还需要双一保证。
6.2 从库方面
Classic Replication
SQL 线程只有一个，所以说只能串行执行relay的事务。
怎么解决？
多加几个SQL线程
再5.6中出现了database级别的多线程SQL
只能针对不同库下的事务，才能并发
到5.7版本加入了MTS，才真正实现了事务级别的并发SQL线程
7. 延时从库
7.1 数据损坏
物理损坏
逻辑损坏
对于传统的主从复制，比较擅长处理物理损坏。
7.2 设计理念
对SQL线程进行延时设置
7.3 延时时间
生产环境一般是 3 - 6 小时
7.4 如何设置从库延时
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_DELAY = 300;
mysql> start slave;
mysql> show slave status \G
SQL_Delay: 300
SQL_Remaining_Delay: NULL
7.5 如何使用延时从库
7.5.1 思路
模拟故障：
mysql -S /data/3307/mysql.sock
create database delay charset utf8mb4;
use delay;
create table t1(id int);
insert into t1 values (1),(2),(3);
commit;
drop database delay;
发现问题：
1. 停止SQL线程，停止主库业务
2. 模拟SQL线程手工恢复relaylog到drop之前的位置点
3. 截取relaylog日志，找到起点（relay-log.info）和终点（drop 操作）
4. 恢复截取的日志，验证数据可用性
开始处理：
1. 停用从库的SQL线程
mysql -S /data/3308/mysql.sock
mysql> stop slave sql_thread;
2. 找 relaylog 的起点和终点
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 320
3. 截取日志
[root@db01 ~]#
mysqlbinlog --start-position=385 --stop-position=992 /data/3308/data/db01-relay-bin.000002 >/tmp/relay.sql
4. 恢复
mysql -S /data/3308/mysql.sock
set sql_log_bin=0;
source /tmp/relay.sql
网友评论