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)

--再次查看狀態出現了錯誤提示
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
replpwd

2、更換複製賬戶及密碼

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  :