环境准备
| IP | HOSTNAME | SERVICE | SYSTEM |
| 192.168.131.129 | mysql-master1 | mysql | CentOS7.6 |
| 192.168.131.130 | mysql-slave1 | mysql | CentOS7.6 |
| 192.168.131.131 | mysql-slave2 | mysql | CentOS7.6 |
[root@localhost ~]# sestatus SELinux status: disabled [root@localhost ~]# systemctl status firewalld ● firewalld.service - firewalld - dynamic firewall daemon Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled) Active: inactive (dead) Docs: man:firewalld(1) [root@localhost ~]# cat /etc/redhat-release CentOS Linux release 7.6.1810 (Core) [root@localhost ~]# hostnamectl --static set-hostname mysql-master1 [root@localhost ~]# hostnamectl --static set-hostname mysql-slave1 [root@localhost ~]# hostnamectl --static set-hostname mysql-master2 [root@localhost ~]# hostnamectl --static set-hostname mysql-slave2
部署mysql
# mysql-master和mysql-slave都需要部署 [root@mysql-master ~]# wget https://repo.mysql.com/mysql57-community-release-el7-11.noarch.rpm [root@mysql-master ~]# yum -y install mysql57-community-release-el7-11.noarch.rpm [root@mysql-master ~]# yum -y install yum-utils # 安装yum管理工具 [root@mysql-master ~]# yum-config-manager --disable mysql80-community # 禁用8.0版本 [root@mysql-master ~]# yum-config-manager --enable mysql57-community # 启用5.7版本 [root@mysql-master ~]# yum repolist enabled | grep mysql # 检查一下，确保只有一个版本 mysql-connectors-community/x86_64 MySQL Connectors Community 165 mysql-tools-community/x86_64 MySQL Tools Community 115 mysql57-community/x86_64 MySQL 5.7 Community Server 444 [root@mysql-master ~]# yum -y install mysql-community-server [root@mysql-master ~]# systemctl enable mysqld --now # 设为开机自启，并立即启动
# 修改默认密码（MySQL从5.7开始不允许首次安装后使用空密码进行登录！为了加强安全性，系统会随机生成一个密码以供管理员首次登录使用，这个密码记录在/var/log/mysqld.log文件中） [root@mysql-master1 ~]# grep "temporary password" /var/log/mysqld.log 2020-08-11T01:38:32.872421Z 1 [Note] A temporary password is generated for root@localhost: pHj_Agoyi3of [root@mysql-master1 ~]# mysql -uroot -p'pHj_Agoyi3of' mysql> alter user 'root'@'localhost' identified by 'Test123.com';
主从复制
配置master1
[root@mysql-master1 ~]# cp /etc/my.cnf{,.bak} [root@mysql-master1 ~]# > /etc/my.cnf [root@mysql-master1 ~]# vim /etc/my.cnf [mysqld] datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock symbolic-links = 0 log-error = /var/log/mysqld.log pid-file = /var/run/mysqld/mysqld.pid #GTID: server_id = 1 gtid_mode = on enforce_gtid_consistency = on #binlog log_bin = mysql-bin log-slave-updates = 1 binlog_format = row sync-master-info = 1 sync_binlog = 1 #relay log skip_slave_start = 1
[root@mysql-master1 ~]# systemctl restart mysqld
[root@mysql-master1 ~]# mysql -uroot -p Enter password: mysql> show master status; # 查看master状态， 发现多了一项"Executed_Gtid_Set " +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> show global variables like '%uuid%'; +---------------+--------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------+ | server_uuid | 5d02c99a-db73-11ea-a39a-000c294ec5c2 | +---------------+--------------------------------------+ 1 row in set (0.00 sec) mysql> show global variables like '%gtid%'; # 查看确认gtid功能打开 +----------------------------------+-------+ | Variable_name | Value | +----------------------------------+-------+ | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed | | | gtid_executed_compression_period | 1000 | | gtid_mode | ON | | gtid_owned | | | gtid_purged | | | session_track_gtids | OFF | +----------------------------------+-------+ 8 rows in set (0.00 sec) mysql> show variables like 'log_bin'; # 查看确认binlog日志功能打开 +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql> grant replication slave,replication client on *.* to slave@'192.168.%' identified by "Slave@123"; Query OK, 0 rows affected, 1 warning (0.04 sec) mysql> show grants for slave@'192.168.%'; +---------------------------------------------------------------------------+ | Grants for slave@192.168.% | +---------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'192.168.%' | +---------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show master status; # 查看master状态 +------------------+----------+--------------+------------------+----------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+----------------------------------------+ | mysql-bin.000001 | 466 | | | 5d02c99a-db73-11ea-a39a-000c294ec5c2:1 | +------------------+----------+--------------+------------------+----------------------------------------+ 1 row in set (0.00 sec)
配置slave1
[root@mysql-master1 ~]# cp /etc/my.cnf{,.bak} [root@mysql-master1 ~]# > /etc/my.cnf [root@mysql-master1 ~]# vim /etc/my.cnf [mysqld] datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock symbolic-links = 0 log-error = /var/log/mysqld.log pid-file = /var/run/mysqld/mysqld.pid #GTID: server_id = 2 gtid_mode = on enforce_gtid_consistency = on #binlog log_bin = mysql-bin log-slave-updates = 1 binlog_format = row sync-master-info = 1 sync_binlog = 1 #relay log skip_slave_start = 1
[root@mysql-master1 ~]# mysql -uroot -p Enter password: mysql> stop slave; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> change master to master_host='192.168.131.129',master_user='slave',master_password='Slave@123',master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.131.129 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 466 Relay_Log_File: mysql-slave1-relay-bin.000002 Relay_Log_Pos: 679 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes ......... ......... Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) # mysql-slave1节点已经和mysql-master1节点配置完成主从同步关系
并行复制
一般Mysql主从复制有三个线程参与，都是单线程：Binlog Dump(主) -> IO Thread (从) -> SQL Thread(从)。复制出现延迟一般出在两个地方: - SQL线程忙不过来 (可能需要应用数据量较大，可能和从库本身的一些操作有锁和资源的冲突；主库可以并发写，SQL线程不可以；主要原因) - 网络抖动导致IO线程复制延迟(次要原因)。 MySQL主从复制延迟的解决办法：MySQL从5.6开始有了SQL Thread多个的概念，可以并发还原数据，即并行复制技术。并行复制的机制，是MySQL的一个非常重要的特性，可以很好的解决MySQL主从延迟问题！
MySQL 5.6版本支持所谓的并行复制，但是其并行只是基于schema的，也就是基于库的。 如果用户的MySQL数据库实例中存在多个schema，对于从机复制的速度的确可以有比较大的帮助。 但是基于schema的并行复制存在两个问题： 1) crash safe功能不好做，因为可能之后执行的事务由于并行复制的关系先完成执行，那么当发生crash的时候，这部分的处理逻辑是比较复杂的。 2) 最为关键的问题是这样设计的并行复制效果并不高，如果用户实例仅有一个库，那么就无法实现并行回放，甚至性能会比原来的单线程更差。而 单库多表是比多库多表更为常见的一种情形 。 注意：mysql 5.6 的MTS是基于库级别的并行，当有多个数据库时，可以将slave_parallel_workers设置为数据库的数量，为了避免新建库后来回修改，也可以将该参数设置的大一些。设置为库级别的事务时，不允许这样做，会报错。
在MySQL 5.7 中，引入了基于组提交的并行复制（官方称为Enhanced Multi-threaded Slaves，即MTS），设置参数 slave_parallel_workers>0 并且 global.slave_parallel_type='LOGICAL_CLOCK'，即可支持一个 schema 下， slave_parallel_workers 个的 worker 线程并发执行 relay log 中主库提交的事务。 其核心思想：一个组提交的事务都是可以并行回放（配合 binary log group commit ）；slave 机器的relay log 中 last_committed 相同的事务（ sequence_num 不同）可以并发执行。其中，变量 slave-parallel-type 可以有两个值： 1 ）DATABASE 默认值，基于库的并行复制方式； 2 ）LOGICAL_CLOCK，基于组提交的并行复制方式； MySQL 5.7是基于组提交的并行复制，并且是支持"真正"的并行复制功能，这其中最为主要的原因：就是slave服务器的回放与主机是一致的, 即master服务器上是怎么并行执行的slave上就怎样进行并行回放。不再有库的并行复制限制，对于二进制日志格式也无特殊的要求（基于库的并行复制也没有要求）。 MySQL5.7的并行复制，期望最大化还原主库的并行度，实现方式是在binlog event中增加必要的信息，以便slave节点根据这些信息实现并行复制。MySQL5.7的并行复制建立在group commit的基础上，所有在主库上能够完成prepared的语句表示没有数据冲突，就可以在slave节点并行复制。
配置master1
[root@mysql-master1 ~]# cp /etc/my.cnf{,.bak} [root@mysql-master1 ~]# vim /etc/my.cnf # 基于GTID主从复制结构，加入并行复制的配置 [mysqld] datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock symbolic-links = 0 log-error = /var/log/mysqld.log pid-file = /var/run/mysqld/mysqld.pid #GTID: server_id = 1 gtid_mode = on enforce_gtid_consistency = on #binlog log_bin = mysql-bin log-slave-updates = 1 binlog_format = row sync-master-info = 1 sync_binlog = 1 #relay log skip_slave_start = 1 #不配置binlog_group_commit从库无法做到基于事物的并行复制 binlog_group_commit_sync_delay = 100 binlog_group_commit_sync_no_delay_count = 10 #为了数据安全再配置 sync_binlog=1 innodb_flush_log_at_trx_commit =1 #这个参数控制binlog写入 磁盘的方式。设置为1时，表示每次commit;都写入磁盘。这个刷新的是redo log 即ib_logfile0，而不是binlog
[root@mysql-master1 ~]# systemctl restart mysqld
[root@mysql-master1 ~]# mysql -uroot -p Enter password: mysql> show variables like 'binlog_group_commit_%'; +-----------------------------------------+-------+ | Variable_name | Value | +-----------------------------------------+-------+ | binlog_group_commit_sync_delay | 100 | | binlog_group_commit_sync_no_delay_count | 10 | +-----------------------------------------+-------+ 2 rows in set (0.01 sec) # 设置binlog_group_commit的上面两个参数，否则从库无法做到基于事物的并行复制! 这两个参数共同决定了是否触发组提交操作！ # 第二个参数表示该事务组提交之前总共等待累积到多少个事务(如上要累计到10个事务)； # 第一个参数则表示该事务组总共等待多长时间后进行提交(如上要总共等待100毫秒的时间)，任何一个条件满足则进行后续操作。 # 因为有这个等待，可以让更多事务的binlog通过一次写binlog文件磁盘来完成提交，从而获得更高的吞吐量。
配置slave1
'记住：只要主数据库的mysqld服务重启，那么从数据库上就要重启slave，以恢复主从同步状态！！！ [root@mysql-slave1 ~]# cp /etc/my.cnf{,.bak} [root@mysql-slave1 ~]# vim /etc/my.cnf [mysqld] datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock symbolic-links = 0 log-error = /var/log/mysqld.log pid-file = /var/run/mysqld/mysqld.pid #GTID: server_id = 2 gtid_mode = on enforce_gtid_consistency = on #binlog log_bin = mysql-bin log-slave-updates = 1 binlog_format = row sync-master-info = 1 sync_binlog = 1 #relay log skip_slave_start = 1 read_only = on #MTS slave-parallel-type = LOGICAL_CLOCK #开启逻辑时钟的复制 slave-parallel-workers = 4 #这里设置线程数为4 (最大线程数不能超过16，即最大线程为16) master_info_repository = TABLE relay_log_info_repository = TABLE relay_log_recovery = on
[root@mysql-slave1 ~]# systemctl restart mysqld
[root@mysql-slave1 ~]# mysql -uroot -p Enter password: mysql> stop slave; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.10 sec) mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.131.129 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 466 Relay_Log_File: mysql-slave1-relay-bin.000002 Relay_Log_Pos: 679 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes ......... ......... Retrieved_Gtid_Set: 5d02c99a-db73-11ea-a39a-000c294ec5c2:1 Executed_Gtid_Set: 5d02c99a-db73-11ea-a39a-000c294ec5c2:1 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) # mysql-slave1从数据库恢复了与mysql-master1主数据库的同步关系了
# 查看线程数，这个跟在my.cnf文件里配置的是一样的 mysql> show variables like '%slave_para%'; +------------------------+---------------+ | Variable_name | Value | +------------------------+---------------+ | slave_parallel_type | LOGICAL_CLOCK | | slave_parallel_workers | 4 | +------------------------+---------------+ 2 rows in set (0.01 sec)
半同步复制
默认情况下MySQL的复制是异步的，master将新生成的binlog发送给各slave后，无需等待slave的ack回复(slave将接收到的binlog写进relay log后才会回复ack)，直接就认为这次DDL/DML成功了； 半同步复制(semi-synchronous replication)是指master将新生成的binlog发送给各slave时, 只需等待一个(默认)slave返回的ack信息就返回成功。
MySQL 5.7对半同步复制作了大改进，新增了一个master线程。 在MySQL 5.7以前，master上的binlog dump线程负责两件事：dump日志给slave的io_thread；接收来自slave的ack消息。它们是串行方式工作的。 在MySQL 5.7中，新增了一个专门负责接受ack消息的线程ack collector thread。这样master上有两个线程独立工作，可以同时发送binlog到slave和接收slave的ack。还新增了几个变量，其中最重要的是 rpl_semi_sync_master_wait_point ，它使得MySQL半同步复制有两种工作模型。
半同步复制的两种类型 从MySQL 5.7.2开始，MySQL支持两种类型的半同步复制。这两种类型由变量 rpl_semi_sync_master_wait_point (MySQL 5.7.2之前没有该变量)控制，它有两种值：AFTER_SYNC和AFTER_COMMIT。在MySQL 5.7.2之后，默认值为AFTER_SYNC，在此版本之前，等价的类型为AFTER_COMMIT。这个变量控制的是master何时提交、何时接收ack以及何时回复成功信息给客户端的时间点。 - AFTER_SYNC模式：master将新事务写进binlog(buffer)后发送给slave，再sync到自己的binlog file(disk), 之后才允许接收slave的ack回复，接收到ack之后才会提交事务，并返回成功信息给客户端。 - AFTER_COMMIT模式：master将新事务写进binlog(buffer)后发送给slave，再sync到自己的binlog file(disk)，然后直接提交事务。之后才允许接收slave的ack回复，然后再返回成功信息给客户端。
AFTER_SYNC和AFTER_COMMIT的优缺点 AFTER_SYNC - 对于所有客户端来说，它们看到的数据是一样的，因为它们看到的数据都是在接收到slave的ack后提交后的数据。 - 这种模式下，如果master突然故障，不会丢失数据，因为所有成功的事务都已经写进slave的relay log中了，slave的数据是最新的。 AFTER_COMMIT - 不同客户端看到的数据可能是不一样的。对于发起事务请求的那个客户端，它只有在master提交事务且收到slave的ack后才能看到提交的数据。但对于那些非本次事务的请求客户端，它们在master提交后就能看到提交后的数据，这时候master可能还没收到slave的ack。 - 如果master收到ack回复前，slave和master都故障了，那么将丢失这个事务中的数据。 在MySQL 5.7.2之前，等价的模式是 AFTER_COMMIT ，在此版本之后，默认的模式为 AFTER_SYNC ，该模式能最大程度地保证数据安全性，且性能上并不比 AFTER_COMMIT 差。
配置master1
[root@mysql-master1 ~]# cp /etc/my.cnf{,.bak} [root@mysql-master1 ~]# vim /etc/my.cnf # 基于GTID主从复制和并行复制，加入半同步复制 [mysqld] datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock symbolic-links = 0 log-error = /var/log/mysqld.log pid-file = /var/run/mysqld/mysqld.pid #GTID: server_id = 1 gtid_mode = on enforce_gtid_consistency = on #binlog log_bin = mysql-bin log-slave-updates = 1 binlog_format = row sync-master-info = 1 sync_binlog = 1 #relay log skip_slave_start = 1 #不配置binlog_group_commit从库无法做到基于事物的并行复制 binlog_group_commit_sync_delay = 100 binlog_group_commit_sync_no_delay_count = 10 #开启半同步复制 (超时时间为1s) plugin-load=rpl_semi_sync_master=semisync_master.so rpl_semi_sync_master_enabled = 1 rpl_semi_sync_master_timeout = 1000
[root@mysql-master1 ~]# systemctl restart mysqld
[root@mysql-master1 ~]# mysql -uroot -p Enter password: mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so'; ERROR 1125 (HY000): Function 'rpl_semi_sync_master' already exists # 在mysql-master主数据库上加载 (前提是/usr/lib64/mysql/plugin/semisync_master.so 文件存在。 一般mysql安装后就默认产生)，我的已经默认带有这个function mysql> select plugin_name, -> plugin_status from information_schema.plugins -> where plugin_name like '%semi%'; # 查看插件是否加载成功 +----------------------+---------------+ | plugin_name | plugin_status | +----------------------+---------------+ | rpl_semi_sync_master | ACTIVE | +----------------------+---------------+ 1 row in set (0.01 sec) mysql> show status like 'Rpl_semi_sync_master_status'; # 查看半同步是否在运行 +-----------------------------+-------+ | Variable_name | Value | +-----------------------------+-------+ | Rpl_semi_sync_master_status | ON | +-----------------------------+-------+ 1 row in set (0.00 sec)
配置slave1
[root@mysql-slave1 ~]# cp /etc/my.cnf{,.bak} [root@mysql-slave1 ~]# vim /etc/my.cnf [mysqld] datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock symbolic-links = 0 log-error = /var/log/mysqld.log pid-file = /var/run/mysqld/mysqld.pid #GTID: server_id = 1 gtid_mode = on enforce_gtid_consistency = on #binlog log_bin = mysql-bin log-slave-updates = 1 binlog_format = row sync-master-info = 1 sync_binlog = 1 #relay log skip_slave_start = 1 #不配置binlog_group_commit从库无法做到基于事物的并行复制 binlog_group_commit_sync_delay = 100 binlog_group_commit_sync_no_delay_count = 10 #为了数据安全再配置 sync_binlog=1 innodb_flush_log_at_trx_commit =1 #这个参数控制binlog写入 磁盘的方式。设置为1时，表示每次commit;都写入磁盘。这个刷新的是redo log 即ib_logfile0，而不是binlog # 开启半同步复制 plugin-load=rpl_semi_sync_slave=semisync_slave.so rpl_semi_sync_slave_enabled=1
[root@mysql-slave1 ~]# systemctl restart mysqld
[root@mysql-slave1 ~]# mysql -uroot -p Enter password: mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so'; Query OK, 0 rows affected (0.00 sec) # 在mysql-slave1从数据库上加载 (前提是/usr/lib64/mysql/plugin/semisync_slave.so 文件存在。 一般mysql安装后就默认产生) mysql> select plugin_name, -> plugin_status from information_schema.plugins -> where plugin_name like '%semi%'; # 查看插件是否加载成功 +----------------------+---------------+ | plugin_name | plugin_status | +----------------------+---------------+ | rpl_semi_sync_slave | ACTIVE | | rpl_semi_sync_master | ACTIVE | +----------------------+---------------+ 2 rows in set (0.00 sec) mysql> show status like 'Rpl_semi_sync_slave_status'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Rpl_semi_sync_slave_status | OFF | +----------------------------+-------+ 1 row in set (0.00 sec) # 发现是OFF，这是因为此时还没有生效，必须从数据库上的IO线程才能生产！！ mysql> stop slave IO_THREAD; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> start slave IO_THREAD; Query OK, 0 rows affected (0.00 sec) mysql> show status like 'Rpl_semi_sync_slave_status'; # 然后再查看mysql-slave1的半同步状态，发现就已经开启了！ +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Rpl_semi_sync_slave_status | ON | +----------------------------+-------+ 1 row in set (0.00 sec) mysql> show slave status \G # 再次查看主从同步状态，发现主从同步出现异常，这个时候再重启下slave即可！ *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.131.129 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 194 Relay_Log_File: mysql-slave1-relay-bin.000005 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: No mysql> stop slave; Query OK, 0 rows affected (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.131.129 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 194 Relay_Log_File: mysql-slave1-relay-bin.000007 Relay_Log_Pos: 367 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes
master1查看Rpl_semi
mysql> show status like '%Rpl_semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 1 | | 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_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) # 从上面信息，发现Rpl_semi_sync_master_clients的数值为1，说明此时mysql-master主数据库已经有一个半同步复制的从机，即mysql-slave1节点。 # Rpl_semi_sync_master_yes_tx的数值为0， 说明此时还没有半同步复制的sql语句被执行。主库写入数据后，Rpl_semi_sync_master_yes_tx的数值为sql语句的数量
slave2加入主从复制&并行复制&半同步复制
[root@mysql-slave2 ~]# cp /etc/my.cnf{,.bak} [root@mysql-slave2 ~]# > /etc/my.cnf [root@mysql-slave2 ~]# vim /etc/my.cnf [mysqld] datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock symbolic-links = 0 log-error = /var/log/mysqld.log pid-file = /var/run/mysqld/mysqld.pid #GTID: server_id = 3 gtid_mode = on enforce_gtid_consistency = on #binlog log_bin = mysql-bin log-slave-updates = 1 binlog_format = row sync-master-info = 1 sync_binlog = 1 #relay log skip_slave_start = 1 read_only = on #MTS slave-parallel-type = LOGICAL_CLOCK #开启逻辑时钟的复制 slave-parallel-workers = 4 #最大线程16 master_info_repository = TABLE relay_log_info_repository = TABLE relay_log_recovery = on # 开启半同步复制 plugin-load=rpl_semi_sync_slave=semisync_slave.so rpl_semi_sync_slave_enabled=1
[root@mysql-slave2 ~]# systemctl restart mysqld
[root@mysql-slave2 ~]# mysql -uroot -p Enter password: mysql> show global variables like 'gtid_%'; +----------------------------------+-------+ | Variable_name | Value | +----------------------------------+-------+ | gtid_executed | | | gtid_executed_compression_period | 1000 | | gtid_mode | ON | | gtid_owned | | | gtid_purged | | +----------------------------------+-------+ 5 rows in set (0.01 sec) mysql> stop slave; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> change master to master_host='192.168.131.129',master_user='slave',master_password='Slave@123',master_auto_position=1; # 开启主从复制 Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.02 sec) mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.131.129 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 194 Relay_Log_File: mysql-slave2-relay-bin.000003 Relay_Log_Pos: 407 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes mysql> show variables like '%slave_para%'; # 查看并行复制 +------------------------+---------------+ | Variable_name | Value | +------------------------+---------------+ | slave_parallel_type | LOGICAL_CLOCK | | slave_parallel_workers | 4 | +------------------------+---------------+ 2 rows in set (0.00 sec) mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so'; # 开启半同步复制 Query OK, 0 rows affected (0.04 sec) mysql> select plugin_name, -> plugin_status from information_schema.plugins -> where plugin_name like '%semi%'; +----------------------+---------------+ | plugin_name | plugin_status | +----------------------+---------------+ | rpl_semi_sync_slave | ACTIVE | | rpl_semi_sync_master | ACTIVE | +----------------------+---------------+ 2 rows in set (0.00 sec) mysql> stop slave IO_THREAD; Query OK, 0 rows affected (0.00 sec) mysql> start slave IO_THREAD; Query OK, 0 rows affected (0.00 sec) mysql> show status like 'Rpl_semi_sync_slave_status'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Rpl_semi_sync_slave_status | ON | +----------------------------+-------+ 1 row in set (0.00 sec)
# 回到mysql-master1主数据库查看 mysql> show slave hosts; +-----------+------+------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+------+------+-----------+--------------------------------------+ | 3 | | 3306 | 1 | 5d138126-db73-11ea-988b-000c29bef1e6 | | 2 | | 3306 | 1 | 5d0fecd7-db73-11ea-b20e-000c2986ee9d | +-----------+------+------+-----------+--------------------------------------+ 2 rows in set (0.00 sec) # mysql-master1主数据库现在有两个从数据库，分别为mysql-slave1 和 mysql-slave2 mysql> show status like '%Rpl_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 | 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 | 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.01 sec) # mysql-master1主数据库现在有两个半同步复制的从库，即mysql-slave1 和mysql-slave2
