CentOS 7.5 部署 MySQL 5.7 基于GTID主从复制+并行复制+半同步复制+读写分离(ProxySQL) 环境- 运维笔记 (完整版)
之前已经详细介绍了Mysql基于GTID主从复制的概念，原理和配置，下面整体记录下MySQL5.7基于GTID主从复制+并行复制+增强半同步复制+读写分离环境的实现过程，以便加深对mysql新特性GTID复制的理解和实际业务场景中部署应用。 一、实验环境 [root@mysql-master~]#cat/etc/redhat-release CentOSLinuxrelease7.5.1804(Core) 为了方便实验，关闭所有节点的防火墙 [root@mysql-master~]#systemctlstopfirewalld [root@mysql-master~]#firewall-cmd--state notrunning [root@mysql-master~]#cat/etc/sysconfig/selinux|grep"SELINUX=disabled" SELINUX=disabled [root@mysql-master~]#setenforce0 setenforce:SELinuxisdisabled [root@mysql-master~]#getenforce Disabled 二、安装Mysql5.7 在三个mysql节点机上使用yum方式安装Mysql5.7，参考：https://www.cnblogs.com/kevingrace/p/8340690.html 安装MySQLyum资源库 [root@mysql-master~]#yumlocalinstallhttps://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm 安装MySQL5.7 [root@mysql-master~]#yuminstall-ymysql-community-server 启动MySQL服务器和MySQL的自动启动 [root@mysql-master~]#systemctlstartmysqld.service [root@mysql-master~]#systemctlenablemysqld.service 设置登录密码 由于MySQL从5.7开始不允许首次安装后使用空密码进行登录！为了加强安全性，系统会随机生成一个密码以供管理员首次登录使用， 这个密码记录在/var/log/mysqld.log文件中，使用下面的命令可以查看此密码： [root@mysql-master~]#cat/var/log/mysqld.log|grep'Atemporarypassword' 2019-01-11T05:53:17.824073Z1[Note]Atemporarypasswordisgeneratedforroot@localhost:TaN.k:*Qw2xs 使用上面查看的密码TaN.k:*Qw2xs登录mysql，并重置密码为123456 [root@mysql-master~]#mysql-p#输入默认的密码：TaN.k:*Qw2xs ............. mysql>setglobalvalidate_password_policy=0; QueryOK,0rowsaffected(0.00sec) mysql>setglobalvalidate_password_length=1; QueryOK,0rowsaffected(0.00sec) mysql>setpassword=password("123456"); QueryOK,0rowsaffected,1warning(0.00sec) mysql>flushprivileges; QueryOK,0rowsaffected(0.00sec) 查看mysql版本 [root@mysql-master~]#mysql-p123456 ........ mysql>selectversion(); +-----------+ |version()| +-----------+ |5.7.24| +-----------+ 1rowinset(0.00sec) ===================================================================== 温馨提示 mysql5.7通过上面默认安装后，执行语句可能会报错： ERROR1819(HY000):Yourpassworddoesnotsatisfythecurrentpolicyrequirements 这个报错与Mysql密码安全策略validate_password_policy的值有关，validate_password_policy可以取0、1、2三个值： 解决办法： setglobalvalidate_password_policy=0; setglobalvalidate_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 #relaylog skip_slave_start=1 配置后，记得重启mysql服务 [root@mysql-master~]#systemctlrestartmysqld 登录mysql，并查看master状态，发现多了一项"Executed_Gtid_Set" mysql>showmasterstatus; +-------------------+----------+--------------+------------------+-------------------+ |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set| +-------------------+----------+--------------+------------------+-------------------+ |mysql-bin.000001|154|||| +-------------------+----------+--------------+------------------+-------------------+ 1rowinset(0.00sec) mysql>showglobalvariableslike'%uuid%'; +---------------+--------------------------------------+ |Variable_name|Value| +---------------+--------------------------------------+ |server_uuid|317e2aad-1565-11e9-9c2e-005056ac6820| +---------------+--------------------------------------+ 1rowinset(0.00sec) 查看确认gtid功能打开 mysql>showglobalvariableslike'%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| +----------------------------------+-------+ 8rowsinset(0.00sec) 查看确认binlog日志功能打开 mysql>showvariableslike'log_bin'; +---------------+-------+ |Variable_name|Value| +---------------+-------+ |log_bin|ON| +---------------+-------+ 1rowinset(0.00sec) 授权slave复制用户，并刷新权限 mysql>grantreplicationslave,replicationclienton*.*toslave@'172.16.60.212'identifiedby"slave@123"; QueryOK,0rowsaffected,1warning(0.03sec) mysql>flushprivileges; QueryOK,0rowsaffected(0.04sec) mysql>showgrantsforslave@'172.16.60.212'; +-------------------------------------------------------------------------------+ |Grantsforslave@172.16.60.212| +-------------------------------------------------------------------------------+ |GRANTREPLICATIONSLAVE,REPLICATIONCLIENTON*.*TO'slave'@'172.16.60.212'| +-------------------------------------------------------------------------------+ 1rowinset(0.00sec) 再次查看master状态 mysql>showmasterstatus; +-------------------+----------+--------------+------------------+------------------------------------------+ |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set| +-------------------+----------+--------------+------------------+------------------------------------------+ |mysql-bin.000001|622|||317e2aad-1565-11e9-9c2e-005056ac6820:1-2| +-------------------+----------+--------------+------------------+------------------------------------------+ 1rowinset(0.00sec) 这里需要注意一下： 启动配置之前，同样需要对从服务器进行初始化。对从服务器初始化的方法基本和基于日志点是相同的，只不过在启动了GTID模式后， 在备份中所记录的就不是备份时的二进制日志文件名和偏移量了，而是记录的是备份时最后的GTID值。 需要先在主数据库机器上把目标库备份一下，假设这里目标库是kevin（为了测试效果，下面手动创建） mysql>showdatabases; +--------------------+ |Database| +--------------------+ |information_schema| |mysql| |performance_schema| |sys| +--------------------+ 4rowsinset(0.00sec) mysql>CREATEDATABASEkevinCHARACTERSETutf8COLLATEutf8_general_ci; QueryOK,1rowaffected(0.02sec) mysql>usekevin; Databasechanged mysql>createtableifnotexistshaha(idint(10)PRIMARYKEYAUTO_INCREMENT,namevarchar(50)NOTNULL); QueryOK,0rowsaffected(0.27sec) mysql>insertintokevin.hahavalues(1,"congcong"),(2,"huihui"),(3,"grace"); QueryOK,3rowsaffected(0.06sec) Records:3Duplicates:0Warnings:0 mysql>select*fromkevin.haha; +----+----------+ |id|name| +----+----------+ |1|congcong| |2|huihui| |3|grace| +----+----------+ 3rowsinset(0.00sec) 把kevin库备份出来 [root@mysql-master~]#mysqldump--single-transaction--master-data=2--triggers--routines--databaseskevin-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 --MySQLdump10.13Distrib5.7.24,forLinux(x86_64) -- --Host:localhostDatabase:kevin -------------------------------------------------------- --Serverversion5.7.24-log ............. ............. -- --GTIDstateatthebeginningofthebackup -- 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.sqlroot@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 #relaylog skip_slave_start=1 read_only=on 配置完成后，记得重启mysql服务 [root@mysql-slave1~]#systemctlrestartmysqld 接着将主数据库目标库的备份数据kevin.sql导入到从数据库里 [root@mysql-slave1~]#ls/root/kevin.sql /root/kevin.sql [root@mysql-slave1~]#mysql-p123456 ......... mysql>showdatabases; +--------------------+ |Database| +--------------------+ |information_schema| |mysql| |performance_schema| |sys| +--------------------+ 4rowsinset(0.00sec) mysql>source/root/kevin.sql; mysql>select*fromkevin.haha; +----+----------+ |id|name| +----+----------+ |1|congcong| |2|huihui| |3|grace| +----+----------+ 3rowsinset(0.00sec) 在从数据库里，使用changemaster配置主从复制 mysql>stopslave; QueryOK,0rowsaffected,1warning(0.00sec) mysql>changemastertomaster_host='172.16.60.211',master_user='slave',master_password='slave@123',master_auto_position=1; QueryOK,0rowsaffected,2warnings(0.26sec) mysql>startslave; QueryOK,0rowsaffected(0.02sec) mysql>showslavestatus\G; ***************************1.row*************************** Slave_IO_State:Waitingformastertosendevent 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>showmasterstatus; +-------------------+----------+--------------+------------------+------------------------------------------+ |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set| +-------------------+----------+--------------+------------------+------------------------------------------+ |mysql-bin.000001|1357|||317e2aad-1565-11e9-9c2e-005056ac6820:1-5| +-------------------+----------+--------------+------------------+------------------------------------------+ 1rowinset(0.00sec) mysql>showslavehosts; +-----------+------+------+-----------+--------------------------------------+ |Server_id|Host|Port|Master_id|Slave_UUID| +-----------+------+------+-----------+--------------------------------------+ |2||3306|1|2c1efc46-1565-11e9-ab8e-00505688047c| +-----------+------+------+-----------+--------------------------------------+ 1rowinset(0.00sec) mysql>insertintokevin.hahavalues(4,"beijing"),(5,"hefei"),(10,"xihu"); QueryOK,3rowsaffected(0.06sec) Records:3Duplicates:0Warnings:0 mysql>deletefromkevin.hahawhereid<4; QueryOK,3rowsaffected(0.10sec) mysql>select*fromkevin.haha; +----+---------+ |id|name| +----+---------+ |4|beijing| |5|hefei| |10|xihu| +----+---------+ 3rowsinset(0.00sec) 4）mysql-slave1从数据库上查看 mysql>select*fromkevin.haha; +----+---------+ |id|name| +----+---------+ |4|beijing| |5|hefei| |10|xihu| +----+---------+ 3rowsinset(0.00sec) 发现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可以有两个值：１）DATABASE 默认值，基于库的并行复制方式；２）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>SHOWBINLOGEVENTSin'mysql-bin.000003'; +------------------+-----+----------------+-----------+-------------+-----------------------------------------------+ |Log_name|Pos|Event_type|Server_id|End_log_pos|Info| +------------------+-----+----------------+-----------+-------------+-----------------------------------------------+ |mysql-bin.000003|4|Format_desc|88|123|Serverver:5.7.7-rc-debug-log,Binlogver: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:108flags:STMT_END_F| ...... 这意味着在 MySQL 5.7版本中即使不开启GTID，每个事务开始前也是会存在一个Anonymous_Gtid ，而这GTID中就存在着组提交的信息。 LOGICAL_CLOCK然而，通过上述的SHOW BINLOG EVENTS，并没有发现有关组提交的任何信息。但是通过mysqlbinlog工具，用户就能发现组提交的内部信息: 如下查看一个binlog日志 root@localhost:~#mysqlbinlogmysql-bin.0000002|greplast_committed #19011217:45:16serverid1end_log_pos219CRC320xca2ee8c2GTIDlast_committed=0sequence_number=1rbr_only=yes #19011217:45:21serverid1end_log_pos506CRC320xe8047dd2GTIDlast_committed=1sequence_number=2rbr_only=yes #19011217:45:16serverid1end_log_pos219CRC320xca2ee8c2GTIDlast_committed=2sequence_number=3rbr_only=yes #19011217:45:21serverid1end_log_pos506CRC320xe8047dd2GTIDlast_committed=3sequence_number=4rbr_only=yes 可以发现较之原来的二进制日志内容多了last_committed和sequence_number，last_committed表示事务提交的时候，上次事务提交的编号，如果事务具有相同的last_committed，表示这些事务都在一组内，可以进行并行的回放 (一般是当执行的sql语句并发数大的情况下会进行组提交)。上面这个binlog日志里没有组提交信息(last_committed数值都不相等)，下一个事物的last_committed永远都和上一个事物的sequence_number是相等的，这是因为事物是顺序提交的！这么理解起来并不奇怪。 下面看一下组提交模式的事物： root@localhost:~#mysqlbinlogmysql-bin.0000006|greplast_committed #15052014:23:11serverid88end_log_pos259CRC320x4ead9ad6GTIDlast_committed=0sequence_number=1 #15052014:23:11serverid88end_log_pos1483CRC320xdf94bc85GTIDlast_committed=0sequence_number=2 #15052014:23:11serverid88end_log_pos2708CRC320x0914697bGTIDlast_committed=0sequence_number=3 #15052014:23:11serverid88end_log_pos3934CRC320xd9cb4a43GTIDlast_committed=0sequence_number=4 #15052014:23:11serverid88end_log_pos5159CRC320x06a6f531GTIDlast_committed=0sequence_number=5 #15052014:23:11serverid88end_log_pos6386CRC320xd6cae930GTIDlast_committed=0sequence_number=6 #15052014:23:11serverid88end_log_pos7610CRC320xa1ea531cGTIDlast_committed=6sequence_number=7 #15052014:23:11serverid88end_log_pos8834CRC320x96864e6bGTIDlast_committed=6sequence_number=8 #15052014:23:11serverid88end_log_pos10057CRC320x2de1ae55GTIDlast_committed=6sequence_number=9 #15052014:23:11serverid88end_log_pos11280CRC320x5eb13091GTIDlast_committed=6sequence_number=10 #15052014:23:11serverid88end_log_pos12504CRC320x16721011GTIDlast_committed=6sequence_number=11 #15052014:23:11serverid88end_log_pos13727CRC320xe2210ab6GTIDlast_committed=6sequence_number=12 #15052014:23:11serverid88end_log_pos14952CRC320xf41181d3GTIDlast_committed=12sequence_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 #relaylog 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~]#systemctlrestartmysqld 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 #relaylog 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;都写入磁盘。这个刷新的是redolog即ib_logfile0，而不是binlog 配置完成后，记得重启mysql服务 [root@mysql-master~]#systemctlrestartmysqld 登录mysql，查看binlog_group_commit mysql>showvariableslike'binlog_group_commit_%'; +-----------------------------------------+-------+ |Variable_name|Value| +-----------------------------------------+-------+ |binlog_group_commit_sync_delay|100| |binlog_group_commit_sync_no_delay_count|10| +-----------------------------------------+-------+ 2rowsinset(0.00sec) 设置binlog_group_commit的上面两个参数，否则从库无法做到基于事物的并行复制!这两个参数共同决定了是否触发组提交操作！ 第二个参数表示该事务组提交之前总共等待累积到多少个事务(如上要累计到10个事务)； 第一个参数则表示该事务组总共等待多长时间后进行提交(如上要总共等待100毫秒的时间)，任何一个条件满足则进行后续操作。 因为有这个等待，可以让更多事务的binlog通过一次写binlog文件磁盘来完成提交，从而获得更高的吞吐量。 3）登录mysql-slave1从数据库 上面在配置并行复制后，主从数据库的mysqld服务都重启了，此时需要重启从数据库上的slave，这样才能恢复正常的主从同步状态！ 记住：只要主数据库的mysqld服务重启，那么从数据库上就要重启slave，以恢复主从同步状态！！！ mysql>showslavestatus\G; ........... ........... Slave_IO_Running:No Slave_SQL_Running:No mysql>stopslave; QueryOK,0rowsaffected,1warning(0.00sec) mysql>startslave; QueryOK,0rowsaffected(0.10sec) mysql>showslavestatus\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>usemysql;#切入到mysql库 Databasechanged mysql>select*fromslave_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||| +----+---------------------------------+---------------+-------------------+----------------+---------------------------------+--------------------------+----------------------------+---------------------------+------------------+-----------------------+------------------------------------------------------------------+--------------+ 4rowsinset(0.00sec) 以上可以查看到，mysql库下的slave_worker_info表下查看到4个线程 也可以使用下面命令查看线程数，这个跟在my.cnf文件里配置的是一样的！ mysql>showvariableslike'%slave_para%'; +------------------------+---------------+ |Variable_name|Value| +------------------------+---------------+ |slave_parallel_type|LOGICAL_CLOCK| |slave_parallel_workers|4| +------------------------+---------------+ 2rowsinset(0.00sec) 4）同步复制测试 在mysql-master主数据库插入新数据 mysql>insertintokevin.hahavalues(21,"caiyi"),(22,"manman"),(23,"titi"); QueryOK,3rowsaffected(0.09sec) Records:3Duplicates:0Warnings:0 mysql>select*fromkevin.haha; +----+---------+ |id|name| +----+---------+ |4|beijing| |5|hefei| |10|xihu| |21|caiyi| |22|manman| |23|titi| +----+---------+ 6rowsinset(0.00sec) 然后在mysql-slave1从数据库查看，发现新数据已经同步过来了 mysql>select*fromkevin.haha; +----+---------+ |id|name| +----+---------+ |4|beijing| |5|hefei| |10|xihu| |21|caiyi| |22|manman| |23|titi| +----+---------+ 6rowsinset(0.00sec) 最后在mysql-master主数据库上查看组提交信息 [root@mysql-master~]#cd/var/lib/mysql [root@mysql-mastermysql]#llmysql-bin* -rw-r-----1mysqlmysql751Jan1218:08mysql-bin.000001 -rw-r-----1mysqlmysql1365Jan1219:28mysql-bin.000002 -rw-r-----1mysqlmysql1326Jan1219:42mysql-bin.000003 -rw-r-----1mysqlmysql57Jan1219:28mysql-bin.index [root@mysql-mastermysql]#mysqlbinlogmysql-bin.000003|greplast_committed #19011219:37:06serverid1end_log_pos259CRC320x893c0ae8GTIDlast_committed=0sequence_number=1rbr_only=yes #19011219:37:12serverid1end_log_pos542CRC320xc36def61GTIDlast_committed=1sequence_number=2rbr_only=yes #19011219:42:42serverid1end_log_pos825CRC320x361701a2GTIDlast_committed=2sequence_number=3rbr_only=yes #19011219:42:48serverid1end_log_pos1108CRC320x8ba858c7GTIDlast_committed=3sequence_number=4rbr_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 #relaylog 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;都写入磁盘。这个刷新的是redolog即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~]#systemctlrestartmysqld 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 #relaylog 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~]#systemctlrestartmysqld 3）在mysql-master主数据库上加载(前提是/usr/lib64/mysql/plugin/semisync_master.so文件存在。一般mysql安装后就默认产生) mysql>INSTALLPLUGINrpl_semi_sync_masterSONAME'semisync_master.so'; QueryOK,0rowsaffected(0.25sec) 查看插件是否加载成功 mysql>SELECTPLUGIN_NAME,PLUGIN_STATUSFROMINFORMATION_SCHEMA.PLUGINSWHEREPLUGIN_NAMELIKE'%semi%'; +----------------------+---------------+ |PLUGIN_NAME|PLUGIN_STATUS| +----------------------+---------------+ |rpl_semi_sync_master|ACTIVE| +----------------------+---------------+ 1rowinset(0.00sec) 查看半同步是否在运行 mysql>showstatuslike'Rpl_semi_sync_master_status'; +-----------------------------+-------+ |Variable_name|Value| +-----------------------------+-------+ |Rpl_semi_sync_master_status|ON| +-----------------------------+-------+ 1rowinset(0.00sec) 4）在mysql-slave1从数据库上加载(前提是/usr/lib64/mysql/plugin/semisync_slave.so文件存在。一般mysql安装后就默认产生) mysql>INSTALLPLUGINrpl_semi_sync_slaveSONAME'semisync_slave.so'; QueryOK,0rowsaffected(0.03sec) 查看插件是否加载成功 mysql>SELECTPLUGIN_NAME,PLUGIN_STATUSFROMINFORMATION_SCHEMA.PLUGINSWHEREPLUGIN_NAMELIKE'%semi%'; +---------------------+---------------+ |PLUGIN_NAME|PLUGIN_STATUS| +---------------------+---------------+ |rpl_semi_sync_slave|ACTIVE| +---------------------+---------------+ 1rowinset(0.00sec) 这个时候，查看mysql-slave1的半同步是否运行 mysql>showstatuslike'Rpl_semi_sync_slave_status'; +----------------------------+-------+ |Variable_name|Value| +----------------------------+-------+ |Rpl_semi_sync_slave_status|OFF| +----------------------------+-------+ 1rowinset(0.01sec) 发现还是OFF，明明已经在my.cnf文件里开启了！ 这是因为此时还没有生效，必须从数据库上的IO线程才能生产！！！ mysql>STOPSLAVEIO_THREAD; QueryOK,0rowsaffected,1warning(0.00sec) mysql>STARTSLAVEIO_THREAD; QueryOK,0rowsaffected(0.00sec) 然后再查看mysql-slave1的半同步状态，发现就已经开启了！ mysql>showstatuslike'Rpl_semi_sync_slave_status'; +----------------------------+-------+ |Variable_name|Value| +----------------------------+-------+ |Rpl_semi_sync_slave_status|ON| +----------------------------+-------+ 1rowinset(0.00sec) 查看此时的主从同步状态 mysql>showslavestatus\G; ........... ........... Slave_IO_Running:Yes Slave_SQL_Running:No 发现主从同步异常，这个时候再重启下slave即可！ mysql>stopslave; QueryOK,0rowsaffected(0.06sec) mysql>startslave; QueryOK,0rowsaffected(0.10sec) mysql>showslavestatus\G; ............ ............ Slave_IO_Running:Yes Slave_SQL_Running:Yes 5)在mysql-master主数据库上查看 mysql>showstatuslike'%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| +--------------------------------------------+-------+ 14rowsinset(0.00sec) 从上面信息，发现Rpl_semi_sync_master_clients的数值为1，说明此时mysql-master主数据库已经有一个半同步复制的从机，即mysql-slave1节点。 Rpl_semi_sync_master_yes_tx的数值为0，说明此时还没有半同步复制的sql语句被执行。 接着在mysql-master主数据库上插入和更新数据，测试半同步复制 mysql>select*fromkevin.haha; +----+---------+ |id|name| +----+---------+ |4|beijing| |5|hefei| |10|xihu| |21|caiyi| |22|manman| |23|titi| +----+---------+ 6rowsinset(0.01sec) mysql>insertintokevin.hahavalues(30,"shikui"),(31,"shibo"),(32,"shijuan"); QueryOK,3rowsaffected(0.15sec) Records:3Duplicates:0Warnings:0 mysql>updatekevin.hahasetid=8wherename="beijing"; QueryOK,1rowaffected(0.22sec) Rowsmatched:1Changed:1Warnings:0 接着再去mysql-slave1从数据库上查看，发现新数据已经同步过来了 mysql>select*fromkevin.haha; +----+---------+ |id|name| +----+---------+ |5|hefei| |8|beijing| |10|xihu| |21|caiyi| |22|manman| |23|titi| |30|shikui| |31|shibo| |32|shijuan| +----+---------+ 9rowsinset(0.00sec) 接着再去mysql-master主数据库上查看，如下： mysql>showstatuslike'%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| +--------------------------------------------+--------+ 14rowsinset(0.01sec) 从上面信息可发现Rpl_semi_sync_master_yes_tx的数值为2，即发生了两条半同步复制的sql语句，就是上面执行的那两条(insert和update)sql语句。 以上都是在mysql-master主数据库和mysql-slave1从数据库之间实现的基于GTID的主从复制、并行复制、半同步复制，即"一主一从"架构。 现在再把mysql-slave2的从节点添加进去，调整为"一主两从"的同步架构。添加操作记录如下: 1）在mysql-master主数据库上操作 先添加同步权限 mysql>grantreplicationslave,replicationclienton*.*toslave@'172.16.60.213'identifiedby"slave@123"; QueryOK,0rowsaffected,1warning(0.14sec) mysql>flushprivileges; QueryOK,0rowsaffected(0.14sec) 备份主库 [root@mysql-master~]#mysqldump-uroot-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.sqlroot@172.16.60.213:/root/ 记录当前的gtid mysql>showglobalvariableslike'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| +----------------------------------+-------------------------------------------+ 5rowsinset(0.00sec) 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 #relaylog 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~]#systemctlrestartmysqld 恢复备份库 [root@mysql-slave2~]#ll/root/213_slave.sql -rw-r--r--1rootroot805694Jan1300:26/root/213_slave.sql mysql>showdatabases; +--------------------+ |Database| +--------------------+ |information_schema| |mysql| |performance_schema| |sys| +--------------------+ 4rowsinset(0.00sec) mysql>source/root/213_slave.sql; mysql>select*fromkevin.haha; +----+---------+ |id|name| +----+---------+ |5|hefei| |8|beijing| |10|xihu| |21|caiyi| |22|manman| |23|titi| |30|shikui| |31|shibo| |32|shijuan| +----+---------+ 9rowsinset(0.00sec) 检查一下mysql-slave2从数据库上当前的gtid mysql>showglobalvariableslike'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| +----------------------------------+-------------------------------------------+ 5rowsinset(0.01sec) 由于是从master-master主库备份恢复过来的，所以此时mysql-slave2从数据库的gtid和主库的gtid是一样的！ 接着进行主从同步 mysql>stopslave; QueryOK,0rowsaffected,1warning(0.00sec) mysql>changemastertomaster_host='172.16.60.211',master_user='slave',master_password='slave@123',master_auto_position=1; QueryOK,0rowsaffected,2warnings(0.18sec) mysql>startslave; QueryOK,0rowsaffected(0.17sec) ================================================================= 如果遇到报错： ERROR1872(HY000):Slavefailedtoinitializerelayloginfostructurefromtherepository 解决办法： stopslave; resetslave; startslave; ================================================================= 查看，发现主从同步正常 mysql>showslavestatus\G; ......... Slave_IO_Running:Yes Slave_SQL_Running:Yes 并行复制查看 mysql>showvariableslike'%slave_para%'; +------------------------+---------------+ |Variable_name|Value| +------------------------+---------------+ |slave_parallel_type|LOGICAL_CLOCK| |slave_parallel_workers|4| +------------------------+---------------+ 2rowsinset(0.01sec) mysql>select*frommysql.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||| +----+----------------+---------------+-----------------+----------------+---------------------------+--------------------------+----------------------------+---------------------------+------------------+-----------------------+------------------------------------------------------------------+--------------+ 4rowsinset(0.00sec) 接着在mysql-slave2从数据库上开启半同步 mysql>INSTALLPLUGINrpl_semi_sync_slaveSONAME'semisync_slave.so'; QueryOK,0rowsaffected(0.03sec) mysql>SELECTPLUGIN_NAME,PLUGIN_STATUSFROMINFORMATION_SCHEMA.PLUGINSWHEREPLUGIN_NAMELIKE'%semi%'; +---------------------+---------------+ |PLUGIN_NAME|PLUGIN_STATUS| +---------------------+---------------+ |rpl_semi_sync_slave|ACTIVE| +---------------------+---------------+ 1rowinset(0.00sec) mysql>STOPSLAVEIO_THREAD; QueryOK,0rowsaffected(0.02sec) mysql>STARTSLAVEIO_THREAD; QueryOK,0rowsaffected(0.00sec) mysql>showstatuslike'Rpl_semi_sync_slave_status'; +----------------------------+-------+ |Variable_name|Value| +----------------------------+-------+ |Rpl_semi_sync_slave_status|ON| +----------------------------+-------+ 1rowinset(0.00sec) 3）现在回到mysql-master主数据库查看 mysql>showslavehosts; +-----------+------+------+-----------+--------------------------------------+ |Server_id|Host|Port|Master_id|Slave_UUID| +-----------+------+------+-----------+--------------------------------------+ |3||3306|1|2658b203-1565-11e9-9f8b-005056880888| |2||3306|1|2c1efc46-1565-11e9-ab8e-00505688047c| +-----------+------+------+-----------+--------------------------------------+ 2rowsinset(0.00sec) 如上信息可知mysql-master主数据库现在有两个从数据库，分别为mysql-slave1和mysql-slave2 mysql>showstatuslike'%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| +--------------------------------------------+--------+ 14rowsinset(0.00sec) 如上信息可知，mysql-master主数据库现在有两个半同步复制的从库，即mysql-slave1和mysql-slave2 4）现在测试下同步效果 在mysql-master主数据库上更新数据 mysql>deletefromkevin.hahawhereid>10; QueryOK,6rowsaffected(0.20sec) mysql>insertintokevin.hahavalues(11,"changping"),(12,"wangjing"); QueryOK,2rowsaffected(0.38sec) Records:2Duplicates:0Warnings:0 在mysql-slave1从数据库查看，发现已经同步过来了 mysql>select*fromkevin.haha; +----+-----------+ |id|name| +----+-----------+ |5|hefei| |8|beijing| |10|xihu| |11|changping| |12|wangjing| +----+-----------+ 5rowsinset(0.00sec) 在mysql-slave2从数据库查看，发现已经同步过来了 mysql>select*fromkevin.haha; +----+-----------+ |id|name| +----+-----------+ |5|hefei| |8|beijing| |10|xihu| |11|changping| |12|wangjing| +----+-----------+ 5rowsinset(0.00sec) 到此，已经完成了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~]#yuminstall-yMariaDB-client ================================================= 如果遇到报错： Error:MariaDB-compatconflictswith1:mariadb-libs-5.5.60-1.el7_5.x86_64 Youcouldtryusing--skip-brokentoworkaroundtheproblem Youcouldtryrunning:rpm-Va--nofiles--nodigest 解决办法： [root@mysql-proxy~]#rpm-qa|grepmariadb* mariadb-libs-5.5.56-2.el7.x86_64 [root@mysql-proxy~]#rpm-emariadb-libs-5.5.56-2.el7.x86_64--nodeps [root@mysql-proxy~]#yuminstall-yMariaDB-client 2）安装ProxySQL （中文安装手册: https://github.com/malongshuai/proxysql/wiki） [root@mysql-proxy~]#vim/etc/yum.repos.d/proxysql.repo [proxysql_repo] name=ProxySQLYUMrepository 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-yinstallproxysql [root@mysql-proxy~]#proxysql--version ProxySQLversion1.4.13-15-g69d4207,codenameTruls 启动ProxySQL [root@mysql-proxy~]#chkconfigproxysqlon [root@mysql-proxy~]#systemctlstartproxysql [root@mysql-proxy~]#systemctlstatusproxysql 启动后会监听两个端口， 默认为6032和6033。6032端口是ProxySQL的管理端口，6033是ProxySQL对外提供服务的端口(即连接到转发后端的真正数据库的转发端口)。 [root@mysql-proxy~]#netstat-tunlp ActiveInternetconnections(onlyservers) ProtoRecv-QSend-QLocalAddressForeignAddressStatePID/Programname tcp000.0.0.0:60320.0.0.0:*LISTEN23940/proxysql tcp000.0.0.0:60330.0.0.0:*LISTEN23940/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的管理接口(admininterface),该接口的默认管理员用户和密码都是admin. 下面是通过管理端口6032去连接的(下面连接命令中后面的--prompt'admin'字段可以不加，也是可以登录进去的) [root@mysql-proxy~]#mysql-uadmin-padmin-P6032-h127.0.0.1--prompt'admin>' WelcometotheMariaDBmonitor.Commandsendwith;or\g. YourMySQLconnectionidis8 Serverversion:5.5.30(ProxySQLAdminModule) Copyright(c)2000,2018,Oracle,MariaDBCorporationAbandothers. Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement. admin>showdatabases; +-----+---------------+-------------------------------------+ |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| +-----+---------------+-------------------------------------+ 5rowsinset(0.001sec) ProxySQL提供了几个库，每个库都有各自的意义； -main内存配置数据库，表里存放后端db实例、用户验证、路由规则等信息。表名以runtime_开头的表示proxysql当前运行的配置内容， 不能通过dml语句修改，只能修改对应的不以runtime_开头的（在内存）里的表，然后LOAD使其生效，SAVE使其存到硬盘以供下次重启加载。 -disk是持久化到硬盘的配置，sqlite数据文件。 -stats是proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间等等。 -monitor库存储monitor模块收集的信息，主要是对后端db的健康/延迟检查。 主要注意main和monitor数据库中的表 admin>showtablesfrommain; +--------------------------------------------+ |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| +--------------------------------------------+ 20rowsinset(0.001sec) 几个重要字段含义： global_variables设置变量，包括监听的端口、管理账号等。 mysql_collations相关字符集和校验规则。 mysql_query_rules定义查询路由规则。 admin>showtablesfrommonitor; +------------------------------------+ |tables| +------------------------------------+ |mysql_server_connect_log| |mysql_server_group_replication_log| |mysql_server_ping_log| |mysql_server_read_only_log| |mysql_server_replication_lag_log| +------------------------------------+ 5rowsinset(0.000sec) runtime_开头的是运行时的配置，这些是不能修改的。要修改ProxySQL的配置，需要修改了非runtime_表，修改后必须执行LOAD...TORUNTIME 才能加载到RUNTIME生效，执行save...todisk才能将配置持久化保存到磁盘。 使用insert语句添加主机到mysql_servers表中，其中：hostgroup_id10表示写组，20表示读组。 admin>insertintomysql_servers(hostgroup_id,hostname,port)values(10,'172.16.60.211',3306); QueryOK,1rowaffected(0.001sec) admin>insertintomysql_servers(hostgroup_id,hostname,port)values(10,'172.16.60.212',3306); QueryOK,1rowaffected(0.000sec) admin>insertintomysql_servers(hostgroup_id,hostname,port)values(10,'172.16.60.213',3306); QueryOK,1rowaffected(0.000sec) 查看这3个节点是否插入成功，以及它们的状态。 admin>select*frommysql_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: 3rowsinset(0.000sec) ERROR:Noqueryspecified admin> 如上修改后，加载到RUNTIME，并保存到disk admin>loadmysqlserverstoruntime; QueryOK,0rowsaffected(0.005sec) admin>savemysqlserverstodisk; QueryOK,0rowsaffected(0.468sec) 4）监控后端MySQL节点 如上添加Mysql节点之后，还需要监控这些后端节点。对于后端是主从复制的环境来说，这是必须的，因为ProxySQL需要通过每个节点的read_only值来自动调整 它们是属于读组还是写组。 首先在后端master主数据节点上创建一个用于监控的用户名(只需在master上创建即可，因为会复制到slave上)，这个用户名只需具有USAGE权限即可。如果还需 要监控复制结构中slave是否严重延迟于master(这个俗语叫做"拖后腿"，术语叫做"replicationlag")，则还需具备replicationclient权限。 即在mysql-master主数据库节点行执行： mysql>createusermonitor@'172.16.60.%'identifiedby'P@ssword1!'; QueryOK,0rowsaffected(0.08sec) mysql>grantreplicationclienton*.*tomonitor@'172.16.60.%'; QueryOK,0rowsaffected(0.07sec) mysql>flushprivileges; QueryOK,0rowsaffected(0.07sec) 然后回到mysql-proxy代理层节点上配置监控 admin>setmysql-monitor_username='monitor'; QueryOK,1rowaffected(0.001sec) admin>setmysql-monitor_password='P@ssword1!'; QueryOK,1rowaffected(0.000sec) 修改后，加载到RUNTIME，并保存到disk admin>loadmysqlvariablestoruntime; QueryOK,0rowsaffected(0.001sec) admin>savemysqlvariablestodisk; QueryOK,96rowsaffected(0.180sec) 验证监控结果：ProxySQL监控模块的指标都保存在monitor库的log表中。 以下是连接是否正常的监控(对connect指标的监控)：(在前面可能会有很多connect_error，这是因为没有配置监控信息时的错误， 配置后如果connect_error的结果为NULL则表示正常) admin>select*frommysql_server_connect_log; +---------------+------+------------------+-------------------------+------------------------------------------------------------------------+ |hostname|port|time_start_us|connect_success_time_us|connect_error| +---------------+------+------------------+-------------------------+------------------------------------------------------------------------+ |172.16.60.213|3306|1547436747566683|0|Accessdeniedforuser'monitor'@'docker-node2'(usingpassword:YES)| |172.16.60.211|3306|1547436748167350|0|Accessdeniedforuser'monitor'@'172.16.60.214'(usingpassword:YES)| |172.16.60.212|3306|1547436748767837|0|Accessdeniedforuser'monitor'@'172.16.60.214'(usingpassword:YES)| |172.16.60.212|3306|1547436807566915|0|Accessdeniedforuser'monitor'@'172.16.60.214'(usingpassword:YES)| |172.16.60.211|3306|1547436808112262|0|Accessdeniedforuser'monitor'@'172.16.60.214'(usingpassword:YES)| |172.16.60.213|3306|1547436808657603|0|Accessdeniedforuser'monitor'@'docker-node2'(usingpassword:YES)| |172.16.60.213|3306|1547436867566829|0|Accessdeniedforuser'monitor'@'docker-node2'(usingpassword:YES)| |172.16.60.211|3306|1547436868151063|0|Accessdeniedforuser'monitor'@'172.16.60.214'(usingpassword:YES)| |172.16.60.212|3306|1547436868735236|0|Accessdeniedforuser'monitor'@'172.16.60.214'(usingpassword:YES)| |172.16.60.212|3306|1547436927567060|0|Accessdeniedforuser'monitor'@'172.16.60.214'(usingpassword:YES)| |172.16.60.211|3306|1547436928247532|0|Accessdeniedforuser'monitor'@'172.16.60.214'(usingpassword:YES)| |172.16.60.213|3306|1547436928927772|0|Accessdeniedforuser'monitor'@'docker-node2'(usingpassword:YES)| |172.16.60.211|3306|1547436987567058|0|Accessdeniedforuser'monitor'@'172.16.60.214'(usingpassword:YES)| |172.16.60.212|3306|1547436988049160|0|Accessdeniedforuser'monitor'@'172.16.60.214'(usingpassword:YES)| |172.16.60.213|3306|1547436988531003|0|Accessdeniedforuser'monitor'@'docker-node2'(usingpassword:YES)| |172.16.60.212|3306|1547437047567194|0|Accessdeniedforuser'monitor'@'172.16.60.214'(usingpassword:YES)| |172.16.60.213|3306|1547437048132480|0|Accessdeniedforuser'monitor'@'docker-node2'(usingpassword:YES)| |172.16.60.211|3306|1547437048697765|0|Accessdeniedforuser'monitor'@'172.16.60.214'(usingpassword:YES)| |172.16.60.211|3306|1547437107567258|0|Accessdeniedforuser'monitor'@'172.16.60.214'(usingpassword:YES)| |172.16.60.212|3306|1547437108310411|0|Accessdeniedforuser'monitor'@'172.16.60.214'(usingpassword:YES)| |172.16.60.213|3306|1547437109053480|0|Accessdeniedforuser'monitor'@'docker-node2'(usingpassword:YES)| |172.16.60.213|3306|1547437167567303|0|Accessdeniedforuser'monitor'@'docker-node2'(usingpassword:YES)| |172.16.60.211|3306|1547437168158076|0|Accessdeniedforuser'monitor'@'172.16.60.214'(usingpassword:YES)| |172.16.60.212|3306|1547437168748763|0|Accessdeniedforuser'monitor'@'172.16.60.214'(usingpassword:YES)| |172.16.60.213|3306|1547437227567525|0|Accessdeniedforuser'monitor'@'docker-node2'(usingpassword:YES)| |172.16.60.211|3306|1547437228288921|0|Accessdeniedforuser'monitor'@'172.16.60.214'(usingpassword:YES)| |172.16.60.212|3306|1547437229010153|0|Accessdeniedforuser'monitor'@'172.16.60.214'(usingpassword:YES)| |172.16.60.211|3306|1547437287567412|0|Accessdeniedforuser'monitor'@'172.16.60.214'(usingpassword:YES)| |172.16.60.212|3306|1547437288343609|0|Accessdeniedforuser'monitor'@'172.16.60.214'(usingpassword:YES)| |172.16.60.213|3306|1547437289119851|0|Accessdeniedforuser'monitor'@'docker-node2'(usingpassword: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| +---------------+------+------------------+-------------------------+------------------------------------------------------------------------+ 33rowsinset(0.001sec) 以下是对心跳信息的监控(对ping指标的监控) admin>select*frommysql_server_ping_log; +---------------+------+------------------+----------------------+------------------------------------------------------------------------+ |hostname|port|time_start_us|ping_success_time_us|ping_error| +---------------+------+------------------+----------------------+------------------------------------------------------------------------+ |172.16.60.213|3306|1547436798804839|0|Accessdeniedforuser'monitor'@'172.16.60.213'(usingpassword:YES)| |172.16.60.211|3306|1547436798875555|0|Accessdeniedforuser'monitor'@'172.16.60.214'(usingpassword:YES)| |172.16.60.212|3306|1547436798946212|0|Accessdeniedforuser'monitor'@'172.16.60.214'(usingpassword:YES)| |172.16.60.213|3306|1547436808804803|0|Accessdeniedforuser'monitor'@'172.16.60.213'(usingpassword:YES)| |172.16.60.212|3306|1547436808931415|0|Accessdeniedforuser'monitor'@'172.16.60.214'(usingpassword:YES)| |172.16.60.211|3306|1547436809058082|0|Accessdeniedforuser'monitor'@'172.16.60.214'(usingpassword: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| +---------------+------+------------------+----------------------+------------------------------------------------------------------------+ 183rowsinset(0.001sec) 但是，read_only和replication_lag的监控日志都为空。 admin>select*frommysql_server_read_only_log; Emptyset(0.000sec) admin>select*frommysql_server_replication_lag_log; Emptyset(0.000sec) 例如，指定写组的id为10，读组的id为20。 admin>insertintomysql_replication_hostgroupsvalues(10,20,1); QueryOK,1rowaffected(0.000sec) 在该配置加载到RUNTIME生效之前，先查看下各mysqlserver所在的组。 admin>selecthostgroup_id,hostname,port,status,weightfrommysql_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| +--------------+---------------+------+--------+--------+ 3rowsinset(0.000sec) 3个节点都在hostgroup_id=10的组中。 现在，将刚才mysql_replication_hostgroups表的修改加载到RUNTIME生效。 admin>loadmysqlserverstoruntime; QueryOK,0rowsaffected(0.005sec) admin>savemysqlserverstodisk; QueryOK,0rowsaffected(0.406sec) 一加载，Monitor模块就会开始监控后端的read_only值，当监控到read_only值后，就会按照read_only的值将某些节点自动移动到读/写组。 例如，此处所有节点都在id=10的写组，slave1和slave2都是slave，它们的read_only=1，这两个节点将会移动到id=20的组。 如果一开始这3节点都在id=20的读组，那么移动的将是Master节点，会移动到id=10的写组。 现在看结果 admin>selecthostgroup_id,hostname,port,status,weightfrommysql_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| +--------------+---------------+------+--------+--------+ 3rowsinset(0.000sec) admin>select*frommysql_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| +---------------+------+------------------+-----------------+-----------+-------+ 147rowsinset(0.001sec) 5）配置mysql_users 上面的所有配置都是关于后端MySQL节点的，现在可以配置关于SQL语句的，包括：发送SQL语句的用户、SQL语句的路由规则、SQL查询的缓存、 SQL语句的重写等等。本小节是SQL请求所使用的用户配置，例如root用户。这要求我们需要先在后端MySQL节点添加好相关用户。这里以root和 sqlsender两个用户名为例。 首先，在mysql-master主数据库节点上执行：(只需master执行即可，会复制给两个slave) mysql>grantallon*.*toroot@'172.16.60.%'identifiedby'passwd'; QueryOK,0rowsaffected,1warning(0.06sec) mysql>grantallon*.*tosqlsender@'172.16.60.%'identifiedby'P@ssword1!'; QueryOK,0rowsaffected,1warning(0.15sec) mysql>flushprivileges; QueryOK,0rowsaffected(0.06sec) 然后回到mysql-proxy代理层节点，配置mysql_users表，将刚才的两个用户添加到该表中。 admin>insertintomysql_users(username,password,default_hostgroup)values('root','passwd',10); QueryOK,1rowaffected(0.001sec) admin>insertintomysql_users(username,password,default_hostgroup)values('sqlsender','P@ssword1!',10); QueryOK,1rowaffected(0.000sec) admin>loadmysqluserstoruntime; QueryOK,0rowsaffected(0.001sec) admin>savemysqluserstodisk; QueryOK,0rowsaffected(0.108sec) mysql_users表有不少字段，最主要的三个字段为username、password和default_hostgroup： -username：前端连接ProxySQL，以及ProxySQL将SQL语句路由给MySQL所使用的用户名。 -password：用户名对应的密码。可以是明文密码，也可以是hash密码。如果想使用hash密码，可以先在某个MySQL节点上执行 selectpassword(PASSWORD)，然后将加密结果复制到该字段。 -default_hostgroup：该用户名默认的路由目标。例如，指定root用户的该字段值为10时，则使用root用户发送的SQL语句默认 情况下将路由到hostgroup_id=10组中的某个节点。 admin>select*frommysql_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 2rowsinset(0.000sec) 虽然这里没有详细介绍mysql_users表，但上面标注了"注意本行"的两个字段必须要引起注意。只有active=1的用户才是有效的用户。 至于transaction_persistent字段，当它的值为1时，表示事务持久化：当某连接使用该用户开启了一个事务后，那么在事务提交/回滚之前， 所有的语句都路由到同一个组中，避免语句分散到不同组。在以前的版本中，默认值为0，不知道从哪个版本开始，它的默认值为1。 我们期望的值为1，所以在继续下面的步骤之前，先查看下这个值，如果为0，则执行下面的语句修改为1。 admin>updatemysql_userssettransaction_persistent=1whereusername='root'; QueryOK,1rowaffected(0.000sec) admin>updatemysql_userssettransaction_persistent=1whereusername='sqlsender'; QueryOK,1rowaffected(0.000sec) admin>loadmysqluserstoruntime; QueryOK,0rowsaffected(0.001sec) admin>savemysqluserstodisk; QueryOK,0rowsaffected(0.098sec) 然后，另开一个终端，分别使用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"createdatabaseproxy_test" [root@mysql-proxy~]#mysql-uroot-ppasswd-P6033-h127.0.0.1-e"showdatabases;" +--------------------+ |Database| +--------------------+ |information_schema| |kevin| |mysql| |performance_schema| |proxy_test| |sys| +--------------------+ [root@mysql-proxy~]#mysql-usqlsender-pP@ssword1!-P6033-h127.0.0.1-e'useproxy_test;createtablet(idint);' [root@mysql-proxy~]#mysql-usqlsender-pP@ssword1!-P6033-h127.0.0.1-e'showtablesfromproxy_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...FORUPDATE它会申请写锁，所以应该路由到hostgroup_id=10的写组。 admin>insertintomysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)VALUES(1,1,'^SELECT.*FORUPDATE$',10,1),(2,1,'^SELECT',20,1); QueryOK,2rowsaffected(0.001sec) admin>loadmysqlqueryrulestoruntime; QueryOK,0rowsaffected(0.000sec) admin>savemysqlqueryrulestodisk; QueryOK,0rowsaffected(0.179sec) 需要注意：select...forupdate规则的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'starttransaction;select@@server_id;commit;select@@server_id;' +-------------+ |@@server_id| +-------------+ |1| +-------------+ +-------------+ |@@server_id| +-------------+ |3| +-------------+ 显然，一切都按照预期进行。最后，如果想查看路由的信息，可查询stats库中的stats_mysql_query_digest表。 以下是该表的一个输出格式示例(和本案例无关)。 admin>SELECThostgrouphg,sum_time,count_star,digest_textFROMstats_mysql_query_digestORDERBYsum_timeDESC; +----+----------+------------+----------------------------------+ |hg|sum_time|count_star|digest_text| +----+----------+------------+----------------------------------+ |10|236771|1|createtablet(idint)| |10|53767|1|createdatabaseproxy_test| |20|10344|1|select@@server_id| |20|3350|3|select@@server_id| |10|2469|2|SELECTDATABASE()| |10|1678|2|select@@server_id| |10|1193|1|showdatabases| |10|1191|1|starttransaction| |10|815|1|showtablesfromproxy_test| |10|289|1|reatetablet(idint)| |10|162|1|commit| |10|0|6|select@@version_commentlimit?| |10|0|4|select@@version_commentlimit?| |10|0|1|select@@version_commentlimit?| +----+----------+------------+----------------------------------+ 14rowsinset(0.002sec) 7）最后测试通过proxysql插件进行读写分离 mysql-master主数据库查看 mysql>select*fromkevin.haha; +----+-----------+ |id|name| +----+-----------+ |5|hefei| |8|beijing| |10|xihu| |11|changping| |12|wangjing| +----+-----------+ 5rowsinset(0.00sec) mysql-slave1和mysql-slave2两个从数据库查看 mysql>select*fromkevin.haha; +----+-----------+ |id|name| +----+-----------+ |5|hefei| |8|beijing| |10|xihu| |11|changping| |12|wangjing| +----+-----------+ 5rowsinset(0.00sec) 在mysql-proxy代理层节点，通过proxysql进行数据写入，并查看 [root@mysql-proxy~]#mysql-uroot-ppasswd-P6033-h127.0.0.1-e'insertintokevin.hahavalues(21,"zhongguo"),(22,"xianggang"),(23,"taiwan");' [root@mysql-proxy~]#mysql-uroot-ppasswd-P6033-h127.0.0.1-e'deletefromkevin.hahawhereid=10' [root@mysql-proxy~]#mysql-uroot-ppasswd-P6033-h127.0.0.1-e'select*fromkevin.haha;' +----+-----------+ |id|name| +----+-----------+ |5|hefei| |8|beijing| |11|changping| |12|wangjing| |21|zhongguo| |22|xianggang| |23|taiwan| 在mysql-master主数据库节点查看 mysql>select*fromkevin.haha; +----+-----------+ |id|name| +----+-----------+ |5|hefei| |8|beijing| |11|changping| |12|wangjing| |21|zhongguo| |22|xianggang| |23|taiwan| +----+-----------+ 7rowsinset(0.00sec) 在mysql-slave1和mysql-slave2从数据库节点查看 mysql>select*fromkevin.haha; +----+-----------+ |id|name| +----+-----------+ |5|hefei| |8|beijing| |11|changping| |12|wangjing| |21|zhongguo| |22|xianggang| |23|taiwan| +----+-----------+ 7rowsinset(0.00sec) 发现在客户端通过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]Usingapasswordonthecommandlineinterfacecanbeinsecure. WelcometotheMySQLmonitor.Commandsendwith;or\g. YourMySQLconnectionidis47 Serverversion:5.5.30(ProxySQL) Copyright(c)2000,2018,Oracleand/oritsaffiliates.Allrightsreserved. OracleisaregisteredtrademarkofOracleCorporationand/orits affiliates.Othernamesmaybetrademarksoftheirrespective owners. Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement. mysql>showdatabases; +--------------------+ |Database| +--------------------+ |information_schema| |kevin| |mysql| |performance_schema| |proxy_test| |sys| +--------------------+ 6rowsinset(0.00sec) 删除上面创建的proxy_test测试库 mysql>dropdatabaseproxy_test; QueryOK,1rowaffected(0.16sec) mysql>showdatabases; +--------------------+ |Database| +--------------------+ |information_schema| |kevin| |mysql| |performance_schema| |sys| +--------------------+ 5rowsinset(0.00sec) mysql>select*fromkevin.haha; +----+-----------+ |id|name| +----+-----------+ |5|hefei| |8|beijing| |11|changping| |12|wangjing| |21|zhongguo| |22|xianggang| |23|taiwan| +----+-----------+ 7rowsinset(0.00sec) 至此，MySQL5.7基于GTID主从复制+并行复制+半同步复制+读写分离的环境部署工作已完成。
