Mysql主从、延时复制、半同步复制、过滤复制和GTID主从复制
序:主从复制(异步复制)
1.主库操作
1.配置server_id,开启binlog
2.主库授权用户
mysql> grant replication slave on *.* to rep@'172.16.1.5%' identified by '123';
3.查看binlog信息
mysql> show master status;
4.导出数据库数据
2.从库操作
1.配置server_id
2.配置主从
mysql>
change master to
master_host='172.16.1.51',
master_user='rep',
master_password='123',
master_log_file='mysql-bin.000001',
master_log_pos=327;
Query OK, 0 rows affected, 2 warnings (2.94 sec)
3.开启线程
start slave;
4.其他命令:重置从库设置
reset slave all;
3.修改密码等其他操作
# 主库修改主从用户密码
alter user rep@'10.0.0.6' identified by '密码';
flush privileges;
# 从库操作
stop slave;
show slave status\G
change master to master_user='rep', master_password= '密码';
start slave;
# 特殊情况跳过1个错误
stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
start slave;
一、主从复制原理
1.文字描述
1.主库配置server_id和开启binlog
2.主库授权从库连接的用户
3.主库查看binlog信息,与服务器信息
4.从库配置跟主库不一致server_id
5.配置主从,通过change master to高速从库主库的信息:ip、用户、密码、端口、binlog位置点、binlog名字
6.从库开启IO线程和sql线程
7.从库连接主库以后,IO线程会向主库的dump线程发起询问,询问是否有新数据
8.dump线程被询问,去查找新数据,并将新数据返回给IO线程
9.IO线程拿到数据先写入TCP缓存
10.TCP缓存将数据写入中继日志,并返回给IO线程一个ACK
11.IO线程收到ACK会记录当前位置点到master.info
12.sql线程会读取relay-log,执行从主库获取的sql语句
13.执行完以后将执行到的位置点,记录到relay-log.info
2.主从监控
- 主库
show processlist
show slave hosts - 从库
show slave status\G
重要参数 Seconds_Behind_Master: 0
系统问题导致的从库的延时时间(故障重点关注参数),不精准,仅供参考。
评估主从延时精确的指标是延时了多少日志量,主库执行的日志量,从库执行的日志对比。
3.主从故障分析及处理
1)IO线程出错
mysql> show slave status\G
Slave_IO_Running: No
Slave_SQL_Running: Yes
mysql> show slave status\G
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
'排查思路,先注意看报错提示。然后排错。'
1.网络,端口,防火墙
ping 172.16.1.53
telnet 172.16.1.53 3306
2.relaylog问题.
3.主库二进制日志不完整或损坏.从库请求起点问题.
4.主从授权的用户错误
5.反向解析
需要在配置文件中设置:skip-name-resolve
6.UUID或server_id相同
7.主库连接数达到上限
mysql> select @@max_connections;
8.版本不统一(例如密码验证方式不同)
2)SQL线程出错
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: No
#原因:
1.主库有的数据不一致,数据异步.
2.约束冲突.或者sql_mode,参数,版本不一致.
#处理方式一:
1.临时停止同步
mysql> stop slave;
2.将同步指针向下移动一个(可重复操作),如果多次不同步,可以重复操作直到指针同步.可以配合pt-toolkit中工具使用.
mysql> set global sql_slave_skip_counter=1;
"要保证故障数据是完全一致时才能使用."
3.开启同步
mysql> start slave;
#处理方式二:不建议
1.编辑配置文件
[root@db01 ~]# vim /etc/my.cnf
#在[mysqld]标签下添加以下参数跳过错误.
slave-skip-errors=1032,1062,1007
#处理方式三:正解
重新同步数据,重新做主从.
# 主从相关命令:
mysql> start slave;
mysql> stop slave;
mysql> stop slave sql_thread;
mysql> stop slave io_thread;
mysql> reset slave all;
4.生产需求
# 主库需要做reset master 清理binlog日志,又需要快速恢复主从和业务.
1.停主库5分钟.(然后稍等片刻,确认有延时的数据同步完成,保证数据一致性)
2.主库reset master;
3.从库stop slave;
4.从库reset slave all;
5.然后查看并重新change master to初始的位置点.
5.主从延时问题的原因分析及处理
主库
- 外部:
网络,硬件配置,参数设定,业务繁忙线程受影响,从库太多。
- 内部:
1.二进制日志更新延时,把sync_binlog=1可以解决.
2.主库大并发到binlog,而传输到dump时是串行,从库的sql线程也是串行,吞吐量阻塞(5.7版本使用GTID前,如下图)。
- 解决主库业务繁忙:
1.拆分业务:组件分离,垂直分表,水平分表
2.大事务的拆分:比如1000w业务拆分成20次执行、 - 解决从库延时问题:
1.开启GTID
2.串行改并行
1)5.6+GTID:database级别,基于库级别的sql线程并发.
2)5.7+GTID:Logic_clock 逻辑时钟,保证了同库级别下的事务顺序问题.(基于事务级别的并发回放MTS.)
二、延时复制
'1. 延时从库只做备份,不提供任何对外服务.
2. 从库io线程已经把主库执行的所有语句读取并存入relay-log中,只是从库的sql线程延时执行。
3. 所以我们可以通过延时主从的relay-log来恢复主库有问题的数据和步骤。'
1.配置延时复制(已经有主从)
1.停止主从
mysql> stop slave;
Query OK, 0 rows affected (0.03 sec)
2.配置延时时间 180秒
mysql> change master to master_delay=180;
Query OK, 0 rows affected (0.01 sec)
3.开启主从
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
2.配置延时复制(没有主从)
1.搭建出一台mysql
2.配置主从
mysql> change master to
-> master_host='172.16.1.51',
-> master_user='rep',
-> master_password='123',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=424,
-> master_delay=180;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
3.开启线程
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
3.关闭延时从库
mysql> stop slave;
mysql> change master to master_delay=0;
mysql> start slave;
- 注意:
'延时从库恢复数据时不要关闭主库的binlog,实际上从库还是会执行主库执行错的语句,只不过又执行了重建语句。'
Master_Log_File/Read_Master_Log_Pos. -----相对于主库,从库读取主库的二进制日志的位置,是IO线程
Relay_Log_File/Relay_Log_Pos ----相对于从库,是从库的sql线程执行到的位置
Relay_Master_Log_File/Exec_Master_Log_Pos ----相对于主库,是从库的sql线程执行到的位置
4.生产实例(利用延时从库恢复数据)
#思考问题:
总数据量级500G,正常备份去恢复需要1.5-2小时
1)配置延时3600秒
mysql>CHANGE MASTER TO MASTER_DELAY = 3600;
2)主库
drop database db;
3)怎么利用延时从库,恢复数据?
提示:
1、从库relaylog存放在datadir目录下
2、mysqlbinlog 可以截取relaylog内容
3、show relay log events in 'db01-relay-bin.000001';
#处理的思路:
1)停止SQL线程
mysql> stop slave sql_thread;
2)截取relaylog到误删除之前点
relay-log.info 获取到上次运行到的位置点,作为恢复起点
分析relay-log的文件内容,获取到误删除之前position
模拟故障处理:
1)关闭延时
mysql -S /data/3308/mysql.sock
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_DELAY = 0;
mysql> start slave;
2)模拟数据
mysql -S /data/3307/mysql.sock
source /root/world.sql
use world;
create table c1 select * from city;
create table c2 select * from city;
3)开启从库延时5分钟
mysql -S /data/3308/mysql.sock
show slave status \G
mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY = 300;
mysql>start slave;
mysql -S /data/3307/mysql.sock
use world;
create table c3 select * from city;
create table c4 select * from city;
4)破坏,模拟删库故障。(以下步骤在5分钟内操作完成。)
mysql -S /data/3307/mysql.sock
drop database world;
5)从库,关闭SQL线程
mysql -S /data/3308/mysql.sock
stop slave sql_thread;
6)截取relay-log
起点:
cd /data/3308/data/
cat relay-log.info
./db01-relay-bin.000002
283
终点:
mysql -S /data/3308/mysql.sock
show relaylog events in 'db01-relay-bin.000002'
db01-relay-bin.000002 | 268047
mysqlbinlog --start-position=283 --stop-position=268047 /data/3308/data/db01-relay-bin.000002 >/tmp/relay.sql
恢复relay.sql
1)取消从库身份
mysql> stop slave;
mysql> reset slave all;
2)恢复数据
mysql> set sql_log_bin=0;
mysql> source /tmp/relay.sql
mysql> use world
mysql> show tables;
三、半同步复制
1.半同步复制概念
通过io线程阻止数据的写入,确认sql线程执行完毕之后再同步。通过插件来实现。
从MYSQL5.5开始,支持半自动复制。之前版本的MySQL Replication都是异步(asynchronous)的。
主库在执行完一些事务后,是不会管备库的进度的。
如果备库不幸落后,而更不幸的是主库此时又出现Crash(例如宕机),这时备库中的数据就是不完整的。
简而言之,在主库发生故障的时候,我们无法使用备库来继续提供数据一致的服务了。
半同步复制(Semi synchronous Replication)则一定程度上保证提交的事务已经传给了至少一个备库。
出发点是保证主从数据一致性问题,安全的考虑。
5.5 出现概念,但是不建议使用,性能太差
5.6 出现group commit 组提交功能,来提升开启半同步复制的性能
5.7 更加完善了,在group commit基础上出现了MGR
5.7 的增强半同步复制的新特性:after commit; after sync;
#缺点:
1.性能差,影响主库效率
2.半同步复制,有一个超时时间,超过这个时间恢复主从异步复制
实际生产一般使用MGR和PXC等一致性架构代替.
2.配置半同步
1)主库操作
#登录数据库
[root@db01 ~]# mysql -uroot -p123
#查看是否有动态支持
mysql> show global variables like 'have_dynamic_loading';
#安装自带插件
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME'semisync_master.so';
#启动插件
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
#设置超时
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;
#修改配置文件
[root@db01 ~]# vim /etc/my.cnf
#在[mysqld]标签下添加如下内容(不用重启库)
[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000
检查安装:
mysql> show variables like'rpl%';
mysql> show global status like 'rpl_semi%';
2)从库操作
#登录数据库
[root@mysql-db02 ~]# mysql -uroot -poldboy123
#安装slave半同步插件
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME'semisync_slave.so';
#启动插件
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
#重启io线程使其生效
mysql> stop slave io_thread;
mysql> start slave io_thread;
#编辑配置文件(不需要重启数据库)
[root@mysql-db02 ~]# vim /etc/my.cnf
#在[mysqld]标签下添加如下内容
[mysqld]
rpl_semi_sync_slave_enabled =1
3)额外参数
rpl_semi_sync_master_timeout=milliseconds
设置此参数值(ms),为了防止半同步复制在没有收到确认的情况下发生堵塞,
如果Master在超时之前没有收到任何确认,将恢复到正常的异步复制,并继续执行没有半同步的复制操作。
rpl_semi_sync_master_wait_no_slave={ON|OFF}
如果一个事务被提交,但Master没有任何Slave的连接,这时不可能将事务发送到其它地方保护起来。
默认情况下,Master会在时间限制范围内继续等待Slave的连接,并确认该事务已经被正确的写到磁盘上。
可以使用此参数选项关闭这种行为,在这种情况下,如果没有Slave连接,Master就会恢复到异步复制。
四、过滤复制
1.过滤复制的方式
# 主库的黑白名单:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 305 | 白名单 | 黑名单 | |
+------------------+----------+--------------+------------------+-------------------+
1)白名单
#从库
replicate-do-db=test "库级别"
replicate-do-table=test.t1 "表级别"
replicate-wild-do-table=test.t* "匹配方式的表级别"
#主库
binlog-do-db=test
binlog-do-table=test.t1
binlog-wild-do-table=test.t*
2)黑名单
#从库配置
replicate-ignore-db=test 配置库
replicate-ignore-table=test.t1 配置表
replicate-wild-ignore-table=test.t* 配置表(支持正则)
#主库配置
binlog-ignore-db=test 配置库
binlog-ignore-table=test.t1 配置表
binlog-wild-ignore-table=test.t* 配置表(支持正则)
2.配置过滤复制
1)主库创建两个库
mysql> create database wzry;
mysql> create database lol;
2)第一台从库配置
[root@db02 data]# vim /etc/my.cnf
[mysqld]
server_id=2
replicate-do-db=wzry
[root@db02 data]# systemctl restart mysqld
#查看主从状态
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: wzry
3)配置第二台从库
[root@db03 ~]# vim /etc/my.cnf
[mysqld]
server_id=2
replicate-do-db=lol
[root@db03 ~]# systemctl restart mysqld
#查看主从状态
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: lol
4)验证过滤复制
#1.主库操作
mysql> use wzry
Database changed
mysql> create table cikexintiao(id int);
mysql> use lol
Database changed
mysql> create table fuleierzhuode(id int);
#第一台从库查看
mysql> use wzry
Database changed
mysql> show tables;
+----------------+
| Tables_in_wzry |
+----------------+
| cikexintiao |
+----------------+
mysql> use lol
Database changed
mysql> show tables;
#第二台从库查看
mysql> use wzry
Database changed
mysql> show tables;
mysql> use lol
Database changed
mysql> show tables;
+---------------+
| Tables_in_lol |
+---------------+
| fuleierzhuode |
+---------------+
3.配置过滤多个库
1)方法一:
[root@db02 data]# vim /etc/my.cnf
[mysqld]
server_id=2
replicate-do-db=wzry,lol
2)方法二:
[root@db02 data]# vim /etc/my.cnf
[mysqld]
server_id=2
replicate-do-db=wzry
replicate-do-db=lol
4.过滤复制配置在主库
'配置在白名单中的时候,只同步配置的库内容,其他都不会同步。'
'配置在黑名单中的时候,只不同步配置的库内容,其他都会同步。'
1.配置
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log_bin=/usr/local/mysql/data/mysql-bin
binlog-do-db=wzry
2.查看主库状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 120 | wzry | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
3.在主库的wzry库和lol库添加数据
4.从库查看数据,只能看到wzry库的数据
5.过滤复制总结
#配置在从库时
1.配置白名单:IO线程将主库的数据拿到了relay-log,但是sql线程只执行白名单配置的数据库相关语句
1.配置黑名单:IO线程将主库的数据拿到了relay-log,但是sql线程只不执行黑名单配置的数据库相关语句
#配置在主库时
1.配置白名单:binlog只记录白名单相关的sql语句
2.配置黑名单:binlog只不记录黑名单相关的sql语句
'所以推荐过滤复制配置在从库,不影响主库的运作。'
'配置在主库的话容易丢失sql语句,不利于数据恢复。'
五、基于GTID的主从复制
1.什么是GTID
1.全局事务标识符
2.组成:UUID + TID
f03a53e0-cd46-11ea-a2c4-000c292c767e:1
'注意:'
1.uuid相同会导致gtid出错
2.如果初始化或者reset master之后binlog会恢复到初始空状态,这时候的gtid标识符为空,没有数据(不影响做主从)。
2.GTID主从复制的优点
1.GTID同步时开启多个SQL线程,每一个库同步时开启一个线程
2.binlog在rows模式下,binlog内容比寻常的主从更加简洁
3.GTID主从复制会记录主从信息,不需要手动配置binlog和位置点
3.GTID主从复制的缺点
1.备份时更加麻烦,需要额外加一个参数 --set-gtid=on
2.主从复制出现错误,没有办法跳过错误
4.搭建GTID主从复制
1)配置三台数据库
#配置第一台主库
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log_bin=/usr/local/mysql/data/mysql-bin
#配置第一台从库
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id=2
#配置第二台从库
[root@db03 ~]# vim /etc/my.cnf
[mysqld]
server_id=3
2)查看是否开启GTID
mysql> show variables like '%gtid%';
+---------------------------------+-----------+
| Variable_name | Value |
+---------------------------------+-----------+
| binlog_gtid_simple_recovery | OFF |
| enforce_gtid_consistency | OFF |
| gtid_executed | |
| gtid_mode | OFF |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| simplified_binlog_gtid_recovery | OFF |
+---------------------------------+-----------+
8 rows in set (0.00 sec)
3)开启GTID
## GTID 所指向的位置点为本条命令执行结束后的位置点.
#主库配置
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log_bin=/usr/local/mysql/data/mysql-bin
gtid_mode=on
enforce_gtid_consistency=on
log-slave-updates
#从库1的配置
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id=2
log_bin=/usr/loc al/mysql/data/mysql-bin
gtid_mode=on
enforce_gtid_consistency=on
log-slave-updates
#从库2的配置
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id=3
log_bin=/usr/local/mysql/data/mysql-bin
gtid_mode=on
enforce_gtid_consistency=on
log-slave-updates
# 查看binlog和截取GTID日志(gtid在binlog日志是始终保持跨文件递增的.)
--include-gtids #包含哪些日志
--exclude-gtids #排除哪些日志
例如:
mysqlbinlog --include-gtids='882e17b3-f750-11ea-a325-000c2934a376:1-12' --exclude-gtids='882e17b3-f750-11ea-a325-000c2934a376:4' /data/binlog/mylog-bin.000006
# 查看gtid
show master status;
show binlog events in 'mysql-bin.000002'
# 利用grep过滤查找gtid信息
mysql> system mysql -e "show binlog events in 'mysql-bin.000001'"|grep Gtid
每一条操作语句或事务操作会使gtid增加.
GTID幂等性
开启gtid后,mysql恢复binlog时,重复gtid的事务就不会再执行了. (默认使用gtid后的所有命令不能再次重复执行.)
恢复时需要加上 --skip-gtids 跳过gtid检查
(或者手工删除sql或binlog里的gtid命令行)
4)扩展
#配置log-slave-updates参数的场景
1.基于GTID的主从复制
2.双主架构+keepalived
3.级联复制
4.MHA
5)主库创建用户
mysql> grant replication slave on *.* to rep@'172.16.1.5%' identified by '123';
6)主库数据同步到从库
mysqldump -uroot -p -R --triggers --master-data=2 --single-transaction -A > /tmp/full.sql
scp
mysql < full.sql
7)从库配置主从
设置从库 mysql>
change master to
master_host='172.16.1.51',
master_user='rep',
master_password='123',
master_auto_position=1;
# 开启主从同步
mysql> start slave;
# 查看主从状态
mysql> show slave status\G
# 查看主从延时
mysql> show slave status\G
其中值即为同步延时 Seconds_Behind_Master:
主库开始GTID,后来关闭了。做从库的时候使用的是:
mysql < change master to......
master_auto_position=1
这种方法,然后就会报以上错误。
解决的办法:
由于之前用的master_auto_position=1,现在改回0,重新用pos方法change就可以了。
关闭gtid或改成做GTID方式即可:
mysql < change master to master_auto_position=n(show master status查看gtid数值n);
六、主从复制架构演变
-
原生态支持
1主1从
1主多从(3-4个)
多级主从
双主结构
延时从库
过滤复制
MGR组复制 -
非原生态
安全:高可用
全年无故障率
99% 一般级别
99.9% 普通级别
99.99% 准高可用
代表产品: mysql MHA
99.999% 金融级别
代表产品: mysql cluster,innodb cluster,PXC,MGC
Oracle RAC, sysbase cluster
99.9999% 超金融级别