MySQL主从复制（下）
传统的主从辅助的缺陷
1.传统住从复制无法做备份
2.传统主从复制无法过滤复制
3.传统主从复制速度慢
4.传统主从复制是异步复制，从库数据延迟
mysql> grant replication slave on prod.* to slave@'%' identified by '123';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
延迟复制
## 企业中一般会延时3-6小时
1.做延时从库的服务器，无法在生产中继续提供服务
2.备份
3.恢复数据速度更快
因为延时复制主从数据同一时间不一致，
所以延时从库一般只能做备份，不提供任何对外服务
延迟复制的原理
# 作用于从库的SQL线程
# 不影响I/O线程的读写
配置延迟复制(已有主从)
######### 从库 #########
1）### 停止主从复制
stop slave;
2）### 重置主从复制
reset slave;
3）### 配置从库延迟
change master to master_delay=180;
4）### 开启延迟复制
start slave;
配置延迟复制（全新从库）
1.#### 搭建出一台mysql
2.#### 配置主从
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;
3.#### 开启线程
start slave;
关闭延迟复制
# 停止主从
mysql> stop slave;
# 修改主从信息
reset slave;
change master to master_delay=0;
# 启动主从
start slave;
注意事项
延时从库恢复数据时不要关闭主库的binlog，实际上从库还是会执行主库执行错的语句，只不过又执行了重建语句
半同步复制
从MYSQL5.5开始，支持半自动复制。之前版本的MySQL Replication都是异步（asynchronous）的，主库在执行完一些事务后，是不会管备库的进度的。如果备库不幸落后，而更不幸的是主库此时又出现Crash（例如宕机），这时备库中的数据就是不完整的。简而言之，在主库发生故障的时候，我们无法使用备库来继续提供数据一致的服务了。
半同步复制出现原因：为了保证主库和从库的数据一致性
从库的IO线程在没有接收到ACK之前，会阻塞主库写入操作
半同步缺点：阻塞主库写入数据，影响主库性能，降低用户体验
半同步优点：主库和从库数据保证了一致性，不会丢数据
半同步复制（Semi synchronous Replication）则一定程度上保证提交的事务已经传给了至少一个备库。
出发点是保证主从数据一致性问题，安全的考虑。
半同步复制（Semi synchronous Replication）则一定程度上保证提交的事务已经传给了至少一个备库。
出发点是保证主从数据一致性问题，安全的考虑。
5.5 出现概念，但是不建议使用，性能太差
5.6 出现group commit 组提交功能，来提升开启半同步复制的性能
5.7 更加完善了，在group commit基础上出现了MGR
5.7 的增强半同步复制的新特性：after commit； after sync；
# 缺点：
1.性能差，影响主库效率
2.半同步复制，有一个超时时间，超过这个时间恢复主从复制
半同步复制的原理
#### 从库的 I/O 线程 在日志持久化到中继日志后，立即发送 ACK 给主库，主库收到 ACK 后确认事务完成。SQL 线程的执行是一个独立的、后续的过程，不会影响主库收到 ACK 并确认事务的时机。
半同步插件
/app/mysql/lib/plugin # besedir目录下lib/plugin
-rwxr-xr-x 1 mysql mysql 722101 Mar 17 2023 semisync_master.so
-rwxr-xr-x 1 mysql mysql 163772 Mar 17 2023 semisync_slave.so
半同步配置额外参数
rpl_semi_sync_master_timeout=milliseconds
设置此参数值（ms）,为了防止半同步复制在没有收到确认的情况下发生堵塞，如果Master在超时之前没有收到任何确认，将恢复到正常的异步复制，并继续执行没有半同步的复制操作。
rpl_semi_sync_master_wait_no_slave={ON|OFF}
如果一个事务被提交,但Master没有任何Slave的连接，这时不可能将事务发送到其它地方保护起来。默认情况下，Master会在时间限制范围内继续等待Slave的连接，并确认该事务已经被正确的写到磁盘上。
可以使用此参数选项关闭这种行为，在这种情况下，如果没有Slave连接，Master就会恢复到异步复制。
配置半同步
################# 主库 ##################
# 登录数据库
[root@db01 ~]# mysql -uroot -p123
# 查看是否有动态支持
show global variables like 'have_dynamic_loading';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| have_dynamic_loading | YES |
+----------------------+-------+
# 安装自带插件
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%';
##################### 从库 ####################
# 登录数据库
[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
过滤复制
过滤复制的方式
### 主库
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 305 | 白名单 | 黑名单 |
+------------------+----------+--------------+------------------+
### 从库
show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.53
# 白名单库
Replicate_Do_DB:
# 黑名单库
Replicate_Ignore_DB:
# 白名单表
Replicate_Do_Table:
# 黑名单表
Replicate_Ignore_Table:
# 支持正则匹配的库
Replicate_Wild_Do_Table:
# 支持正则匹配的黑名单库
Replicate_Wild_Ignore_Table:
过滤复制-白名单
# 从库
# relay-log中全都记录 作用的是sql线程
replicate-do-db=test
replicate-do-table=test.t1
replicate-wild-do-table=test.t*
# 主库
# bin-log就只记录白名单的数据
binlog-do-db=test
binlog-do-table=test.t1
binlog-wild-do-table=test.t*
过滤复制的黑名单
# 从库
# relay-log中全都记录 作用的是sql线程
replicate-ignore-db=test
replicate-ignore-table=test.t1
replicate-wild-ignore-table=test.t*
# 主库
# bin-log就不记录黑名单的数据
binlog-ignore-db=test
binlog-ignore-table=test.t1
binlog-wild-ignore-table=test.t*
GTID主从复制
什么是GTID
1.全局事务标识符
2.组成：UUID + TID
f03a53e0-cd46-11ea-a2c4-000c292c767e:1
GTID主从复制的优点
1.GTID同步时开启多个SQL线程，每一个库同步时开启一个线程
2.binlog在rows模式下，binlog内容比寻常的主从更加简洁
3.GTID主从复制会记录主从信息，不需要手动配置binlog和位置点
GTID主从复制的缺点
1.备份时更加麻烦，需要额外加一个参数 --set-gtid=on
2.主从复制出现错误，没有办法跳过错误
搭建GTID的主从复制
# 配置三台数据库
# 配置第一台主库
[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
#### 查看是否开启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)
#### 修改配置文件
# 主库配置
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log_bin=/usr/local/mysql/data/mysql-bin
gtid_mode=on
enforce_gtid_consistency
log-slave-updates
# 从库1的配置
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id=2
log_bin=/usr/local/mysql/data/mysql-bin
gtid_mode=on
enforce_gtid_consistency
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
log-slave-updates
#### 主库创建用户
mysql> grant replication slave on *.* to rep@'172.16.1.5%' identified by '123';
#### 从库开始复制
## 主库导出数据从库导入
mysqldump -uroot -p -R --triggers --master-data=2 --single-transaction -A > /tmp/full.sql
scp
mysql < full.sql
## 从库开始复制
mysql> change master to
-> master_host='172.16.1.51',
-> master_user='rep',
-> master_password='123',
-> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.03 sec)