MySQL修改复制用户及密码

客官°小女子只卖身不卖艺 2022-08-07 14:47 261阅读 0赞

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

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.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   : 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)