溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
一、Xtrabackup 說明及安裝
一、Xtrabackup 說明及安裝
二、Xtrabackup 全備份及恢復
三、Xtrabackup 全備和增量備份及恢復
四、Xtrabackup 部分全備和增量及恢復
一、Xtrabackup 說明及安裝
1.Xtrabackup 介紹及說明
Percona XtraBackup是世界上唯一開放源碼的免費MySQL熱備份軟件，可以執行非阻塞
InnoDB和XtraDB數據庫的備份。使用Percona XtraBackup，您可以實現以下好處：
快速可靠地完成備份
備份期間不間斷的事務處理
節省磁盤空間和網絡帶寬
自動備份驗證
由于更快的恢復時間，更長的正常運行時間
Percona XtraBackup為所有版本的Percona Server，MySQL和MariaDB提供了MySQL熱備份。它執行
流式傳輸，壓縮和增量MySQL備份。
Percona XtraBackup適用于MySQL，MariaDB和Percona Server。它支持完全非阻塞備份
的InnoDB，XtraDB和HailDB存儲引擎。此外，它可以簡單地備份以下存儲引擎
在備份結束時暫停寫入：MyISAM，Merge和Archive，包括分區表，觸發器和
數據庫選項。
2、Xtrabackup 下載安裝
2.1.下載地址
https://www.percona.com/downloads/XtraBackup/LATEST/
2.3.安裝
yum install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
額外需要安裝一些依賴perl包
2.4.查看安裝結果
# rpm -ql percona-xtrabackup-24-2.4.7-1.el7.x86_64 /usr/bin/innobackupex /usr/bin/xbcloud /usr/bin/xbcloud_osenv /usr/bin/xbcrypt /usr/bin/xbstream /usr/bin/xtrabackup /usr/share/doc/percona-xtrabackup-24-2.4.7 /usr/share/doc/percona-xtrabackup-24-2.4.7/COPYING /usr/share/man/man1/innobackupex.1.gz /usr/share/man/man1/xbcrypt.1.gz /usr/share/man/man1/xbstream.1.gz /usr/share/man/man1/xtrabackup.1.gz
二、Xtrabackup 全備份
1.Xtrabackup 準備全備
1.1.開始全備
創建備份目錄
# mkdir /data/backups
準備前數據查看：
MariaDB [ckldb]> select * from jone; +------+--------+ | id | name | +------+--------+ | 1 | wukaka | | 2 | side | | 3 | ckl | | 4 | kk | | 5 | zld | | 6 | ned | | 7 | stark | +------+--------+ 7 rows in set (0.00 sec)
1.2.開始備份
# innobackupex --user=root /data/backups/ 170430 23:04:14 innobackupex: Starting the backup operation IMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!". 170430 23:04:14 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/tmp/mysql.sock' as 'root' (using password: NO). 170430 23:04:14 version_check Connected to MySQL server 170430 23:04:14 version_check Executing a version check against the server... 170430 23:04:14 version_check Done. 170430 23:04:14 Connecting to MySQL server host: localhost, user: root, password: not set, port: 3306, socket: /tmp/mysql.sock Using server version 10.1.22-MariaDB innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 6f7a799) xtrabackup: uses posix_fadvise(). xtrabackup: cd to /data/mysql/3306/data xtrabackup: open files limit requested 0, set to 1024 xtrabackup: using the following InnoDB configuration: xtrabackup: innodb_data_home_dir = /data/mysql/3306/data xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend xtrabackup: innodb_log_group_home_dir = /data/mysql/3306/data xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 67108864 ........ 170430 23:04:24 Executing UNLOCK TABLES 170430 23:04:24 All tables unlocked 170430 23:04:24 Backup created in directory '/data/backups/2017-04-30_23-04-14/' MySQL binlog position: filename 'mysql-bin.000009', position '745', GTID of the last change '0-1-62' 170430 23:04:24 [00] Writing backup-my.cnf 170430 23:04:24 [00] ...done 170430 23:04:24 [00] Writing xtrabackup_info 170430 23:04:24 [00] ...done xtrabackup: Transaction log of lsn (1634473) to (1634473) was copied. 170430 23:04:25 completed OK!
1.3.查看備份文件
# ll /data/backups/2017-04-30_23-04-14/ total 75800 -rw-r-----. 1 root root 418 Apr 30 23:04 backup-my.cnf drwxr-x---. 2 root root 52 Apr 30 23:04 ckldb -rw-r-----. 1 root root 77594624 Apr 30 23:04 ibdata1 drwxr-x---. 2 root root 4096 Apr 30 23:04 mysql drwxr-x---. 2 root root 20 Apr 30 23:04 performance_schema drwxr-x---. 2 root root 20 Apr 30 23:04 test -rw-r-----. 1 root root 28 Apr 30 23:04 xtrabackup_binlog_info -rw-r-----. 1 root root 113 Apr 30 23:04 xtrabackup_checkpoints -rw-r-----. 1 root root 494 Apr 30 23:04 xtrabackup_info -rw-r-----. 1 root root 2560 Apr 30 23:04 xtrabackup_logfile # cd /data/backups/2017-04-30_23-04-14/ # cat xtrabackup_binlog_info mysql-bin.000009 745 0-1-62 #二進制日志文件 日志POS點 時間戳 LSN，日志序列號，Innodb的日志序列號是一個64位的整型 # cat xtrabackup_checkpoints backup_type = full-backuped #備份類型為全備 from_lsn = 0 #LSN起始位置 to_lsn = 1634473 #LSN結束位置 last_lsn = 1634473 #最新LSN位置 compact = 0 recover_binlog_info = 0
1.4.模擬刪除文件
# rm -rf /data/mysql/3306/data
2.準備恢復
創建備份后，數據未準備好還原。可能會撤銷未提交的事務或要重放日志中的事務。做這些等待操作將使數據文件保持一致，這是準備階段的目的。一旦完成，數據就可以使用了。要使用innobackupex準備備份，您必須使用--apply-log和備份目錄的路徑作為參數：
2.1.停止數據庫
# service mysqld stop
2.2.恢復準備
# innobackupex --apply-log /data/backups/2017-04-30_23-04-14/ 170430 23:18:35 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!". ....... InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 1634856 170430 23:18:46 completed OK!
2.3.開始恢復
# innobackupex --copy-back /data/backups/2017-04-30_23-04-14/ 170430 23:20:31 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!". ...... 170430 23:20:42 [01] Copying ./ibtmp1 to /data/mysql/3306/data/ibtmp1 170430 23:20:42 [01] ...done 170430 23:20:43 completed OK!
2.4.查看恢復的文件
# ll total 219148 drwxr-x---. 2 root root 52 Apr 30 23:20 ckldb -rw-r-----. 1 root root 77594624 Apr 30 23:20 ibdata1 -rw-r-----. 1 root root 67108864 Apr 30 23:20 ib_logfile0 -rw-r-----. 1 root root 67108864 Apr 30 23:20 ib_logfile1 -rw-r-----. 1 root root 12582912 Apr 30 23:20 ibtmp1 drwxr-x---. 2 root root 4096 Apr 30 23:20 mysql drwxr-x---. 2 root root 20 Apr 30 23:20 performance_schema drwxr-x---. 2 root root 20 Apr 30 23:20 test -rw-r-----. 1 root root 23 Apr 30 23:20 xtrabackup_binlog_pos_innodb -rw-r-----. 1 root root 494 Apr 30 23:20 xtrabackup_info
添加權限，啟動數據庫：
# chown -R mysql.mysql data/ # service mysqld start
查看恢復結果：
MariaDB [(none)]> use ckldb Database changed MariaDB [ckldb]> show tables; +-----------------+ | Tables_in_ckldb | +-----------------+ | jone | +-----------------+ 1 row in set (0.00 sec) MariaDB [ckldb]> select * from jone; +------+--------+ | id | name | +------+--------+ | 1 | wukaka | | 2 | side | | 3 | ckl | | 4 | kk | | 5 | zld | | 6 | ned | | 7 | stark | +------+--------+ 7 rows in set (0.01 sec)
三、Xtrabackup 全備和增量備份及恢復
1.執行全備，上面已經備份，這次不需要
2.增量備份
2.1.添加第一次數據
MariaDB [ckldb]> insert into jone values(8,'lanester'),(9,'gonku'); Query OK, 2 rows affected (0.39 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [ckldb]> select * from jone; +------+----------+ | id | name | +------+----------+ | 1 | wukaka | | 2 | side | | 3 | ckl | | 4 | kk | | 5 | zld | | 6 | ned | | 7 | stark | | 8 | lanester | | 9 | gonku | +------+----------+ 9 rows in set (0.00 sec)
2.2.執行第一次增量備份
# innobackupex --incremental /data/backups/ --incremental-basedir=/data/backups/2017-04-30_23-04-14/ 170430 23:37:49 innobackupex: Starting the backup operation IMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!". ....... xtrabackup: Transaction log of lsn (1636718) to (1636718) was copied. 170430 23:37:56 completed OK!
查看備份結果：
# ls /data/backups/ 2017-04-30_23-04-14 2017-04-30_23-37-49 # cd /data/backups/2017-04-30_23-37-49 backup_type = incremental from_lsn = 1634473 to_lsn = 1636718 last_lsn = 1636718 compact = 0 recover_binlog_info = 0
2.3.增加第二次數據
MariaDB [ckldb]> insert into jone values(10,'dragon'),(11,'lanli'); Query OK, 2 rows affected (0.61 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [ckldb]> select * from jone; +------+----------+ | id | name | +------+----------+ | 1 | wukaka | | 2 | side | | 3 | ckl | | 4 | kk | | 5 | zld | | 6 | ned | | 7 | stark | | 8 | lanester | | 9 | gonku | | 10 | dragon | | 11 | lanli | +------+----------+ 11 rows in set (0.00 sec)
2.4.執行第二次增量備份
# innobackupex --incremental /data/backups/ --incremental-basedir=/data/backups/2017-04-30_23-37-49/ 170430 23:41:46 innobackupex: Starting the backup operation IMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!". ...... xtrabackup: Transaction log of lsn (1638894) to (1638894) was copied. 170430 23:41:54 completed OK!
查看備份結果：
# du -sh /data/backups/* 224M /data/backups/2017-04-30_23-04-14 1.3M /data/backups/2017-04-30_23-37-49 1.1M /data/backups/2017-04-30_23-41-46 # cd /data/backups/2017-04-30_23-41-46/ # cat xtrabackup_checkpoints backup_type = incremental from_lsn = 1636718 to_lsn = 1638894 last_lsn = 1638894 compact = 0 recover_binlog_info = 0
3.模擬刪除數據庫
# rm -rf /data/mysql/3306/data/
4.增量備份恢復
使用innobackupex準備增量備份準備增量備份有點不同
完整的。這也許是需要更多關注的階段：
首先，必須在每個備份上提交的事務。這將合并基數備份與增量。
然后，必須回滾未提交的事務才能進行即用備份。
如果您重放提交的事務并在基本備份上回滾未提交的事務，那么您將無法進行
添加增量。如果您以增量方式執行此操作，則無法從該時刻添加數據
和剩余的增量。
4.1.全備恢復準備
# innobackupex --apply-log --redo-only /data/backups/2017-04-30_23-04-14/
4.1.第一次增量備份恢復準備
# innobackupex --apply-log --redo-only /data/backups/2017-04-30_23-04-14/ --incremental-dir=/data/backups/2017-04-30_23-37-49
4.2.第二次增量備份恢復準備
# innobackupex --apply-log --redo-only /data/backups/2017-04-30_23-04-14/ --incremental-dir=/data/backups/2017-04-30_23-41-46/
5.開始恢復
# innobackupex --copy-back /data/backups/2017-04-30_23-04-14/
6.查看恢復數據及文件
# chown -R mysql.mysql /data/mysql/3306/data/ # service mysqld start
查看數據
MariaDB [(none)]> use ckldb Database changed MariaDB [ckldb]> select * from jone; +------+----------+ | id | name | +------+----------+ | 1 | wukaka | | 2 | side | | 3 | ckl | | 4 | kk | | 5 | zld | | 6 | ned | | 7 | stark | | 8 | lanester | | 9 | gonku | | 10 | dragon | | 11 | lanli | +------+----------+ 11 rows in set (0.00 sec)
兩次增量備份數據都已恢復
四、Xtrabackup 部分全備和增量及恢復
Percona XtraBackup提供部分備份，這意味著您只能備份一些特定的表或
數據庫。您備份的表必須在單獨的表空間中，因為在您之后創建或更改
在服務器上啟用了innodb_file_per_table選項。
只有一個關于部分備份的警告：不要復制備份的備份。恢復部分備份
應該通過導入表來完成，而不是使用傳統的-copy-back選項。雖然有一些
通過復制文件可以進行恢復的場景，這可能導致數據庫不一致
案例并不是推薦的方法。
創建部分備份有三種方式可以指定要備份整個數據的哪一部分：常規
表達式（--include），枚舉文件中的表（--tables-file）或提供數據庫列表（-數據庫）。
使用--include選項提供給這個的正則表達式將與完全限定匹配
表名，包括數據庫名稱，格式為databasename.tablename。
1.單個庫全備
# innobackupex --include='ckldb' /data/backups/ 170501 00:07:31 innobackupex: Starting the backup operation IMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!". ....... 170501 00:07:35 Backup created in directory '/data/backups/2017-05-01_00-07-31/' MySQL binlog position: filename 'mysql-bin.000001', position '313', GTID of the last change '' 170501 00:07:35 [00] Writing backup-my.cnf 170501 00:07:35 [00] ...done 170501 00:07:35 [00] Writing xtrabackup_info 170501 00:07:35 [00] ...done xtrabackup: Transaction log of lsn (1634875) to (1634875) was copied. 170501 00:07:35 completed OK!
查看備份文件：
# cd /data/backups/2017-05-01_00-07-31/ [root@localhost 2017-05-01_00-07-31]# ls backup-my.cnf ckldb ibdata1 xtrabackup_binlog_info xtrabackup_checkpoints xtrabackup_info xtrabackup_logfile
2.單個庫增量備份
#innobackupex --include='ckldb' --incremental /data/backups/ --incremental-basedir=2017-05-01_00-07-31
3.增加數據，第二次增量備份
3.1.增加數據
MariaDB [ckldb]> insert into jone values(12,'firegod'),(13,'sevenGod'); Query OK, 2 rows affected (0.38 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [ckldb]> select * from jone; +------+----------+ | id | name | +------+----------+ | 1 | wukaka | | 2 | side | | 3 | ckl | | 4 | kk | | 5 | zld | | 6 | ned | | 7 | stark | | 8 | lanester | | 9 | gonku | | 10 | dragon | | 11 | lanli | | 12 | firegod | | 13 | sevenGod | +------+----------+ 13 rows in set (0.00 sec)
3.2.開始第二次增量備份：
# innobackupex --include='ckldb' --incremental /data/backups/ --incremental-basedir=/data/backups/2017-05-01_00-10-00/
4.模擬刪除數據庫
MariaDB [(none)]> drop database ckldb; Query OK, 1 row affected (0.19 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec)
5.準備恢復
5.1.準備恢復全備
# innobackupex --apply-log --export /data/backups/2017-05-01_00-07-31/
5.2.準備第一次增量備份
# innobackupex --apply-log --redo-only /data/backups/2017-05-01_00-07-31/ --incremental-dir=/data/backups/2017-05-01_00-10-00/
5.3.準備第二次增量備份
# innobackupex --apply-log --redo-only /data/backups/2017-05-01_00-07-31/ --incremental-dir=/data/backups/2017-05-01_00-13-35/
6.開始恢復
# \cp -rf /data/backups/2017-05-01_00-07-31/* /data/mysql/3306/data/
權限添加：
# chown -R mysql.mysql /data/mysql/3306/data/ # service mysqld start
查看數據：
MariaDB [(none)]> use ckldb Database changed MariaDB [ckldb]> select * from jone; +------+----------+ | id | name | +------+----------+ | 1 | wukaka | | 2 | side | | 3 | ckl | | 4 | kk | | 5 | zld | | 6 | ned | | 7 | stark | | 8 | lanester | | 9 | gonku | | 10 | dragon | | 11 | lanli | | 12 | firegod | | 13 | sevenGod | +------+----------+ 13 rows in set (0.06 sec)
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
