主从复制

主从复制前言

主从复制模式

异步复制

默认值，主节点数据写入成功则立即通知用户成功
优点：速度快，用户体验性好
缺点：主从数据不一致比较常见，解决方法？？？？

同步复制

主节点和从节点都将数据写入成功才通知用户成功
优点：数据安全性好
缺点：速度慢，用户体验性差

常见架构

一主一从

Master –> Slave

一主多从

Master –> Slaves

级联复制

一主一从，一从在带多从
Master –> Slave –> Slaves

主主

虽然名为双主架构，但还是当主从来用
Master –> Master(Slave)

主从复制相关线程

主节点：

dump Thread
- 为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events

从节点：

I/O Thread
- 向Master请求二进制日志事件，并保存于中继日志中
SQL Thread
- 从中继日志中读取日志事件，在本地完成重放

主从复制相关文件

主节点：

bin-log

从节点：

mariadb-relay-bin.00000#

中继日志，保存从主节点复制过来的二进制日志,本质就是二进制日志

[root@slave ~]#file /var/lib/mysql/mariadb-relay-bin.000001 
/var/lib/mysql/mariadb-relay-bin.000001: MySQL replication log, server id 18
MySQL V5+, server version 10.3.17-MariaDB-log

master.info
- 用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等
relay-log.info
- 保存在当前slave节点上已经复制的当前二进制日志和本地relay log日志的对应关系

主从复制工作原理

首先当主节点数据发生更新时二进制日志也会随之更新
主节点将更新的二进制日志利用dump线程通过网络发送给从节点
从节点利用IO线程接受二进制日志并保存于中继日志中
最后从节点读取中继日志的内容进而完成数据更新

主从复制相关配置说明

主节点

[root@mysql-master ~]# vim /etc/my.cnf
[mysqld]
######bin-log######
binlog_format=ROW
log_bin=/data/mysql_bin_log/mysql_bin_log
max_binlog_size=1073741824
binlog_cache_size=4m
max_binlog_cache_size=512m
sync_binlog=1
binlog_expire_logs_seconds=2592000
######replication-master######
server-id=8 # 为主节点设置一个全局惟一的ID号，取值范围1 to 4294967295
innodb_flush_log_at_trx_commit=2 # 优化事务日志性能，每次事务提交后写入日志，并每秒刷新一次到磁盘。未刷新日志的事务可能会在崩溃中丢失。
...

从节点

[root@mysql-slave ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
######bin-log######
binlog_format=ROW
log_bin=/data/mysql_bin_log/mysql_bin_log # 从节点同样开启二进制日志，便于后期升主或备份
max_binlog_size=1073741824
binlog_cache_size=4m
max_binlog_cache_size=512m
sync_binlog=1
binlog_expire_logs_seconds=2592000
######replication-slave######
server_id=# # 为从节点设置一个全局惟的ID号
read_only=ON # 设置数据库只读，针对supper user无效，通常设在从节点，但是从节点被提升为新主呢？？？？？？？？？？？？？？？？
innodb_flush_log_at_trx_commit=2 # 优化事务日志性能，每次事务提交后写入日志，并每秒刷新一次到磁盘。未刷新日志的事务可能会在崩溃中丢失。
#可选项
relay_log=relay-log # 可选项，relay log的文件路径，默认值hostname-relay-bin
relay_log_index=relay-log.index  # 可选项，默认值hostname-relay-bin.index
log_slave_updates=ON # 从节点在复制时同时也记录二进制日志

主从复制的实现

参考文档：

注意事项

二进制日志记录格式选择行型 ROW
从节点增量还原时临时关闭二进制日志记录，否则会生成大量的二进制日志
数据库版本统一
实现前停止服务
从节点开启二进制日志便于后期在从节点实现备份从而减轻主节点的压力，需要开启log_slave_updates选项（否则从节点从主节点更新数据后，不会生成相应的二进制日志）（最新版本8.0.26已经自动开启了）
主节点的复制账号最好先于完全备份前创建，否则使用二进制日志同步数据前还需在从节点创建
从节点只能复制记录二进制位置以后的内容
如果主节点之前有数据，则需要进行完全备份在进行配置，没有数据的话那无需备份只需记录二进制日志位置然后在从节点执行同步即可

一主一从

下面演示的是主节点已有数据的情况：

环境说明

hostname	IP
mysql-master-1	10.0.0.8
mysql-slave-1	10.0.0.18

主节点配置

修改主节点配置文件

主要是开启二进制日志、指定唯一的server-id

#准备二进制日志存放目录
[root@mysql-master ~]# mkdir -p /data/mysql_bin_log/
[root@mysql-master ~]# chown -R mysql.mysql /data/mysql_bin_log/

#修改配置文件
[root@mysql-master ~]# vim /etc/my.cnf
[mysqld]
######bin-log######
binlog_format=ROW
log_bin=/data/mysql_bin_log/mysql_bin_log
max_binlog_size=1073741824
binlog_cache_size=4m
max_binlog_cache_size=512m
sync_binlog=1
binlog_expire_logs_seconds=2592000
######replication-master######
server-id=8
innodb_flush_log_at_trx_commit=2
...


#重启服务
systemctl restart mariadb|mysql


#查看配置是否正确
mysql> SHOW variables like 'sql_log_bin';
mysql> SHOW variables like 'binlog_format';
mysql> SHOW MASTER STATUS;
mysql> SHOW MASTER LOGS;

创建有复制权限的用户账号

#创建
mysql> CREATE USER 'repluser'@'10.0.0.%' IDENTIFIED BY 'cqmyg';

#授权，仅授予复制权限即可
mysql> GRANT REPLICATION SLAVE ON *.* TO repluser@'10.0.0.%';

进行完全备份并拷贝到slave节点

#进行完全备份
[root@mysql-master ~]# mysqldump -uroot -p'12345' -A -F --single-transaction --source-data=2 --flush-privileges --hex-blob > all.sql

#复制到远程主机的家目录中
[root@mysql-master ~]# rsync all.sql 10.0.0.18:~

假设此时发生数据更新

mysql> insert hellodb.teachers (name,age,gender)value('xiaohong',18,'F');
Query OK, 1 row affected (0.04 sec)

mysql> select * from hellodb.teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | xiaohong      |  18 | F      |
+-----+---------------+-----+--------+

从节点配置

修改从节点配置文件

主要是启动中继日志、指定server-id、开启二进制日志(便于后期升主或备份）

#准备二进制日志存放目录
[root@mysql-slave-1 ~]# mkdir -p /data/mysql_bin_log/
[root@mysql-slave-1 ~]# chown -R mysql.mysql /data/mysql_bin_log/

#修改配置文件
[root@mysql-slave-1 ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
######bin-log######
binlog_format=ROW
log_bin=/data/mysql_bin_log/mysql_bin_log
max_binlog_size=1073741824
binlog_cache_size=4m
max_binlog_cache_size=512m
sync_binlog=1
binlog_expire_logs_seconds=2592000
######replication-slave######
server_id=18
innodb_flush_log_at_trx_commit=2
log_slave_updates=ON
...

#重启服务
[root@mysql-slave-1 ~]# systemctl restart mariadb|mysql

将完全备份还原并启动复制

使用有复制权限的用户账号连接至主服务器，并启动复制线程

MariaDB [(none)]> HELP CHANGE MASTER TO; #配置文件帮助
CHANGE MASTER TO #注意此选项复制时是否重复

方法一

常用，此方法备份时–source-data=2即可

[root@mysql-slave-1 ~]# mysql -uroot -p12345
...

#关闭二进制日志记录
mysql> set @@sql_log_bin=off;

#导入备份文件
mysql> source all.sql
#导入备份文件方法二（不建议使用，因为会产生大量的二进制日志）
[root@mysql-slave-1 ~]# mysql -uroot -p12345 < all.sql

#恢复二进制日志记录
mysql> set @@sql_log_bin=on;

#查看备份文件的相关复制信息
[root@mysql-slave-1 ~]# head -n100 all.sql|grep 'CHANGE MASTER TO'
CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin_log.000008', MASTER_LOG_POS=156;

#进入mysql执行CHANGE MASTER TO
CHANGE MASTER TO
  MASTER_HOST='10.0.0.8',
  MASTER_USER='repluser',
  MASTER_PASSWORD='cqmyg',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql_bin_log.000008',
  MASTER_LOG_POS=156,
  MASTER_CONNECT_RETRY=10,
  GET_MASTER_PUBLIC_KEY=1; #身份验证插件使用的是caching_sha2_password 并且 没有启动安全连接SSL需要开启此选项
  
#启动
START SLAVE [IO_THREAD|SQL_THREAD]; #不加[]的内容默认两个都启动
STOP SLAVE; #停止服务

#查看结果
SHOW SLAVE STATUS\G;
         Seconds_Behind_Master: NULL #主从复制延迟时间，最好是0，0表示已经同步
         Slave_IO_Running: Yes #IO线程是否开启
         Slave_SQL_Running: Yes #SQL线程是否开启
         Last_IO_Error: #IO线程错误信息
         Last_SQL_Errno: #SQL线程错误信息

#删除复制信息（配置错误需要重新修改的情况）
STOP SLAVE; #需要先停止服务
reset slave all; #删除
SHOW SLAVE STATUS\G; #删除后slave的信息就看不到了

方法二

不常用，因为文件较大的话会导致打开异常修改困难，此方法备份时–source-data=1即可
CHANGE MASTER TO #注意此选项复制时是否重复

#写入到备份文件中
[root@mysql- ~]# vim all.sql
CHANGE MASTER TO MASTER_HOST='10.0.0.8', 
MASTER_USER='repluser', 
MASTER_PASSWORD='cqmyg', 
[MASTER_LOG_FILE='master111-bin.000005', ] #此前记录二进制日志位置，在此行的上面添加以上三行
[MASTER_LOG_POS=393;]
#导入数据库
mysql < all.sql


#启动
START SLAVE [IO_THREAD|SQL_THREAD]; #不加[]的内容默认两个都启动
STOP SLAVE; #停止服务
#查看结果
SHOW SLAVE STATUS\G;
         Seconds_Behind_Master: NULL #主从复制延迟时间，最好是0，0表示已经同步
         Slave_IO_Running: Yes #IO线程是否开启
         Slave_SQL_Running: Yes #SQL线程是否开启
         Last_IO_Error: #IO线程错误信息
         Last_SQL_Errno: #SQL线程错误信息

#删除复制信息（配置错误需要重新修改的情况）
STOP SLAVE #需要先停止服务
reset slave all #删除
SHOW SLAVE STATUS\G; #删除后slave的信息就看不到了

查看后续更改的数据是否也已经同步

mysql> select * from hellodb.teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | xiaohong      |  18 | F      |
+-----+---------------+-----+--------+

一主多从

基于一主一从的基础上在配置另一台从服务器，一主一从的从节点配置一样(server-id除外)，但是要注意二进制日志先关闭等相关事情
下面演示的是主节点已有数据的情况：

环境说明

hostname	IP
mysql-master-1	10.0.0.8
mysql-slave-1	10.0.0.18
mysql-slave-2	10.0.0.100

另一台从节点配置

主从配置参考上文

修改从节点配置文件

主要是启动中继日志、指定server-id、开启二进制日志(便于后期升主或备份）

#准备二进制日志存放目录
root@mysql-slave-2:~# mkdir -p /data/mysql_bin_log/
root@mysql-slave-2:~# chown -R mysql.mysql /data/mysql_bin_log/

#修改配置文件
root@mysql-slave-2:~# vim /etc/my.cnf
[mysqld]
######bin-log######
binlog_format=ROW
log_bin=/data/mysql_bin_log/mysql_bin_log
max_binlog_size=1073741824
binlog_cache_size=4m
max_binlog_cache_size=512m
sync_binlog=1
binlog_expire_logs_seconds=2592000
######replication-slave######
server_id=100 #注意唯一
innodb_flush_log_at_trx_commit=2
log_slave_updates=ON
...

#重启服务
root@mysql-slave-2:~# systemctl restart mariadb|mysql

将完全备份还原并启动复制

使用有复制权限的用户账号连接至主服务器，并启动复制线程

MariaDB [(none)]> HELP CHANGE MASTER TO; #配置文件帮助

方法一

常用，此方法备份时–source-data=2即可

root@mysql-slave-2:~# mysql -uroot -p12345
...

#关闭二进制日志记录
mysql> set @@sql_log_bin=off;

#导入备份文件
mysql> source all.sql
#导入备份文件方法二（不建议使用，因为会产生大量的二进制日志）
root@mysql-slave-2:~# mysql -uroot -p12345 < all.sql

#恢复二进制日志记录
mysql> set @@sql_log_bin=on;


#查看备份文件的相关复制信息
root@mysql-slave-2:~# head -n100 all.sql|grep 'CHANGE MASTER TO'
CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin_log.000008', MASTER_LOG_POS=156;

#进入mysql执行CHANGE MASTER TO
CHANGE MASTER TO
  MASTER_HOST='10.0.0.8',
  MASTER_USER='rep_user',
  MASTER_PASSWORD='cqmyg',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql_bin_log.000008',
  MASTER_LOG_POS=156,
  MASTER_CONNECT_RETRY=10;

#启动
START SLAVE [IO_THREAD|SQL_THREAD]; #不加[]的内容默认两个都启动
STOP SLAVE; #停止服务

#查看结果
SHOW SLAVE STATUS\G;
         Seconds_Behind_Master: NULL #主从复制延迟时间，最好是0，0表示已经同步
         Slave_IO_Running: Yes #IO线程是否开启
         Slave_SQL_Running: Yes #SQL线程是否开启
         Last_IO_Error: #IO线程错误信息
         Last_SQL_Errno: #SQL线程错误信息

#删除复制信息（配置错误需要重新修改的情况）
STOP SLAVE; #需要先停止服务
reset slave all; #删除
SHOW SLAVE STATUS\G; #删除后slave的信息就看不到了

主主

两个节点，都可以更新数据，并且互为主从，但是用的时候还是以一主一从的方式来用以防数据冲突
双主模式是指两台服务器互为主从，任何一台服务器数据变更，都会通过复制应用到另外一方的数据库中。
下面演示的是主节点已有数据的情况：

环境说明

hostname	IP
mysql-master-1	10.0.0.8
mysql-master-2	10.0.0.18

实现主节点1向主节点2同步

Master1 –> Master2(Slave)

修改Master1配置文件

主要是开启二进制日志、指定唯一的server-id

#准备二进制日志存放目录
[root@mysql-master-1 ~]# mkdir -p /data/mysql_bin_log/
[root@mysql-master-1 ~]# chown -R mysql.mysql /data/mysql_bin_log/

#修改配置文件
[root@mysql-master-1 ~]# vim /etc/my.cnf
[mysqld]
######bin-log######
binlog_format=ROW
log_bin=/data/mysql_bin_log/mysql_bin_log
max_binlog_size=1073741824
binlog_cache_size=4m
max_binlog_cache_size=512m
sync_binlog=1
binlog_expire_logs_seconds=2592000
######replication-master######
server-id=8
innodb_flush_log_at_trx_commit=2
log_slave_updates=ON
...


#重启服务
[root@mysql-master-1 ~]# systemctl restart mariadb|mysql


#查看配置是否正确
mysql> SHOW variables like 'sql_log_bin';
mysql> SHOW variables like 'binlog_format';
mysql> SHOW MASTER STATUS;
mysql> SHOW MASTER LOGS;

创建有复制权限的用户账号

#创建复制账号并设置密码
mysql> CREATE USER 'repluser'@'10.0.0.%' IDENTIFIED BY 'cqmyg';

#授权复制账号复制权限即可
mysql> GRANT REPLICATION SLAVE ON *.* TO repluser@'10.0.0.%';

进行完全备份并拷贝到Master2节点

#进行完全备份
[root@mysql-master-1 ~]# mysqldump -uroot -p'12345' -A -F --single-transaction --source-data=2 --flush-privileges --hex-blob > all.sql

#复制到远程主机的家目录中
[root@mysql-master-1 ~]# rsync all.sql 10.0.0.18:~

修改Master2配置文件

#准备二进制日志存放目录
[root@mysql-slave-2 ~]# mkdir -p /data/mysql_bin_log/
[root@mysql-slave-2 ~]# chown -R mysql.mysql /data/mysql_bin_log/

#修改配置文件
[root@mysql-slave-2 ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
######bin-log######
binlog_format=ROW
log_bin=/data/mysql_bin_log/mysql_bin_log
max_binlog_size=1073741824
binlog_cache_size=4m
max_binlog_cache_size=512m
sync_binlog=1
binlog_expire_logs_seconds=2592000
######replication-slave######
server_id=18
innodb_flush_log_at_trx_commit=2
log_slave_updates=ON
...

#重启服务
[root@mysql-slave-2 ~]# systemctl restart mysql

Master2恢复备份并启动复制

[root@mysql-slave-2 ~]# mysql -uroot -p12345
...

#关闭二进制日志记录
mysql> set @@sql_log_bin=off;

#导入备份文件
mysql> source all.sql
#导入备份文件方法二（不建议使用，因为会产生大量的二进制日志）
[root@mysql-slave-1 ~]# mysql -uroot -p12345 < all.sql

#恢复二进制日志记录
mysql> set @@sql_log_bin=on;

#查看备份文件的相关复制信息
[root@mysql-slave-1 ~]# head -n100 all.sql|grep 'CHANGE MASTER TO'
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin_log.000011', MASTER_LOG_POS=156;

#进入mysql执行CHANGE MASTER TO
CHANGE MASTER TO
  MASTER_HOST='10.0.0.8',
  MASTER_USER='repluser',
  MASTER_PASSWORD='cqmyg',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql_bin_log.000011',
  MASTER_LOG_POS=156,
  MASTER_CONNECT_RETRY=10;

#启动
START SLAVE [IO_THREAD|SQL_THREAD]; #不加[]的内容默认两个都启动
STOP SLAVE; #停止服务

#查看结果
SHOW SLAVE STATUS\G;
         Seconds_Behind_Master: NULL #主从复制延迟时间，最好是0，0表示已经同步
         Slave_IO_Running: Yes #IO线程是否开启
         Slave_SQL_Running: Yes #SQL线程是否开启
         Last_IO_Error: #IO线程错误信息
         Last_SQL_Errno: #SQL线程错误信息

#删除复制信息（配置错误需要重新修改的情况）
STOP SLAVE; #需要先停止服务
reset slave all; #删除
SHOW SLAVE STATUS\G; #删除后slave的信息就看不到了

测试单向复制

#master-1
mysql> create database db1;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

#master-2
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

实现主节点2向主节点1同步

Master1(Slave) <– Master2

查看Master2二进制日志位置

*************************** 1. row ***************************
             File: mysql_bin_log.000003
         Position: 345
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 

Master1启动复制

#进入mysql执行CHANGE MASTER TO
CHANGE MASTER TO
  MASTER_HOST='10.0.0.18',
  MASTER_USER='repluser',
  MASTER_PASSWORD='cqmyg',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql_bin_log.000003',
  MASTER_LOG_POS=345,
  MASTER_CONNECT_RETRY=10;
  
#启动复制
mysql> start slave;

测试双向复制

# master-1
mysql> create database db1;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

# master-2
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

# master-2
mysql> create database db2;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

# master-1
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

可选项补充

自动增长id，通过配置不同的节点id防止主键冲突，非必选项，因为通常都是一个服务器写

配置一个节点使用奇数id

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

另一个节点使用偶数id

auto_increment_offset=2
auto_increment_increment=2

级联复制

Master –> Slave –> Slaves
本质上就是在主从复制的基础上再建立从节点而这个从节点向向前面的从节点进行复制
注意：核心就是将中间的slave开启log_slave_updates=ON，让此从节点在复制时同时也记录二进制日志，否则后面的Slaves将无法通过二进制日志进行数据更新

环境说明

hostname	IP	role
mysql-master	10.0.0.8	master
mysql-slave-1	10.0.0.18	slave
mysql-slave-2	10.0.0.100	slaves

mysql-master配置

参考前面的一主一从的主节点配置

mysql-slave-1配置

参考前面的一主一从的从节点配置
再添加以下配置

[mysqld]
log_slave_updates=ON #主要添加此项
...

mysql-slave-2配置

参考前面的一主一从的从节点配置
注意复制节点要指向mysql-slave-1

半同步复制

至少有一个从节点数据复制成功后，主节点才通过代理服务器告诉客户端成功（为了避免主节点的修改信息未完全同步到从节点就告诉客户端成功，进而导致的单点失败问题）
如果从节点在10秒(默认值)内没有同步成功，主页点也会通知用户成功，这样虽然也有一定概率导致单点失败，但是主节点有二进制日志同样也可以恢复数据，这样的话既顾及了用户的体验，同时也保证了数据的安全

MySQL实现半同步复制

MySQL 8.0.26
官方文档：https://dev.mysql.com/doc/refman/8.0/en/replication-semisync.html
半同步复制是使用插件实现的，插件必须安装在源和副本上，以使半同步复制在实例上可用。源和副本有不同的插件。安装插件后，您可以通过与其关联的系统变量来控制它。这些系统变量仅在安装了相关插件后才可用。

环境说明

hostname	IP	role
mysql-master	10.0.0.8	master
mysql-slave-1	10.0.0.18	slave
mysql-slave-2	10.0.0.101	slave

MySQL半同步复制核心配置

在实现一主一从或一主多从后添加以下配置：

主节点执行

#启动插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
#Or from MySQL 8.0.26:
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';

#验证插件是否启动成功
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_source | ACTIVE        |
+----------------------+---------------+


#修改配置文件
[root@mysql-master ~]# vim /etc/my.cnf 
[mysqld]
rpl_semi_sync_source_enabled=ON #MySQL 8.0.26版本以前需将source改为master
...


#启动半同步复制，也可以直接重启mysql使配置文件生效
mysql> set global rpl_semi_sync_source_enabled=1;
mysql> show variables like 'rpl_semi_sync_source_enabled';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| rpl_semi_sync_source_enabled | ON    |
+------------------------------+-------+


#如果在运行的副本上启用半同步复制，需要重启I/O线程
STOP REPLICA IO_THREAD;
START REPLICA IO_THREAD;

所有从节点执行

#启动插件
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
#Or from MySQL 8.0.26:
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';


#验证插件是否启动成功
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';
+-----------------------+---------------+
| PLUGIN_NAME           | PLUGIN_STATUS |
+-----------------------+---------------+
| rpl_semi_sync_replica | ACTIVE        |
+-----------------------+---------------+


#修改配置文件
[root@mysql-master ~]# vim /etc/my.cnf 
[mysqld]
rpl_semi_sync_replica_enabled=ON #MySQL 8.0.26版本以前需将replica改为slave
...

#启动半同步复制，也可以直接重启mysql使配置文件生效
mysql> set global rpl_semi_sync_replica_enabled=1;
mysql> show variables like 'rpl_semi_sync_replica_enabled';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| rpl_semi_sync_source_enabled | ON    |
+------------------------------+-------+


#如果在运行的副本上启用半同步复制，需要重启I/O线程
STOP REPLICA IO_THREAD;
START REPLICA IO_THREAD;

测试半同步复制

主从节点正常的情况

几乎没有延迟

#mysql-master
mysql> create database db1;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

#mysql-slave-1
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

#mysql-slave-2
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

从节点坏一台的情况

几乎没有延迟

#mysql-slave-2
root@mysql-slave-2:~# systemctl stop mysql.service

#mysql-master
mysql> create database db2;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.00 sec)


#mysql-slave-1
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.00 sec)

从节点全坏的情况

延迟10秒，延迟时间可调

#mysql-slave-1
root@mysql-slave-1:~# systemctl stop mysql.service

#mysql-slave-2
root@mysql-slave-2:~# systemctl stop mysql.service

#mysql-master
mysql> create database db3;
Query OK, 1 row affected (10.01 sec) #延迟10秒，延迟时间可调

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| db3                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
8 rows in set (0.00 sec)

MySQL半同步复制其他配置

参考文档：https://dev.mysql.com/doc/refman/8.0/en/replication-semisync-interface.html
注意：从 MySQL 8.0.26 开始，在系统变量和状态变量中将术语“ master ”和“ slave ”替换为“ source ”和“ replica ” 。

#半同步复制主节点相关状态变量
mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+---------------------------------------------+------------+
| Variable_name                               | Value      |
+---------------------------------------------+------------+
| rpl_semi_sync_source_enabled                | ON #主节点是否启用半同步复制
| rpl_semi_sync_source_timeout                | 10000 #等待从节点返回的超时时间，毫秒为单位（10000=10秒）
| rpl_semi_sync_source_trace_level            | 32         |
| rpl_semi_sync_source_wait_for_replica_count | 1 #等待几个从节点返回成功的信息在通知客户端成功
| rpl_semi_sync_source_wait_no_replica        | ON         |
| rpl_semi_sync_source_wait_point             | AFTER_SYNC |
+---------------------------------------------+------------+



#半同步复制从节点相关状态变量
mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| rpl_semi_sync_replica_enabled     | ON #从节点是否启用半同步复制
| rpl_semi_sync_replica_trace_level | 32    |
+-----------------------------------+-------+

MySQL半同步复制状态监控

官方文档：https://dev.mysql.com/doc/refman/8.0/en/replication-semisync-monitoring.html

#主节点相关状态变量
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_source_clients               | 2 #连接到源服务器的半同步副本数
| Rpl_semi_sync_source_net_avg_wait_time     | 0     |
| Rpl_semi_sync_source_net_wait_time         | 0     |
| Rpl_semi_sync_source_net_waits             | 22    |
| Rpl_semi_sync_source_no_times              | 1     |
| Rpl_semi_sync_source_no_tx                 | 1 #副本未成功确认的提交数。
| Rpl_semi_sync_source_status                | ON #建议监控，半同步复制当前是否在源服务器上运行。如果插件已启用且未发生提交确认，则值为1(ON)。如果未启用插件或源由于提交确认超时而退回到异步复制 则为0(OFF)，半同步复制失效则此值为OFF
| Rpl_semi_sync_source_timefunc_failures     | 0     |
| Rpl_semi_sync_source_tx_avg_wait_time      | 1954  |
| Rpl_semi_sync_source_tx_wait_time          | 21494 |
| Rpl_semi_sync_source_tx_waits              | 11    |
| Rpl_semi_sync_source_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_source_wait_sessions         | 0     |
| Rpl_semi_sync_source_yes_tx                | 11 #副本成功确认的提交数
+--------------------------------------------+-------+
14 rows in set (0.03 sec)


#从节点相关状态变量
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| Rpl_semi_sync_replica_status | ON #半同步复制当前是否在副本上运行。如果插件已启用并且复制 I/O（接收器）线程正在运行，则为 1(ON)，否则为 0(OFF)
+------------------------------+-------+
1 row in set (0.15 sec)

Mariadb实现半同步复制

CentOS 8 在Mariadb-10.3.28上实现实现半同步复制
官方文档： https://mariadb.com/kb/en/library/semisynchronous-replication/

#在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   #启动master半同步插件
rpl_semi_sync_master_timeout=3000   #设置3s内无法同步，也将返回成功信息给客户端，不写则默认10s

[root@centos8 ~]#systemctl restart mariadb

MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       330 | #查看二进制日志位置
+--------------------+-----------+

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)

#在主节点创建账号，同时也能验证因为没有创建从节点而导致复制时间变慢
MariaDB [(none)]> grant replication slave on *.* to repluser@'10.0.0.%' identified by 'magedu';
Query OK, 0 rows affected (3.009 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)
MariaDB [(none)]> 

#让从节点往主节点复制
MariaDB [(none)]> CHANGE MASTER TO
MASTER_HOST='10.0.0.8',
MASTER_USER='repluser',
MASTER_PASSWORD='magedu',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=330;
Query OK, 0 rows affected (0.007 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.007 sec)
MariaDB [(none)]> show slave status\G;
#其他从节点也同样配置，都是指向主节点进行复制

#在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)

主从复制管理与监控语句

#启动复制，从 MySQL 8.0.22 开始，使用 START REPLICA;代替
START SLAVE;

#停止复制，从 MySQL 8.0.22 开始，使用 STOP REPLICA;代替
STOP SLAVE;

#查看主从复制状态，从 MySQL 8.0.22 开始，使用 SHOW REPLICA STATUS\G代替
SHOW MASTER STATUS\G #主节点执行
SHOW SLAVE STATUS\G #从节点执行

#删除所有复制信息，从 MySQL 8.0.22 开始，使用 RESET REPLICA代替，需要先停止复制 再删除复制信息，删除后slave的信息就看不到了
RESET SLAVE [ALL];

SHOW BINARY LOGS;
SHOW BINLOG EVENTS;
SHOW PROCESSLIST\G

CHANGE MASTER TO 未来更改名称

mysql> CHANGE MASTER TO
    ->   MASTER_HOST='10.0.0.8',
    ->   MASTER_USER='repluser',
    ->   MASTER_PASSWORD='cqmyg',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='mysql_bin_log.000014',
    ->   MASTER_LOG_POS=156,
    ->   MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 10 warnings (0.02 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1287
Message: 'CHANGE MASTER' is deprecated and will be removed in a future release. Please use CHANGE REPLICATION SOURCE instead
*************************** 2. row ***************************
  Level: Warning
   Code: 1287
Message: 'MASTER_HOST' is deprecated and will be removed in a future release. Please use SOURCE_HOST instead
*************************** 3. row ***************************
  Level: Warning
   Code: 1287
Message: 'MASTER_USER' is deprecated and will be removed in a future release. Please use SOURCE_USER instead
*************************** 4. row ***************************
  Level: Warning
   Code: 1287
Message: 'MASTER_PASSWORD' is deprecated and will be removed in a future release. Please use SOURCE_PASSWORD instead
*************************** 5. row ***************************
  Level: Warning
   Code: 1287
Message: 'MASTER_PORT' is deprecated and will be removed in a future release. Please use SOURCE_PORT instead
*************************** 6. row ***************************
  Level: Warning
   Code: 1287
Message: 'MASTER_LOG_FILE' is deprecated and will be removed in a future release. Please use SOURCE_LOG_FILE instead
*************************** 7. row ***************************
  Level: Warning
   Code: 1287
Message: 'MASTER_LOG_POS' is deprecated and will be removed in a future release. Please use SOURCE_LOG_POS instead
*************************** 8. row ***************************
  Level: Warning
   Code: 1287
Message: 'MASTER_CONNECT_RETRY' is deprecated and will be removed in a future release. Please use SOURCE_CONNECT_RETRY instead
*************************** 9. row ***************************
  Level: Note
   Code: 1759
Message: Sending passwords in plain text without SSL/TLS is extremely insecure.
*************************** 10. row ***************************
  Level: Note
   Code: 1760
Message: Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
10 rows in set (0.00 sec)

主从状态信息说明

从节点监控：

#show slave status\G;

#重点关注
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 10.0.0.8
                   Master_User: copyuser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: master-bin.000002
           Read_Master_Log_Pos: 1140526 #从主节点复制的数据点，当主节点宕机时 并有多台从节点 可以选择此值较高的主机(复制的更多)提升为新主，此数据同样存放于
                Relay_Log_File: mariadb-relay-bin.000002
                 Relay_Log_Pos: 1139535
         Relay_Master_Log_File: master-bin.000002
              Slave_IO_Running: Yes #IO线程是否开启
             Slave_SQL_Running: Yes #SQL线程是否开启
               Replicate_Do_DB: 
           Replicate_Ignore_DB: 
            Replicate_Do_Table: 
        Replicate_Ignore_Table: 
       Replicate_Wild_Do_Table: 
   Replicate_Wild_Ignore_Table: 
                    Last_Errno: 0
                    Last_Error: 
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 1140526
               Relay_Log_Space: 1139846
               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: 0 #主从复制延迟时间，最好是0，0表示已经同步
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error:  #IO线程错误信息
                Last_SQL_Errno: 0
                Last_SQL_Error:  #SQL线程错误信息
   Replicate_Ignore_Server_Ids: 
              Master_Server_Id: 111
                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 has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 30
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 2610

复制过滤器

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

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

主节点设置二进制日志黑白名单

在主节点修改
在主服务器设置指定库的黑白名单来实现过滤复制，即指定内容不记录二进制日志
缺点：不记录二进制日志将无法进行基于二进制日志备份

从节点设置过滤复制内容

在从节点修改
缺点：将主节点发过来的二进制日志再进行过滤更新会造成带宽冗余，另外每个从节点都需要进行修改配置文件

方案一：在主节点实现复制过滤器

官方文档：

注意：此项和 binlog_format相关

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

注意：

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.

范例

下面演示以数据库白名单的方式实现

主节点配置

#主节点目前已有数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| db3                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

#修改主节点配置文件
[root@mysql-master ~]# vim /etc/my.cnf
[mysqld]
binlog-do-db=hellodb
binlog-do-db=db1
...

测试

#在主节点上分别向hellodb、db1、db2库插入数据
mysql> use hellodb;
mysql> insert teachers (name,age,gender)value('azheng',24,'M');
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   9 | azheng        |  24 | M      |
+-----+---------------+-----+--------+
mysql> use db1;
mysql> create table t1(id int,name VARCHAR(10));
mysql> insert t1 value('1','a');
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
+------+------+
mysql> use db2;
mysql> create table t2(id int,name VARCHAR(10));
mysql> insert t2 value('2','b');
mysql> select * from t2;


#在从节点查看复制结果
mysql> select * from hellodb.teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   9 | azheng        |  24 | M      |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)
mysql> select * from db1.t1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)
mysql> select * from db2.t2;
ERROR 1146 (42S02): Table 'db2.t2' doesn't exist #因为db2库不在白名单中 所以db2库的内容不会被复制

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

#主节点的配置文件
[root@mysql-master ~]# vim /etc/my.cnf
[mysqld]
######bin-log######
binlog-do-db=hellodb
binlog-do-db=db1
...

#主节点插入内容
mysql> select database();
+------------+
| database() |
+------------+
| db2        |
+------------+
mysql> insert db1.t1 (id,name)value(6,'aaa');

#从节点可以同步
mysql> select * from db1.t1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | aa   |
|    6 | aaa  |
+------+------+

#主节点创建新表
mysql> select database();
+------------+
| database() |
+------------+
| db2        |
+------------+
mysql> create table db1.t123(id int);
mysql> show tables from db1;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
| t123          |
+---------------+

#从节点无法同步
mysql> show tables from db1;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
+---------------+

方案二：在从节点实现复制过滤器

在从服务设置选项或变量来实现过滤复制内容

从服务器SQL_THREAD在relay log中的事件时，仅读取与特定数据库(特定表)相关的事件并应用于本地

另外要注意：跨库进行添加记录等操作时会有可能会导致数据向从节点复制出现问题

从服务器上的复制过滤器相关变量

vim /etc/my.cnf
[mysqld]
#指定复制库的白名单，变量可以指定逗号分隔的多个值，选项不支持多值,只能分别写多行实现
replicate_do_db=db1 
replicate_do_db=db2
replicate_do_db=db3

vim /etc/my.cnf
[mysqld]
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.
  

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

#修改主节点配置文件
[root@mysql-master ~]# vim /etc/my.cnf
[mysqld]
replicate_do_db=db1  
replicate_do_db=db2
replicate_do_db=db3

#从节点插入内容
MariaDB [db1]> create table db2.t1(id int);
Query OK, 0 rows affected (0.010 sec)

#测试结果

实现复制过滤后的状态

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: mysql_bin_log.000022
         Position: 196
     Binlog_Do_DB: hellodb,db1 #只复制的库
 Binlog_Ignore_DB: 
Executed_Gtid_Set: fbdce6f9-d981-11ec-84d8-000c290b148b:1-2

主从复制加密

默认MySQL服务是不加密的，可以通过MySQL+ssl来实现加密

GTID 复制

Global Transaction ID 全局事务标识符，MySQL 5.6 版本开始支持
GTID复制不像传统的复制方式（异步复制、半同步复制）需要找到binlog文件名和POS点，只需知道master的IP、端口、账号、密码即可。
开启GTID后，执行change master to master_auto_postion=1即可，它会自动寻找到相应的位置开始同步
GTID = server_uuid:transaction_id，在一组复制中，全局唯一（server_uuid 来源于 /var/lib/mysql/auto.cnf）

GTID核心配置说明

[root@mysql-master ~]# vim /etc/my.cnf
[mysqld]
#################GTID#################
gtid_mode=ON #启用gtid模式，默认为OFF 即不启用，此项为ON则必须enforce_gtid_consistency也为ON
enforce_gtid_consistency=ON #保证GTID安全的参数，ON表示不允许任何事务违反GTID一致性
...

范例：基于GTID实现主从复制

注意：主要配置在于主从节点都开启gtid_mode和enforce_gtid_consistency，然后从节点在CHANGE MASTER TO 时只需指定主节点主机、复制账号密码、以及MASTER_AUTO_POSITION=1即可

环境说明

hostname	IP
mysql-master-1	10.0.0.8
mysql-slave-1	10.0.0.18

主节点配置

修改主节点配置文件

主要是开启二进制日志、指定唯一的server-id

#准备二进制日志存放目录
[root@mysql-master ~]# mkdir -p /data/mysql_bin_log/
[root@mysql-master ~]# chown -R mysql.mysql /data/mysql_bin_log/

#修改配置文件
[root@mysql-master ~]# vim /etc/my.cnf
[mysqld]
######bin-log######
binlog_format=ROW
log_bin=/data/mysql_bin_log/mysql_bin_log
max_binlog_size=1073741824
binlog_cache_size=4m
max_binlog_cache_size=512m
sync_binlog=1
binlog_expire_logs_seconds=2592000
######replication-master######
server-id=8
innodb_flush_log_at_trx_commit=2
#################GTID#################
gtid_mode=ON
enforce_gtid_consistency=ON
...


#重启服务
systemctl restart mariadb|mysql

创建有复制权限的用户账号

#创建
mysql> CREATE USER 'repluser'@'10.0.0.%' IDENTIFIED BY 'cqmyg';

#授权，仅授予复制权限即可
mysql> GRANT REPLICATION SLAVE ON *.* TO repluser@'10.0.0.%';

进行完全备份并拷贝到slave节点

#进行完全备份
[root@mysql-master ~]# mysqldump -uroot -p'12345' -A -F --single-transaction --source-data=2 --flush-privileges --hex-blob > all.sql

#复制到远程主机的家目录中
[root@mysql-master ~]# rsync all.sql 10.0.0.18:~

假设此时发生数据更新

mysql> insert hellodb.teachers (name,age,gender)value('xiangzheng',25,'M');

mysql> select * from hellodb.teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   9 | azheng        |  24 | M      |
|  10 | xiangzheng    |  25 | M      |
+-----+---------------+-----+--------+

从节点配置

修改从节点配置文件

#准备二进制日志存放目录
[root@mysql-slave-1 ~]# mkdir -p /data/mysql_bin_log/
[root@mysql-slave-1 ~]# chown -R mysql.mysql /data/mysql_bin_log/

#修改配置文件
[root@mysql-slave-1 ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
######bin-log######
binlog_format=ROW
log_bin=/data/mysql_bin_log/mysql_bin_log
max_binlog_size=1073741824
binlog_cache_size=4m
max_binlog_cache_size=512m
sync_binlog=1
binlog_expire_logs_seconds=2592000
######replication-slave######
server_id=18
innodb_flush_log_at_trx_commit=2
log_slave_updates=ON
#################GTID#################
gtid_mode=ON
enforce_gtid_consistency=ON
...


#重启服务
[root@mysql-slave-1 ~]# systemctl restart mariadb|mysql

将完全备份还原

[root@mysql-slave-1 ~]# mysql -uroot -p12345
...

#关闭二进制日志记录
mysql> set @@sql_log_bin=off;

#导入备份文件
mysql> source all.sql
#导入备份文件方法二（不建议使用，因为会产生大量的二进制日志）
[root@mysql-slave-1 ~]# mysql -uroot -p12345 < all.sql

#恢复二进制日志记录
mysql> set @@sql_log_bin=on;

启动GTID并开启复制线程

#进入mysql执行CHANGE MASTER TO
CHANGE MASTER TO
  MASTER_HOST='10.0.0.8',
  MASTER_USER='repluser',
  MASTER_PASSWORD='cqmyg',
  MASTER_PORT=3306,
  MASTER_CONNECT_RETRY=10,
  GET_MASTER_PUBLIC_KEY=1,
  MASTER_AUTO_POSITION=1;
 
#启动复制线程
START SLAVE;

#查看复制状态
SHOW SLAVE STATUS\G;
         Seconds_Behind_Master: NULL #主从复制延迟时间，最好是0，0表示已经同步
         Slave_IO_Running: Yes #IO线程是否开启
         Slave_SQL_Running: Yes #SQL线程是否开启
         Last_IO_Error: #IO线程错误信息
         Last_SQL_Errno: #SQL线程错误信息

查看后续更改的数据是否也已经同步

mysql> select * from hellodb.teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   9 | azheng        |  24 | M      |
|  10 | xiangzheng    |  25 | M      |
+-----+---------------+-----+--------+

主从复制报错汇总

Authentication plugin ‘caching_sha2_password’ reported error: Authentication requires secure connection.

解决方案：在CHANGE MASTER TO时添加GET_MASTER_PUBLIC_KEY=1（身份验证插件使用的是caching_sha2_password 并且没有启动安全连接SSL需要开启此选项）

CHANGE MASTER TO
 MASTER_HOST='10.0.0.8',
 MASTER_USER='repluser',
 MASTER_PASSWORD='cqmyg',
 MASTER_PORT=3306,
 MASTER_LOG_FILE='mysql_bin_log.000014',
 MASTER_LOG_POS=156,
 GET_MASTER_PUBLIC_KEY=1, #添加此项
 MASTER_CONNECT_RETRY=10;

主从复制的问题和解决方案

造成主从不一致的原因有？

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

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

percona-toolkit

数据不一致如何修复？

最彻底解决删除从库重新复制
- 虽然这也是一种解决方法，但是这个方案恢复时间比较慢，而且有时候从库也是承担一部分的查询操作的，不能贸然重建
使用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 -pmagedu -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; 

#4、把A_B_C.sql拷贝到Slave机器上，并做指向新位置
mysql>start slave until MASTERLOGFILE='mysql-bin.888888', MASTERLOGPOS=666666; #以上指令是为了保障其他表的数据不丢失，一直同步，直到同步完那个点结束，A,B,C表的数据在之前的备份已经生成了一份快照，只需要导入进入，然后开启同步即可

#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; 

#6、导入完毕后，从库开启同步即可。
mysql>start slave;

如何避免主从不一致？

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