一、Xtrabackup 說(shuō)明及安裝
成都創(chuàng)新互聯(lián)專(zhuān)注為客戶(hù)提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于成都做網(wǎng)站、網(wǎng)站建設(shè)、象山網(wǎng)絡(luò)推廣、小程序開(kāi)發(fā)、象山網(wǎng)絡(luò)營(yíng)銷(xiāo)、象山企業(yè)策劃、象山品牌公關(guān)、搜索引擎seo、人物專(zhuān)訪(fǎng)、企業(yè)宣傳片、企業(yè)代運(yùn)營(yíng)等，從售前售中售后，我們都將竭誠(chéng)為您服務(wù)，您的肯定，是我們最大的嘉獎(jiǎng)；成都創(chuàng)新互聯(lián)為所有大學(xué)生創(chuàng)業(yè)者提供象山建站搭建服務(wù)，24小時(shí)服務(wù)熱線(xiàn)：13518219792，官方網(wǎng)址：www.cdcxhl.com
一、Xtrabackup 說(shuō)明及安裝
二、Xtrabackup 全備份及恢復(fù)
三、Xtrabackup 全備和增量備份及恢復(fù)
四、Xtrabackup 部分全備和增量及恢復(fù)
一、Xtrabackup 說(shuō)明及安裝
1.Xtrabackup 介紹及說(shuō)明
Percona XtraBackup是世界上唯一開(kāi)放源碼的免費(fèi)MySQL熱備份軟件，可以執(zhí)行非阻塞
InnoDB和XtraDB數(shù)據(jù)庫(kù)的備份。使用Percona XtraBackup，您可以實(shí)現(xiàn)以下好處：
快速可靠地完成備份
備份期間不間斷的事務(wù)處理
節(jié)省磁盤(pán)空間和網(wǎng)絡(luò)帶寬
自動(dòng)備份驗(yàn)證
由于更快的恢復(fù)時(shí)間，更長(zhǎng)的正常運(yùn)行時(shí)間
Percona XtraBackup為所有版本的Percona Server，MySQL和MariaDB提供了MySQL熱備份。它執(zhí)行
流式傳輸，壓縮和增量MySQL備份。
Percona XtraBackup適用于MySQL，MariaDB和Percona Server。它支持完全非阻塞備份
的InnoDB，XtraDB和HailDB存儲(chǔ)引擎。此外，它可以簡(jiǎn)單地備份以下存儲(chǔ)引擎
在備份結(jié)束時(shí)暫停寫(xiě)入：MyISAM，Merge和Archive，包括分區(qū)表，觸發(fā)器和
數(shù)據(jù)庫(kù)選項(xiàng)。
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
額外需要安裝一些依賴(lài)perl包
2.4.查看安裝結(jié)果
# 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 準(zhǔn)備全備
1.1.開(kāi)始全備
創(chuàng)建備份目錄
# mkdir /data/backups
準(zhǔn)備前數(shù)據(jù)查看：
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.開(kāi)始備份
# 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 #二進(jìn)制日志文件 日志POS點(diǎn) 時(shí)間戳 LSN，日志序列號(hào)，Innodb的日志序列號(hào)是一個(gè)64位的整型 # cat xtrabackup_checkpoints backup_type = full-backuped #備份類(lèi)型為全備 from_lsn = 0 #LSN起始位置 to_lsn = 1634473 #LSN結(jié)束位置 last_lsn = 1634473 #最新LSN位置 compact = 0 recover_binlog_info = 0
1.4.模擬刪除文件
# rm -rf /data/mysql/3306/data
2.準(zhǔn)備恢復(fù)
創(chuàng)建備份后，數(shù)據(jù)未準(zhǔn)備好還原?？赡軙?huì)撤銷(xiāo)未提交的事務(wù)或要重放日志中的事務(wù)。做這些等待操作將使數(shù)據(jù)文件保持一致，這是準(zhǔn)備階段的目的。一旦完成，數(shù)據(jù)就可以使用了。要使用innobackupex準(zhǔn)備備份，您必須使用--apply-log和備份目錄的路徑作為參數(shù)：
2.1.停止數(shù)據(jù)庫(kù)
# service mysqld stop
2.2.恢復(fù)準(zhǔn)備
# 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.開(kāi)始恢復(fù)
# 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.查看恢復(fù)的文件
# 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
添加權(quán)限，啟動(dòng)數(shù)據(jù)庫(kù)：
# chown -R mysql.mysql data/ # service mysqld start
查看恢復(fù)結(jié)果：
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 全備和增量備份及恢復(fù)
1.執(zhí)行全備，上面已經(jīng)備份，這次不需要
2.增量備份
2.1.添加第一次數(shù)據(jù)
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.執(zhí)行第一次增量備份
# 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!
查看備份結(jié)果：
# 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.增加第二次數(shù)據(jù)
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.執(zhí)行第二次增量備份
# 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!
查看備份結(jié)果：
# 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.模擬刪除數(shù)據(jù)庫(kù)
# rm -rf /data/mysql/3306/data/
4.增量備份恢復(fù)
使用innobackupex準(zhǔn)備增量備份準(zhǔn)備增量備份有點(diǎn)不同
完整的。這也許是需要更多關(guān)注的階段：
首先，必須在每個(gè)備份上提交的事務(wù)。這將合并基數(shù)備份與增量。
然后，必須回滾未提交的事務(wù)才能進(jìn)行即用備份。
如果您重放提交的事務(wù)并在基本備份上回滾未提交的事務(wù)，那么您將無(wú)法進(jìn)行
添加增量。如果您以增量方式執(zhí)行此操作，則無(wú)法從該時(shí)刻添加數(shù)據(jù)
和剩余的增量。
4.1.全備恢復(fù)準(zhǔn)備
# innobackupex --apply-log --redo-only /data/backups/2017-04-30_23-04-14/
4.1.第一次增量備份恢復(fù)準(zhǔn)備
# 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.第二次增量備份恢復(fù)準(zhǔn)備
# innobackupex --apply-log --redo-only /data/backups/2017-04-30_23-04-14/ --incremental-dir=/data/backups/2017-04-30_23-41-46/
5.開(kāi)始恢復(fù)
# innobackupex --copy-back /data/backups/2017-04-30_23-04-14/
6.查看恢復(fù)數(shù)據(jù)及文件
# chown -R mysql.mysql /data/mysql/3306/data/ # service mysqld start
查看數(shù)據(jù)
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)
兩次增量備份數(shù)據(jù)都已恢復(fù)
四、Xtrabackup 部分全備和增量及恢復(fù)
Percona XtraBackup提供部分備份，這意味著您只能備份一些特定的表或
數(shù)據(jù)庫(kù)。您備份的表必須在單獨(dú)的表空間中，因?yàn)樵谀髣?chuàng)建或更改
在 查看備份文件： 2.單個(gè)庫(kù)增量備份 3.增加數(shù)據(jù)，第二次增量備份 3.1.增加數(shù)據(jù) 3.2.開(kāi)始第二次增量備份： 4.模擬刪除數(shù)據(jù)庫(kù) 5.準(zhǔn)備恢復(fù) 5.1.準(zhǔn)備恢復(fù)全備 5.2.準(zhǔn)備第一次增量備份 5.3.準(zhǔn)備第二次增量備份 6.開(kāi)始恢復(fù) 權(quán)限添加： 查看數(shù)據(jù)：# 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
#innobackupex --include='ckldb' --incremental /data/backups/ --incremental-basedir=2017-05-01_00-07-31
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)
# innobackupex --include='ckldb' --incremental /data/backups/ --incremental-basedir=/data/backups/2017-05-01_00-10-00/
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)
# innobackupex --apply-log --export /data/backups/2017-05-01_00-07-31/
# innobackupex --apply-log --redo-only /data/backups/2017-05-01_00-07-31/ --incremental-dir=/data/backups/2017-05-01_00-10-00/
# innobackupex --apply-log --redo-only /data/backups/2017-05-01_00-07-31/ --incremental-dir=/data/backups/2017-05-01_00-13-35/
# \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)
本文題目：Xtrabackup備份恢復(fù)
本文URL：http://weahome.cn/article/gcjspi.html
重慶分公司
重慶分公司
