初識MariaDB之3——MariaDB備份與還原

一、基礎(chǔ)知識

數(shù)據(jù)的重要性無需多說，需要強調(diào)的是備份不是目的，能恢復(fù)才是目的，以下就Mariadb的一些概念進行說明。

1.存儲引擎的分類

Mariadb5.1以后的版本，默認使用支持事務(wù)的InnoDB存儲引擎，之前的MyISAM存儲引擎不支持事務(wù)，所謂事務(wù)型存儲引擎，必須滿足ACID原則：

A：atom（原子性），即一個事務(wù)中所有的操作必須全部完成執(zhí)行或不執(zhí)行

C：consistent（一致性），一個事務(wù)從開始到結(jié)束狀態(tài)必須保持一致

I：isolation（隔離性），相同的數(shù)據(jù)不允許多個事務(wù)同時訪問

D：durability （持久性），一經(jīng)修改永久保存

2.事務(wù)隔離級別

InnoDB存儲引擎分為4種隔離級別：

read-uncommitted：讀未提交，可以讀取其他會話未提交的事務(wù)，稱為臟讀

read-committed：讀提交，可以讀取其他會話已提交的事務(wù)，解決了臟讀，引發(fā)不可重讀

repeatable-read：可重讀（默認），通過MVCC多版本機制讓已發(fā)起的事務(wù)在提交前讀取到的數(shù)據(jù)不發(fā)生變化，即使有其他會話已提交了事務(wù)，解決了不可重讀，引發(fā)幻讀

serializable：串行提交，在每個讀的行上施加共享鎖，在寫的行上施加排他鎖，解決了幻讀，但是降低了并發(fā)

3.鎖機制

行鎖和表鎖：依據(jù)粒度不同進行的劃分，行鎖的并發(fā)能力更強

讀鎖和寫鎖：讀鎖又稱共享鎖，施加讀鎖后所有事務(wù)只能進行讀操作，不能再進行寫操作；寫鎖又稱排他鎖，施加寫鎖后除當前事務(wù)外的其他事務(wù)不能進行讀寫操作

InnoDB存儲引擎的行鎖其實是加在索引上的

4.備份方式

數(shù)據(jù)庫按照備份方法分為物理備份和邏輯備份，按照服務(wù)運行的狀態(tài)又可以分為冷備和熱備，對于不支持事物的MyISAM存儲引擎，可以采用溫備的方式進行（前提是必須使用lvm2或者Btree文件系統(tǒng)）。

物理備份：基于文件系統(tǒng)層的備份，優(yōu)點是速度快，缺點是收到文件系統(tǒng)的約束

邏輯備份：備份時將數(shù)據(jù)庫抽取為sql語句，優(yōu)點是不受操作系統(tǒng)約束，可移植性強，缺點是當數(shù)據(jù)庫較大時速度慢

二、使用工具

1.mysqldump

--single-transaction 只對InnoDB引擎有效，用于保護數(shù)據(jù)的一致性，備份發(fā)起前，創(chuàng)建一個數(shù)據(jù)快照，直到備份結(jié)束都不會讀取備份開始后提交的任何事務(wù)

--quick 配合--single-transaction加速大空間表備份

--all-databases（-A） 備份所有數(shù)據(jù)庫

--where（-w） 按條件篩選要備份的數(shù)據(jù)

--databases（-B） 備份指定數(shù)據(jù)庫，多個數(shù)據(jù)庫之間用","分隔

--master-date=VALUE VALUE值有1|2兩個值，如果是1會在備份出的文件添加一個CHANGE MASTER TO語句；如果是2會在備份出的文件添加一個CHANGE MASTER TO語句并注釋，備份時建議使用該選項并將VALUE設(shè)置為2，原因是該參數(shù)備份時會攜帶當前時刻binlog日志的名稱和位置，便于后期使用binlog恢復(fù)

需要注意的是：mysqldump如果不帶-A|-B選項默認不備份數(shù)據(jù)結(jié)構(gòu)，在恢復(fù)時需要先創(chuàng)建數(shù)據(jù)庫和表結(jié)構(gòu)。單表恢復(fù)時只需要指定庫名，不需要指定表名。

2.mysqlbinlog

mysqlbinglog是一個客戶端工具，它可以在其他終端以客戶端的身份訪問mysql服務(wù)器

--database=DATABASE 只適用于本地日志，查看二進制日志中指定數(shù)據(jù)庫的信息（默認是所有庫），導(dǎo)出二進制日志時建議使用該選項，否則還原時遇到該服務(wù)器上沒有的數(shù)據(jù)庫會報錯，無法正?；謴?fù)

--start-position=N 查看指定位置以后的事務(wù)

--to-last-log（-t） 在MySQL服務(wù)器中請求的二進制日志結(jié)尾處不停止，而是繼續(xù)打印直到最后一個二進制日志的結(jié)尾，如果發(fā)送給同一臺服務(wù)器則會無限循環(huán)。需要同時使用--read-from-remote-server選項

--result-file=FILENAME 輸出為指定文件，同重定向">"

三、操作步驟

本次實驗操作系統(tǒng)版本為CentOS7.4，數(shù)據(jù)庫版本為Mariadb5.5.6，為避免干擾，關(guān)閉selinux和iptables，172.16.10.30/24為數(shù)據(jù)庫服務(wù)器，172.16.10.40/24為備用服務(wù)器

1.冷備

（1）停止172.16.10.30/24的MariaDB服務(wù)

systemctl stop mariadb.service

（2）使用scp命令將172.16.10.30/24服務(wù)器上的hellodb數(shù)據(jù)庫拷到172.16.10.40/24服務(wù)器datadir目錄下

scp -r hellodb root@172.16.10.40:/var/lib/mysql/

（3）修改拷貝后172.16.10.40/24服務(wù)器hellodb數(shù)據(jù)庫的屬主和屬組

chown -R mysql.mysql /var/lib/mysql/hellodb

（4）啟動172.16.10.40/24的MariaDB服務(wù)

systemctl start mariadb.service

2.熱備

（1）備份172.16.10.30/24上的數(shù)據(jù)庫

mysqldump -uroot -ppassword1! --database hellodb --master-data=2 --single-transaction --quick > /tmp/hellodb.sql

（2）根據(jù)/tmp/hellodb.sql中記錄備份時使用的binlog及其位置將后續(xù)的sql語句備份到指定文件中，如果不止一個二進制日志后續(xù)以追加方式進行

mysqlbinlog --database hellodb --start-position=245  mysql-binlog.000005 > /tmp/binlog.sql

（3）將hellodb.sql和/tmp/binlog.sql傳輸?shù)?72.16.10.40/24服務(wù)器

scp -r {binlog.sql,hellodb.sql} root@172.16.10.40:/tmp/

（4）導(dǎo)入數(shù)據(jù)庫過程無需記錄二進制日志，把以先暫時關(guān)閉

MariaDB [hellodb]>set sql_log_bin=0

（5）在172.16.10.40/24服務(wù)器執(zhí)行sql語句恢復(fù)數(shù)據(jù)庫

mysql -uroot -p < hellodb.sql

mysql -uroot -p < binlog.sql

（6）開啟二進制日志

MariaDB [hellodb]>set sql_log_bin=1

由于二進制日志的重要性，生產(chǎn)環(huán)境中建議將數(shù)據(jù)文件和二進制文件放在不同的磁盤上，如果有專門的日志服務(wù)器也可以遠程同步二進制日志中的sql語句

mysqlbinlog  --read-from-remote-server --host=172.16.10.30 --port=3306  --user=root --password=password1! --start-position=733 --to-last-log  mysql-binlog.000005 --result-file=/tmp/bin_log.sql

3.溫備

溫備是針對不支持事務(wù)的MyISAM存儲引擎采取的一種折中的備份方式，它通過lvm2的快照功能結(jié)合二進制日志，實現(xiàn)了幾乎熱備的效果

（1）將172.16.10.30/24服務(wù)器上的表同步到磁盤并且鎖表

MariaDB [hellodb]> flush tables with read lock;

（2）刷新172.16.10.30/24服務(wù)器日志

MariaDB [hellodb]> flush logs;

（3）記錄172.16.10.30/24服務(wù)器上當前二進制日志文件及位置

[root@host3 ~]# mysql -uroot -ppassword1! -e 'show master status;' > /tmp/position.txt

（4）為datadir目錄所在的設(shè)備創(chuàng)建一個快照

[root@host3 ~]# lvcreate -L 1G -s -n mylv_snap /dev/myvg/mylv

（5）解除讀鎖

MariaDB [hellodb]> unlock tables;

（6）掛載快照

mount /dev/myvg/mylv_snap /mnt/

（7）將備份拷貝至172.16.10.40/24服務(wù)器，并修改屬主和屬組

[root@host3 mysql]#scp -r /mnt/hellodb root@172.16.10.40:/data/mysql

[root@host3 mysql]#chown -R mysq.mysq /data/mysql/

（8）導(dǎo)入數(shù)據(jù)庫過程無需記錄二進制日志，把以先暫時關(guān)閉

MariaDB [hellodb]>set sql_log_bin=0

（9）將172.16.10.30/24上的二進制文件同步至172.16.10.40/24

[root@host4 mysql]# mysqlbinlog  --read-from-remote-server --host=172.16.10.30 --port=3306  --user=root --password=password1! --start-position=245 --to-last-log  mysql-binlog.000006 --result-file=/tmp/bin_log.sql

（10）執(zhí)行快照之后的sql語句

[root@host4 mysql]# mysql -uroot -p'password1!' < /tmp/bin_log.sql

（11）開啟二進制日志

MariaDB [hellodb]>set sql_log_bin=1

（12）刪除172.16.10.30/24上的快照，至此操作完成

[root@host3 mysql]#umount /mnt/

[root@host3 ~]# lvremove mylv_snap

補充說明：

mysql5.6以后的版本中可以將遠程服務(wù)器上的服務(wù)器復(fù)制到本地，其中mysqldump命令中的-raw（以binlog形式將遠程服務(wù)器的日志存儲到本地）和--stop-never（連接遠程mysql服務(wù)器讀取日志，知道服務(wù)器關(guān)閉后才會退出或被kill掉）選項在mariadb10.1中未找到，不確定是否有該功能