MySQL5.7主从复制从零开始设置及详解——实现多线程并行同步，解决主从复制延迟问题！
MySQL5.7主从复制从零开始设置及详解——实现多线程并行同步，解决主从复制延迟问题！有一个项目要求实现一个数据实时传输的功能，实时性要求也不是特别高，于是想到了使用数据库同步的方法解决这个问题，但之前因为使用5.5版本的mysql，设置的主从复制在数据量较大或者网络拥塞的时候延迟会更高，而且经过查资料，老版本是无法从根本上改善这个问题的。最近了解了MySQL 5.7版本的特性，知道了5.7版本有个新特性可以更大的改善这个问题。接下来对相关的内容进行详细的总结和概括。
一、Mysql 5.6 及更低版本的主从复制
（1） 在MySQL 5.6之前的版本里，有三个线程参与，都是单线程：Binlog Dump（主） ----->IO Thread （从） -----> SQL Thread（从）。复制出现延迟一般出在两个地方
1）SQL线程忙不过来（可能需要应用数据量较大，可能和从库本身的一些操作有锁和资源的冲突；） 虽然主库可以并发写，但Slave_SQL_Running线程不可以（主要原因）。 2）网络抖动导致IO线程复制延迟（次要原因）。
（2） MySQL从5.6开始有了SQL Thread多个的概念，可以并发还原数据，即并行复制技术。MySQL 5.6中，设置参数slave_parallel_workers = 4(>1)，即可有4个SQL Thread（coordinator线程）来进行并行复制，其状态为：Waiting for an evant from Coordinator。但是其并行只是基于Schema的，也就是基于库的。如果数据库实例中存在多个Schema，这样设置对于Slave复制的速度可以有比较大的提升。通常情况下单库多表是更常见的一种情形，所以基于库的并发就没有什么卵用。
二、Mysql 5.7 的主从复制
（1）新版本增加了一种类型，变成了两种类型
1、DATABASE 基于库的并行复制 ， 每个数据库对应一个复制线程（5.6版本就有了，然并卵）；
2、LOGICAL_CLOCK 基于组提交的并行复制方式，同一个数据库下可以有多个线程（对大多数数据库更实用）。
对于第二种类型，设置参数slave_parallel_workers>0并且global.slave_parallel_type＝‘LOGICAL_CLOCK’，即可支持一个schema下，slave_parallel_workers中的worker线程并发执行relay log中主库提交的事务。其核心思想是：一个组提交的事务都是可以并行回放（配合binary log group commit）；
三、具体的设置与操作
1、操作条件
主服务器：（1）系统：windows 7 （2）数据库：MySQL 5.7.18
从服务器（虚拟机）：（1）windows 7 （2）数据库：mysql 5.7.18
2、主服务器配置
（1）在主服务器上建立需同步的数据库 create database test; 并建立两张表
CREATE TABLE `backup_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) CHARACTER SET utf8 NOT NULL, `sex` varchar(2) CHARACTER SET utf8 NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1; CREATE TABLE `user` ( `User_ID` int(50) NOT NULL, `User_Name` char(100) DEFAULT NULL, PRIMARY KEY (`User_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
（2）对于windows系统，直接按键windows+R键弹出运行窗口，输入地址C:\ProgramData\MySQL\MySQL Server 5.7，找到my.ini配置文件设置
[mysqld] # 开启log-bin日志 log-bin=mysql-bin server-id=1 # 我这里要复制名为test的数据库 binlog-do-db=test然后再找到参数或者添加参数设置如下（这两个参数控制着二进制日志刷新的速度，先按下不表）：
innodb_flush_log_at_trx_commit=1 sync_binlog=1（3）然后root用户登录数据库，新建一个用户并授权（我这里设置为testuser用户，密码也是testuser，IP找到主服务器的ip写上）
CREATE USER 'testuser'@'192.168.0.193' IDENTIFIED BY 'testuser'; GRANT REPLICATION SLAVE ON *.* TO 'testuser'@'%';（4）再开一个会话，连接mysql,执行 SHOW MASTER STATUS; 显示如下，记住那个mysql-bin.000002和position的值
mysql> show master status;
+------------------+----------+--------------+------------------+---------------
----+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_
Set |
+------------------+----------+--------------+------------------+---------------
----+
| mysql-bin.000002 | 412 | test | |
|
+------------------+----------+--------------+------------------+---------------
----+
1 row in set (0.00 sec)
3、从服务器配置
（1）从服务器的server-id设置成2，且同样要在目录 C:\ProgramData\MySQL中找到my.ini文件打开进行设置 （ProgramData文件夹可能是隐藏的，直接输入地址栏就能找到），后面的设置可直接写在server-id后面，同样是在该目录下的my.ini中
server-id=2 #识别服务器的唯一值 replicate-do-db=test #要同步的数据库 replicate-do-table=test.bakeup_table #要同步的表，改成自己的数据库和表 replicate-do-table=test.user #要同步的第二个表
（2）在上面设置的参数之后紧随下面的参数
skip-slave-start=true #跳过slave线程启动 read_only=ON #开启的只读模式 relay-log=relay-bin relay-log-index=relay-bin.index
（3）配置寻找主服务器，然后启动从服务器。先输入start slave；然后执行如下命令：
CHANGE MASTER TO MASTER_HOST='主服务器ip', MASTER_USER='testuser', #新建的用户 MASTER_PASSWORD='testuser', #我的用户密码 MASTER_LOG_FILE='mysql-bin.000002', #上图查询出的同步文件 MASTER_LOG_POS=412; #上图查询出的同步点（即：position下的值）（4）接下来配置从服务器上的多线程并行复制的参数（此处为实现多线程复制的重要参数）
slave-parallel-type=LOGICAL_CLOCK slave-parallel-workers=16 #16为设置的并发线程个数，之后根据项目对数据传输的具体要求再更改 #一个schema下，slave_parallel_workers中的worker线程并发执行relay log中主库提交的事务 master_info_repository=TABLE relay_log_info_repository=TABLE relay_log_recovery=ON注：变量slave-parallel-type可以有两个值
DATABASE 为默认值，意为基于库的并行复制方式；
LOGICAL_CLOCK：基于组提交的并行复制方式
slave机器的relay log中 last_committed相同的事务（sequence_num不同）可以并发执行。
（5）最后保存my.ini并运行services.msc，重启mysql服务。
四、配置检查及测试
1、启动从服务器的mysql命令行界面
执行 start slave;
再查看其状态，执行
show slave status\G;
结果如下：
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.193
Master_User: testuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 412
Relay_Log_File: slave-relay-bin.000007
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes <<<--------------------------此处可以看到
Slave_SQL_Running: Yes <<<--------------------------这两个线程都在运行
Replicate_Do_DB: test
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: 412
Relay_Log_Space: 951
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: ed1d6bc3-51a6-11e7-a527-083e8e9a4d6f
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more up
dates <<<<<<<<<<<<<<<<<<---------------------------此处可以看到这个线程正在等待接受数据
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
2、执行命令show processlist;
查看一下正在执行等待接收数据的16个线程，结果如下：
所有线程都在等待接受数据，设置成功！
3、数据库记录更新测试
接下来就可以在主数据库中添加记录了， 添加之后便可看到从数据库中已经实时更新了。
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对我的支持！
- 文章
- 推荐
- 热门新闻