mariadb mysql同步_MySQL/MariaDB数据库的半同步复制

最新推荐文章于 2022-04-22 18:38:40 发布

weixin_39829501

最新推荐文章于 2022-04-22 18:38:40 发布

阅读量176

点赞数

文章标签： mariadb mysql同步

本文链接：https://blog.csdn.net/weixin_39829501/article/details/113586191

版权

MySQL/MariaDB数据库的半同步复制

作者：尹正杰

一.MySQL半同步复制概述

1>.MySQL默认的异步复制

默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库即结束，并不验证从库是否接收完毕。

这意味着当主服务器或从服务器端发生故障时，有可能从服务器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失的风险。

2>.MySQL的半同步复制

为了方便说明，我画了一个草图,如下图所示:

我们模拟在生产环境中半同步复制的场景，正如我图中所示,现在的MySQL架构是一主两从的架构。当用户访问数据库的流程大致如下:

1>.用户通过调度器访问到master节点，要求进行写操作,如果只是读操作起始也可以请求到任意一台slave节点;

2>.当master节点完成客户端提交的事务后,并不会立即响应客户端，而是需要等待它的两个从节点中的任意一个节点完成信息同步;

3>.当slave节点中任意一个节点数据和master数据同步后，master节点就立即响应客户端说操作执行成功，而不会等待另一个节点也同步完成。

温馨提示:

在某种特殊的场景下，复制过程可能需要较长时间,如果在规定时间内(这个时间有咱们根据业务需求来自定义),所有的slave节点都无法完成数据库同步,其实master也会返回给客户端执行完毕。

如果真有这种情况发生,这就意味着只有主库的数据是准确的，因为没有任何一个从节点数据是准确的!这个时候我们就会意识到一个道理，主从复制并不能代替备份!因此当我们做了主从复制时，千万要记得备份master节点数据啊!(不仅如此，二进制日志也是相当重要的,强烈建议和数据文件分开存放)这样即使当master节点挂掉我们也可以通过它的二进制日志配合slave节点快速手动完成数据同步。

3>.半同步复制的应用场景

通过上面的分析，我们已经对半同步复制的原理有所了解，它不确保所有数据库都同步，它只需要确定有任意一台slave节点和master节点数据同步即可。

那这种模式的应用场景在哪呢？

我们应该从它的优点和缺点进行分析，然后选择合适的场景。

优点:当master挂掉后,我们可以从一个之前和master同步数据的slave节点快速恢复生产环境使用，这个过程不需要太多时间，因为我们知道有一台slave节点数据和master是同步的。

缺点:当master完成客户端提交的事务后，此时需要等待slave节点同步数据，这个过程会降低mysql的性能，因为master节点已经完成了任务，等待slave同步的过程用户也是需要承担的。

综上分析，我个人决定它适合使用在对数据可靠性较高的且对MySQL的延迟时间可以忍受的场景。

二.半同步复制实战案例

1>.试验环境说明

半同步复制概述:

当主库执行一个更新操作事物时，提交操作会被阻止直到至少有一个半同步的复制slave确认依据接收到本次更新操作，主库的提交操作才会继续。

半同步复制的slave发送确认消息只会在本次更新操作已经记录到本地的relay log之后

如果没有任何slave发送确认消息而导致超时时，半同步复制会转换成异步复制。

半同步复制会对MySQL性能产生影响，因为主库的提交动作只有在收到至少一个从库的确认消息之后才能执行。但这个功能是性能和数据可靠性方面的权衡。

需要开启的系统参数包括：

rpl_semi_sync_master_enabled:在主库配置，确保主库的半同步复制功能开启。

rpl_semi_sync_master_timeout:配置主库等待多少毫秒时间来保证接收备库的确认消息，当超时这个时间时，半同步变成异步方式。

rpl_semi_sync_slave_enabled：在从库配置，确保从库的半同步复制功能开启。

半同步复制是通过插件的方式建立，要分别在主库和从库安装一个插件半同步复制的前提条件：

MySQL5.5版本及以上。

have_dynamic_loading参数必须是YES代表可以安装插件并动态加载。

实现建立好异步复制关系

相关插件安装文件会在plugin_dir文件夹下，并以semisync_master和semisync_slave名称开头。下面是本次试验角色分配:

node102.yinzhengjie.org.cn : master节点

node103.yinzhengjie.org.cn : slave节点

node104.yinzhengjie.org.cn : slave节点

2>.配置node102.yinzhengjie.org.cn节点与其它两个节点主从复制

[root@node102.yinzhengjie.org.cn ~]# cat /etc/my.cnf

[mysqld]

server-id = 102binlog_format=row

log_bin= /data/mysql/logbin/master-102character-set-server =utf8mb4

default_storage_engine=InnoDB

datadir= /var/lib/mysql

socket= /var/lib/mysql/mysql.sock

[mysqld_safe]

log-error = /var/log/mariadb/mariadb.log

pid-file = /var/run/mariadb/mariadb.pid!includedir /etc/my.cnf.d

[root@node102.yinzhengjie.org.cn~]#

[root@node102.yinzhengjie.org.cn ~]# cat /etc/my.cnf

[root@node102.yinzhengjie.org.cn ~]# ll /data/mysql/logbin/total0[root@node102.yinzhengjie.org.cn~]#

[root@node102.yinzhengjie.org.cn~]# ll /var/lib/mysql/total0[root@node102.yinzhengjie.org.cn~]#

[root@node102.yinzhengjie.org.cn~]# systemctl start mariadb

[root@node102.yinzhengjie.org.cn~]#

[root@node102.yinzhengjie.org.cn~]# ll /var/lib/mysql/total37852

-rw-rw---- 1 mysql mysql 16384 Nov 10 07:13 aria_log.00000001

-rw-rw---- 1 mysql mysql 52 Nov 10 07:13aria_log_control-rw-rw---- 1 mysql mysql 18874368 Nov 10 07:13ibdata1-rw-rw---- 1 mysql mysql 5242880 Nov 10 07:13ib_logfile0-rw-rw---- 1 mysql mysql 5242880 Nov 10 07:13ib_logfile1

drwx------ 2 mysql mysql 4096 Nov 10 07:13mysql

srwxrwxrwx1 mysql mysql 0 Nov 10 07:13mysql.sock

drwx------ 2 mysql mysql 4096 Nov 10 07:13performance_schema

drwx------ 2 mysql mysql 6 Nov 10 07:13test

[root@node102.yinzhengjie.org.cn~]#

[root@node102.yinzhengjie.org.cn~]# ll /data/mysql/logbin/total940

-rw-rw---- 1 mysql mysql 26813 Nov 10 07:13 master-102.000001

-rw-rw---- 1 mysql mysql 921736 Nov 10 07:13 master-102.000002

-rw-rw---- 1 mysql mysql 245 Nov 10 07:13 master-102.000003

-rw-rw---- 1 mysql mysql 111 Nov 10 07:13 master-102.index

[root@node102.yinzhengjie.org.cn~]#

[root@node102.yinzhengjie.org.cn ~]# systemctl start mariadb

[root@node102.yinzhengjie.org.cn ~]# ll /data/mysql/logbin/total940

-rw-rw---- 1 mysql mysql 26813 Nov 10 07:13 master-102.000001

-rw-rw---- 1 mysql mysql 921736 Nov 10 07:13 master-102.000002

-rw-rw---- 1 mysql mysql 245 Nov 10 07:13 master-102.000003

-rw-rw---- 1 mysql mysql 111 Nov 10 07:13 master-102.index

[root@node102.yinzhengjie.org.cn~]#

[root@node102.yinzhengjie.org.cn~]# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connectionid is 5Server version:5.5.64-MariaDB MariaDB Server

Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.

MariaDB [(none)]>MariaDB [(none)]>SHOW MASTER LOGS;+-------------------+-----------+

| Log_name | File_size |

+-------------------+-----------+

| master-102.000001 | 26813 |

| master-102.000002 | 921736 |

| master-102.000003 | 245 |

+-------------------+-----------+

3 rows in set (0.00sec)

MariaDB [(none)]>MariaDB [(none)]>SELECT user,host,password FROM mysql.user;+------+----------------------------+----------+

| user | host | password |

+------+----------------------------+----------+

| root | localhost | |

| root | node102.yinzhengjie.org.cn | |

| root | 127.0.0.1 | |

| root | ::1 | |

| | localhost | |

| | node102.yinzhengjie.org.cn | |

+------+----------------------------+----------+

6 rows in set (0.00sec)

MariaDB [(none)]>MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'copy'@'172.30.1.10%' IDENTIFIED BY 'yinzhengjie';

Query OK,0 rows affected (0.00sec)

MariaDB [(none)]>MariaDB [(none)]>SELECT user,host,password FROM mysql.user;+------+----------------------------+-------------------------------------------+

| user | host | password |

+------+----------------------------+-------------------------------------------+

| root | localhost | |

| root | node102.yinzhengjie.org.cn | |

| root | 127.0.0.1 | |

| root | ::1 | |

| | localhost | |

| | node102.yinzhengjie.org.cn | |

| copy | 172.30.1.10% | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |

+------+----------------------------+-------------------------------------------+

7 rows in set (0.00sec)

MariaDB [(none)]>MariaDB [(none)]>MariaDB [(none)]>SHOW MASTER LOGS;+-------------------+-----------+

| Log_name | File_size |

+-------------------+-----------+

| master-102.000001 | 26813 |

| master-102.000002 | 921736 |

| master-102.000003 | 403 |

+-------------------+-----------+

3 rows in set (0.00sec)

MariaDB [(none)]>

node102.yinzhengjie.org.cn上创建有复制权限的账户(在此之前建议执行"show master logs"便于复制时指定)

[root@node103.yinzhengjie.org.cn ~]# cat /etc/my.cnf

[mysqld]

server-id = 103binlog_format=row

read-only

relay_log= relay-log-103relay_log_index= relay-log-103.index

character-set-server =utf8mb4

default_storage_engine=InnoDB

datadir= /var/lib/mysql

socket= /var/lib/mysql/mysql.sock

[mysqld_safe]

log-error = /var/log/mariadb/mariadb.log

pid-file = /var/run/mariadb/mariadb.pid!includedir /etc/my.cnf.d

[root@node103.yinzhengjie.org.cn~]#

[root@node103.yinzhengjie.org.cn ~]# cat /etc/my.cnf

[mysqld]

server-id = 103binlog_format=row

read-only =on

relay_log= relay-log-103relay_log_index= relay-log-103.index

character-set-server =utf8mb4

default_storage_engine=InnoDB

datadir= /var/lib/mysql

socket= /var/lib/mysql/mysql.sock

[mysqld_safe]

log-error = /var/log/mariadb/mariadb.log

pid-file = /var/run/mariadb/mariadb.pid!includedir /etc/my.cnf.d

[root@node103.yinzhengjie.org.cn~]#

[root@node103.yinzhengjie.org.cn~]# ss -ntl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN0 128 *:22 *:*LISTEN0 128 :::22 :::*[root@node103.yinzhengjie.org.cn~]#

[root@node103.yinzhengjie.org.cn~]# ll /var/lib/mysql/total0[root@node103.yinzhengjie.org.cn~]#

[root@node103.yinzhengjie.org.cn~]# systemctl start mariadb

[root@node103.yinzhengjie.org.cn~]#

[root@node103.yinzhengjie.org.cn~]# ll /var/lib/mysql/total37852

-rw-rw---- 1 mysql mysql 16384 Nov 10 07:22 aria_log.00000001

-rw-rw---- 1 mysql mysql 52 Nov 10 07:22aria_log_control-rw-rw---- 1 mysql mysql 18874368 Nov 10 07:22ibdata1-rw-rw---- 1 mysql mysql 5242880 Nov 10 07:22ib_logfile0-rw-rw---- 1 mysql mysql 5242880 Nov 10 07:22ib_logfile1

drwx------ 2 mysql mysql 4096 Nov 10 07:22mysql

srwxrwxrwx1 mysql mysql 0 Nov 10 07:22mysql.sock

drwx------ 2 mysql mysql 4096 Nov 10 07:22performance_schema

drwx------ 2 mysql mysql 6 Nov 10 07:22test

[root@node103.yinzhengjie.org.cn~]#

[root@node103.yinzhengjie.org.cn~]# ss -ntl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN0 50 *:3306 *:*LISTEN0 128 *:22 *:*LISTEN0 128 :::22 :::*[root@node103.yinzhengjie.org.cn~]#

[root@node103.yinzhengjie.org.cn~]#

[root@node103.yinzhengjie.org.cn ~]# systemctl start mariadb

[root@node103.yinzhengjie.org.cn ~]# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connectionid is 2Server version:5.5.64-MariaDB MariaDB Server

Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.

MariaDB [(none)]>MariaDB [(none)]>SELECT user,host,password FROM mysql.user;+------+----------------------------+----------+

| user | host | password |

+------+----------------------------+----------+

| root | localhost | |

| root | node103.yinzhengjie.org.cn | |

| root | 127.0.0.1 | |

| root | ::1 | |

| | localhost | |

| | node103.yinzhengjie.org.cn | |

+------+----------------------------+----------+

6 rows in set (0.00sec)

MariaDB [(none)]>MariaDB [(none)]>CHANGE MASTER TO-> MASTER_HOST='172.30.1.102',-> MASTER_USER='copy',-> MASTER_PASSWORD='yinzhengjie',-> MASTER_PORT=3306,-> MASTER_LOG_FILE='master-102.000003',-> MASTER_LOG_POS=245,-> MASTER_CONNECT_RETRY=10;

Query OK,0 rows affected (0.01sec)

MariaDB [(none)]>MariaDB [(none)]>SLAVE START;

Query OK,0 rows affected (0.00sec)

MariaDB [(none)]>MariaDB [(none)]>SHOW SLAVE STATUS\G*************************** 1. row ***************************Slave_IO_State: Waitingformaster to send event

Master_Host:172.30.1.102Master_User: copy

Master_Port:3306Connect_Retry:10Master_Log_File: master-102.000003Read_Master_Log_Pos:403Relay_Log_File: relay-log-103.000002Relay_Log_Pos:688Relay_Master_Log_File: master-102.000003Slave_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:0Last_Error:

Skip_Counter:0Exec_Master_Log_Pos:403Relay_Log_Space:980Until_Condition: None

Until_Log_File:

Until_Log_Pos:0Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert: No

Last_IO_Errno:0Last_IO_Error:

Last_SQL_Errno:0Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id:102

1 row in set (0.00sec)

MariaDB [(none)]>MariaDB [(none)]>SELECT user,host,password FROM mysql.user;+------+----------------------------+-------------------------------------------+

| user | host | password |

+------+----------------------------+-------------------------------------------+

| root | localhost | |

| root | node103.yinzhengjie.org.cn | |

| root | 127.0.0.1 | |

| root | ::1 | |

| | localhost | |

| | node103.yinzhengjie.org.cn | |

| copy | 172.30.1.10% | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |

+------+----------------------------+-------------------------------------------+

7 rows in set (0.00sec)

MariaDB [(none)]>

在node103.yinzhengjie.org.cn中配置与node102.yinzhengjie.org.cn同步

[root@node104.yinzhengjie.org.cn ~]# cat /etc/my.cnf

[mysqld]

server-id = 104binlog_format=row

read-only =on

character-set-server =utf8mb4

default_storage_engine=InnoDB

datadir= /var/lib/mysql

socket= /var/lib/mysql/mysql.sock

[mysqld_safe]

log-error = /var/log/mariadb/mariadb.log

pid-file = /var/run/mariadb/mariadb.pid!includedir /etc/my.cnf.d

[root@node104.yinzhengjie.org.cn~]#

[root@node104.yinzhengjie.org.cn ~]# cat /etc/my.cnf

[mysqld]

server-id = 104binlog_format=row

read-only =on

character-set-server =utf8mb4

default_storage_engine=InnoDB

datadir= /var/lib/mysql

socket= /var/lib/mysql/mysql.sock

[mysqld_safe]

log-error = /var/log/mariadb/mariadb.log

pid-file = /var/run/mariadb/mariadb.pid!includedir /etc/my.cnf.d

[root@node104.yinzhengjie.org.cn~]#

[root@node104.yinzhengjie.org.cn~]# ll /var/lib/mysql/total0[root@node104.yinzhengjie.org.cn~]#

[root@node104.yinzhengjie.org.cn~]# systemctl start mariadb

[root@node104.yinzhengjie.org.cn~]#

[root@node104.yinzhengjie.org.cn~]# ll /var/lib/mysql/total37852

-rw-rw---- 1 mysql mysql 16384 Nov 10 07:30 aria_log.00000001

-rw-rw---- 1 mysql mysql 52 Nov 10 07:30aria_log_control-rw-rw---- 1 mysql mysql 18874368 Nov 10 07:30ibdata1-rw-rw---- 1 mysql mysql 5242880 Nov 10 07:30ib_logfile0-rw-rw---- 1 mysql mysql 5242880 Nov 10 07:30ib_logfile1

drwx------ 2 mysql mysql 4096 Nov 10 07:30mysql

srwxrwxrwx1 mysql mysql 0 Nov 10 07:30mysql.sock

drwx------ 2 mysql mysql 4096 Nov 10 07:30performance_schema

drwx------ 2 mysql mysql 6 Nov 10 07:30test

[root@node104.yinzhengjie.org.cn~]#

[root@node104.yinzhengjie.org.cn~]# ss -ntl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN0 50 *:3306 *:*LISTEN0 128 *:22 *:*LISTEN0 128 :::22 :::*[root@node104.yinzhengjie.org.cn~]#

[root@node104.yinzhengjie.org.cn ~]# systemctl start mariadb

[root@node104.yinzhengjie.org.cn ~]# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connectionid is 2Server version:5.5.64-MariaDB MariaDB Server

Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.

MariaDB [(none)]>SELECT user,host,password FROM mysql.user;+------+----------------------------+----------+

| user | host | password |

+------+----------------------------+----------+

| root | localhost | |

| root | node104.yinzhengjie.org.cn | |

| root | 127.0.0.1 | |

| root | ::1 | |

| | localhost | |

| | node104.yinzhengjie.org.cn | |

+------+----------------------------+----------+

6 rows in set (0.00sec)

Query OK,0 rows affected (0.01sec)

MariaDB [(none)]>MariaDB [(none)]>START SLAVE;

Query OK,0 rows affected (0.00sec)

MariaDB [(none)]>SELECT user,host,password FROM mysql.user;+------+----------------------------+-------------------------------------------+

| user | host | password |

+------+----------------------------+-------------------------------------------+

| root | localhost | |

| root | node104.yinzhengjie.org.cn | |

| root | 127.0.0.1 | |

| root | ::1 | |

| | localhost | |

| | node104.yinzhengjie.org.cn | |

| copy | 172.30.1.10% | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |

+------+----------------------------+-------------------------------------------+

7 rows in set (0.00sec)

MariaDB [(none)]>MariaDB [(none)]>SHOW SLAVE STATUS\G*************************** 1. row ***************************Slave_IO_State: Waitingformaster to send event

Master_Host:172.30.1.102Master_User: copy

Master_Port:3306Connect_Retry:10Master_Log_File: master-102.000003Read_Master_Log_Pos:403Relay_Log_File: mariadb-relay-bin.000002Relay_Log_Pos:688Relay_Master_Log_File: master-102.000003Slave_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:0Last_Error:

Skip_Counter:0Exec_Master_Log_Pos:403Relay_Log_Space:984Until_Condition: None

Until_Log_File:

Until_Log_Pos:0Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert: No

Last_IO_Errno:0Last_IO_Error:

Last_SQL_Errno:0Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id:102

1 row in set (0.00sec)

MariaDB [(none)]>

在node104.yinzhengjie.org.cn中配置与node102.yinzhengjie.org.cn同步

3>.主服务器配置半同步插件

[root@node102.yinzhengjie.org.cn ~]# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connectionid is 21Server version:5.5.64-MariaDB MariaDB Server

Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.

MariaDB [(none)]>MariaDB [(none)]>SHOW PLUGINS;　　　　　　#查看现有插件+--------------------------------+----------+--------------------+---------+---------+

+--------------------------------+----------+--------------------+---------+---------+

+--------------------------------+----------+--------------------+---------+---------+

42 rows in set (0.00sec)

MariaDB [(none)]>MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';　　　　　　#安装插件

Query OK,0 rows affected (0.00sec)

MariaDB [(none)]>MariaDB [(none)]>SHOW PLUGINS;　　　　　　#查看现有插件+--------------------------------+----------+--------------------+--------------------+---------+

+--------------------------------+----------+--------------------+--------------------+---------+

+--------------------------------+----------+--------------------+--------------------+---------+

43 rows in set (0.00sec)

MariaDB [(none)]>

MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; #安装插件

[root@node102.yinzhengjie.org.cn ~]# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connectionid is 24Server version:5.5.64-MariaDB MariaDB Server

Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.

MariaDB [(none)]> SHOW GLOBAL SVARIABLES 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.00sec)

MariaDB [(none)]>MariaDB [(none)]>SELECT @@rpl_semi_sync_master_enabled;+--------------------------------+

| @@rpl_semi_sync_master_enabled |

+--------------------------------+

| 0 |

+--------------------------------+

1 row in set (0.00sec)

MariaDB [(none)]>MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=1;　　　　#临时开启master同步插件

Query OK,0 rows affected (0.00sec)

MariaDB [(none)]>MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';+------------------------------------+-------+

| Variable_name | Value |

+------------------------------------+-------+

| rpl_semi_sync_master_wait_no_slave | ON |

+------------------------------------+-------+

4 rows in set (0.00sec)

MariaDB [(none)]>MariaDB [(none)]>SELECT @@rpl_semi_sync_master_enabled;+--------------------------------+

| @@rpl_semi_sync_master_enabled |

+--------------------------------+

| 1 |

+--------------------------------+

1 row in set (0.00sec)

MariaDB [(none)]>

MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=1;　　　　#临时开启master同步插件

[root@node102.yinzhengjie.org.cn ~]# cat /etc/my.cnf #编辑配置文件开启master节点的同步功能

[mysqld]

server-id = 102binlog_format=row

log_bin= /data/mysql/logbin/master-102rpl_semi_sync_master_enabled= 1#开启master节点的同步功能

character-set-server =utf8mb4

default_storage_engine=InnoDB

datadir= /var/lib/mysql

socket= /var/lib/mysql/mysql.sock

[mysqld_safe]

log-error = /var/log/mariadb/mariadb.log

pid-file = /var/run/mariadb/mariadb.pid!includedir /etc/my.cnf.d

[root@node102.yinzhengjie.org.cn~]#

[root@node102.yinzhengjie.org.cn~]# systemctl restart mariadb

[root@node102.yinzhengjie.org.cn~]#

[root@node102.yinzhengjie.org.cn~]# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connectionid is 4Server version:5.5.64-MariaDB MariaDB Server

Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.

MariaDB [(none)]>MariaDB [(none)]>SELECT @@rpl_semi_sync_master_enabled;+--------------------------------+

| @@rpl_semi_sync_master_enabled |

+--------------------------------+

| 1 |

+--------------------------------+

1 row in set (0.00sec)

MariaDB [(none)]>MariaDB [(none)]>

[root@node102.yinzhengjie.org.cn ~]# cat /etc/my.cnf 　　 #修改配置文件开启master节点的同步功能

4>.从服务器配置半同步插件

[root@node103.yinzhengjie.org.cn ~]# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connectionid is 6Server version:5.5.64-MariaDB MariaDB Server

Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.

MariaDB [(none)]>SHOW PLUGINS;+--------------------------------+----------+--------------------+---------+---------+

+--------------------------------+----------+--------------------+---------+---------+

+--------------------------------+----------+--------------------+---------+---------+

42 rows in set (0.00sec)

MariaDB [(none)]>MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';　　　　#安装slave节点同步插件

Query OK,0 rows affected (0.00sec)

MariaDB [(none)]>MariaDB [(none)]>SHOW PLUGINS;+--------------------------------+----------+--------------------+-------------------+---------+

+--------------------------------+----------+--------------------+-------------------+---------+

+--------------------------------+----------+--------------------+-------------------+---------+

43 rows in set (0.00sec)

MariaDB [(none)]>

MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';　　#安装插件

MariaDB [(none)]>SELECT @@rpl_semi_sync_slave_enabled;+-------------------------------+

| @@rpl_semi_sync_slave_enabled |

+-------------------------------+

| 0 |

+-------------------------------+

1 row in set (0.00sec)

MariaDB [(none)]>MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=1;　　　　#临时开启slave同步插件

Query OK,0 rows affected (0.00sec)

MariaDB [(none)]>MariaDB [(none)]>SELECT @@rpl_semi_sync_slave_enabled;+-------------------------------+

| @@rpl_semi_sync_slave_enabled |

+-------------------------------+

| 1 |

+-------------------------------+

1 row in set (0.00sec)

MariaDB [(none)]>

MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=1;　　　　#临时开启slave同步插件

[root@node103.yinzhengjie.org.cn ~]# cat /etc/my.cnf

[mysqld]

server-id = 103binlog_format=row

read-only =on

rpl_semi_sync_slave_enabled= 1relay_log= relay-log-103relay_log_index= relay-log-103.index

character-set-server =utf8mb4

default_storage_engine=InnoDB

datadir= /var/lib/mysql

socket= /var/lib/mysql/mysql.sock

[mysqld_safe]

log-error = /var/log/mariadb/mariadb.log

pid-file = /var/run/mariadb/mariadb.pid!includedir /etc/my.cnf.d

[root@node103.yinzhengjie.org.cn~]#

[root@node103.yinzhengjie.org.cn~]# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connectionid is 8Server version:5.5.64-MariaDB MariaDB Server

Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.

MariaDB [(none)]>MariaDB [(none)]>SELECT @@rpl_semi_sync_slave_enabled;+-------------------------------+

| @@rpl_semi_sync_slave_enabled |

+-------------------------------+

| 1 |

+-------------------------------+

1 row in set (0.00sec)

MariaDB [(none)]>MariaDB [(none)]>

[root@node103.yinzhengjie.org.cn ~]# cat /etc/my.cnf 　　 #修改配置文件开启master节点的同步功能

[root@node104.yinzhengjie.org.cn ~]# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connectionid is 6Server version:5.5.64-MariaDB MariaDB Server

Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.

MariaDB [(none)]>MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

Query OK,0 rows affected (0.00sec)

MariaDB [(none)]>MariaDB [(none)]>SELECT @@rpl_semi_sync_slave_enabled;+-------------------------------+

| @@rpl_semi_sync_slave_enabled |

+-------------------------------+

| 0 |

+-------------------------------+

1 row in set (0.00sec)

MariaDB [(none)]>MariaDB [(none)]>QUIT

Bye

[root@node104.yinzhengjie.org.cn~]#

[root@node104.yinzhengjie.org.cn~]# vim /etc/my.cnf

[root@node104.yinzhengjie.org.cn~]#

[root@node104.yinzhengjie.org.cn~]# cat /etc/my.cnf

[mysqld]

server-id = 104binlog_format=row

read-only =on

rpl_semi_sync_slave_enabled= 1character-set-server =utf8mb4

default_storage_engine=InnoDB

datadir= /var/lib/mysql

socket= /var/lib/mysql/mysql.sock

[mysqld_safe]

log-error = /var/log/mariadb/mariadb.log

pid-file = /var/run/mariadb/mariadb.pid!includedir /etc/my.cnf.d

[root@node104.yinzhengjie.org.cn~]#

[root@node104.yinzhengjie.org.cn~]# systemctl restart mariadb

[root@node104.yinzhengjie.org.cn~]#

[root@node104.yinzhengjie.org.cn~]# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connectionid is 4Server version:5.5.64-MariaDB MariaDB Server

Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.

MariaDB [(none)]>SELECT @@rpl_semi_sync_slave_enabled;+-------------------------------+

| @@rpl_semi_sync_slave_enabled |

+-------------------------------+

| 1 |

+-------------------------------+

1 row in set (0.00sec)

MariaDB [(none)]>

node104.yinzhengjie.org.cn节点重复上面的操作即可

5>.验证半同步复制是否成功

[root@node102.yinzhengjie.org.cn ~]# mysql　　　　　　#在master节点创建修改相应数据

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connectionid is 7Server version:5.5.64-MariaDB MariaDB Server

Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.

MariaDB [(none)]>SHOW DATABASES;+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

+--------------------+

4 rows in set (0.00sec)

MariaDB [(none)]>MariaDB [(none)]>CREATE DATABASE yinzhengjie2019;

Query OK,1 row affected (0.01sec)

MariaDB [(none)]>MariaDB [(none)]>USE yinzhengjie2019

Database changed

MariaDB [yinzhengjie2019]>MariaDB [yinzhengjie2019]> CREATE TABLE students(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(30) NOT NULL,sex ENUM('boy','girl') DEFAULT 'boy',age TINYINT UNSIGNED,mobile CHAR(11),address VARCHAR(50));

Query OK,0 rows affected (0.00sec)

MariaDB [yinzhengjie2019]>MariaDB [yinzhengjie2019]>DESC students;+---------+---------------------+------+-----+---------+----------------+

+---------+---------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| sex | enum('boy','girl') | YES | | boy | |

| age | tinyint(3) unsigned | YES | | NULL | |

+---------+---------------------+------+-----+---------+----------------+

6 rows in set (0.00sec)

MariaDB [yinzhengjie2019]>MariaDB [yinzhengjie2019]> INSERT INTO students (name,age,mobile,address) VALUES ('Jason Yin',18,10000,'beijing'),('Jay','40',10086,'Taiwan');

Query OK,2 rows affected (0.01sec)

Records:2 Duplicates: 0 Warnings: 0MariaDB [yinzhengjie2019]>MariaDB [yinzhengjie2019]> SELECT *FROM students;+----+-----------+------+------+--------+---------+

| id | name | sex | age | mobile | address |

+----+-----------+------+------+--------+---------+

| 1 | Jason Yin | boy | 18 | 10000 | beijing |

| 2 | Jay | boy | 40 | 10086 | Taiwan |

+----+-----------+------+------+--------+---------+

2 rows in set (0.00sec)

MariaDB [yinzhengjie2019]>MariaDB [yinzhengjie2019]>QUIT

Bye

[root@node102.yinzhengjie.org.cn~]#

[root@node102.yinzhengjie.org.cn ~]# mysql　　　　　　#在master节点创建修改相应数据

[root@node103.yinzhengjie.org.cn ~]# mysql　　　　　　　#验证该节点数据是否与master节点数据同步

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connectionid is 9Server version:5.5.64-MariaDB MariaDB Server

Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.

MariaDB [(none)]>MariaDB [(none)]>SHOW DATABASES;+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

| yinzhengjie2019 |

+--------------------+

5 rows in set (0.00sec)

MariaDB [(none)]>MariaDB [(none)]>USE yinzhengjie2019;

Reading table informationforcompletion of table and column names

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

Database changed

MariaDB [yinzhengjie2019]>MariaDB [yinzhengjie2019]>SHOW TABLES;+---------------------------+

| Tables_in_yinzhengjie2019 |

+---------------------------+

| students |

+---------------------------+

1 row in set (0.00sec)

MariaDB [yinzhengjie2019]> SELECT *FROM students;+----+-----------+------+------+--------+---------+

| id | name | sex | age | mobile | address |

+----+-----------+------+------+--------+---------+

| 1 | Jason Yin | boy | 18 | 10000 | beijing |

| 2 | Jay | boy | 40 | 10086 | Taiwan |

+----+-----------+------+------+--------+---------+

2 rows in set (0.00sec)

MariaDB [yinzhengjie2019]>MariaDB [yinzhengjie2019]>QUIT

Bye

[root@node103.yinzhengjie.org.cn~]#

[root@node103.yinzhengjie.org.cn ~]# mysql　　　　　　　#验证该节点数据是否与master节点数据同步

[root@node104.yinzhengjie.org.cn ~]# mysql　　　　　　　　#操作同上

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connectionid is 5Server version:5.5.64-MariaDB MariaDB Server

Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.

MariaDB [(none)]>USE yinzhengjie2019

Reading table informationforcompletion of table and column names

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

Database changed

MariaDB [yinzhengjie2019]>MariaDB [yinzhengjie2019]> SELECT *FROM students;+----+-----------+------+------+--------+---------+

| id | name | sex | age | mobile | address |

+----+-----------+------+------+--------+---------+

| 1 | Jason Yin | boy | 18 | 10000 | beijing |

| 2 | Jay | boy | 40 | 10086 | Taiwan |

+----+-----------+------+------+--------+---------+

2 rows in set (0.00sec)

MariaDB [yinzhengjie2019]>QUIT

Bye

[root@node104.yinzhengjie.org.cn~]#

[root@node104.yinzhengjie.org.cn ~]# mysql　　　　　　　　#操作同上

6>.让node104.yinzhengjie.org.cn节点下线观察master节点是否正常运行

[root@node104.yinzhengjie.org.cn ~]# ss -ntl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN0 50 *:3306 *:*LISTEN0 128 *:22 *:*LISTEN0 128 :::22 :::*[root@node104.yinzhengjie.org.cn~]#

[root@node104.yinzhengjie.org.cn~]# systemctl stop mariadb

[root@node104.yinzhengjie.org.cn~]#

[root@node104.yinzhengjie.org.cn~]# ss -ntl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN0 128 *:22 *:*LISTEN0 128 :::22 :::*[root@node104.yinzhengjie.org.cn~]#

[root@node104.yinzhengjie.org.cn ~]# systemctl stop mariadb

[root@node102.yinzhengjie.org.cn ~]# mysql　　　　　　#在master节点修改数据

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connectionid is 8Server version:5.5.64-MariaDB MariaDB Server

Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.

MariaDB [(none)]>MariaDB [(none)]>USE yinzhengjie2019

Reading table informationforcompletion of table and column names

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

Database changed

MariaDB [yinzhengjie2019]>MariaDB [yinzhengjie2019]> INSERT INTO students SET name='尹正杰',age=27,address='北京';　　　 #发现可以正常使用!MariaDB [yinzhengjie2019]>MariaDB [yinzhengjie2019]> SELECT *FROM students;+----+-----------+------+------+--------+---------+

| id | name | sex | age | mobile | address |

+----+-----------+------+------+--------+---------+

| 1 | Jason Yin | boy | 18 | 10000 | beijing |

| 2 | Jay | boy | 40 | 10086 | Taiwan |

| 3 | 尹正杰 | boy | 27 | NULL | 北京 |

+----+-----------+------+------+--------+---------+

3 rows in set (0.00sec)

MariaDB [yinzhengjie2019]>MariaDB [yinzhengjie2019]> INSERT INTO students SET name='yinzhengjie',age=27,address='shanxi';

Query OK,1 row affected (0.01sec)

MariaDB [yinzhengjie2019]>MariaDB [yinzhengjie2019]> SELECT *FROM students;+----+-------------+------+------+--------+---------+

| id | name | sex | age | mobile | address |

+----+-------------+------+------+--------+---------+

| 1 | Jason Yin | boy | 18 | 10000 | beijing |

| 2 | Jay | boy | 40 | 10086 | Taiwan |

| 3 | 尹正杰 | boy | 27 | NULL | 北京 |

| 4 | yinzhengjie | boy | 27 | NULL | shanxi |

+----+-------------+------+------+--------+---------+

4 rows in set (0.00sec)

MariaDB [yinzhengjie2019]>MariaDB [yinzhengjie2019]>QUIT

Bye

[root@node102.yinzhengjie.org.cn~]#

[root@node102.yinzhengjie.org.cn ~]# mysql　　　　　　#在master节点修改数据

[root@node103.yinzhengjie.org.cn ~]# mysql　　　　　　#该节点数据和master节点数据是同步的

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connectionid is 10Server version:5.5.64-MariaDB MariaDB Server

Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.

MariaDB [(none)]>MariaDB [(none)]> SELECT *FROM yinzhengjie2019.students;+----+-------------+------+------+--------+---------+

| id | name | sex | age | mobile | address |

+----+-------------+------+------+--------+---------+

| 1 | Jason Yin | boy | 18 | 10000 | beijing |

| 2 | Jay | boy | 40 | 10086 | Taiwan |

| 3 | 尹正杰 | boy | 27 | NULL | 北京 |

| 4 | yinzhengjie | boy | 27 | NULL | shanxi |

+----+-------------+------+------+--------+---------+

4 rows in set (0.00sec)

MariaDB [(none)]>MariaDB [(none)]>QUIT

Bye

[root@node103.yinzhengjie.org.cn~]#

[root@node103.yinzhengjie.org.cn ~]# mysql　　　　　　#该节点数据和master节点数据是同步的

7>.让所有slave下线观察master节点是否正常运行

[root@node103.yinzhengjie.org.cn ~]# ss -ntl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN0 50 *:3306 *:*LISTEN0 128 *:22 *:*LISTEN0 128 :::22 :::*[root@node103.yinzhengjie.org.cn~]#

[root@node103.yinzhengjie.org.cn~]# systemctl stop mariadb

[root@node103.yinzhengjie.org.cn~]#

[root@node103.yinzhengjie.org.cn~]# ss -ntl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN0 128 *:22 *:*LISTEN0 128 :::22 :::*[root@node103.yinzhengjie.org.cn~]#

[root@node103.yinzhengjie.org.cn ~]# systemctl stop mariadb

[root@node104.yinzhengjie.org.cn ~]# ss -ntl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN0 50 *:3306 *:*LISTEN0 128 *:22 *:*LISTEN0 128 :::22 :::*[root@node104.yinzhengjie.org.cn~]#

[root@node104.yinzhengjie.org.cn~]# systemctl stop mariadb

[root@node104.yinzhengjie.org.cn~]#

[root@node104.yinzhengjie.org.cn~]# ss -ntl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN0 128 *:22 *:*LISTEN0 128 :::22 :::*[root@node104.yinzhengjie.org.cn~]#

[root@node104.yinzhengjie.org.cn ~]# systemctl stop mariadb

[root@node102.yinzhengjie.org.cn ~]# mysql　　　　　　#修改master数据

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connectionid is 9Server version:5.5.64-MariaDB MariaDB Server

Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.

MariaDB [(none)]>USE yinzhengjie2019

Reading table informationforcompletion of table and column names

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

Database changed

MariaDB [yinzhengjie2019]>MariaDB [yinzhengjie2019]> INSERT students (age,sex,name,mobile,address) VALUES (28,'girl','Gloria Tang Tsz-Kei',null,'Hong Kong'); #由于2个slave节点都挂掉啦!因此master无法和任意一个slave通信，等到了默认超时时间就会自动提交该事务。

Query OK, 1 row affected (10.01 sec)

MariaDB [yinzhengjie2019]>MariaDB [yinzhengjie2019]> SELECT *FROM students;+----+---------------------+------+------+--------+-----------+

| id | name | sex | age | mobile | address |

+----+---------------------+------+------+--------+-----------+

| 1 | Jason Yin | boy | 18 | 10000 | beijing |

| 2 | Jay | boy | 40 | 10086 | Taiwan |

| 3 | 尹正杰 | boy | 27 | NULL | 北京 |

| 4 | yinzhengjie | boy | 27 | NULL | shanxi |

+----+---------------------+------+------+--------+-----------+

5 rows in set (0.00sec)

MariaDB [yinzhengjie2019]> UPDATE students SET address='陕西' WHERE id = 4;　　　　　　#当第一次阻塞后，就从半同步复制转换成异步复制啦！若再想启用半同步复制的话需要重启复制线程。

Query OK,1 row affected (0.00sec)

Rows matched:1 Changed: 1 Warnings: 0MariaDB [yinzhengjie2019]>MariaDB [yinzhengjie2019]> SELECT *FROM students;+----+---------------------+------+------+--------+-----------+

| id | name | sex | age | mobile | address |

+----+---------------------+------+------+--------+-----------+

| 1 | Jason Yin | boy | 18 | 10000 | beijing |

| 2 | Jay | boy | 40 | 10086 | Taiwan |

| 3 | 尹正杰 | boy | 27 | NULL | 北京 |

| 4 | yinzhengjie | boy | 27 | NULL | 陕西 |

+----+---------------------+------+------+--------+-----------+

5 rows in set (0.00sec)

MariaDB [yinzhengjie2019]>MariaDB [yinzhengjie2019]>QUIT

Bye

[root@node102.yinzhengjie.org.cn~]#

[root@node103.yinzhengjie.org.cn ~]# systemctl start mariadb 　　　　　　#启动数据库并观察数据是否同步

[root@node103.yinzhengjie.org.cn~]#

[root@node103.yinzhengjie.org.cn~]# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connectionid is 4Server version:5.5.64-MariaDB MariaDB Server

Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.

MariaDB [(none)]>MariaDB [(none)]> SELECT *FROM yinzhengjie2019.students;+----+---------------------+------+------+--------+-----------+

| id | name | sex | age | mobile | address |

+----+---------------------+------+------+--------+-----------+

| 1 | Jason Yin | boy | 18 | 10000 | beijing |

| 2 | Jay | boy | 40 | 10086 | Taiwan |

| 3 | 尹正杰 | boy | 27 | NULL | 北京 |

| 4 | yinzhengjie | boy | 27 | NULL | 陕西 |

+----+---------------------+------+------+--------+-----------+

5 rows in set (0.00sec)

MariaDB [(none)]>QUIT

Bye

[root@node103.yinzhengjie.org.cn~]#

[root@node103.yinzhengjie.org.cn ~]# systemctl start mariadb　　　　#启动数据库并观察数据是否同步

[root@node104.yinzhengjie.org.cn ~]# systemctl start mariadb

[root@node104.yinzhengjie.org.cn~]#

[root@node104.yinzhengjie.org.cn~]# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connectionid is 4Server version:5.5.64-MariaDB MariaDB Server

Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.

MariaDB [(none)]> SELECT *FROM yinzhengjie2019.students;+----+---------------------+------+------+--------+-----------+

| id | name | sex | age | mobile | address |

+----+---------------------+------+------+--------+-----------+

| 1 | Jason Yin | boy | 18 | 10000 | beijing |

| 2 | Jay | boy | 40 | 10086 | Taiwan |

| 3 | 尹正杰 | boy | 27 | NULL | 北京 |

| 4 | yinzhengjie | boy | 27 | NULL | 陕西 |

+----+---------------------+------+------+--------+-----------+

5 rows in set (0.00sec)

MariaDB [(none)]>QUIT

Bye

[root@node104.yinzhengjie.org.cn~]#

[root@node104.yinzhengjie.org.cn ~]# systemctl start mariadb　　　　#同上操作

8>.修改超时时间

[root@node102.yinzhengjie.org.cn ~]# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connectionid is 11Server version:5.5.64-MariaDB MariaDB Server

Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.

MariaDB [(none)]>MariaDB [(none)]>SELECT @@rpl_semi_sync_master_timeout;+--------------------------------+

| @@rpl_semi_sync_master_timeout |

+--------------------------------+

| 10000 |

+--------------------------------+

1 row in set (0.00sec)

MariaDB [(none)]>QUIT

Bye

[root@node102.yinzhengjie.org.cn~]# vim /etc/my.cnf

[root@node102.yinzhengjie.org.cn~]#

[root@node102.yinzhengjie.org.cn~]# cat /etc/my.cnf #修改配置文件，将默认的master同步slave超时秒数改为2秒

[mysqld]

server-id = 102binlog_format=row

log_bin= /data/mysql/logbin/master-102rpl_semi_sync_master_enabled= 1rpl_semi_sync_master_timeout= 2000　　　　#我们将默认10秒改为2秒，这里的单位是毫秒哟，2s=2000ms

character-set-server =utf8mb4

default_storage_engine=InnoDB

datadir= /var/lib/mysql

socket= /var/lib/mysql/mysql.sock

[mysqld_safe]

log-error = /var/log/mariadb/mariadb.log

pid-file = /var/run/mariadb/mariadb.pid!includedir /etc/my.cnf.d

[root@node102.yinzhengjie.org.cn~]#

[root@node102.yinzhengjie.org.cn~]# systemctl restart mariadb

[root@node102.yinzhengjie.org.cn~]#

[root@node102.yinzhengjie.org.cn~]# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connectionid is 2Server version:5.5.64-MariaDB MariaDB Server

Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.

MariaDB [(none)]>MariaDB [(none)]>SELECT @@rpl_semi_sync_master_timeout;+--------------------------------+

| @@rpl_semi_sync_master_timeout |

+--------------------------------+

| 2000 |

+--------------------------------+

1 row in set (0.00sec)

MariaDB [(none)]>

[root@node102.yinzhengjie.org.cn ~]# cat /etc/my.cnf 　　#将默认的master同步slave超时秒数改为2秒

[root@node104.yinzhengjie.org.cn ~]# ss -ntl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN0 50 *:3306 *:*LISTEN0 128 *:22 *:*LISTEN0 128 :::22 :::*[root@node104.yinzhengjie.org.cn~]#

[root@node104.yinzhengjie.org.cn~]# systemctl stop mariadb

[root@node104.yinzhengjie.org.cn~]#

[root@node104.yinzhengjie.org.cn~]# ss -ntl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN0 128 *:22 *:*LISTEN0 128 :::22 :::*[root@node104.yinzhengjie.org.cn~]#

[root@node104.yinzhengjie.org.cn ~]# systemctl stop mariadb

[root@node102.yinzhengjie.org.cn ~]# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connectionid is 6Server version:5.5.64-MariaDB MariaDB Server

Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.

MariaDB [(none)]>MariaDB [(none)]>USE yinzhengjie2019

Reading table informationforcompletion of table and column names

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

Database changed

MariaDB [yinzhengjie2019]>MariaDB [yinzhengjie2019]> SELECT *FROM students;+----+---------------------+------+------+--------+-----------+

| id | name | sex | age | mobile | address |

+----+---------------------+------+------+--------+-----------+

| 1 | Jason Yin | boy | 18 | 10000 | beijing |

| 2 | Jay | boy | 40 | 10086 | Taiwan |

| 3 | 尹正杰 | boy | 27 | NULL | 北京 |

| 4 | yinzhengjie | boy | 27 | NULL | 陕西 |

+----+---------------------+------+------+--------+-----------+

5 rows in set (0.00sec)

MariaDB [yinzhengjie2019]>MariaDB [yinzhengjie2019]> DELETE FROM students WHERE id = 4;

Query OK,1 row affected (0.01sec)

MariaDB [yinzhengjie2019]>MariaDB [yinzhengjie2019]>MariaDB [yinzhengjie2019]> SELECT *FROM students;+----+---------------------+------+------+--------+-----------+

| id | name | sex | age | mobile | address |

+----+---------------------+------+------+--------+-----------+

| 1 | Jason Yin | boy | 18 | 10000 | beijing |

| 2 | Jay | boy | 40 | 10086 | Taiwan |

| 3 | 尹正杰 | boy | 27 | NULL | 北京 |

+----+---------------------+------+------+--------+-----------+

4 rows in set (0.00sec)

MariaDB [yinzhengjie2019]>

当关闭一个节点时观察master执行语句的操作

[root@node103.yinzhengjie.org.cn ~]# ss -ntl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN0 50 *:3306 *:*LISTEN0 128 *:22 *:*LISTEN0 128 :::22 :::*[root@node103.yinzhengjie.org.cn~]#

[root@node103.yinzhengjie.org.cn~]# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connectionid is 6Server version:5.5.64-MariaDB MariaDB Server

Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.

MariaDB [(none)]> SELECT *FROM yinzhengjie2019.students;+----+---------------------+------+------+--------+-----------+

| id | name | sex | age | mobile | address |

+----+---------------------+------+------+--------+-----------+

| 1 | Jason Yin | boy | 18 | 10000 | beijing |

| 2 | Jay | boy | 40 | 10086 | Taiwan |

| 3 | 尹正杰 | boy | 27 | NULL | 北京 |

+----+---------------------+------+------+--------+-----------+

4 rows in set (0.01sec)

MariaDB [(none)]>quit

Bye

[root@node103.yinzhengjie.org.cn~]#

[root@node103.yinzhengjie.org.cn~]# systemctl stop mariadb

[root@node103.yinzhengjie.org.cn~]#

[root@node103.yinzhengjie.org.cn~]# ss -ntl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN0 128 *:22 *:*LISTEN0 128 :::22 :::*[root@node103.yinzhengjie.org.cn~]#

数据同步到另外一台未关闭的节点啦,验证完毕后将该节点也关闭掉!

[root@node102.yinzhengjie.org.cn ~]# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connectionid is 6Server version:5.5.64-MariaDB MariaDB Server

Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.

MariaDB [(none)]>MariaDB [(none)]>USE yinzhengjie2019

Reading table informationforcompletion of table and column names

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

Database changed

MariaDB [yinzhengjie2019]>MariaDB [yinzhengjie2019]> SELECT *FROM students;+----+---------------------+------+------+--------+-----------+

| id | name | sex | age | mobile | address |

+----+---------------------+------+------+--------+-----------+

| 1 | Jason Yin | boy | 18 | 10000 | beijing |

| 2 | Jay | boy | 40 | 10086 | Taiwan |

| 3 | 尹正杰 | boy | 27 | NULL | 北京 |

| 4 | yinzhengjie | boy | 27 | NULL | 陕西 |

+----+---------------------+------+------+--------+-----------+

5 rows in set (0.00sec)

MariaDB [yinzhengjie2019]>MariaDB [yinzhengjie2019]> DELETE FROM students WHERE id = 4;　　#关闭一个slaves节点貌似对master没有啥影响

Query OK,1 row affected (0.01sec)

MariaDB [yinzhengjie2019]>MariaDB [yinzhengjie2019]>MariaDB [yinzhengjie2019]> SELECT *FROM students;+----+---------------------+------+------+--------+-----------+

| id | name | sex | age | mobile | address |

+----+---------------------+------+------+--------+-----------+

| 1 | Jason Yin | boy | 18 | 10000 | beijing |

| 2 | Jay | boy | 40 | 10086 | Taiwan |

| 3 | 尹正杰 | boy | 27 | NULL | 北京 |

+----+---------------------+------+------+--------+-----------+

4 rows in set (0.00sec)

MariaDB [yinzhengjie2019]>MariaDB [yinzhengjie2019]> DELETE FROM students WHERE id < 3;　　　　#如果将2个节点都关闭，我们发现默认的超时时间的确和我们修改的一致啦~

Query OK,2 rows affected (2.01sec)

MariaDB [yinzhengjie2019]>MariaDB [yinzhengjie2019]> SELECT *FROM students;+----+---------------------+------+------+--------+-----------+

| id | name | sex | age | mobile | address |

+----+---------------------+------+------+--------+-----------+

| 3 | 尹正杰 | boy | 27 | NULL | 北京 |

+----+---------------------+------+------+--------+-----------+

2 rows in set (0.00sec)

MariaDB [yinzhengjie2019]>MariaDB [yinzhengjie2019]>MariaDB [yinzhengjie2019]>QUIT

Bye

[root@node102.yinzhengjie.org.cn~]#

三.半同步复制监控参数

1>.rpl_semi_sync_master_clients

检查半同步的slave个数。

2>.rpl_semi_sync_master_status

1表示主库的半同步功能开启并且运行正常，

0表示主库的半同步功能关闭或者半同步复制已经变成了异步复制。

3>.rpl_semi_sync_master_no_tx

表示有多少提交没有收到slave的确认消息。

4>.rpl_semi_sync_master_yes_tx

表示有多个提交收到了slave的确认消息。

5>.rpl_semi_sync_slave_status

1表示备库上slave功能开启并且运行正常

0表示功能为开启或者运行异常。

6>.主库通过MySQL命令查看各个参数的状态

[root@node102.yinzhengjie.org.cn ~]# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connectionid is 10Server version:5.5.64-MariaDB MariaDB Server

Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.

MariaDB [(none)]>MariaDB [(none)]> SHOW STATUS LIKE 'rpl_semi_sync%';+--------------------------------------------+-------+

| Variable_name | Value |

+--------------------------------------------+-------+

| Rpl_semi_sync_master_clients | 2 |

| Rpl_semi_sync_master_net_avg_wait_time | 995 |

| Rpl_semi_sync_master_net_wait_time | 3981 |

| Rpl_semi_sync_master_net_waits | 4 |

| Rpl_semi_sync_master_no_times | 1 |

| Rpl_semi_sync_master_no_tx | 1 |

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

+--------------------------------------------+-------+

14 rows in set (0.00sec)

MariaDB [(none)]>

MariaDB [(none)]> SHOW STATUS LIKE 'rpl_semi_sync%';

7>.从库通过MySQL命令查看各个参数的状态

[root@node103.yinzhengjie.org.cn ~]# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connectionid is 4Server version:5.5.64-MariaDB MariaDB Server

Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.

MariaDB [(none)]> SHOW STATUS LIKE 'rpl_semi_sync%';+----------------------------+-------+

| Variable_name | Value |

+----------------------------+-------+

| Rpl_semi_sync_slave_status | ON |

+----------------------------+-------+

1 row in set (0.00sec)

MariaDB [(none)]>

MariaDB [(none)]> SHOW STATUS LIKE 'rpl_semi_sync%';

weixin_39829501

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mariadb mysql同步_MySQL/MariaDB数据库的半同步复制

MySQL/MariaDB数据库的半同步复制作者：尹正杰版权声明：原创作品，谢绝转载！否则将追究法律责任。一.MySQL半同步复制概述1>.MySQL默认的异步复制默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库即结束，并不验证从库是否接收完毕。这意味着当主服务器或从服务器端发生故障时，有可能从服务器没有接收到主服务器发送过来的binlo...
复制链接

扫一扫