MySQL 主备库切换记录

旧主库：切换前将主库的表上锁，防止切换期间数据写到主库mysql> flush tables with read lock;旧备库：mysql> show processlist ;Slave has read all relay log; waiting for the slave I/O thread to update itmysql> show slave status G
旧主库：
mysql> show processlist ;
Master has sent all binlog to slave; waiting for binlog to be updated旧从库mysql> STOP SLAVE IO_THREAD;
mysql> SHOW PROCESSLIST;
确保状态为：has read all relay log
旧主库：
mysql> show master status

旧从库变主库
mysql> STOP SLAVE;mysql> RESET MASTER;mysql> RESET SLAVE;mysql> show master status G
关闭旧主库/etc/init.d/mysql stop待主库应用切到备库，关闭原主库的表lock状态
unlock tables; 从库变主库
mysql> RESET MASTER;mysql> RESET SLAVE;主库重新做备库新主库：mysql> grant replication slave on *.* to "root"@"192.168.1.29" identified by "test123";vi /etc/my.cnflog-bin=mysql-masterbinservice mysql restartmysql> flush tables with read lock;mysql> show master status; 记下file和positon /usr/bin/mysqldump -uroot -ptest123 --log-error=mysqldump47.log --all-databases > mysqldump47.sqlmysql> unlock tables;scp mysqldump47.sql 192.168.234.29:/root/ 新备库：mysql> stop slave;mysql> /usr/bin/mysql -uroot -ptest123 < mysqldump47.sqlmysql> change master to master_host="192.168.1.47",master_user="root",master_password="test123",master_log_file="mysql-masterbin.00001" ,master_log_pos=65238;mysql> start slave;新主库操作数据，新备库验证一下附录：不重启MySQL修改配置参数（万不得已使用）system gdb -p $（pidof mysqld） -ex "set log_bin = mysql-masterbin47" -batch
gdb -p $（pidof mysqld） -ex "set log_bin = mysql-masterbin47" -batch数据库只读锁定命令，防止导出数据库的时候有数据写入flush tables with read lock; 解除锁定
unlock tables;备份或者准备主备切换前设置数据为只读状态（具有超级管理权限的用户仍然可以DML操作表）SET GLOBAL READ_ONLY = ON;SET GLOBAL READ_ONLY = OFF; SHOW VARIABLES LIKE "%read_only%"; set global read_only=on/off是DBA经常用的一个操作：进行主备切换的时候，一般都会先对主库进行只读操作（on），然后主备同步完成后，再把备库置为可读写（off）。这样可以避免切换的过程中双写引起脏数据。mysqld.cc中同时定义了2个变量：my_bool read_only= 0, opt_readonly= 0; opt_readonly是当前系统的read_only状态,read_only是要把read_only设置成的值。本文永久更新链接地址