第8课 MySQL主从复制

第1章企业高可用性标准

1.1 全年无故障率(非计划内故障停机)

1
2
3

99.9%                 ----> 0.001*365*24*60=525.6  min
99.99%                ----> 0.0001*365*24*60=52.56 min
99.999%               ----> 0.0001*365*24*60=5.256 min

1.2 高可用架构方案

负载均衡:有一定的高可用性 
LVS  Nginx
主备系统:有高可用性,但是需要切换,是单活的架构
KA ,   MHA, MMM
真正高可用(多活系统): 
NDB Cluster  Oracle RAC  Sysbase cluster   , InnoDB Cluster（MGR）,PXC , MGC

第2章主从复制

2.1 简介

1.1. 基于二进制日志复制的
1.2. 主库的修改操作会记录二进制日志
1.3. 从库会请求新的二进制日志并回放,最终达到主从数据同步
1.4. 主从复制核心功能:
辅助备份,处理物理损坏                   
扩展新型的架构:高可用,高性能,分布式架构等

2.2 主从复制前提(搭建主从的过程)

## 2.1 两台以上mysql实例 ,server_id,server_uuid不同
## 2.2 主库开启二进制日志
## 2.3 专用的复制用户
## 2.4 保证主从开启之前的某个时间点,从库数据是和主库一致(补课)
## 2.5 告知从库,复制user,passwd,IP port,以及复制起点(change master to)
## 2.6 线程(三个):Dump thread  IO thread  SQL thread 开启(start slave)

第3章主从复制搭建

3.1 准备两个数据库实例(3307(主),3308(从))

略

3.2 修改主库my.cnf 开启二进制日志功能

[root@db01 ~]# vim /data/3307/my.cnf 
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3307/data
server_id=7
socket=/data/3307/mysql.sock
port=3307
log_bin=/data/3307/mysql-bin

3.3 启动所有节点

1 2	systemctl start mysqld3307 systemctl start mysqld3308

3.4 主库创建复制用户(3307 主库)

1 2	# mysql -S /data/3307/mysql.sock mysql> grant replication slave on . to repl@'10.0.1.%' identified by '123456';

3.5 备份主库数据恢复到从库

1 2	[root@db01 ~]# mysqldump -uroot -S /data/3307/mysql.sock -A --master-data=2 --single-transaction -R -E --triggers >/tmp/full.sql [root@db01 ~]# mysql -S /data/3308/mysql.sock </tmp/full.sql

3.6 配置从库

[root@db01 ~]# mysql -S /data/3308/mysql.sock 
CHANGE MASTER TO
  MASTER_HOST='10.0.1.51',
  MASTER_USER='repl',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154,
  MASTER_CONNECT_RETRY=10;

3.7 开启主从专用线程

1	start slave ;

3.8 检查状态

mysql> show slave status \G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

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

3.9 如果搭建报错,解决方法

stop slave ;
reset slave all;
change master to ...
start slave;

第4章主从复制的原理

4.1 主从中设置到的文件和线程

4.1.1 线程

主:
DUMP THREAD
从:
IO  THREAD
SQL THREAD

4.1.2 文件

主:
mysql-bin.000001
从: 
db01-relay.000001     ===>中继日志
master.info                 ===》主库信息记录日志
relay-log.info              ===> 记录中继应用情况信息

4.2 主从复制原理

主从复制原理描述：

(1) 从库 执行change master to语句 ,将以下信息保存至master.info文件中
(2) 从库 start slave , 从库IO_T和SQL_T生成
(3)Io_T ,读取master.info中主库连接信息(ip , port, user,password)
   连接主库,主库专门开启Dump_T,和从库IO_T进行交互
(5) IO_T,读取 master.info ,复制的起点信息,找主库DUMP要最新的binlog
	MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154
(6)主库dump_T,截取最新的binlog,发送给从库IO_T
(7)扩展: 基于TCP/IP网络工作模式,IO_T将日志存储到TCP/Ip缓存中,并返回ACK给主库.
(8) IO_T最终会将缓存中数据,写入到relay_log文件中保存.更新master.info为新位置点.
(9) SQL_T,读取relay-log.info信息,获取上次SQL_T回放的位置点.
(10)SQL_T,根据位置点,向下回放最新的relaylog.并且再次更新relay-log.info文件为最新点.
补充:
(11) 主库dump_T 会持续的监控binlog的变化,一旦有新的日志生成,给从库发一些信号.
(12) 从库 relay_log_purge线程会定期自动清理回放过的relay日志.
细节：
1.主库一旦有新的日志生成，会发送“信号”给binlog dump ，IO线程再请求

第5章主从故障监控\分析\处理

5.1 线程相关监控

5.1.1 主库信息监控(master.info)

mysql> show slave status \G
(1) 主库信息监控(master.info)
Master_Host: 10.0.1.51
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 154

5.1.2 从库relay-log的应用信息(relay-log.info)

(2) 从库relay-log的应用信息(relay-log.info)
Relay_Log_File: db01-relay-bin.000008
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000003

5.1.3 从库线程工作状态

(3) 从库线程工作状态
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error: 
Last_SQL_Errno: 0
Last_SQL_Error:

5.1.4 过滤复制有关信息

Replicate_Do_DB: 
Replicate_Ignore_DB: 
Replicate_Do_Table: 
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table:

5.1.5 主从延时时间

1	Seconds_Behind_Master: 0

5.1.6 延时从库的状态

1 2	SQL_Delay: 0 SQL_Remaining_Delay: NULL

5.1.7 GTID复制有关

1 2	Retrieved_Gtid_Set: Executed_Gtid_Set:

5.2 主从复制故障分析

5.2.1 IO线程故障

(1) 连接主库

(1) 连接主库 
网络不通,防火墙
连接信息错误(ip,port,user,password)

[root@db01 data]# mysql -urepl1 -p123 -h10.0.1.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 data]# mysql -urepl -p12311 -h10.0.1.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 data]# mysql -urepl -p123 -h10.0.1.55 -P3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.1.55' (113)
[root@db01 data]# mysql -urepl -p123 -h10.0.1.51 -P33011
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.1.51' (111)

解决方法: 
	stop slave;
	reset slave all; 
	重新change master to 
	start slave;

主库连接数上线,或者是主库太繁忙

show slave  staus \G 
Last_IO_Errno: 1040
Last_IO_Error: error reconnecting to master 'repl@10.0.0.51:3307' - retry-time: 10  retries: 7
处理思路:
拿复制用户,手工连接一下

[root@db01 ~]# mysql -urepl -p123 -h 10.0.0.51 -P 3307 
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1040 (HY000): Too many connections
处理方法:
db01 [(none)]>set global max_connections=300;

(3) 防火墙,网络不通

(2) 请求二进制日志 (最常见报错)

(2) 请求日志 (最常见报错)
主库日志损坏 ,丢失,不连续

#说明: 
在主从复制环境中,严令禁止主库中reset master; 可以选择expire 进行定期清理主库二进制日志
#解决方案:
最好的办法是重新构建主库.

5.2.2 SQL 线程故障

SQL线程功能：

1
2
3

(1)读写relay-log.info 
(2)relay-log损坏,断节,找不到
(3)接收到的SQL无法执行

导致SQL线程故障原因分析：

回放日志 (为什么SQL语句执行失败?)
1. 版本差异，参数设定不同，比如：数据类型的差异，SQL_MODE影响
2.要创建的数据库对象,已经存在
3.要删除或修改的对象不存在  
4.DML语句不符合表定义及约束时.  
归根揭底的原因都是由于从库发生了写入操作.
Last_SQL_Error: Error 'Can't create database 'db'; database exists' on query. Default database: 'db'. Query: 'create database db'

处理方法(以从库为核心的处理方案)：

方法一：
stop slave; 
set global sql_slave_skip_counter = 1;
#将同步指针向下移动一个，如果多次不同步，可以重复操作。
start slave;
方法二：
/etc/my.cnf
slave-skip-errors = 1032,1062,1007
常见错误代码:
1007:对象已存在
1032:无法执行DML
1062:主键冲突,或约束冲突

但是，以上操作有时是有风险的，最安全的做法就是重新构建主从。把握一个原则,一切以主库为主.

一劳永逸的方法:

处理建议: 
1. 一切以主库为准.最彻底方法重新构建主从。
2. 将从库设置为只读库,采用读写分离的中间件,防止从库写入
mysql> show variables like '%read_only%';
read_only=ON
super_read_only=ON

了解:
可以借助工具,校验和同步
pt-checksum 
pt-sync

第6章主从延时

6.1 监控

(1) 有没有延时 
show slave status \G 
Seconds_Behind_Master: 0
(2) 延时了多少日志量?
 1) 从库回放了多少日志?
relay-log.info   -----> 从库回放到了哪个点.
 2) 以上位置点对应着主库binlog的位置点.
./db01-relay-bin.000002
501
mysql-bin.000001
335
(3) 看主库的位置点
show master status;
(5) 找到哪一个事件
mysql-bin.000001
335

6.2 主从延时原因分析

1	主库做了修改操作,从库比较长时间才能追上.

6.2.1 外在因素

网络 
主从硬件差异较大
版本差异
参数因素
从库较多

6.2.2 主库

(1) 二进制日志写入不及时
[rep]>select @@sync_binlog;
(2) CR的主从复制中,binlog_dump线程,事件为单元,串行传送二进制日志(5.6 5.5)

1. 主库并发事务量大,主库可以并行,传送时是串行
2. 主库发生了大事务,由于是串行传送,会产生阻塞后续的事务.

解决方案:
1. 5.6 开始,开启GTID,实现了GC(group commit)机制,可以并行传输日志给从库IO
2. 5.7 开始,不开启GTID,会自动维护匿名的GTID,也能实现GC,我们建议还是认为开启GTID
3. 大事务拆成多个小事务,可以有效的减少主从延时.

6.2.3 从库

SQL线程导致的主从延时
在CR复制情况下: 从库默认情况下只有一个SQL,只能串行回放事务SQL
1. 主库如果并发事务量较大,从库只能串行回放
2. 主库发生了大事务,会阻塞后续的所有的事务的运行

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

第7章过滤复制

7.1 主库 (了解)

1
2
3

mysql> show master status;
binlog_do_db    白名单 
binlog_ignore_db        黑名单

7.2 从库

在SQL_T回放日志,选择部分日志回放
replicate_do_db=world
replicate_do_db=oldguo
replicate_ignore_db 

replicate_do_table=world.t1
replicate_ignore_table

replicate_wild_do_table=world.t*
replicate_wild_ignore_table

7.3 案例

从库: 
vim /data/3308/my.cnf
replicate_do_db=world

[root@db01 data]# systemctl restart mysqld3308

第8章延时从库

8.1 介绍

1	是我们认为配置的一种特殊从库.人为配置从库和主库延时N小时.

8.2 为什么要有延时从

数据库故障?
物理损坏
主从复制非常擅长解决物理损坏.
逻辑损坏
普通主从复制没办法解决逻辑损坏

8.3 配置延时从库

mysql>  STOP SLAVE;   -- 先停止同步
mysql> CHANGE MASTER TO MASTER_DELAY = 300;  -- 设置延迟300s，一般设置3-6个小时，单位是s
mysql> START SLAVE; -- 启动同步
mysql> SHOW SLAVE STATUS \G;  -- 查看状态
SQL_Delay: 300
SQL_Remaining_Delay: NULL

8.4 模拟故障及恢复

(1) 模拟数据(3307)

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

drop database oss;

(2) 停线程(3308)

1 2	mysql> STOP SLAVE; -- 都停了 mysql> stop slave sql_thread; --停止SQL线程方法

(3) 截取relaylog

#起点:
show slave status \G
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 320

#终点:
mysql> show relaylog events in 'db01-relay-bin.000002';
| db01-relay-bin.000005 | 1401 | Query          |         7 |        1826 | drop database oss    
 db01-relay-bin.000005  1401

(4)截取日志

1	[root@db01 data]# mysqlbinlog --start-position=320 --stop-position=1401 db01-relay-bin.000002 >/tmp/relay.sql

(5)恢复

mysql> set sql_log_bin=0;
mysql> source /tmp/relay.sql
mysql> set sql_log_bin=1;
mysql> show slave status \G
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

(6)从库身份解除

1 2	>stop slave; >reset slave all;

第9章半同步

1	解决主从数据一致性问题

9.1 半同步复制工作原理的变化

1. 主库执行新的事务,commit时,更新 show master status\G ,触发一个信号给 
2. binlog dump 接收到主库的 show master status\G信息,通知从库日志更新了 
3. 从库IO线程请求新的二进制日志事件 
4. 主库会通过dump线程传送新的日志事件,给从库IO线程 
5. 从库IO线程接收到binlog日志,当日志写入到磁盘上的relaylog文件时,给主库ACK_receiver线程 6. ACK_receiver线程触发一个事件,告诉主库commit可以成功了 
7. 如果ACK达到了我们预设值的超时时间,半同步复制会切换为原始的异步复制.

9.2 配置半同步复制

加载插件
主:
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
从:
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
查看是否加载成功:
show plugins;
启动:
主:
SET GLOBAL rpl_semi_sync_master_enabled = 1;
从:
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
重启从库上的IO线程
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;
查看是否在运行
主:
show status like 'Rpl_semi_sync_master_status';
从:
show status like 'Rpl_semi_sync_slave_status';

第10章 GTID复制

10.1 GTID介绍

GTID(Global Transaction ID)是对于一个已提交事务的唯一编号，并且是一个全局(主从复制)唯一的编号。
它的官方定义如下：
GTID = source_id ：transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29
什么是sever_uuid，和Server-id 区别？
核心特性: 全局唯一,具备幂等性

10.2 GTID核心参数

gtid-mode=on 
enforce-gtid-consistency=true 
log-slave-updates=1 

gtid-mode=on 					--启用gtid类型，否则就是普通的复制架构 
enforce-gtid-consistency=true 	--强制GTID的一致性 
log-slave-updates=1 			--slave更新是否记入日志

10.3 GTID复制配置过程

10.3.1 清理环境

1
2
3

pkill mysqld
 \rm -rf /data/mysql/data/*
 \rm -rf /data/binlog/*

10.3.2 准备配置文件

主库db01：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/data/mysql/
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=51
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db01 [\\d]>
EOF

slave1(db02)：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/data/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=52
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db02 [\\d]>
EOF

slave2(db03)：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/data/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=53
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db03 [\\d]>
EOF

10.3.3 初始化数据

1	mysqld --initialize-insecure --user=mysql --basedir=/data/mysql --datadir=/data/mysql/data

10.3.4 启动数据库

1	systemctl start mysqld

10.3.5 构建主从

master:51
slave:52,53

51:
grant replication slave  on *.* to repl@'10.0.1.%' identified by '123';

52\53:
change master to 
master_host='10.0.0.51',
master_user='repl',
master_password='123' ,
MASTER_AUTO_POSITION=1;

start slave;

10.3.6 GTID 从库误写入操作处理

查看监控信息: 
Last_SQL_Error: Error 'Can't create database 'oldboy'; database exists' on query. Default database: 'oldboy'. Query: 'create database oldboy'

Retrieved_Gtid_Set: 71bfa52e-4aae-11e9-ab8c-000c293b577e:1-3 
Executed_Gtid_Set: 71bfa52e-4aae-11e9-ab8c-000c293b577e:1-2, 
7ca4a2b7-4aae-11e9-859d-000c298720f6:1

注入空事物的方法： 
stop slave; 
set gtid_next='99279e1e-61b7-11e9-a9fc-000c2928f5dd:3'; 
begin;commit; 
set gtid_next='AUTOMATIC'; 

这里的xxxxx:N 也就是你的slave sql thread报错的GTID，或者说是你想要跳过的GTID。 
最好的解决方案：重新构建主从环境

10.3.7 GTID 复制和普通复制的区别

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

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

（0）在主从复制环境中，主库发生过的事务，在全局都是由唯一GTID记录的，更方便Failover
（1）额外功能参数（3个）
（2）change master to 的时候不再需要binlog 文件名和position号,MASTER_AUTO_POSITION=1;
（3）在复制过程中，从库不再依赖master.info文件，而是直接读取最后一个relaylog的 GTID号
（4） mysqldump备份时，默认会将备份中包含的事务操作，以以下方式
    SET @@GLOBAL.GTID_PURGED='8c49d7ec-7e78-11e8-9638-000c29ca725d:1';
    告诉从库，我的备份中已经有以上事务，你就不用运行了，直接从下一个GTID开始请求binlog就行。

文章作者: Wu Fei

文章链接: http://linuxwf.com/2020/04/15/8-MySQL%E4%B8%BB%E4%BB%8E%E5%A4%8D%E5%88%B6/

mysql mysql主从复制

打赏

微信
支付宝

第8课 MySQL主从复制

第1章 企业高可用性标准

1.1 全年无故障率(非计划内故障停机)

1.2 高可用架构方案

第2章 主从复制

2.1 简介

2.2 主从复制前提(搭建主从的过程)

第3章 主从复制搭建

3.1 准备两个数据库实例(3307(主),3308(从))

3.2 修改主库my.cnf 开启二进制日志功能

3.3 启动所有节点

3.4 主库创建复制用户(3307 主库)

3.5 备份主库数据恢复到从库

3.6 配置从库

3.7 开启主从专用线程

3.8 检查状态

3.9 如果搭建报错,解决方法

第4章 主从复制的原理

4.1 主从中设置到的文件和线程

4.1.1 线程

4.1.2 文件

4.2 主从复制原理

第5章 主从故障监控\分析\处理

5.1 线程相关监控

5.1.1 主库信息监控(master.info)

5.1.2 从库relay-log的应用信息(relay-log.info)

5.1.3 从库线程工作状态

5.1.4 过滤复制有关信息

5.1.5 主从延时时间

5.1.6 延时从库的状态

5.1.7 GTID复制有关

5.2 主从复制故障分析

5.2.1 IO线程故障

5.2.2 SQL 线程故障

第6章 主从延时

6.1 监控

6.2 主从延时原因分析

6.2.1 外在因素

6.2.2 主库

6.2.3 从库

第7章 过滤复制

7.1 主库 (了解)

7.2 从库

7.3 案例

第8章 延时从库

8.1 介绍

8.2 为什么要有延时从

8.3 配置延时从库

8.4 模拟故障及恢复

第9章 半同步

9.1 半同步复制工作原理的变化

9.2 配置半同步复制

第10章 GTID复制

10.1 GTID介绍

10.2 GTID核心参数

10.3 GTID复制配置过程

10.3.1 清理环境

10.3.2 准备配置文件

10.3.3 初始化数据

10.3.4 启动数据库

10.3.5 构建主从

10.3.6 GTID 从库误写入操作处理

10.3.7 GTID 复制和普通复制的区别

第1章企业高可用性标准

第2章主从复制

第3章主从复制搭建

第4章主从复制的原理

第5章主从故障监控\分析\处理

第6章主从延时

第7章过滤复制

第8章延时从库

第9章半同步