MySQL study之主从复制--半同步复制

以你之姓@ 2022-07-14 00:52 283阅读 0赞

**MySQL study之主从复制--半同步复制**

**![Center][]**

MySQL半同步复制和异步复制的差别如上述架构图所示：在mysql异步复制的情况下，Mysql Master Server将自己的Binary Log通过复制线程传输出去以后，Mysql MasterSever就自动返回数据给客户端，而不管slave上是否接受到了这个二进制日志。在半同步复制的架构下，当master在将自己binlog发给slave上的时候，要确保slave已经接受到了这个二进制日志以后，才会返回数据给客户端。对比两种架构：异步复制对于用户来说，可以确保得到快速的响应结构，但是不能确保二进制日志确实到达了slave上；半同步复制对于客户的请求响应稍微慢点，但是他可以保证二进制日志的完整性。

**一、配置主从同步**

**主服务器my.cnf：**

**\[root@rh6 ~\]\# cat /etc/my.cnf**

\[mysqld\]

log-bin=mysql-bin

binlog\_format=mixed

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

socket=/tmp/mysql.sock

user=mysql

server\_id = 1

**\# rpl\_semi\_sync\_master\_enabled = 1**

**\# rpl\_semi\_sync\_master\_timeout = 1000**

sql\_mode=NO\_ENGINE\_SUBSTITUTION,STRICT\_TRANS\_TABLES

**在主服务器上授权从服务器用户访问：**

**master 授权：**

**mysql> GRANT REPLICATION SLAVE ON \*.\* to 'tom'@'%' identified by'oracle';**

Query OK, 0 rows affected (0.01 sec)

mysql> use mysql;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

**mysql> select user,host,password from user;**

\+------+-----------+-------------------------------------------+

**| tom  | %         | \*2447D497B9A6A15F2776055CB2D1E9F86758182F|**

\+------+-----------+-------------------------------------------+

7 rows in set (0.00 sec)

**查看master服务器状态：**

mysql> show master status;

\+------------------+----------+--------------+------------------+

\+------------------+----------+--------------+------------------+

**| mysql-bin.000003 |      317|              |                  |**

\+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

**配置从服务器：**

**从服务器my.cnf：**

**\[root@mysrv01 ~\]\# cat /etc/my.cnf**

**\[mysqld\]**

sql\_mode=NO\_ENGINE\_SUBSTITUTION,STRICT\_TRANS\_TABLES

relay-log = relay-log

relay-log-index = relay-log.index

skip-slave-start=1

log-bin=mysql-bin

binlog\_format=mixed

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

socket=/tmp/mysql.sock

user=mysql

innodb\_buffer\_pool\_size=1GB

innodb\_buffer\_pool\_instances=2

symbolic-links=0

init\_connect='set autocommit=0'

server-id=2

**\# rpl\_semi\_sync\_slave\_enabled = 1**

**\[mysqld\_safe\]**

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

** 登陆从服务器验证：**

**\[root@mysrv01 ~\]\# mysql -u root -p**

Enter password:

**mysql> show variables like '%relay%';**

\+---------------------------+---------------------------------------+

| Variable\_name             |Value                                 |

\+---------------------------+---------------------------------------+

| max\_relay\_log\_size        |0                                     |

| relay\_log                 |relay-log                             |

| relay\_log\_basename        |/usr/local/mysql/data/relay-log       |

| relay\_log\_index           |/usr/local/mysql/data/relay-log.index |

| relay\_log\_info\_file       |relay-log.info                        |

| relay\_log\_info\_repository | FILE                                  |

| relay\_log\_purge           | ON                                    |

| relay\_log\_recovery        |OFF                                   |

| relay\_log\_space\_limit     |0                                     |

| sync\_relay\_log            |10000                                 |

| sync\_relay\_log\_info       |10000                                 |

\+---------------------------+---------------------------------------+

11 rows in set (0.02 sec)

**配置从服务器连接主服务器：**

**mysql> change master tomaster\_host='192.168.8.200',master\_user='tom',master\_password='oracle',master\_log\_file='mysql-bin.000003',master\_log\_pos=317;**

Query OK, 0 rows affected, 2 warnings (0.14 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.8.200

Master\_User:tom

Master\_Port:3306

Connect\_Retry:60

Master\_Log\_File:mysql-bin.000003

Read\_Master\_Log\_Pos:317

Relay\_Log\_File:relay-log.000002

Relay\_Log\_Pos:283

Relay\_Master\_Log\_File:mysql-bin.000003

**Slave\_IO\_Running:Yes                           ；；从服务器IO thread启动，用于接收relay log并写入本地relay log files**

**            Slave\_SQL\_Running:Yes                         ；； 从服务器SQL thread启动，对relay log应用并执行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:317

Relay\_Log\_Space:450

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

Master\_SSL\_Verify\_Server\_Cert: No

Last\_IO\_Errno:0

Last\_IO\_Error:

Last\_SQL\_Errno:0

Last\_SQL\_Error:

Replicate\_Ignore\_Server\_Ids:

Master\_Server\_Id:1

Master\_UUID:0954ca39-ac09-11e6-b4ab-08002786d9b2

Master\_Info\_File:/usr/local/mysql/data/master.info

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

Master\_Retry\_Count:86400

Master\_Bind:

Last\_IO\_Error\_Timestamp:

Last\_SQL\_Error\_Timestamp:

Master\_SSL\_Crl:

Master\_SSL\_Crlpath:

Retrieved\_Gtid\_Set:

Executed\_Gtid\_Set:

Auto\_Position:0

1 row in set (0.00 sec)

**二、配置半同步：**

**主服务器配置：**

**mysql>  install plugin rpl\_semi\_sync\_masterSONAME 'semisync\_master.so';                      ；；安装半同步插件**

Query OK, 0 rows affected (0.04 sec)

mysql> show 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    |

\+------------------------------------+-------+

4 rows in set (0.00 sec)

**半同步相关参数：**

**| 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 | 没有slave的时候是否也需要等待，默认为也需要等待**

**激活半同步：**

**mysql> set global rpl\_semi\_sync\_master\_enabled = 1;                   ；；将这两个参数写入到my.cnf**

Query OK, 0 rows affected (0.00 sec)

**mysql> set global rpl\_semi\_sync\_master\_timeout = 1000;**

Query OK, 0 rows affected (0.00 sec)

**mysql> show variables like "%semi%";**

\+------------------------------------+-------+

| Variable\_name                     | Value |

\+------------------------------------+-------+

**| rpl\_semi\_sync\_master\_enabled      | ON    |**

**| rpl\_semi\_sync\_master\_timeout      | 1000  |**

| rpl\_semi\_sync\_master\_trace\_level  | 32    |

| rpl\_semi\_sync\_master\_wait\_no\_slave | ON    |

\+------------------------------------+-------+

4 rows in set (0.00 sec)

**从服务器配置：**

**mysql> install plugin rpl\_semi\_sync\_slave SONAME'semisync\_slave.so';                ；；安装客户端半同步插件**

Query OK, 0 rows affected (0.04 sec)

**mysql> show variables like "%semi%";**

\+---------------------------------+-------+

| Variable\_name                  | Value |

\+---------------------------------+-------+

| rpl\_semi\_sync\_slave\_enabled    | OFF   |

| rpl\_semi\_sync\_slave\_trace\_level | 32    |

\+---------------------------------+-------+

2 rows in set (0.00 sec)

**激活半同步：**

**mysql> set global rpl\_semi\_sync\_slave\_enabled = 1;                         ；；将参数写入到my.cnf**

Query OK, 0 rows affected (0.00 sec)

**mysql> show variables like "%semi%";**

\+---------------------------------+-------+

| Variable\_name                  | Value |

\+---------------------------------+-------+

| rpl\_semi\_sync\_slave\_enabled    | ON    |

| rpl\_semi\_sync\_slave\_trace\_level | 32    |

\+---------------------------------+-------+

2 rows in set (0.00 sec)

**重新启动slave service：**

**mysql> stop slave;**

Query OK, 0 rows affected (0.07 sec)

**mysql> start slave;**

Query OK, 0 rows affected (0.01 sec)

**三、将半同步配置参数写入到my.cnf**

**在主服务器my.cnf文件中添加两行信息：**

rpl\_semi\_sync\_master\_enabled = 1

rpl\_semi\_sync\_master\_timeout = 1000

**然后重启server：**

**mysql> show variables like "%semi%";**

\+------------------------------------+-------+

| Variable\_name                     | Value |

\+------------------------------------+-------+

| rpl\_semi\_sync\_master\_enabled      | ON    |

| rpl\_semi\_sync\_master\_timeout      | 1000  |

| rpl\_semi\_sync\_master\_trace\_level  | 32    |

| rpl\_semi\_sync\_master\_wait\_no\_slave | ON    |

\+------------------------------------+-------+

4 rows in set (0.00 sec)

**在从服务器my.cnf文件中添加：**

rpl\_semi\_sync\_slave\_enabled = 1

**重启从服务器server：**

**mysql> show variables like "%semi%";**

\+---------------------------------+-------+

| Variable\_name                  | Value |

\+---------------------------------+-------+

| rpl\_semi\_sync\_slave\_enabled    | ON    |

| rpl\_semi\_sync\_slave\_trace\_level | 32    |

\+---------------------------------+-------+

2 rows in set (0.00 sec)

**mysql> start slave;**

Query OK, 0 rows affected (0.03 sec)

**mysql> show slave status\\G**

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Slave\_IO\_State:Waiting for master to send event

Master\_Host:192.168.8.200

Master\_User:tom

Master\_Port:3306

Connect\_Retry:60

Master\_Log\_File:mysql-bin.000004

Read\_Master\_Log\_Pos:120

Relay\_Log\_File:relay-log.000007

Relay\_Log\_Pos:283

Relay\_Master\_Log\_File:mysql-bin.000004

**Slave\_IO\_Running:Yes**

**            Slave\_SQL\_Running:Yes**

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:120

Relay\_Log\_Space:450

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

Master\_SSL\_Verify\_Server\_Cert: No

Last\_IO\_Errno:0

Last\_IO\_Error:

Last\_SQL\_Errno:0

Last\_SQL\_Error:

Replicate\_Ignore\_Server\_Ids:

Master\_Server\_Id:1

Master\_UUID:0954ca39-ac09-11e6-b4ab-08002786d9b2

Master\_Info\_File:/usr/local/mysql/data/master.info

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**

Master\_Retry\_Count:86400

Master\_Bind:

Last\_IO\_Error\_Timestamp:

Last\_SQL\_Error\_Timestamp:

Master\_SSL\_Crl:

Master\_SSL\_Crlpath:

Retrieved\_Gtid\_Set:

Executed\_Gtid\_Set:

Auto\_Position:0

1 row in set (0.00 sec)

**四、主从复制同步测试：**

**主服务器做事务处理：**

**mysql> show databases;**

\+--------------------+

| Database           |

\+--------------------+

| information\_schema |

| mysql              |

| performance\_schema |

| test               |

\+--------------------+

4 rows in set (0.02 sec)

**mysql> create database prod;**

Query OK, 1 row affected (0.00 sec)

**mysql> use prod;**

Database changed

**mysql> create table t1 (id int);**

Query OK, 0 rows affected (0.07 sec)

**mysql> insert into t1 values (10),(20),(30);**

Query OK, 3 rows affected (0.04 sec)

Records: 3  Duplicates: 0  Warnings: 0

**mysql> commit;**

Query OK, 0 rows affected (0.00 sec)

** 查看数据：**

**mysql> select \* from t1;**

\+------+

| id   |

\+------+

|   10 |

|   20 |

|   30 |

\+------+

3 rows in set (0.00 sec)

** **

**从服务器查看同步信息：**

**mysql> show databases;**

\+--------------------+

| Database           |

\+--------------------+

| information\_schema |

| mysql              |

| performance\_schema |

| prod               |

| test               |

\+--------------------+

5 rows in set (0.05 sec)

**mysql> use prod;**

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

**mysql> select \* from t1;**

\+------+

| id   |

\+------+

|    1 |

|    2 |

|    3 |

\+------+

3 rows in set (0.00 sec)

**五、查看半同步相关参数**

**mysql> show status like "%semi%";**

\+-------------------------------------------------------------------+----------+

| Variable\_name | Value |

\+-------------------------------------------------------------------+----------+

| Rpl\_semi\_sync\_master\_clients | 1 | 半同步复制客户端的个数

| Rpl\_semi\_sync\_master\_net\_avg\_wait\_time | 555 | 平均等待时间（默认毫秒）

| Rpl\_semi\_sync\_master\_net\_wait\_time | 1665 | 总共等待时间

| Rpl\_semi\_sync\_master\_net\_waits | 3 | 等待次数

| Rpl\_semi\_sync\_master\_no\_times | 0 | 关闭半同步复制的次数

| Rpl\_semi\_sync\_master\_no\_tx | 0 | 表示没有成功接收slave提交的次数

| Rpl\_semi\_sync\_master\_status | ON | 表示当前是异步模式还是半同步模式，on为半同步

| Rpl\_semi\_sync\_master\_timefunc\_failures | 0 | 调用时间函数失败的次数

| Rpl\_semi\_sync\_master\_tx\_avg\_wait\_time | 575 | 事物的平均传输时间

| Rpl\_semi\_sync\_master\_tx\_wait\_time | 1725 | 事物的总共传输时间

| Rpl\_semi\_sync\_master\_tx\_waits | 3 | 事物等待次数

| Rpl\_semi\_sync\_master\_wait\_pos\_backtraverse | 0 |

| Rpl\_semi\_sync\_master\_wait\_sessions | 0 | 当前有多少个session因为slave的回复而造成等待

| Rpl\_semi\_sync\_master\_yes\_tx | 3 | 成功接受到slave事物回复的次数

\+-------------------------------------------------------------------+---------+

**取消半同步复制的插件：**

**主服务器：**

\# mysql > uninstall plugin rpl\_semi\_sync\_master;

\# mysql > show status like "%semi%"

**从服务器：**

\# mysql > uninstall plugin rpl\_semi\_sync\_slave;

\# mysql > show status like "%semi%"

======== 至此@主从复制半同步配置完成 ！

[Center]: /images/20220714/1726cb6e834a4b79a2b43e78f54d4049.png

发表评论取消回复

表情：

评论列表（有 0 条评论，283人围观）

MySQL study之主从复制--半同步复制

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL主从复制原理 | 异步复制 | 半同步复制 | GTID复制

相关 MySQL 主从复制--半同步复制

相关配置mysql5.5主从复制、半同步复制、主主复制

相关 MySQL Study之--MySQL主从复制类型

相关 MySQL半同步复制

相关 MySQL study之主从复制--半同步复制

相关 mysql 主从复制基于gtid的同步复制，并行复制，半同步复制

相关 MySQL的主从复制、GTID复制，半同步复制，组复制

相关 mysql的主从复制、基于GDIT主从复制/半同步复制

相关 Mysql进阶之主从复制、基于gtid的主从复制、半同步复制、组复制