MySQL8.0之XtraBackup
XtraBackup是由Percona提供的開源備份軟體。它能在不關閉伺服器的情況下複製普通檔案。但為了避免不一致，它會使用redo日誌檔案。XtraBackup被許多公司廣泛用做標準備份工具。與邏輯備份工具相比，其優勢是備份速度非常快，恢復速度也非常快。
Percona XtraBackup的工作原理：
1.XtraBackup複製InnoDB資料檔案，這會導致內部不一致的資料，但是它會對檔案執行崩潰恢復，以使其再次成為一個一致的可用資料庫
2.這樣做的可行性是因為InnoDB維護一個REDO日誌，也稱為事務日誌。REDO日誌包含了InnoDB資料每次更改的記錄。當InnoDB啟動時，REDO日誌會檢查資料檔案和事務日誌，並執行兩個步驟。它將已提交的事務日誌條目應用於資料檔案，並對任何修改了資料但未提交的事務執行undo操作
3.Percona XtraBackup會在啟動時記住日誌序列號（LSN）,然後複製資料檔案。這需要一些時間來完成，如果檔案正在改變，那麼它會在不同的時間點反映資料庫的狀態。同時，Percona XtraBackup執行一個後臺程式，用於監視事務日誌檔案，並從中複製更改。Percona XtraBackup需要持續這樣做，因為事務日誌是以迴圈方式寫入的，並且可以在一段時間後重新使用。Percona XtraBackup開始執行後，需要複製每次資料檔案更改對應的事務日誌記錄。
安裝
--下載rpm包
[root@mysql ~]#
wget
--yum安裝rpm包，提示缺少
libev.so.4()(64bit)
[root@mysql ~]#
yum localinstall percona-xtrabackup-80-8.0.6-1.el6.x86_64.rpm
Loaded plugins: fastestmirror, security
Setting up Local Package Process
Examining percona-xtrabackup-80-8.0.6-1.el6.x86_64.rpm: percona-xtrabackup-80-8.0.6-1.el6.x86_64
Marking percona-xtrabackup-80-8.0.6-1.el6.x86_64.rpm to be installed
....省略...
--> Finished Dependency Resolution
Error: Package: percona-xtrabackup-80-8.0.6-1.el6.x86_64 (/percona-xtrabackup-80-8.0.6-1.el6.x86_64)
Requires: libev.so.4()(64bit)
You could try using --skip-broken to work around the problem
** Found 3 pre-existing rpmdb problem(s), 'yum check' output follows:
2:postfix-2.6.6-2.2.el6_1.x86_64 has missing requires of libmysqlclient.so.16()(64bit)
2:postfix-2.6.6-2.2.el6_1.x86_64 has missing requires of libmysqlclient.so.16(libmysqlclient_16)(64bit)
2:postfix-2.6.6-2.2.el6_1.x86_64 has missing requires of mysql-libs
--下載安裝
libev.so.4()(64bit)
去如下網站查詢對應系統的包
%28%29%2864bit%29&submit=Search+...&system=&arch=
--下載並rpm安裝
[root@oracle ~]#
wget
[root@oracle ~]#
rpm -ivh libev-4.03-3.el6.x86_64.rpm
Preparing... ########################################### [100%]
1:libev ########################################### [100%]
--重新安裝
percona-xtrabackup
[root@oracle ~]# yum localinstall percona-xtrabackup-80-8.0.6-1.el6.x86_64.rpm
Loaded plugins: fastestmirror, security
Setting up Local Package Process
Examining percona-xtrabackup-80-8.0.6-1.el6.x86_64.rpm: percona-xtrabackup-80-8.0.6-1.el6.x86_64
....省略...
Complete!
許可權
如果需要建立專門的使用者去執行備份恢復操作，需要選項如下：
mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cr%T';
mysql> GRANT
BACKUP_ADMIN, SUPER, PROCESS,
CREATE TABLESPACE, RELOAD, LOCK TABLES, REPLICATION CLIENT
ON *.* TO 'bkpuser'@'localhost';
mysql> GRANT
SELECT, INSERT, CREATE
ON performance_schema.* TO 'bkpuser'@'localhost';
mysql> FLUSH PRIVILEGES;
--如下是官網對每個引數含義的解釋
•
RELOAD
and
LOCK TABLES
(unless the --no-lock option is specified) in order to run FLUSH TABLES WITH READ LOCK and FLUSH ENGINE LOGS prior to start copying the files, and requires this privilege when Backup Locks are used
•
BACKUP_ADMIN
privilege is needed to query the performance_schema.log_status table, and run LOCK INSTANCE FOR BACKUP, LOCK BINLOG FOR BACKUP, or LOCK TABLES FOR BACKUP
•
REPLICATION CLIENT
in order to obtain the binary log position
•
CREATE TABLESPACE
in order to import tables (see Restoring Individual Tables)
•
PROCESS
in order to run SHOW ENGINE INNODB STATUS (which is mandatory), and optionally to see all threads which are running on the server (see Handling FLUSH TABLES WITH READ LOCK)
•
SUPER
in order to start/stop the slave threads in a replication environment, use XtraDB Changed Page Tracking for Incremental Backups and for handling FLUSH TABLES WITH READ LOCK
•
CREATE
privilege in order to create the PERCONA_SCHEMA.xtrabackup_history database and table
•
INSERT
privilege in order to add history records to the PERCONA_SCHEMA.xtrabackup_history table
•
SELECT
privilege in order to use --incremental-history-name or --incremental-history-uuid in order for the feature to look up the innodb_to_lsn values in the PERCONA_SCHEMA.xtrabackup_history table
1.全量備份和恢復
全量備份
在XtraBackup8概述
移除了innobackupex命令。
由於新的MySQL重做日誌和資料字典格式，8.0版本只支援mysql8.0和percona8.0。
早於mysql8.0的版本需要使用xtrabackup2.4備份和恢復
--備份命令
[root@oracle bin]#
xtrabackup --user=root --backup --socket=/tmp/mysql.sock --target-dir=/xtrabackup
...省略...
190802 15:52:55 All tables unlocked
190802 15:52:55 [00] Copying ib_buffer_pool to /xtrabackup/ib_buffer_pool
190802 15:52:55 [00] ...done
190802 15:52:55 Backup created in directory '/xtrabackup/'
MySQL binlog position: filename 'binlog.000011', position '155'
190802 15:52:55 [00] Writing /xtrabackup/backup-my.cnf
190802 15:52:55 [00] ...done
190802 15:52:55 [00] Writing /xtrabackup/xtrabackup_info
190802 15:52:55 [00] ...done
xtrabackup: Transaction log of lsn (20323274) to (20323294) was copied.
190802 15:52:55 completed OK!
--檢視備份
[root@oracle bin]#
ll /xtrabackup/
total 56372
-rw-r----- 1 root root 476 Aug 2 15:52 backup-my.cnf
-rw-r----- 1 root root 155 Aug 2 15:52 binlog.000011
-rw-r----- 1 root root 16 Aug 2 15:52 binlog.index
drwxr-x--- 2 root root 4096 Aug 2 15:52 company
-rw-r----- 1 root root 3329 Aug 2 15:52 ib_buffer_pool
-rw-r----- 1 root root 12582912 Aug 2 15:52 ibdata1
drwxr-x--- 2 root root 4096 Aug 2 15:52 mysql
-rw-r----- 1 root root 24117248 Aug 2 15:52 mysql.ibd
drwxr-x--- 2 root root 4096 Aug 2 15:52 performance_schema
drwxr-x--- 2 root root 4096 Aug 2 15:52 sys
-rw-r----- 1 root root 10485760 Aug 2 15:52 undo_001
-rw-r----- 1 root root 10485760 Aug 2 15:52 undo_002
-rw-r----- 1 root root 18 Aug 2 15:52 xtrabackup_binlog_info
-rw-r----- 1 root root 95 Aug 2 15:52 xtrabackup_checkpoints
-rw-r----- 1 root root 479 Aug 2 15:52 xtrabackup_info
-rw-r----- 1 root root 2560 Aug 2 15:52 xtrabackup_logfile
-rw-r----- 1 root root 262 Aug 2 15:52 xtrabackup_tablespaces
備份時間長短根據資料庫大小有關，在備份的期間可以隨時取消，因為xtrabackup命令不會對資料庫進行操作
全量恢復
--停止資料庫
[root@oracle data]#
service mysqld stop
Shutting down MySQL.. SUCCESS!
--刪除現有的data目錄
[root@oracle data]# rm -rf /usr/local/mysql/data/*
--執行恢復命令
[root@oracle data]#
xtrabackup --prepare --target-dir=/xtrabackup/
[root@oracle data]#
xtrabackup --copy-back --target-dir=/xtrabackup/
xtrabackup: recognized server arguments: --datadir=/usr/local/mysql/data --server-id=1
xtrabackup: recognized client arguments: --copy-back=1 --target-dir=/xtrabackup/
xtrabackup version 8.0.6 based on MySQL server 8.0.14 Linux (x86_64) (revision id: c0a2d91)
190802 16:33:39 [01] Copying undo_001 to /usr/local/mysql/data/undo_001
...省略...
190802 16:33:41 [01] ...done
190802 16:33:41 completed OK!
--對檔案授權
chwon -R mysql.mysql /usr/local/mysql/data/*
Tips:
--copy-back命令表示將備份
複製
到datadir目錄下，如果不想保留備份，可以使用--move-back命令，直接將備份
移動
到datadir目錄下
2.增量備份
在進行增量備份之前，透過先進行一次全量備份。XtraBackup透過二進位制方式在備份目錄下寫入xtrabackup_checkpoints檔案。該檔案其中一行會顯示to_lsn，
該引數記錄了資料庫備份完成的LSN。全量備份命令：
方式一：
基於base的備份
[root@oracle xtrabackup]# xtrabackup --user=root --backup --socket=/tmp/mysql.sock --target-dir=/xtrabackup/base
xtrabackup_checkpoints的內容：
[root@oracle xtrabackup]# cat /xtrabackup/base/xtrabackup_checkpoints
backup_type =
full-backuped
from_lsn = 0
to_lsn =
19957274
last_lsn = 19957284
flushed_lsn = 0
在進行了全量備份後
，我們可以透過
增量備份
的命令進行備份：
基於base的incr1備份
[root@oracle xtrabackup]# xtrabackup --user=root --backup --socket=/tmp/mysql.sock --target-dir=/xtrabackup/incr1 --incremental-basedir=
/xtrabackup/base
xtrabackup_checkpoints的內容：
[root@oracle xtrabackup]# cat /xtrabackup/incr1/xtrabackup_checkpoints
backup_type =
incremental
from_lsn =
19957274
to_lsn = 19957304
last_lsn = 19957314
flushed_lsn = 0
基於incr1的incr2備份
[root@oracle xtrabackup]# xtrabackup --user=root --backup --socket=/tmp/mysql.sock --target-dir=/xtrabackup/incr2 --incremental-basedir=
/xtrabackup/incr1/
xtrabackup_checkpoints的內容：
[root@oracle xtrabackup]# cat /xtrabackup/incr2/xtrabackup_checkpoints
backup_type =
incremental
from_lsn =
19957304
to_lsn = 19957364
last_lsn = 19957374
flushed_lsn = 0
結論：
假設週一是基於base的備份，週二是基於base的incr1備份，週三是基於incr1的incr2備份，在恢復資料庫的時候，需要使用base，incr1，incr2三個備份都存在時，才能進行完整的恢復，每個備份的from_lsn都是基於上一個備份的to_lsn，所以缺一不可。
方式二：
基於base的備份
[root@oracle xtrabackup]# xtrabackup --user=root --backup --socket=/tmp/mysql.sock --target-dir=/xtrabackup/base
xtrabackup_checkpoints的內容：
[root@oracle xtrabackup]# cat /xtrabackup/base/xtrabackup_checkpoints
backup_type =
full-backuped
from_lsn = 0
to_lsn =
19957274
last_lsn = 19957284
flushed_lsn = 0
基於base的incr1備份
[root@oracle xtrabackup]# xtrabackup --user=root --backup --socket=/tmp/mysql.sock --target-dir=/xtrabackup/incr1 --incremental-basedir=
/xtrabackup/base
xtrabackup_checkpoints的內容：
[root@oracle xtrabackup]# cat /xtrabackup/incr1/xtrabackup_checkpoints
backup_type =
incremental
from_lsn =
19957274
to_lsn = 19957304
last_lsn = 19957314
flushed_lsn = 0
基於base的incr2備份
[root@oracle xtrabackup]# xtrabackup --user=root --backup --socket=/tmp/mysql.sock --target-dir=/xtrabackup/incr2 --incremental-basedir=
/xtrabackup/base/
xtrabackup_checkpoints的內容：
[root@oracle xtrabackup]# cat /xtrabackup/incr2/xtrabackup_checkpoints
backup_type =
incremental
from_lsn =
19957274
to_lsn = 19957394
last_lsn = 19957404
flushed_lsn = 0
結論：假設週一是基於base的備份，週二是基於base的incr1備份，週三是基於base的incr2備份，在恢復資料庫的時候，需要使用base和incr1，incr2兩個備份中的其中一個，才能進行完整的恢復，因為incr1和incr2的from_lsn都是基於base備份中的to_lsn，所以恢復資料庫時，只需要base和任意一個基於base的增量備份。
3.增量備份恢復
增量備份和全量備份的--prepare執行的方式是不一樣的。在全量備份中，可以透過兩種操作保持資料庫的一致性:已提交的事務將根據資料檔案和日誌檔案進行重放操作，並回滾未提交的事務。在準備增量備份時，必須跳過未提交事務的回滾，因為在備份的過程中，可能存在進行中且未提交的事務，並且這些事務很可能在下一次的增量備份中才進行提交，所以必須使用--apply-log-only選項來防止回滾操作。
基於方式一的恢復：
3.1準備好備份片
[root@oracle xtrabackup]# ll
total 20
drwxr-x--- 6 root root 4096 Aug 5 08:30 base
drwxr-x--- 6 root root 4096 Aug 5 08:32 incr1
drwxr-x--- 6 root root 4096 Aug 5 08:52 incr2
3.2執行恢復命令
[root@oracle xtrabackup]# xtrabackup --prepare --apply-log-only --target-dir=/xtrabackup/base
[root@oracle xtrabackup]# xtrabackup --prepare --apply-log-only --target-dir=/xtrabackup/base --incremental-dir=/xtrabackup/incr1
[root@oracle xtrabackup]# xtrabackup --prepare --target-dir=/xtrabackup/base --incremental-dir=/xtrabackup/incr2
[root@oracle xtrabackup]# xtrabackup --copy-back --target-dir=/xtrabackup/base/
3.3對data目錄授權
[root@oracle mysql] chown -R mysql.mysql data/
基於方式二的恢復：
3.1準備好備份片
[root@oracle xtrabackup]# ll
total 20
drwxr-x--- 6 root root 4096 Aug 5 08:30 base
drwxr-x--- 6 root root 4096 Aug 5 08:32 incr1
drwxr-x--- 6 root root 4096 Aug 5 08:52 incr2
3.2執行恢復命令
[root@oracle xtrabackup]# xtrabackup --prepare --apply-log-only --target-dir=/xtrabackup/base
[root@oracle xtrabackup]# xtrabackup --prepare --target-dir=/xtrabackup/base --incremental-dir=/xtrabackup/incr2
[root@oracle xtrabackup]# xtrabackup --copy-back --target-dir=/xtrabackup/base/
3.3對data目錄授權
[root@oracle mysql] chown -R mysql.mysql data/
Tips:
--apply-log-only命令應該用在所有增量備份（除最後一次增量備份）
，這就是為什麼恢復指令碼中，最後一次的命令不包含--apply-log-only。即使--apply-log-only在最後一次增量備份時被使用，備份仍將是一致的，但在這種情況下，資料庫會執行回滾的操作。
4.壓縮備份
[root@oracle xtrabackup]# xtrabackup --user=root --backup
--compress
--socket=/tmp/mysql.sock --target-dir=/xtrabackup/compress
壓縮和未壓縮的對比
[root@oracle xtrabackup]# du -h --max-depth=1
57M ./base
3.3M ./compress
壓縮後的檔案列表
[root@oracle compress]# ll
total 2876
-rw-r----- 1 root root 453 Aug 5 09:02 backup-my.cnf.qp
-rw-r----- 1 root root 183 Aug 5 09:02 binlog.000018.qp
-rw-r----- 1 root root 93 Aug 5 09:02 binlog.index.qp
drwxr-x--- 2 root root 4096 Aug 5 09:02 company
-rw-r----- 1 root root 912 Aug 5 09:02 ib_buffer_pool.qp
-rw-r----- 1 root root 253886 Aug 5 09:02 ibdata1.qp
drwxr-x--- 2 root root 4096 Aug 5 09:02 mysql
-rw-r----- 1 root root 2191005 Aug 5 09:02 mysql.ibd.qp
drwxr-x--- 2 root root 4096 Aug 5 09:02 performance_schema
drwxr-x--- 2 root root 4096 Aug 5 09:02 sys
-rw-r----- 1 root root 224626 Aug 5 09:02 undo_001.qp
-rw-r----- 1 root root 220474 Aug 5 09:02 undo_002.qp
-rw-r----- 1 root root 105 Aug 5 09:02 xtrabackup_binlog_info.qp
-rw-r----- 1 root root 95 Aug 5 09:02 xtrabackup_checkpoints
-rw-r----- 1 root root 471 Aug 5 09:02 xtrabackup_info.qp
-rw-r----- 1 root root 333 Aug 5 09:02 xtrabackup_logfile.qp
-rw-r----- 1 root root 234 Aug 5 09:02 xtrabackup_tablespaces.qp
如果想要加速備份的速度，可以採用--compress-threads命令
[root@oracle xtrabackup]# xtrabackup --user=root --backup
--compress
--compress-threads=4
--socket=/tmp/mysql.sock --target-dir=/xtrabackup/compress
解壓縮
[root@oracle xtrabackup]# xtrabackup
--decompress
--target-dir=/xtrabackup/compress
報錯：
xtrabackup: recognized server arguments: --datadir=/usr/local/mysql/data --server-id=1
xtrabackup: recognized client arguments: --decompress=1 --target-dir=/xtrabackup/compress
xtrabackup version 8.0.6 based on MySQL server 8.0.14 Linux (x86_64) (revision id: c0a2d91)
190805 09:07:23 [01] decompressing ./ibdata1.qp
sh: qpress: command not found
cat: write error: Broken pipe
Error: decrypt and decompress thread 0 failed.
出現這種錯誤，使用如下解決方式：
[root@oracle ~]# wget
[root@oracle ~]# tar xf qpress-11-linux-x64.tar
[root@oracle ~]# cp qpress /usr/bin
重新解壓縮
[root@oracle xtrabackup]# xtrabackup
--decompress
--target-dir=/xtrabackup/compress
...省略...
190805 10:29:16 [01] decompressing ./mysql.ibd.qp
190805 10:29:16 [01] decompressing ./binlog.index.qp
190805 10:29:16 completed OK!
Tips:
--parallel命令可以配合--decompress一起使用
正常，檢視目錄檔案，會發現壓縮檔案和解壓檔案共存，如果不想保留原壓縮檔案，可以使用
--remove-original
命令
[root@oracle compress]# ll
total 59236
-rw-r--r-- 1 root root 476 Aug 5 10:29 backup-my.cnf
-rw-r----- 1 root root 453 Aug 5 09:07 backup-my.cnf.qp
-rw-r--r-- 1 root root 155 Aug 5 10:29 binlog.000019
-rw-r----- 1 root root 183 Aug 5 09:07 binlog.000019.qp
-rw-r--r-- 1 root root 16 Aug 5 10:29 binlog.index
-rw-r----- 1 root root 93 Aug 5 09:07 binlog.index.qp
drwxr-x--- 2 root root 4096 Aug 5 10:29 company
-rw-r--r-- 1 root root 3329 Aug 5 10:29 ib_buffer_pool
-rw-r----- 1 root root 912 Aug 5 09:07 ib_buffer_pool.qp
-rw-r--r-- 1 root root 12582912 Aug 5 10:29 ibdata1
-rw-r----- 1 root root 253881 Aug 5 09:07 ibdata1.qp
drwxr-x--- 2 root root 4096 Aug 5 10:29 mysql
-rw-r--r-- 1 root root 24117248 Aug 5 10:29 mysql.ibd
-rw-r----- 1 root root 2191005 Aug 5 09:07 mysql.ibd.qp
drwxr-x--- 2 root root 12288 Aug 5 10:29 performance_schema
drwxr-x--- 2 root root 4096 Aug 5 10:29 sys
-rw-r--r-- 1 root root 10485760 Aug 5 10:29 undo_001
-rw-r----- 1 root root 224626 Aug 5 09:07 undo_001.qp
-rw-r--r-- 1 root root 10485760 Aug 5 10:29 undo_002
-rw-r----- 1 root root 220474 Aug 5 09:07 undo_002.qp
-rw-r--r-- 1 root root 18 Aug 5 10:29 xtrabackup_binlog_info
-rw-r----- 1 root root 105 Aug 5 09:07 xtrabackup_binlog_info.qp
-rw-r----- 1 root root 95 Aug 5 09:07 xtrabackup_checkpoints
-rw-r--r-- 1 root root 529 Aug 5 10:29 xtrabackup_info
-rw-r----- 1 root root 486 Aug 5 09:07 xtrabackup_info.qp
-rw-r--r-- 1 root root 2560 Aug 5 10:29 xtrabackup_logfile
-rw-r----- 1 root root 342 Aug 5 09:07 xtrabackup_logfile.qp
-rw-r--r-- 1 root root 262 Aug 5 10:29 xtrabackup_tablespaces
-rw-r----- 1 root root 234 Aug 5 09:07 xtrabackup_tablespaces.qp
不保留原壓縮檔案，使用
--remove-original
[root@oracle compress]# xtrabackup --decompress
--remove-original
--target-dir=/xtrabackup/compress
檢視目錄檔案
[root@oracle compress]# ll
total 56380
-rw-r--r-- 1 root root 476 Aug 5 10:32 backup-my.cnf
-rw-r--r-- 1 root root 155 Aug 5 10:32 binlog.000019
-rw-r--r-- 1 root root 16 Aug 5 10:32 binlog.index
drwxr-x--- 2 root root 4096 Aug 5 10:32 company
-rw-r--r-- 1 root root 3329 Aug 5 10:32 ib_buffer_pool
-rw-r--r-- 1 root root 12582912 Aug 5 10:32 ibdata1
drwxr-x--- 2 root root 4096 Aug 5 10:32 mysql
-rw-r--r-- 1 root root 24117248 Aug 5 10:32 mysql.ibd
drwxr-x--- 2 root root 12288 Aug 5 10:32 performance_schema
drwxr-x--- 2 root root 4096 Aug 5 10:32 sys
-rw-r--r-- 1 root root 10485760 Aug 5 10:32 undo_001
-rw-r--r-- 1 root root 10485760 Aug 5 10:32 undo_002
-rw-r--r-- 1 root root 18 Aug 5 10:32 xtrabackup_binlog_info
-rw-r----- 1 root root 95 Aug 5 09:07 xtrabackup_checkpoints
-rw-r--r-- 1 root root 529 Aug 5 10:32 xtrabackup_info
-rw-r--r-- 1 root root 2560 Aug 5 10:32 xtrabackup_logfile
-rw-r--r-- 1 root root 262 Aug 5 10:32 xtrabackup_tablespaces
5.流式備份(Streaming Backups)
Percona XtraBackup支援xbstream流模式將備份傳送到STDOUT，而不是將檔案複製到備份目錄。這允許您使用其他程式過濾備份的輸出，從而為備份的儲存提供更大的靈活性。例如，透過輸出管道的方式可以實現壓縮且備份可以自動加密。
使用xbstream作為流選項，可以並行複製和壓縮備份，這可以顯著加快備份過程。 如果備份既壓縮又加密，則需要先解密才能解壓縮。
--並行壓縮且並行複製備份
[root@oracle /]# xtrabackup --backup --socket=/tmp/mysql.sock --compress --compress-threads=8 --stream=xbstream --parallel=4 --target-dir=/xtrabackup/ >backup.xbstream
--檢視結果
[root@oracle xtrabackup]# ll backup.xbstream
-rw-r--r-- 1 root root 3148078 Aug 6 15:01 backup.xbstream
恢復時必須先解壓xbstream格式，再解壓qb
--解壓xbstream
[root@oracle /]# xbstream -x < backup.xbstream -C /xtrabackup/
--檢視內容
[root@oracle xtrabackup]# ll
total 2920
-rw-r----- 1 root root 453 Aug 6 15:03 backup-my.cnf.qp
-rw-r----- 1 root root 183 Aug 6 15:03 binlog.000035.qp
-rw-r----- 1 root root 93 Aug 6 15:03 binlog.index.qp
drwxr-x--- 2 root root 4096 Aug 6 15:03 company
-rw-r----- 1 root root 945 Aug 6 15:03 ib_buffer_pool.qp
-rw-r----- 1 root root 315187 Aug 6 15:03 ibdata1.qp
drwxr-x--- 2 root root 4096 Aug 6 15:03 mysql
-rw-r----- 1 root root 2187997 Aug 6 15:03 mysql.ibd.qp
drwxr-x--- 2 root root 4096 Aug 6 15:03 performance_schema
drwxr-x--- 2 root root 4096 Aug 6 15:03 sys
-rw-r----- 1 root root 212804 Aug 6 15:03 undo_001.qp
-rw-r----- 1 root root 215761 Aug 6 15:03 undo_002.qp
-rw-r----- 1 root root 105 Aug 6 15:03 xtrabackup_binlog_info.qp
-rw-r----- 1 root root 95 Aug 6 15:03 xtrabackup_checkpoints
-rw-r----- 1 root root 498 Aug 6 15:03 xtrabackup_info.qp
-rw-r----- 1 root root 333 Aug 6 15:03 xtrabackup_logfile.qp
-rw-r----- 1 root root 234 Aug 6 15:03 xtrabackup_tablespaces.qp
--解壓qp
[root@oracle /]# xtrabackup --decompress --remove-original --target-dir=/xtrabackup/
--檢視內容
[root@oracle xtrabackup]# ll
total 56372
-rw-r--r-- 1 root root 476 Aug 6 15:04 backup-my.cnf
-rw-r--r-- 1 root root 155 Aug 6 15:04 binlog.000035
-rw-r--r-- 1 root root 16 Aug 6 15:04 binlog.index
drwxr-x--- 2 root root 4096 Aug 6 15:04 company
-rw-r--r-- 1 root root 3458 Aug 6 15:04 ib_buffer_pool
-rw-r--r-- 1 root root 12582912 Aug 6 15:04 ibdata1
drwxr-x--- 2 root root 4096 Aug 6 15:04 mysql
-rw-r--r-- 1 root root 24117248 Aug 6 15:04 mysql.ibd
drwxr-x--- 2 root root 4096 Aug 6 15:04 performance_schema
drwxr-x--- 2 root root 4096 Aug 6 15:04 sys
-rw-r--r-- 1 root root 10485760 Aug 6 15:04 undo_001
-rw-r--r-- 1 root root 10485760 Aug 6 15:04 undo_002
-rw-r--r-- 1 root root 18 Aug 6 15:04 xtrabackup_binlog_info
-rw-r----- 1 root root 95 Aug 6 15:03 xtrabackup_checkpoints
-rw-r--r-- 1 root root 544 Aug 6 15:04 xtrabackup_info
-rw-r--r-- 1 root root 2560 Aug 6 15:04 xtrabackup_logfile
-rw-r--r-- 1 root root 262 Aug 6 15:04 xtrabackup_tablespaces
6.加密備份（Encrypting Backups）
Percona XtraBackup支援使用xbstream選項加密和解密本地和流式備份，從而增加了另一層保護。使用GnuPG的libgcrypt庫實現加密
加密
--encrypt-key選項
透過ssl生成金鑰
[root@oracle xtrabackup]#
openssl rand -base64 24
LymjcKljN4xxtPUe3GkF/4mNzj3x4y9D
加密時指定金鑰
[root@oracle xtrabackup]# xtrabackup --backup
--encrypt=AES256 --encrypt-key="LymjcKljN4xxtPUe3GkF/4mNzj3x4y9D"
--target-dir=/xtrabackup/ --socket=/tmp/mysql.sock
檢視備份的檔案
[root@oracle xtrabackup]# ll
total 56468
-rw-r----- 1 root root 568 Aug 6 16:21 backup-my.cnf.xbcrypt
-rw-r----- 1 root root 247 Aug 6 16:21 binlog.000036.xbcrypt
-rw-r----- 1 root root 108 Aug 6 16:21 binlog.index.xbcrypt
drwxr-x--- 2 root root 4096 Aug 6 16:21 company
-rw-r----- 1 root root 3550 Aug 6 16:21 ib_buffer_pool.xbcrypt
-rw-r----- 1 root root 12600576 Aug 6 16:21 ibdata1.xbcrypt
drwxr-x--- 2 root root 4096 Aug 6 16:21 mysql
-rw-r----- 1 root root 24151104 Aug 6 16:21 mysql.ibd.xbcrypt
drwxr-x--- 2 root root 4096 Aug 6 16:21 performance_schema
drwxr-x--- 2 root root 4096 Aug 6 16:21 sys
-rw-r----- 1 root root 10500480 Aug 6 16:21 undo_001.xbcrypt
-rw-r----- 1 root root 10500480 Aug 6 16:21 undo_002.xbcrypt
-rw-r----- 1 root root 110 Aug 6 16:21 xtrabackup_binlog_info.xbcrypt
-rw-r----- 1 root root 95 Aug 6 16:21 xtrabackup_checkpoints
-rw-r----- 1 root root 595 Aug 6 16:21 xtrabackup_info.xbcrypt
-rw-r----- 1 root root 2744 Aug 6 16:21 xtrabackup_logfile.xbcrypt
-rw-r----- 1 root root 354 Aug 6 16:21 xtrabackup_tablespaces.xbcrypt
Tips:
透過使用--encrypt-threads選項，可以指定多個執行緒並行使用加密。選項--encrypt-chunk-size可用於指定每個加密執行緒的工作加密緩衝區的大小（以位元組為單位）（預設為64K）。
解密
--decrypt選項
執行解密，如果不想保留原壓縮檔案，可以使用
--remove-original
命令
[root@oracle xtrabackup]# xtrabackup
--remove-original
--decrypt=AES256 --encrypt-key="LymjcKljN4xxtPUe3GkF/4mNzj3x4y9D" --target-dir=/xtrabackup/
檢視備份檔案
[root@oracle xtrabackup]# ll
total 56380
-rw-r--r-- 1 root root 476 Aug 6 16:25 backup-my.cnf
-rw-r--r-- 1 root root 155 Aug 6 16:25 binlog.000036
-rw-r--r-- 1 root root 16 Aug 6 16:25 binlog.index
drwxr-x--- 2 root root 4096 Aug 6 16:25 company
-rw-r--r-- 1 root root 3458 Aug 6 16:25 ib_buffer_pool
-rw-r--r-- 1 root root 12582912 Aug 6 16:25 ibdata1
drwxr-x--- 2 root root 4096 Aug 6 16:25 mysql
-rw-r--r-- 1 root root 24117248 Aug 6 16:25 mysql.ibd
drwxr-x--- 2 root root 12288 Aug 6 16:25 performance_schema
drwxr-x--- 2 root root 4096 Aug 6 16:25 sys
-rw-r--r-- 1 root root 10485760 Aug 6 16:25 undo_001
-rw-r--r-- 1 root root 10485760 Aug 6 16:25 undo_002
-rw-r--r-- 1 root root 18 Aug 6 16:25 xtrabackup_binlog_info
-rw-r----- 1 root root 95 Aug 6 16:21 xtrabackup_checkpoints
-rw-r--r-- 1 root root 503 Aug 6 16:25 xtrabackup_info
-rw-r--r-- 1 root root 2560 Aug 6 16:25 xtrabackup_logfile
-rw-r--r-- 1 root root 262 Aug 6 16:25 xtrabackup_tablespaces
Tips:
--parallel可以與--decrypt選項一起使用來同時解密多個檔案。
7.部分備份和恢復(
不建議
)
部分備份
xtrabackup支援部分備份，前提是innodb_file_per_table選項被啟用。可以透過三種方式進行部分備份：
1.使用正規表示式匹配表名稱
2.將包含表名稱的列表放入一個檔案中
3.資料庫名稱列表
重要提示
關於部分備份只有一個注意事項：
不要複製準備好的備份。應該透過匯入表的方式來恢復部分備份，而不是使用--copy-back選項。
儘管在某些情況下可以透過複製檔案來進行恢復，但這可能導致資料庫在許多情況下出現不一致，強烈不建議透過這種方式去恢復。
--tables選項
第一種方式是透過xtrabackup --tables選項。該選項的值是一個正規表示式，用於完全匹配資料庫和表名稱，格式為database.table。
只備份company資料庫下的所有表，可以使用如下命令
[root@oracle ~]# xtrabackup
--socket=/tmp/mysql.sock --backup --target-dir=/xtrabackup/company
--tables="^company[.].*"
只備份company下的t1表，可以使用如下命令
[root@oracle ~]# xtrabackup
--socket=/tmp/mysql.sock --backup --target-dir=/xtrabackup/company_t1
--tables="^company[.]t1"
--tables-file選項
該命令指定某個檔案，可以檔案包含了多個要備份的表名，每行一個表名。只有在該檔案中的表才會被備份。表名必須完全匹配，區分大小寫，表名不能包含任何表示式。表名必須完全匹配database.table格式
檢視檔案內容
[root@oracle tmp]# cat /tmp/tables.txt
company.t1
company.t2
備份命令
[root@oracle tmp]# xtrabackup --socket=/tmp/mysql.sock --backup
--tables-file=/tmp/tables.txt
--target-dir=/xtrabackup/partial_tables
--databases選項
xtrabackup --databases支援以空格分隔的資料庫和表列表，格式為database[.table]。除此列表以外，請確保mysql, sys, performance_schema三個資料庫也包含在內，這些資料庫在恢復時需要使用xtrabackup --copy-back選項來恢復。
[root@oracle tmp]# xtrabackup --databases='mysql sys performance_schema ...'
-databases-file選項
該命令指定某個檔案，可以檔案包含了多個要備份的表名，每行一個表名。只有在該檔案中的資料庫和表才會被備份。表名必須完全匹配，區分大小寫，表名不能包含任何表示式。表名必須完全匹配database[.table]格式
部分恢復
在進行部分備份的恢復時，使用xtrabackup --prepare選項時，將會提示不存在該表的警告，這是因為這些表雖然存在於InnoDB的資料字典中，但是相應的.ibd檔案不存在。他們還未被複制到備份目錄中，當你恢復備份並啟動InnoDB時，這些表將會從資料字典中刪除，這時在日誌檔案中才不會出現任何錯誤或警告。
該過程類似於還原單個表：應用日誌並使用--export選項：
[root@oracle xtrabackup]# xtrabackup --prepare
--export
--target-dir=/xtrabackup/company_t1/
[root@oracle xtrabackup]# ll /xtrabackup/company_t1/company
-rw-r--r-- 1 root root 581 Aug 6 13:27 t1.cfg
-rw-r----- 1 root root 114688 Aug 6 13:27 t1.ibd
1.將t1表offline
mysql> alter table company.t1 discard tablespace;
2.cp複製
[root@oracle company]# cp
/xtrabackup/company_t1/company/* /usr/local/mysql/data/company/
3.授權
[root@oracle company]# chown -R mysql.mysql
/usr/local/mysql/data/company/t1.*
4.將t1表online
mysql> alter table company.t1 import tablespace;
BUG問題：
筆者在成功恢復後，又再次對該t1表重新進行部分備份和恢復，在部分恢復的時候，產生了如下
BUG
，所以不建議使用該功能進行備份和恢復
[root@oracle company]# xtrabackup --prepare --export --target-dir=/xtrabackup/company_t1/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=innodb --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=1 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0
xtrabackup: recognized client arguments: --prepare=1 --export=1 --target-dir=/xtrabackup/company_t1/
xtrabackup version 8.0.6 based on MySQL server 8.0.14 Linux (x86_64) (revision id: c0a2d91)
xtrabackup: auto-enabling --innodb-file-per-table due to the --export option
xtrabackup: cd to /xtrabackup/company_t1/
xtrabackup: This target seems to be not prepared yet.
Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(20169689)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
PUNCH HOLE support available
Mutexes and rw_locks use GCC atomic builtins
Uses event mutexes
GCC builtin __atomic_thread_fence() is used for memory barrier
Compressed tables use zlib 1.2.3
Number of pools: 1
Using CPU crc32 instructions
Directories to scan '.;.;.'
Scanning './'
Completed space ID check of 4 files.
Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M
Completed initialization of buffer pool
page_cleaner coordinator priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
The log sequence number 20140501 in the system tablespace does not match the log sequence number 20169689 in the ib_logfiles!
Database was not shutdown normally!
Starting crash recovery.
Starting to parse redo log at lsn = 20169366, whereas checkpoint_lsn = 20169689
Doing recovery: scanned up to log sequence number 20169709
Log background threads are being started...
Applying a batch of 1 redo log records ...
100%
Apply batch completed!
Using undo tablespace './undo_001'.
Using undo tablespace './undo_002'.
Opened 2 existing undo tablespaces.
Creating shared tablespace for temporary tables
Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
File './ibtmp1' size is now 12 MB.
Scanning temp tablespace dir:'./#innodb_temp/'
Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active.
8.0.14 started; log sequence number 20169709
Allocated tablespace ID 10 for company/t1, old maximum was 0
InnoDB: Assertion failure: dict0dict.cc:1215:table2 == NULL
InnoDB: thread 140589081102528InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to
.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: about forcing recovery.
05:34:21 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.
key_buffer_size=0
read_buffer_size=131072
max_used_connections=0
max_threads=0
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1676 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x5ed7650
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7fff80e30ba8 thread_stack 0x46000
xtrabackup(my_print_stacktrace(unsigned char*, unsigned long)+0x2e) [0x1fc2d9e]
xtrabackup(handle_fatal_signal+0x413) [0xfe8883]
/lib64/libpthread.so.0() [0x3720a0f7e0]
/lib64/libc.so.6(gsignal+0x35) [0x3720632495]
/lib64/libc.so.6(abort+0x175) [0x3720633c75]
xtrabackup(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x9b) [0x141105b]
xtrabackup(dict_table_add_to_cache(dict_table_t*, unsigned long, mem_block_info_t*)+0x11c) [0x115b2fc]
xtrabackup(dd_table_create_on_dd_obj(dd::Table const*, dd::Partition const*, std::basic_string<char, std::char_traits<char>, Stateless_allocator<char, dd::String_type_alloc, My_free_functor> > const*, bool)+0x16f7) [0x117e897]
xtrabackup(dd_table_load_part(unsigned long, dd::Table const&, dd::Partition const*, dict_table_t*&, THD*, std::basic_string<char, std::char_traits<char>, Stateless_allocator<char, dd::String_type_alloc, My_free_functor> > const*, bool)+0x40e) [0x117f7fe]
xtrabackup(dd_table_load_on_dd_obj(dd::cache::Dictionary_client*, unsigned int, dd::Table const&, dict_table_t*&, THD*, std::basic_string<char, std::char_traits<char>, Stateless_allocator<char, dd::String_type_alloc, My_free_functor> > const*, bool)+0x50) [0x117f870]
xtrabackup(dict_load_tables_from_space_id(unsigned int, THD*, trx_t*)+0x685) [0xba5805]
xtrabackup() [0xba5ebd]
xtrabackup() [0xbad711]
xtrabackup(main+0x704) [0xb6f134]
/lib64/libc.so.6(__libc_start_main+0xfd) [0x372061ed1d]
xtrabackup() [0xb9b9a5]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): Connection ID (thread ID): 0
Status: NOT_KILLED
Please report a bug at
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29812844/viewspace-2658452/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- mysql之 xtrabackup-2.4.12 安裝MySql
- MySQL8.0變化之密碼策略MySql密碼
- MySQL入門學習之——實戰XtraBackupMySql
- MySQL8.0之跳躍範圍掃描MySql
- 【Xtrabackup】Xtrabackup備份基礎知識
- xtrabackup: Error: cannot open /../../base/xtrabackup_checkpointsError
- MySQL 之XtraBackup全量增量熱備實踐MySql
- Xtrabackup之innobackupex備份恢復詳解薦
- XtraBackup工具詳解 Part 2 xtrabackup安裝
- XtraBackup工具詳解 Part 3 XtraBackup工作原理
- mysql8.0插入慢之sync_binlog(一)MySql
- MySQL8.0的坑之lower_case_table_namesMySql
- 安裝xtrabackup
- 【Xtrabackup】Xtrabackup全備、增量備份及恢復示例
- mysql之 percona-xtrabackup 2.4.7安裝（熱備工具）MySql
- XtraBackup工具詳解 Part 4 XtraBackup許可權及配置
- MySQL：xtrabackup備份MySql
- RHEL安裝Xtrabackup
- MySQL Percona XtraBackupMySql
- XtraBackup備份MySQLMySql
- Percona XtraBackup 2.4 xtrabackup全量、增量備份恢復流程
- MYSQL8.0安裝MySql
- Mysql8.0 報錯MySql
- MySQL 備份軟體 Xtrabackup 的 xtrabackup_binlog_pos_innodb和xtrabackup_binlog_info 檔案區別MySql
- mysql之 xtrabackup原理、備份日誌分析、備份資訊獲取MySql
- xtrabackup備份mysql innodbMySql
- Xtrabackup安裝及使用
- Mac 安裝 MySQL8.0MacMySql
- mysql8.0修改密碼MySql密碼
- Docker 安裝 MySQL8.0DockerMySql
- MySQL8.0效能優化MySql優化
- MySQL8.0初體驗MySql
- MySQL8.0：The General Query LogMySql
- Xtrabackup 2.4.14使用指南
- XtraBackup實踐(一)備份
- mysql xtrabackup 遭遇嚴重bugMySql
- Xtrabackup備份恢復原理
- XtraBackup 搭建從庫的一般步驟及 XtraBackup 8.0 的注意事項