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