CentOS 7.5 部署 MySQL 5.7 基于GTID主从复制+并行复制+半同步复制+读写分离(ProxySQL) 环境- 运维笔记 (完整版)

 

之前已经详细介绍了Mysql基于GTID主从复制的概念,原理和配置,下面整体记录下MySQL5.7基于GTID主从复制+并行复制+增强半同步复制+读写分离环境的实现过程,以便加深对mysql新特性GTID复制的理解和实际业务场景中部署应用。

一、实验环境

[root@mysql-master ~]# cat /etc/redhat-release 
CentOS Linux release 7.5.1804 (Core) 

为了方便实验,关闭所有节点的防火墙
[root@mysql-master ~]# systemctl stop firewalld
[root@mysql-master ~]# firewall-cmd --state
not running

[root@mysql-master ~]# cat /etc/sysconfig/selinux |grep "SELINUX=disabled"
SELINUX=disabled
[root@mysql-master ~]# setenforce 0               
setenforce: SELinux is disabled
[root@mysql-master ~]# getenforce                 
Disabled

二、安装Mysql5.7

在三个mysql节点机上使用yum方式安装Mysql5.7,参考:https://www.cnblogs.com/kevingrace/p/8340690.html
 
安装MySQL yum资源库
[root@mysql-master ~]# yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
 
安装MySQL 5.7
[root@mysql-master ~]# yum install -y mysql-community-server
 
启动MySQL服务器和MySQL的自动启动
[root@mysql-master ~]# systemctl start mysqld.service
[root@mysql-master ~]# systemctl enable mysqld.service
 
设置登录密码
由于MySQL从5.7开始不允许首次安装后使用空密码进行登录!为了加强安全性,系统会随机生成一个密码以供管理员首次登录使用,
这个密码记录在/var/log/mysqld.log文件中,使用下面的命令可以查看此密码:
[root@mysql-master ~]# cat /var/log/mysqld.log|grep 'A temporary password'
2019-01-11T05:53:17.824073Z 1 [Note] A temporary password is generated for root@localhost: TaN.k:*Qw2xs
 
使用上面查看的密码TaN.k:*Qw2xs 登录mysql,并重置密码为123456
[root@mysql-master ~]# mysql -p                 #输入默认的密码:TaN.k:*Qw2xs
.............
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
 
mysql> set password=password("123456");
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
查看mysql版本
[root@mysql-master ~]# mysql -p123456
........
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.24    |
+-----------+
1 row in set (0.00 sec)

=====================================================================
温馨提示
mysql5.7通过上面默认安装后,执行语句可能会报错:
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

这个报错与Mysql 密码安全策略validate_password_policy的值有关,validate_password_policy可以取0、1、2三个值:
解决办法:
set global validate_password_policy=0;
set global validate_password_length=1;

三、配置基于GTID的主从复制 

传统的基于binlog position复制的方式有个严重的缺点:如果slave连接master时指定的binlog文件错误或者position错误,会造成遗漏或者重复,
很多时候前后数据是有依赖性的,这样就会出错而导致数据不一致。

从MYSQL5.6开始,mysql开始支持GTID复制。GTID的全称是global transaction id,表示的是全局事务ID。GTID的分配方式为uuid:trans_id,其中:uuid是每个mysql服务器都唯一的,记录在$datadir/auto.cnf中。如果复制结构中,任意两台服务器uuid重复的话(比如直接冷备份时,auto.conf中的内容是一致的),在启动复制功能的时候会报错。这时可以删除auto.conf文件再重启mysqld。

基于GTID主从复制的优点大致有:
-  保证同一个事务在某slave上绝对只执行一次,没有执行过的gtid事务总是会被执行。
-  不用像传统复制那样保证binlog的坐标准确,因为根本不需要binlog以及坐标。
-  故障转移到新的master的时候很方便,简化了很多任务。
-  很容易判断master和slave的数据是否一致。只要master上提交的事务在slave上也提交了,那么一定是一致的。
-  当然,MySQL提供了选项可以控制跳过某些gtid事务,防止slave第一次启动复制时执行master上的所有事务而导致耗时过久。
-  虽然对于row-based和statement-based的格式都能进行gtid复制,但建议采用row-based格式。 

                                                              基于GTID主从复制环境部署记录                                                             

1)mysql-master主数据库上的操作
 
在my.cnf文件中配置GTID主从复制
[root@mysql-master ~]# cp /etc/my.cnf /etc/my.cnf.bak
[root@mysql-master ~]# >/etc/my.cnf
[root@mysql-master ~]# cat /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
 
symbolic-links = 0
 
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
 
#GTID:
server_id = 1
gtid_mode = on
enforce_gtid_consistency = on
   
#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
  
#relay log
skip_slave_start = 1
 
配置后,记得重启mysql服务
[root@mysql-master ~]# systemctl restart mysqld
 
登录mysql,并查看master状态, 发现多了一项"Executed_Gtid_Set "
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
 
mysql> show global variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid   | 317e2aad-1565-11e9-9c2e-005056ac6820 |
+---------------+--------------------------------------+
1 row in set (0.00 sec)
 
查看确认gtid功能打开
mysql> show global variables like '%gtid%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| binlog_gtid_simple_recovery      | ON    |
| enforce_gtid_consistency         | ON    |
| gtid_executed                    |       |
| gtid_executed_compression_period | 1000  |
| gtid_mode                        | ON    |
| gtid_owned                       |       |
| gtid_purged                      |       |
| session_track_gtids              | OFF   |
+----------------------------------+-------+
8 rows in set (0.00 sec)
 
查看确认binlog日志功能打开
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)
 
授权slave复制用户,并刷新权限
mysql> grant replication slave,replication client on *.* to slave@'172.16.60.212' identified by "slave@123";
Query OK, 0 rows affected, 1 warning (0.03 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)
 
mysql> show grants for slave@'172.16.60.212';
+-------------------------------------------------------------------------------+
| Grants for slave@172.16.60.212                                                |
+-------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'172.16.60.212' |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
再次查看master状态
mysql> show master status;
+-------------------+----------+--------------+------------------+------------------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+-------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 |      622 |              |                  | 317e2aad-1565-11e9-9c2e-005056ac6820:1-2 |
+-------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
 
这里需要注意一下:
启动配置之前,同样需要对从服务器进行初始化。对从服务器初始化的方法基本和基于日志点是相同的,只不过在启动了GTID模式后,
在备份中所记录的就不是备份时的二进制日志文件名和偏移量了,而是记录的是备份时最后的GTID值。
   
需要先在主数据库机器上把目标库备份一下,假设这里目标库是kevin(为了测试效果,下面手动创建)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
 
mysql> CREATE DATABASE kevin CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.02 sec)
 
mysql> use kevin;
Database changed
mysql> create table if not exists haha (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);
Query OK, 0 rows affected (0.27 sec)
 
mysql> insert into kevin.haha values(1,"congcong"),(2,"huihui"),(3,"grace");    
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> select * from kevin.haha;
+----+----------+
| id | name     |
+----+----------+
|  1 | congcong |
|  2 | huihui   |
|  3 | grace    |
+----+----------+
3 rows in set (0.00 sec)
 
把kevin库备份出来
[root@mysql-master ~]# mysqldump --single-transaction --master-data=2 --triggers --routines --databases kevin -uroot -p123456 > /root/kevin.sql
 
这里稍微注意下:
mysql5.6使用mysqldump备份时,指定备份的具体库,使用--database
mysql5.7使用mysqldump备份时,指定备份的具体库,使用--databases
 
[root@mysql-master ~]# ls /root/kevin.sql
/root/kevin.sql
[root@mysql-master ~]# cat /root/kevin.sql
-- MySQL dump 10.13  Distrib 5.7.24, for Linux (x86_64)
--
-- Host: localhost    Database: kevin
-- ------------------------------------------------------
-- Server version       5.7.24-log
.............
.............
--
-- GTID state at the beginning of the backup
--
 
SET @@GLOBAL.GTID_PURGED='317e2aad-1565-11e9-9c2e-005056ac6820:1-5';
 
然后把备份的/root/kevin.sql文件拷贝到mysql-slave1从数据库服务器上
[root@mysql-master ~]# rsync -e "ssh -p22" -avpgolr /root/kevin.sql root@172.16.60.212:/root/
 
==============================================================
2)mysql-slave1从数据库上的操作
 
在my.cnf文件中配置GTID主从复制
与主服务器配置大概一致,除了server_id不一致外,从服务器还可以在配置文件里面添加:"read_only=on" ,
使从服务器只能进行读取操作,此参数对超级用户无效,并且不会影响从服务器的复制;
[root@mysql-slave1 ~]# cp /etc/my.cnf /etc/my.cnf.bak
[root@mysql-slave1 ~]# >/etc/my.cnf
[root@mysql-slave1 ~]# vim /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
 
symbolic-links = 0
 
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
 
#GTID:
server_id = 2
gtid_mode = on
enforce_gtid_consistency = on
   
#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
  
#relay log
skip_slave_start = 1
read_only = on
 
配置完成后,记得重启mysql服务
[root@mysql-slave1 ~]# systemctl restart mysqld
 
接着将主数据库目标库的备份数据kevin.sql导入到从数据库里
[root@mysql-slave1 ~]# ls /root/kevin.sql
/root/kevin.sql
[root@mysql-slave1 ~]# mysql -p123456
.........
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
 
mysql> source /root/kevin.sql;
 
mysql> select * from kevin.haha;
+----+----------+
| id | name     |
+----+----------+
|  1 | congcong |
|  2 | huihui   |
|  3 | grace    |
+----+----------+
3 rows in set (0.00 sec)
 
在从数据库里,使用change master 配置主从复制
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> change master to master_host='172.16.60.211',master_user='slave',master_password='slave@123',master_auto_position=1; 
Query OK, 0 rows affected, 2 warnings (0.26 sec)
 
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
 
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.60.211
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1357
               Relay_Log_File: mysql-slave1-relay-bin.000002
                Relay_Log_Pos: 417
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
................
................
            Executed_Gtid_Set: 317e2aad-1565-11e9-9c2e-005056ac6820:1-5
                Auto_Position: 1
 
由上面可知,mysql-slave1节点已经和mysql-master节点配置了主从同步关系
 
3) mysql-master主数据库上进行状态查看和测试测试插入
 
mysql> show master status;
+-------------------+----------+--------------+------------------+------------------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+-------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 |     1357 |              |                  | 317e2aad-1565-11e9-9c2e-005056ac6820:1-5 |
+-------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
 
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         2 |      | 3306 |         1 | 2c1efc46-1565-11e9-ab8e-00505688047c |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)
 
mysql> insert into kevin.haha values(4,"beijing"),(5,"hefei"),(10,"xihu");
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> delete from kevin.haha where id<4;
Query OK, 3 rows affected (0.10 sec)
 
mysql> select * from kevin.haha;
+----+---------+
| id | name    |
+----+---------+
|  4 | beijing |
|  5 | hefei   |
| 10 | xihu    |
+----+---------+
3 rows in set (0.00 sec)
 
4)mysql-slave1从数据库上查看
mysql> select * from kevin.haha;
+----+---------+
| id | name    |
+----+---------+
|  4 | beijing |
|  5 | hefei   |
| 10 | xihu    |
+----+---------+
3 rows in set (0.00 sec)
 
发现mysql-slave1从数据库已经将新插入的数据同步过来了,由此,基于GTID的主从同步复制关系已经正常部署完成了!

四、并行复制 (解决主从复制延迟问题)

一般Mysql主从复制有三个线程参与,都是单线程:Binlog Dump(主) -> IO Thread (从) -> SQL Thread(从)。复制出现延迟一般出在两个地方:
SQL线程忙不过来 (可能需要应用数据量较大,可能和从库本身的一些操作有锁和资源的冲突;主库可以并发写,SQL线程不可以;主要原因)
网络抖动导致IO线程复制延迟(次要原因)。

MySQL主从复制延迟的解决办法:MySQL从5.6开始有了SQL Thread多个的概念,可以并发还原数据,即并行复制技术。并行复制的机制,是MySQL的一个非常重要的特性,可以很好的解决MySQL主从延迟问题!

在MySQL 5.6中,设置参数slave_parallel_workers = 4(>1),即可有4个SQL Thread(coordinator线程)来进行并行复制,其状态为:Waiting for an evant from Coordinator。但是其并行只是基于Schema的,也就是基于库的。如果数据库实例中存在多个Schema,这样设置对于Slave复制的速度可以有比较大的提升。通常情况下单库多表是更常见的一种情形,那基于库的并发就没有卵用。其核心思想是:不同schema下的表并发提交时的数据不会相互影响,即slave节点可以用对relay log中不同的schema各分配一个类似SQL功能的线程,来重放relay log中主库已经提交的事务,保持数据与主库一致。

MySQL 5.6版本支持所谓的并行复制,但是其并行只是基于schema的,也就是基于库的。如果用户的MySQL数据库实例中存在多个schema,对于从机复制的速度的确可以有比较大的帮助。但是基于schema的并行复制存在两个问题:
1) crash safe功能不好做,因为可能之后执行的事务由于并行复制的关系先完成执行,那么当发生crash的时候,这部分的处理逻辑是比较复杂的。
2) 最为关键的问题是这样设计的并行复制效果并不高,如果用户实例仅有一个库,那么就无法实现并行回放,甚至性能会比原来的单线程更差。而 单库多表是比多库多表更为常见的一种情形 。

注意:mysql 5.6的MTS是基于库级别的并行,当有多个数据库时,可以将slave_parallel_workers设置为数据库的数量,为了避免新建库后来回修改,也可以将该参数设置的大一些。设置为库级别的事务时,不允许这样做,会报错。

在MySQL 5.7中,引入了基于组提交的并行复制(官方称为Enhanced Multi-threaded Slaves,即MTS),设置参数slave_parallel_workers>0并且global.slave_parallel_type=‘LOGICAL_CLOCK’,即可支持一个schema下,slave_parallel_workers个的worker线程并发执行relay log中主库提交的事务。其核心思想:一个组提交的事务都是可以并行回放(配合binary log group commit);slave机器的relay log中 last_committed相同的事务(sequence_num不同)可以并发执行。其中,变量slave-parallel-type可以有两个值:1)DATABASE 默认值,基于库的并行复制方式;2)LOGICAL_CLOCK,基于组提交的并行复制方式;

MySQL 5.7开启Enhanced Multi-Threaded Slave很简单,只需要在Slave从数据库的my.cnf文件中如下配置即可:

# slave
 slave-parallel-type=LOGICAL_CLOCK
 slave-parallel-workers=8        #一般建议设置4-8,太多的线程会增加线程之间的同步开销
 master_info_repository=TABLE
 relay_log_info_repository=TABLE
 relay_log_recovery=ON

MySQL 5.7是基于组提交的并行复制,并且是支持"真正"的并行复制功能,这其中最为主要的原因:就是slave服务器的回放与主机是一致的, 即master服务器上是怎么并行执行的slave上就怎样进行并行回放。不再有库的并行复制限制,对于二进制日志格式也无特殊的要求(基于库的并行复制也没有要求)。

MySQL5.7的并行复制,期望最大化还原主库的并行度,实现方式是在binlog event中增加必要的信息,以便slave节点根据这些信息实现并行复制。MySQL5.7的并行复制建立在group commit的基础上,所有在主库上能够完成prepared的语句表示没有数据冲突,就可以在slave节点并行复制。

所以在并行复制环境中,除了在Slace从数据库中配置之外,还需要在Master主数据库上的my.cnf文件中添加binlog_group_commit配置,否则从库无法做到基于事物的并行复制:

binlog_group_commit_sync_delay = 100                
binlog_group_commit_sync_no_delay_count = 10

binlog_group_commit_sync_delay,这个参数控制着日志在刷盘前日志提交要等待的时间,默认是0也就是说提交后立即刷盘,但是并不代表是关闭了组提交,当设置为0以上的时候,就允许多个事物的日志同时间一起提交刷盘,也就是我们说的组提交。组提交是并行复制的基础,我们设置这个值的大于0就代表打开了组提交的延迟功能,而组提交是默认开启的。最大值只能设置为1000000微妙。
binlog_group_commit_sync_no_delay_count ,这个参数表示我们在binlog_group_commit_sync_delay等待时间内,如果事物数达到这个参数的设定值,就会触动一次组提交,如果这个值设为0的话就不会有任何的影响。如果到达时间但是事物数并没有达到的话,也是会进行一次组提交操作的。

MySQL 5.7并行复制的思想简单易懂,一言以蔽之: 一个组提交的事务都是可以并行回放 ,因为这些事务都已进入到事务的prepare阶段,则说明事务之间没有任何冲突(否则就不可能提交)。为了兼容MySQL 5.6基于库的并行复制,5.7引入了新的变量slave-parallel-type,其可以配置的值有:
-  DATABASE:默认值,基于库的并行复制方式
-  LOGICAL_CLOCK:基于组提交的并行复制方式

支持并行复制的GTID
如何知道事务是否在一组中,又是一个问题,因为原版的MySQL并没有提供这样的信息。在MySQL 5.7版本中,其设计方式是将组提交的信息存放在GTID中。那么如果用户没有开启GTID功能,即将参数gtid_mode设置为OFF呢?故MySQL 5.7又引入了称之为Anonymous_Gtid的二进制日志event类型,如:

mysql> SHOW BINLOG EVENTS in 'mysql-bin.000003';
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------+
| mysql-bin.000003 | 4   | Format_desc    | 88 | 123 | Server ver: 5.7.7-rc-debug-log, Binlog ver: 4 |
| mysql-bin.000003 | 123 | Previous_gtids | 88 | 194 | f11232f7-ff07-11e4-8fbb-00ff55e152c6:1-2 |
| mysql-bin.000003 | 194 | Anonymous_Gtid | 88 | 259 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 259 | Query          | 88 | 330 | BEGIN |
| mysql-bin.000003 | 330 | Table_map      | 88 | 373 | table_id: 108 (aaa.t) |
| mysql-bin.000003 | 373 | Write_rows     | 88 | 413 | table_id: 108 flags: STMT_END_F |
......

这意味着在 MySQL 5.7版本中即使不开启GTID,每个事务开始前也是会存在一个Anonymous_Gtid ,而这GTID中就存在着组提交的信息。

LOGICAL_CLOCK
然而,通过上述的SHOW BINLOG EVENTS,并没有发现有关组提交的任何信息。但是通过mysqlbinlog工具,用户就能发现组提交的内部信息:

如下查看一个binlog日志

root@localhost:~# mysqlbinlog mysql-bin.0000002 | grep last_committed
#190112 17:45:16 server id 1  end_log_pos 219 CRC32 0xca2ee8c2  GTID    last_committed=0        sequence_number=1       rbr_only=yes
#190112 17:45:21 server id 1  end_log_pos 506 CRC32 0xe8047dd2  GTID    last_committed=1        sequence_number=2       rbr_only=yes
#190112 17:45:16 server id 1  end_log_pos 219 CRC32 0xca2ee8c2  GTID    last_committed=2        sequence_number=3       rbr_only=yes
#190112 17:45:21 server id 1  end_log_pos 506 CRC32 0xe8047dd2  GTID    last_committed=3        sequence_number=4       rbr_only=yes
可以发现较之原来的二进制日志内容多了last_committed和sequence_number,last_committed表示事务提交的时候,上次事务提交的编号,如果事务具有相同的last_committed,表示这些事务都在一组内,可以进行并行的回放 (一般是当执行的sql语句并发数大的情况下会进行组提交)。上面这个binlog日志里没有组提交信息(last_committed数值都不相等),下一个事物的last_committed永远都和上一个事物的sequence_number是相等的,这是因为事物是顺序提交的!这么理解起来并不奇怪。
下面看一下组提交模式的事物:
root@localhost:~# mysqlbinlog mysql-bin.0000006 | grep last_committed
#150520 14:23:11 server id 88 end_log_pos 259  CRC32 0x4ead9ad6 GTID last_committed=0 sequence_number=1
#150520 14:23:11 server id 88 end_log_pos 1483 CRC32 0xdf94bc85 GTID last_committed=0 sequence_number=2
#150520 14:23:11 server id 88 end_log_pos 2708 CRC32 0x0914697b GTID last_committed=0 sequence_number=3
#150520 14:23:11 server id 88 end_log_pos 3934 CRC32 0xd9cb4a43 GTID last_committed=0 sequence_number=4
#150520 14:23:11 server id 88 end_log_pos 5159 CRC32 0x06a6f531 GTID last_committed=0 sequence_number=5
#150520 14:23:11 server id 88 end_log_pos 6386 CRC32 0xd6cae930 GTID last_committed=0 sequence_number=6
#150520 14:23:11 server id 88 end_log_pos 7610 CRC32 0xa1ea531c GTID last_committed=6 sequence_number=7
#150520 14:23:11 server id 88 end_log_pos 8834 CRC32 0x96864e6b GTID last_committed=6 sequence_number=8
#150520 14:23:11 server id 88 end_log_pos 10057 CRC32 0x2de1ae55 GTID last_committed=6 sequence_number=9
#150520 14:23:11 server id 88 end_log_pos 11280 CRC32 0x5eb13091 GTID last_committed=6 sequence_number=10
#150520 14:23:11 server id 88 end_log_pos 12504 CRC32 0x16721011 GTID last_committed=6 sequence_number=11
#150520 14:23:11 server id 88 end_log_pos 13727 CRC32 0xe2210ab6 GTID last_committed=6 sequence_number=12
#150520 14:23:11 server id 88 end_log_pos 14952 CRC32 0xf41181d3 GTID last_committed=12 sequence_number=13
...

例如上述last_committed为0的事务有6个,这意味什么呢?意味着这6个事物是作为一个组提交的,6个事物在perpare截断获取相同的last_committed而且相互不影响,最终是会作为一个组进行提交?,这就是所谓的组提交!上面表示组提交时提交了6个事务,而这6个事务在从机是可以进行并行回放的。

总之:MySQL 5.7推出的Enhanced Multi-Threaded Slave解决了困扰MySQL长达数十年的复制延迟问题,再次提醒一些无知的PostgreSQL用户,不要再停留在之前对于MySQL的印象,物理复制也不一定肯定比逻辑复制有优势,而MySQL 5.7的MTS已经完全可以解决延迟问题。总之, 5.7版本后,主从复制延迟问题将不存在!

                                                       基于GTID的并行复制环境部署记录                                             

1)在mysql-slave1从数据库的my.cnf 文件中添加下面MTS并行复制的配置
[root@mysql-slave1 ~]# vim /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
   
symbolic-links = 0
   
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
   
#GTID:
server_id = 2
gtid_mode = on
enforce_gtid_consistency = on
     
#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
    
#relay log
skip_slave_start = 1
read_only = on
   
#MTS
slave-parallel-type = LOGICAL_CLOCK          #开启逻辑时钟的复制
slave-parallel-workers = 4                   #这里设置线程数为4 (最大线程数不能超过16,即最大线程为16)
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = on
   
配置完成后,记得重启mysql服务
[root@mysql-slave1 ~]# systemctl restart mysqld
   
2)在mysql-master主数据库的my.cnf 文件中添加最后两行配置
[root@mysql-master ~]# vim /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
   
symbolic-links = 0
   
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
   
#GTID:
server_id = 1
gtid_mode = on
enforce_gtid_consistency = on
     
#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
    
#relay log
skip_slave_start = 1
   
#不配置binlog_group_commit从库无法做到基于事物的并行复制
binlog_group_commit_sync_delay = 100               
binlog_group_commit_sync_no_delay_count = 10
   
#为了数据安全再配置
sync_binlog=1
innodb_flush_log_at_trx_commit =1 
#这个参数控制binlog写入 磁盘的方式。设置为1时,表示每次commit;都写入磁盘。这个刷新的是redo log 即ib_logfile0,而不是binlog
   
配置完成后,记得重启mysql服务
[root@mysql-master ~]# systemctl restart mysqld
   
登录mysql,查看binlog_group_commit 
mysql> show variables like 'binlog_group_commit_%';
+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| binlog_group_commit_sync_delay          | 100   |
| binlog_group_commit_sync_no_delay_count | 10    |
+-----------------------------------------+-------+
2 rows in set (0.00 sec)
   
设置binlog_group_commit的上面两个参数,否则从库无法做到基于事物的并行复制! 这两个参数共同决定了是否触发组提交操作!
第二个参数表示该事务组提交之前总共等待累积到多少个事务(如上要累计到10个事务);
第一个参数则表示该事务组总共等待多长时间后进行提交(如上要总共等待100毫秒的时间),任何一个条件满足则进行后续操作。
因为有这个等待,可以让更多事务的binlog通过一次写binlog文件磁盘来完成提交,从而获得更高的吞吐量。
 
3)登录mysql-slave1从数据库
 
上面在配置并行复制后,主从数据库的mysqld服务都重启了,此时需要重启从数据库上的slave,这样才能恢复正常的主从同步状态!
记住:只要主数据库的mysqld服务重启,那么从数据库上就要重启slave,以恢复主从同步状态!!!
 
mysql> show slave status \G;        
...........
...........
             Slave_IO_Running: No
            Slave_SQL_Running: No
   
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
   
mysql> start slave;
Query OK, 0 rows affected (0.10 sec)
   
mysql> show slave status \G;
..........
..........
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
..........
            Executed_Gtid_Set: 317e2aad-1565-11e9-9c2e-005056ac6820:1-11
                Auto_Position: 1
   
这样,mysql-slave1从数据库就恢复了与mysql-master主数据库的同步关系了
 
查看优化项
mysql> use mysql;                                 #切入到mysql库
Database changed
mysql> select * from slave_worker_info;                 #查看slave_worker_info表
+----+---------------------------------+---------------+-------------------+----------------+---------------------------------+--------------------------+----------------------------+---------------------------+------------------+-----------------------+------------------------------------------------------------------+--------------+
| Id | Relay_log_name                  | Relay_log_pos | Master_log_name   | Master_log_pos | Checkpoint_relay_log_name       | Checkpoint_relay_log_pos | Checkpoint_master_log_name | Checkpoint_master_log_pos | Checkpoint_seqno | Checkpoint_group_size | Checkpoint_group_bitmap                                          | Channel_name |
+----+---------------------------------+---------------+-------------------+----------------+---------------------------------+--------------------------+----------------------------+---------------------------+------------------+-----------------------+------------------------------------------------------------------+--------------+
|  1 | ./mysql-slave1-relay-bin.000005 |           656 | mysql-bin.000008 |            481 | ./mysql-slave1-relay-bin.000005 |                      369 | mysql-bin.000008          |                       194 |                0 |                    64 |                                                                 |              |
|  2 |                                 |             0 |                   |              0 |                                 |                        0 |                            |                         0 |                0 |                    64 |                                                                  |              |
|  3 |                                 |             0 |                   |              0 |                                 |                        0 |                            |                         0 |                0 |                    64 |                                                                  |              |
|  4 |                                 |             0 |                   |              0 |                                 |                        0 |                            |                         0 |                0 |                    64 |                                                                  |              |
+----+---------------------------------+---------------+-------------------+----------------+---------------------------------+--------------------------+----------------------------+---------------------------+------------------+-----------------------+------------------------------------------------------------------+--------------+
4 rows in set (0.00 sec)
   
以上可以查看到,mysql库下的slave_worker_info表下查看到4个线程
  
也可以使用下面命令查看线程数,这个跟在my.cnf文件里配置的是一样的!
mysql> show variables like '%slave_para%';
+------------------------+---------------+
| Variable_name          | Value         |
+------------------------+---------------+
| slave_parallel_type    | LOGICAL_CLOCK |
| slave_parallel_workers | 4             |
+------------------------+---------------+
2 rows in set (0.00 sec)
 
4)同步复制测试
在mysql-master主数据库插入新数据
mysql> insert into kevin.haha values(21,"caiyi"),(22,"manman"),(23,"titi");
Query OK, 3 rows affected (0.09 sec)
Records: 3  Duplicates: 0  Warnings: 0
   
mysql> select * from kevin.haha;
+----+---------+
| id | name    |
+----+---------+
|  4 | beijing |
|  5 | hefei   |
| 10 | xihu    |
| 21 | caiyi   |
| 22 | manman  |
| 23 | titi    |
+----+---------+
6 rows in set (0.00 sec)
   
然后在mysql-slave1从数据库查看,发现新数据已经同步过来了
mysql> select * from kevin.haha;
+----+---------+
| id | name    |
+----+---------+
|  4 | beijing |
|  5 | hefei   |
| 10 | xihu    |
| 21 | caiyi   |
| 22 | manman  |
| 23 | titi    |
+----+---------+
6 rows in set (0.00 sec)

最后在mysql-master主数据库上查看组提交信息
[root@mysql-master ~]# cd /var/lib/mysql
[root@mysql-master mysql]# ll mysql-bin*
-rw-r----- 1 mysql mysql  751 Jan 12 18:08 mysql-bin.000001
-rw-r----- 1 mysql mysql 1365 Jan 12 19:28 mysql-bin.000002
-rw-r----- 1 mysql mysql 1326 Jan 12 19:42 mysql-bin.000003
-rw-r----- 1 mysql mysql   57 Jan 12 19:28 mysql-bin.index

[root@mysql-master mysql]# mysqlbinlog mysql-bin.000003 |grep last_committed
#190112 19:37:06 server id 1  end_log_pos 259 CRC32 0x893c0ae8  GTID    last_committed=0       sequence_number=1       rbr_only=yes
#190112 19:37:12 server id 1  end_log_pos 542 CRC32 0xc36def61  GTID    last_committed=1       sequence_number=2       rbr_only=yes
#190112 19:42:42 server id 1  end_log_pos 825 CRC32 0x361701a2  GTID    last_committed=2       sequence_number=3       rbr_only=yes
#190112 19:42:48 server id 1  end_log_pos 1108 CRC32 0x8ba858c7 GTID    last_committed=3       sequence_number=4       rbr_only=yes

如上,通过最新的binlog日志看到,last_committed数值都不一样,即没有事务是在同一个组内提交的,也就是说这个日志里没有组提交信息。
这是因为没有满足binlog_grup_commit那两个参数的条件,所以没有进行组提交!

五、半同步复制

半同步复制: 默认情况下MySQL的复制是异步的,master将新生成的binlog发送给各slave后,无需等待slave的ack回复(slave将接收到的binlog写进relay log后才会回复ack),直接就认为这次DDL/DML成功了, 半同步复制(semi-synchronous replication)是指master将新生成的binlog发送给各slave时, 只需等待一个(默认)slave返回的ack信息就返回成功。

MySQL 5.7对半同步复制作了大改进,新增了一个master线程。在MySQL 5.7以前,master上的binlog dump线程负责两件事:dump日志给slave的io_thread;接收来自slave的ack消息。它们是串行方式工作的。在MySQL 5.7中,新增了一个专门负责接受ack消息的线程ack collector thread。这样master上有两个线程独立工作,可以同时发送binlog到slave和接收slave的ack。还新增了几个变量,其中最重要的是 rpl_semi_sync_master_wait_point ,它使得MySQL半同步复制有两种工作模型。

半同步复制的两种类型
从MySQL 5.7.2开始,MySQL支持两种类型的半同步复制。这两种类型由变量 rpl_semi_sync_master_wait_point (MySQL 5.7.2之前没有该变量)控制,它有两种值:AFTER_SYNC和AFTER_COMMIT。在MySQL 5.7.2之后,默认值为AFTER_SYNC,在此版本之前,等价的类型为AFTER_COMMIT。这个变量控制的是master何时提交、何时接收ack以及何时回复成功信息给客户端的时间点。
AFTER_SYNC模式:master将新事务写进binlog(buffer)后发送给slave,再sync到自己的binlog file(disk), 之后才允许接收slave的ack回复,接收到ack之后才会提交事务,并返回成功信息给客户端。
AFTER_COMMIT模式:master将新事务写进binlog(buffer)后发送给slave,再sync到自己的binlog file(disk),然后直接提交事务。之后才允许接收slave的ack回复,然后再返回成功信息给客户端。

如下作图,理解起来就容易了。(前提: 已经设置了sync_binlog=1,否则binlog刷盘时间由操作系统决定)

根据上面的图解,接下来分析下这两种模式的优缺点。

AFTER_SYNC
-  对于所有客户端来说,它们看到的数据是一样的,因为它们看到的数据都是在接收到slave的ack后提交后的数据。
-  这种模式下,如果master突然故障,不会丢失数据,因为所有成功的事务都已经写进slave的relay log中了,slave的数据是最新的。

AFTER_COMMIT
-  不同客户端看到的数据可能是不一样的。对于发起事务请求的那个客户端,它只有在master提交事务且收到slave的ack后才能看到提交的数据。但对于那些非本次事务的请求客户端,它们在master提交后就能看到提交后的数据,这时候master可能还没收到slave的ack。
-  如果master收到ack回复前,slave和master都故障了,那么将丢失这个事务中的数据。

在MySQL 5.7.2之前,等价的模式是 AFTER_COMMIT ,在此版本之后,默认的模式为 AFTER_SYNC ,该模式能最大程度地保证数据安全性,且性能上并不比 AFTER_COMMIT 差。

                                                               基于GTID的半同步复制环境部署记录                                                                    

Mysql 半同步复制配置可以参考: https://www.cnblogs.com/kevingrace/p/10228694.html

开启半同步复制的方法有两种: mysql命令行启动; my.cnf文件里添加启动配置;
推荐在my.cnf文件里添加半同步启动配置方式

1) 配置mysql-master主数据库上 my.cnf文件,添加启动半同步复制的配置
[root@mysql-master ~]# vim /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
    
symbolic-links = 0
    
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
    
#GTID:
server_id = 1
gtid_mode = on
enforce_gtid_consistency = on
      
#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
     
#relay log
skip_slave_start = 1
    
#不配置binlog_group_commit从库无法做到基于事物的并行复制
binlog_group_commit_sync_delay = 100              
binlog_group_commit_sync_no_delay_count = 10
    
#为了数据安全再配置
sync_binlog=1
innodb_flush_log_at_trx_commit =1
#这个参数控制binlog写入 磁盘的方式。设置为1时,表示每次commit;都写入磁盘。这个刷新的是redo log 即ib_logfile0,而不是binlog

#开启半同步复制 (超时时间为1s)
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 1000 

配置完成后,记得重启mysql服务
[root@mysql-master ~]# systemctl restart mysqld

2) 配置mysql-slave从数据库上 my.cnf文件,添加启动半同步复制的配置
[root@mysql-slave1 ~]# vim /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
  
symbolic-links = 0
  
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
  
#GTID:
server_id = 2
gtid_mode = on
enforce_gtid_consistency = on
    
#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
   
#relay log
skip_slave_start = 1
read_only = on
  
#MTS
slave-parallel-type = LOGICAL_CLOCK          #开启逻辑时钟的复制
slave-parallel-workers = 4                               #最大线程16
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = on

# 开启半同步复制
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1

配置完成后,记得重启mysql服务
[root@mysql-slave1 ~]# systemctl restart mysqld

3)在mysql-master主数据库上加载 (前提是/usr/lib64/mysql/plugin/semisync_master.so 文件存在。 一般mysql安装后就默认产生)
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.25 sec)

查看插件是否加载成功
mysql>  SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
1 row in set (0.00 sec)

查看半同步是否在运行
mysql> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON    |
+-----------------------------+-------+
1 row in set (0.00 sec)

4)在mysql-slave1从数据库上加载 (前提是/usr/lib64/mysql/plugin/semisync_slave.so 文件存在。 一般mysql安装后就默认产生)
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.03 sec)

查看插件是否加载成功
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%';
+---------------------+---------------+
| PLUGIN_NAME         | PLUGIN_STATUS |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE        |
+---------------------+---------------+
1 row in set (0.00 sec)

这个时候,查看mysql-slave1的半同步是否运行
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF   |
+----------------------------+-------+
1 row in set (0.01 sec)

发现还是OFF,明明已经在my.cnf文件里开启了!
这是因为此时还没有生效,必须从数据库上的IO线程才能生产!!!
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> START SLAVE IO_THREAD;  
Query OK, 0 rows affected (0.00 sec)

然后再查看mysql-slave1的半同步状态,发现就已经开启了!
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

查看此时的主从同步状态
mysql> show slave status \G; 
...........
...........
             Slave_IO_Running: Yes
            Slave_SQL_Running: No

发现主从同步异常,这个时候再重启下slave即可!
mysql> stop slave;
Query OK, 0 rows affected (0.06 sec)

mysql> start slave;
Query OK, 0 rows affected (0.10 sec)

mysql> show slave status \G;
............
............
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

5) 在mysql-master主数据库上查看
mysql> show status like '%Rpl_semi%';
+--------------------------------------------+-------+
| 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     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

从上面信息,发现Rpl_semi_sync_master_clients的数值为1,说明此时mysql-master主数据库已经有一个半同步复制的从机,即mysql-slave1节点。
Rpl_semi_sync_master_yes_tx的数值为0, 说明此时还没有半同步复制的sql语句被执行。

接着在mysql-master主数据库上插入和更新数据,测试半同步复制
mysql> select * from kevin.haha;
+----+---------+
| id | name    |
+----+---------+
|  4 | beijing |
|  5 | hefei   |
| 10 | xihu    |
| 21 | caiyi   |
| 22 | manman  |
| 23 | titi    |
+----+---------+
6 rows in set (0.01 sec)

mysql> insert into kevin.haha values(30,"shikui"),(31,"shibo"),(32,"shijuan");
Query OK, 3 rows affected (0.15 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> update kevin.haha set id=8 where name="beijing";
Query OK, 1 row affected (0.22 sec)
Rows matched: 1  Changed: 1  Warnings: 0

接着再去mysql-slave1从数据库上查看,发现新数据已经同步过来了
mysql> select * from kevin.haha;
+----+---------+
| id | name    |
+----+---------+
|  5 | hefei   |
|  8 | beijing |
| 10 | xihu    |
| 21 | caiyi   |
| 22 | manman  |
| 23 | titi    |
| 30 | shikui  |
| 31 | shibo   |
| 32 | shijuan |
+----+---------+
9 rows in set (0.00 sec)

接着再去mysql-master主数据库上查看,如下:
mysql> show status like '%Rpl_semi%';
+--------------------------------------------+--------+
| 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      | 133362 |
| Rpl_semi_sync_master_tx_wait_time          | 266725 |
| 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      |
+--------------------------------------------+--------+
14 rows in set (0.01 sec)

从上面信息可发现Rpl_semi_sync_master_yes_tx的数值为2,即发生了两条半同步复制的sql语句,就是上面执行的那两条(insert 和update) sql语句。

 以上都是在mysql-master主数据库和mysql-slave1从数据库之间实现的基于GTID的主从复制、并行复制、半同步复制,即"一主一从"架构。 现在再把mysql-slave2的从节点添加进去,调整为"一主两从"的同步架构。添加操作记录如下:

1)在mysql-master主数据库上操作
先添加同步权限
mysql> grant replication slave,replication client on *.* to slave@'172.16.60.213' identified by "slave@123";
Query OK, 0 rows affected, 1 warning (0.14 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.14 sec)

备份主库
[root@mysql-master ~]# mysqldump -u root -p'123456' --lock-all-tables --master-data=2 --flush-logs --all-databases --triggers --routines --events > 213_slave.sql

将备份文件拷贝到mysql-slave2从数据库上
[root@mysql-master ~]# rsync -e "ssh -p22" -avpgolr /root/213_slave.sql root@172.16.60.213:/root/

记录当前的gtid
mysql> show global variables like 'gtid_%';
+----------------------------------+-------------------------------------------+
| Variable_name                    | Value                                     |
+----------------------------------+-------------------------------------------+
| gtid_executed                    | 317e2aad-1565-11e9-9c2e-005056ac6820:1-34 |
| gtid_executed_compression_period | 1000                                      |
| gtid_mode                        | ON                                        |
| gtid_owned                       |                                           |
| gtid_purged                      | 317e2aad-1565-11e9-9c2e-005056ac6820:1-16 |
+----------------------------------+-------------------------------------------+
5 rows in set (0.00 sec)

2)在mysql-slave2从数据库上操作
首先在my.cnf文件添加相关同步配置 (跟mysql-slave1从节点的my.cnf配置相比,除了将server_id修改为3之外,其他配置内容都一样!)
[root@mysql-slave2 ~]# cp /etc/my.cnf /etc/my.cnf.bak
[root@mysql-slave2 ~]# >/etc/my.cnf
[root@mysql-slave2 ~]# vim /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
   
symbolic-links = 0
   
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
   
#GTID:
server_id = 3
gtid_mode = on
enforce_gtid_consistency = on
     
#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
    
#relay log
skip_slave_start = 1
read_only = on
   
#MTS
slave-parallel-type = LOGICAL_CLOCK          #开启逻辑时钟的复制
slave-parallel-workers = 4                               #最大线程16
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = on
 
# 开启半同步复制
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1

配置完成后,不要忘记重启mysql服务
[root@mysql-slave2 ~]# systemctl restart mysqld

恢复备份库
[root@mysql-slave2 ~]# ll /root/213_slave.sql 
-rw-r--r-- 1 root root 805694 Jan 13 00:26 /root/213_slave.sql

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> source /root/213_slave.sql;

mysql> select * from kevin.haha;
+----+---------+
| id | name    |
+----+---------+
|  5 | hefei   |
|  8 | beijing |
| 10 | xihu    |
| 21 | caiyi   |
| 22 | manman  |
| 23 | titi    |
| 30 | shikui  |
| 31 | shibo   |
| 32 | shijuan |
+----+---------+
9 rows in set (0.00 sec)

检查一下mysql-slave2从数据库上当前的gtid 
mysql> show global variables like 'gtid_%';
+----------------------------------+-------------------------------------------+
| Variable_name                    | Value                                     |
+----------------------------------+-------------------------------------------+
| gtid_executed                    | 317e2aad-1565-11e9-9c2e-005056ac6820:1-34 |
| gtid_executed_compression_period | 1000                                      |
| gtid_mode                        | ON                                        |
| gtid_owned                       |                                           |
| gtid_purged                      | 317e2aad-1565-11e9-9c2e-005056ac6820:1-34 |
+----------------------------------+-------------------------------------------+
5 rows in set (0.01 sec)

由于是从master-master主库备份恢复过来的,所以此时mysql-slave2从数据库的gtid和主库的gtid是一样的!

接着进行主从同步
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_host='172.16.60.211',master_user='slave',master_password='slave@123',master_auto_position=1;  
Query OK, 0 rows affected, 2 warnings (0.18 sec)

mysql> start slave;
Query OK, 0 rows affected (0.17 sec)

=================================================================
如果遇到报错:
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

解决办法:
stop slave;
reset slave;
start slave;
=================================================================

查看,发现主从同步正常
mysql> show slave status \G;
.........
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

并行复制查看
mysql> show variables like '%slave_para%';
+------------------------+---------------+
| Variable_name          | Value         |
+------------------------+---------------+
| slave_parallel_type    | LOGICAL_CLOCK |
| slave_parallel_workers | 4             |
+------------------------+---------------+
2 rows in set (0.01 sec)

mysql> select * from mysql.slave_worker_info;  
+----+----------------+---------------+-----------------+----------------+---------------------------+--------------------------+----------------------------+---------------------------+------------------+-----------------------+------------------------------------------------------------------+--------------+
| Id | Relay_log_name | Relay_log_pos | Master_log_name | Master_log_pos | Checkpoint_relay_log_name | Checkpoint_relay_log_pos | Checkpoint_master_log_name | Checkpoint_master_log_pos | Checkpoint_seqno | Checkpoint_group_size | Checkpoint_group_bitmap                                          | Channel_name |
+----+----------------+---------------+-----------------+----------------+---------------------------+--------------------------+----------------------------+---------------------------+------------------+-----------------------+------------------------------------------------------------------+--------------+
|  1 |                |             0 |                 |              0 |                           |                        0 |                            |                         0 |                0 |                    64 |                                                                  |              |
|  2 |                |             0 |                 |              0 |                           |                        0 |                            |                         0 |                0 |                    64 |                                                                  |              |
|  3 |                |             0 |                 |              0 |                           |                        0 |                            |                         0 |                0 |                    64 |                                                                  |              |
|  4 |                |             0 |                 |              0 |                           |                        0 |                            |                         0 |                0 |                    64 |                                                                  |              |
+----+----------------+---------------+-----------------+----------------+---------------------------+--------------------------+----------------------------+---------------------------+------------------+-----------------------+------------------------------------------------------------------+--------------+
4 rows in set (0.00 sec)

接着在mysql-slave2从数据库上开启半同步
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS  WHERE PLUGIN_NAME LIKE '%semi%';
+---------------------+---------------+
| PLUGIN_NAME         | PLUGIN_STATUS |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE        |
+---------------------+---------------+
1 row in set (0.00 sec)

mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.02 sec)

mysql> START SLAVE IO_THREAD;  
Query OK, 0 rows affected (0.00 sec)

mysql> show status like 'Rpl_semi_sync_slave_status';      
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

3) 现在回到mysql-master主数据库查看
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         3 |      | 3306 |         1 | 2658b203-1565-11e9-9f8b-005056880888 |
|         2 |      | 3306 |         1 | 2c1efc46-1565-11e9-ab8e-00505688047c |
+-----------+------+------+-----------+--------------------------------------+
2 rows in set (0.00 sec)

如上信息可知mysql-master主数据库现在有两个从数据库,分别为mysql-slave1 和 mysql-slave2

mysql> show status like '%Rpl_semi%';                                                                      
+--------------------------------------------+--------+
| Variable_name                              | Value  |
+--------------------------------------------+--------+
| Rpl_semi_sync_master_clients               | 2      |
| Rpl_semi_sync_master_net_avg_wait_time     | 0      |
| Rpl_semi_sync_master_net_wait_time         | 0      |
| Rpl_semi_sync_master_net_waits             | 4      |
| 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      | 98077  |
| Rpl_semi_sync_master_tx_wait_time          | 392310 |
| Rpl_semi_sync_master_tx_waits              | 4      |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0      |
| Rpl_semi_sync_master_wait_sessions         | 0      |
| Rpl_semi_sync_master_yes_tx                | 4      |
+--------------------------------------------+--------+
14 rows in set (0.00 sec)

如上信息可知,mysql-master主数据库现在有两个半同步复制的从库,即mysql-slave1 和mysql-slave2

4)现在测试下同步效果
在mysql-master主数据库上更新数据
mysql> delete from kevin.haha where id>10;
Query OK, 6 rows affected (0.20 sec)

mysql> insert into kevin.haha values(11,"changping"),(12,"wangjing");
Query OK, 2 rows affected (0.38 sec)
Records: 2  Duplicates: 0  Warnings: 0

在mysql-slave1从数据库查看,发现已经同步过来了
mysql> select * from kevin.haha;
+----+-----------+
| id | name      |
+----+-----------+
|  5 | hefei     |
|  8 | beijing   |
| 10 | xihu      |
| 11 | changping |
| 12 | wangjing  |
+----+-----------+
5 rows in set (0.00 sec)

在mysql-slave2从数据库查看,发现已经同步过来了
mysql> select * from kevin.haha;
+----+-----------+
| id | name      |
+----+-----------+
|  5 | hefei     |
|  8 | beijing   |
| 10 | xihu      |
| 11 | changping |
| 12 | wangjing  |
+----+-----------+
5 rows in set (0.00 sec)

到此,已经完成了Mysql5.7"一主两从"架构上基于GTID主从复制+并行复制+增强半同步复制环境,下面再说下读写分离环境

六、读写分离 (MySQL ProxySQL)

ProxySQL是用C++语言开发的,也是percona推的一款中间件,虽然也是一个轻量级产品,但性能很好(据测试,能处理千亿级的数据),功能也足够,能满足中间件所需的绝大多数功能,包括:
最基本的读写分离,且安装起来也十分方便。
可定制基于用户、基于schema、基于语句的规则对SQL语句进行路由。换句话说,规则很灵活。基于schema和与语句级的规则,可以实现简单的sharding。
可缓存查询结果。虽然ProxySQL的缓存策略比较简陋,但实现了基本的缓存功能,绝大多数时候也够用了。此外,作者已经打算实现更丰富的缓存策略。
监控后端节点。ProxySQL可以监控后端节点的多个指标,包括:ProxySQL和后端的心跳信息,后端节点的read-only/read-write,slave和master的数据同步延迟性(replication lag)。

ProxySQL是一个能实实在在用在生产环境的MySQL中间件,可以实现读写分离,支持 Query 路由功能,支持动态指定某个 SQL 进行 cache,支持动态加载配置、故障切换和一些 SQL的过滤功能。还有一些同类产品比如 DBproxy、MyCAT、OneProxy 等。但经过反复对比和测试之后,还是觉得ProxySQL是一款性能不谙,靠谱稳定的MySQL 中间件产品 !

                                                                       ProxySQL读写分离环境部署记录                                                                

下面操作是在mysql-proxy代理层节点上完成的(需要注意: 两个从库都要开启 read_only=on)
1)先在本机安装mysql客户端,用于在本机连接到ProxySQL的管理接口

[root@mysql-proxy ~]# vim /etc/yum.repos.d/mariadb.repo
[mariadb]   
name = MariaDB
baseurl = http://yum.mariadb.org/10.3.5/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB  
gpgcheck=1

安装mysql-clinet客户端
[root@mysql-proxy ~]# yum install -y MariaDB-client 
=================================================
如果遇到报错:
Error: MariaDB-compat conflicts with 1:mariadb-libs-5.5.60-1.el7_5.x86_64
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest

解决办法:
[root@mysql-proxy ~]# rpm -qa|grep mariadb*
mariadb-libs-5.5.56-2.el7.x86_64
[root@mysql-proxy ~]# rpm -e mariadb-libs-5.5.56-2.el7.x86_64 --nodeps
[root@mysql-proxy ~]# yum install -y MariaDB-client 

2)安装ProxySQL (中文安装手册: https://github.com/malongshuai/proxysql/wiki

[root@mysql-proxy ~]# vim /etc/yum.repos.d/proxysql.repo 
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key

执行安装
[root@mysql-proxy ~]# yum -y install proxysql

[root@mysql-proxy ~]# proxysql --version
ProxySQL version 1.4.13-15-g69d4207, codename Truls

启动ProxySQL
[root@mysql-proxy ~]# chkconfig proxysql on
[root@mysql-proxy ~]# systemctl start proxysql        
[root@mysql-proxy ~]# systemctl status proxysql 

启动后会监听两个端口,
默认为6032和6033。6032端口是ProxySQL的管理端口,6033是ProxySQL对外提供服务的端口 (即连接到转发后端的真正数据库的转发端口)。
[root@mysql-proxy ~]# netstat -tunlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:6032            0.0.0.0:*               LISTEN      23940/proxysql      
tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      23940/proxysql 

3)向ProxySQL中添加MySQL节点

ProxySQL也是有管理接口和客户端接口,通过配置文件/etc/proxysql.cnf可以看到管理和客户端接口的信息.
[root@mysql-proxy ~]# cat /etc/proxysql.cnf           
.........
admin_variables=
{
        admin_credentials="admin:admin"
#       mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
        mysql_ifaces="0.0.0.0:6032"
#       refresh_interval=2000
#       debug=true
}

mysql_variables=
{
        threads=4
        max_connections=2048
        default_query_delay=0
        default_query_timeout=36000000
        have_compress=true
        poll_timeout=2000
#       interfaces="0.0.0.0:6033;/tmp/proxysql.sock"
        interfaces="0.0.0.0:6033"
        default_schema="information_schema"
        stacksize=1048576
        server_version="5.5.30"
        connect_timeout_server=3000
..........

通过上面,可以看到管理接口的端口是6032,账号密码是admin(可以动态修改),允许客户端连接, 客户端接口的端口是6033,账号密码通过管理接口去设置。
在mysql-proxy本机使用mysql客户端连接到ProxySQL的管理接口(admin interface), 该接口的默认管理员用户和密码都是admin.
下面是通过管理端口6032去连接的(下面连接命令中后面的--prompt 'admin'字段可以不加,也是可以登录进去的)
[root@mysql-proxy ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 --prompt 'admin> '
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

admin> show databases;
+-----+---------------+-------------------------------------+
| seq | name          | file                                |
+-----+---------------+-------------------------------------+
| 0   | main          |                                     |
| 2   | disk          | /var/lib/proxysql/proxysql.db       |
| 3   | stats         |                                     |
| 4   | monitor       |                                     |
| 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.001 sec)

ProxySQL提供了几个库,每个库都有各自的意义;
-  main 内存配置数据库,表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime_开头的表示proxysql当前运行的配置内容,
   不能通过dml语句修改,只能修改对应的不以 runtime_ 开头的(在内存)里的表,然后 LOAD 使其生效, SAVE 使其存到硬盘以供下次重启加载。
-  disk 是持久化到硬盘的配置,sqlite数据文件。
-  stats 是proxysql运行抓取的统计信息,包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间等等。
-  monitor 库存储 monitor 模块收集的信息,主要是对后端db的健康/延迟检查。

主要注意main和monitor数据库中的表
admin> show tables from main;
+--------------------------------------------+
| tables                                     |
+--------------------------------------------+
| global_variables                           |
| mysql_collations                           |
| mysql_group_replication_hostgroups         |
| mysql_query_rules                          |
| mysql_query_rules_fast_routing             |
| mysql_replication_hostgroups               |
| mysql_servers                              |
| mysql_users                                |
| proxysql_servers                           |
| runtime_checksums_values                   |
| runtime_global_variables                   |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_query_rules                  |
| runtime_mysql_query_rules_fast_routing     |
| runtime_mysql_replication_hostgroups       |
| runtime_mysql_servers                      |
| runtime_mysql_users                        |
| runtime_proxysql_servers                   |
| runtime_scheduler                          |
| scheduler                                  |
+--------------------------------------------+
20 rows in set (0.001 sec)

几个重要字段含义:
global_variables       设置变量,包括监听的端口、管理账号等。
mysql_collations       相关字符集和校验规则。
mysql_query_rules   定义查询路由规则。

admin> show tables from monitor;
+------------------------------------+
| tables                             |
+------------------------------------+
| mysql_server_connect_log           |
| mysql_server_group_replication_log |
| mysql_server_ping_log              |
| mysql_server_read_only_log         |
| mysql_server_replication_lag_log   |
+------------------------------------+
5 rows in set (0.000 sec)

runtime_开头的是运行时的配置,这些是不能修改的。要修改ProxySQL的配置,需要修改了非runtime_表,修改后必须执行LOAD ... TO RUNTIME
才能加载到RUNTIME生效,执行save ... to disk才能将配置持久化保存到磁盘。

使用insert语句添加主机到mysql_servers表中,其中:hostgroup_id 10 表示写组,20表示读组。
admin> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.60.211',3306); 
Query OK, 1 row affected (0.001 sec)

admin> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.60.212',3306);
Query OK, 1 row affected (0.000 sec)

admin> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.60.213',3306);
Query OK, 1 row affected (0.000 sec)

查看这3个节点是否插入成功,以及它们的状态。
admin> select * from mysql_servers\G;
*************************** 1. row ***************************
       hostgroup_id: 10
           hostname: 172.16.60.211
               port: 3306
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment: 
*************************** 2. row ***************************
       hostgroup_id: 10
           hostname: 172.16.60.212
               port: 3306
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment: 
*************************** 3. row ***************************
       hostgroup_id: 10
           hostname: 172.16.60.213
               port: 3306
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment: 
3 rows in set (0.000 sec)

ERROR: No query specified

admin> 

如上修改后,加载到RUNTIME,并保存到disk
admin> load mysql servers to runtime;
Query OK, 0 rows affected (0.005 sec)

admin> save mysql servers to disk;   
Query OK, 0 rows affected (0.468 sec)

4)监控后端MySQL节点

如上添加Mysql节点之后,还需要监控这些后端节点。对于后端是主从复制的环境来说,这是必须的,因为ProxySQL需要通过每个节点的read_only值来自动调整
它们是属于读组还是写组。

首先在后端master主数据节点上创建一个用于监控的用户名(只需在master上创建即可,因为会复制到slave上),这个用户名只需具有USAGE权限即可。如果还需
要监控复制结构中slave是否严重延迟于master(这个俗语叫做"拖后腿",术语叫做"replication lag"),则还需具备replication client权限。

即在mysql-master主数据库节点行执行:
mysql> create user monitor@'172.16.60.%' identified by 'P@ssword1!'; 
Query OK, 0 rows affected (0.08 sec)

mysql> grant replication client on *.* to monitor@'172.16.60.%';
Query OK, 0 rows affected (0.07 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.07 sec)

然后回到mysql-proxy代理层节点上配置监控
admin> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.001 sec)

admin> set mysql-monitor_password='P@ssword1!';
Query OK, 1 row affected (0.000 sec)

修改后,加载到RUNTIME,并保存到disk
admin> load mysql variables to runtime;
Query OK, 0 rows affected (0.001 sec)

admin> save mysql variables to disk;
Query OK, 96 rows affected (0.180 sec)

验证监控结果:ProxySQL监控模块的指标都保存在monitor库的log表中。

以下是连接是否正常的监控(对connect指标的监控):(在前面可能会有很多connect_error,这是因为没有配置监控信息时的错误,
配置后如果connect_error的结果为NULL则表示正常)
admin> select * from mysql_server_connect_log;
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
| hostname      | port | time_start_us    | connect_success_time_us | connect_error                                                          |
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
| 172.16.60.213 | 3306 | 1547436747566683 | 0                       | Access denied for user 'monitor'@'docker-node2' (using password: YES)  |
| 172.16.60.211 | 3306 | 1547436748167350 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.212 | 3306 | 1547436748767837 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.212 | 3306 | 1547436807566915 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.211 | 3306 | 1547436808112262 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.213 | 3306 | 1547436808657603 | 0                       | Access denied for user 'monitor'@'docker-node2' (using password: YES)  |
| 172.16.60.213 | 3306 | 1547436867566829 | 0                       | Access denied for user 'monitor'@'docker-node2' (using password: YES)  |
| 172.16.60.211 | 3306 | 1547436868151063 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.212 | 3306 | 1547436868735236 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.212 | 3306 | 1547436927567060 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.211 | 3306 | 1547436928247532 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.213 | 3306 | 1547436928927772 | 0                       | Access denied for user 'monitor'@'docker-node2' (using password: YES)  |
| 172.16.60.211 | 3306 | 1547436987567058 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.212 | 3306 | 1547436988049160 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.213 | 3306 | 1547436988531003 | 0                       | Access denied for user 'monitor'@'docker-node2' (using password: YES)  |
| 172.16.60.212 | 3306 | 1547437047567194 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.213 | 3306 | 1547437048132480 | 0                       | Access denied for user 'monitor'@'docker-node2' (using password: YES)  |
| 172.16.60.211 | 3306 | 1547437048697765 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.211 | 3306 | 1547437107567258 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.212 | 3306 | 1547437108310411 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.213 | 3306 | 1547437109053480 | 0                       | Access denied for user 'monitor'@'docker-node2' (using password: YES)  |
| 172.16.60.213 | 3306 | 1547437167567303 | 0                       | Access denied for user 'monitor'@'docker-node2' (using password: YES)  |
| 172.16.60.211 | 3306 | 1547437168158076 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.212 | 3306 | 1547437168748763 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.213 | 3306 | 1547437227567525 | 0                       | Access denied for user 'monitor'@'docker-node2' (using password: YES)  |
| 172.16.60.211 | 3306 | 1547437228288921 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.212 | 3306 | 1547437229010153 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.211 | 3306 | 1547437287567412 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.212 | 3306 | 1547437288343609 | 0                       | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.213 | 3306 | 1547437289119851 | 0                       | Access denied for user 'monitor'@'docker-node2' (using password: YES)  |
| 172.16.60.213 | 3306 | 1547437295397678 | 1388                    | NULL                                                                   |
| 172.16.60.211 | 3306 | 1547437295826682 | 1377                    | NULL                                                                   |
| 172.16.60.212 | 3306 | 1547437296255659 | 8072                    | NULL                                                                   |
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
33 rows in set (0.001 sec)

以下是对心跳信息的监控(对ping指标的监控)
admin> select * from mysql_server_ping_log;
+---------------+------+------------------+----------------------+------------------------------------------------------------------------+
| hostname      | port | time_start_us    | ping_success_time_us | ping_error                                                             |
+---------------+------+------------------+----------------------+------------------------------------------------------------------------+
| 172.16.60.213 | 3306 | 1547436798804839 | 0                    | Access denied for user 'monitor'@'172.16.60.213' (using password: YES)  |
| 172.16.60.211 | 3306 | 1547436798875555 | 0                    | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.212 | 3306 | 1547436798946212 | 0                    | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.213 | 3306 | 1547436808804803 | 0                    | Access denied for user 'monitor'@'172.16.60.213' (using password: YES)  |
| 172.16.60.212 | 3306 | 1547436808931415 | 0                    | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
| 172.16.60.211 | 3306 | 1547436809058082 | 0                    | Access denied for user 'monitor'@'172.16.60.214' (using password: YES) |
....................
....................
| 172.16.60.213 | 3306 | 1547437295030407 | 335                  | NULL                                                                   |
| 172.16.60.211 | 3306 | 1547437295159540 | 218                  | NULL                                                                   |
| 172.16.60.212 | 3306 | 1547437295288638 | 172                  | NULL                                                                   |
| 172.16.60.211 | 3306 | 1547437365241682 | 337                  | NULL                                                                   |
| 172.16.60.211 | 3306 | 1547437375031403 | 435                  | NULL                                                                   |
..................
..................
| 172.16.60.213 | 3306 | 1547437395160777 | 469                  | NULL                                                                   |
| 172.16.60.211 | 3306 | 1547437395289919 | 377                  | NULL                                                                   |
+---------------+------+------------------+----------------------+------------------------------------------------------------------------+
183 rows in set (0.001 sec)

但是,read_only和replication_lag的监控日志都为空。
admin> select * from mysql_server_read_only_log;
Empty set (0.000 sec)

admin> select * from mysql_server_replication_lag_log;
Empty set (0.000 sec)

例如,指定写组的id为10,读组的id为20。
admin> insert into mysql_replication_hostgroups values(10,20,1);
Query OK, 1 row affected (0.000 sec)

在该配置加载到RUNTIME生效之前,先查看下各mysql server所在的组。
admin> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname      | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10           | 172.16.60.211 | 3306 | ONLINE | 1      |
| 10           | 172.16.60.212 | 3306 | ONLINE | 1      |
| 10           | 172.16.60.213 | 3306 | ONLINE | 1      |
+--------------+---------------+------+--------+--------+
3 rows in set (0.000 sec)

3个节点都在hostgroup_id=10的组中。
现在,将刚才mysql_replication_hostgroups表的修改加载到RUNTIME生效。
admin> load mysql servers to runtime;
Query OK, 0 rows affected (0.005 sec)

admin> save mysql servers to disk;
Query OK, 0 rows affected (0.406 sec)

一加载,Monitor模块就会开始监控后端的read_only值,当监控到read_only值后,就会按照read_only的值将某些节点自动移动到读/写组。
例如,此处所有节点都在id=10的写组,slave1和slave2都是slave,它们的read_only=1,这两个节点将会移动到id=20的组。
如果一开始这3节点都在id=20的读组,那么移动的将是Master节点,会移动到id=10的写组。

现在看结果
admin> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname      | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10           | 172.16.60.211 | 3306 | ONLINE | 1      |
| 20           | 172.16.60.212 | 3306 | ONLINE | 1      |
| 20           | 172.16.60.213 | 3306 | ONLINE | 1      |
+--------------+---------------+------+--------+--------+
3 rows in set (0.000 sec)

admin> select * from mysql_server_read_only_log;
+---------------+------+------------------+-----------------+-----------+-------+
| hostname      | port | time_start_us    | success_time_us | read_only | error |
+---------------+------+------------------+-----------------+-----------+-------+
| 172.16.60.212 | 3306 | 1547438982938183 | 694             | 1         | NULL  |
| 172.16.60.211 | 3306 | 1547438982950903 | 741             | 0         | NULL  |
..............
..............
| 172.16.60.211 | 3306 | 1547439054961006 | 517             | 0         | NULL  |
| 172.16.60.213 | 3306 | 1547439054977658 | 479             | 1         | NULL  |
+---------------+------+------------------+-----------------+-----------+-------+
147 rows in set (0.001 sec)

5)配置mysql_users

上面的所有配置都是关于后端MySQL节点的,现在可以配置关于SQL语句的,包括:发送SQL语句的用户、SQL语句的路由规则、SQL查询的缓存、
SQL语句的重写等等。本小节是SQL请求所使用的用户配置,例如root用户。这要求我们需要先在后端MySQL节点添加好相关用户。这里以root和
sqlsender两个用户名为例。

首先,在mysql-master主数据库节点上执行:(只需master执行即可,会复制给两个slave)
mysql> grant all on *.* to root@'172.16.60.%' identified by 'passwd';
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> grant all on *.* to sqlsender@'172.16.60.%' identified by 'P@ssword1!';
Query OK, 0 rows affected, 1 warning (0.15 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.06 sec)

然后回到mysql-proxy代理层节点,配置mysql_users表,将刚才的两个用户添加到该表中。
admin> insert into mysql_users(username,password,default_hostgroup) values('root','passwd',10);
Query OK, 1 row affected (0.001 sec)

admin> insert into mysql_users(username,password,default_hostgroup) values('sqlsender','P@ssword1!',10);
Query OK, 1 row affected (0.000 sec)

admin> load mysql users to runtime;
Query OK, 0 rows affected (0.001 sec)

admin> save mysql users to disk;
Query OK, 0 rows affected (0.108 sec)

mysql_users表有不少字段,最主要的三个字段为username、password和default_hostgroup:
-  username:前端连接ProxySQL,以及ProxySQL将SQL语句路由给MySQL所使用的用户名。
-  password:用户名对应的密码。可以是明文密码,也可以是hash密码。如果想使用hash密码,可以先在某个MySQL节点上执行
   select password(PASSWORD),然后将加密结果复制到该字段。
-  default_hostgroup:该用户名默认的路由目标。例如,指定root用户的该字段值为10时,则使用root用户发送的SQL语句默认
   情况下将路由到hostgroup_id=10组中的某个节点。

admin> select * from mysql_users\G
*************************** 1. row ***************************
              username: root
              password: passwd
                active: 1
               use_ssl: 0
     default_hostgroup: 10
        default_schema: NULL
         schema_locked: 0
transaction_persistent: 1
          fast_forward: 0
               backend: 1
              frontend: 1
       max_connections: 10000
*************************** 2. row ***************************
              username: sqlsender
              password: P@ssword1!
                active: 1
               use_ssl: 0
     default_hostgroup: 10
        default_schema: NULL
         schema_locked: 0
transaction_persistent: 1
          fast_forward: 0
               backend: 1
              frontend: 1
       max_connections: 10000
2 rows in set (0.000 sec)

虽然这里没有详细介绍mysql_users表,但上面标注了"注意本行"的两个字段必须要引起注意。只有active=1的用户才是有效的用户。
至于transaction_persistent字段,当它的值为1时,表示事务持久化:当某连接使用该用户开启了一个事务后,那么在事务提交/回滚之前,
所有的语句都路由到同一个组中,避免语句分散到不同组。在以前的版本中,默认值为0,不知道从哪个版本开始,它的默认值为1。
我们期望的值为1,所以在继续下面的步骤之前,先查看下这个值,如果为0,则执行下面的语句修改为1。

admin> update mysql_users set transaction_persistent=1 where username='root';
Query OK, 1 row affected (0.000 sec)

admin> update mysql_users set transaction_persistent=1 where username='sqlsender';
Query OK, 1 row affected (0.000 sec)

admin> load mysql users to runtime;
Query OK, 0 rows affected (0.001 sec)

admin> save mysql users to disk;
Query OK, 0 rows affected (0.098 sec)

然后,另开一个终端,分别使用root用户和sqlsender用户测试下它们是否能路由到默认的hostgroup_id=10(它是一个写组)读、写数据。
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@server_id"            #这是通过转发端口6033连接的,连接的是转发到后端真正的数据库!
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "create database proxy_test"
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kevin              |
| mysql              |
| performance_schema |
| proxy_test         |
| sys                |
+--------------------+

[root@mysql-proxy ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'use proxy_test;create table t(id int);' 
[root@mysql-proxy ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'show tables from proxy_test;'
+----------------------+
| Tables_in_proxy_test |
+----------------------+
| t                    |
+----------------------+

6)读写分离:配置路由规则

ProxySQL的路由规则非常灵活,可以基于用户、基于schema以及基于每个语句实现路由规则的定制。本案例作为一个入门配置,实现一个最简单的语句级路由规则,从而实现读写分离。
必须注意,这只是实验,实际的路由规则绝不应该仅根据所谓的读、写操作进行分离,而是从各项指标中找出压力大、执行频繁的语句单独写规则、做缓存等等。和查询规则有关的表有两个:
mysql_query_rules和mysql_query_rules_fast_routing,后者是前者的扩展表,1.4.7之后才支持该快速路由表。本案例只介绍第一个表。插入两个规则,目的是将select语句分离到
hostgroup_id=20的读组,但由于select语句中有一个特殊语句SELECT...FOR UPDATE它会申请写锁,所以应该路由到hostgroup_id=10的写组。

admin> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1), (2,1,'^SELECT',20,1);
Query OK, 2 rows affected (0.001 sec)
  
admin> load mysql query rules to runtime;
Query OK, 0 rows affected (0.000 sec)
  
admin> save mysql query rules to disk;
Query OK, 0 rows affected (0.179 sec)
  
需要注意: select ... for update规则的rule_id必须要小于普通的select规则的rule_id,因为ProxySQL是根据rule_id的顺序进行规则匹配的。
  
再来测试下,读操作是否路由给了hostgroup_id=20的读组。
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select @@server_id'
+-------------+
| @@server_id |
+-------------+
|           3 |
+-------------+
  
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select @@server_id'
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+
  
读操作已经路由给读组,再看看写操作。这里以事务持久化进行测试。
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'start transaction;select @@server_id;commit;select @@server_id;'
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
+-------------+
| @@server_id |
+-------------+
|           3 |
+-------------+
  
显然,一切都按照预期进行。最后,如果想查看路由的信息,可查询stats库中的stats_mysql_query_digest表。
以下是该表的一个输出格式示例(和本案例无关)。
admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+----------------------------------+
| hg | sum_time | count_star | digest_text                      |
+----+----------+------------+----------------------------------+
| 10 | 236771   | 1          | create table t(id int)           |
| 10 | 53767    | 1          | create database proxy_test       |
| 20 | 10344    | 1          | select @@server_id               |
| 20 | 3350     | 3          | select @@server_id               |
| 10 | 2469     | 2          | SELECT DATABASE()                |
| 10 | 1678     | 2          | select @@server_id               |
| 10 | 1193     | 1          | show databases                   |
| 10 | 1191     | 1          | start transaction                |
| 10 | 815      | 1          | show tables from proxy_test      |
| 10 | 289      | 1          | reate table t(id int)            |
| 10 | 162      | 1          | commit                           |
| 10 | 0        | 6          | select @@version_comment limit ? |
| 10 | 0        | 4          | select @@version_comment limit ? |
| 10 | 0        | 1          | select @@version_comment limit ? |
+----+----------+------------+----------------------------------+
14 rows in set (0.002 sec)

7)最后测试通过proxysql插件进行读写分离

mysql-master主数据库查看
mysql> select * from kevin.haha;
+----+-----------+
| id | name      |
+----+-----------+
|  5 | hefei     |
|  8 | beijing   |
| 10 | xihu      |
| 11 | changping |
| 12 | wangjing  |
+----+-----------+
5 rows in set (0.00 sec)
  
mysql-slave1和mysql-slave2两个从数据库查看
mysql> select * from kevin.haha;
+----+-----------+
| id | name      |
+----+-----------+
|  5 | hefei     |
|  8 | beijing   |
| 10 | xihu      |
| 11 | changping |
| 12 | wangjing  |
+----+-----------+
5 rows in set (0.00 sec)
  
在mysql-proxy代理层节点,通过proxysql进行数据写入,并查看
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'insert into kevin.haha values(21,"zhongguo"),(22,"xianggang"),(23,"taiwan");'
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'delete from kevin.haha where id=10' 
 
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select * from kevin.haha;'                                          
+----+-----------+
| id | name      |
+----+-----------+
|  5 | hefei     |
|  8 | beijing   |
| 11 | changping |
| 12 | wangjing  |
| 21 | zhongguo  |
| 22 | xianggang |
| 23 | taiwan    |
 
在mysql-master主数据库节点查看
mysql> select * from kevin.haha;
+----+-----------+
| id | name      |
+----+-----------+
|  5 | hefei     |
|  8 | beijing   |
| 11 | changping |
| 12 | wangjing  |
| 21 | zhongguo  |
| 22 | xianggang |
| 23 | taiwan    |
+----+-----------+
7 rows in set (0.00 sec)
 
在mysql-slave1和mysql-slave2从数据库节点查看
mysql> select * from kevin.haha;
+----+-----------+
| id | name      |
+----+-----------+
|  5 | hefei     |
|  8 | beijing   |
| 11 | changping |
| 12 | wangjing  |
| 21 | zhongguo  |
| 22 | xianggang |
| 23 | taiwan    |
+----+-----------+
7 rows in set (0.00 sec)
 
发现在客户端通过proxysql插件更新的数据,已经写到mysql-master主数据库上,并同步到mysql-slave1和mysql-slave2两个从数据库上了!

==========================需要注意=======================
上面测试的客户机是在mysql-proxy本机,所以用127.0.0.1.
如果是在别的客户机上,那么连接命令中就用proxysql的地址,即"mysql -uroot -ppasswd -P6033 -h172.16.60.214"

比如在mysql-slave2机器上连接proxysql插件:
[root@mysql-slave2 ~]# mysql -uroot -ppasswd -P6033 -h172.16.60.214
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kevin              |
| mysql              |
| performance_schema |
| proxy_test         |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

删除上面创建的proxy_test测试库
mysql> drop database proxy_test;
Query OK, 1 row affected (0.16 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kevin              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> select * from kevin.haha;
+----+-----------+
| id | name      |
+----+-----------+
|  5 | hefei     |
|  8 | beijing   |
| 11 | changping |
| 12 | wangjing  |
| 21 | zhongguo  |
| 22 | xianggang |
| 23 | taiwan    |
+----+-----------+
7 rows in set (0.00 sec)

至此,MySQL5.7基于GTID主从复制+并行复制+半同步复制+读写分离的环境部署工作已完成。 

posted @ 2016-06-08 12:12  散尽浮华  阅读(8911)  评论(2编辑  收藏  举报