MySQL主从以外的其他集群及维护和解决方案

实现级联复制

需要在中间的从服务器启用以下配置，实现中间slave节点能将master的二进制日志在本机进行数据库更新，并且也同时更新本机的二进制，从而实现级联复制

[mysqld]
server-id=18
log_bin
log_slave_updates #级联复制中间节点的必选项,MySQL8.0此为默认值,可以不要人为添加,其它版本默认不开启
read-only

案例：三台主机实现级联复制

master --> slave1 -->slave2

#在10.0.0.8充当master
#在10.0.0.18充当级联slave
#在10.0.0.28充当slave

#在master实现
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=8
log-bin

[root@centos8 ~]#systemctl restart mariadb
[root@centos8 ~]#mysql

MariaDB [(none)]> grant replication slave on *.* to repluser@'10.0.0.%' identified by 'ayaka'; #切记 8.0不支持这么写

[root@centos8 ~]#mysqldump -A -F --single-transaction --master-data=1 >/data/all.sql

[root@centos8 ~]#scp /data/all.sql 10.0.0.18:/data
[root@centos8 ~]#scp /data/all.sql 10.0.0.28:/data

#在中间级联slave实现
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18
log-bin
read-only
log_slave_updates #级联复制中间节点的必选项,MySQL8.0此为默认值,可以不用人为添加
[root@centos8 ~]#systemctl restart mariadb

#还原数据库
#mysql> set sql_log_bin=off
#mysql> source /data/all.sql;
#mysql> set sql_log_bin=on

#[root@centos8 ~]#grep '^CHANGE MASTER' /data/all.sql
#CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=523;

[root@centos8 ~]#vim /data/all.sql
CHANGE MASTER TO
MASTER_HOST='master节点的iP',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=523;

[root@centos8 ~]#mysql
MariaDB [(none)]> set sql_log_bin=0;
MariaDB [(none)]> source /data/all.sql
MariaDB [(none)]> show master logs; #记录二进制位置，给第三个节点使用
MariaDB [(none)]> set sql_log_bin=1;
MariaDB [(none)]> start slave;

#在第三个节点slave上实现
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=28
read-only

[root@centos8 ~]#systemctl restart mariadb

[root@centos8 ~]#vim /data/all.sql
CHANGE MASTER TO
MASTER_HOST='中间节点的IP',
MASTER_USER='repluser',
MASTER_PASSWORD='ayaka',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=344;

[root@centos8 ~]#mysql < /data/all.sql
[root@centos8 ~]#mysql -e 'start slave;'

主主复制

主主复制：两个节点，都可以更新数据，并且互为主从

容易产生的问题：数据不一致；因此慎用

考虑要点：自动增长id

配置一个节点使用奇数id

auto_increment_offset=1 #开始点
auto_increment_increment=2 #增长幅度

另一个节点使用偶数id

auto_increment_offset=2
auto_increment_increment=2

主主复制的配置步骤：

(1) 各节点使用一个惟一server_id

(2) 都启动binary log和relay log

(3) 创建拥有复制权限的用户账号

(4) 定义自动增长id字段的数值范围各为奇偶

(5) 均把对方指定为主节点，并启动复制线程

范例：实现两个节点的主主复制模型

#在第一个master节点上实现
[root@master1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=8
log-bin
auto_increment_offset=1 #开始点
auto_increment_increment=2 #增长幅度

[root@master1 ~]#systemctl start mariadb
[root@master1 ~]#mysql
ariaDB [(none)]>show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 28303 |
| mariadb-bin.000002 | 386 |
+--------------------+-----------+
2 rows in set (0.000 sec)

MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.100.%' identified by 'ayaka';

#在第二个master节点上实现
[rootmaster2 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18
log-bin
auto_increment_offset=2 #开始点
auto_increment_increment=2 #增长幅度

[root@master2 ~]#systemctl start mariadb
[root@master2 ~]#mysql
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.100.8',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='magedu',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mariadb-bin.000002',
-> MASTER_LOG_POS=386;
Query OK, 0 rows affected (0.019 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.003 sec)
MariaDB [(none)]> show master logs; #查看二进制位置
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 28303 |
| mariadb-bin.000002 | 344 |
+--------------------+-----------+
2 rows in set (0.001 sec)

#在第一个master节点上实现
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.100.18',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='magedu',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mariadb-bin.000002',
-> MASTER_LOG_POS=344;
Query OK, 0 rows affected (0.007 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)

MariaDB [db1]> create table t1(id int auto_increment primary key,name char(10));


#两个节点分别插入数据
#在第一个节点上执行
MariaDB [db1]> create database db1;
MariaDB [db1]> insert t1 (name) values('user1');
#在第二个节点上执行
MariaDB [db1]> insert t1 (name) values('user2');

#两个节点同时插入数据
MariaDB [db1]> insert t1 (name) values('userX');
MariaDB [db1]> select * from t1;
+----+-------+
| id | name |
+----+-------+
| 1 | user1 |
| 2 | user2 |
| 3 | userX |
| 4 | userX |
+----+-------+
4 rows in set (0.001 sec)

#两个节点同时创建数据库，发生复制冲突
MariaDB [db1]> create database db2;
MariaDB [db1]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.18
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000002
Read_Master_Log_Pos: 1029
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 1110
Relay_Master_Log_File: mariadb-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1007
Last_Error: Error 'Can't create database 'db2'; database
exists' on query. Default database: 'db2'. Query: 'create database db2'
Skip_Counter: 0
Exec_Master_Log_Pos: 897
Relay_Log_Space: 1553
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1007
Last_SQL_Error: Error 'Can't create database 'db2'; database
exists' on query. Default database: 'db2'. Query: 'create database db2'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 18
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Slave_DDL_Groups: 2
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 2
1 row in set (0.003 sec)

半同步复制

默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库即结束，并不验证从库是否接收完毕。这意味着当主服务器或从服务器端发生故障时，有可能从服务器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失

MySQL5.5版本为了保证主从数据的一致性问题。加入了半同步复制的组件(插件),可以控制从库IO线程是否将relaylog落盘，一旦落盘通过插件返回ACK给主库ACK_REC。接受到ACK之后，主库的事务才能提交成功。在默认情况下，如果超过10秒没有返回ACK，此次复制行为会切换为异步复制

在MySQL5.6,5.7 当中也加入了一些比较好的特性，也不能完全保证的数据一致。如果生产业务比较关注主从最终一致(比如:金融等)。推荐可以使用MGR的架构，或者PXC等一致性架构

优点

缺点1： 幻读
当用户提交一个事务，该事务已经写入redo日志和binlog日志，但该事务还没写入从库，此时处在waiting slave dump处，此时另一个用户可以读取到这条数据，而他自己却不能；
缺点2：数据丢失
一个提交的事务在waiting slave dump处crash后，主库将比从库多一条数据

增强半同步复制(MySQL5.7新增功能)

#MySQL8.0的默认值
rpl_semi_rsync_master_wait_point=after_sync

优点

改善1：解决幻读
当用户发起一个事务，该事务先写入二进制后，再向从库进行同步，由于还没有完成提交，此时其他用户无法读取到该数据，解决了幻读
改善2：解决数据丢失
一个事务在waiting slave dump处crash掉后，可以通过观察从库上是否存在主库的last gtid值，如果存在，这条数据正常恢复，如果不存在则删除主库的那条多余的GTID值，然后恢复，保证了数据的完整性

半同步复制实现：

官方文档：

https://dev.mysql.com/doc/refman/8.0/en/replication-semisync.html
https://dev.mysql.com/doc/refman/5.7/en/replication-semisync.html
https://mariadb.com/kb/en/library/semisynchronous-replication/

范例: CentOS8 在MySQL8.0 实现半同步复制

#查看插件文件
[root@centos8 ~]#rpm -ql mysql-server |grep semisync
/usr/lib64/mysql/plugin/semisync_master.so
/usr/lib64/mysql/plugin/semisync_slave.so

#master服务器配置
[root@master ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=8
log-bin
rpl_semi_sync_master_enabled=ON #修改此行,需要先安装semisync_master.so插件后,再重启,否则无法启动
rpl_semi_sync_master_timeout=3000 #设置3s内无法同步，也将返回成功信息给客户端


#slave服务器配置
[root@slave1 ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=18
rpl_semi_sync_slave_enabled=ON #修改此行,需要先安装semisync_slave.so插件后,再重启,否则无法启动

[root@slave2 ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=28
rpl_semi_sync_slave_enabled=ON #修改此行,需要先安装semisync_slave.so插件后,再重启,否则无法启动

#主服务器配置:
mysql>INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; #永久安装插
件
mysql>UNINSTALL PLUGIN rpl_semi_sync_master ;
mysql>SHOW PLUGINS; #查看插件
mysql>SET GLOBAL rpl_semi_sync_master_enabled=1; #临时修改变量
mysql>SET GLOBAL rpl_semi_sync_master_timeout = 3000; #超时长1s,默认值为10s
mysql>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_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 2 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 2 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| 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 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

#从服务器配置:
mysql>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
mysql>SET GLOBAL rpl_semi_sync_slave_enabled=1; #临时修改变量
mysql>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.00 sec)
#注意:如果已经实现主从复制,需要stop slave;start slave;
mysql> stop slave;
mysql> start slave;
mysql> SHOW GLOBAL STATUS LIKE '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)

范例：CentOS 8 在Mariadb-10.3.11上实现实现半同步复制

#在master实现，启用半同步功能
[root@master ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=8
log-bin
plugin-load-add = semisync_master
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=3000 #设置3s内无法同步，也将返回成功信息给客户端

[root@centos8 ~]#systemctl restart mariadb
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+---------------------------------------+--------------+
| Variable_name | Value |
+---------------------------------------+--------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 3000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_COMMIT |
| rpl_semi_sync_slave_delay_master | OFF |
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_kill_conn_timeout | 5 |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------------+--------------+
9 rows in set (0.002 sec)

MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_get_ack | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_request_ack | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| 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 |
| Rpl_semi_sync_master_yes_tx | 0 |
| Rpl_semi_sync_slave_send_ack | 0 |
| Rpl_semi_sync_slave_status | OFF |
+--------------------------------------------+-------+
18 rows in set (0.001 sec)

#在其它所有slave节点上都实现，启用半同步功能
[root@slave ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18
plugin_load_add = semisync_slave
rpl_semi_sync_slave_enabled=ON

[root@slave ~]#systemctl restart mariadb
[root@slave ~]#mysql
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+---------------------------------------+--------------+
| Variable_name | Value |
+---------------------------------------+--------------+
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_COMMIT |
| rpl_semi_sync_slave_delay_master | OFF |
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_kill_conn_timeout | 5 |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------------+--------------+
9 rows in set (0.001 sec)

MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_get_ack | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_request_ack | 0 |
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| 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 |
| Rpl_semi_sync_master_yes_tx | 0 |
| Rpl_semi_sync_slave_send_ack | 0 |
| Rpl_semi_sync_slave_status | ON |
+--------------------------------------------+-------+
18 rows in set (0.001 sec)

#在master上实现
MariaDB [db1]> SHOW GLOBAL STATUS LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 2 | #两个从节点
| Rpl_semi_sync_master_get_ack | 4 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 4 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 1 |
| Rpl_semi_sync_master_request_ack | 3 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 1177 |
| Rpl_semi_sync_master_tx_wait_time | 2355 |
| 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 |
| Rpl_semi_sync_slave_send_ack | 0 |
| Rpl_semi_sync_slave_status | OFF |
+--------------------------------------------+-------+
18 rows in set (0.001 sec)

#测试
#在master实现，创建数据库，立即成功
MariaDB [db1]> create database db2;
Query OK, 1 row affected (0.004 sec)

#在所有slave节点实现，停止复制线程
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.011 sec)

#在master实现，创建数据库，等待3s才能成功
MariaDB [db1]> create database db3;
Query OK, 1 row affected (3.003 sec)

#在任意一个slave节点实现，恢复复制线程
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.006 sec)

#在master实现，创建数据库，立即成功
MariaDB [db1]> create database db4;
Query OK, 1 row affected (0.002 sec

#在所有从节点停止同步线程,在主节点可以看到以下日志信息
MariaDB [db1]> stop slave;
[root@centos8 ~]#tail /var/log/mariadb/mariadb.log
2020-08-29 10:11:19 15 [Warning] IP address '10.0.0.28' could not be resolved:
Name or service not known
2020-08-29 10:11:19 15 [Note] Start binlog_dump to slave_server(28),
pos(mariadb-bin.000001, 330)
2020-08-29 10:11:19 15 [Note] Start semi-sync binlog_dump to slave (server_id:
28), pos(mariadb-bin.000001, 330)
2020-08-29 10:12:34 15 [Note] Stop semi-sync binlog_dump to slave (server_id:
28)
2020-08-29 10:16:05 17 [Note] Start binlog_dump to slave_server(28),
pos(mariadb-bin.000002, 27378670)
2020-08-29 10:16:05 17 [Note] Start semi-sync binlog_dump to slave (server_id:
28), pos(mariadb-bin.000002, 27378670)
2020-08-29 10:16:31 12 [Note] Stop semi-sync binlog_dump to slave (server_id:
18)
2020-08-29 10:16:37 17 [Note] Stop semi-sync binlog_dump to slave (server_id:
28)
2020-08-29 10:17:19 14 [Warning] Timeout waiting for reply of binlog (file:
mariadb-bin.000002, pos: 27378922), semi-sync up to file mariadb-bin.000002,
position 27378795.
2020-08-29 10:17:19 14 [Note] Semi-sync replication switched OFF.

范例：CentOS 7 实现Mariadb 5.5.65 的半同步复制

#主服务器配置:
MariaDB [(none)]>INSTALL PLUGIN rpl_semi_sync_master SONAME
'semisync_master.so';
MariaDB [(none)]>UNINSTALL PLUGIN rpl_semi_sync_master ;
MariaDB [(none)]>SHOW PLUGINS; #查看插件
MariaDB [(none)]>SET GLOBAL rpl_semi_sync_master_enabled=1;
MariaDB [(none)]>SET GLOBAL rpl_semi_sync_master_timeout = 1000; #超时长1s,默认值
为10s
MariaDB [(none)]>SHOW GLOBAL VARIABLES LIKE '%semi%';
MariaDB [(none)]>SHOW GLOBAL STATUS LIKE '%semi%';
#从服务器配置:
MariaDB [(none)]>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
MariaDB [(none)]>SET GLOBAL rpl_semi_sync_slave_enabled=1;

复制过滤器

让从节点仅复制指定的数据库，或指定数据库的指定表

复制过滤器两种实现方式：

(1) 服务器选项：主服务器仅向二进制日志中记录与特定数据库相关的事件
缺点：基于二进制还原将无法实现；不建议使用
优点: 只需要在主节点配置一次即可
注意：此项和 binlog_format相关

参考：https://mariadb.com/kb/en/library/mysqld-options/#-binlog-ignore-db

vim /etc/my.cnf
binlog-do-db=db1 #数据库白名单列表，不支持同时指定多个值，如果想实现多个数据库需多行实现
binlog-do-db=db2
binlog-ignore-db= #数据库黑名单列表

注意：

This option will not work with cross-database updates with statement-based
logging. See the Statement-Based Logging section for more information.
This option can not be set dynamically.
When setting it on the command-line or in a server option group in an option
file, the option does not accept a comma-separated list. If you would like to
specify multiple filters, then you need to specify the option multiple times.

(2) 从服务器SQL_THREAD在relay log中的事件时，仅读取与特定数据库(特定表)相关的事件并应用于本地
缺点：会造成网络及磁盘IO浪费,在所有从节点都要配置
优点: 不影响二进制备份还原
从服务器上的复制过滤器相关变量

replicate_do_db="db1,db2,db3" #指定复制库的白名单，变量可以指定逗号分隔的多个值，选项不支持多值,只能分别写多行实现
replicate_ignore_db= #指定复制库黑名单
replicate_do_table= #指定复制表的白名单
replicate_ignore_table= #指定复制表的黑名单
replicate_wild_do_table= foo%.bar% #支持通配符
replicate_wild_ignore_table=

When setting it dynamically with SET GLOBAL, the system variable accepts a
comma-separated list of filters.
When setting it on the command-line or in a server option group in an option
file, the system variable does not accept a comma-separated list. If you would
like to specify multiple filters, then you need to specify the system variable
multiple times.

注意：跨库的更新将无法同步

范例:

[mysqld]
replicate_do_db=db1
replicate_do_db=db2
replicate_do_db=db3

范例: 通过二进制日志服务器选项实现过滤器

[mysqld]
server-id=8
log-bin
binlog-do-db=db1
binlog-do-db=db2
binlog-do-db=hellodb

范例: 通过系统变量实现过滤器

MariaDB [(none)]> set global replicate_do_db='db1,hellodb';
ERROR 1198 (HY000): This operation cannot be performed as you have a running
slave ''; run STOP SLAVE '' first

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.009 sec)

MariaDB [(none)]> set global replicate_do_db='db1,hellodb';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> select @@replicate_do_db;
+-------------------+
| @@replicate_do_db |
+-------------------+
| db1,hellodb |
+-------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)

GTID复制

GTID（Global Transaction ID 全局事务标识符） MySQL 5.6 版本开始支持,但不太成熟,建议使用MySQL5.7以上版本的GTID功能

MySQL5.6版本出现没有默认开启，5.7中即使不开启也有匿名的GTID记录。

开启GTID功能可以支持多DUMP线程的并发复制,而且MySQL5.6实现了基于库级别多SQL线程并发。在MySQL5.7利用 GTID的 Logic clock 逻辑时钟。保证了同库级别下的事务顺序问题。即可以实现基于事务级别的并发回放。从而大大减少了同步的延迟

同时GTID具有幂等性特性,即多次执行结果是一样的

利用 GTID复制不像传统的复制方式（异步复制、半同步复制）需要找到binlog文件名和POS点，只需知道master的IP、端口、账号、密码即可。开启GTID后，执行change master to master_auto_postion=1即可，它会自动寻找到相应的位置开始同步

GTID 优点:

保证事务全局统一
截取日志更加方便。跨多文件，判断起点终点更加方便
判断主从工作状态更加方便
传输日志，可以并发传输。SQL回放可以更高并发
主从复制构建更加方便

GTID 架构

GTID = server_uuid:transaction_id，在一组复制中，全局唯一

server_uuid 来源于 /var/lib/mysql/auto.cnf

GTID服务器相关选项

gtid_mode 	#gtid模式
enforce_gtid_consistency	 #保证GTID安全的参数

GTID配置范例

主服务器

vim /etc/my.cnf
server-id=1
gtid_mode=ON
enforce_gtid_consistency
log-bin=mysql-bin #可选

systemctl restart mysqld

mysql> grant replication slave on *.* to 'repluser'@'10.0.0.%' identified by 'ayaka';

从服务器

vim /etc/my.cnf
server-id=2
gtid_mode=ON
enforce_gtid_consistency

systemctl restart mysqld

#如果主服务器和从服务器数据不一致,需要先将主库数据备份还原至从库,再执行下面操作
mysqldump -A --master-data=2 > /backup/full.sql

mysql>CHANGE MASTER TO MASTER_HOST='10.0.0.100',
MASTER_USER='repluser',
MASTER_PASSWORD='ayaka',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1; #使用GTID

mysql>start slave;
#注意观察:Retrieved_Gtid_set和Executed_Gtid_Set这两个值,对比主节点执行show master status的值,如果相同表示同步完成

复制的监控和维护

清理日志

PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
RESET MASTER TO # #mysql 不支持
RESET SLAVE [ALL]

复制监控

SHOW MASTER STATUS
SHOW BINARY LOGS
SHOW BINLOG EVENTS
SHOW SLAVE STATUS
SHOW PROCESSLIST

从服务器是否落后于主服务

Seconds_Behind_Master：0

如何确定主从节点数据是否一致

percona-toolkit

数据不一致如何修复

删除从数据库，重新复制

复制的问题和解决方案

数据损坏或丢失

Master：MHA + semisync replication
Slave：重新复制

不唯一的 server id

解决方法: 重新复制

复制延迟

升级到MySQL5.7以上版本(5.7之前的版本，没有开GTID之前，主库可以并发事务，但是dump传输时是串行)利用GTID(MySQL5.6需要手动开启,MySQL5.7以上默认开启)支持并发传输binlog及并行多个SQL线程
减少大事务,将大事务拆分成小事务
减少锁
sync_binlog=1 加快binlog更新时间,从而加快日志复制
需要额外的监控工具的辅助
多线程复制：对多个数据库复制
一从多主：Mariadb10 版后支持

MySQL 主从数据不一致

造成主从不一致的原因

主库binlog格式为Statement，同步到从库执行后可能造成主从不一致。
主库执行更改前有执行set sql_log_bin=0，会使主库不记录binlog，从库也无法变更这部分数据。
从节点未设置只读，误操作写入数据
主库或从库意外宕机，宕机可能会造成binlog或者relaylog文件出现损坏，导致主从不一致
主从实例版本不一致，特别是高版本是主，低版本为从的情况下，主数据库上面支持的功能，从数据库上面可能不支持该功能
主从sql_mode 不一致
MySQL自身bug导致

主从不一致修复方法

将从库重新实现
- 虽然这也是一种解决方法，但是这个方案恢复时间比较慢，而且有时候从库也是承担一部分的查询操作的，不能贸然重建。
使用percona-toolkit工具辅助
- PT工具包中包含pt-table-checksum和pt-table-sync两个工具，主要用于检测主从是否一致以及修复数据不一致情况。这种方案优点是修复速度快，不需要停止主从辅助，缺点是需要知识积累，需要时间去学习，去测试，特别是在生产环境，还是要小心使用
  
  关于使用方法，可以参考下面链接：https://www.cnblogs.com/feiren/p/7777218.html
手动重建不一致的表
- 在从库发现某几张表与主库数据不一致，而这几张表数据量也比较大，手工比对数据不现实，并且重做整个库也比较慢，这个时候可以只重做这几张表来修复主从不一致
- 这种方案缺点是在执行导入期间需要暂时停止从库复制，不过也是可以接受的

范例：A,B,C这三张表主从数据不一致

1、从库停止Slave复制
mysql>stop slave;

2、在主库上dump这三张表，并记录下同步的binlog和POS点
mysqldump -uroot -payaka -q --single-transaction --master-data=2 testdb A B
C >/backup/A_B_C.sql

3、查看A_B_C.sql文件，找出记录的binlog和POS点
head A_B_C.sql
例如:MASTERLOGFILE='mysql-bin.888888', MASTERLOGPOS=666666;

#以下指令是为了保障其他表的数据不丢失，一直同步直到那个点结束，A,B,C表的数据在之前的备份已经生成了一份快照，只需要导入进入，然后开启同步即可
4、把A_B_C.sql拷贝到Slave机器上，并做指向新位置
#虽然你停止了 Slave，但从库的 Relay Log（中继日志）里可能还堆积着之前没跑完的数据这一步让从库跑完剩余的任务，恰好停在主库刚才备份的那一刻。此时，从库除了 A、B、C 三张表，其他表的数据已经和主库导出的那一刻完全对齐了。
mysql>start slave until MASTERLOGFILE='mysql-bin.888888',
MASTERLOGPOS=666666;

5、在Slave机器上导入A_B_C.sql
mysql -uroot -pmagedu testdb
mysql>set sql_log_bin=0;
mysql>source /backup/A_B_C.sql
mysql>set sql_log_bin=1;
#在执行第五步之前，建议先在从库 DROP 掉 A、B、C 表，或者确保 SQL 文件中有 DROP TABLE IF EXISTS，否则导入时会报“表已存在”或主键冲突的错误。
6、导入完毕后，从库开启同步即可。
mysql>start slave;

如何避免主从不一致

主库binlog采用ROW格式
主从实例数据库版本保持一致
主库做好账号权限把控，不可以执行set sql_log_bin=0
从库开启只读，不允许人为写入
定期进行主从一致性检验

MySQL主从以外的其他集群 及维护和解决方案