MySQL主从复制切换

MySQL
,

背景

由于公司iaas人员在操作虚拟ip时手误，导致应该指向mysql主库的虚拟ip指向了从库。因为业务app使用的是虚拟ip链接的数据库，因此导致所有的业务数据都写入了从库，而主库的数据从此没有任何变化。

为了解决此问题，需要把原来的主库变成从库，从而继续保持mysql数据库的高可用。

基本信息

旧主库ip：172.99.0.32

旧从库ip：172.99.0.31

具体操作如下

验证当前的主从复制状态

旧的主库数据是否全部同步到从库，在原主库执行主从同步状态：

1
2
3
4
5
6
7
sys@172.99.0.32:(none) 17:54:52>show processlist;
+-------+------------+-------------------+------+-------------+--------+-----------------------------------------------------------------------+------------------+----------+
| Id    | User       | Host              | db   | Command     | Time   | State                                                                 | Info             | Progress |
+-------+------------+-------------------+------+-------------+--------+-----------------------------------------------------------------------+------------------+----------+
| 11679 | rep   | 172.99.0.31:8609  | NULL | Binlog Dump | 279575 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |    0.000 |
+-------+------------+-------------------+------+-------------+--------+-----------------------------------------------------------------------+------------------+----------+
3 rows in set (0.00 sec)

查看从库是否可以做主库

主要是查看bin-log是否打开，结论：31服务器可以作为主库

1
2
3
4
5
6
7
sys@172.99.0.31:(none) 17:57:01>show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000153 |  2786181 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

查看从哪里进行主从复制

查看旧主库最后一条bin-log位置

因为重新启动过，并且重新启动之后主库没有执行过sql，所以最新的bin-log并没有sql，因此查看上一个bin-log的最后的位置。

把二进制的bin-log转换成人类语言
1
mysqlbinlog --base64-output=decode-rows -v mysql-bin.000706 > mysql-bin.000706.txt
查看bin-log最后的位置如下：

tail -200 mysql-bin.000706.txt

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
BEGIN
/*!*/;
# at 32438411
# at 32438848
# at 32438939
#220317 15:00:00 server id 533307  end_log_pos 32438848 	Annotate_rows:
#Q> update job_log set
#Q> 			job_name='detail_NoticeJob',
#Q> 			job_cron_expression='0 0 1,10,15,21 * * ?',
#Q> 			create_time='2022-03-17 15:00:00',
#Q> 			job_class_name='com.hello.lease.job.NoticeJob',
#Q> 			staff_name='系统操作',
#Q> 			update_time='2022-03-17 15:00:00',
#Q> 			staff_id=0,
#Q> 			job_msg='通知-每日1、10、15、21点',
#Q> 			status='end'
#Q> 		 wher
#220317 15:00:00 server id 533307  end_log_pos 32438939 	Table_map: `db`.`job_log` mapped to number 3857
#220317 15:00:00 server id 533307  end_log_pos 32439375 	Update_rows: table id 3857 flags: STMT_END_F
### UPDATE `db`.`job_log`
### WHERE
###   @1=14424
###   @2='detail_NoticeJob'
###   @3='0 0 1,10,15,21 * * ?'
###   @4='com.hello.lease.job.NoticeJob'
###   @5='通知-每日1、10、15、21点'
###   @6='start'
###   @7=0
###   @8='系统操作'
###   @9=2022-03-17 15:00:00
###   @10=1647500400
### SET
###   @1=14424
###   @2='detail_NoticeJob'
###   @3='0 0 1,10,15,21 * * ?'
###   @4='com.hello.lease.job.NoticeJob'
###   @5='通知-每日1、10、15、21点'
###   @6='end'
###   @7=0
###   @8='系统操作'
###   @9=2022-03-17 15:00:00
###   @10=1647500400
# at 32439375
#220317 15:00:00 server id 533307  end_log_pos 32439402 	Xid = 825866165
COMMIT/*!*/;
DELIMITER ;
# End of log file
旧从库同步最后一条数据时的bin-log位置

根据主库的最后一条bin-log信息找到从库对应的bin-log位置如下：

mysql-bin.000703

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
BEGIN
/*!*/;
# at 132131716
# at 132131807
#220317 15:00:00 server id 533307  end_log_pos 132131807        Table_map: `db`.`job_log` mapped to number 54116
#220317 15:00:00 server id 533307  end_log_pos 132132243        Update_rows: table id 54116 flags: STMT_END_F
### UPDATE `db`.`job_log`
### WHERE
###   @1=14424
###   @2='detail_NoticeJob'
###   @3='0 0 1,10,15,21 * * ?'
###   @4='com.hello.lease.job.NoticeJob'
###   @5='通知-每日1、10、15、21点'
###   @6='start'
###   @7=0
###   @8='系统操作'
###   @9=2022-03-17 15:00:00
###   @10=1647500400
### SET
###   @1=14424
###   @2='detail_NoticeJob'
###   @3='0 0 1,10,15,21 * * ?'
###   @4='com.hello.lease.job.NoticeJob'
###   @5='通知-每日1、10、15、21点'
###   @6='end'
###   @7=0
###   @8='系统操作'
###   @9=2022-03-17 15:00:00
###   @10=1647500400
# at 132132243
#220317 15:00:00 server id 533307  end_log_pos 132132270        Xid = 49158632
COMMIT/*!*/;
# at 132132270

进行主从切换

验证31上是否有主从复制的用户

如果没有需要新建

1
2
3
4
5
6
7
sys@172.99.0.31:mysql 16:07:25>select user,host from user where user='rep';
+----------+------+
| user     | host |
+----------+------+
| rep | %    |
+----------+------+
1 row in set (0.00 sec)

停掉31的主从复制

1
2
3
4
5
6
# 停止同步
stop slave;
# 重置同步：https://mariadb.com/kb/en/reset-replica/
RESET SLAVE;
# 验证同步状态
show slave status \G

启动32的主从复制

MASTER_LOG_FILE和MASTER_LOG_POS的值是从上面的旧从库的bin-log中获取的。位置是旧主库的最后一条bin-log执行的sql的位置。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 添加主从同步配置
CHANGE MASTER TO
  MASTER_HOST='172.99.0.31',
  MASTER_USER='rep',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3308,
  MASTER_LOG_FILE='mysql-bin.000703',
  MASTER_LOG_POS=132132270,
  MASTER_CONNECT_RETRY=10;

# 启动主从同步
start slave;

# 验证同步状态
show slave status \G