mysql的主从复制有以下三张方式
一、异步复制
在异步复制时,主库执行Commit提交操作并写入BINLOG日志后即可成功返回客户端,无需等待BINLOG日志传送给从库,如图所示。
image.png
二、半同步复制
半同步复制时,为了保证主库上的每一个BINLOG事务都能够被可靠地复制到从库上,主库在每次事务成功提交时,并不及时反馈给前端应用用户,而是等待至少一个从库(详见参数rpl_semi_sync_master_wait_for_slave_count)也接收到BINLOG事务并成功写入中继日志后,主库才返回Commit操作成功给客户端(不管是传统的半同步复制,还是增强的半同步复制,目的都是一样的,只不过两种方式有一个席位地方不同,将在下面说明)
半同步复制保证了事务成功提交后,至少有两份日志记录,一份在主库的BINLOG日志上,另一份在至少一个从库的中继日志Relay Log上,从而更进一步保证了数据的完整性。
半同步复制分为传统的半同步复制和增强半同步复制(也称无损复制)
在传统的半同步复制中,主库写数据到BINLOG,且执行Commit操作后,会一直等待从库的ACK,即从库写入Relay Log后,并将数据落盘,返回给主库消息,通知主库可以返回前端应用操作成功,这样会出现一个问题,就是实际上主库已经将该事务Commit到了事务引擎层,应用已经可以看到数据发生了变化,只是在等待返回而已,如果此时主库宕机,有可能从库还没能写入Relay Log,就会发生主从库不一致。增强半同步复制就是为了解决这个问题,做了微调,即主库写数据到BINLOG后,就开始等待从库的应答ACK,直到至少一个从库写入Relay Log后,并将数据落盘,然后返回给主库消息,通知主库可以执行Commit操作,然后主库开始提交到事务引擎层,应用此时可以看到数据发生了变化。增强半同步复制的大致流程如下图所示
image.png
master1和master2上安装插件
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.05 sec)
mysql> set global rpl_semi_sync_master_enabled = 1;//热生效
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'rpl_semi_sync%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |//超时10秒,没有收到slave的ack应答则切换回异步同步
| 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.01 sec)
mysql> show status like 'rpl_semi_sync%'
-> ;
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |//slave端未打开
| 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)
masrer1,master2,slave上安装从库插件
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.19 sec)
mysql> set global rpl_semi_sync_slave_enabled=1;//热生效
Query OK, 0 rows affected (0.01 sec)
mysql> show status like 'rpl_semi_sync%';//查看状态
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |
+----------------------------+-------+
1 row in set (0.03 sec)
mysql> stop slave io_thread;//重启io线程
Query OK, 0 rows affected (0.02 sec)
mysql> start slave io_thread;
Query OK, 0 rows affected (0.02 sec)
mysql> show status like 'rpl_semi_sync%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
编辑配置文件使配置生效,vi /etc/my.cnf
,增加rpl_semi_sync_slave_enabled=1
再次查看slave状态
mysql> show status like 'rpl_semi_sync%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.01 sec)
查看master上的状态
mysql> show status like 'rpl_semi_sync%';
+--------------------------------------------+-------+
| 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.01 sec)
master上编辑/etc/my.cnf
增加rpl_semi_sync_master_enabled=1
测试
在master上新增数据,查看半同步情况
mysql> show status like 'rpl_semi_sync%';
+--------------------------------------------+-------+
| 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 | 1 |
| 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 | 2598 |
| Rpl_semi_sync_master_tx_wait_time | 2598 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |//半同步成功条数
+--------------------------------------------+-------+
14 rows in set (0.02 sec)
测试半同步超时自动切换为异步同步,在slave上停止io线程
mysql> stop slave io_thread;
master上新增数据
mysql>INSERT INTO `master`.`im_article`( `user_id`, `class_id`, `tags_id`, `title`, `abstract`, `image`, `is_asterisk`, `status`, `created_at`, `updated_at`, `deleted_at`) VALUES ( 1, 1, '1', '1', '1', '1', 1, 1, '2023-07-05 13:46:08', '2023-07-05 13:46:04', '2023-07-05 13:46:11');
Query OK, 1 rows affected (10.05 sec)//10秒后启用异步复制,用时0.05秒进行数据同步
mysql> show status like 'rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |//io关闭后,此时没有关联的从库
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 1 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 1 |//此处变为1,未使用半同步成功的条数
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 2598 |
| Rpl_semi_sync_master_tx_wait_time | 2598 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |//半同步成功条数
+--------------------------------------------+-------+
14 rows in set (0.02 sec)
网友评论