MYSQL主從切換

MYSQL主從切換

1修改配置文件
read-only=1（主庫）
#read-only=1（備庫）

2、查看主備庫狀態
show slave status \G
show master status\G

3、操作備庫
STOP SLAVEIO_THREAD;
SHOW PROCESSLIST;
看到Has read all relay log
show slave status \G
STOP SLAVE;
RESET MASTER;
RESET SLAVE;

grant replication slave on *.* to'testuser'@'10.45.172.40' identified by 'test123';
show master status\G

如執行reset master;後

遇到ERROR 1186 (HY000): Binlog closed, cannot RESET MASTER的問題

處理方法：

修改vim /etc/my.cnf

加上log-bin=mysql-bin

重啟mysqld服務。

4、操作主庫
show master status\G
RESET MASTER;
RESET SLAVE;

CHANGE MASTER TO

MASTER_HOST='10.45.172.37',

MASTER_USER='testuser',

MASTER_PASSWORD='test123',

MASTER_PORT=3306,

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=98,

MASTER_CONNECT_RETRY=60;

5、重啟新主從庫，開啟slave進程

Start slave

SHOW PROCESSLIST;
show slave status \G