浅谈MYSQL之半同步复制
半同步复制来源
- 默认情况下,MySQL的复制功能是异步的,异步复制可以提供最佳的性能,主库把binlog日志发送给从库即结束,并不验证从库是否接收完毕。
- 这意味着当主服务器或从服务器端发生故障时,有可能从服务器没有接收到主服务器发送过来的binlog日志,这就会造成主服务器和从服务器的数据不一致,甚至在恢复时造成数据的丢失
半同步复制概述
- 半同步复制是介于异步复制和全同步复制之间,
- 主库在执行完客户端提交的事务后不是立刻返回给客户端,而是等待至少一个从库接收到并写到relay log中才返回给客户端。
- 相对于异步复制,半同步复制提高了数据的安全性,同时它也造成了一定程度的延迟,这个延迟最少是一个TCP/IP往返的时间。所以,半同步复制最好在低延时的网络中使用。
MySQL半同步复制存在以下问题:
- 当Slave超时时,会退化成异步复制。
- 当Master宕机时,数据一致性无法保证,需要人工处理。
- 复制是串行的
半同步原理图
半同步复制实现:
官方文档: https://mariadb.com/kb/en/library/semisynchronous-replication/
半同步复制的安装部署条件
要想使用半同步复制,必须满足以下几个条件:
-
- MySQL 5.5及以上版本
-
- 变量have_dynamic_loading为YES
-
- 异步复制已经存在
半同步复制配置参数
/ 主服务器配置:
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
UNINSTALL PLUGIN rpl_semi_sync_master ;
SHOW PLUGINS; / 查看插件
SET GLOBAL rpl_semi_sync_master_enabled=1;
SET GLOBAL rpl_semi_sync_master_timeout = 1000; /超时长1s,默认值为10s
SHOW GLOBAL VARIABLES LIKE '%semi%';
SHOW GLOBAL STATUS LIKE '%semi%';
/ 或者写进主服务器配置文件
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout = 1000
/ 从服务器配置:
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled=1;
/ 或者写进从服务器配置文件
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1
/ mariadb-10.3版以后
/ 主服务器配置:
[mysqld]
plugin_load_add = semisync_master
/ 从服务器配置:
[mysqld]
plugin_load_add = semisync_slave
实验: 实现主从半同步模式
-
准备工作
- 二台干净环境的主机
- 时间同步完成
- 防火墙关闭
- SELINUX关闭
- 实验环境是Centos7
- Mysql版本事MariaDB 5.5
-
配置1号MASTER172.20.54.1
vim /etc/my.cnf
[mysqld]
server-id=1
log-gin
plugin-load=rpl_semi_sync_master=semisync_master.so / 半同步插件模块
rpl_semi_sync_master_enabled=1 / 启用半同步插件
rpl_semi_sync_master_timeout=3000 / 半同步超时长
systemctl start mariadb
/ 查看半同步变量
MariaDB [(none)]> SHOW VARIABLES LIKE '%semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 3000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.00 sec)
/ 查看半同步插件模块
SHOW PLUGINS\G;
*************************** 43. row ***************************
Name: rpl_semi_sync_master
Status: ACTIVE
Type: REPLICATION
Library: semisync_master.so
License: GPL
43 rows in set (0.01 sec)
/ 增加复制权限账户
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'repliuser'@'172.20.54.%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (3.01 sec)
/ 注意上面时间,虽然半同步还没设置完成,但是模块已经生效
MariaDB [(none)]> SHOW MASTER LOGS;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 264 |
| mariadb-bin.000002 | 402 |
+--------------------+-----------+
2 rows in set (0.00 sec)
/ 找到创建复制权限账户之前的POS点
MariaDB [(none)]> SHOW BINLOG EVENTS IN 'mariadb-bin.000002';
+--------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+--------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------+
| mariadb-bin.000002 | 4 | Format_desc | 1 | 245 | Server ver: 5.5.64-MariaDB, Binlog ver: 4 |
| mariadb-bin.000002 | 245 | Query | 1 | 402 | GRANT REPLICATION SLAVE ON *.* TO 'repliuser'@'172.20.54.%' IDENTIFIED BY '123456' |
| mariadb-bin.000002 | 402 | Query | 1 | 491 | CREATE DATABASE TESTDB1 |
| mariadb-bin.000002 | 491 | Query | 1 | 580 | CREATE DATABASE TESTDB2 |
| mariadb-bin.000002 | 580 | Query | 1 | 669 | CREATE DATABASE TESTDB3 |
+--------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
- 配置从服务器SLAVE172.20.54.2
vim /etc/my.cnf
[mysqld]
server_id=2
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1
systemctl start mariadb
/ 查看插件加载模块
SHOW PLUGINS\G;
*************************** 43. row ***************************
Name: rpl_semi_sync_slave
Status: ACTIVE
Type: REPLICATION
Library: semisync_slave.so
License: GPL
43 rows in set (0.00 sec)
ERROR: No query specified
/ 查看半同步slave变量
MariaDB [(none)]> SHOW VARIABLES LIKE 'rpl_semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)
/ 下面是同步复制操作
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='172.20.54.1',
-> MASTER_USER='repliuser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mariadb-bin.000002',
-> MASTER_LOG_POS=245,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.03 sec)
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.20.54.1
Master_User: repliuser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mariadb-bin.000002
Read_Master_Log_Pos: 402
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 688
Relay_Master_Log_File: mariadb-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 402
Relay_Log_Space: 984
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
ERROR: No query specified
- 测试
/ 1号MASTER节点
MariaDB [(none)]> CREATE DATABASE TESTDB1;
Query OK, 1 row affected (0.00 sec)
/ 2号SLAVE节点
STOP SLAVE;
/ 1号MASTER节点,超时自动提交
MariaDB [(none)]> CREATE DATABASE TESTDB2;
Query OK, 1 row affected (3.00 sec)
/ 2号SLAVE
START SLAVE;
/ 1号MASTER节点
MariaDB [(none)]> CREATE DATABASE TESTDB3;
Query OK, 1 row affected (0.00 sec)
/ 2号从SLAVE节点
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| TESTDB1 |
| TESTDB2 |
| TESTDB3 |
| mysql |
| performance_schema |
| test |
+--------------------+
7 rows in set (0.00 sec)
主从+半步模式实现完成