在生產環境中有時候需要修改復制用戶賬戶的密碼，比如密碼遺失，或者由於多個不同的復制用戶想統一為單獨一個復制賬戶。對於這些操作應盡可能慎重以避免操作不同導致主從不一致而需要進行修復。本文描述了修改復制賬戶密碼以及變更復制賬戶。
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)
--再次查看狀態出現了錯誤提示
slave@localhost[test]> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.1.177
Master_User: repl
Master_Port: 3406
Connect_Retry: 60
Master_Log_File: inst3406bin.000001
Read_Master_Log_Pos: 3296438
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 1243
Relay_Master_Log_File: inst3406bin.000001
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB: test,sakila
....................
Last_IO_Errno: 1045
Last_IO_Error: error connecting to master '[email protected]:3406' - retry-time: 60 retries: 1
--更改重庫連接密碼，該信息記錄在從庫master.info文件中
slave@localhost[test]> stop slave;
slave@localhost[test]> change master to
-> master_user='repl',
-> master_password='replpwd';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
--修改密碼後，從庫狀態正常，以下檢查結果不再列出
slave@localhost[test]> start slave;
--查看master.info，密碼已更改且為名文
slave@localhost[(none)]> system grep repl /data/inst3506/data3506/master.info
repl
replpwd2、更換復制賬戶及密碼
master@localhost[test]> GRANT REPLICATION SLAVE ON *.* TO 'repl2'@'192.168.1.177' IDENTIFIED BY 'Repl2';
Query OK, 0 rows affected (0.00 sec)
slave@localhost[test]> stop slave;
Query OK, 0 rows affected (0.28 sec)
master@localhost[test]> insert into tb1 values(3,'jack');
Query OK, 1 row affected (0.00 sec)
slave@localhost[test]> change master to
-> MASTER_USER='repl2',
-> MASTER_PASSWORD='Repl2';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
slave@localhost[test]> system more /data/inst3506/data3506/master.info
23
inst3406bin.000001
3294834
192.168.1.177
repl2
Repl2
3406
..........
slave@localhost[test]> start slave;
Query OK, 0 rows affected (0.01 sec)
slave@localhost[test]> select * from tb1 where id=3;
+------+------+
| id | name |
+------+------+
| 3 | jack |
+------+------+
1 row in set (0.00 sec)
slave@localhost[(none)]> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.177
Master_User: repl2
Master_Port: 3406
Connect_Retry: 60
Master_Log_File: inst3406bin.000001 --Author :Leshami
Read_Master_Log_Pos: 3296871 --Blog : http://blog.csdn.net/leshami
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 501
Relay_Master_Log_File: inst3406bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test,sakila3、關於change master
Options not specified retain their value, except as indicated in the following discussion. Thus, in most
STOP SLAVE; -- if replication was running
MASTER_HOST, MASTER_USER, MASTER_PASSWORD, and MASTER_PORT provide information to the
Note: Replication cannot use Unix socket files. You must be able to connect to the
If you specify the MASTER_HOST or MASTER_PORT option, the slave assumes that the master
Setting MASTER_HOST='' (that is, setting its value explicitly to an empty string) is not the same as
