mysql主从切换
linux1 master 10.10.10.70
linux2 slave 10.10.10.80
做主从是在从服务器也事先打开binglog、以及授权好各种用户。
基础环境(双机互联)
[root@linux2 ~]# ssh-keygen
[root@linux2 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 10.10.10.70
[root@linux2 ~]# mysql -uroot -p123456 -e "GRANT replication slave ON *.* TO 'slave'@'%' IDENTIFIED BY '123456@';"
[root@linux2 ~]# mysql -uroot -p123456 -e "flush privileges;"
在从库远程执行命令把主库的业务IP down掉:
[root@linux2 ~]# ssh -t root@10.10.10.70 "ifconfig eth0 down"
停止从服务器:
[root@linux2 ~]# mysql -uroot -p123456 -e "stop slave;"
Warning: Using a password on the command line interface can be insecure.
清除从中master的信息:
[root@linux2 ~]# mysql -uroot -p123456 -e "reset master;"
Warning: Using a password on the command line interface can be insecure.
重启mysql:
[root@linux2 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. [ OK ]
Starting MySQL.. [ OK ]
记录从的二进制日志位置点:
[root@linux2 ~]# mysql -uroot -p123456 -e "show master status" > /root/master_status.txt
Warning: Using a password on the command line interface can be insecure.
拷贝位置点信息到主:
[root@linux2 ~]# scp /root/master_status.txt 10.10.10.70:/root/
master_status.txt 100% 86 0.1KB/s 00:00
重置从库的IP提示为主:
[root@linux2 ~]# sed -i s#IPADDR=10.10.10.80#IPADDR=10.10.10.70#g /etc/sysconfig/network-scripts/ifcfg-eth0
[root@linux2 ~]# /etc/init.d/network restart
通过管理IP登录原主库:
更改业务IP为原从的IP:
[root@linux1 ~]# sed -i s#IPADDR=10.10.10.70#IPADDR=10.10.10.80#g /etc/sysconfig/network-scripts/ifcfg-eth0
[root@linux1 ~]# /etc/init.d/network restart
登录mysql:(执行同步命令)
[root@linux1 ~]# mysql -uroot -p123456
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.10.10.70',
-> MASTER_PORT=3306,
-> MASTER_USER='slave',
-> MASTER_PASSWORD='123456@',
-> MASTER_LOG_FILE='mysql-bin.000102',
-> MASTER_LOG_POS=26422;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
启动从库:
mysql> reset slave;
mysql> start slave;
mysql> show slave status\G
#change master命令:
"""
CHANGE MASTER TO
MASTER_HOST='10.10.10.70',
MASTER_PORT=3306,
MASTER_USER='slave',
MASTER_PASSWORD='123456@',
MASTER_LOG_FILE='mysql-bin.000102',
MASTER_LOG_POS=26422;
"""
#mysql5.6支持 GTID