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：在从库配置，确保从库的半同步复制功能开启。 半同步复制是通过插件的方式建立，要分别在主库和从库安装一个插件半同步复制的前提条件：
MySQL .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 =
binlog_format = row
log_bin = /data/mysql/logbin/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 ~]# cat /etc/my.cnf
[root@node102.yinzhengjie.org.cn ~]# ll /data/mysql/logbin/
total
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# ll /var/lib/mysql/
total
[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/
total
-rw-rw---- mysql mysql Nov : aria_log.
-rw-rw---- mysql mysql Nov : aria_log_control
-rw-rw---- mysql mysql Nov : ibdata1
-rw-rw---- mysql mysql Nov : ib_logfile0
-rw-rw---- mysql mysql Nov : ib_logfile1
drwx------ mysql mysql Nov : mysql
srwxrwxrwx mysql mysql Nov : mysql.sock
drwx------ mysql mysql Nov : performance_schema
drwx------ mysql mysql Nov : test
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# ll /data/mysql/logbin/
total
-rw-rw---- mysql mysql Nov : master-102.000001
-rw-rw---- mysql mysql Nov : master-102.000002
-rw-rw---- mysql mysql Nov : master-102.000003
-rw-rw---- mysql mysql Nov : master-.index
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# systemctl start mariadb
[root@node102.yinzhengjie.org.cn ~]# ll /data/mysql/logbin/
total
-rw-rw---- mysql mysql Nov : master-102.000001
-rw-rw---- mysql mysql Nov : master-102.000002
-rw-rw---- mysql mysql Nov : master-102.000003
-rw-rw---- mysql mysql Nov : master-.index
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
MariaDB [(none)]> SHOW MASTER LOGS;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-102.000001 | |
| master-102.000002 | |
| master-102.000003 | |
+-------------------+-----------+
rows in set (0.00 sec) 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 | :: | |
| | localhost | |
| | node102.yinzhengjie.org.cn | |
+------+----------------------------+----------+
rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'copy'@'172.30.1.10%' IDENTIFIED BY 'yinzhengjie';
Query OK, rows affected (0.00 sec) 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 | :: | |
| | localhost | |
| | node102.yinzhengjie.org.cn | |
| copy | 172.30.1.10% | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
+------+----------------------------+-------------------------------------------+
rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> SHOW MASTER LOGS;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-102.000001 | |
| master-102.000002 | |
| master-102.000003 | |
+-------------------+-----------+
rows in set (0.00 sec) MariaDB [(none)]>
node102.yinzhengjie.org.cn上创建有复制权限的账户(在此之前建议执行"show master logs"便于复制时指定)
[root@node103.yinzhengjie.org.cn ~]# cat /etc/my.cnf
[mysqld]
server-id =
binlog_format = row
read-only
relay_log = relay-log-
relay_log_index = relay-log-.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 ~]#
[root@node103.yinzhengjie.org.cn ~]# cat /etc/my.cnf
[root@node103.yinzhengjie.org.cn ~]# cat /etc/my.cnf
[mysqld]
server-id =
binlog_format = row
read-only = on
relay_log = relay-log-
relay_log_index = relay-log-.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
LISTEN *: *:*
LISTEN ::: :::*
[root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]# ll /var/lib/mysql/
total
[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/
total
-rw-rw---- mysql mysql Nov : aria_log.
-rw-rw---- mysql mysql Nov : aria_log_control
-rw-rw---- mysql mysql Nov : ibdata1
-rw-rw---- mysql mysql Nov : ib_logfile0
-rw-rw---- mysql mysql Nov : ib_logfile1
drwx------ mysql mysql Nov : mysql
srwxrwxrwx mysql mysql Nov : mysql.sock
drwx------ mysql mysql Nov : performance_schema
drwx------ mysql mysql Nov : test
[root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN *: *:*
LISTEN *: *:*
LISTEN ::: :::*
[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 connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the 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 | :: | |
| | localhost | |
| | node103.yinzhengjie.org.cn | |
+------+----------------------------+----------+
rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='172.30.1.102',
-> MASTER_USER='copy',
-> MASTER_PASSWORD='yinzhengjie',
-> MASTER_PORT=,
-> MASTER_LOG_FILE='master-102.000003',
-> MASTER_LOG_POS=,
-> MASTER_CONNECT_RETRY=;
Query OK, rows affected (0.01 sec) MariaDB [(none)]>
MariaDB [(none)]> SLAVE START;
Query OK, rows affected (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** . row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.30.1.102
Master_User: copy
Master_Port:
Connect_Retry:
Master_Log_File: master-102.000003
Read_Master_Log_Pos:
Relay_Log_File: relay-log-103.000002
Relay_Log_Pos:
Relay_Master_Log_File: master-102.000003
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
row in set (0.00 sec) 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 | :: | |
| | localhost | |
| | node103.yinzhengjie.org.cn | |
| copy | 172.30.1.10% | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
+------+----------------------------+-------------------------------------------+
rows in set (0.00 sec) MariaDB [(none)]>
在node103.yinzhengjie.org.cn中配置与node102.yinzhengjie.org.cn同步
[root@node104.yinzhengjie.org.cn ~]# cat /etc/my.cnf
[mysqld]
server-id =
binlog_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 ~]#
[root@node104.yinzhengjie.org.cn ~]# cat /etc/my.cnf
[root@node104.yinzhengjie.org.cn ~]# cat /etc/my.cnf
[mysqld]
server-id =
binlog_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/
total
[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/
total
-rw-rw---- mysql mysql Nov : aria_log.
-rw-rw---- mysql mysql Nov : aria_log_control
-rw-rw---- mysql mysql Nov : ibdata1
-rw-rw---- mysql mysql Nov : ib_logfile0
-rw-rw---- mysql mysql Nov : ib_logfile1
drwx------ mysql mysql Nov : mysql
srwxrwxrwx mysql mysql Nov : mysql.sock
drwx------ mysql mysql Nov : performance_schema
drwx------ mysql mysql Nov : test
[root@node104.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN *: *:*
LISTEN *: *:*
LISTEN ::: :::*
[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 connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the 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 | :: | |
| | localhost | |
| | node104.yinzhengjie.org.cn | |
+------+----------------------------+----------+
rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='172.30.1.102',
-> MASTER_USER='copy',
-> MASTER_PASSWORD='yinzhengjie',
-> MASTER_PORT=,
-> MASTER_LOG_FILE='master-102.000003',
-> MASTER_LOG_POS=,
-> MASTER_CONNECT_RETRY=;
Query OK, rows affected (0.01 sec) MariaDB [(none)]>
MariaDB [(none)]> START SLAVE;
Query OK, rows affected (0.00 sec) 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 | :: | |
| | localhost | |
| | node104.yinzhengjie.org.cn | |
| copy | 172.30.1.10% | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
+------+----------------------------+-------------------------------------------+
rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** . row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.30.1.102
Master_User: copy
Master_Port:
Connect_Retry:
Master_Log_File: master-102.000003
Read_Master_Log_Pos:
Relay_Log_File: mariadb-relay-bin.
Relay_Log_Pos:
Relay_Master_Log_File: master-102.000003
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
row in set (0.00 sec) 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 connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
MariaDB [(none)]> SHOW PLUGINS; #查看现有插件
+--------------------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+--------------------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_RSEG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_UNDO_LOGS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLE_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_INDEX_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_PAGES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_PAGES_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_PAGES_BLOB | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| XTRADB_ADMIN_COMMAND | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CHANGED_PAGES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| Aria | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEEDBACK | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+--------------------------------+----------+--------------------+---------+---------+
rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; #安装插件
Query OK, rows affected (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW PLUGINS; #查看现有插件
+--------------------------------+----------+--------------------+--------------------+---------+
| Name | Status | Type | Library | License |
+--------------------------------+----------+--------------------+--------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_RSEG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_UNDO_LOGS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLE_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_INDEX_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_PAGES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_PAGES_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_PAGES_BLOB | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| XTRADB_ADMIN_COMMAND | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CHANGED_PAGES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| Aria | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEEDBACK | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |
+--------------------------------+----------+--------------------+--------------------+---------+
rows in set (0.00 sec) 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 connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> SHOW GLOBAL SVARIABLES LIKE '%semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_master_timeout | |
| rpl_semi_sync_master_trace_level | |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SELECT @@rpl_semi_sync_master_enabled;
+--------------------------------+
| @@rpl_semi_sync_master_enabled |
+--------------------------------+
| |
+--------------------------------+
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=; #临时开启master同步插件
Query OK, rows affected (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON | #开启了该功能
| rpl_semi_sync_master_timeout | | #默认的超时时间是10秒，即10000毫秒
| rpl_semi_sync_master_trace_level | |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SELECT @@rpl_semi_sync_master_enabled;
+--------------------------------+
| @@rpl_semi_sync_master_enabled |
+--------------------------------+
| |
+--------------------------------+
row in set (0.00 sec) 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 =
binlog_format = row
log_bin = /data/mysql/logbin/master-
rpl_semi_sync_master_enabled = #开启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 ~]#
[root@node102.yinzhengjie.org.cn ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
MariaDB [(none)]> SELECT @@rpl_semi_sync_master_enabled;
+--------------------------------+
| @@rpl_semi_sync_master_enabled |
+--------------------------------+
| |
+--------------------------------+
row in set (0.00 sec) 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 connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> SHOW PLUGINS;
+--------------------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+--------------------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_RSEG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_UNDO_LOGS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLE_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_INDEX_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_PAGES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_PAGES_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_PAGES_BLOB | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| XTRADB_ADMIN_COMMAND | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CHANGED_PAGES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| Aria | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEEDBACK | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+--------------------------------+----------+--------------------+---------+---------+
rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; #安装slave节点同步插件
Query OK, rows affected (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW PLUGINS;
+--------------------------------+----------+--------------------+-------------------+---------+
| Name | Status | Type | Library | License |
+--------------------------------+----------+--------------------+-------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_RSEG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_UNDO_LOGS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLE_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_INDEX_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_PAGES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_PAGES_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_PAGES_BLOB | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| XTRADB_ADMIN_COMMAND | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CHANGED_PAGES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| Aria | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEEDBACK | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |
+--------------------------------+----------+--------------------+-------------------+---------+
rows in set (0.00 sec) 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 |
+-------------------------------+
| |
+-------------------------------+
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=; #临时开启slave同步插件
Query OK, rows affected (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SELECT @@rpl_semi_sync_slave_enabled;
+-------------------------------+
| @@rpl_semi_sync_slave_enabled |
+-------------------------------+
| |
+-------------------------------+
row in set (0.00 sec) 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 =
binlog_format = row
read-only = on
rpl_semi_sync_slave_enabled =
relay_log = relay-log-
relay_log_index = relay-log-.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 ~]#
[root@node103.yinzhengjie.org.cn ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
MariaDB [(none)]> SELECT @@rpl_semi_sync_slave_enabled;
+-------------------------------+
| @@rpl_semi_sync_slave_enabled |
+-------------------------------+
| |
+-------------------------------+
row in set (0.00 sec) 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 connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, rows affected (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SELECT @@rpl_semi_sync_slave_enabled;
+-------------------------------+
| @@rpl_semi_sync_slave_enabled |
+-------------------------------+
| |
+-------------------------------+
row in set (0.00 sec) 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 =
binlog_format = row
read-only = on
rpl_semi_sync_slave_enabled =
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 ~]# 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 connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> SELECT @@rpl_semi_sync_slave_enabled;
+-------------------------------+
| @@rpl_semi_sync_slave_enabled |
+-------------------------------+
| |
+-------------------------------+
row in set (0.00 sec) 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 connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> CREATE DATABASE yinzhengjie2019;
Query OK, row affected (0.01 sec) MariaDB [(none)]>
MariaDB [(none)]> USE yinzhengjie2019
Database changed
MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> CREATE TABLE students(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR() NOT NULL,sex ENUM('boy','girl') DEFAULT 'boy',age TINYINT UNSIGNED,mobile CHAR(),address VARCHAR());
Query OK, rows affected (0.00 sec) MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> DESC students;
+---------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+----------------+
| id | int() unsigned | NO | PRI | NULL | auto_increment |
| name | varchar() | NO | | NULL | |
| sex | enum('boy','girl') | YES | | boy | |
| age | tinyint() unsigned | YES | | NULL | |
| mobile | char() | YES | | NULL | |
| address | varchar() | YES | | NULL | |
+---------+---------------------+------+-----+---------+----------------+
rows in set (0.00 sec) MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> INSERT INTO students (name,age,mobile,address) VALUES ('Jason Yin',,,'beijing'),('Jay','',,'*');
Query OK, rows affected (0.01 sec)
Records: Duplicates: Warnings: MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> SELECT * FROM students;
+----+-----------+------+------+--------+---------+
| id | name | sex | age | mobile | address |
+----+-----------+------+------+--------+---------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
+----+-----------+------+------+--------+---------+
rows in set (0.00 sec) 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 connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| yinzhengjie2019 |
+--------------------+
rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> USE yinzhengjie2019;
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
MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> SHOW TABLES;
+---------------------------+
| Tables_in_yinzhengjie2019 |
+---------------------------+
| students |
+---------------------------+
row in set (0.00 sec) MariaDB [yinzhengjie2019]> SELECT * FROM students;
+----+-----------+------+------+--------+---------+
| id | name | sex | age | mobile | address |
+----+-----------+------+------+--------+---------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
+----+-----------+------+------+--------+---------+
rows in set (0.00 sec) 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 connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> USE yinzhengjie2019
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
MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> SELECT * FROM students;
+----+-----------+------+------+--------+---------+
| id | name | sex | age | mobile | address |
+----+-----------+------+------+--------+---------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
+----+-----------+------+------+--------+---------+
rows in set (0.00 sec) 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
LISTEN *: *:*
LISTEN *: *:*
LISTEN ::: :::*
[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
LISTEN *: *:*
LISTEN ::: :::*
[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 connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
MariaDB [(none)]> USE yinzhengjie2019
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
MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> INSERT INTO students SET name='尹正杰',age=,address='北京'; #发现可以正常使用! MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> SELECT * FROM students;
+----+-----------+------+------+--------+---------+
| id | name | sex | age | mobile | address |
+----+-----------+------+------+--------+---------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
| | 尹正杰 | boy | | NULL | 北京 |
+----+-----------+------+------+--------+---------+
rows in set (0.00 sec) MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> INSERT INTO students SET name='yinzhengjie',age=,address='shanxi';
Query OK, row affected (0.01 sec) MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> SELECT * FROM students;
+----+-------------+------+------+--------+---------+
| id | name | sex | age | mobile | address |
+----+-------------+------+------+--------+---------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
| | 尹正杰 | boy | | NULL | 北京 |
| | yinzhengjie | boy | | NULL | shanxi |
+----+-------------+------+------+--------+---------+
rows in set (0.00 sec) 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 connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
MariaDB [(none)]> SELECT * FROM yinzhengjie2019.students;
+----+-------------+------+------+--------+---------+
| id | name | sex | age | mobile | address |
+----+-------------+------+------+--------+---------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
| | 尹正杰 | boy | | NULL | 北京 |
| | yinzhengjie | boy | | NULL | shanxi |
+----+-------------+------+------+--------+---------+
rows in set (0.00 sec) 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
LISTEN *: *:*
LISTEN *: *:*
LISTEN ::: :::*
[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
LISTEN *: *:*
LISTEN ::: :::*
[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
LISTEN *: *:*
LISTEN *: *:*
LISTEN ::: :::*
[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
LISTEN *: *:*
LISTEN ::: :::*
[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 connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> USE yinzhengjie2019
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
MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> INSERT students (age,sex,name,mobile,address) VALUES (,'girl','Gloria Tang Tsz-Kei',null,'*'); #由于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 |
+----+---------------------+------+------+--------+-----------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
| | 尹正杰 | boy | | NULL | 北京 |
| | yinzhengjie | boy | | NULL | shanxi |
| | Gloria Tang Tsz-Kei | girl | | NULL | * |
+----+---------------------+------+------+--------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie2019]> UPDATE students SET address='陕西' WHERE id = ; #当第一次阻塞后，就从半同步复制转换成异步复制啦！若再想启用半同步复制的话需要重启复制线程。
Query OK, row affected (0.00 sec)
Rows matched: Changed: Warnings: MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name | sex | age | mobile | address |
+----+---------------------+------+------+--------+-----------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
| | 尹正杰 | boy | | NULL | 北京 |
| | yinzhengjie | boy | | NULL | 陕西 |
| | Gloria Tang Tsz-Kei | girl | | NULL | * |
+----+---------------------+------+------+--------+-----------+
rows in set (0.00 sec) 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 connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
MariaDB [(none)]> SELECT * FROM yinzhengjie2019.students;
+----+---------------------+------+------+--------+-----------+
| id | name | sex | age | mobile | address |
+----+---------------------+------+------+--------+-----------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
| | 尹正杰 | boy | | NULL | 北京 |
| | yinzhengjie | boy | | NULL | 陕西 |
| | Gloria Tang Tsz-Kei | girl | | NULL | * |
+----+---------------------+------+------+--------+-----------+
rows in set (0.00 sec) 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 connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> SELECT * FROM yinzhengjie2019.students;
+----+---------------------+------+------+--------+-----------+
| id | name | sex | age | mobile | address |
+----+---------------------+------+------+--------+-----------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
| | 尹正杰 | boy | | NULL | 北京 |
| | yinzhengjie | boy | | NULL | 陕西 |
| | Gloria Tang Tsz-Kei | girl | | NULL | * |
+----+---------------------+------+------+--------+-----------+
rows in set (0.00 sec) 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 connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
MariaDB [(none)]> SELECT @@rpl_semi_sync_master_timeout;
+--------------------------------+
| @@rpl_semi_sync_master_timeout |
+--------------------------------+
| |
+--------------------------------+
row in set (0.00 sec) 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 =
binlog_format = row
log_bin = /data/mysql/logbin/master-
rpl_semi_sync_master_enabled =
rpl_semi_sync_master_timeout = #我们将默认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 connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
MariaDB [(none)]> SELECT @@rpl_semi_sync_master_timeout;
+--------------------------------+
| @@rpl_semi_sync_master_timeout |
+--------------------------------+
| |
+--------------------------------+
row in set (0.00 sec) 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
LISTEN *: *:*
LISTEN *: *:*
LISTEN ::: :::*
[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
LISTEN *: *:*
LISTEN ::: :::*
[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 connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
MariaDB [(none)]> USE yinzhengjie2019
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
MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name | sex | age | mobile | address |
+----+---------------------+------+------+--------+-----------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
| | 尹正杰 | boy | | NULL | 北京 |
| | yinzhengjie | boy | | NULL | 陕西 |
| | Gloria Tang Tsz-Kei | girl | | NULL | * |
+----+---------------------+------+------+--------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> DELETE FROM students WHERE id = ;
Query OK, row affected (0.01 sec) MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name | sex | age | mobile | address |
+----+---------------------+------+------+--------+-----------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
| | 尹正杰 | boy | | NULL | 北京 |
| | Gloria Tang Tsz-Kei | girl | | NULL | * |
+----+---------------------+------+------+--------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie2019]>
当关闭一个节点时观察master执行语句的操作
[root@node103.yinzhengjie.org.cn ~]# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN *: *:*
LISTEN *: *:*
LISTEN ::: :::*
[root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> SELECT * FROM yinzhengjie2019.students;
+----+---------------------+------+------+--------+-----------+
| id | name | sex | age | mobile | address |
+----+---------------------+------+------+--------+-----------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
| | 尹正杰 | boy | | NULL | 北京 |
| | Gloria Tang Tsz-Kei | girl | | NULL | * |
+----+---------------------+------+------+--------+-----------+
rows in set (0.01 sec) 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
LISTEN *: *:*
LISTEN ::: :::*
[root@node103.yinzhengjie.org.cn ~]#
数据同步到另外一台未关闭的节点啦,验证完毕后将该节点也关闭掉!
[root@node102.yinzhengjie.org.cn ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
MariaDB [(none)]> USE yinzhengjie2019
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
MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name | sex | age | mobile | address |
+----+---------------------+------+------+--------+-----------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
| | 尹正杰 | boy | | NULL | 北京 |
| | yinzhengjie | boy | | NULL | 陕西 |
| | Gloria Tang Tsz-Kei | girl | | NULL | * |
+----+---------------------+------+------+--------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> DELETE FROM students WHERE id = ; #关闭一个slaves节点貌似对master没有啥影响
Query OK, row affected (0.01 sec) MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name | sex | age | mobile | address |
+----+---------------------+------+------+--------+-----------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
| | 尹正杰 | boy | | NULL | 北京 |
| | Gloria Tang Tsz-Kei | girl | | NULL | * |
+----+---------------------+------+------+--------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> DELETE FROM students WHERE id < ; #如果将2个节点都关闭，我们发现默认的超时时间的确和我们修改的一致啦~
Query OK, rows affected (2.01 sec) MariaDB [yinzhengjie2019]>
MariaDB [yinzhengjie2019]> SELECT * FROM students;
+----+---------------------+------+------+--------+-----------+
| id | name | sex | age | mobile | address |
+----+---------------------+------+------+--------+-----------+
| | 尹正杰 | boy | | NULL | 北京 |
| | Gloria Tang Tsz-Kei | girl | | NULL | * |
+----+---------------------+------+------+--------+-----------+
rows in set (0.00 sec) 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 connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
MariaDB [(none)]> SHOW STATUS LIKE 'rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | |
| Rpl_semi_sync_master_net_avg_wait_time | |
| Rpl_semi_sync_master_net_wait_time | |
| Rpl_semi_sync_master_net_waits | |
| Rpl_semi_sync_master_no_times | |
| Rpl_semi_sync_master_no_tx | |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | |
| Rpl_semi_sync_master_tx_avg_wait_time | |
| Rpl_semi_sync_master_tx_wait_time | |
| Rpl_semi_sync_master_tx_waits | |
| Rpl_semi_sync_master_wait_pos_backtraverse | |
| Rpl_semi_sync_master_wait_sessions | |
| Rpl_semi_sync_master_yes_tx | |
+--------------------------------------------+-------+
rows in set (0.00 sec) 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 connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> SHOW STATUS LIKE 'rpl_semi_sync%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW STATUS LIKE 'rpl_semi_sync%';