MySQL 5.6和 5.7_同步账号修改密码方式：真的不一样
【引言】
大家都知道，MySQL配置了同步，通用账号密码在master做了修改，slave会自动同步过来。但有一个账号除外，那就是同步账号。
是不是第一感觉不太对?
为啥主从配置下的其他账号密码的修改都能同步，即使是root@local这样的本地账号都能同步至从库，同步账号就不行？
大家都知道，MySQL账号信息是保存在数据字典mysql.user中，按照道理来说，mysql.user内容的修改是会同步至从库。
先看一段在配置同步过程中change master的一段官方介绍：
MySQL 5.6官方介绍， 链接：
https://dev.mysql.com/doc/refman/5.6/en/change-master-to.html
CHANGE MASTER TO changes the parameters that the replica uses for connecting to the replication source server, for reading the source's binary log, and reading the replica's relay log. It also updates the contents of the replication metadata repositories (see Section 17.2.2, “Relay Log and Replication Metadata Repositories”). CHANGE MASTER TO requires the SUPER privilege.
In MySQL 5.6.11 and later,gtid_next must also be set to AUTOMATIC (Bug #16062608).
看到这里应该明白了，这里以配置了gtid的同步配置为例
change master to
master_host='192.168.0.110',
master_user='replication_account',
master_password='user_password',
master_port=3306,
master_auto_position=1;
按照上述MySQL官方文档解释，从库通过CHANGE MASTER TO 中的上述参数来做三件事：连接主库、读取主库binary log，读取从库relay log。
可以得出原因说明：
当主库master的同步账号发成改变，从库中的change master to中的同步账号配置在第一个阶段connecting master server时，会连接不通，原因为change master to 的相关配置是存放在从库的复制元数据存储库（replication metadata repositories）中，而复制元数据存储库这时还是保存的原来的同步账号密码。
所以，主库修改了同步账号，从库需要做一次updates the contents of the replication metadata repositories，才能保证从库对主库的连接成功，Slave_IO_Running才会由 connecting状态变为yes正常状态。
那如何修改同步账号呐？
再接着看官方文档，链接：
https://dev.mysql.com/doc/refman/5.6/en/change-master-to.html
Options not specified retain their value, except as indicated in the following discussion. Thus, in most cases, there is no need to specify options that do not change. For example, if the password to connect to your MySQL source has changed, issue these statements to tell the replica about the new password:
STOP SLAVE; -- if replication was running
CHANGE MASTER TO MASTER_PASSWORD='new3cret';
START SLAVE; -- if you want to restart replication
MASTER_HOST, MASTER_USER, MASTER_PASSWORD, and MASTER_PORT provide information to the slave about how to connect to its master:
Note: Replication cannot use Unix socket files. You must be able to connect to the master MySQL server using TCP/IP.
MASTER_USER and MASTER_PASSWORD are the user name and password of the account to use for connecting to the source. If you specify MASTER_PASSWORD, MASTER_USER is also required. The password used for a replication user account in a CHANGE MASTER TOstatement is limited to 32 characters in length; if the password is longer, the statement succeeds, but any excess characters are silently truncated. This is an issue specific to MySQL Replication, which is fixed in MySQL 5.7. (Bug #11752299, Bug #43439)
文档写的也很清晰，修改从库的MASTER_PASSWORD即可，见上述标蓝处。
以下为MySQL 5.6 双主架构下如何修改同步账号密码为例，来说明如何修改同步账号。
MySQL5.6版本下修改MySQL双主架构下同步账号密码步骤
步骤1：分别登陆master，slave 检查当前同步状态知否一直/正常
SHOW MASTER STATUS;
SHOW SLAVE STATUS;
步骤2：在master节点上修改同步账号密码
1） 检查同步账号
select host,user from mysql.user;
2） 检查同步账号权限（如同步账号为：’repl’@’%’）
show grants for ’repl’@’%’;
步骤3：在master节点上，修改同步账号repl
1）修改repl密码
grant replication slave on *.* to ’repl’@’%’ identified by ’New_password’;
2）确认账号生效：
select user,host,password from mysql.user where user='repl';
步骤4：在slave节点，修改同步密码
1）暂停同步过程
stop slave;
2）重新配置同步账号新密码
change master to
master_user='repl',
master_password='new_password;
3）重启同步过程
start slave;
4）检查同步状态
show slave status \G;
Master节点做一个insert插入测试表，验证从库是否同步数据
insert into testdb.tb1 values(2,'fred');
步骤5：在侯主中修改反向同步账号密码
重复上述步骤1 ~ 4即可。
MySQL双主架构下同步账号密码修改完成。
注意：
如果是单向主从同步，步骤1~4即结束，如果是主主同步，主主和侯主均需做上述步骤1~4。
文章到此还未结束。接着看。
以上步骤是针对MySQL5.6版本。
文章没完，接着看。
发问下：主流的版本MySQL5.7和8.0是否和5.6的同步账号密码修改方式一样？
我都这么问了，肯定不一样，不一样在什么地方呐？
继续看官方文档，链接：
https://dev.mysql.com/doc/refman/5.7/en/change-master-to.html
CHANGE MASTER TO changes the parameters that the replica uses for connecting to the replication source server, for reading the source's binary log, and reading the replica's relay log. It also updates the contents of the replication metadata repositories (see Section 16.2.4, “Relay Log and Replication Metadata Repositories”). CHANGE MASTER TO requires the SUPER privilege.
Prior to MySQL 5.7.4, the replication threads must be stopped, using STOP SLAVE if necessary, before issuing this statement. In MySQL 5.7.4 and later, you can issue CHANGE MASTER TO statements on a running replica without doing this, depending on the states of the replication SQL thread and replication I/O thread. The rules governing such use are provided later in this section.
这里可以看到和5.6版本的第一个不同的地方就是，MySQL 5.7.4及以后的版本以后的我们可以在线改同步账号了。不用像MySQL5.6一样做如下步骤：
STOP SLAVE; -- if replication was running
CHANGE MASTER TO MASTER_PASSWORD='new3cret';
START SLAVE; -- if you want to restart replication
MySQL 5.7.4及以上版本怎么改同步账号？
接着看官网。
When using a multithreaded replica (in other words slave_parallel_workers is greater than 0), stopping the replica can cause “gaps” in the sequence of transactions that have been executed from the relay log, regardless of whether the replica was stopped intentionally or otherwise. When such gaps exist, issuing CHANGE MASTER TO fails. The solution in this situation is to issue START SLAVE UNTIL SQL_AFTER_MTS_GAPS which ensures that the gaps are closed.
Options not specified retain their value, except as indicated in the following discussion. Thus, in most cases, there is no need to specify options that do not change. For example, if the password to connect to your replication source server has changed, issue this statement to tell the replica about the new password:
CHANGE MASTER TO MASTER_PASSWORD='new3cret';
由上得知，MySQL 5.7.4及以上版本不需要做stop slave操作，因为MySQL 5.7.4及以上版本基本都开启了多线程复制multithreaded replica (in other words slave_parallel_workers is greater than 0)，默认为10,。如果做了stop slave的操作，可能会导致从库上执行执行完relay log的事务序号不一致，此刻执行CHANGE MASTER TO的任何操作都会失败。
怎么办？
在MySQL 5.7.4及以上版本，如果不小心执行了stop slave操作，这时候如果
CHANGE MASTER TO MASTER_PASSWORD='new3cret'; 操作报错了；
不要着急，在从库执行START SLAVE UNTIL SQL_AFTER_MTS_GAPS即可，该操作能确保事务序号不一致情况消除。
然后再次执行CHANGE MASTER TO MASTER_PASSWORD='new3cret';即可。
MySQL 5.7.4及以上版本修改MySQL双主架构下同步账号密码步骤：
步骤1：分别登陆master，slave 检查当前同步状态知否一直/正常
SHOW MASTER STATUS;
SHOW SLAVE STATUS;
步骤2：在master节点上修改同步账号密码
1） 检查同步账号
select host,user from mysql.user;
2） 检查同步账号权限（如同步账号为：’repl’@’%’）
show grants for ’repl’@’%’;
步骤3：在master节点上，修改同步账号repl
1）修改repl密码
grant replication slave on *.* to ’repl’@’%’ identified by ’New_password’;
2）确认账号生效：
select user,host,password from mysql.user where user='repl';
步骤4：在slave节点，修改同步密码
1）重新配置同步账号新密码
change master to
master_user='repl',
master_password='new_password;
2）检查同步状态
show slave status \G;
步骤5：在侯主中修改反向同步账号密码
重复上述步骤1 ~ 4即可。
至此，文章结束。
【总结】
1. MySQL 5.6的同步账号还需要停启salve过程；到我MySQL 5.7.4及以后版本，则支持在线改；
2. 另，InnoDB cluster不需要，因为没有同步账号这一说话，数据一致性使用Paxos协议保障。
近期热文
你可能也会对以下话题感兴趣。点击链接便可查看。