分享一次生产MySQL数据库主备切换演练
作者:应用开发 来源:系统运维 浏览: 【大中小】 发布时间:2025-11-05 08:55:26 评论数:
概述
一般生产环境都需要定期去做一次mysql数据库主备切换演练，分享这里简单记录一下，次生产后面再演示一下备份恢复的数据一些演练工作。
1、网站模板库主查看主库状态
show processlist; Master has sent all binlog to slave; waiting for binlog to be updated show master status \G
2、备切查看从库状态
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、切换原有主库为从库(原有主库执行)
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插入数据 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)
9.1.2从库查询： 
9.2 删除数据 9.2.1主库删除表： mysql> drop table test123; 
9.2.2 从库查询： 