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 for copying a MySQL user account, for example, if the password is lost, or because multiple different copy 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 and slave indicates operations on the slave database.
Master @ localhost [(none)]> show variables like 'version ';
+ --------------- + ------------ +
| Variable_name | Value |
+ --------------- + ------------ +
| Version | 5.6.12-log |
+ --------------- + ------------ +

-- Records on the master database
Master @ localhost [test]> select * from tb1;
+ ------ + ------- +
| Id | name |
+ ------ + ------- +
| 1 | robin |
+ ------ + ------- +

-- Records from the database
Slave @ localhost [test]> select * from tb1;
+ ------ + ------- +
| Id | name |
+ ------ + ------- +
| 1 | robin |
+ ------ + ------- +

-- Status information of the current slave Database
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 -- only the test and sakila databases are copied.
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

-- Copy account information on the master database
Master @ localhost [test]> show grants for 'repl' @ '2017. 168.1.177 ';
+ Shards +
| Grants for [email protected] |
+ Shards +
| Grant replication slave on *. * TO 'repl' @ '192. 168.1.177 'identified BY password' * A424E797037BF191C5C2038C039 '|
+ Shards +

-- Modify the password of the duplicate account
Master @ localhost [test]> grant replication slave on *. * TO 'repl' @ '192. 168.1.177 'identified by 'replpwd ';

-- Query the password changed as follows:
Master @ localhost [test]> select user, host, password from mysql. user where user = 'repl ';
+ ------ + --------------- + --------------------------------------------- +
| User | host | password |
+ ------ + --------------- + --------------------------------------------- +
| Repl | 192.168.1.177 | * 4A04E4FD524292A79E3DCFEBBD46094478F178EF |
+ ------ + --------------- + --------------------------------------------- +

-- Update record
Master @ localhost [test]> insert into tb1 values (2, 'fred ');

-- Record updated can be queried on the database.
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)

-- An error message is displayed when you view the status again.
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]: 100'-retry-time: 60 retries: 1

-- Change the password for the database reconnection, which is recorded in the slave master.info file.
Slave @ localhost [test]> stop slave;

Slave @ localhost [test]> change master
-> Master_user = 'repl ',
-> Master_password = 'replpwd ';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

-- After the password is changed, the slave database status is normal and the following check results are not listed
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.info
Repl
Replpwd

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