美文网首页面向简历编程(重学php)
1.5 配置mysql高可用-使用增强型半同步复制(无损复制)

1.5 配置mysql高可用-使用增强型半同步复制(无损复制)

作者: 回眸淡然笑 | 来源:发表于2023-07-05 09:44 被阅读0次

    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)
    

    相关文章

      网友评论

        本文标题:1.5 配置mysql高可用-使用增强型半同步复制(无损复制)

        本文链接:https://www.haomeiwen.com/subject/hzlhudtx.html