mysql 不锁表备份_Mysql不锁表备份之Xtrabackup的备份与恢复
一、Xtrabackup介紹
MySQL冷備、熱備、mysqldump都無法實現對數據庫進行增量備份。如果數據量較大我們每天進行完整備份不僅耗時且影響性能。而Percona-Xtrabackup就是為了實現增量備份用于MySQL數據庫物理熱備的備份工具，xtrabakackup有2個工具，分別是xtrabakup、innobakupex。
二、Xtrabackup優點
(1)備份速度快，物理備份可靠
(2)備份過程不會打斷正在執行的事務(無需鎖表)
(3)能夠基于壓縮等功能節約磁盤空間和流量
(4)自動備份校驗
(5)還原速度快
(6)可以流傳將備份傳輸到另外一臺機器上
(7)在不增加服務器負載的情況備份數據
定制數據備份策略。例如每周日完整備份，周一到周六增量備份。
三、Xtrabackup備份原理
流程圖：
在InnoDB內部會維護一個redo日志文件，我們也可以叫做事務日志文件。事務日志會存儲每一個InnoDB表數據的記錄修改。當InnoDB啟動時，InnoDB會檢查數據文件和事務日志，并執行兩個步驟：它應用(前滾)已經提交的事務日志到數據文件，并將修改過但沒有提交的數據進行回滾操作。
(1)innobackupex啟動后，會先fork一個進程，用于啟動xtrabackup，然后等待xtrabackup備份ibd數據文件；
(2)xtrabackup在備份innoDB數據時有2種線程：redo拷貝線程和ibd數據拷貝線程。xtrabackup進程開始執行后，會啟動一個redo拷貝的線程，用于從最新的checkpoint點開始順序拷貝redo.log；再啟動ibd數據拷貝線程，進行拷貝ibd數據。這里是先啟動redo拷貝線程的。在此階段，innobackupex進行處于等待狀態(等待文件被創建)
(4)xtrabackup拷貝完成ibd數據文件后，會通知innobackupex(通過創建文件)，同時xtrabackup進入等待狀態(redo線程依舊在拷貝redo.log)
(5)innobackupex收到xtrabackup通知后哦，執行FLUSH TABLES WITH READ LOCK(FTWRL)，取得一致性位點，然后開始備份非InnoDB文件(如frm、MYD、MYI、CSV、opt、par等格式的文件)，在拷貝非InnoDB文件的過程當中，數據庫處于全局只讀狀態。
(6)當innobackup拷貝完所有的非InnoDB文件后，會通知xtrabackup，通知完成后，進入等待狀態；
(7)xtrabackup收到innobackupex備份完成的通知后，會停止redo拷貝線程，然后通知innobackupex，redo.log文件拷貝完成；
(8)innobackupex收到redo.log備份完成后，就進行解鎖操作，執行：UNLOCK TABLES；
(9)最后innbackupex和xtrabackup進程各自釋放資源，寫備份元數據信息等，innobackupex等xtrabackup子進程結束后退出。
四、xtrabackup的安裝部署以及備份恢復實現
1、xtrabackup的安裝(注意對應linux系統版本)
下載地址：https://www.percona.com/downloads/XtraBackup/LATEST/
可以選擇rpm包方式安裝，也可以下載源碼包編譯安裝，這里直接采用rpm包的方式進行安裝
[root@master tools]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm
[root@master tools]# yum install -y percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm
[root@master~]# rpm -qa |grep xtrabackup
percona-xtrabackup-24-2.4.9-1.el6.x86_64
Xtrabackup中主要包含兩個工具：
xtrabackup：是用于熱備innodb，xtradb表中數據的工具，不能備份其他類型的表，也不能備份數據表結構；
innobackupex：是將xtrabackup進行封裝的perl腳本，提供了備份myisam表的能力。
常用選項:--host 指定主機--user 指定用戶名--password 指定密碼--port 指定端口--databases 指定數據庫--incremental 創建增量備份--incremental-basedir 指定包含完全備份的目錄--incremental-dir 指定包含增量備份的目錄--apply-log 對備份進行預處理操作
一般情況下，在備份完成后，數據尚且不能用于恢復操作，因為備份的數據中可能會包含尚未提交的事務或已經提交但尚未同步至數據文件中的事務。因此，此時數據文件仍處理不一致狀態。“準備”的主要作用正是通過回滾未提交的事務及同步已經提交的事務至數據文件也使得數據文件處于一致性狀態。--redo-only 不回滾未提交事務--copy-back 恢復備份目錄
使用innobackupex備份時，其會調用xtrabackup備份所有的InnoDB表，復制所有關于表結構定義的相關文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相關文件，同時還會備份觸發器和數據庫配置信息相關的文件，這些文件會被保存到一個以時間命名的目錄當中。在備份的同時，innobackupex還會在備份目錄中創建如下文件：
(1)xtrabackup_checkpoints --備份類型(如完全或增量)、備份狀態(如是否已經為prepared狀態)和LSN(日志序列號)范圍信息：
每個InnoDB頁(通常為16k大小)
都會包含一個日志序列號，即LSN，LSN是整個數據庫系統的系統版本號，每個頁面相關的LSN能夠表明此頁面最近是如何發生改變的。
(2)xtrabackup_binlog_info --mysql服務器當前正在使用的二進制日志文件及備份這一刻位置二進制日志時間的位置。
(3)xtrabackup_binlog_pos_innodb --二進制日志文件及用于InnoDB或XtraDB表的二進制日志文件的當前position。
(4)xtrabackup_binary --備份中用到的xtrabackup的可執行文件；
(5)backup-my.cnf --備份命令用到的配置選項信息：
在使用innobackupex進行備份時，還可以使用--no-timestamp選項來阻止命令自動創建一個以時間命名的目錄：如此一來，innobackupex命令將會創建一個BACKUP-DIR目錄來存儲備份數據。
如果要使用一個最小權限的用戶進行備份，則可基于如下命令創建此類用戶：如果要使用一個最小權限的用戶進行備份，則可基于如下命令創建此類用戶：
mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY '123456'; #創建用戶
mysql> REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'bkpuser'; #回收此用戶所有權限
mysql> GRANT RELOAD,LOCK TABLES,RELICATION CLIENT ON *.* TO 'bkpuser'@'localhost'; #授權刷新、鎖定表、用戶查看服務器狀態
mysql> FLUSH PRIVILEGES; #刷新授權表
注意：備份時需啟動MySQL,恢復時需關閉MySQL,清空mysql數據目錄且不能重新初始化,恢復數據后應該立即進行一次完全備份
2、xtrabackup全量備份與恢復
備份(需開啟mysql服務)：
innobackupex --defaults-file=/etc/my.cnf --user=root --password=pwd --socket=/tmp/mysql.sock /home/backup/full
xtrabackup: Error: --defaults-file must be specified first on the command line ----提示配置文件參數必須放在第一位
恢復(需關閉mysql服務)：
innobackupex --apply-log /home/backup/full/2020.../
innobackupex --copy-back /home/backup/full/2020.../
(1)準備(prepare)一個完全備份
一般情況下，在備份完成后，數據尚且不能用于恢復操作，因為備份的數據中可能會包含尚未提交的事務或者已經提交但尚未同步至數據文件中的事務。因此，此時數據文件仍處于不一致狀態。"準備"的主要作用正是通過回滾未提交的事務及同步已經提交的事務至數據文件也使用得數據文件處于一致性狀態。
innobackupex命令的--apply-log選項可用于實現上述功能，如下面的命令：
# innobackupex --apply-log /path/to/BACKUP-DIR
如果執行正確，其最后輸出的幾行信息通常如下：
120407 09:01:04 innobackupex: completed OK!
在實現"準備"的過程中，innobackupex通常還可以使用--user-memory選項來指定其可以使用的內存的大小，默認為100M.如果有足夠的內存空間可用，可以多劃分一些內存給prepare的過程，以提高其完成備份的速度。
(2)從一個完全備份中恢復數據
innobackupex命令的--copy-back選項用于恢復操作，其通過復制所有數據相關的文件至mysql服務器DATADIR目錄中來執行恢復過程。innobackupex通過backup-my.cnf來獲取DATADIR目錄的相關信息。
# innobackupex --copy-back /path/to/BACKUP-DIR
當數據恢復至DATADIR目錄以后，還需要確保所有的數據文件的屬主和屬組均為正確的用戶，如mysql，否則，在啟動mysqld之前還需要事先修改數據文件的屬主和屬組。如：
# chown -R mysql.mysql /mydata/data/
(3)實戰練習
(1)全量備份
[root@master backups]# innobackupex--defaults-file=/etc/my.cnf --user=root --password=123456 --host=127.0.0.1 /home/backup/full/ #在master上進行全庫備份#語法解釋說明：#--user=root 指定備份用戶#--password=123456 指定備份用戶密碼#--host 指定主機#/backups 指定備份目錄
[root@master backups]# ll
total0drwxr-x--- 7 root root 232 Jul 30 11:01 2018-07-30_11-01-37[root@master backups]# ll2018-07-30_11-01-37/#查看備份數據
total77856
-rw-r----- 1 root root 418 Jul 30 11:01 backup-my.cnf #備份用到的配置選項信息文件-rw-r----- 1 root root 79691776 Jul 30 11:01ibdata1 #數據文件
drwxr-x--- 2 root root 4096 Jul 30 11:01mysql
drwxr-x--- 2 root root 4096 Jul 30 11:01performance_schema-rw-r----- 1 root root 21 Jul 30 11:01xtrabackup_binlog_info #mysql服務器當前正在使用的二進制日志文件和此時二進制日志時間的位置信息文件-rw-r----- 1 root root 113 Jul 30 11:01xtrabackup_checkpoints #備份的類型、狀態和LSN狀態信息文件-rw-r----- 1 root root 482 Jul 30 11:01xtrabackup_info-rw-r----- 1 root root 2560 Jul 30 11:01xtrabackup_logfile #備份的日志文件
(2)在另外一臺服務器恢復
[root@slave~]# /etc/init.d/mysqld stop #停止slave上的mysql
Shutting down MySQL.. SUCCESS![root@slave tools]# yum install-y percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm #安裝xtrabackup
[root@master backups]# scp-r 2018-07-30_11-01-37/ root@192.168.56.12:/home/backup/full/#從master上拷貝備份數據
[root@slave tools]# innobackupex--apply-log /home/backup/full/2020.../#合并數據，使數據文件處于一致性的狀態180729 23:18:23 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints"completed OK!".
innobackupex version2.4.9 based on MySQL server 5.7.13Linux (x86_64) (revision id: a467167cdd4)
xtrabackup: cd to/backups/2018-07-30_11-01-37/xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools:1xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(3127097)
......
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number3129915
180729 23:18:30 completed OK![root@slave~]# rm -rf /usr/local/mysql/data/#在slave上刪除原有的數據
[root@slave~]# vim /etc/my.cnf #配置my.cnf的數據目錄路徑，否則會報錯，要和master一致
datadir=/usr/local/mysql/data
[root@slave~]# innobackupex --copy-back /home/backup/full/2020.../#在slave上數據恢復180729 23:32:03 innobackupex: Starting the copy-back operation
IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex
prints"completed OK!".
......180729 23:32:08 completed OK!#看到completed OK就是恢復正常了
[root@slave~]# ll /usr/local/mysql/data/#slave上查看數據目錄，可以看到數據已經恢復，但是屬主會有問題，需要進行修改，所以一般使用mysql的運行用戶進行恢復，否則需要進行修改屬主和屬組信息
total188432
-rw-r----- 1 root root 79691776 Jul 29 23:32ibdata1-rw-r----- 1 root root 50331648 Jul 29 23:32ib_logfile0-rw-r----- 1 root root 50331648 Jul 29 23:32ib_logfile1-rw-r----- 1 root root 12582912 Jul 29 23:32ibtmp1
drwxr-x--- 2 root root 20 Jul 29 23:32kim
drwxr-x--- 2 root root 4096 Jul 29 23:32mysql
drwxr-x--- 2 root root 4096 Jul 29 23:32performance_schema
drwxr-x--- 2 root root 20 Jul 29 23:32repppp
drwxr-x--- 2 root root 4096 Jul 29 23:32wordpress-rw-r----- 1 root root 482 Jul 29 23:32xtrabackup_info
[root@slave~]# chown -R mysql.mysql /usr/local/mysql/data/#修改屬主屬組
[root@slave~]# /etc/init.d/mysqld start #啟動mysql
Starting MySQL. SUCCESS![root@slave~]# mysql -uroot -p -e "show databases;"#查看數據，是否恢復
Enter password:+--------------------+
| Database |
+--------------------+
| information_schema |
| kim |
| mysql |
| performance_schema |
| repppp |
| wordpress |
+--------------------+
總結全庫備份與恢復三步曲：
a. innobackupex全量備份，并指定備份目錄路徑；
b. 在恢復前，需要使用--apply-log參數先進行合并數據文件，確保數據的一致性要求；
c. 恢復時，直接使用--copy-back參數進行恢復，需要注意的是，在my.cnf中要指定數據文件目錄的路徑。
3、xtrabackup增量備份與恢復
使用innobackupex進行增量備份，每個InnoDB的頁面都會包含一個LSN信息，每當相關的數據發生改變，相關的頁面的LSN就會自動增長。這正是InnoDB表可以進行增量備份的基礎，即innobackupex通過備份上次完全備份之后發生改變的頁面來實現。在進行增量備份時，首先要進行一次全量備份，第一次增量備份是基于全備的，之后的增量備份都是基于上一次的增量備份的，以此類推。
要實現第一次增量備份，可以使用下面的命令進行：
基于全量備份的增量備份與恢復
做一次增量備份(基于當前最新的全量備份)
innobackupex--defaults-file=/etc/my.cnf --user=root --password=root --incremental /home/backup/incr/ --incremental-basedir=/home/backup/full/2020.../
恢復
1. 準備基于全量
innobackupex--defaults-file=/etc/my.cnf --user=root --password=root --apply-log --redo-only /home/backup/full/2020.../
2. 準備基于增量
innobackupex--defaults-file=/etc/my.cnf --user=root --password=root --apply-log --redo-only /home/backup/full/2020.../ --incremental-dir=/home/backup/incr/2020.../
3. 恢復
innobackupex --defaults-file=/etc/my.cnf --copy-back /home/backup/full/2020.../
解釋：
1. full里指的是完全備份所在的目錄。
2. incr指定是第一次基于full增量備份的目錄，其他類似以此類推，即如果有多次增量備份。每一次都要執行如上操作。
需要注意的是，增量備份僅能應用于InnoDB或XtraDB表，對于MyISAM表而言，執行增量備份時其實進行的是完全備份。
"準備"(prepare)增量備份與整理完全備份有著一些不同，尤其要注意的是：①需要在每個備份 (包括完全和各個增量備份)上，將已經提交的事務進行"重放"。"重放"之后，所有的備份數據將合并到完全備份上。②基于所有的備份將未提交的事務進行"回滾"
(1)增量備份演示
[root@master backups]# innobackupex --user=root --password=123456 --host=127.0.0.1 /backups/ #全備數據[root@master ~]# mysql -uroot -p #在master上創建student庫并創建testtb表插入若干數據Enter password:
mysql>create database student;
Query OK,1 row affected (0.03sec)
mysql>use student;
Database changed
mysql> create table testtb(id int);
Query OK,0 rows affected (0.07sec)
mysql> insert into testtb values(1),(10),(99);
Query OK,3 rows affected (0.04sec)
Records:3 Duplicates: 0 Warnings: 0mysql> select *from testtb;+------+
| id |
+------+
| 1 |
| 10 |
| 99 |
+------+
3 rows in set (0.00sec)
mysql>quit;
Bye
#使用innobackupex進行增量備份
[root@master backups]# innobackupex--user=root --password=123456 --host=127.0.0.1 --incremental /backups/ --incremental-basedir=/backups/2018-07-30_11-01-37/......180730 13:51:50Executing UNLOCK TABLES180730 13:51:50All tables unlocked180730 13:51:50 Backup created in directory '/backups/2018-07-30_13-51-47/'MySQL binlog position: filename'mysql-bin.000005', position '664'
180730 13:51:50 [00] Writing /backups/2018-07-30_13-51-47/backup-my.cnf180730 13:51:50 [00] ...done180730 13:51:50 [00] Writing /backups/2018-07-30_13-51-47/xtrabackup_info180730 13:51:50 [00] ...done
xtrabackup: Transaction log of lsn (3158741) to (3158741) was copied.180730 13:51:50 completed OK![root@master backups]# ll #查看備份數據
total0drwxr-x--- 7 root root 232 Jul 30 11:01 2018-07-30_11-01-37#全量備份數據目錄
drwxr-x--- 8 root root 273 Jul 30 13:51 2018-07-30_13-51-47#增量備份數據目錄
[root@master2018-07-30_11-01-37]# cat xtrabackup_checkpoints #查看全量備份的xtrabackup_checkpoints
backup_type= full-backuped #備份類型為全量備份
from_lsn= 0#lsn從0開始
to_lsn= 3127097#lsn到3127097結束
last_lsn= 3127097compact= 0recover_binlog_info= 0[root@master2018-07-30_13-51-47]# cat xtrabackup_checkpoints #查看增量備份的xtrabackup_checkpoints
backup_type=incremental #備份類型為增量備份
from_lsn= 3127097#lsn從3127097開始
to_lsn= 3158741#lsn到啊3158741結束
last_lsn= 3158741compact= 0recover_binlog_info= 0
(2)增量備份后數據恢復演示
(1)模擬mysql故障，刪除數據目錄所有數據[root@master ~]# /etc/init.d/mysqld stop #模擬mysql故障，停止mysql
Shutting down MySQL.. SUCCESS!
[root@master ~]# rm -rf /usr/local/mysql/data/* #刪除數據目錄中的所有數據(2)合并全備數據目錄，確保數據的一致性
[root@master ~]# innobackupex --apply-log --redo-only /backups/2018-07-30_11-01-37/
180730 14:05:27 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
innobackupex version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
xtrabackup: cd to /backups/2018-07-30_11-01-37/
......
......
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 3127106
InnoDB: Number of pools: 1
180730 14:05:29 completed OK!(3)將增量備份數據合并到全備數據目錄當中
[root@master ~]# innobackupex --apply-log --redo-only /backups/2018-07-30_11-01-37/ --incremental-dir=/backups/2018-07-30_13-51-47/
180730 14:06:42 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
......
......
180730 14:06:44 [00] ...done
180730 14:06:44 completed OK!
[root@master ~]# cat /backups/2018-07-30_11-01-37/xtrabackup_checkpoints
backup_type = log-applied #查看到數據備份類型是增加
from_lsn = 0 #lsn從0開始
to_lsn = 3158741 #lsn結束號為最新的lsn
last_lsn = 3158741
compact = 0
recover_binlog_info = 0(4)恢復數據
[root@master ~]# innobackupex --copy-back /backups/2018-07-30_11-01-37/
180730 14:07:51 innobackupex: Starting the copy-back operation
IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex
prints "completed OK!".
.......
.......
180730 14:08:17 [01] ...done
180730 14:08:17 completed OK!
[root@master ~]# ll /usr/local/mysql/data/
total 77844
-rw-r----- 1 root root 79691776 Jul 30 14:08 ibdata1
drwxr-x--- 2 root root 20 Jul 30 14:08 kim
drwxr-x--- 2 root root 4096 Jul 30 14:08 mysql
drwxr-x--- 2 root root 4096 Jul 30 14:08 performance_schema
drwxr-x--- 2 root root 20 Jul 30 14:08 repppp
drwxr-x--- 2 root root 56 Jul 30 14:08 student
drwxr-x--- 2 root root 4096 Jul 30 14:08 wordpress
-rw-r----- 1 root root 21 Jul 30 14:08 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 554 Jul 30 14:08 xtrabackup_info
[root@master ~]# chown -R mysql.mysql /usr/local/mysql/data #更改數據的屬主屬組
[root@master ~]# /etc/init.d/mysqld start #啟動mysql
Starting MySQL.Logging to '/usr/local/mysql/data/master.err'.
.. SUCCESS!
[root@master ~]# mysql -uroot -p -e "show databases;" #查看數據是否恢復
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| kim |
| mysql |
| performance_schema |
| repppp |
| student |
| wordpress |
+--------------------+
總結：
(1)增量備份需要使用參數--incremental指定需要備份到哪個目錄，使用incremental-dir指定全備目錄；
(2)進行數據備份時，需要使用參數--apply-log redo-only先合并全備數據目錄數據，確保全備數據目錄數據的一致性；
(3)再將增量備份數據使用參數--incremental-dir合并到全備數據當中；
(4)最后通過最后的全備數據進行恢復數據，注意，如果有多個增量備份，需要逐一合并到全備數據當中，再進行恢復。
總結
以上是生活随笔為你收集整理的mysql 不锁表备份_Mysql不锁表备份之Xtrabackup的备份与恢复的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Python学习笔记：入门（2）
- 下一篇： java ee jstl_Java EE