概述

一般生產(chǎn)環(huán)境都需要定期去做一次MySQL數(shù)據(jù)庫主備切換演練，這里簡單記錄一下，后面再演示一下備份恢復(fù)的一些演練工作。

1、查看主庫狀態(tài)

show processlist;
Master has sent all binlog to slave; waiting for binlog to be updated
show master status G

2、查看從庫狀態(tài)

show processlist;
show slave status G

3、從庫停止 IO_THREAD 線程

stop slave IO_THREAD;
show processlist;
show slave status G

4、從庫切換為主庫

stop slave;
reset master;
reset slave all;
show master status G

5、激活帳戶

SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'xxx;
FLUSH PRIVILEGES;

6、切換原有主庫為從庫(原有主庫執(zhí)行)

reset master;
reset slave all;
CHANGE MASTER TO
MASTER_HOST='1xxx',
MASTER_USER='repl',
MASTER_PASSword='xxx,
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=589;

7、檢查主庫

SHOW PROCESSLIST;
show master status G

8、啟動從庫

SHOW PROCESSLIST;
start slave;
show slave status G

9、MySQL主從測試

9.1插入數(shù)據(jù)

9.1.1創(chuàng)建測試表并插入數(shù)據(jù)

mysql> create table test123(id int(4));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test123 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test123 values(2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test123 values(3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test123 values(4);
Query OK, 1 row affected (0.00 sec)

9.1.2從庫查詢：

9.2 刪除數(shù)據(jù)

9.2.1主庫刪除表：

mysql> drop table test123;

9.2.2 從庫查詢：