轻松了解各种云产品、服务及动态、提供各种云服务。

记一次阿里云RDS与本地数据库同步中断的补救过程

137
文章目录
  1. 背景
  2. 当前数据库同步架构图
  3. 补救策略
  4. 补救过程
  5. 参考资料
  6. 更多内容请关注公众号

背景

2018年4月24日上午9点左右,小编在即将托管的服务器上建立数据库,并通过openvpn建立加密隧道,从而实现阿里云RDS和本地自建数据库之间同步。在托管服务器上配置openvpn后,重启openvpn客户端时,一直获取不到ip,后面重启了一下openvpn服务端,本地托管服务器获取到了IP,之后小编很开心的配置着MySQL主从同步。但是到11:00左右,有同事反应线上自建数据库无法访问,小编通过查看,发现在阿里云虚拟机上的自建MySQL同步中断,从而导致本地同步也中断,并且已经同步停止了2个小时。为了保证业务的正常运行,小编做了如下操作。

当前数据库同步架构图

补救策略

  1. 在RDS上获取gtid点,在从库上从新设置同步点,使从库正常运行起来,保证业务的流畅
  2. 通过解析RDS binlog,把binlog转换成SQL语句,然后把SQL语句插入到从库中
  3. 重新做RDS到自建库的数据同步
  4. 数据库同步架构改进,解决单点问题。

补救过程

1. 先让同步正常运行,保证业务不中断

1. 在RDS上获取gtid节点
命令:

 show master status\G

结果:

*************************** 1. row ***************************
File: mysql-bin.000886
Position: 324398134
Binlog_Do_DB: 
Binlog_Ignore_DB: 
Executed_Gtid_Set: 005304be-d260-11e7-b402-6c92bf475505:1-219538493,
759ccfb9-70d0-11e7-b7d5-6c92bf47591d:1-2572999,
936b4aea-70d0-11e7-b7d6-6c92bf4757fb:1-132842752,
df8f8121-d25f-11e7-b401-6c92bf4757d1:1-44768267
共返回 1 行记录,花费 5 ms.

2. 配置从节点过滤主键不存在的错误
编辑 从库配置文件(小编的配置文件为/etc/my.cnf)

slave-skip-errors = 1062,1053,1032

3. 重启数据库

service mysqld restart

4. 在从节点重新设置同步点
停止同步

stop slave

reset 同步

reset slave all;
reset master;

重新设置同步点

set global gtid_purged='005304be-d260-11e7-b402-6c92bf475505:1-219538493,
759ccfb9-70d0-11e7-b7d5-6c92bf47591d:1-2572999,
936b4aea-70d0-11e7-b7d6-6c92bf4757fb:1-132842752,
df8f8121-d25f-11e7-b401-6c92bf4757d1:1-44768267';
CHANGE MASTER TO
MASTER_HOST='xxxxxxxx',
MASTER_USER='xxxxx',
MASTER_PASSWORD='xxxxxxxx',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1,
MASTER_CONNECT_RETRY=10;

5. 启动同步

start slave

6. 查看同步是否正常

show slave status\G

2. 弥补数据

由于从库无法通过运行RDS的binlog来修复数据,所以需要解析RDS的binlog,让其转换成SQL语句,然后再在各个从库上执行解析出来的SQL语句,具体流程如下:

1. 下载binlog
在阿里云控制台,选择RDS的日志备份,并获取下载地址,在服务器上通过如下命令下载

wget  -c '日志地址' -O xxx.tar.gz

2. 获取某一时间段的binlog并转换成可识别语句

 mysqlbinlog --no-defaults --base64-output=decode-rows -v -v --start-datetime='2018-04-27 08:58:38' --stop-datetime='2018-04-27 11:58:37' mysql-bin.000797 > 797_10_11.txt

3. 使用下列脚本把上面转换后的binlog解析成SQL语句
脚本binlog_parse_to_sql.py
小编千辛万苦终于找到了一可以把binlog转换成SQL语句的脚本,github地址为:https://github.com/yy1117/binlog_parse_to_sql。但是该脚本直接使用不适合小编的需求,所以小编对代码进行了更改,满足了自己的需求。

#!/usr/bin/python
# -*- coding:utf8 -*-
#version_1.2
#######Comment:####################################################################
## Author:GuiJiaoQi&XuYou  MAIL:yinyi1117@126.com;QQ:85531861       ##
###### set global group_concat_max_len =1024000;避免group_concat  默认长度不够   ##
###### 如果binlog文件的表在后面发生表结果变化,这个解析会报错           ##
######  如果columns是二进制的解析会报错,如果有二进制的还请换工具,抱歉               ##
###################################################################################
import os,sys
import re
import MySQLdb
import optparse
def main():
p = optparse.OptionParser()
p.add_option('-u','--user',type='string',dest='user',default='root',
help='User for login ')
p.add_option('-p','--password',type='string',dest='password',
help='Password to use when connecting')
p.add_option('-s','--socket',type='string',dest='socket',
help='The socket file to use for connection.')
p.add_option('-f','--file',type="string",dest='filename',
help="incoming parse binlogfile For Example:mysqlbinlog  --no-defaults --base64-output=decode-rows -v -v mysql-bin.00000x",metavar='FILE')
p.add_option('-b','--report',dest='binlog',help="write format binlog to normal sql",metavar='binlog')
p.add_option('-a','--single-tables-file',dest='tb_file',help="grep single table to normal sql file")
p.add_option('-t','--table',dest='table',help='specify table')
#print "\n      ==========================================================================================\n======Author:GuiJiaoQi&XuYou                                                        =======\n======For Example:python ts.py -u user -p password -f mysql-bin.00000x.sql -b binlog_to_sql.sql =======\n      ==========================================================================================\n"
print "\n      ==========================================================================================\n======Author:GuiJiaoQi&XuYou                                                         =======\n======For Example:python ts.py -u user -p password -f mysql-bin.00000x.sql -b binlog_to_sql.sql    =======\n======      :python ts.py -u user -p password -t tb_name -f mysql-bin.00000x.sql -a single_tb_.sql =======\n      ==========================================================================================\n"
(options, arguments) = p.parse_args()
out_in_binlog = options.filename
binlog_to_sql = options.binlog
if str(out_in_binlog) == 'None'  :
p.print_help()
return str(out_in_binlog)+','+str(binlog_to_sql)+','+str(options.user)+','+str(options.password)+','+str(options.socket)+','+str(options.tb_file)+','+str(options.table)
main= main()
if main.split(',')[0] == 'None':
exit();
else:
user=main.split(',')[2]
passwd=main.split(',')[3]
socket=main.split(',')[4]
if socket == 'None':
conn1=MySQLdb.connect(host="192.168.63.66",user=user,passwd=passwd,port=3306,db="information_schema",read_default_file="/etc/my.cnf",charset="utf8")
else:
conn1=MySQLdb.connect(host="192.168.63.66",user=user,passwd=passwd,port=3306,db="information_schema",read_default_file="/etc/my.cnf",charset="utf8",unix_socket=socket)
cursor=conn1.cursor()
def get_columns(db_tb_name):
try:
db_name_1 = db_tb_name.split('|')[0]
tb_name_1 = db_tb_name.split('|')[1]
cursor.execute('select group_concat(column_name) cls from information_schema.COLUMNS where table_schema="%s" and table_name="%s"' %(db_name_1,tb_name_1))
row1 = cursor.fetchall()
cls = str(row1[0])
cls = cls.decode('utf-8');
#list_cls =  cls.replace('u','').replace('\'','').replace('(','').replace(',)','').split(',')
list_cls = cls.replace("u'",'').replace('\'','').replace('(','').replace(',)','').split(',')
len_li = len(list_cls)
return list_cls
except Exception as e:
print e
def _get_table_name(i):
try:
if i.find('Table_map:')!=-1:
l = i.index('Table_map')
tb_name = i[l::].split(' ')[1].replace('`','').split('.')[1]
db_name = i[l::].split(' ')[1].replace('`','').split('.')[0]
db_tb_name= db_name+'|'+tb_name
return  db_tb_name
except Exception as e:
print e
binlog_to_sql =  main.split(',')[1]
out_in_binlog = main.split(',')[0]
single_table_name = main.split(',')[5]
table_name = main.split(',')[6]
res = open(binlog_to_sql,'w')
fh = open(out_in_binlog,'r')
tbl=open(single_table_name,'w')
lines = ''
while True:
i = fh.readline()
if i == '':
break
i = i.decode('utf-8')
current = ''
if i.find('Table_map:') !=-1:
lineCls = _get_table_name(i)
list_cls = get_columns(lineCls)
if lineCls.find("mysql") != -1:
continue;
if '###' in i:
if i.find('###   @') !=-1 and len(list_cls) > 0:
if lineCls.find("mysql") != -1:
print(lineCls)
continue;
#print("-----------------------")
#print(lineCls)
#print(list_cls)
#print(len(list_cls))
#print(i)
ix1 = i.replace('###   @','').split('=')[0]
#print(int(ix1)-1)
if len(list_cls) < int(ix1) :
continue
ixx1 =  i.replace('###   @'+ str(ix1), list_cls[int(ix1)-1]),
ixx1 = ''.join(ixx1);
if (int(ix1) == len(list_cls)):
line = re.sub('\(\d+\)',' ',ixx1)
line =  re.sub('/\*.*/','',line)
current += line.replace('\n','').replace("('",'').replace("\\n'",'').replace(',)','').replace('\\n"','').replace('("','').replace('(255)','').replace('(65535)','')
lines += current
if lines.find('INSERT') !=-1:
lineRe = re.search('INSERT (.*`) SET(.*)',lines)
if not lineRe:
#res.write(lines.encode('utf-8')+"\n")
continue
if lineRe.group(1).encode('utf-8').replace('`','').split('.')[1] == table_name:
tbl.write("INSERT  "+lineRe.group(1).encode('utf-8')+" VALUES("+re.sub('\w+=','',lineRe.group(2).encode('utf-8'))+");\n")
else:
res.write("INSERT  "+lineRe.group(1).encode('utf-8')+" VALUES("+re.sub('\w+=','',lineRe.group(2).encode('utf-8'))+");\n")
elif lines.find('UPDATE') !=-1:
lineRe = re.search('UPDATE (.*`) WHERE(.*) SET(.*)',lines)
if not lineRe:
#res.write(lines.encode('utf-8')+"\n")
continue
if lineRe.group(1).encode('utf-8').replace('`','').split('.')[1] == table_name:
tbl.write("UPDATE "+lineRe.group(1).encode('utf-8')+" SET "+lineRe.group(3).encode('utf-8')+" WHERE "+lineRe.group(2).encode('utf-8').replace(',',' AND ')+";\n")
else:
res.write("UPDATE "+lineRe.group(1).encode('utf-8')+" SET "+lineRe.group(3).encode('utf-8')+" WHERE "+lineRe.group(2).encode('utf-8').replace(',',' AND ')+";\n")
elif lines.find('DELETE') !=-1:
lineRe = re.search('DELETE (.*`) WHERE(.*)',lines)
if not lineRe:
#res.write(lines.encode('utf-8')+"\n")
continue
if lineRe.group(1).encode('utf-8').replace('`','').split('.')[1] == table_name:
tbl.write("DELETE "+lineRe.group(1).encode('utf-8')+" WHERE "+lineRe.group(2).encode('utf-8').replace(',',' AND ')+";\n")
else:
res.write("DELETE "+lineRe.group(1).encode('utf-8')+" WHERE "+lineRe.group(2).encode('utf-8').replace(',',' AND ')+";\n")
else:
res.write(lines+"\n")
else:
line = re.sub('\(\d+\)',' ',ixx1)
line = re.sub('/\*.*/',',',line)
current += line.replace('\n','').replace("('",'').replace("\\n'",'').replace(',)','').replace('\\n"','').replace('("','').replace('(255)','').replace('(65535)','')
else:
current += i.replace('### ','').replace('\n',' ')
else:
res.write(i)
continue
if current.find('INSERT') != -1 or current.find('UPDATE') != -1 or current.find('DELETE') != -1 :
if not lines:
lines += current
continue
lines = current
else:
lines += current
fh.close()
  • 脚本运行命令
python binlog_parse_to_sql.py -u yunwei -p xxxxxxxx  -f 780_11_12.txt -b 780_11_12.sql
  • 把解析后的SQL语句导入从库
mysql -uroot -pxxxx  --default-character-set=utf8  < 780_11_12.sql

至此,大部分数据恢复。

3. 弥补数据出现的问题

小编在使用上面的脚本弥补数据后,大部分的数据(目测恢复了95%))已经弥补回来了,但是要有一小部分数据仍然有问题,这部分数据有问题的原因,可能是:
1. 脚本本身有缺陷,导致解析出来的数据有问题,比如时间戳转时间
2. 有些数据本来在从库上已经更新为最新的了,但是执行回复语句后又把数据刷回原来的样子
所以为了弥补这5%的数据,小编要么使用工具对比主库RDS和从库之间的差异,使数据完成,要么重做数据同步,下面对两个方案进行对比,然后做出最后的选择
1. 对比主从数据是否一致
由于阿里云RDS不提供root用户,并且无法获取RDS的配置文件,所以checksum之类的工具无法使用,那么只能使用mysqlcompare之类的工具,但是由于小编数据库数据量非常大,所以短时间内很难比较出结果,并且不能在有业务的时候做相应的操作。

2. 重做数据同步
重做数据同步能确保主从数据一致,并且在做主从同步的时,重新开启新的虚拟服务器和使用一台本地服务器即可,不影响线上业务。

综合比较后,为了保险起见,小编最后打算重做数据同步。

3. 数据同步架构问题
如最开始架构图所示,由于当前数据同步都是单点,一个出现问题,必然导致系统异常,所以需要重新设计数据同步架构,解决单点问题。

4. 数据同步架构该进

1. 数据同步架构图

2. 架构图说明
* 阿里云数据库RDS自身为主备架构,不存在单点问题
* ECS自建MySQL5.7和RDS之间的同步有两个节点,解决了本次事故中由于ECS自建数据库同步异常导致整条同步链路异常的问题。
* 在移动,联通,电信机房都自建了数据库,并且完成数据同步,即解决了数据库同步单点问题,也解决了网络链路只依靠电信的问题。

5. 重做RDS到虚拟服务器自建数据库主从复制

由于小编购买的RDS为MySQL5.6, 而自建的数据库为MySQL5.7,所以为了避免版本不一致导致同步出现问题,小编最开始使用mysqldump来导出数据,然后通过source命令导入数据,但是小编经过两天的折腾后发现,导入的数据有遗漏,并且容易出问题,所以小编最后决定使用innobackupex来完成数据的恢复,然后再通过upgrate完成升级。
1. 通过RDS控制台获取RDS全量备份

wget -c '全量备份地址'  -O xxxx.tar.gz

2. 安装Percona XtraBackup 工具
通过Percona官网下载XtraBackup工具,并安装.

下载地址: https://www.percona.com/downloads/XtraBackup/LATEST/

3. 下载阿里云数据备份文件解压工具
下载地址:

http://oss.aliyuncs.com/aliyunecs/rds_backup_extract.sh?spm=a2c4g.11186623.2.6.uk68zS&file=rds_backup_extract.sh

4. 将下载的数据备份恢复到本地MySQL数据库中,步骤如下
执行如下命令,解压已下载的数据备份文件

bash rds_backup_extract.sh -f <数据备份文件名>.tar.gz -C  /home/mysql/data(自建数据库data目录)

查看解压后生成的文件

ls -l /home/mysql/data

命令执行成功后,系统会返回如下结果,其中蓝色字体为生成备份文件时RDS实例所包含的数据库。

执行如下命令,恢复解压好的备份文件

innobackupex --defaults-file=/home/mysql/data/backup-my.cnf --apply-log /home/mysql/data

若系统返回如下类似结果,则说明备份文件已成功恢复到本地数据库

修改解压后的文件,确定文件所属为MySQL用户

chown -R mysql:mysql /home/mysql/data

启动MySQL

service mysqld restart

执行如下命令,登录MySQL数据库以验证进程启动成功

mysql -uroot -p

恢复完成后,表mysql.user中不包含在RDS实例中创建的用户,需要新建。在新建用户前,执行如下命令

delete from mysql.db where user<>’root’ and char_length(user)>0;delete from mysql.tables_priv where user<>’root’ and char_length(user)>0;flush privileges;

执行mysql_upgrade升级MySQL数据库表,确保执行都是ok

./bin/mysql_upgrade -u root -p   --force

重启数据库

service mysqld start

获取同步所需的gtid值
从/home/mysql/data/backup-my.cnf 获取同步gtid值

设置同步

reset slave all;
reset master;
set global gtid_purged="从/home/mysql/data/backup-my.cnf  获取gtid值";
CHANGE MASTER TO
MASTER_HOST='RDS域名',
MASTER_USER='repl',
MASTER_PASSWORD='xxxxx',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1,
MASTER_CONNECT_RETRY=10;
start slave;

查看同步是否正常

show slave status \G;  

当出现如下信息后,表示同步正常

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

5. 重做虚拟机自建服务器到本地自建数据库同步

1. 备份数据库

innobackupex --safe-slave-backup --safe-slave-backup-timeout=30 --slave-info --parallel=3 --user=root --password="xxxx" --stream=tar  ./ | gzip > ./`date +%F_%H-%M-%S`.tar.gz

2. 拷贝备份文件
登录本地服务器,远程拷贝备份文件

$ nohup rsync -avP --rsh=ssh 用户@IP地址:/mnt/data_00/dump/备份文件名.tar.gz ./ &
$ fg (输入密码)
$ ctr + z
$ bg

注:使用该方法拷贝数据,会把带宽占用完,需要晚上执行

3. 数据恢复
解压备份文件数据库data目录

tar zxf 备份文件名.tar.gz -C 数据库data目录

恢复数据

innobackupex --defaults-file=/data/msyql/data/backup-my.cnf --user=root --password=fs2134  --apply-log /data/msyql/data/

更改数据文件权限

chown -R mysql.mysql /data/msyql/data/

重启数据库

service mysqld restart

在ECS自建库上创建同步账户并授权

grant replication slave on *.* to 'repl'@'IP地址' identified by 'xxxxx';
flush privileges;

获取同步点
从/data/msyql/data/xtrabackup_info 获取gtid值

设置同步信息

reset slave all;
reset master;
set global gtid_purged="从/data/msyql/data/xtrabackup_info 获取gtid值";
CHANGE MASTER TO
MASTER_HOST='ECS自建库域名或IP',
MASTER_USER='repl',
MASTER_PASSWORD='xxxxx',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1,
MASTER_CONNECT_RETRY=10;
start slave;

查看同步是否正常

show slave status \G;  

当出现如下信息后,表示同步正常

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

参考资料

恢复云数据库MySQL的备份文件到自建数据库

更多内容请关注公众号


来源:本文由云小秘原创撰写,欢迎分享本文,转载请保留出处和链接!

抢沙发

昵称*

邮箱*

网址