MySQL

mysql5.7主从同步 MySQL5.7主从复制从零开始设置及详解

MySQL5.7主从复制从零开始设置及详解——实现多线程并行同步,解决主从复制延迟问题!有一个项目要求实现一个数据实时传输的功能,实时性要求也不是特别高,于是想到了使用数据库同步的方法解决这个问题,但之前因为使用5.5版本的mysql,设置的主从复制在数据量较大或者网络拥塞的时候延迟会更高,而且经过查资料,老版本是无法从根本上改善这个问题的。最近了解了MySQL 5.7版本的特性,知道了5.7版本有个新特性可以更大的改善这个问题。接下来对相关的内容进行详细的总结和概括。

(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复制的速度可以有比较大的提升。通常情况下单库多表是更常见的一种情形,所以基于库的并发就没有什么卵用。

(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、数据库记录更新测试

接下来就可以在主数据库中添加记录了, 添加之后便可看到从数据库中已经实时更新了。