MySQL modifies the copy user and password, and mysql modifies the user

MySQL modifies the copy user and password, and mysql modifies the user

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
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 ic (Bug
#16062608 ).

 

Options not specified retain their value, counter T 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
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
Slave about how to connect to its master:

 

Note: Replication cannot use Unix socket files. You must be able to connect to
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
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)