概述
一般生产环境都需要定期去做一次mysql数据库主备切换演练,这里简单记录一下,后面再演示一下备份恢复的一些演练工作。
1、查看主库状态
show processlist;Master has sent all binlog to slave; waiting for binlog to be updatedshow master status G
![554bc6773cd2dcfb5085696e86992f20.png](https://i-blog.csdnimg.cn/blog_migrate/50497b48eda7613a91a689fc182f9193.jpeg)
2、查看从库状态
show processlist;show slave status G
![db1c694cff9a16db8622b311d5761ec5.png](https://i-blog.csdnimg.cn/blog_migrate/8af23acd21c3e5ad31b382f707c18645.jpeg)
3、从库停止 IO_THREAD 线程
stop slave IO_THREAD;show processlist;show slave status G
![5884ba6d2af5ab70c78f4603289325f1.png](https://i-blog.csdnimg.cn/blog_migrate/c5dc642d86456280daf69cb438e26b39.jpeg)
4、从库切换为主库
stop slave;reset master;reset slave all;show master status G
![b09730c36f173f21e77a695973d5be9f.png](https://i-blog.csdnimg.cn/blog_migrate/0fde2dfdb306e8019e00ae9b97c6aa0f.jpeg)
5、激活帐户
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'xxx;FLUSH PRIVILEGES;
![53808a1925baadcc95a8738e090f8257.png](https://i-blog.csdnimg.cn/blog_migrate/0ea476e500bde92490f9c26e80b26761.jpeg)
6、切换原有主库为从库(原有主库执行)
reset master;reset slave all;CHANGE MASTER TOMASTER_HOST='1xxx',MASTER_USER='repl',MASTER_PASSWORD='xxx,MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=589;
![e6e9be9e7a5718c10f66e33287cfed00.png](https://i-blog.csdnimg.cn/blog_migrate/6b3c2d8846253500d522aa4bceadcc04.jpeg)
7、检查主库
SHOW PROCESSLIST;show master status G
![982986b415aaa65c0b71b9ffda582f77.png](https://i-blog.csdnimg.cn/blog_migrate/752b630d0684e0fad65aecd97e259a02.jpeg)
8、启动从库
SHOW PROCESSLIST;start slave;show slave status G
![6c66b220fca2a0dece305d5861d97177.png](https://i-blog.csdnimg.cn/blog_migrate/2ad6f4f5919efb886f3810d4a1c8e987.jpeg)
9、MySQL主从测试
9.1插入数据
9.1.1创建测试表并插入数据
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)
![00445fef7e0b72653270568fb58dfe02.png](https://i-blog.csdnimg.cn/blog_migrate/c0026ae4786f7951ade7655f096188b7.jpeg)
9.1.2从库查询:
![cf1e6957c073ce49271823a08085ac8e.png](https://i-blog.csdnimg.cn/blog_migrate/86562371f2d4d6014ddde5ca16135930.jpeg)
9.2 删除数据
9.2.1主库删除表:
mysql> drop table test123;
![aa7cb5a371e8fae88a6a786eb4e8dfd4.png](https://i-blog.csdnimg.cn/blog_migrate/e5035627b45d540e353d3343b42c1c70.jpeg)
9.2.2 从库查询:
![ed72bf17a571a85fc24c5d84e2da8a55.png](https://i-blog.csdnimg.cn/blog_migrate/8fc1a48e34c645bf44234c69de663056.jpeg)
篇幅有限,这块内容就介绍到这了,后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注下~
![f77eba941cbc766e75d7cc6fd821a7b6.gif](https://i-blog.csdnimg.cn/blog_migrate/8270e39d4acc57208cb2606c9fd219fc.gif)