天天看點
傳回

MySQL5.7線上無鎖添加SLAVE節點—xtrabackup線上備份方式

作者：老哥講資料庫

概述

MySQL發展至今，在企業級高可用方面進化出了兩大架構，一個是InnoDB Cluster架構， 另一個是基于binlog的Master-Slave同步架構。即使在MySQL 8.0中推出了redo log歸檔等多個飛躍式改進的情況下，Master-Slave同步依然不可替代。

此文檔細緻講述如何為MySQL 5.7 Master節點線上無鎖添加Slave節點。

一：環境資訊

Master節點資訊：

MySQL5.7線上無鎖添加SLAVE節點—xtrabackup線上備份方式

Master節點配置資訊

Slave節點資訊：

MySQL5.7線上無鎖添加SLAVE節點—xtrabackup線上備份方式

Slave節點配置資訊

二：從庫準備

從庫節點安裝RHEL7.4，與主庫節點保持一緻，安裝後關鍵配置如下：

2.1 修改limits

編輯/etc/security/limits.conf 檔案，添加如下配置内容：

mysql soft nproc 65536
mysql hard nproc 65536
mysql soft nofile 65535
mysql hard nofile 65535
* soft memlock unlimited
* hard memlock unlimited

編輯/etc/security/limits.d/20-nproc.conf 檔案，添加如下内容：

* soft nproc 65536

2.2 關閉防火牆

systemctl stop firewalld
systemctl disable firewalld
setenforce 0

2.3 安裝MySQL SLAVE 執行個體

從庫節點安裝MySQL 5.7.29，配置參數與主庫節點保持一緻，注意server-id與主庫不一緻。

  • 從官網下載下傳MySQL 5.7.29，下載下傳位址：https://downloads.mysql.com/archives/community/
MySQL5.7線上無鎖添加SLAVE節點—xtrabackup線上備份方式
  • 解壓MySQL 
tar xzvf mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar
  • 安裝MySQL

rpm -ivh mysql-community-common-5.7.29-1.el7.x86_64.rpm

rpm -ivh mysql-community-libs-5.7.29-1.el7.x86_64.rpm

rpm -ivh mysql-community-devel-5.7.29-1.el7.x86_64.rpm

rpm -ivh mysql-community-client-5.7.29-1.el7.x86_64.rpm

rpm -ivh mysql-community-server-5.7.29-1.el7.x86_64.rpm

  • 配置MySQL

在Slave節點執行以下指令取得主庫my.cnf

rsync -avh IMMASTER:/etc/my.cnf   /etc/

編輯my.cnf 檔案,修改server-id與log_slave_updates

server-id=101  ##此值不能與Master節點的值相同
log_slave_updates=ON

根據my.cnf内容建立對應的資料目錄

mkdir /data/mysql -p
mkdir /data/tmp -p
chown mysql.mysql data -R
chmod 755 data -R

初始化mysql

mysqld --initialize --user=mysql --datadir=/data/mysql

取得随機密碼

grep password /data/mysql/mysqld.log
  • 啟動mysql 
systemctl start mysqld

登入修改使用者資訊

mysql -uroot -p (此處為上面取得的随機密碼)           
mysql> set password='LaoGeDB123!';
mysql> grant all on *.* to root@localhost identified by 'LaoGeDB123!';
mysql> grant all on *.* to root@'%' identified by 'LaoGeDB123!';
mysql> flush privileges;

三：工具準備

percona公司開發的xtrabackup工具包是一款可以對MySQL做資料庫級别實體備份的利器，其在備份過程中既可以保持事務一緻性，又不會對資料庫造成鎖表，當然前提是InnoDB引擎資料表，對于MyISAM引擎的資料表無法避免鎖表。

3.1 工具下載下傳

下載下傳方式

cd /root/Downloads
wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.20/binary/tarball/percona-xtrabackup-2.4.20-Linux-x86_64.el7.tar.gz

3.2 部署安裝

cd /root/Downloads 
tar xzvf percona-xtrabackup-2.4.20-Linux-x86_64.el7.tar.gz
mv ./install/percona-xtrabackup-2.4.20-Linux-x86_64  /usr/local/
cd /usr/local/
ln -s percona-xtrabackup-2.4.20-Linux-x86_64.el7.tar.gz  xtrabackup

3.3 配置環境變量

編輯/etc/profile.d/xtra.sh 檔案，添加如下内容:

PATH=$PATH:/usr/local/xtrabackup/bin
export PATH

source檔案讓配置生效：

source /etc/profile.d/xtra.sh

此時可以直接執行xtrabckup指令了

四：核心流程

4.1 主庫前置條件檢查

  • 檢查MySQL版本
MySQL5.7線上無鎖添加SLAVE節點—xtrabackup線上備份方式
  • 檢查MySQL預設資料引擎
MySQL5.7線上無鎖添加SLAVE節點—xtrabackup線上備份方式
MySQL5.7線上無鎖添加SLAVE節點—xtrabackup線上備份方式
  • 檢查binlog格式為ROW

此處binlog設定為ROW是為了采用GTID同步方式。

MySQL5.7線上無鎖添加SLAVE節點—xtrabackup線上備份方式
  • 确認binlog開啟

此處紅色框表示binlog開啟，藍色框表示我同時開啟了GTID。

MySQL5.7線上無鎖添加SLAVE節點—xtrabackup線上備份方式

4.2 主庫線上無鎖備份

  • 使用xtrabackup對資料庫做線上無鎖備份

以下是Master節點中資料庫，其中紅色框為系統資料庫，藍色框為生産業務資料庫。

MySQL5.7線上無鎖添加SLAVE節點—xtrabackup線上備份方式

主庫整體備份，包括系統資料庫(mysql、performance_schema)，指令如下:

innobackupex --defaults-file=/etc/my.cnf --user=root \
--password=Lenovo123 –parallel=4  \
/data/dbbackup
  • 主庫傳輸備份資料到從庫節點 
rsync -avh  /data/dbbackup  IMSLAVE:/data/dbbackup
  • 看一下備份資料究竟是什麼内容
MySQL5.7線上無鎖添加SLAVE節點—xtrabackup線上備份方式

1). backup-my.cnf是主庫中關鍵配置參數，xtrabackup在APPLY階段會根據它生成ib_logfile以及ib_tmp等關鍵資料檔案;

2). ibdata1是共享表空間;

3). tpcc是生産業務資料庫,此狀态下tpcc中的資料檔案是不一緻的;

4). xtrabackup_binlog_info中記錄了備份後的binlog資訊，是做從庫同步的關鍵内容;

5). xtrabackup_logfile則是備份過程中的redo日志，因為是無鎖表備份，是以在備份過程中所有的事務修改都被記錄在這個檔案中。

4.3 從庫還原資料庫備份

  • Apply Redo Log資料到資料檔案

正如上面所述，備份所得資料檔案本身是不一緻的，需要通過redo log來apply事務到資料檔案才能達到資料一緻，也就是把xtrabackup_logfile中的事務重做一遍，進而recovery ibdata1和tpcc中的資料檔案到一緻性狀态。

在從庫節點上apply redo log到資料檔案，指令如下：

cd /data/dbbackup
innobackupex --apply-log 2020-05-27_21-09-50

Apply過程中會同時根據backup-my.cnf建立ib_logfile、ibtmp。完成後狀态如下：

MySQL5.7線上無鎖添加SLAVE節點—xtrabackup線上備份方式

上圖紅色框内是新生産的檔案，藍色框内為APPLY過redo後的共享表空間檔案，當然tpcc庫内是ibd資料檔案也同樣被APPLY redo。

至此，主庫的資料庫一緻性快照我們已經拿到了，那麼接下來就是把這個快照加載到從庫節點資料庫執行個體中。

  • Copy Back資料庫到資料目錄

在從庫執行以下指令，完成資料庫copy back

rm -rf  /data/mysql/*
innobackupex --defaults-file=/etc/my.cnf  \
--socket=/data/mysql/mysql.sock --user=root --password=LaoGeDB123!  \
--copy-back /data/dbbackup/2020-05-27_21-09-50
  • 啟動從庫執行個體

啟動前要将/data/mysql目錄下的檔案屬主修改為mysql

chown mysql.mysql /data/mysql -R
systemctl start mysqld

4.4 配置主從

配置主從可以采用傳統的binlog_file+binlog_position的方式，也可以采用MySQL 5.6開始引入的GTID方式。此文檔中，我們先采用傳統的方式配置主從同步，再切換為GTID方式。

  • 在主庫上執行以下SQL指令建立同步使用者并賦予其最低權限，其中192.168.11.62為從庫節點IP： 
mysql> CREATE USER 'repl'@'192.168.11.62' IDENTIFIED BY 'LaoGeDB123!';
mysql> GRANT REPLICATION SLAVE ON *.*TO 'repl'@'192.168.11.62';
  • 在從庫上執行以下指令清理本地binlog： 
mysql> RESET MASTER;
mysql> RESET SLAVE ALL;
  • 在從庫上建立與主庫的主從同步關系

首先，檢視xtrabackup資料備份中的xtrabackup_binlog_info檔案，此檔案中記錄了三個重要資訊，分别是：

1). 備份主庫時binlog檔案名--下圖中紅色框内标記的值

2). 備份主庫時最後一個事務ID--下圖中黃色框内标記的值

3).備份主庫時最後一個事務的GTID--下圖中藍色框内标記的值

MySQL5.7線上無鎖添加SLAVE節點—xtrabackup線上備份方式

然後，根據以上三個值建立主從同步

如果要使用傳統binlog_file+binlog_position同步模式,則可以在從庫上執行以下指令：

mysql> CHANGE MASTER TO
MASTER_HOST='192.168.11.61',
MASTER_USER='repl',
MASTER_PASSWORD='LaoGeDB123!',
MASTER_LOG_FILE='mysql-bin.000006',
MASTER_LOG_POS=565400722;

如果要使用binog_file+GTID同步模式，則可以在從庫上執行以下指令：

mysql> SET GLOBAL GTID_PURGED='7a62a100-a00b-11ea-848c-e04f43073594:1-23129';
mysql > CHANGE MASTER TO
MASTER_HOST='192.168.11.61',
MASTER_USER='repl',
MASTER_PASSWORD='LaoGeDB123!',
MASTER_AUTO_POSTION= 1;

最後，在從庫上執行以下指令啟動slave的IO_thread和SQL_thread

mysql> START SLAVE;
  • 在從庫上執行以下指令檢視slave同步狀态 
mysql> SHOW SLAVE STATUS\G
MySQL5.7線上無鎖添加SLAVE節點—xtrabackup線上備份方式

IO_thread和SQL_thread都已啟動，且已經sync master的binlog到mysql-bin.000008，驗證資料一緻性後證明資料同步正常，傳統同步模式資料庫主從建立完成。

五：傳統POS與GTID切換

GTID同步模式更符合全局事務唯一性的概念，一個事務在主從中的事務ID是全局唯一的，而要使用GTID同步模式，需要具備如下2個前提：

1). 主庫執行個體binlog_format=ROW

2). 主從庫均開啟GTID

下面我們就将上面的傳統同步模式切換為GTID同步模式。

  • 開啟強一緻性檢測參數 
mysql> SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;
  • 開啟GTID_MODE狀态

檢查GTID_MODE是否為ON，若不是則需要在mysql主庫執行個體和從庫執行個體上設定GTID_MODE為ON，其GTID_MODE需要由低到高逐漸推進。

首先，檢查狀态

mysql>show variables like '%gtid%';
MySQL5.7線上無鎖添加SLAVE節點—xtrabackup線上備份方式

然後，開啟GTID_MODE

mysql> SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
mysql >SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
mysql >SET @@GLOBAL.GTID_MODE = ON;
MySQL5.7線上無鎖添加SLAVE節點—xtrabackup線上備份方式
  • 在從庫上停止slave同步 
mysql> STOP SLAVE;
mysql> RESET SLAVE ALL;
  • 在從庫上檢視此時slave端的GTID值，此值中23130-23133很重要，後面建立同步會用到，這裡要記錄好。 
mysql> SHOW MASTER STATUS\G
MySQL5.7線上無鎖添加SLAVE節點—xtrabackup線上備份方式
  • 在從庫上清空GTID_EXECUTED狀态 
mysql> RESET MASTER;
mysql> SHOW MASTER STATUS\G
MySQL5.7線上無鎖添加SLAVE節點—xtrabackup線上備份方式
  • 在從庫設定GTID_PURGED，此值要用到在上面記錄的23130-23133這個值。 
mysql>show global variables like ‘%gtid%’ ; --此時為空
MySQL5.7線上無鎖添加SLAVE節點—xtrabackup線上備份方式
mysql> SET GLOBAL GTID_PURGED='7a62a100-a00b-11ea-848c-e04f43073594:1-23133';
MySQL5.7線上無鎖添加SLAVE節點—xtrabackup線上備份方式
  • 在從庫建立主從同步關系 
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.11.61',
MASTER_USER='repl',
MASTER_PASSWORD='LaoGeDB123!',
MASTER_AUTO_POSITION =1;           
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G
MySQL5.7線上無鎖添加SLAVE節點—xtrabackup線上備份方式

IO_thread和SQL_thread都已啟動，且已經sync master的binlog到mysql-bin.000012，驗證資料一緻性後證明資料同步正常，傳統同步模式資料庫主從切換為GTID模式完成。

一切正常，至此MySQL線上無鎖添加SLAVE節點完成。

六：延伸讨論

6.1 前文收尾

MySQL資料庫線上無鎖表添加slave節點，關鍵在于主庫的線上無鎖備份，而這是建立在InnoDB引擎之上，對于MyISAM引擎則無法達到無鎖備份。

MySQL的線上無鎖表備份有兩種方式:

1). 實體備份--percona公司的xtrabackup工具與mysql官方的mysqlbackup工具

2). 邏輯備份--mysql原生的mysqldump或mysqlpump工具

  • 本文檔中講述的percona公司開發的xtrabackup工具集方式

xtrabackup是基于資料檔案級别的實體備份，是以在體量較大的資料庫上強烈建議使用xtrabackup。

  • MySQL官方的企業級備份工具MySQL Enterprise Backup

MySQL Enterprise Backup與Percona xtrabackup有同樣的功能，使用方式與工作原理也幾乎一樣，後面我會單獨寫文章講解如何使用MySQL Enterprise Backup線上無鎖添加slave節點。

  • 采用MySQL原生自帶的mysqldump或mysqlpump工具方式

mysqldump要想達到無鎖備份資料庫則要啟用single-transaction，它在備份過程中會開啟一個事務，并将資料庫隔離級别設定為可重複讀，這保證了事務一緻性，也就保證了資料一緻性。對于體量較小的資料庫則可以使用mysqldump。

mysqlpump是mysqldump的一個衍生品，在MySQL 5.7中開始出現的工具，與mysqldump一樣都屬于邏輯備份，它最主要的特點是增加了并行備份資料庫和資料庫中的對象的功能，加快備份過程，并且可以靈活選擇要備份的資料庫schema。

關于如何使用mysqldump和mysqlpump線上無鎖添加slave節點的不再讨論，這種方式太不友好。

6.2 主從同步先天缺陷

MySQL 5.7對GTID做了大量改進，相對于MySQL 5.6已經有了非常大的提高，且在MySQL 8.0中得到進一步增強，這極大改善了MySQL資料庫主從同步的靈活性和擴充性。

然而，無論Oracle對MySQL做出了多大的改進，迄今為止，MySQL主從同步架構與生俱來的緻命缺陷從來都沒有得到過修複，其缺陷如下：

  • 主庫并行而從庫串行

盡管現在已經添加了從庫并行解析relay log以及replay log的功能，但其性能依然低下，串行化的本質未變。

  • 同步基于binlog

基于binlog的同步其本質上是邏輯同步，這導緻了binlog在從庫上需要再做一次SQL解析、優化器排程、SQL Plan、多次回盤取資料、索引讀取與更新、排序、重新整理redo、占用undo、最後flush的全套流程。每條SQL都必須跟在主庫上一樣被再次執行一遍，消耗大量資源。

以上兩點則會導緻主從同步延遲頻發、從庫IO抖動、CPU負載升高，這一切都增加了從庫的易碎性。

Oracle在MySQL 8.0.18以及之後的版本中添加了redo log歸檔功能，這就類似Oracle資料庫的redo log歸檔，這種特性讓MySQL實作類似Oracle實體Active Dataguard的主從同步更近了一步，也許将來Oracle真的會把MySQL實體主從同步功能帶來，否則它做這個redo log歸檔的意義是什麼呢？

作者：老哥講資料庫

簡介：資料庫進階架構師，oracle 11g OCM認證，MySQL 5.7 & 8.0 OCP認證。

原創文章，轉載請注明來源。

備份方式 xtrabackup backup mysql back SQL5 ABAC
上一篇: Windows 鍵盤 F1~F12 功能鍵使用指南
下一篇: 滑動頁面時的控件設計規範——吸底&錨點

繼續閱讀