修改 MySQL 同步用户密码

数据库版本：
Server version: 5.6.24-log MySQL Community Server (GPL)

在主备库上均检查 GTID 模式是否开启：

mysql> show variables like '%gtid_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

这儿未开启，则后面重新同步时需要指定详细位置。

在从库停止复制并记录位置：

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 148.7.5.19
                  Master_User: sync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000015
          Read_Master_Log_Pos: 223572050

主库修改密码：

set password for 'sync'@'148.7.5.137' = password('YPASSWD');

备库上修改同步信息：

change master to 
MASTER_HOST='148.7.5.19',
MASTER_USER='sync',
MASTER_PASSWORD='YPASSWD',
MASTER_LOG_FILE='mysql-bin.000015',
MASTER_LOG_POS=223572050;

开启同步并检查状态：

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

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 148.7.5.19
                  Master_User: sync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000015
          Read_Master_Log_Pos: 223572194
               Relay_Log_File: mypdb-slave-relay-bin.000002
                Relay_Log_Pos: 427
        Relay_Master_Log_File: mysql-bin.000015
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

遗留问题：

主库：

select user,host from mysql.user;
+--------+-------------+
| user   | host        |
+--------+-------------+
| msp    | %           |
| root   | %           |
| root   | 127.0.0.1   |
| sync   | 148.7.5.137 |
| root   | ::1         |
| dboper | localhost   |
| root   | mypdb       |
+--------+-------------+
7 rows in set (0.00 sec)

备库：

mysql>   select user,host from mysql.user;
+--------+--------------+
| user   | host         |
+--------+--------------+
| msp    | %            |
| root   | 127.0.0.1    |
| root   | ::1          |
| dboper | localhost    |
| root   | localhost    |
| root   | msp-db-slave |
+--------+--------------+
6 rows in set (0.00 sec)

从主备库的用户可知，该库当时创建主从同步时，未考虑数据库切换的情况，如果发生数据库切换，新备库将不能从新主库进行同步。但该数据库系统承载的业务已经下线，仅保留数据库供偶而手动查询，故不再改进该问题。