mysql5.7基于GTID搭建主从
一直在用mysql主从集群,却从来没有动手搭建过,主要是因为:
以往的mysql版本,基于binlog+position的复制机制,几乎很难手工完成主从切换,只能依靠复杂的MHA项目来搞定。
而在最新的mysql中,支持了全局的事务ID:GTID。有了它之后,mysql主从可以统一的基于binlog完成主从的数据同步,slave上传自己已经执行过的GTID SET给master,master即可将自己binlog中拥有而slave没有执行过的GITD SET同步给slave,整个对比和同步过程因为GTID有序的原因而变得自动化,高效化。
这里有一篇比较有代表性博客介绍GTID,推荐给大家阅读:《MySQL5.7杀手级新特性:GTID原理与实战》。
GTID概念理解
GTID是master产生的自增ID,每个事务唯一标识,它由SERVER_ID + 自增序号 构成。因此,不同mysql节点产生的GTID必然不同,因此在整个集群全局不会重复。
因为事务是顺序产生的,事务日志是顺序写入binlog文件的,并且每个事务由唯一的GITD标识一起写入在binlog的每一条记录中,所以每个binlog文件包含的GTID是一个连续区间,我称为一个GTID SET,这个区间是记录在每个binlog的头部的,方便高效的查找。因此,要根据GTID找到所属的binlog文件,只需要遍历每个binlog文件,根据其头部的GITD区间判断即可。(而在以往的mysql版本中,每一条事务日志中并没有像GTID这样的唯一标识,所以在binlog中查找一个事务是采用低效遍历binlog的方法,大家可以自己学习以往mysql的binlog查找方式。)
在master中,下一个事务的GTID是通过GTID_NEXT变量控制的,默认它是自动递增的。我们也可以强制更新它,这通常用于处理一些异常的场景,在此不做赘述,你总会碰到的,到时你对GTID的理解应该足以帮助你自行找到解决方案。
binlog是记录历史操作的日志文件,相当于一种增量的日志,当slave同步完成后,它实际上就失去了保存的价值。因此,binlog是会回收的,以便释放出磁盘空间。这是通过GTID_PURGED变量管理的,每当mysql对比较旧的binlog进行回收,该变量就会记录下删除哪些GTID SET。
这里需要提到另外一个变量,叫做GTID_EXECUTED,它记录的是mysql执行过的事务GTID SET。总体来说,如果一条事务被执行过,就会被记录在GTID_EXECUTED中,而事务的GTID实际上是GTID_NEXT生成的,随着事务执行的增多,GTID_EXECUTED不断变大,binlog文件越来越多,因此触发binlog回收,会将较老的binlog回收,被回收的GTID列表将作为GTID SET区间追加到变量GTID_PURGED中。
主从同步理解
master生成的binlog中每一条事务都有GTID标识,那么当binlog同步到slave后,slave先将它们保存到中继日志relay-log,然后再读取relay-log逐条的重放事务。
slave也有自己的GTID_NEXT,GTID_EXECUTED,GTID_PURGED变量。当slave重放relay-log时,GITD_NEXT总是赋值为relay-log中的事务GTID,它是master生成的。正因为这样,slave的GTID_EXECUTED集合中不断的增加的GTID SET也是master传来的GTID构成的,所以当slave同步完成后,其GTID_EXECUTED集合应该和master的GTID_EXECUTED一样。
在GTID的主从模式下,slave要求开启binlog,并且将重放relay-log产生的事务日志也写入binlog中。这样的意义是,当slave重放relay-log时,可以根据事务GTID去自己的binlog中确认这条事务是否已经执行过,避免因为各种异常造成重复的重放事务。另外,根据此前对GTID的理解可知,GTID模式的主从同步是基于binlog实现的,为了实现failover容灾,每个slave都应该开启binlog,这样当master宕机后,某个slave作为新master时,其他slave可以直接基于binlog+GTID从新master开始同步,这才是GTID简化主从同步&failover的真正意义。
那么GTID_PURGED对于slave又是什么呢?既然slave也在产生binlog,那么它也要定期的清理太旧的binlog,这与master没有任何关系,它有自己的周期,当它回收了自己的binlog后也会将删除的GTID SET区间更新到GTID_PURGED变量集合中去,仅此而已。
这里再提示一下,既然主从同步是基于binlog,那么被回收的binlog就没有办法同步给slave了,但是我们知道主从通常是非常实时的在同步的,所以binlog保留几十天是完全足够给故障转移期间的数据同步服务的。如果是一个运行了很久的主从集群,希望加入一个新的slave,那么正确做法应该是dump一份master的数据给slave,然后让slave从dump点之后的binlog开始同步,因为这段时期的binlog一定还没有被master清理。
搭建主从同步
废话不多说,原理已经搞清楚,下面就做一个最小化的主从搭建作为演示。
我在单机完成部署,一个master,两个slave。
yum安装mysql5.7
去官方的yum页面,找到属于你的yum版本:https://dev.mysql.com/downloads/repo/yum/。
下载后rpm 安装一下yum源,然后执行yum update更新一下,最后yum install mysql-server即可,正常的话会看到安装的版本是5.7。
准备目录
1 2 3 4 5 |
[liangdong@10-10-162-70 mysql]$ ll total 12 drwxrwxr-x 4 liangdong liangdong 4096 Sep 15 10:12 master drwxrwxr-x 5 liangdong liangdong 4096 Sep 15 10:12 slave drwxrwxr-x 5 liangdong liangdong 4096 Sep 15 10:07 standby |
配置master
1 2 3 4 5 |
[liangdong@10-10-162-70 master]$ ll total 12 drwxrwxr-x 2 liangdong liangdong 4096 Sep 15 10:12 data drwxrwxr-x 2 liangdong liangdong 4096 Sep 15 10:12 log -rw-rw-r-- 1 liangdong liangdong 304 Sep 15 09:42 my.cnf |
创建data目录,它用于保存mysql数据库。
创建log目录,它用于保存binlog。
my.cnf是master的配置:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
[liangdong@10-10-162-70 master]$ cat my.cnf [mysqld] server_id = 1 gtid_mode = ON enforce-gtid-consistency = ON log-bin = /home/liangdong/mysql/master/log/binlog binlog_format = row datadir = /home/liangdong/mysql/master/data pid-file = /home/liangdong/mysql/master/mysql.pid socket = /home/liangdong/mysql/master/mysql.sock port = 3306 |
- server_id:主从集群里每个mysql唯一标识,GTID第一部分就是server_id。
- gtid_mode:开启GTID模式,每个事务有唯一ID标识。
- enforce-gtid-consistency:用于禁用对GTID模式不安全的事务操作,总是配置即可。
- log-bin:记录binlog的路径,注意log是目录,binlog是文件名的前缀。
- binlog_format:基于行复制,生产环境一般都这样,最靠谱。
- datadir:数据目录。
- pid-file:进程号文件。
- socket:本机的unix socket服务地址。
- port:对外的tcp socket服务端口。
初始化数据库
mysql必须初始化data目录才能拉起服务进程,所以先执行命令初始化:
1 |
mysqld --defaults-file=/home/liangdong/mysql/master/my.cnf --initialize |
它会提示一个初始化的root密码如下:
1 |
2017-09-14T09:44:16.989436Z 1 [Note] A temporary password is generated for root@localhost: phk7=.*t>R)Y |
现在,可以启动mysqld进程,一般都采用mysqld_safe这个二进制间接拉起mysqld:
1 |
mysqld_safe --defaults-file=/home/liangdong/mysql/master/my.cnf & |
现在,我们可以连接master,输入刚才提示的密码:
1 |
mysql -S /home/liangdong/mysql/master/mysql.sock -u root -p |
首次访问mysqld,需要修改root密码,执行如下命令修改密码为baidu:
1 |
mysql> SET PASSWORD = PASSWORD("baidu"); |
为了slave可以来同步数据,需要进行授权:
1 |
grant replication slave on *.* to root@"%" identified by "baidu"; |
将所有DB的所有table,授权给来自任意host的root用户,其密码是baidu。
现在万事俱备,你可以show master status看一下binlog的状况:
1 2 3 4 5 6 7 |
mysql> show master status; +---------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+------------------------------------------+ | binlog.000002 | 681 | | | a805814a-99c2-11e7-9c9e-525400caafe4:1-2 | +---------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec) |
GITD_EXECUTED是1-2,说明已经有2个事务执行过(一个set password,一个grant),用1-2区间表达可以降低GTID_EXECUTED的存储消耗。
其实,刚刚的set password操作是一个修改操作,产生了一条事务日志到binlog,未来这条事务将会同步到slave,导致slave的root密码也是baidu,知晓即可。
配置slave
1 2 3 4 5 6 |
[liangdong@10-10-162-70 slave]$ ll total 16 drwxrwxr-x 2 liangdong liangdong 4096 Sep 15 10:08 data drwxrwxr-x 2 liangdong liangdong 4096 Sep 15 09:35 log -rw-rw-r-- 1 liangdong liangdong 418 Sep 15 10:08 my.cnf drwxrwxr-x 2 liangdong liangdong 4096 Sep 15 10:08 relaylog |
与master相比,多了一个relaylog目录,这是因为slave从master同步数据首先要写入本地中继日志缓冲下来。
my.cnf也略有不同:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
[liangdong@10-10-162-70 slave]$ cat my.cnf [mysqld] server_id = 2 gtid_mode = ON enforce-gtid-consistency = ON log-bin = /home/liangdong/mysql/slave/log/binlog binlog_format = row log-slave-updates = ON relay_log = /home/liangdong/mysql/slave/relaylog/relay datadir = /home/liangdong/mysql/slave/data pid-file = /home/liangdong/mysql/slave/mysql.pid socket = /home/liangdong/mysql/slave/mysql.sock port = 3307 read_only = ON super_read_only = ON |
- log-slave-updates:重放relay-log里的事务而产生的日志也写入到binlog中。(binlog本身是记录在本机执行的更新命令,同步来的默认不会记录)
- relay_log:指定中继日志的存储路径。
- read_only:非root用户禁止在slave上执行写操作。
- super_read_only:root用户也禁止在slave上执行写操作,主要是防止误操作。
假设我们误操作在slave上执行了写操作,产生的事务日志是由slave的SERVER_ID生成的GTID,当slave重连master时上传自己的GITD_EXECUTED集合,master会发现在slave上的写事务GTID自己这里并没有出现过,从而导致slave异常,这种情况通常来说比较严重,并不好处理,所以read_only和super_read_only配置还是很重要的。
现在完成同样的初始化mysql步骤:
1 |
mysqld --defaults-file=/home/liangdong/mysql/slave/my.cnf --initialize |
启动服务:
1 |
mysqld_safe --defaults-file=/home/liangdong/mysql/slave/my.cnf & |
连接:
1 |
mysql -S /home/liangdong/mysql/slave/mysql.sock -u root -p |
临时开放root的写权限,以便修改密码,最后重新关闭root写权限:
1 2 3 4 5 6 7 8 |
mysql> set global super_read_only=off; Query OK, 0 rows affected (0.00 sec) mysql> SET PASSWORD = PASSWORD("google"); Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> set global super_read_only=ON; Query OK, 0 rows affected (0.00 sec) |
我刻意让slave的密码是google而不是baidu,因为当我们从master开始同步数据后这个密码将会被binlog传来的事务覆盖回baidu。
配置master地址:
1 |
CHANGE MASTER TO MASTER_HOST="localhost", MASTER_PORT=3306,MASTER_USER='root',MASTER_PASSWORD='baidu',MASTER_AUTO_POSITION=1; |
这里关键是MASTER_AUTO_POSITION=1,这是基于GTID主从同步的关键,它会自动上传自己的GITD_EXECUTED集合到master,由master同步slave缺失的GTID日志,从而达成数据一致。整个过程不需要我们关心,这正是GTID的强大所在。
现在,我们可以show slave status查看同步的状况:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Master_Host: localhost Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: relay.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 0 Relay_Log_Space: 154 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_UUID: Master_Info_File: /home/liangdong/mysql/slave/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: 6d167b53-99c4-11e7-bada-525400caafe4:1 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specifie |
重点关注几个字段:
- SLAVE_IO_RUNNING:网络同步线程是否工作,它负责从master拉取binlog并写到relay-log。
- SLAVE_SQL_RUNNING:重放线程是否工作,它负责从relay-log读取事务日志进行重放。
- Error相关字段:当主从同步出现问题,它们会提示你错误的原因。
- Retrieved_Gtid_Set:IO线程已经写到relay-log的GTID SET集合。
- Executed_Gtid_Set:slave上执行过的GTID SET集合,包括从master同步来的以及直接在slave上执行的,这里的GTID集合在binlog中也可以找到对应的事务日志。
为什么上面的RUNNING都是NO呢?因为还缺少一个最后的指令:
1 |
mysql> start slave; |
再次查看,同步已经在正常进行中:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000002 Read_Master_Log_Pos: 681 Relay_Log_File: relay.000002 Relay_Log_Pos: 888 Relay_Master_Log_File: binlog.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 681 Relay_Log_Space: 1085 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: a805814a-99c2-11e7-9c9e-525400caafe4 Master_Info_File: /home/liangdong/mysql/slave/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: a805814a-99c2-11e7-9c9e-525400caafe4:1-2 Executed_Gtid_Set: 6d167b53-99c4-11e7-bada-525400caafe4:1, a805814a-99c2-11e7-9c9e-525400caafe4:1-2 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) |
slave已经从master同步来了2个GTID日志(Retrieved_Gtid_Set),并且已经执行完成(Executed_Gtid_Set)。
根据我们对slave的配置,同步而来的GITD也应该写到slave的binlog中,以便提升slave为master时可以提供完整的binlog供其他slave来同步。在slave上执行如下命令,发现3个GTID全部出现在slave的binlog中,完全符合预期:
1 2 3 4 5 6 7 8 |
mysql> show master status; +---------------+----------+--------------+------------------+----------------------------------------------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+----------------------------------------------------------------------------------+ | binlog.000002 | 925 | | | 6d167b53-99c4-11e7-bada-525400caafe4:1, a805814a-99c2-11e7-9c9e-525400caafe4:1-2 | +---------------+----------+--------------+------------------+----------------------------------------------------------------------------------+ 1 row in set (0.00 sec) |
另外,此时此刻,slave的root密码已经被master的GTID日志覆盖为了baidu,而不是原先的google,所以下次登录slave密码请输入baidu。
搭建standby
standby也是一个slave,配置过程与slave完全相同,后续的博客中我会模拟master宕机,并将standby作为新的master,在此就不重复配置了。
验证主从同步
现在,我们在master上创建一个db和一个table,并插入一条数据,验证一切如我们所愿。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
mysql> create database mydb; mysql> use mydb; Database changed mysql> create table user(id bigint primary key not null auto_increment, name varchar(64) not null ); Query OK, 0 rows affected (0.02 sec) mysql> insert into user(name) values("owenliang"); Query OK, 1 row affected (0.01 sec) mysql> select * from user; +----+-----------+ | id | name | +----+-----------+ | 1 | owenliang | +----+-----------+ 1 row in set (0.00 sec) |
去slave上看一下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mydb | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use mydb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from user; +----+-----------+ | id | name | +----+-----------+ | 1 | owenliang | +----+-----------+ 1 row in set (0.00 sec) |
成功!
分别在master和slave上看一下最新的GTID状态。
master上查看binlog输出:
1 2 3 4 5 6 7 |
mysql> show master status; +---------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+------------------------------------------+ | binlog.000002 | 1343 | | | a805814a-99c2-11e7-9c9e-525400caafe4:1-5 | +---------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec) |
可见,GTID SET扩展了3,4,5三个ID,分别对应create database ,create table,insert。
在slave上查看同步进度:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000002 Read_Master_Log_Pos: 1343 Relay_Log_File: relay.000002 Relay_Log_Pos: 1550 Relay_Master_Log_File: binlog.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1343 Relay_Log_Space: 1747 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: a805814a-99c2-11e7-9c9e-525400caafe4 Master_Info_File: /home/liangdong/mysql/slave/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: a805814a-99c2-11e7-9c9e-525400caafe4:1-5 Executed_Gtid_Set: 6d167b53-99c4-11e7-bada-525400caafe4:1, a805814a-99c2-11e7-9c9e-525400caafe4:1-5 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) |
Retrieved_Gtid_Set和Executed_Gtid_Set都发生了响应的变化,符合预期。
在一篇中,我会模拟master宕机,将standby节点提升为新的master,并令slave同步新的master。
如果文章帮助您解决了工作难题,您可以帮我点击屏幕上的任意广告,或者赞助少量费用来支持我的持续创作,谢谢~

👍