概述
一般生产环境都需要定期去做一次 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 从库查询:
来源: http://database.51cto.com/art/201909/602502.htm