主从复制三大模式

最新推荐文章于 2024-04-27 15:33:16 发布

mo石mo

最新推荐文章于 2024-04-27 15:33:16 发布

阅读量4.9k

点赞数 31

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_52077701/article/details/127151366

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

一、半同步模式

Ⅰ、MySQL半同步复制的问题

半同步测试搭建

【二进制日志和redo, undo的关系】

【二进制的作用：】

二、异步模式：

三、同步模式

(组复制)group replication

一、半同步模式

sync :同步

async : 异步

semi-sync : 半同步

Ⅰ、MySQL半同步复制的问题

- rpl_semi_sync_master_wait_point 等待slave的ack确认

- AFTER_SYNC(默认)︰源将每个事务写入其二进制日志和副本，并将二进制日志同步到磁盘。同步后，源将等待事务接收的副本确认。收到确认后，源将事务提交到存储擎，并将结果返回给客户端，然后客户端可以继续进行。

- AFTER CONNIT:源将每个事务写入其二进制日志和副本中，同步二进制日志，然后副事务提交到存储引擎。提交后，源将等待事务回执的副本确认。收到确认后，源将结果返回给客户端，然后客户端可以继续进行。

下面步骤、结构图更简单直观

半同步测试搭建

master上：

#1、在master上
    ---安装插件
shimm@TestDB 15:37  mysql>install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)
    --- 查看状态
shimm@TestDB 15:37  mysql>show plugins;
#启用半同步
shimm@TestDB 15:40  mysql>set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)
shimm@TestDB 15:40  mysql>show global variables like '%rpl_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)
shimm@TestDB 15:42  mysql>
#2、slave上的操作
    ---安装插件    
root@(none) 15:48  mysql>install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.03 sec)
    --- 查看状态
root@(none) 15:52  mysql>show plugins;
……………………
| rpl_semi_sync_master       | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |
+----------------------------+----------+--------------------+--------------------+---------+
45 rows in set (0.01 sec)
    --- 启用半同步
root@(none) 15:53  mysql>set global rpl_semi_sync_slave_enabled=1;   #(0/1)  -->1是开启状态，0 是关闭状态
Query OK, 0 rows affected (0.00 sec)
    --- 刷新服务
root@(none) 15:53  mysql>stop slave;
Query OK, 0 rows affected (0.03 sec)
root@(none) 15：52  mysql>start slave;
Query OK, 0 rows affected (0.00 sec)
root@(none) 16:06  mysql>show global variables like '%rpl_semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_slave_enabled               | ON         |
| rpl_semi_sync_slave_trace_level           | 32         |
+-------------------------------------------+------------+
8 rows in set (0.01 sec)
root@(none) 16:12  mysql>
#3、半同步测试
从库停止复制以后在主库建表测试

rpl_semi_sync_master_timeout,the value N is glven in milliseconds.The sefault value is 10000(10 seconds).

如果ack确认包在10秒钟内没有送达，master会启用异步模式。

slave上：

#slave服务器上
root@(none) 16:16  mysql>stop slave;
Query OK, 0 rows affected (0.00 sec)
root@(none) 16:23  mysql>
#主服务器上
shimm@TestDB 16:17  mysql>create database s;
Query OK, 1 row affected (10.00 sec)
shimm@TestDB 16:23  mysql>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| TENNIS             |
| TestDB             |
| apiserver          |
| flask_SM           |
| flask_TM           |
| mysql              |
| performance_schema |
| s                  |
| sys                |
| training           |
+--------------------+
11 rows in set (0.00 sec)
#在master上查看，slave的复制账号一直连接到master上
root@(none) 17:02  mysql>show processlist;
+----+----------+---------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User     | Host                | db   | Command     | Time | State                                                         | Info             |
+----+----------+---------------------+------+-------------+------+---------------------------------------------------------------+------------------+
|  2 | root     | localhost           | NULL | Query       |    0 | starting                                                      | show processlist |
|  4 | sc_slave | 192.168.8.142:36634 | NULL | Binlog Dump |  138 | Master has sent all binlog to slave; waiting for more updates | NULL             |
+----+----------+---------------------+------+-------------+------+---------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
root@(none) 17:03  mysql>

master服务器/etc/my.cnf里加入：

rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000 # 1second

socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
log_bin
server_id=1
expire_logs_days = 7
gtid-mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000 # 1second
innodb_buffer_pool_size = 512M
character-set-server=utf8

slave服务器/etc/my.cnf里加入：

rpl_semi_sync_slave_enabled=1

socket=/data/mysql/mysql.sock
port = 3306
server_id = 2
log_bin
gtid-mode=ON
enforce-gtid-consistency=ON
log_slave_updates=ON
rpl_semi_sync_slave_enabled=1
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8