mysql的半同步、延迟及并行复制
MySQL的同步复制
MySQL之间数据复制的基础是二进制日志文件（binary log file）。MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中（relay_log），然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。
MySQL的半同步复制
介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用
1 实验环境
server1为主
server2为辅
server3为server2的辅助
上篇博客我们已经配置了server2为server1的辅数据库，这篇博客将在上篇博客的基础上介绍线性的，即server为server1的辅助，server2作为server3的辅助
实验的前提保证部署前server3和server2的数据库必须保持一致
因为server2中比server3中只多了linux库，故备份server2中的linux库
mysqldump -p linux --set-gtid-purged=OFF > db.sql
scp db.sql server3:/root
注意： --set-gtid-purged=OFF必须添加，会记录binlog日志，否则不记录binlog日志，所以在我们做主从用了gtid时，用mysqldump备份时就要加–set-gtid-purged=OFF，否则你在主上导入恢复了数据，主没有了binlog日志，同步则不会被同步。
2 在server3上同步数据库
（1）在server3上还原数据库
cd /root ##数据库备份文件的位置
mysqladmin -p create linux;
mysql -p linux < db.sql;
mysql -p
select * from linux.test;
server2：
- 查看master的状态：
show master status\G;
grant replication slave on *.* to marry@'%' identified by 'westos';
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.12.1
Master_User: marry
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 1688
Relay_Log_File: server2-relay-bin.000004
Relay_Log_Pos: 618
Relay_Master_Log_File: binlog.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: 1688
Relay_Log_Space: 867
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 5cc61444-5514-11eb-acb0-5254007dfb7a
Master_Info_File: /data/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: 5cc61444-5514-11eb-acb0-5254007dfb7a:2-6
Executed_Gtid_Set: 4c070d46-5519-11eb-b46b-525400031029:1-4,
5cc61444-5514-11eb-acb0-5254007dfb7a:1-6
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
server3：
change master to master_host='172.25.12.2', master_user='marry',master_password='westos',master_auto_position=1;
start slave; ## 启动slave
show slave status\G; ## 查看slave的状态
测试
- 在server1插入数据：
insert into linux.test values ('user4','18');
select * from linux.test;
- server2和server3查看数据
3 半同步复制
（1）server3端安装半同步的工具
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
（2）server1端开启半同步slave功能
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
stop slave io_thread;
start slave io_thread;
show status like 'Rpl_semi%';
（3）server2：安装半同步的插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
SET GLOBAL rpl_semi_sync_master_enabled = 1;
stop slave io_thread;
start slave io_thread;
（4）server1端开启半同步master功能
SET GLOBAL rpl_semi_sync_master_enabled =1;
show variables like 'rpl%';
- 查看半同步的状态：
show status like 'Rpl_semi%';
（5）测试
- 在server1的linux库的test表中插入数据，数据库的同步成功
4 延迟复制
server3：
（1）在server3上设置延迟复制
stop slave; ## 停止集群
CHANGE MASTER TO MASTER_DELAY=20; ## 设置master的延迟时间
start slave; ## 启动集群
show slave status\G; ## 查看集群状态
Seconds_Behind_Master: 0
SQL_Delay: 20
（2）测试：
- 在server1数据库中插入数据
- server2同步更新server1数据库中的数据：
select * from linux.test;
- 查看server2的slave的状态：
show slave status\G;
- server3在20s内未更新server1数据库的信息：
select * from linux.test;
- 查看server3的slave状态：
show slave status\G;
- 20s之后server3成功同步server1的数据库信息：
select * from linux.test;
5 并行复制
- 查看中继日志的信息：
cat relay-log.info
- 查看master的信息：
cat master.info
（1）编辑数据库的配置文件：vim /etc/my.cnf
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
（2）重启mysql服务：/etc/init.d/mysqld restart
- 登录数据库：
mysql -p
show processlist;
mysql> show processlist;
+----+-------------+---------------+------+------------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+---------------+------+------------------+------+---------------------------------------------------------------+------------------+
| 1 | system user | | NULL | Connect | 172 | Waiting for master to send event | NULL |
| 2 | system user | | NULL | Connect | 172 | Slave has read all relay log; waiting for more updates | NULL |
| 3 | system user | | NULL | Connect | 172 | Waiting for an event from Coordinator | NULL |
| 4 | system user | | NULL | Connect | 172 | Waiting for an event from Coordinator | NULL |
| 5 | system user | | NULL | Connect | 172 | Waiting for an event from Coordinator | NULL |
| 6 | system user | | NULL | Connect | 172 | Waiting for an event from Coordinator | NULL |
| 7 | system user | | NULL | Connect | 172 | Waiting for an event from Coordinator | NULL |
| 8 | system user | | NULL | Connect | 172 | Waiting for an event from Coordinator | NULL |
| 9 | system user | | NULL | Connect | 172 | Waiting for an event from Coordinator | NULL |
| 10 | system user | | NULL | Connect | 172 | Waiting for an event from Coordinator | NULL |
| 11 | system user | | NULL | Connect | 172 | Waiting for an event from Coordinator | NULL |
| 12 | system user | | NULL | Connect | 172 | Waiting for an event from Coordinator | NULL |
| 13 | system user | | NULL | Connect | 172 | Waiting for an event from Coordinator | NULL |
| 15 | system user | | NULL | Connect | 172 | Waiting for an event from Coordinator | NULL |
| 16 | system user | | NULL | Connect | 172 | Waiting for an event from Coordinator | NULL |
| 17 | system user | | NULL | Connect | 172 | Waiting for an event from Coordinator | NULL |
| 18 | system user | | NULL | Connect | 172 | Waiting for an event from Coordinator | NULL |
| 19 | system user | | NULL | Connect | 172 | Waiting for an event from Coordinator | NULL |
| 20 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 21 | marry | server3:52046 | NULL | Binlog Dump GTID | 152 | Master has sent all binlog to slave; waiting for more updates | NULL |
+----+-------------+---------------+------+------------------+------+---------------------------------------------------------------+------------------+
use mysql;
show tables;
select * from slave_relay_log_info;
select * from slave_master_info;
2996
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
