MySQL修改複製使用者及密碼，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 repl@192.168.1.177                                                                                  |+----------------------------------------------------------------------------------------------------------------+| 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 'repl@192.168.1.177: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.inforeplreplpwd

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.info23inst3406bin.0000013294834192.168.1.177repl2Repl23406  ..........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,sakila

3、關於change master
CHANGE MASTER TO changes the parameters that the slave server uses for connecting to the master
server, for reading the master binary log, and reading the slave relay log. It also updates the contents
of the master info and relay log info repositories (see Section 16.2.2, “Replication Relay and Status
Logs”). To use CHANGE MASTER TO, the slave replication threads must be stopped (use STOP SLAVE
if necessary). In MySQL 5.6.11 and later, gtid_next [2060] must also be set to AUTOMATIC (Bug
#16062608).

 

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 master has changed, you just need to issue these statements to tell the slave 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.

 

If you specify the MASTER_HOST or MASTER_PORT option, the slave assumes that the master
server is different from before (even if the option value is the same as its current value.) In this
case, the old values for the master binary log file name and position are considered no longer
applicable, so if you do not specify MASTER_LOG_FILE and MASTER_LOG_POS in the statement,
MASTER_LOG_FILE='' and MASTER_LOG_POS=4 are silently appended to it.

 

Setting MASTER_HOST='' (that is, setting its value explicitly to an empty string) is not the same as
not setting MASTER_HOST at all. Beginning with MySQL 5.5, trying to set MASTER_HOST to an empty
string fails with an error. Previously, setting MASTER_HOST to an empty string caused START SLAVE
subsequently to fail. (Bug #28796)