MySQL修改复制用户口令的过程

1. 概述

由于忘记复制用户口令,想要修改下,后来发现其实在从库上的master.info文件中以明文保存了复制用户的口令

2. 检查主库

  • 检查master状态
  • root@localhost[(none)]> show master status;
    +-------------------+----------+--------------+------------------+-------------------+
    | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +-------------------+----------+--------------+------------------+-------------------+
    | master-bin.000006 |      154 |              |                  |                   |
    +-------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)
    
  • 检查用户情况
  • root@localhost[(none)]> use mysql;
    Database changed
    root@localhost[mysql]> select user,host,authentication_string,password_expired from user;
    +-----------+---------------+-------------------------------------------+------------------+
    | user      | host          | authentication_string                     | password_expired |
    +-----------+---------------+-------------------------------------------+------------------+
    | root      | localhost     | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | N                |
    | mysql.sys | localhost     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N                |
    | repl_user | xxx.xxx.xxx.% | *51125B3597BEE0FC43E0BCBFEE002EF8641B44CF | N                |
    +-----------+---------------+-------------------------------------------+------------------+
    3 rows in set (0.01 sec)
    
  • 检查复制用户权限
  • root@localhost[mysql]> show grants for 'repl_user'@'xxx.xxx.xxx.%';
    +------------------------------------------------------------------------------+
    | Grants for repl_user@xxx.xxx.xxx.%                                           |
    +------------------------------------------------------------------------------+
    | GRANT RELOAD, SUPER, REPLICATION SLAVE ON *.* TO 'repl_user'@'xxx.xxx.xxx.%' |
    +------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    

    3. 检查从库

  • 检查复制状态
  • 当前复制正常

    root@localhost[(none)]> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: xxx.xxx.xxx.45
                      Master_User: repl_user
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: master-bin.000006
              Read_Master_Log_Pos: 154
                   Relay_Log_File: slave-relay-bin.000013
                    Relay_Log_Pos: 369
            Relay_Master_Log_File: master-bin.000006
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              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: 154
                  Relay_Log_Space: 624
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 1
                      Master_UUID: 05d54147-c68e-11e6-9c5a-001a4a1ee9ff
                 Master_Info_File: /data/mysql/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
    1 row in set (0.00 sec)
    

    4. 修改主库密码

    root@localhost[mysql]> set password for 'repl_user'@'xxx.xxx.xxx.%'=password('slave2');
    Query OK, 0 rows affected, 1 warning (0.04 sec)
    
  • 检查主库:
  • repl_user@xxx.xxx.xxx.45[(none)]> show master status;
    +-------------------+----------+--------------+------------------+-------------------+
    | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +-------------------+----------+--------------+------------------+-------------------+
    | master-bin.000006 |      670 |              |                  |                   |
    +-------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)
    
  • 检查从库:
  • root@localhost[(none)]> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: xxx.xxx.xxx.45
                      Master_User: repl_user
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: master-bin.000006
              Read_Master_Log_Pos: 670                              ---> 与主库一致
                   Relay_Log_File: slave-relay-bin.000013
                    Relay_Log_Pos: 885
            Relay_Master_Log_File: master-bin.000006
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              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: 670                              ---> 与主库一致
                  Relay_Log_Space: 1140
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 1
                      Master_UUID: 05d54147-c68e-11e6-9c5a-001a4a1ee9ff
                 Master_Info_File: /data/mysql/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
    1 row in set (0.00 sec)
    

    在从库复制进程没有停的情况下,还可以从主库复制数据过来

    5. 重启从库的复制进程

    重启从库的复制,无法起动,因为复制用户的口令已修改

    root@localhost[(none)]> stop slave;
    Query OK, 0 rows affected (0.05 sec)
    
    root@localhost[(none)]> start slave;
    Query OK, 0 rows affected (0.02 sec)
    
    root@localhost[(none)]> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Connecting to master
                      Master_Host: xxx.xxx.xxx.45
                      Master_User: repl_user
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: master-bin.000006
              Read_Master_Log_Pos: 670
                   Relay_Log_File: slave-relay-bin.000013
                    Relay_Log_Pos: 885
            Relay_Master_Log_File: master-bin.000006
                 Slave_IO_Running: Connecting
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              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: 670
                  Relay_Log_Space: 1140
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: NULL
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 1045
                    Last_IO_Error: error connecting to master 'repl_user@xxx.xxx.xxx.45:3306' - retry-time: 60  retries: 1
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 1
                      Master_UUID: 05d54147-c68e-11e6-9c5a-001a4a1ee9ff
                 Master_Info_File: /data/mysql/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 170216 09:28:14
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
    1 row in set (0.00 sec)
    

    6.修改复制用户口令

    root@localhost[(none)]> change master to
        -> master_user='repl_user',
        -> master_password='slave2';
    ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first.
    

    修改从库上复制的配置,需要停止复制进程

    root@localhost[(none)]> stop slave;
    Query OK, 0 rows affected (0.01 sec)
    
    root@localhost[(none)]> change master to
        -> master_user='repl_user',
        -> master_password='slave2';
    Query OK, 0 rows affected, 2 warnings (0.06 sec)
    
    root@localhost[(none)]> start slave;
    Query OK, 0 rows affected (0.00 sec)
    

    启动状态正常
    检查master.info文件,密码已改且为明文

    root@localhost[(none)]> system strings /data/mysql/master.info
    master-bin.000006
    xxx.xxx.xxx.45
    repl_user
    slave2
    3306
    30.000
    05d54147-c68e-11e6-9c5a-001a4a1ee9ff
    86400
    

    关于紫砂壶

    感悟技术人生
    此条目发表在复制分类目录,贴了标签。将固定链接加入收藏夹。