前言
最近尝试在阿里云RDS与ECS自建库之间搭建主从复制,主要是想用于备份数据,所以对于同步一致性要求不是很高.模拟了几次,遇到的一些坑记录在这里,使用的是基于GTID的主从服务,关于GTID:MySQL GTID是在传统的mysql主从复制的基础之上演化而来的产物，即通过UUID加上事务ID的方式来确保每一个事物的唯一性。这样的操作方式使得我们不再需要关心所谓的log_file和log_Pos，只是简单的告诉从库，从哪个服务器上去找主库就OK了。简化了主从的搭建以及failover的过程，同时比传统的复制更加安全可靠。由于GTID是连续没有空洞的，因此主从库出现数据冲突时，可以通过注入空事物的方式进行跳过
MySQL5.7杀手级新特性：GTID原理与实战
具体过程如下:
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的相同
2 配置从实例 (slave)
需要先安装mysql5.6以上
/n/1330000014166676#articleHeader8
--$ systemctl stop mysqld--$ vim /etc/f[mysqld]server-id=1629281463 # 从实例的id,不能与master的id相同port=3306log-bin-trust-function-creators=1 # 设置为1，MySQL不会对创建存储函数实施限制slave-skip-errors = 1032,1062,1007,1050 # datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socklog-bin=/var/lib/mysql/mysql-binexpire_logs_days=10 # 控制binlog日志文件保留时间max_binlog_size=100M replicate-ignore-db=mysql # 不需要同步的库 replicate-ignore-db=information_schema replicate-ignore-db=performance_schema #GTIDgtid_mode=onenforce_gtid_consistency=onbinlog_format=row# 设置日志格式为rowlog-slave-updates=1# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# Recommended in standard MySQL setupsql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid--$ systemctl start mysqld
3 查看是否开启了binlog,及使用的日志格式
>>> show variables like '%binlog_format%';
1. Row日志中会记录成每一行数据被修改的形式，然后在 slave 端再对相同的数据进行修改。2. Statement每一条会修改数据的 SQL 都会记录到 master 的 bin-log 中。slave 在复制的时候 SQL 进程会解析成和原来 master 端执行过的相同的 SQL 再次执行
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. -p --databases exampledb > exampledb.sql# mysqldump -uname -p < exampledb.sql
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
6 检查同步结果
>>> show slave status\G;查看系统返回信息中 Slave_IO_Running 和 Slave_SQL_Running 的状态是否为 Yes
7 测试主从同步
在主库上执行UPDATE INSERT等操作,然后在从库上查看是否同步了过来.我测试了几次,同步没出现什么问题,可能是由于数据量比较小.
遇到的问题
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: 1007Last_SQL_Error: Error 'Can't create database 'abu'; database exists' on query. Default database: 解决方法：在从库的/etc/f里添加如下代码[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: 1236Last_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的事务了。
同步检测脚本
为了知道主从的同步情况，自己写了个脚本，查询主从数据库某些表的行数，以及数据大小，最近产生的数据比对。#!/usr/bin/env python# -*- coding: utf-8 -*-# @Author : Lyon Walker# @Time : 18/08/11 9:54import pymysqlimport contextlibimport timefrom functools import wrapssql = '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, resultreturn function_timerclass MysqlClient:def __init__(self, host, port, user, passwd, db):self.host = hostself.port = portself.user = userself.passwd = passwdself.db = db@contextlib.contextmanagerdef 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 cursorfinally:mit()cursor.close()conn.close()@fn_timerdef exec_sql(self, sql):with self.mysql() as cursor:cursor.execute(sql)res = cursor.fetchall()return resmy_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 /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 /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下)
+ 执行脚本导入
“`
./rds_backup_extract.sh -f hins2950629_data_0813031740.tar -C /var/lib/mysql
innobackupex –defaults-file=/var/lib/mysql/backup-f –apply-log /var/lib/mysql
vi /home/mysql/data/backup-f
注释掉 保存
#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;
“`
+ 重新设置主从
+ 过程中还有很多其他小错误,这里就不一一赘述了
参考文章
/kevingrace/p/6256603.html
如果觉得《阿里云RDS与ECS自建库搭建主从复制》对你有帮助，请点赞、收藏，并留下你的观点哦！
