RDS同步到本地数据库从库

时间:2019-05-11 09:48来源:计算机教程
同步检测脚本 为了知道主从的同步情况,自己写了个脚本,查询主从数据库某些表的行数,以及数据大小,最近产生的数据比对。#!/usr/bin/env python# -*- coding: utf-8 -*-# @Author : Lyon Walker#

同步检测脚本

为了知道主从的同步情况,自己写了个脚本,查询主从数据库某些表的行数,以及数据大小,最近产生的数据比对。
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Author  : Lyon Walker
# @Time    : 18/08/11 9:54

import pymysql
import contextlib
import time
from functools import wraps

sql = 'SELECT COUNT(1) tbl_chargingorder from t1;'
sql2 = 'SELECT COUNT(1) tbl_chargingrecord from t1;'
sql3 = 'SELECT COUNT(1) tbl_purchasehistory from t3;'
sql4 = "select concat(round((sum(DATA_LENGTH) SUM(INDEX_LENGTH))/1024/1024/1024,2),'G') size from information_schema.tables where table_schema='dbname';"

# 这里用于计算执行sql的时间
def fn_timer(function):
    @wraps(function)
    def function_timer(*args, **kwargs):
        t0 = time.time()
        result = function(*args, **kwargs)
        t1 = time.time()
        t = float('%.2f' % (t1-t0))
        return t, result
    return function_timer


class MysqlClient:
    def __init__(self, host, port, user, passwd, db):
        self.host = host
        self.port = port
        self.user = user
        self.passwd = passwd
        self.db = db

    @contextlib.contextmanager
    def mysql(self):
        conn = pymysql.connect(host=self.host,
                               port=self.port,
                               user=self.user,
                               passwd=self.passwd,
                               db=self.db)
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        try:
            yield cursor
        finally:
            conn.commit()
            cursor.close()
            conn.close()

    @fn_timer
    def exec_sql(self, sql):
        with self.mysql() as cursor:
            cursor.execute(sql)
            res = cursor.fetchall()
            return res

my_m = MysqlClient('10.9.2.100', 3306, 'root', 'xxx', 'dbname')
row_m1 = my_m.exec_sql(sql)
row2_m2 = my_m.exec_sql(sql2)
row3_m3 = my_m.exec_sql(sql3)
row4_m4 = my_m.exec_sql(sql4)

my_s = MysqlClient('10.9.2.101', 3306, 'root', 'xxx', 'dbname')
row_s1 = my_s.exec_sql(sql)
row2_s2 = my_s.exec_sql(sql2)
row3_s3 = my_s.exec_sql(sql3)
row4_s4 = my_s.exec_sql(sql4)


print(row_m1   row_m2   row_m3   row_m4)
print(row_s1   row_s2   row_s3   row_s4)


测试了一下,结果令人失望,行数不一致,数据大小也不一致,只是最近产生的数据是同步一致的,查看slave状态也很正常,到这里经提醒看看是不是备份数据的时候出了岔子,去RDS控制台看了下,阿里云是有备份的(采用的是物理备份),根据阿里云提供的备份恢复文档恢复云数据库MySQL的备份文件到自建数据库

  • 下载阿里云控制台备份的数据
  • 安装Percona-XtraBackup

    cd /usr/local/src/
    wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/source/tarball/percona-xtrabackup-2.4.12.tar.gz
    yum install cmake gcc gcc-c   libaio libaio-devel automake autoconf bison libtool ncurses-devel libgcrypt-devel libev-devel libcurl-devel
    cmake -DBUILD_CONFIG=xtrabackup_release -DWITH_MAN_PAGES=OFF && make -j4
    
  • cmake报错

    CMake Error at cmake/boost.cmake:81 (MESSAGE): You can download it with -DDOWNLOAD_BOOST
    
  • 下载boost 重新cmake

    mkdir -p /usr/local/boost
    cd /usr/local/boost
    wget http://www.sourceforge.net/projects/boost/files/boost/1.59.0/boost_1_59_0.tar.gz
    tar zxvf boost_1_59_0.tar.gz
    cd /usr/local/src/
    cmake -DBUILD_CONFIG=xtrabackup_release -DWITH_MAN_PAGES=OFF -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/boost && make -j4
    make install
    ln -s /usr/local/xtrabackup/bin/innobackupex /usr/bin/innobackupex
    
  • 删除之前的库以及一些其他的信息(/var/lib/mysql下),删除之前的主从信息。

use mysql;
drop table if exists innodb_index_stats;
drop table if exists innodb_table_stats;
drop table if exists slave_master_info;
drop table if exists slave_relay_log_info;
drop table if exists slave_worker_info;
  • 执行脚本导入

    ./rds_backup_extract.sh -f hins2950629_data_20180813031740.tar -C /var/lib/mysql
    innobackupex --defaults-file=/var/lib/mysql/backup-my.cnf --apply-log /var/lib/mysql
    
    vi /home/mysql/data/backup-my.cnf
    注释掉 保存
    #innodb_fast_checksum
    #innodb_page_size
    #innodb_log_block_size
    
    chown -R mysql:mysql /home/mysql/data
    systemctl restart mysql
    mysql -uroot # 登录
    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; # 新建用户前,执行这条命令
    
    # 设置root密码
    use mysql;
    UPDATE user SET Password = PASSWORD('newpass') WHERE user = 'root';
    GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY 'newpass' WITH GRANT OPTION;
    FLUSH PRIVILEGES;
    
  • 重新设置主从

  • 过程中还有很多其他小错误,这里就不一一赘述了
  • 下载解压脚本 rds_backup_extract.sh
  • 下载恢复工具 Percona-XtraBackup
  • sh rds_backup_extract.sh -f hins1438123_xtra_20160912032142.tar.gz -C /home/mysql/data
  • /home/backup/percona-xtrabackup-2.4.4-Linux-x86_64/bin/innobackupex --defaults-file=/home/mysql/data/backup-my.cnf --apply-log /home/mysql/data
  • 编辑mysql配置文件,开启gtid

前言

最近尝试在阿里云RDS与ECS自建库之间搭建主从复制,主要是想用于备份数据,所以对于同步一致性要求不是很高.模拟了几次,遇到的一些坑记录在这里,使用的是基于GTID的主从服务,关于GTID:
MySQL GTID是在传统的mysql主从复制的基础之上演化而来的产物,即通过UUID加上事务ID的方式来确保每一个事物的唯一性。这样的操作方式使得我们不再需要关心所谓的log_file和log_Pos,只是简单的告诉从库,从哪个服务器上去找主库就OK了。简化了主从的搭建以及failover的过程,同时比传统的复制更加安全可靠。由于GTID是连续没有空洞的,因此主从库出现数据冲突时,可以通过注入空事物的方式进行跳过

MySQL5.7杀手级新特性:GTID原理与实战

/usr/local/mysql/bin/mysqld_safe --defaults-file=/home/mysql/data/slave-my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/home/mysql/data &
需要先安装mysql5.6以上
https://segmentfault.com/n/1330000014166676#articleHeader8

--$ systemctl stop mysqld
--$ vim /etc/my.cnf

[mysqld]
server-id=1629281463  # 从实例的id,不能与master的id相同
port=3306
log-bin-trust-function-creators=1 # 设置为1,MySQL不会对创建存储函数实施限制

slave-skip-errors = 1032,1062,1007,1050  # 
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin=/var/lib/mysql/mysql-bin
expire_logs_days=10 # 控制binlog日志文件保留时间
max_binlog_size=100M             
replicate-ignore-db=mysql   # 不需要同步的库            
replicate-ignore-db=information_schema    
replicate-ignore-db=performance_schema   
#GTID
gtid_mode=on
enforce_gtid_consistency=on
binlog_format=row     # 设置日志格式为row          
log-slave-updates=1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

--$ systemctl start mysqld

3、恢复备份到内网测试机

具体过程如下:

  • 上面的报错有两种解决方法:
    • 设置 master_auto_position=0 并指定 binlog 文件和 pos 位置开始复制

5 登录从实例(slave),设置同步选项

>>> stop slave
# 注意!!! 当使用 MASTER_AUTO_POSITION 参数的时候,MASTER_LOG_FILE,MASTER_LOG_POS参数不能使用
>>> stop slave;
>>> change master to 
master_host='yourhost', 
master_port=3306, 
master_user='xxx', 
master_password='yyy',
master_auto_position = 1;
>>> start slave
cd /home/mysql/data  
cp backup-my.cnf slave-my.cnf
vim slave-my.cnf 
[mysqld]
# from rds backup-my.cnf
innodb_checksum_algorithm=innodb
innodb_data_file_path=ibdata1:200M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=524288000
innodb_undo_directory=.
innodb_undo_tablespaces=0
# need for slave 
server-id = 17
master-info-repository = file
relay-log-info_repository = file
binlog-format = ROW
gtid-mode = ON
enforce-gtid-consistency = true
log-bin = hostname-bin
relay-log = hostname-relay-bin
log-slave-updates

参考文章

http://www.cnblogs.com/kevingrace/p/6256603.html
  • 启动mysqld

6 检查同步结果

>>> show slave statusG;
查看系统返回信息中 Slave_IO_Running 和 Slave_SQL_Running 的状态是否为 Yes
  • 尝试 master_auto_position=1 让主从自行寻找开始复制的pos

3 查看是否开启了binlog,及使用的日志格式

>>> show variables like '%binlog_format%';

1. Row
日志中会记录成每一行数据被修改的形式,然后在 slave 端再对相同的数据进行修改。
2. Statement
每一条会修改数据的 SQL 都会记录到 master 的 bin-log 中。slave 在复制的时候 SQL 进程会解析成和原来 master 端执行过的相同的 SQL 再次执行
  • 清除slave信息

4 将主实例数据全量同步到从实例 (最好下载RDS控制台的备份数据,不要mysqldump),详见恢复云数据库MySQL的备份文件到自建数据库

# GRANT ALL PRIVILEGES ON *.* TO 'test'@'%'IDENTIFIED BY 'passwd' WITH GRANT OPTION;
# CREATE DATABASE sakila DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
# mysqldump -uname -hmasterhost.mysql.rds.aliyuncs.com -p --databases exampledb > exampledb.sql
# mysqldump -uname -p < exampledb.sql
sql> change master to
master_host=’rdsxfjwiofjwofe.mysql.rds.aliyuncs.com’,
master_user=’user_name’,master_port=3306,master_password=’xxxxx’,
master_auto_position=1;
# 结果 start slave 报错
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

2 配置从实例 (slave)

参考文档:
RDS for MySQL 备份文件恢复到自建数据库
如何将RDS的数据同步到本地自建数据库
MySQL 5.6版本GTID复制异常处理一例

遇到的问题

ERROR 1776 (HY000): Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active.
当使用 MASTER_AUTO_POSITION 参数的时候,MASTER_LOG_FILE,MASTER_LOG_POS参数不能使用

Last_SQL_Errno: 1007
Last_SQL_Error: Error 'Can't create database 'abu'; database exists' on query. Default database: 
解决方法:在从库的/etc/my.cnf里添加如下代码
[mysqld]
slave-skip-errors = 1032,1062,1007,1050

使用change master to master_host = 'xxx', master_port = xxx, master_user = 'xxx', master_password='xxx', master_auto_position = 12866271;
时报错:
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'


解决:
这个应该是由于你在主库上执行过purge binary logs,然后当从库change master的时候,却要执行那些事务。
你可以在主库上先查找哪些gtid被purge了。
show global variables like 'gtid_purged';
然后拿着这个value,去从库上依次
stop slave;
reset mater;
set global gtid_purged = 'xxx'; # xxx是你主库上查到的value。
start slave;
这样能跳过执行被主库已经purge的事务了。
  • 内网中转?数据量不大,直接下载
  • 下载数据备份(外网下载链接) wget -c "https://rdslog-st.oss-cn-shenzhen.aliyuncs.com/xxx" -O xxx.tar.gz

7 测试主从同步

在主库上执行UPDATE INSERT等操作,然后在从库上查看是否同步了过来.我测试了几次,同步没出现什么问题,可能是由于数据量比较小.

1 准备工作,申请阿里云RDS

a 登录 RDS 控制台,选择目标实例。
b 配置从实例读取数据使用的只读账号和授权数据库。
c 将从实例的 IP 地址加入主实例的 IP 白名单中 [同一地域 则可以内网IP] 
d 登录主实例 [如果外部无法登录 白名单添加0.0.0.0/0][或者提工单]
e 查询主实例的 server-id [show variables like '%server_id%';] # 记下这个id,不能与slave的相同

编辑:计算机教程 本文来源:RDS同步到本地数据库从库

关键词: