title: MySQL：5.備份還原的幾種方式
categories: 數(shù)據(jù)庫
tags:
- MySQL
timezone: Asia/Shanghai
date: 2019-03-10
環(huán)境
[root@centos181001 ~]# cat /etc/centos-release
CentOS Linux release 7.6.1810 (Core)
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.25 |
+-----------+
1 row in set (0.00 sec)
方式1：Xtrabackup
Xtrabackup是一個對InnoDB做數(shù)據(jù)備份的工具钱骂，支持在線熱備份（備份時不影響數(shù)據(jù)讀寫）灶挟，是商業(yè)備份工具InnoDB Hotbackup的一個很好的替代品。它能對InnoDB和XtraDB存儲引擎的數(shù)據(jù)庫非阻塞地備份（對于MyISAM的備份同樣需要加表鎖）踱讨。XtraBackup支持所有的Percona Server惹谐、MySQL持偏、MariaDB和Drizzle。
- (1)備份過程快速氨肌、可靠
- (2)備份過程不會打斷正在執(zhí)行的事務(wù)
- (3)能夠基于壓縮等功能節(jié)約磁盤空間和流量
- (4)自動實現(xiàn)備份檢驗
- (5)還原速度快
- (6)可以備份MyISAM, InnoDB, XtraDB表鸿秆。但在處理Myisam時需要加一個讀鎖。
1.安裝
wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.13/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.13-1.el7.x86_64.rpm
yum install -y percona-xtrabackup-24-2.4.13-1.el7.x86_64.rpm
xtrabackup：一個由C編譯而來的二進制文件怎囚，只能備份InnoDB和XtraDB數(shù)據(jù)卿叽。
xbcrypt：用來加密或解密備份的數(shù)據(jù)。
xbstream：用來解壓或壓縮xbstream格式的壓縮文件恳守。
2.innobackupex常用參數(shù)
--user=USER 指定備份用戶考婴，不指定的話為當(dāng)前系統(tǒng)用戶
--password=PASSWD 指定備份用戶密碼
--port=PORT 指定數(shù)據(jù)庫端口
--defaults-group=GROUP-NAME 在多實例的時候使用
--host=HOST 指定備份的主機，可以為遠程數(shù)據(jù)庫服務(wù)器
--apply-log 應(yīng)用 BACKUP-DIR 中的 xtrabackup_logfile 事務(wù)日志文件催烘。
一般情況下沥阱，在備份完成后，數(shù)據(jù)尚且不能用于恢復(fù)操作伊群，因為備份的數(shù)據(jù)中可能會包含尚未提交的事務(wù)或已經(jīng)提交但尚未同步至數(shù)據(jù)文件中的事務(wù)考杉。
因此策精，此時數(shù)據(jù)文件仍處于不一致狀態(tài)。
“準備”的主要作用正是通過回滾未提交的事務(wù)及同步已經(jīng)提交的事務(wù)至數(shù)據(jù)文件使得數(shù)據(jù)文件處于一致性狀態(tài)崇棠。
--database 指定需要備份的數(shù)據(jù)庫咽袜，多個數(shù)據(jù)庫之間以空格分開。
格式為：--database="db1[.tb1] db2[.tb2]" 多個庫之間以空格隔開易茬，如果此選項不被指定酬蹋，將會備份所有的數(shù)據(jù)庫。
--defaults-file 指定mysql的配置文件抽莱，只能從給定的文件中讀取默認選項范抓。
且必須作為命令行上的第一個選項；必須是一個真實的文件食铐，它不能是一個符號鏈接匕垫。
--copy-back 將備份數(shù)據(jù)復(fù)制回原始位置
--incremental 增量備份，后面跟要增量備份的路徑
--incremental-basedir=DIRECTORY 增量備份時使用指向上一次的增量備份所在的目錄
--incremental-dir=DIRECTORY 增量備份還原的時候用來合并增量備份到全量虐呻，用來指定全備路徑
--rsync 加快本地文件傳輸象泵，適用于non-InnoDB數(shù)據(jù)庫引擎。不與--stream共用
--no-timestamp 生成的備份文件不以時間戳為目錄.
--tables-file=FILE 此選項的參數(shù)需要是一個文件名斟叼，此文件中每行包含一個要備份的表的完整名稱偶惠，格式為databasename.tablename。
該選項傳遞給 xtrabackup --tables-file朗涩，與--tables選項不同忽孽，只有要備份的表的庫才會被創(chuàng)建。
注意：部分備份（--include谢床、--tables-file兄一、--database）需要開啟 innodb_file_per_table 。
--redo-only 在“準備基本完整備份” 和 “合并所有的增量備份(除了最后一個增備)”時使用此選項识腿。
它直接傳遞給xtrabackup的 xtrabackup --apply-log-only 選項出革，使xtrabackup跳過"undo"階段，只做"redo"操作渡讼。
如果后面還有增量備份應(yīng)用到這個全備,這是必要的
3.全量備份
innobackupex \
--defaults-file=/etc/my.cnf \
--user=root \
--password=Xiaoliu123! \
--socket=/var/lib/mysql/mysql.sock \
/data/
4.備份文件說明
[root@centos181002 2019-03-10_00-46-04]# ll
總用量 17788
-rw-r-----. 1 root root 488 3月 10 00:46 backup-my.cnf
-rw-r-----. 1 root root 349 3月 10 00:46 ib_buffer_pool
-rw-r-----. 1 root root 12582912 3月 10 00:46 ibdata1
drwxr-x---. 2 root root 4096 3月 10 00:46 mysql
drwxr-x---. 2 root root 8192 3月 10 00:46 performance_schema
drwxr-x---. 2 root root 8192 3月 10 00:46 sys
drwxr-x---. 2 root root 138 3月 10 00:46 test
-rw-r-----. 1 root root 33 3月 10 00:46 xtrabackup_binlog_info
-rw-r-----. 1 root root 115 3月 10 00:46 xtrabackup_checkpoints
-rw-r-----. 1 root root 540 3月 10 00:46 xtrabackup_info
-rw-r-----. 1 root root 5581312 3月 10 00:46 xtrabackup_logfile
- 其中骂束，mysql/， performance_schema/成箫， sys/ 展箱，test/ 下存放的是數(shù)據(jù)庫文件。
- backup-my.cnf：備份命令用到的配置選項信息伟众；
- ib_buffer_pool：buffer pool 中的熱數(shù)據(jù)，當(dāng)設(shè)置 innodb_buffer_pool_dump_at_shutdown=1 召廷，在關(guān)閉 MySQL 時凳厢，會把內(nèi)存中的熱數(shù)據(jù)保存在磁盤里 ib_buffer_pool 文件中账胧，位于數(shù)據(jù)目錄下。
- ibdata1：備份的共享表空間文件先紫；
- xtrabackup_binlog_info：mysql服務(wù)器當(dāng)前正在使用的二進制日志文件及至備份這一刻為止二進制日志事件的位置治泥；
- xtrabackup_checkpoints：備份類型（如完全或增量）、備份狀態(tài)（如是否已經(jīng)為prepared狀態(tài)）和LSN(日志序列號)范圍信息遮精；
- xtrabackup_info：記錄備份的基本信息居夹，uuid、備份命令本冲、備份時間准脂、binlog、LSN檬洞、以及其他加密壓縮等信息狸膏。
- xtrabackup_logfile：備份的重做日志文件。
5.全備恢復(fù)
## 1.準備一個完全備份
innobackupex --apply-log /root/2019-03-10_00-46-04
## 2.關(guān)閉數(shù)據(jù)庫
systemctl stop mysqld
## 3.刪除原有數(shù)據(jù)文件
mkdir /home/bak
mv /var/lib/mysql/* /home/bak
## 4.執(zhí)行恢復(fù)
innobackupex \
--defaults-file=/etc/my.cnf \
--copy-back \
--rsync \
/root/2019-03-10_00-46-04
## 5.更改權(quán)限
chown -R mysql.mysql /var/lib/mysql
## 6.啟動服務(wù)
systemctl start mysqld
systemctl status mysqld
## 7.登錄MySQL
mysql -uroot -p
6.增量備份 - 直接以之前的全備為基準添怔，在其基礎(chǔ)上做增量備份
1.增量備份
## 1.插入測試數(shù)據(jù)
insert into test.linux values ('zengliang1', 'zengliang1');
## 2.做增量備份1湾戳，以之前的``2019-03-10_00-46-04``為基準
innobackupex \
--defaults-file=/etc/my.cnf \
--user=root \
--password=Xiaoliu123! \
--socket=/var/lib/mysql/mysql.sock \
--incremental \
/data/inc \
--incremental-basedir=/data/2019-03-10_00-46-04/ \
--parallel=2
## 3.再插入測試數(shù)據(jù)
insert into test.linux values ('zengliang2', 'zengliang2');
## 4.做增量備份2：（以增量1為基準/data/inc/2019-03-10_01-45-25/）
innobackupex \
--defaults-file=/etc/my.cnf \
--user=root \
--password=Xiaoliu123! \
--socket=/var/lib/mysql/mysql.sock \
--incremental \
/data/inc \
--incremental-basedir=/data/inc/2019-03-10_01-45-25/ \
--parallel=2
2.增量備份恢復(fù)
增量備份的恢復(fù)需要有3個步驟
- 恢復(fù)完全備份
- 恢復(fù)增量備份到完全備份(開始恢復(fù)的增量備份要添加--redo-only參數(shù)，到最后一次增量備份要去掉--redo-only)
- 對整體的完全備份進行恢復(fù)广料，回滾未提交的數(shù)據(jù)
## 1.準備一個全備
innobackupex --apply-log --redo-only /root/2019-03-10_00-46-04/
## 2.將增量1應(yīng)用到完全備份
innobackupex --apply-log --redo-only /root/2019-03-10_00-46-04/ --incremental-dir=/root/inc/2019-03-10_01-45-25
## 3.將增量2應(yīng)用到完全備份（這里的增量2是最后一個備份砾脑，所以不要加``--redo-only``參數(shù)了）
innobackupex --apply-log /root/2019-03-10_00-46-04/ --incremental-dir=/root/inc/2019-03-10_01-49-56
## 4.把所有合在一起的完全備份整體進行一次apply操作，回滾未提交的數(shù)據(jù)
innobackupex --apply-log /root/2019-03-10_00-46-04/
## 5.開始執(zhí)行恢復(fù)操作（這里的5-10跟全備恢復(fù)的2-7完全一致）
## 5.關(guān)閉數(shù)據(jù)庫
systemctl stop mysqld
## 6.刪除原有數(shù)據(jù)文件
mkdir /home/bak
mv /var/lib/mysql/* /home/bak
## 7.執(zhí)行恢復(fù)
innobackupex \
--defaults-file=/etc/my.cnf \
--copy-back \
--rsync \
/root/2019-03-10_00-46-04
## 8.更改權(quán)限
chown -R mysql.mysql /var/lib/mysql
## 9.啟動服務(wù)
systemctl start mysqld
systemctl status mysqld
## 10.登錄MySQL
mysql -uroot -p
方式2：mysqldump命令備份
mysqldump命令將數(shù)據(jù)庫中的數(shù)據(jù)備份成一個文本文件艾杏。表的結(jié)構(gòu)和表中的數(shù)據(jù)將存儲在生成的文本文件中韧衣。
mysqldump命令的工作原理很簡單。它先查出需要備份的表的結(jié)構(gòu)糜颠，再在文本文件中生成一個CREATE語句汹族。然后，將表中的所有記錄轉(zhuǎn)換成一條INSERT語句其兴。然后通過這些語句顶瞒，就能夠創(chuàng)建表并插入數(shù)據(jù)。
1.備份
## 備份全庫
mysqldump -uroot -pXiaoliu123! --all-databases >/home/alldatabases.sql
## 備份全庫并直接壓縮
mysqldump -uroot -pXiaoliu123! --all-databases | gzip > backupfile.sql.gz
## 備份databases庫
mysqldump -uroot -pXiaoliu123! --databases test > /home/db_test.sql
## 備份test庫的linux表
mysqldump -uroot -pXiaoliu123! test linux > /home/table_linux.sql
## 僅備份數(shù)據(jù)庫結(jié)構(gòu)
mysqldump -uroot -pXiaoliu123! --no-data --databases test > db_test_no-data.sql
mysqldump -uroot -pXiaoliu123! --no-data --all-databases > db_test_no-data.sql
## 語法
[root@centos181001 home]# mysqldump
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help
2.還原
mysql -uroot -pXiaoliu123! mysql < alldatabases.sql
附錄：
XtraBackup8.0下載地址：https://www.percona.com/downloads/Percona-XtraBackup-LATEST/
XtraBackup2.4下載地址：https://www.percona.com/downloads/Percona-XtraBackup-2.4/LATEST/
https://www.percona.com/downloads/
特色Percona開源項目：https://www.percona.com/software
