mysql的主从复制和半同步复制
一.主从复制
MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实现读写分离还能进一步提升数据库的负载性能。
Mysql主从复制的实现原理图大致如下(来源网络)：
MySQL之间数据复制的基础是二进制日志文件（binary log file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。
下边我进行的实验是一个互相为主从，即server1是server2的master ，server2是server3的master，同时server2又是server1的slave
Mysql的server2链接server1， server3 链接server2，实现主从复制
主机（master）：server1 172.25.24.1
从机（slave）：server2 172.25.24.2
1.mysql数据库的安装
mysql-community-client-5.7.17-1.el6.x86_64.rpmmysql-community-common-5.7.17-1.el6.x86_64.rpmmysql-community-libs-5.7.17-1.el6.x86_64.rpmmysql-community-libs-compat-5.7.17-1.el6.x86_64.rpmmysql-community-server-5.7.17-1.el6.x86_64.rpm- 1
- 2
- 3
- 4
- 5
[root@server1 ~]# yum install *.rpm -y[root@server2 ~]# yum install *.rpm -y[root@server3 ~]# yum install *.rpm -y- 1
- 2
- 3
- 4
2.开启mysql并初始化
[root@server1 ~]# /etc/init.d/mysqld startInitializing MySQL data : [ OK ]Installing validate password plugin: [ OK ]Starting mysqld: - 1
- 2
- 3
- 4
- 5
初始化密码可以通过日志来查看：
Cat /vatlog/nginx.log
[root@server1 ~]# mysql_secure_installation ##安全初始化脚本Enter password for user root: The existing password for the user account root has expired. Please set a new password.New password: Re-enter new password: - 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
以上操作完了数据库的安装就结束了
1.修改mysql配置
找到主数据库的配置文件f /etc/my.cnf
server1配置文件加入:
server-id=1 ###服务器IP，要保证唯一性log-bin=mysql-bin ###开启二进制日志gtid_mode=ON ###gtidenforce_gtid_consistency=true ###强制gtid一致性，开启后对于特定create table不被支持## 重启服务- 1
- 2
- 3
- 4
[root@server1 ~]# /etc/init.d/mysqld restart ##重启服务Stopping mysqld: [ OK ]Starting mysqld: [ OK ]- 1
- 2
- 3
server2配置文件加入:
Server2:server-id=2log-bin=mysql-binlog-slave-updates ###默认情况下是OFF，这个参数时为了让server2去同步server3gtid_mode=ONenforce_gtid_consistency=true- 1
- 2
- 3
- 4
- 5
- 6
[root@server2 ~]# /etc/init.d/mysqld restart ##重启服务Stopping mysqld: [ OK ]Starting mysqld: [ OK ]- 1
- 2
- 3
server3配置文件加入：
server-id=3log-bin=mysql-bin- 1
- 2
[root@server2 ~]# /etc/init.d/mysqld restart ##重启服务Stopping mysqld: [ OK ]Starting mysqld: [ OK ]- 1
- 2
- 3
2.创建同步帐号
server1：
mysql> grant replication slave on *.* to 'repl'@'172.25.24.%' identified by 'Westos+007'; #分配权限Query OK, 0 rows affected, 1 warning (0.43 sec)mysql> flush privileges; #刷新权限- 1
- 2
- 3
server2（连server1并作master）
mysql> change master to master_host='172.25.24.1' ,master_user='repl', master_password='Westos+007', MASTER_AUTO_POSITION=1; # 连接server1数据库Query OK, 0 rows affected, 2 warnings (0.55 sec)mysql> start slave; #打开slaveQuery OK, 0 rows affected, 1 warning (0.00 sec)mysql> show slave statusG ##查看slave状态*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.25.24.1 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 447 Relay_Log_File: server2-relay-bin.000002 Relay_Log_Pos: 660 Relay_Master_Log_File: mysql-bin.000001 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: 447 Relay_Log_Space: 909 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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
mysql> grant replication slave on *.* to 'repl'@'172.25.24.%' identified by 'Westos+007'; #分配权限Query OK, 0 rows affected, 1 warning (0.34 sec)- 1
- 2
server3（连server2）：
mysql> change master to master_host='172.25.24.2' ,master_user='repl', master_password='Westos+007', MASTER_AUTO_POSITION=1; #连接Query OK, 0 rows affected, 2 warnings (0.15 sec)mysql> start slave; #打开slave Query OK, 0 rows affected (0.10 sec)mysql> show slave statusG #查看slave状态*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.25.24.2 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 1033 Relay_Log_File: server3-relay-bin.000002 Relay_Log_Pos: 1246 Relay_Master_Log_File: mysql-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: 1033 Relay_Log_Space: 1455 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: 0Master_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: 2 Master_UUID: 0138abca-9c46-11e8-a12a-52540022235b Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 0138abca-9c46-11e8-a12a-52540022235b:1-2,1f74a753-9c46-11e8-9fd6-525400b9dbc1:1 Executed_Gtid_Set: 0138abca-9c46-11e8-a12a-52540022235b:1-2,1f74a753-9c46-11e8-9fd6-525400b9dbc1:1 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
测试：
Server1建立表
mysql> create data test;Query OK, 1 row affected (0.05 sec)mysql> use test;Data changedmysql> create table userlist ( -> username varchar(15) not null, -> password varchar(25) not null);Query OK, 0 rows affected (0.40 sec)mysql> desc userlist;+----------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| username | varchar(15) | NO | | NULL | || password | varchar(25) | NO | | NULL | |+----------+-------------+------+-----+---------+-------+2 rows in set (0.01 sec)mysql> insert into userlist values ('user1','147');Query OK, 1 row affected (0.10 sec)mysql> insert into userlist values ('user2','258');Query OK, 1 row affected (0.08 sec)mysql> insert into userlist values ('user3','369');Query OK, 1 row affected (0.36 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
Server2：
mysql> use test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -AData changedmysql> select * from userlist;Empty set (0.00 sec)mysql> select * from userlist;+----------+----------+| username | password |+----------+----------+| user1 | 147 || user2 | 258 || user3 | 369 |+----------+----------+3 rows in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
Server3：
mysql> use testReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -AData changedmysql> select * from userlist;+----------+----------+| username | password |+----------+----------+| user1 | 147 || user2 | 258 || user3 | 369 |+----------+----------+3 rows in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
二.半同步复制
从MySQL5.5开始，MySQL以插件的形式支持半同步复制。如何理解半同步呢？首先我们来看看异步，全同步的概念
异步复制（Asynchronous replication）
MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从上，如果此时，强行将从提升为主，可能导致新主上的数据不完整。
全同步复制（Fully synchronous replication）
指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。
半同步复制（Semisynchronous replication）
介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。
原理图(源于网络):
1.安装服模块
Server1:
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so' -> ;Query OK, 0 rows affected (0.06 sec)- 1
- 2
- 3
Server2:
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';Query OK, 0 rows affected (0.11 sec)mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';Query OK, 0 rows affected (0.10 sec)- 1
- 2
- 3
- 4
Server3:
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';Query OK, 0 rows affected (0.12 sec)- 1
- 2
- 3
测试：
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
-> FROM INFORMATION_SCHEMA.PLUGINS
-> WHERE PLUGIN_NAME LIKE ‘%semi%’;
+———————-+—————+
| PLUGIN_NAME | PLUGIN_STATUS |
+———————-+—————+
| rpl_semi_sync_master | ACTIVE |
| rpl_semi_sync_slave | ACTIVE |
+———————-+—————+
2.添加服务（可以选择再命令行输入这样的是临时的加载，重启之后会消失 或者可以再配置文件中添加这样是永久的）
Server1: 开启master半复制服务
mysql> SET GLOBAL rpl_semi_sync_master_enabled=1;Query OK, 0 rows affected (0.00 sec)- 1
- 2
Server2: 开启master和slave模块打开
mysql> SET GLOBAL rpl_semi_sync_master_enabled=1;Query OK, 0 rows affected (0.00 sec)mysql> SET GLOBAL rpl_semi_sync_slave_enabled=1;Query OK, 0 rows affected (0.00 sec)- 1
- 2
- 3
- 4
- 5
Server3: 开启slave半复制模块
mysql> SET GLOBAL rpl_semi_sync_slave_enabled=1;Query OK, 0 rows affected (0.00 sec)- 1
- 2
重启slave：（server2和server3）
mysql> STOP SLAVE IO_THREAD;Query OK, 0 rows affected (0.08 sec)mysql> START SLAVE IO_THREAD;Query OK, 0 rows affected (0.00 sec)- 1
- 2
- 3
- 4
- 5
3.查看状态
Server1:查看
mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';+-------------------------------------------+------------+| Variable_name | Value |+-------------------------------------------+------------+| rpl_semi_sync_master_enabled | ON || rpl_semi_sync_master_timeout | 10000 || rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_for_slave_count | 1 | 启动了一个服务| rpl_semi_sync_master_wait_no_slave | ON || rpl_semi_sync_master_wait_point | AFTER_SYNC | 启动服务+-------------------------------------------+------------+6 rows in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%'; 查看服务列表+--------------------------------------------+-------+| Variable_name | Value |+--------------------------------------------+-------+| Rpl_semi_sync_master_clients | 1 || Rpl_semi_sync_master_net_avg_wait_time | 0 || Rpl_semi_sync_master_net_wait_time | 0 || Rpl_semi_sync_master_net_waits | 0 || Rpl_semi_sync_master_no_times | 0 || Rpl_semi_sync_master_no_tx | 0 || 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 | 0 |+--------------------------------------------+-------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
Server2：
mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';+-------------------------------------------+------------+| Variable_name | Value |+-------------------------------------------+------------+| rpl_semi_sync_master_enabled | ON || rpl_semi_sync_master_timeout | 10000 || rpl_semi_sync_master_trace_level | 32 || rpl_semi_sync_master_wait_for_slave_count | 1 |##接受几个ack才会提交| rpl_semi_sync_master_wait_no_slave | ON || rpl_semi_sync_master_wait_point | AFTER_SYNC || rpl_semi_sync_slave_enabled | ON || rpl_semi_sync_slave_trace_level | 32 |+-------------------------------------------+------------+8 rows in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
Server3:
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';+----------------------------+-------+| Variable_name | Value |+----------------------------+-------+| Rpl_semi_sync_slave_status | ON |+----------------------------+-------+1 row in set (0.00 sec)mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';+---------------------------------+-------+| Variable_name | Value |+---------------------------------+-------+| rpl_semi_sync_slave_enabled | ON || rpl_semi_sync_slave_trace_level | 32 |+---------------------------------+-------+2 rows in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
4.测试
Server1:
mysql> use testReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -AData changedmysql> insert into userlist values('user4','111');Query OK, 1 row affected (0.11 sec)mysql> insert into userlist values('user5','555');Query OK, 1 row affected (0.11 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
Server2:
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';+--------------------------------------------+-------+| Variable_name | Value |+--------------------------------------------+-------+| Rpl_semi_sync_master_clients | 1 || Rpl_semi_sync_master_net_avg_wait_time | 0 || Rpl_semi_sync_master_net_wait_time | 0 || Rpl_semi_sync_master_net_waits | 2 || Rpl_semi_sync_master_no_times | 0 || Rpl_semi_sync_master_no_tx | 0 || Rpl_semi_sync_master_status | ON || Rpl_semi_sync_master_timefunc_failures | 0 || Rpl_semi_sync_master_tx_avg_wait_time | 498 || Rpl_semi_sync_master_tx_wait_time | 997 || Rpl_semi_sync_master_tx_waits | 2 || Rpl_semi_sync_master_wait_pos_backtraverse | 0 || Rpl_semi_sync_master_wait_sessions | 0 || Rpl_semi_sync_master_yes_tx | 2 || Rpl_semi_sync_slave_status | ON |+--------------------------------------------+-------+15 rows in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
Server3:
mysql> use testData changedmysql> select * from userlist;+----------+----------+| username | password |+----------+----------+| user1 | 147 || user2 | 258 || user3 | 369 || user4 | 111 || user5 | 555 |+----------+----------+5 rows in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
为什么叫半同步：
server2:
mysql> STOP SLAVE IO_THREAD; 停止io线程Query OK, 0 rows affected (0.06 sec)- 1
- 2
Server1：
mysql> insert into userlist values ('user6','666'); 在建立新的表格数据时候，会有10s延迟Query OK, 1 row affected (10.09 sec)- 1
- 2
server3：
mysql> mysql> select * from userlist;+----------+----------+| username | password |+----------+----------+| user1 | 147 || user2 | 258 || user3 | 369 || user4 | 111 || user5 | 555 |+----------+----------+5 rows in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
再开启server2的IO线程
mysql> START SLAVE IO_THREAD;Query OK, 0 rows affected (0.00 sec)- 1
- 2
Server3： 数据会同步。
mysql> select * from userlist;+----------+----------+| username | password |+----------+----------+| user1 | 147 || user2 | 258 || user3 | 369 || user4 | 111 || user5 | 555 || user6 | 666 |+----------+----------+6 rows in set (0.00 sec)原文地址https://blog.csdn.net/a939029674/article/details/81632565继续阅读与本文标签相同的文章
上一篇 :
excel导入工具
下一篇 :
数字化转型是企业制胜的法宝
-
C# 面试题解析-请遍历页面上所有的TextBox控件并给它赋值为string.Empty
2026-05-25栏目： 教程
-
DNN使用非80端口和总是跳转到http://localhost问题的解决（翻译）
2026-05-25栏目： 教程
-
如何使用DNN中的Calendar控件
2026-05-25栏目： 教程
-
模块开发中一点疑惑？
2026-05-25栏目： 教程
-
对DNN的理解：
2026-05-25栏目： 教程