1、修改配置文件
read-only=1（主库）
#read-only=1（备库）
2、查询从库状态
mysql> show processlist ;
mysql> show slave status G
3、查询主库状态
mysql> show processlist;
mysql> show master status G
*************************** 1. row ***************************
File: mysql-bin.000004
Position: 107
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
4、从库操作
mysql> STOP SLAVE IO_THREAD;
mysql> SHOW PROCESSLIST;
确保状态为：has read all relay log
mysql> show slave status G
5、查询主库状态
mysql> show master status G
6、从库变主库
mysql> STOP SLAVE;
mysql> RESET MASTER;
mysql> RESET SLAVE;
mysql> show master status G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 107
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
7、主库变从库
mysql> RESET MASTER;
Query OK, 0 rows affected (0.06 sec)
mysql> RESET SLAVE;
Query OK, 0 rows affected (0.03 sec)
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.1.4',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='chengce243',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=107;
Query OK, 0 rows affected (0.05 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
8、重启主和从库
[root@localhost mysql]# service mysql restart
Shutting down MySQL….[ OK ]
Starting MySQL…………….[ OK ]
9、检查主从是否都正常
主库
SHOW PROCESSLIST;
show master status G
从库
SHOW PROCESSLIST;
start slave;
show slave status G