MySQL5.7传统主从复制、基于Gtid的主从复制、基于组提交的并行复制、半同步复制
一、MySQL主从复制原理
Mysql之间数据复制的基础时二进制日志文件。一台Mysql数据库一旦启用二进制日志后，其作为master，他的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，因此实现从数据库和主数据库一致性，也就实现了主从复制。
在此过程中，主库有个Binlog Dump线程，从库生成了两个线程，一个I/O线程，一个SQL线程
主从复制存在的问题
- 主库宕机后，数据可能丢失
- 从库只有一个SQL Thread，主库写压力大，复制很可能出现延时（可能需要应用数据量较大，可能和从库本身的一些操作有锁和资源的冲突；主库可以并发写，但是SQL线程不可以；主要原因）
解决方法 - 半同步复制—解决数据丢失问题
- 并行复制—解决从库复制延时问题
二、配置主从复制
主从库的配置流程
-
主服务器：
（1）开启二进制日志
（2）配置唯一的server-id
（3）获得master二进制日志文件名及位置
（4）创建一个用于slave和master通信的用户帐号 -
从服务器：
（1）配置唯一的server-id
（2）使用master分配的用户帐号读取master二进制日志
（3）启用slave服务
1、在server2(master)和server3(slave)解压安装数据库
[[email protected] ~]# tar xf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
[[email protected] ~]# yum install -y mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-1.el6.x86_64.rpm mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm
2、查看数据库的初始密码
[[email protected] ~]# /etc/init.d/mysqld start
[[email protected] ~]# cat /var/log/mysqld.log |grep password
3、数据库的安全初始化并且修改密码（密码必须是八位以上，并且包含数字大小写，特殊字符）
[[email protected] ~]# mysql_secure_installation
4、设置server-id
[[email protected] ~]# vim /etc/my.cnf
29 server-id=1 #服务器id（两台主机的id号不能一样）
30 log-bin=mysql-bin #开启二进制日志
[[email protected] ~]# /etc/init.d/mysqld start
5、Mysql主库的配置
[[email protected] ~]# mysql -p
Enter password:
mysql> grant replication slave on *.* to 'server3'@'172.25.66.3' identified by '[email protected]'; #赋予server3'@'172.25.66.3主机登录数据库，并给slave复制的权力
Query OK, 0 rows affected, 1 warning (0.40 sec)
mysql> flush privileges; #刷新
Query OK, 0 rows affected (0.06 sec)
6、Mysql从库的配置
[[email protected] ~]# mysql -p
Enter password:
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>change master to master_host='172.25.66.2',master_user='server3',master_password='[email protected]',master_log_file='mysql-bin.000003',master_log_pos=1198; #这里_host和_user是主库中赋予的主机名还有ip，_log_file和_log_pos为在主库用show master status查看出来的
Query OK, 0 rows affected, 2 warnings (0.19 sec)
mysql> start slave; #开启从库
Query OK, 0 rows affected (0.03 sec)
遇到的问题
-
当出现Slave_IO_Running: Connecting的提示时，说明主库和从库没有连接上，有以下三点原因：
- 网络问题：检查网络连接是否能够连接上
- 密码或POS号错误：查看pos号和主库的号是否对应
- 防火墙的问题：查看主库防火墙的策略，数据库是否拒绝外来连接，然后做相应的改动
我在实验中也遇到了这个错误，原因是因为在slave上
mysql>change master to master_host=‘172.25.66.2’,master_user=‘westos_jy’,master_password=‘[email protected]’,master_log_file=‘mysql-bin.000003’,master_log_pos=1198;
我这里的master_user=‘westos_jy’,写错了，在master上我写的是‘server3’所以出现了这个问题
注意：这里的master_host是你主库的ip，master_user这个用户是你在主库中赋予哪个用户权力，这里就写谁，master_password密码就写你主库的mysql登录密码
-
语法错误
当输完mysql> show slave status\G;，出现了
ERROR:
No query specified这个错误。
解决方法：mysql> show slave status\G这个后面不能分号，因为\G在功能上就等于分号，这样一加就相当于是两个分号 -
在查看mysql日志时出现的这个错误Error reading relay log event for channel ‘’: slave SQL thread was killed
解决办法：执行mysql> stop slave;这个命令，然后在change，之后在start slave
7、主从复制测试：
主库：
mysql> create database user; #新建user数据库
Query OK, 1 row affected (0.11 sec)
mysql> use user #进入user数据库
mysql> create table userinfo( #在user数据库里面建userinfo表
-> username varchar(10) not null, #username不超过10个字符，且不能为空
-> password varchar(10) not null); #密码不超过10个字符，且不能为空
Query OK, 0 rows affected (0.59 sec)
mysql> desc userinfo; #显示userinfo表的数据结构
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | NO | | NULL | |
| password | varchar(10) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> insert into userinfo values ('jy','123'); #在userinfo表中插入值
Query OK, 1 row affected (0.08 sec)
mysql> select * from userinfo; #查询user库下userinfo表中的所有内容
+----------+----------+
| username | password |
+----------+----------+
| jy | 123 |
+----------+----------+
1 row in set (0.00 sec)
从库
mysql> show databases;
mysql> use user;
mysql> show tables;
mysql> select * from userinfo;
二、利用Gtid实现主从复制
1、Gtid的工作原理
master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。slave端的i/o 线程将变更的binlog，写入到本地的relay log中。sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。如果有记录，说明该GTID的事务已经执行，slave会忽略。如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。
2、基于Gtid复制的优点
一个事务对应一个唯一ID，一个GTID在一个服务器上只会执行一次。.GTID是用来代替传统复制的方法，GTID复制与普通复制模式的最大不同就是不需要指定二进制文件名和位置。减少手工干预和降低服务故障时间，当主机挂了之后通过软件从众多的备机中提升一台备机为主机
3、修改配置文件（/etc/my.cng）并且重新启动mysql（主库和从库都要配置）
[[email protected] ~]# vim /etc/my.cnf
31 gtid_mode=ON
32 enforce-gtid-consistency=1 #这里也可以写成true
[[email protected] ~]# /etc/init.d/mysqld restart
4、配置从数据库
[[email protected] ~]# mysql -p
mysql>change master to master_host='172.25.66.2',master_user='server3',master_password='[email protected]',master_auto_position=1;
5、测试
主库
[[email protected] ~]# mysql -p
mysql> select * from userinfo;
+----------+----------+
| username | password |
+----------+----------+
| jy | 123 |
+----------+----------+
1 row in set (0.00 sec)
mysql> insert into userinfo values ('yh','234');
Query OK, 1 row affected (0.04 sec)
mysql> select * from userinfo;
+----------+----------+
| username | password |
+----------+----------+
| jy | 123 |
| yh | 234 |
+----------+----------+
2 rows in set (0.00 sec)
从库
三、基于LOGICAL_CLOCK（组提交）的并行复制
1、mysql并行复制原理
在MySQL5.7引入基于Logical clock的并行复制方案前，MySQL使用基于Schema的并行复制，也就是基于库的，使不同db下的DML操作可以在备库并发回放。但是如果业务在Master端高并发写入一个库（或是一个表），那么slave端就会出现较大的延迟。基于schema的并行复制，slave作为只读实例提供读取功能时可以保证同schema下事务的因果序，而无法保证不同schema间的。例如当业务关注事务执行先后顺序时，在Master端db1写入T1，收到T1返回后，才在db2执行T2，但在slave端可能先读取到T2的数据，才读到T1的数据。
Mysql5.7的LOGICAL CLOCK并行复制，解除了schema的限制，使得主库对一个db或一张表并发执行的事务到slave端也可以并行执行。
2、在从库配置
[[email protected] ~]# vim /etc/my.cnf
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16 #worker线程个数
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
测试
现在就可以看出有更多的线程等着主线程的调用，这就大大解决了从库复制延时问题
四、半同步复制
1、半同步复制的原理
他是介于异步复制和全同步复制之间的，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。在5.7版本后新增ack线程（单线程工作）用于接受应答，分摊dump线程的压力
2、半同步复制的特点
- 5.5集成到mysql，以插件的形式存在，需要单独安装
- 确保事务提交后binlog至少传输到一个从库
- 不保证从库应用完这个事务的binlog
- 性能有一定的降低，响应时间会更长
- 网络异常或从库宕机，卡主主库，直到超时或从库恢复
3、配置半同步
主库（server2）的配置
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so'; #安装master的插件
mysql> set global rpl_semi_sync_master_enabled=on;
mysql> set global rpl_semi_sync_master_enabled=1; #打开半同步复制master端的开关
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 |
+-------------------------------------------+------------+
mysql> show status like 'rpl_semi_sync%' #查看各个参数的状态
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| 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 | #同步成功的次数
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
从库（server3）的配置
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.47 sec)
mysql> set global rpl_semi_sync_slave_enabled=1;
stop slave io_thread;
start slave io_thread;
mysql> show variables like 'rpl_semi_sync%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_slave_enabled | ON |
+-------------------------------------------+------------+
mysql> show variables like 'rpl_semi_sync%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+-------------------------------------------+------------+
4、测试
在server2上插入数据，在server3上看是否同步，并查看成功的次数
在server2上
mysql> use user;
Database changed
mysql> show tables;
+----------------+
| Tables_in_user |
+----------------+
| userinfo |
+----------------+
1 row in set (0.00 sec)
mysql> select * from userinfo;
+----------+----------+
| username | password |
+----------+----------+
| jy | 123 |
+----------+----------+
1 row in set (0.00 sec)
mysql> insert into userinfo values('user1','222');
Query OK, 1 row affected (0.16 sec)
mysql> insert into userinfo values('user2','222');
Query OK, 1 row affected (0.51 sec)
mysql> insert into userinfo values('user3','222');
Query OK, 1 row affected (0.13 sec)
在主库上测试看成功的次数
当关闭server3上的io线程，在server2上插入数据，查看成功次数
mysql> stop slave io_thread;