前言
最近嘗試在阿里雲RDS與ECS自建庫之間搭建主從複製,主要是想用於備份資料,所以對於同步一致性要求不是很高.模擬了幾次,遇到的一些坑記錄在這裡,使用的是基於GTID的主從服務,關於GTID:
MySQL GTID是在傳統的mysql主從複製的基礎之上演化而來的產物，即通過UUID加上事務ID的方式來確保每一個事物的唯一性。這樣的操作方式使得我們不再需要關心所謂的log_file和log_Pos，只是簡單的告訴從庫，從哪個伺服器上去找主庫就OK了。簡化了主從的搭建以及failover的過程，同時比傳統的複製更加安全可靠。由於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以上
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 mysqld3 檢視是否開啟了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.rds.aliyuncs.com -p --databases exampledb > exampledb.sql
# mysqldump -uname -p < exampledb.sql5 登入從例項(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 slave6 檢查同步結果
>>> show slave statusG;
檢視系統返回資訊中 Slave_IO_Running 和 Slave_SQL_Running 的狀態是否為 Yes7 測試主從同步
在主庫上執行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: 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的事務了。
同步檢測指令碼
為了知道主從的同步情況，自己寫了個指令碼，查詢主從資料庫某些表的行數，以及資料大小，最近產生的資料比對。
#!/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; - 重新設定主從
- 過程中還有很多其他小錯誤,這裡就不一一贅述了
參考文章
http://www.cnblogs.com/kevingrace/p/6256603.html