09MySQL主从复制

09MySQL主从复制

Deng YongJie's blog 313 2022-04-24

第1章 主从复制介绍

1.介绍

MySQL数据库的主从复制技术与使用scp/rsync等命令进行的异机文件级别复制类似,都是数据的远程传输.
只不过MySQL的主从复制技术是其软件自身携带的功能,无须借助第三方工具.
MySQL的主从复制并不是直接复制数据库磁盘上的文件,而是将逻辑的记录数据库更新的binlog日志发送到需要同步的数据库服务器本地,然后再由本地的数据库线程读取日志中的SQL语句并重新应用到MySQL数据库中,从而即可实现数据库的主从复制。

2.应用场景

1.从服务器作为主服务器的实时数据备份
2.主从服务器实现读写分离,从服务器实现负载均衡
3.根据业务重要性对多个服务器进行拆分

第2章 主从复制搭建部署

1.安装部署mysql实例

安装过程略,此处只给出3台实例的配置文件
# db-01配置
cat > /etc/my.cnf<<EOF
[mysqld]
user=mysql
datadir=/data/mysql_3306
basedir=/opt/mysql/
socket=/tmp/mysql.sock
port=3306
log_error=/var/log/mysql/mysql.err
server_id=51
log_bin=/binlog/mysql-bin

[mysql]
socket=/tmp/mysql.sock

[client]
socket=/tmp/mysql.sock
EOF

# db-52配置
cat > /etc/my.cnf<<EOF
[mysqld]
user=mysql
datadir=/data/mysql_3306
basedir=/opt/mysql/
socket=/tmp/mysql.sock
port=3306
log_error=/var/log/mysql/mysql.err
server_id=52

[mysql]
socket=/tmp/mysql.sock

[client]
socket=/tmp/mysql.sock
EOF

# db-53配置
cat > /etc/my.cnf<<EOF
[mysqld]
user=mysql
datadir=/data/mysql_3306
basedir=/opt/mysql/
socket=/tmp/mysql.sock
port=3306
log_error=/var/log/mysql/mysql.err
server_id=53

[mysql]
socket=/tmp/mysql.sock

[client]
socket=/tmp/mysql.sock
EOF

# 初始化
mysqld --initialize-insecure --user=mysql --basedir=/opt/mysql --datadir=/data/mysql_3306/

2.主库操作

2.1 创建复制用户

mysql -uroot -p123 -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123';"
mysql -uroot -p123 -e "select user,host,plugin from mysql.user;"

2.2 备份数据并发送到从库

mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M > /data/full.sql
scp /data/full.sql 10.0.0.52:/tmp/
scp /data/full.sql 10.0.0.53:/tmp/

3.从库操作

3.1 查看从库位置点

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=444;

3.2 导入主库数据

mysql -uroot -p123 < /tmp/full.sql

3.3 配置主从同步信息

CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;

3.4 启动线程

start slave;

4.查看复制状态

mysql -uroot -p123 -e "show slave status\G"|grep "Running:"

第3章 主从复制原理

1.涉及到的线程

1.1 主库

线程说明:

binlog_dump_thread
负责接收slave请求和传送主库binlog给slave

查看命令:

mysql> show processlist;
+----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host            | db   | Command     | Time | State                                                         | Info             |
+----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+
| 32 | repl | 10.0.0.53:47726 | NULL | Binlog Dump |  284 | Master has sent all binlog to slave; waiting for more updates | NULL             |
| 33 | repl | 10.0.0.52:55250 | NULL | Binlog Dump |  280 | Master has sent all binlog to slave; waiting for more updates | NULL             |
| 34 | root | localhost       | NULL | Query       |    0 | starting                                                      | show processlist |
+----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+

1.2 从库

线程说明:

IO线程 :
连接主库DUMP线程,请求Master日志、接收Master日志、存储日志(relay-log)。

SQL线程 
回放relaylog

查看命令:

[root@db-52 ~]# mysql -uroot -p123 -e "show slave status\G"|grep "Running:"
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

2.涉及到的文件

2.1 主库

binlog日志文件

2.2 从库

relay-log 中继日志

命名方式: 
datadir/HOSTNAME-relay-bin.00000N

作用: 
存储获取到的binlog 

主库信息文件

命名方式:  
datadir/master.info
作用: 
记录主库ip  port  user  password  binlog位置点等信息。

中继日志应用信息

命名方式: 
relay-log.info

作用: 
记录SQL 线程回放到的位置点信息。

3.主从复制原理!

从库: Change master to IP,Port,USER,PASSWORD,binlog位置信息写入到M.info中,执行Start slave(启动SQL,IO)。
从库: 连接主库。
主库: 分配Dump_T,专门和S_IO通信。show processlist;
从库:  IO线程:IO线程请求新日志
主库:  DUMP_T 接收请求,截取日志,返回给S_IO
从库: IO线程接收到binlog,此时网络层层面返回ACK给主库。主库工作完成。
从库: IO将binlog最终写入到relaylog中,并更新M.info。IO线程工作结束。
从库:  SQL线程读R.info,获取上次执行到的位置点
从库: SQL线程向后执行新的relay-log,再次更新R.info。 

画图:

第4章 主从复制监控

1.主库状态

查看复制线程:

[root@db-51 ~]# mysql -uroot -p123 -e "show processlist" |grep "Dump"
mysql: [Warning] Using a password on the command line interface can be insecure.
32      repl    10.0.0.53:47726 NULL    Binlog Dump     568     Master has sent all binlog to slave; waiting for more updates   NULL
33      repl    10.0.0.52:55250 NULL    Binlog Dump     564     Master has sent all binlog to slave; waiting for more updates   NULL

查看复制节点信息:

[root@db-51 ~]# mysql -uroot -p123 -e "show slave hosts;"                      
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|        53 |      | 3306 |        51 | 0f61194b-f733-11ea-8ca6-000c29c20a5d |
|        52 |      | 3306 |        51 | 2fbe6b47-f731-11ea-9d25-000c29605eb5 |
+-----------+------+------+-----------+--------------------------------------+

2.从库状态

查看主从状态:

mysql -uroot -p123  -e "show slave status \G"

主库连接信息、binlog位置信息

Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Read_Master_Log_Pos: 444
Relay_Master_Log_File: mysql-bin.000001

从库中relay-log的回放信息

Relay_Log_File: db-52-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Exec_Master_Log_Pos: 444

线程监控信息:主要用来排查主从故障-重点监控

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

3.位置点信息

IO 已经获取到的主库Binlog的位置点

Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 444
作用: IO下次请求日志时,起点位置。

SQL 回放到的relaylog位置点

Relay_Log_File: db01-relay-bin.000006
Relay_Log_Pos: 320

SQL回放的realylog位置点,对应的主库binlog的位置点

Relay_Master_Log_File: mysql-bin.000001
Exec_Master_Log_Pos: 600
作用: 计算主从复制延时日志量。

第5章 主从复制故障

1.如何监控

Slave_IO_Running: Yes        # IO线程工作状态: YES、NO、Connecting  
Slave_SQL_Running: Yes       # SQL线程工作状态:YES、NO
Last_IO_Errno: 0             # IO故障代码:2003,1045,1040,1593,1236
Last_IO_Error:               # IO线程报错详细信息  
Last_SQL_Errno: 0            # SQL故障代码:  1008,1007
Last_SQL_Error:              # IO线程报错详细信息

2.IO线程故障

2.1 正常状态

Slave_IO_Running: Yes

2.2 不正常状态

NO
Connecting

2.3 故障原因

1.网络,端口,防火墙
2.用户 ,密码,授权
  replication slave
3.主库连接数上限
  mysql> select @@max_connections;
4.版本不统一  5.7 native  , 8.0 sha2

2.4 模拟故障

主库操作

mysql> start slave; # 启动所有线程
mysql> stop slave;  # 关闭所有线程
mysql> start slave sql_thread; #单独启动SQL线程
mysql> start slave io_thread; #单独启动IO线程
mysql> stop  slave sql_thread;
mysql> stop  slave io_thread;

解除从库身份:
mysql> reset slave all;
mysql> show slave status \G

从库操作

stop slave; 
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.000003',
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10;
start slave;

2.5 解决思路

1.网络是否互通
2.确定复制账号授权是否正确
3.主从的server_id是否相同
4.主从的server_uuid是否相同

3.SQL线程故障

3.1 SQL线程主要工作

回放relay-log中的日志。可以理解为执行relay-log SQL

3.2 故障本质

为什么SQL线程执行不了SQL语句

3.3 故障原因

创建的对象已经存在
需要操作的对象不存在
约束冲突。
以上问题: 大几率出现在从库写入或者双主结构中容易出现。

3.4 故障模拟

(1)先在从库 create database oldguo charset=utf8;
(2)在主库  create database oldguo charset=utf8mb4;
(3)检查从库SQL线程状态
Slave_SQL_Running: No
Last_Error: Error 'Can't create database 'oldguo'; database exists' on query. Default database: 'oldguo'. Query: 'create database oldguo'

3.5 故障处理

思路1: 一切以主库为准

在从库上进行反操作一下。重启线程
mysql> drop database oldguo;
mysql> start slave;

思路2: 以从库为准,跳过此次复制错误,不建议

binlog跳过:
stop slave; 
set global sql_slave_skip_counter = 1;

GTID跳过:
stop slave; 
SET @@SESSION.GTID_NEXT= '8f9e146f-0a18-11e7-810a-0050568833c8:4'

#将同步指针向下移动一个,如果多次不同步,可以重复操作。
start slave;

思路3: 暴力方法,遇到自动跳过,不建议。

/etc/my.cnf
slave-skip-errors = 1032,1062,1007

常见错误代码:
1007:对象已存在
1032:无法执行DML
1062:主键冲突,或约束冲突

思路4: 重新搭建主从

备份恢复 + 重新构建

第7章 过滤复制

1.过滤复制介绍

从节点仅仅复制指定的数据库,或指定数据库的指定数据表

2.主库实现

binlog_do_db      白名单
binlog_ignore_db  黑名单
通过是否记录binlog日志来控制过滤

3.从库实现

实现方法:

IO线程不做限制。
SQL线程回放时,选择性回放。

配置参数:

replicate_do_db=world   #从库白名单
replicate_do_db=oldboy     
replicate_ignore_db=    #从库黑名单

replicate_do_table=world.city 
replicate_ignore_table= 

replicate_wild_do_table=world.t*
replicate_wild_ignore_table=

配置方法1: 修改配置文件

replicate_do_db=world
replicate_do_db=oldboy

配置方法2: 在线热配置

STOP SLAVE SQL_THREAD;
CHANGE REPLICATION FILTER REPLICATE_DO_DB = (old, oldjie);
START  SLAVE SQL_THREAD;

第8章 延时从库的应用

1.延时从库介绍

控制从库的SQL线程执行速度,二进制日志照常去主库取,但是存放到中继日志之后就延迟执行。
如果主库被误操作,这时候对中继日志进行处理,就不用根据全备二进制日志恢复,节省了大部分的时间

2.配置方法

stop slave;
CHANGE MASTER TO MASTER_DELAY = 300;
start slave;

3.查看状态

mysql> show slave status \G
SQL_Delay: 300
SQL_Remaining_Delay: NULL

3.故障处理流程

1. 及时监控故障: 主库 10:05发现故障,从库此时8:05数据状态
2. 立即将从库的SQL线程关闭。 需要对A业务挂维护页。
3. 停止所有线程。
4. 在延时从。恢复A库数据
   手工模拟SQL线程工作,找到drop之前位置点。
   SQL线程上次执行到的位置 ----> drop之前
   relay.info ----> 分析drop位置点 ----> 截取relaylog日志 ----> source

4.故障模拟及恢复

主库操作:

create database yongjie charset utf8mb4;
use yongjie;
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;

drop database yongjie;

从库操作:

stop slave sql_thread;
show slave status \G;

截取日志:

起点:SQL上次执行到的位置点,

Relay_Log_File: db-52-relay-bin.000002
Relay_Log_Pos: 320

终点:drop 之前

mysql> show relaylog events in 'db-52-relay-bin.000002';
....略
| db-52-relay-bin.000002 | 985 | Query          |        51 |        1201 | drop database json  

截取日志:

mysqlbinlog --start-position=320 --stop-position=985 /data/mysql_3306/db-52-relay-bin.000002 >/tmp/bin.sql

从库恢复操作:

stop slave;
reset slave all;
set sql_log_bin=0;
source /tmp/bin.sql;
set sql_log_bin=1;

第9章 GTID复制

1.GITD复制介绍

功能:主从之间自动校验GTID一致性: 主库binlog,从库binlog ,relay-log 

没有备份:
自动从主库的第一个gtid对应的pos号开始复制

有备份:    
SET @@GLOBAL.GTID_PURGED='2386f449-98a0-11ea-993c-000c298e182d:1-10';
从库会自动从第11个gtid开始复制。

2.清理环境

pkill mysqld  
rm -rf /data/mysql_3306/* 
rm -rf /binlog/* 
mkdir /binlog/

3.准备配置文件

db01配置

cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
datadir=/data/mysql_3306
basedir=/opt/mysql/
socket=/tmp/mysql.sock
port=3306
log_error=/var/log/mysql/mysql.err
server_id=51
log_bin=/binlog/mysql-bin
autocommit=0
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1

[mysql]
socket=/tmp/mysql.sock

[client]
socket=/tmp/mysql.sock
EOF

db02配置

cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
datadir=/data/mysql_3306
basedir=/opt/mysql/
socket=/tmp/mysql.sock
port=3306
log_error=/var/log/mysql/mysql.err
server_id=52
autocommit=0
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1

[mysql]
socket=/tmp/mysql.sock

[client]
socket=/tmp/mysql.sock
EOF

4.初始化数据

mysqld --initialize-insecure --user=mysql --basedir=/opt/mysql --datadir=/data/mysql_3306/

5.启动数据库

/etc/init.d/mysqld start

6.创建用户

grant replication slave on *.* to repl@'10.0.0.%' identified by '123';

7.构建主从

52和53操作:

change master to 
master_host='10.0.0.51',
master_user='repl',
master_password='123' ,
MASTER_AUTO_POSITION=1;
start slave;

第10章 主从延时问题的原因和处理

1.什么是主从延时

主库发生了操作,从库'很久'才跟上来,甚至一直追不上

2.如何监控主从延时

粗略估计:

show slave status \G
Seconds_Behind_Master: 0

准确计算:

日志量: 
主库binlog位置点
从relay执行的位置点

3.如何计算延时的日志量

show master status;
cat /data/3308/data/relay-log.info 

4.主从延时的原因

4.1 主库可能的原因

外部原因: 
网络,硬件配置,主库业务繁忙,从库太多
	 
主库业务繁忙 : 
1. 拆分业务(分布式): 组件分离  ,垂直  , 水平 
2. 大事务的拆分 。比如,1000w 业务  拆分为 20次执行。 

内部 : 
1. 二进制日志更新问题:
解决方案:
sync_binlog=1

2. 问题: 5.7之前的版本,没有开GTID之前,主库可以并发事务,但是dump传输时是串行。
所以会导致,事务量,大事务时会出现比较严重延时。
解决方案: 
5.6+ 版本,手工开启gtid,事务在主从的全局范围内就有了唯一性标志。
5.7+ 版本,无需手工开启,系统会自动生成匿名的GTID信息
有了GTID之后,就可以实现并发传输binlog。
但是,即使有这么多的优秀特性,我们依然需要尽可能的减少大事务,以及锁影响。

4.2 从库可能的原因

外部  :  
网络,从库配置低,参数设定。   

内部  : 
IO线程: 
写relay-log  --> IO 性能。

SQL线程: 
回放 SQL 默认在非GTID模式下是串行的

解决方案:	
1. 开启GTID