MySQL(mariadb)主从复制模式与复制过滤
在前一篇文章《mysql多实例与复制应用》中只对mysql的复制做了简单的介绍，本篇内容专门介绍一下mysql的复制。
MySQL复制
mysql复制是指将主数据库的DDL和DML操作通过二进制日志传到复制服务器(从库)上，然后在从库上对这些日志重新执行(也叫重做)，从而使从库的数据与主库保持一致。
从库一般不接受写操作，可以接受读操作
复制的功能作用
- 数据分布
- 负载均衡 读操作
- 数据备份
- 高可用和故障切换
- 升级测试
MySQL复制简述
mysql复制原理大致如下：
1，mysql主数据库在事物提交时会把数据变更作为事件events记录在二进制日志文件bin-log中，mysql主库上的sync_binlog参数控制bin-log日志刷新到磁盘
2，主库推送二进制日志文件bin-log中的事件到从库的中继日志Relay Log ,之后从库根据中继日志Relay log重写数据操作将数据写入从库，以此达到主库和从库的数据一致
mysql复制过程中通过3个线程来完成复制操作：其中binlog dump线程在主库上，I/O线程和SQL线程在从库上，当在从库上启动复制（START SLAVE）时，首先会I/O线程连接主库，（连接主库用户用主库创建），主库随后创建binlog dump线程读取数据库事件（binlog日志）并发送给I/O线程，I/O获取到binlog日志数据后更新到从库的中继日志Relay log中，从库上的SQL线程读取中继日志Relay log 中数据写入本地数据库，最终完成主从复制。
简化复制流程
主从复制流程：
- 主节点： 必须开启二进制日志文件
- 从节点： 启用中继日志
- 从节点： 启动I/O线程
- 主节点： 启动mysql dump线程推送数据给从节点
- 从节点： 启动SQL线程将中继日志数据写入本地
从节点线程：
- I/O 线程:从master请求二进制日志事件，并保存于中继日志中
- SQL 线程：从中继日志读取日志事件，在本地完成重放，写入本地
mysql复制的实现
实验：主库：192.168.214.130 ： 从库： 192.168.214.142
由于编译安装时间比较长，安装mariadb使用yum安装
1. 安装mariadb
yum install mariadb-server -y
1. 配置主库
[mysqld]
log_bin=master-bin #开启二进制日志
service_id= 1 #server-id 全局唯一，不能相同
innodb_file_per_table=on #开启独立表空间
重启服务并创建 复制用户账号
[root@master ~]# systemctl restart mariadb
MariaDB [(none)]> grant replication slave on *.* to yufu@'192.168.214.130' identified by 'centos';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
查看主库二进制日志状态
MariaDB [(none)]> show variables like '%log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
| sql_log_bin | ON |
+---------------+-------+
2 rows in set (0.01 sec)
2. 配置从库
[mysqld]
relay_log=relay-log #启用中继日志
server_id= 2
关于从库是否要开启二进制日志：当使用多级复制，从库同时也是其他库的主库时，需要开启二进制，否则可以不开启
重启服务 并查看中继日志启用状态
systemctl restart mariadb
MariaDB [(none)]> show variables like 'relay_log';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| relay_log | relay-log |
+---------------+-----------+
1 row in set (0.00 sec)
3. 主库查看二进制日志记录pos值
MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000001 | 474 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
4. 从库连接至主库
MariaDB [(none)]> change master to master_host='192.168.214.142',master_user='yufu',master_port=3306,master_password='centos',master_log_file='master-log.000001',master_log_pos=474;
Query OK, 0 rows affected (0.39 sec)
5. 启动从库复制
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (1.89 sec)
查看复制状态
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.214.142
Master_User: yufu
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000001
Read_Master_Log_Pos: 474
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 530
Relay_Master_Log_File: master-log.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
从库复制状态中需要注意观察的几个参数
MariaDB [(none)]> show slave status\G
Master_Log_File: master-log.000001 #开始复制的主库的日志文件
Read_Master_Log_Pos: 474 #主库日志文件中的起始位置：pos值
Slave_IO_Running: Yes #从库的I/O线程状态
Slave_SQL_Running: Yes #从库的SQL线程状态
Seconds_Behind_Master: 0 #从库数据落后主库多少
6. 查看主从复制线程
主库dump线程
MariaDB [(none)]> show processlist\G
| 2 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
| 3 | yufu | 192.168.214.130:40046 | NULL | Binlog Dump | 1317 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | 0.000 |
主库连接了几个从库，就会有几个dump线程
从库I/O线程与SQL线程(SQL线程在工作时才可以看到)
MariaDB [(none)]> show processlist;
+----+-------------+-----------+------+---------+------+------------------------------
| 3 | system user | | NULL | Connect | 1908 | Waiting for master to send event | NULL | 0.000 |
| 4 | system user | | NULL | Connect | 8 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | 0.000 |
+----+-------------+-----------+------+---------+------+------------------------------
3 rows in set (0.00 sec)
7. 写入数据测试
主库：[root@master ~]# mysql < /opt/hello.sql
登录从库查看数据：
MariaDB [(none)]> use hellosb
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
主从读写分离
通常主从架构的复制中，从库是不提供写操作的，由主库负责写操作，从库负责读操作，但禁止写操作，以免数据错乱，主从读写分离一定程度上分担了主库的压力，实现了负载的作用。因此需要设置从库的只读操作；
设置从库为 read only
vim /etc/my.cnf
[mysqld]
read_only=on #添加
注意的地方：在my.cnf中配置read only限制对具有supper权限的用户无效，如果禁止所有用户写操作，则在从库本地开启一个会话，执行 flush tables with read lock; 即可禁所有用户写操作
加强复制的事务安全
为了更好地保证数据的完整性和安全性，每个SQL语句执行完成时都要写binlog，为了保证binlog的安全，mysql引入了sync_binlog参数来控制binlog刷新到磁盘的频率；
MariaDB [(none)]> show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 0 |
+---------------+-------+
1 row in set (0.01 sec)
在默认情况下，sync_binlog=0,表示mysql不控制binlog的刷新，由文件系统自己控制文件系统缓存刷新。
如果sync_binlog>0,则表示每次sync_binlog事务提交，mysql调用文件系统的刷新操作将binlog刷新到磁盘。
比如：当sync_binlog=1，表示每一次事务提交，mysql都需要把binlog刷新到磁盘，这样的话，数据库主机发生故障，系统最多损失最近的一个事务的数据，因为上一次的事务提交时已经把binlog刷新到了磁盘，设置sync_binlog=1，尽最大可能保证数据安全，但是在多个事务并发提交时，同时高频率的刷新binlog对I/O的影响比较明显，会影响mysql的性能。
除此外，主库节点上还可以有选择地开启如下参数
如果使用的是innodb，最好开启
innodb_flush_logs_at_trx_commit=ON #每次事务提交立即同步日志写磁盘
innodb_support_xa=ON #默认值，分布式事务MariaDB10.3.0废除
sync_master_info=# #多少次事件后master.info同步到磁盘
在从节点上可以设置如下：
skip_slave_start=ON #不自动启动slave
sync_relay_log=# #多少次写后同步relay log到磁盘,非必须
sync_relay_log_info=# #多少次事务后同步relay-log.info到磁盘，非必须
双主复制架构
mysql双主复制架构就是两台主机互为主从，使用双主复制会有数据不一致的风险，因此要谨慎使用，另外是需要对自增字段进行设定，否则会导致数据错乱。
因此，在上面的主从基础上，设置双主复制，需要在原来的主节点开启中继日志，在原来的从节点开启二进制日志，并新建复制用户，此外，双主各节点还要设置各自自动增长id规则，配置步奏如下：
配置步奏：
1. 各节点使用一个唯一server-id
2. 都启用binary log和relay log
3，创建拥有复制权限的用户账号（主从）
4，定义自动增长id字段的数值范围为奇偶
5， 均把对方指定为主节点，并启动复制线程
在原来的主库上
log_bin=master-log
server_id= 1
innodb_file_per_table=on
sync_binlog=1
relay_log=relay-log
auto_increment_offset=1 #开始点
auto_increment_increment=2 #增长幅度
在原来的从库上
新建复制用户
MariaDB [(none)]> grant replication slave on *.* to yufu@'192.168.214.142' identified by 'centos';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
my.cnf内容修改如下：
log_bin=master-log
relay_log=relay-log
server_id= 2
innodb_file_per_table=on
sync_binlog=1
auto_increment_offset=2 #开始点
auto_increment_increment=2 #增长幅度
主从库修改配置后重启服务
在原来主节点上
添加复制从库的master信息
MariaDB [(none)]> change master to master_host='192.168.214.130',master_port=3306,master_user='yufu',master_password='centos',master_log_file='master-log.000001',master_log_pos=245;
Query OK, 0 rows affected (0.86 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
启动主从slave
start slave;
测试数据写入
在原来的主库新建db1库，另一个库上查看
MariaDB [(none)]> create database db1;
Query OK, 1 row affected (1.88 sec)
#对方查看
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
在原来的主库新建db2库，另一个库上查看
MariaDB [(none)]> create database db2;
Query OK, 1 row affected (0.01 sec)
#对方查看
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
7 rows in set (0.00 sec)
半同步复制
默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主
库把binlog日志发送给从库即结束，并不验证从库是否接收完毕。这意味着当
主服务器或从服务器端发生故障时，有可能从服务器没有接收到主服务器发送
过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢
复时造成数据的丢失
实现半同步复制要在主从节点上安装相应的插件来实现主节点安装：semisync_master.so，从节点安装：semisync_slave.so，
过程如下：
安装主节点插件：
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; #安装主节点插件
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show plugins; #查看已安装插件
安装从节点插件
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
启用主节点
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)
#查看启用状态
MariaDB [(none)]> show global variables like '%semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.00 sec)
启用从节点
MariaDB [(none)]> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.01 sec)
#查看启用状态
MariaDB [(none)]> show global variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.01 sec)
注意： 配置了半同步复制后，要重启从库的I/O_THREAD线程
MariaDB [(none)]> stop slave io_thread;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
测试写入数据后查看半同步复制的详细状态信息
查看主库更详细的信息：
MariaDB [db4]> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 1466 |
| Rpl_semi_sync_master_net_wait_time | 2932 |
| Rpl_semi_sync_master_net_waits | 2 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 3 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 473 |
| Rpl_semi_sync_master_tx_wait_time | 947 |
| 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)
上面的一些信息：
Rpl_semi_sync_master_status ：值为ON ，表示半同步复制处于打开状态
Rpl_semi_sync_master_yes_tx： 值为2， 表示主库当前有两个事务通过半同步同步
Rpl_semi_sync_master_no_tx ： 值为3，表示主库当前有3个事务不是通过半同步模式下从库即使响应的(这个参数比较重要，会经常观察对比)
半同步复制超时
在主库上有一个参数，定义半同步复制的超时时间，默认时间是10秒，如果因为从库网络故障或者宕机，主库上提交的操作会被阻塞相应的时长，超过时长后主库会自动关闭半同步模式启用异步复制模式。等从库恢复正常后，主库会自动切换成半同步模式。
查看同步超时时间
MariaDB [db4]> show variables like '%semi%time%';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| rpl_semi_sync_master_timeout | 10000 | # 默认10秒
+------------------------------+-------+
1 row in set (0.00 sec)
设置半同步超时时间
MariaDB [db4]> set global rpl_semi_sync_master_timeout = 30000;
Query OK, 0 rows affected (0.00 sec)
这里把超时时间设置成30秒后，然后在从库上挡住主的ip，禁止通行
从库上添加防火墙禁止主从通信：
iptables -A INPUT -s 192.168.214.142 -j DROP
主库再写入数据观察：
MariaDB [db4]> insert into tb1 values (40,'demo40');
Query OK, 1 row affected (30.01 sec)
可以看到第一条数据等待了30秒后在执行完，在等待过程中查看主库有等待操作的wait线程
MariaDB [(none)]> show processlist\G
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 7073
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
Progress: 0.000
*************************** 2. row ***************************
Id: 10
User: root
Host: localhost
db: db4
Command: Query
Time: 4
State: Waiting for semi-sync ACK from slave
Info: insert into tb1 values (40,'demo40')
Progress: 0.000
此时再看半同步状态Rpl_semi_sync_master_status 的值已经为 OFF
MariaDB [db4]> show status like '%semi%';
+--------------------------------------------+-----------+
| Variable_name | Value |
+--------------------------------------------+-----------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 10137213 |
| Rpl_semi_sync_master_net_wait_time | 121646565 |
| Rpl_semi_sync_master_net_waits | 12 |
| Rpl_semi_sync_master_no_times | 9 |
| Rpl_semi_sync_master_no_tx | 23 |
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 414 |
| Rpl_semi_sync_master_tx_wait_time | 1244 |
| Rpl_semi_sync_master_tx_waits | 3 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 5 |
+--------------------------------------------+-----------+
14 rows in set (0.00 sec)
再次写入数据时就变成了异步复制
MariaDB [db4]> insert into tb1 values (41,'demo41');
Query OK, 1 row affected (0.09 sec)
MariaDB [db4]> insert into tb1 values (42,'demo42');
Query OK, 1 row affected (0.09 sec)
清除从库防火墙规则，重启slave io_thread线程，查看是否自动切换为半同步模式
iptables -F
MariaDB [db4]> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)
MariaDB [db4]> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
如果 IO_THREAD不重启，主动的状态无法自动切换
重启io_thread后主库的Rpl_semi_sync_master_status 状态会自动变为ON
MariaDB [db4]> show status like '%semi%';
+--------------------------------------------+-----------+
| Variable_name | Value |
+--------------------------------------------+-----------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 8114248 |
| Rpl_semi_sync_master_net_wait_time | 121713734 |
| Rpl_semi_sync_master_net_waits | 15 |
| Rpl_semi_sync_master_no_times | 9 |
| Rpl_semi_sync_master_no_tx | 24 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 515 |
| Rpl_semi_sync_master_tx_wait_time | 2062 |
| Rpl_semi_sync_master_tx_waits | 4 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 7 |
+--------------------------------------------+-----------+
14 rows in set (0.00 sec)
再次写入数据：
MariaDB [db4]> insert into tb1 values (43,'demo43');
Query OK, 1 row affected (0.31 sec)
MariaDB [db4]> insert into tb1 values (44,'demo44');
Query OK, 1 row affected (0.01 sec)
从库检查数据同步正常
MariaDB [db4]> select * from tb1 where id in (33,34);
+------+--------+
| id | name |
+------+--------+
| 33 | demo33 |
| 34 | demo34 |
+------+--------+
2 rows in set (0.00 sec)
复制过滤器
复制过滤就是让从节点复制主节点上指定的某些库或者是某张表。有两种方式可以实现数据复制过滤：
- 主服务节点仅向二进制日志中记录与特定数据库相关的事件（只记录指定的库日志文件），这种方式存在一个问题：如果主库出现故障，其他没有记录事务的库数据无法基于时间点还原(谨慎使用)
binlog_do_db= #数据库白名单列表
binlog_ignore_db= #数据库黑名单列表
- 从服务器sql线程在重现relay log中继日志事件时，仅读取与特定数据库相关的事件并应用，这种方式会造成网络及磁盘I/O浪费。
replicate_do_db=
replicate_ignore_db=
replicate_do_table=
replicate_ignore_table=
以上设置在命令行设置临时生效，重启后失效，通常在配置文件中设置
使用第二种从库重写过滤的方式做复制过滤
设置只重写的库：
[mysqld]
replicate_do_db=db4
mysqld段添加
查看过滤的库
MariaDB [db4]> show global variables like '%replicate%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| replicate_annotate_row_events | OFF |
| replicate_do_db | db4 |
| replicate_do_table | |
| replicate_events_marked_for_skip | replicate |
| replicate_ignore_db | |
| replicate_ignore_table | |
| replicate_wild_do_table | |
| replicate_wild_ignore_table | |
+----------------------------------+-----------+
8 rows in set (0.00 sec)
测试复制过滤
在主库db4中添加数据
MariaDB [db4]> insert into tb1 values (55,'demo55');
Query OK, 1 row affected (0.00 sec)
MariaDB [db4]> insert into tb1 values (56,'demo56');
Query OK, 1 row affected (0.00 sec)
在主库db5中创建一个表写入数据
MariaDB [db4]> use db5;
Database changed
MariaDB [db5]> create table tb2 (
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.15 sec)
MariaDB [db5]> insert into tb2 values (1,'demo');
Query OK, 1 row affected (0.08 sec)
从库查看db4中第55,56条数据
MariaDB [db4]> select * from tb1 where id in (55,56);
+------+--------+
| id | name |
+------+--------+
| 55 | demo55 |
| 56 | demo56 |
+------+--------+
2 rows in set (0.00 sec)
从库查看db5中的数据
MariaDB [db4]> use db5;
Database changed
MariaDB [db5]> show tables;
Empty set (0.00 sec)
复制的监控
- 清理日志
使用purge指令
查看日志信息：
show binary logs;
purge binary logs to 'master-bin.00003'; # 03之前的文件(01,02)被删除
2 . 复制监控
一些常用查看状态指令：
show master status;
show binlog events;
show binary logs;
show slave status;
show process list;
3.查看从库比主库落后多少时间
show slave status；
seconds_behind_master:0
