MySQL modifies the copied user and password

MySQL modifies the copied user and password

In the production environment, you sometimes need to modify the password of the copied user account, for example, the password is lost, or because multiple different copied users want to be a single copy account. These operations should be as careful as possible to avoid different operations resulting in inconsistent master and need to be repaired. This document describes how to change the password of a duplicate account and how to change the password of a duplicate account.

1. Change the password of the duplicate account

-- Demo environment: two instances on the same host, master 3406, from 3506 -- current version. Note: The master account indicates that operations are performed on the master database, slave performs operations on the slave database. master @ localhost [(none)]> show variables like 'version '; + --------------- + ------------ + | Variable_name | Value | + --------------- + ------------ + | version | 5.6.12-log | + --------------- + ------------ + -- master database record @ localhost [test]> * 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: The relay-bin.000002 Relay_Log_Pos: 811 Relay_Master_Log_File: inst3406bin. 000001 rows: Yes rows: test, sakila -- only the test and sakila databases are copied: Replicate_Do_Table: tables: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 3296006 Relay_Log_Space: 978 -- Copy account information on the master database master @ localhost [test]> show grants for 'repl' @ '192. 168.1.177 '; + tables + | Grants for repl@192.168.1.177 | + tables + | grant replication slave on *. * TO 'repl' @ '192. 168.1.177 'identified BY password' * a%e797037bf191c5c2038c039 '| + modify + -- modify the PASSWORD of the REPLICATION account master @ localhost [test]> grant replication slave on *. * TO 'repl' @ '192. 168.1.177 'identified BY 'replpwd'; -- The following query shows that the password has changed master @ localhost [test]> select user, host, password from mysql. user where user = 'repl '; + ------ + --------------- + primary + | user | host | password | + ------ + --------------- + primary + | repl | 192.168.1.177 | * Primary | + ------ + ------------- + primary + -- Update the master Record @ localhost [test]> insert into tb1 values (2, 'fred '); -- the database record that has just been updated can be queried on the record 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) -- check the status again. The error message "slave @ localhost [test]> show slave status \ G **********************" appears ******************** * ****** 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: The 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 -- change the password for the reconnection, this information is recorded in the slave database master.info file 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) -- after changing the password, the slave database status is normal, the following check results do not list slave @ localhost [test]> start slave; -- View master.info. The password has been changed and is named slave @ localhost [(none)]> system grep repl/data/inst3506/data3506/master. inforeplreplpwd

2. Change the duplicate account and password

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. About change master

Options not specified retain their value, counter T 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

Note: Replication cannot use Unix socket files. You must be able to connect to

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