MySQL修改复制用户及密码

在生产环境中有时候需要修改复制MySQL用户账户的密码，比如密码遗失，或者由于多个不同的复制用户想统一为单独一个复制账户。对于

在生产环境中有时候需要修改复制MySQL用户账户的密码，比如密码遗失，或者由于多个不同的复制用户想统一为单独一个复制账户。对于这些操作应尽可能慎重以避免操作不同导致主从不一致而需要进行修复。本文描述了修改复制账户密码以及变更复制账户。

1、更改复制账户密码

--演示环境，同一主机上的2个实例，主3406，从3506
--当前版本，注：master账户表明是对主库进行相关操作，slave则是对从库进行相关操作
master@localhost[(none)]> show variables like 'version';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| version | 5.6.12-log |
+---------------+------------+

--主库上的记录
master@localhost[test]> select * from tb1;
+------+-------+
| id | name |
+------+-------+
| 1 | robin |
+------+-------+

--从库上的记录
slave@localhost[test]> select * from tb1;
+------+-------+
| id | name |
+------+-------+
| 1 | robin |
+------+-------+

--当前从库上的状态信息
slave@localhost[test]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.177
Master_User: repl
Master_Port: 3406
Connect_Retry: 60
Master_Log_File: inst3406bin.000001
Read_Master_Log_Pos: 3296006
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 811
Relay_Master_Log_File: inst3406bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test,sakila --仅复制了test以及sakila数据库
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: 3296006
Relay_Log_Space: 978

--主库上复制账户的信息
master@localhost[test]> show grants for 'repl'@'192.168.1.177';
+----------------------------------------------------------------------------------------------------------------+
| Grants for [email protected] |
+----------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.177' IDENTIFIED BY PASSWORD '*A424E797037BF191C5C2038C039' |
+----------------------------------------------------------------------------------------------------------------+

--修改复制账户密码
master@localhost[test]> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.177' IDENTIFIED BY 'replpwd';

--如下查询密码已更改
master@localhost[test]> select user,host,password from mysql.user where user='repl';
+------+---------------+-------------------------------------------+
| user | host | password |
+------+---------------+-------------------------------------------+
| repl | 192.168.1.177 | *4A04E4FD524292A79E3DCFEBBD46094478F178EF |
+------+---------------+-------------------------------------------+

--更新记录
master@localhost[test]> insert into tb1 values(2,'fred');

--重库上可以查询到刚刚被更新的记录
slave@localhost[test]> select * from tb1;
+------+-------+
| id | name |
+------+-------+
| 1 | robin |
| 2 | fred |
+------+-------+

slave@localhost[test]> stop slave;
Query OK, 0 rows affected (0.02 sec)

slave@localhost[test]> start slave;
Query OK, 0 rows affected (0.01 sec)