MySQL之主从复制

1. 介绍

• 两台或以上数据库实例

• 通过binlog二进制日志，实现数据的“同步”关系

前提：

• 两台以上的mysql实例，server_id、server_uuid不同
• 主库开启binlog
• 需要专用的复制用户
• 保证主从开启之前的某个时间点，从库数据和主库数据一致(补课)
• 从库需知道复制的user、password、port、复制起点
• 从库开启专用的复制线程

2. 架构搭建

2.1 实例准备

实例	属性	server_id	server_uuid
mysqld3307	主库	7	92cb138b-ac82-11ea-a6d8-000c29c8dc7e
mysqld3308	从库	8	9404d395-ac82-11ea-a67a-000c29c8dc7e
mysqld3309	从库	9	95a58207-ac82-11ea-a89a-000c29c8dc7e

2.2 主库准备

-- 确认binlog开启状态
mysql> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

-- 创建复制用户并授权
mysql> grant replication slave on *.* to repl@'%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> select user,host from mysql.user;
+---------------+---------------+
| user          | host          |
+---------------+---------------+
| repl          | %             |
| mysql.session | localhost     |
| mysql.sys     | localhost     |
| root          | localhost     |
+---------------+---------------+
4 rows in set (0.00 sec)

-- 导出全备给从库
[root@db01 ~]# mysqldump -S /data/3307/mysql.sock -A --master-data=2 --single-transaction > /tmp/all.sql

2.3 从库准备

-- 将备份恢复进来
mysql> source /tmp/all.sql;

-- 查看配置主库所需信息
mysql> help change master to
CHANGE MASTER TO
  MASTER_HOST='',
  MASTER_USER='',
  MASTER_PASSWORD='',
  MASTER_PORT=,
  MASTER_LOG_FILE='',
  MASTER_LOG_POS=,
  MASTER_CONNECT_RETRY=;

-- MASTER_LOG可以通过下面命令查看
[root@db01 ~]# grep "\-- CHANGE MASTER TO" /tmp/all.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=449;

-- 配置主库
CHANGE MASTER TO
  MASTER_HOST='192.168.159.51',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000002',
  MASTER_LOG_POS=449,
  MASTER_CONNECT_RETRY=10;
  
-- 开启专用复制线程
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

-- 检查从库状态
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status\G" | grep Running:
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[root@db01 ~]# mysql -S /data/3309/mysql.sock -e "show slave status\G" | grep Running:
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

-- 若连接不成功，可以使用一下命令重置从库状态重做
mysql> stop slave;
mysql> reset slave all;

3. 主从复制原理

3.1 相关文件

• 主库：binlog文件
• 从库

relay-bin.00000x
(1) 作用：存储接收的binlog，中继日志，默认存放在数据目录下
(2) 相关参数：
	mysql> select @@relay_log_basename;
    +--------------------------------+
    | @@relay_log_basename           |
    +--------------------------------+
    | /data/3308/data/db01-relay-bin |
    +--------------------------------+
    1 row in set (0.00 sec)

master.info
(1) 作用：记录连接主库的信息及已经接收binlog的位置点信息，默认存放在数据目录下
(2) 相关参数：FILE为生成一个文件存放在存储设备，TABLE为生成一个表放在数据库中
	mysql> select @@master_info_repository;
    +--------------------------+
    | @@master_info_repository |
    +--------------------------+
    | FILE                     |
    +--------------------------+
    1 row in set (0.00 sec)

relay-log.info
(1) 作用：记录从库回放到的relay-log位置点，默认存放再数据目录下
(2) 相关参数：FILE为生成一个文件存放在存储设备，TABLE为生成一个表放在数据库中
	mysql> select @@relay_log_info_repository;
    +-----------------------------+
    | @@relay_log_info_repository |
    +-----------------------------+
    | FILE                        |
    +-----------------------------+
    1 row in set (0.00 sec)

3.2 相关线程

• 主库

Binlog Dump Thread
作用：用来接收从库请求，并且投递binlog给从库
mysql> show processlist;
+-----+------+------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id  | User | Host       | db   | Command     | Time | State                                                         | Info             |
+-----+------+------------+------+-------------+------+---------------------------------------------------------------+------------------+
| 245 | repl | db01:48494 | NULL | Binlog Dump | 2355 | Master has sent all binlog to slave; waiting for more updates | NULL             |
| 246 | repl | db01:48496 | NULL | Binlog Dump | 2331 | Master has sent all binlog to slave; waiting for more updates | NULL             |
| 247 | root | localhost  | NULL | Query       |    0 | starting                                                      | show processlist |
+-----+------+------------+------+-------------+------+---------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

• 从库

IO Thread
作用：请求binlog日志，接收binlog日志

SQL Thread
作用：回放relay日志

mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                  | Info             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 22 | system user |           | NULL | Connect | 2422 | Waiting for master to send event                       | NULL             |
| 23 | system user |           | NULL | Connect | 2294 | Slave has read all relay log; waiting for more updates | NULL             |
| 26 | root        | localhost | NULL | Query   |    0 | starting                                               | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status\G" | grep Running:
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

3.3 文字说明

1. 从库：CHANGE MASTER TO，将ip、port、user、password、binlog、position写入到master.info进行记录
2. 从库：START SLAVE，启动IO_THREAD和SQL_THREAD，
3. 从库：IO_THREAD读取master.info信息，连接主库
4. 主库：分配DUMP_THREAD线程响应从库，该连接为长连接
5. 从库：IO_THREAD根据master.info记录的binlog文件名和position号向主库请求最新日志
6. 主库：DUMP_THREAD检查binlog日志，如果有新的将截取日志返回给从库
7. 从库：IO_THREAD将发送来的binlog存储到TCP/IP缓存中，立即返回ACK给主库，主库工作完成
8. 从库：IO_THREAD将缓存中的数据刷写到relay-log中，同时更新master.info文件中的binlog文件名和position，IO_THREAD工作完成
9. 从库：SQL_THREAD读取relay-log.info获取上次指定到的relay-log位置作为起点，回放relay-log
10. 从库：SQL_THREAD回放完成后，更新relay-log.info文件

* 若 `relay_log_purge` 功能开启，SQL_THREAD将定期删除应用过的relay-log；DUMP_THREAD回实时监控主库中的binlog变化，若有新变化，则会发信息通知从库

4. 主从状态查看

4.1 主库查看

mysql> show processlist;
+-----+------+------------+------+-------------+-------+---------------------------------------------------------------+------------------+
| Id  | User | Host       | db   | Command     | Time  | State                                                         | Info             |
+-----+------+------------+------+-------------+-------+---------------------------------------------------------------+------------------+
| 245 | repl | db01:48494 | NULL | Binlog Dump | 10108 | Master has sent all binlog to slave; waiting for more updates | NULL             |
| 246 | repl | db01:48496 | NULL | Binlog Dump | 10084 | Master has sent all binlog to slave; waiting for more updates | NULL             |
| 247 | root | localhost  | NULL | Query       |     0 | starting                                                      | show processlist |
+-----+------+------------+------+-------------+-------+---------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         9 |      | 3309 |         7 | 95a58207-ac82-11ea-a89a-000c29c8dc7e |
|         8 |      | 3308 |         7 | 9404d395-ac82-11ea-a67a-000c29c8dc7e |
+-----------+------+------+-----------+--------------------------------------+
2 rows in set (0.00 sec)

4.2 从库查看

mysql> show slave status\G;
*************************** 1. row ***************************
-- IO_THREAD当前状态
Slave_IO_State: Waiting for master to send event

-- 主库相关信息，来自于master.info
Master_Host: 192.168.159.51
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1797
Master_Server_Id: 7
Master_UUID: 92cb138b-ac82-11ea-a6d8-000c29c8dc7e
Master_Info_File: /data/3308/data/master.info

-- 从库relay-log的执行情况，一半用于判断主从延时
Relay_Log_File: db01-relay-bin.000004
Relay_Log_Pos: 495
Relay_Master_Log_File: mysql-bin.000002
Exec_Master_Log_Pos: 1797
Seconds_Behind_Master: 0

-- 从库线程状态及具体报错信息
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: 
Replicate_Ignore_Server_Ids: 

-- 延时从库的配置信息
SQL_Delay: 0
SQL_Remaining_Delay: NULL

-- SQL_THREAD当前状态
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

-- GTID相关复制信息
Retrieved_Gtid_Set: 
Executed_Gtid_Set: 

5. 主从故障分析及处理

5.1 IO线程

5.1.1 正常状态

mysql> show slave status\G
Slave_IO_Running: Yes

5.1.2 连接故障

mysql> show slave status\G
Slave_IO_Running: Connecting
Last_IO_Errno: 
Last_IO_Error: 

通用故障处理思路：手工连接

• 用户名或密码或授权错误

[root@db01 ~]# mysql -urepl1 -p123 -h192.168.159.51 -P3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'repl1'@'db01' (using password: YES)
[root@db01 ~]# mysql -urepl -p123456 -h192.168.159.51 -P3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'repl'@'db01' (using password: YES)

• 主机错误

[root@db01 ~]# mysql -urepl -p123 -h192.168.159.50 -P3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.159.50' (113)

• 端口错误

[root@db01 ~]# mysql -urepl -p123 -h192.168.159.51 -P3306
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.159.51' (111)

• 主机连接数超出

-- 修改主库的最大连接数
mysql> set global max_connections=3;
Query OK, 0 rows affected (0.00 sec)

-- 开启多个会话测试
[root@db01 ~]# mysql -urepl -p123 -h192.168.159.51 -P3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1040 (HY000): Too many connections

• 防火墙策略
• 数据库版本不一致

故障修复过程：

-- 停止主从线程
mysql> stop slave;

-- 清除原来的主库连接信息
mysql> reset slave all;

-- 重新设置主库连接信息
mysql> CHANGE MASTER TO
     > MASTER_HOST='',
     > MASTER_USER='',
     > MASTER_PASSWORD='',
     > MASTER_PORT=,
     > MASTER_LOG_FILE='',
     > MASTER_LOG_POS=,
     > MASTER_CONNECT_RETRY=10;

-- 开启主从线程
mysql> start slave;

5.1.3 日志故障

mysql> show slave status\G
Slave_IO_Running: No
Last_IO_Errno: 
Last_IO_Error: 

• 主库binlog不完整(损坏、不连续等)

-- 主库重置binlog
mysql> reset master;

-- 从库必宕机，此时只能重新搭建主从恢复
mysql> show slave status\G
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.000002' at 1622, the last event read from '/binlog/3307/mysql-bin.000002' at 1797, the last byte read from '/binlog/3307/mysql-bin.000002' at 1797.'

生成中如果需要重置binlog：
1. 找业务不繁忙期间，停止业务5分钟(防止在重置过程中有变更的数据导致从库找不到新的binlog起始点)
2. 等待从库重放完完所有主库日志
3. 主库reset master
4. 从库重置主库连接设置重新同步binlog
5. 恢复业务

• 从库的binlog请求起点问题
• 主从的server_id(server_uuid)相同

5.2 SQL线程

5.2.1 正常状态

mysql> show slave status\G
Slave_SQL_Running: Yes

5.2.2 故障状态

mysql> show slave status\G
Slave_SQL_Running: No

SQL线程的故障大概率出现在直接对从库增删改或双主架构中

• 创建的对象已经存在
• 需要操作的对象不存在
• 约束冲突

故障修复过程：

• 思路一：以主库为准，将从库反操作，重启线程

-- 如删除已存在的库
mysql> drop database awei;
-- 重启线程
mysql> start slave;
-- 或单独启动SQL线程
mysql> start slave sql_thread;

• 思路二：以从库为准(已知此时从库数据状态与主库其实是相同的，有风险)，跳过此次复制错误

方法一：将同步指针向下移动一个，如果多次不同步，可以反复操作
-- 若使用此种方法，一定要保证此时故障数据是完全一致的才能采用
mysql> stop slave;
mysql> set global sql_slave_skip_counter=1;
mysql> start slave;

方法二：自动跳过指定类型错误(暴力，危险)
-- 修改配置文件
vim /etc/my.cnf
slave-skip-errors=1032,1062,1007
-- 1007：对象已存在
-- 1032：无法执行DML
-- 1062：主键冲突或约束冲突

• 思路三：重新搭建主从

扩展：从库只读

-- 普通用户只读
mysql> select @@read_only;
-- 普通管理员只读
mysql> select @@super_read_only;

5.3 主库故障

物理故障：
1. 看主库能否ssh上
2. 检查binlog是否完整
3. 手工追加日志到最新位置
4. 从库代替主库工作
	1. 修复到最新状态
	2. 取消从库身份
	3. 清空binlog日志信息，全备

6. 主从延时

6.1 介绍

主库发生了操作，从库“很久”才追上

6.2 主从延时判断

-- 粗略判断
-- 该参数根据binlog时间戳计算从主库binlog生成到IO线程请求接收落盘时间差
-- 主要记录的是网络层的时延，即使等于0也不代表不存在时延
mysql> show slave status\G
	Seconds_Behind_Master: 0

-- 精确判断：计算主库的binlog和从库relay-log延时日志量
-- 主库
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
-- 从库
[root@db01 ~]# cat /data/3308/data/relay-log.info 
7
./db01-relay-bin.000002
320
mysql-bin.000001
154
0
0
1

6.3 主从延时原因

6.3.1 主库方面

• 外部：

  • 网络
  • 硬件
  • 主库业务繁忙
    • 拆分业务(分布式)：组件分离、垂直拆分、水平拆分
    • 大事务的拆分
  • 从库太多

• 内部

  • binlog更新时机：sync_log_bin=1
  • 未开GTID时，主库可以并发事务，但从库需要按序执行日志，dump只能串行传输
  • 开启GTID后(5.7+默认开启，5.6+出现该功能)，主库可以并发事务，dump也可以并行传输(因为GTID是连续递增的)

• 判断主库传输不及时

  • Seconds_Behind_Master
  • show master status&show slave status\G查看binlog文件名及位置点

6.3.2 从库方面

• 外部：

  • 网络
  • 硬件(比主库低)

• 内部

  • IO线程

    • 写relay-log --> IO性能

  • SQL线程

    1. 5.6 版本开启GTID之后,加入了SQL多线程的特性,但是只能针对不同库(database)下的事务进行并发回放.
    2. 5.7 版本开始GTID之后,在SQL方面,提供了基于逻辑时钟(logical_clock),binlog加入了seq_no机制,
    真正实现了基于事务级别的并发回放,这种技术我们把它称之为MTS(enhanced multi-threaded slave).
    3. 大事务拆成多个小事务,可以有效的减少主从延时.