阿裡雲RDS與ECS自建庫搭建主從複製

来源:https://www.cnblogs.com/lyw92/archive/2018/08/14/9473779.html
-Advertisement-
Play Games

前言 MySQL5.7殺手級新特性：GTID原理與實戰 具體過程如下: 1 準備工作,申請阿裡雲RDS 2 配置從實例 (slave) 3 查看是否開啟了binlog,及使用的日誌格式 4 將主實例數據全量同步到從實例 (最好下載RDS控制台的備份數據，不要mysqldump)，詳見恢復雲資料庫My ...


前言

最近嘗試在阿裡雲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以上
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 查看是否開啟了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.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: 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

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • mssql sqlserver 驗證整型函數分享
    轉自:http://www.maomao365.com/?p=6227 摘要: 下文將製作一個isnumber驗證整型的函數，供在sql腳本中做數值判斷，如下所示: 例: 實現原理:判斷 是否包含特殊字元，是否全部為0-9的自然數組成 ...
  • mongodb查詢資料庫中某個欄位中的值包含某個字元串的方法
    正則表達式最能解決： 例如： 這裡主要是註意正則表達式要寫對，該轉義的註意轉義，否則報錯。 ...
  • Spark 載入資料庫mysql表中數據進行分析
    1.工程maven依賴包 2.spark載入資料庫中數據 3.spark支持載入多種資料庫，僅需要用戶依賴不同的資料庫驅動包，並且代碼進行微調即可 根據以上java代碼，僅需調整18行，更改驅動載入類即可。 ...
  • hive 學習系列六 hive 去重辦法的思考
    方法1，建立臨時表，利用hive的collect_set 進行去重。 2，方法2， 利用row_number 去重 比如，我有一大堆的表格， 表格內容大多類似，只是有些許差別。 現在的需求是把我要統計所有的表格中，都有哪些欄位，也就是把所有的表格整合成一張大表 則可以利用row_number 進行去 ...
  • Elasticsearch 常用API
    1. Elasticsearch 常用API 1.1.數據輸入與輸出 1.1.1.Elasticsearch 文檔 #在 Elasticsearch 中，術語 文檔 有著特定的含義。它是指最頂層或者根對象, 這個根對象被序列化成 JSON 並存儲到 Elasticsearch 中，指定了唯一 ID。 ...
  • mssql sqlserver 模擬for迴圈的寫法
    轉自:http://www.maomao365.com/?p=6567 摘要: 下文講述sql腳本模擬for迴圈的寫法，如下所示: ...
  • mssql sqlserver 從指定字元串中獲取數字的方法
    轉自:http://www.maomao365.com/?p=6410 摘要: 下文主要分享從指定字元串或列中獲取數字信息，如下所示: 實驗環境:sql server 2000 ...
  • Flink基本概念
    Flink基本概念 1.The history of Flink？ 2.What is Flink？ Apache Flink是一個開源的分散式、高性能、高可用、準確的流處理框架，主要由Java代碼實現，支持實時流(stream)處理和批(batch)處理，批數據只是流數據的一個極限的特例。原生支持 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類