MYSQL Master-Slave switch

50297321

# # # #sample 1 Test passed (5.6 + GTID mode sync)

1> Normal Switching

1) Check the output of the show Processlist statement from the server until you see have read all relaylogwaiting for the slave I/O thread to update it

From the server:
Mysql> show Processlist;
+----+-------------+-----------+------+---------+------+------------------------------------------------------- ----------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+------------------------------------------------------- ----------------------+------------------+
| 3 | System user | | NULL | Connect | 2316 | Waiting for Master to send event | NULL |
| 4 | System user | | NULL | Connect | 2178 | Slave have read all relay log; Waiting for the slave I/O thread to update it | NULL |
| 12 | Root | localhost | NULL | Query | 0 | init | Show Processlist |
+----+-------------+-----------+------+---------+------+------------------------------------------------------- ----------------------+------------------+
3 Rows in Set (0.00 sec)

2) Ensure that all statements in the log have been processed from the server.
mysql> STOP SLAVE Io_thread

When these are done from the server, they can be reconfigured to a new setting.

3) on the slave server that is promoted to the primary server, issue the STOP slave and reset master and reset slave operations.

Mysql> STOP SLAVE;
Query OK, 0 rows Affected (0.00 sec)

Mysql> RESET MASTER;
Query OK, 0 rows affected (0.01 sec)

Mysql> RESET SLAVE;
Query OK, 0 rows affected (0.01 sec)

4) Then restart the MySQL service.

Mysqladmin-uroot-p shutdown-s/var/lib/mysql/mysql_3307.sock

Mysqld_safe--DEFAULTS-FILE=/MYSQLDB/DATA_3307/MY.CNF &

5) Create user grant replication Slave on * * [email protected] ' IP of A ' identified by ' replpwd ' in the standby server (new primary server)

Grant Allprivileges on * * to ' Repdcs ' @ ' IPs ' identified by REPLPWD;

6) Reset master on the primary server. Then change MASTER tomaster_host= ' original from server IP ', master_user= ' username ', master_password= ' password ', master_log_file= ' Master-bin.000015 ';

MySQL--host=*--port=3307-urep-p*

Change Master to
Master_host= ' * ',
Master_user= ' rep ',
Master_password= ' 123456 ',
master_port=3307,
Master_auto_position = 1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

Start SLAVE;
View status show slave status\g;

7)
New Main Library:
Show Master status;

mysql> Show Master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 151 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in Set (0.00 sec)

Do some DML

mysql> Show Master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000001 | 411 | | | A7a643b1-5a42-11e8-aa18-005056a436cc:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in Set (0.00 sec)

If not, restart the main library again and perform the change Master tomaster_host= ' 192.168.0.110 ', master_user= ' Repdcs ', master_password= ' 111111 ' on the standby, master_log_file= ' mysqlbin.000002 ';

8) Modify the app's connection address to the new main library

Switch is complete.

MYSQL Master-Slave switch