mysql5.5-主从替换

一、环境：

一台虚拟机三个实例（一主两从）
主：3306
从: 3307
从：3308

二、主库故障切换

1.停止主库

[root@mysql ~]# /data/3306/mysqld stop
mysql 3306 stop......                                      [确定]

======================================
此时，slave1，slave2上show slave status\G都会发现错误：
Last_IO_Error: error reconnecting to master'RepUser@192.168.1.100:3307' - retry-time: 60 retries: 1

IO进程和sql进程状态：
Slave_IO_Running: Connecting(该状态表示会一直尝试重连主，如果主正常了，该进程状态会自动变成Yes)
Slave_SQL_Running: Yes
此时，master不能提供读写服务。我们想将其中最新的slave提升为主。
=================================

2.查看从库两个线程的状态

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 1
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 429
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
*************************** 2. row ***************************
     Id: 2
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 19628
  State: Waiting for master to send event
   Info: NULL
*************************** 3. row ***************************
     Id: 8
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
3 rows in set (0.00 sec)
================================================》
State: Slave has read all relay log; waiting for the slave I/O thread to update it
State: Waiting for master to send event
这两个状态说明从库与主库的数据已经同步
如果未同步
则查看两个从库的info
哪个从库的数据更靠前说民与主库数据一致性越接近，
则把哪个从库提升为主库
[root@mysql ~]# cat /data/3307/data/master.info |head -3
18
mysql-bin.000005
811
[root@mysql ~]# cat /data/3308/data/master.info |head -3 
18
mysql-bin.000005
811
=================================================

3.3307从库提升成主库

3.1停止slave

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

3.2清除同步信息

mysql> reset slave all
    -> ;
Query OK, 0 rows affected (0.04 sec)
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       107 |
+------------------+-----------+
1 row in set (0.00 sec)
====
reset slave all是清除从库的同步复制信息、包括连接信息和二进制文件名、位置-- 从库上执行这个命令后,使用show slave status将不会有输出。

3.3开启log-bin

[root@mysql ~]# grep log-bin /data/3307/my.conf   
log-bin = /data/3307/mysql-bin 
如果存在log-slave-updates read-only等一定要注释。

3.4重启

[root@mysql ~]# /data/3307/mysqld restart
mysql 3307 stop......                                      [确定]
mysql 3307 start......                                     [确定]

3.53308从库同步

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> change master to master_port=3307;###因为3307与3306数据一致，同步的用户也一致，所以只需要改端口
Query OK, 0 rows affected (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

3.6测试

3307主库创建数据库
mysql> create database test3307;
Query OK, 1 row affected (0.00 sec)
3308从库查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aa                 |
| bb                 |
| liang              |
| liang_gbk          |
| liang_utf8         |
| mysql              |
| performance_schema |
| test               |
| test02             |
| test03             |
| test123            |
| test3307           |
+--------------------+
13 rows in set (0.01 sec)

三、正常切换

• 将主库3307切换成3308

1.主库设置只读

mysql> set global read_only=1;  
Query OK, 0 rows affected (0.00 sec)

2.停止从库

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)`

3.清除从库配置信息

mysql> reset slave all
    -> ;
Query OK, 0 rows affected (0.01 sec)
mysql> reset master;
Query OK, 0 rows affected (0.04 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       107 |
+------------------+-----------+
1 row in set (0.00 sec)

4.从库开启log-bin

[root@mysql ~]# grep log-bin /data/3308/my.conf  
log-bin = /data/3308/mysql-bin 

5.重启

[root@mysql ~]# /data/3308/mysqld restart
mysql 3308 stop......                                      [确定]
mysql 3308 start......                                     [确定]

6.3307当从库

配置3308master信息
mysql> change master to
    -> master_host='10.0.0.13',
    -> master_port=3308
    -> ,master_user='rep',
    -> master_password='000000',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=107;
Query OK, 0 rows affected (0.04 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
[root@mysql ~]# mysql -uroot -p000000 -S /data/3307/mysql.sock -e "show slave status\G"|grep -i run 
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

7.测试

主库创建库
mysql> create database zhuku3308;
Query OK, 1 row affected (0.00 sec)
从库查看
[root@mysql ~]# mysql -uroot -p000000 -S /data/3307/mysql.sock -e "show databases"|grep zhuku              
zhuku3308