mysql 半同步复制与过滤复制
半同步复制
原理
半同步问题：主库写入一个事务commit提交并执行完之后，并不直接将请求反馈给前端应用用户，
而是等待从库也接收到binlog日志并成功写入中继日志后，主库才返回commit操作成功给客户端。
半同步复制保障了事物执行后，至少有两份日志记录，一份在主库的binlog上 ，
另一份至少在从库的中继日志Relay log上，这样就极大的保证了数据的一致性。
从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；
#半同步复制实际上就是mysql的一个插件
优缺点：
1.保证主从数据一致性，实时的
2.性能差，效率低
3.从库sql写入会影响主库的性能
4.半同步复制有一个超时时间，如果超过这个超时时间，那么恢复异步复制
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 -p123
#安装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就会恢复到异步复制。
mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 | #记录支持半同步的slave的个数
| Rpl_semi_sync_master_net_avg_wait_time | 0 | #master 等待slave回复的平均等待时间单位微秒
| Rpl_semi_sync_master_net_wait_time | 0 | #master 总的等待时间
| Rpl_semi_sync_master_net_waits | 0 | #master 等待slave回复的的总的等待次数
| Rpl_semi_sync_master_no_times | 0 | #master 关闭半同步复制的次数
| Rpl_semi_sync_master_no_tx | 0 | #master 没有收到slave的回复而提交的次数
| Rpl_semi_sync_master_status | OFF | #标记master现在是否是半同步复制状态
| Rpl_semi_sync_master_timefunc_failures | 0 | #时间函数未正常工作的次数
| Rpl_semi_sync_master_tx_avg_wait_time | 0 | #开启Semi-sync，事务返回需要等待的平均时间
| Rpl_semi_sync_master_tx_wait_time | 0 | #事务等待备库响应的总时间
| Rpl_semi_sync_master_tx_waits | 0 | #事务等待备库响应的总次数
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 | #改变当前等待最小二进制日志的次数
| Rpl_semi_sync_master_wait_sessions | 0 | #当前有多少个session因为slave的回复而造成等待
| Rpl_semi_sync_master_yes_tx | 0 | #master 成功接收到slave的回复的次数
+--------------------------------------------+-------+
4.测试半同步复制
#创建两个数据库，test1和test2
mysql> create database test1;
Query OK, 1 row affected (0.04 sec)
mysql> create database test2;
Query OK, 1 row affected (0.00 sec)
#查看复制状态
mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 768 |
| Rpl_semi_sync_master_net_wait_time | 1497 |
| Rpl_semi_sync_master_net_waits | 2 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 884 |
| Rpl_semi_sync_master_tx_wait_time | 1769 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
#此行显示2，表示刚才创建的两个库执行了半同步
| Rpl_semi_sync_master_yes_tx | 2 |
+--------------------------------------------+-------+
14 rows in set (0.06 sec)
#从库查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| test1 |
| test2 |
+--------------------+
#关闭半同步（1:开启 0:关闭）
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 0;
#查看半同步状态
mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 768 |
| Rpl_semi_sync_master_net_wait_time | 1497 |
| Rpl_semi_sync_master_net_waits | 2 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | OFF | #状态为关闭
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 884 |
| Rpl_semi_sync_master_tx_wait_time | 1769 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 2 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
#再一次创建两个库
mysql> create database test3;
Query OK, 1 row affected (0.00 sec)
mysql> create database test4;
Query OK, 1 row affected (0.00 sec)
#再一次查看半同步状态
mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 768 |
| Rpl_semi_sync_master_net_wait_time | 1497 |
| Rpl_semi_sync_master_net_waits | 2 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 884 |
| Rpl_semi_sync_master_tx_wait_time | 1769 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
#此行还是显示2，则证明，刚才的那两条并没有执行半同步否则应该是4
| Rpl_semi_sync_master_yes_tx | 2 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
注:不难发现，在查询半同步状态是，开启半同步，查询会有延迟时间，关闭之后则没有
过滤复制
grant replication slave on mysql.* to rep@'172.16.1.%' identified by '123';
1.确认两台从库状态
2.过滤复制的两种方式
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000010 | 120 | 白名单 | 黑名单 | |
+------------------+----------+--------------+------------------+-------------------+
1）黑名单
黑名单：不执行黑名单中列出的库或者表的中继日志
replicate-ignore-db=test
replicate-ignore-table=test.t1
replicate-wild-ignore-table=test.t2
2）白名单
白名单：只执行白名单中列出的库或者表的中继日志
replicate-do-db=test
replicate-do-table=test.t1
replicate-wild-do-table=test.t2
3.从库配置过滤复制
1）主库创建两个库
mysql> create database lol;
Query OK, 1 row affected (0.00 sec)
mysql> create database wzry;
Query OK, 1 row affected (0.00 sec)
2）第一台从库
1.配置数据库
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
replicate-do-db=lol
2.重启数据库
[root@db02 ~]# systemctl restart mysqld
3.查看主从状态
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: lol #从库的执行白名单
3）第二台从库
1.配置数据库
[root@db03 ~]# vim /etc/my.cnf
[mysqld]
replicate-do-db=wzry
2.重启数据库
[root@db03 ~]# systemctl restart mysqld
3.查看主从状态
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: wzry #从库的执行白名单
4）主库建表测试
mysql> use lol;
mysql> create table bierjiwote(id int);
mysql> use wzry;
mysql> create table cikexintiao(id int);
5）从库查看
1> 第一台从库查看
mysql> use lol;
mysql> show tables;
+---------------+
| Tables_in_lol |
+---------------+
| bierjiwote |
+---------------+
1 row in set (0.00 sec)
mysql> use wzry
mysql> show tables;
Empty set (0.00 sec)
2> 第二台从库查看
mysql> use lol;
mysql> show tables;
Empty set (0.00 sec)
mysql> use wzry
mysql> show tables;
+----------------+
| Tables_in_wzry |
+----------------+
| cikexintiao |
+----------------+
1 row in set (0.00 sec)
4.扩展
#一台机器配置多个白名单
1.方式一：
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id=2
replicate-do-db=lol
replicate-do-db=dnf
2.方式二：
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id=2
replicate-do-db=lol,dnf
5.主库配置白名单
1.配置白名单
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log_bin=mysql-bin
binlog-do-db=dnf #主库配置
2.主库查看白名单
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000011 | 120 | dnf | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
3.主库建表测试
mysql> use lol;
mysql> create table heisemeigui(id int);
Query OK, 0 rows affected (0.01 sec)
mysql> use wzry
mysql> create table weilaizhanshi(id int);
Query OK, 0 rows affected (0.01 sec)
mysql> use dnf
mysql> create table shanghai1qu(id int);
Query OK, 0 rows affected (0.00 sec)
4.从库查看
mysql> use lol;
mysql> showtables;
mysql> show tables;
+---------------+
| Tables_in_lol |
+---------------+
| bierjiwote |
+---------------+
1 row in set (0.00 sec)
mysql> use wzry
mysql> show tables;
Empty set (0.00 sec)
mysql> use dnf
mysql> show tables;
+-----------------+
| Tables_in_dnf |
+-----------------+
| heilongjiang1qu |
| shanghai1qu |
+-----------------+
2 rows in set (0.00 sec)
6.过滤复制总结
#从库配置：
1.白名单：IO线程会将主库所有的binlog都取过来，存储在relay-log中，但是sql线程根据我们配置的白名单，只执行了跟白名单有关的数据库语句
2.黑名单：IO线程会将主库所有的binlog都取过来，存储在relay-log中，但是sql线程根据我们配置的给名单，不执行跟黑名单有关的数据库语句
#主库配置：
1.白名单：主库只记录对白名单中的库操作的sql语句到binlog
2.黑名单：主库不记录对黑名单中的库操作的sql语句到binlog
