xtrabackup 是由percona公司開源免費的資料庫熱備軟體，它能對InnoDB資料庫和XtraDB存儲引擎的資料庫非阻塞地備份，對于myisam的備份同樣需要加表鎖。mysqldump備份方式是通過使用者名密碼連接配接到資料庫，将其要備份的資料庫用SQL語句的形式輸出到标準輸出，這種邏輯備份的方式最大的缺點是備份和恢複的速度較慢，如果資料庫過大，mysqldump備份就顯得力不從心了。這時選擇用xtrabackup來備份就能很好解決資料庫過大而導緻備份過慢的問題。
一、xtrabackup 簡介
xtrabackup 是由percona公司開源免費的資料庫熱備軟體，它能對InnoDB資料庫和XtraDB存儲引擎的資料庫非阻塞地備份，對于myisam的備份同樣需要加表鎖。mysqldump備份方式是通過使用者名密碼連接配接到資料庫，将其要備份的資料庫用SQL語句的形式輸出到标準輸出，這種邏輯備份的方式最大的缺點是備份和恢複的速度較慢，如果資料庫過大，mysqldump備份就顯得力不從心了。這時選擇用xtrabackup來備份就能很好解決資料庫過大而導緻備份過慢的問題。
xtrabackup的優點
1）備份還原過程快速、可靠
2）備份過程不會打斷正在執行的事務
3）能夠基于壓縮等功能節約磁盤空間和流量
4）自動實作備份檢驗
5）開源，免費
xtrabackup2.2版本之前包括4個可執行檔案：
innobackuppex：這個是一個perl腳本，它的主要作用是用來備份非innodb表，同時會調用xtrabackup指令來備份innodb表，還會和mysql server 發送指令進行互動，如家全局讀鎖（FTWRL）、擷取位置點（show slave status）等。
xtrabackup:這個檔案是用C/C++編譯的二進制檔案，它的主要作用用來備份Innodb表達，不能備份非Innodb表，也沒有和mysql server互動。
xbcrypt:這個檔案的主要作用用于加密解密
xbstream:這個檔案的主要作用支援并發寫的流檔案格式
xtrabackup版本更新到2.4後，相比之前的2.1有了較大的變化，innobackupex功能全部內建到xtrabackup裡面，隻有一個二進制程式，另外為了相容考慮，innobackupex作為xtrabackup的軟連接配接，換句話說xtrabackup現在支援非Innodb表備份，并且innobackupex在下一個版本中移除，是以建議xtrabackup替換innobackupex
xtrabackup備份原理
1）備份開始時會啟動兩個程序一個是主程序innobackuppex，一個是子程序xtrabackup
2）xtrabackup建立一個redo線程，用于拷貝和實時監測msyql redo的變化，一旦有新的資料寫入，它将立即把日志記錄到日志檔案xtrabackup_log中。
3）xtrabackup建立一個ibd線程，用于拷貝ibd（存放資料庫的資料，索引）檔案，ibd檔案拷貝完成後，線程立即退出，并通知主程序ibd拷貝完畢
4）主程序對非Innodb表加全局讀鎖
5）主程序拷貝非innodb表的表空間檔案，資料檔案，索引檔案，以及mysql的配置檔案
6）拷貝完非innodb表的檔案後，主程序将給redo線程發送停止拷貝redo的消息
7）redo線程收到主程序的停止信号後，立刻停止，并給主程序發送redo拷貝結束的消息，然後線程退出
8）主程序收到redo拷貝完成對消息後，立刻對非innodb表解鎖，并等待子程序xtrabackup的結束
9）子程序結束退出後，并告知主程序。主程序退出，備份結束。
二、xtrabackup安裝
1）添加yum源
[root@test-centos7-node1 ~]# yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
說明：以上指令會在/etc/yum.repo.d/下面生成percona-release.repo 檔案，此檔案就是xtrabackup的yum倉庫位址檔案
2）檢視xtrabackup包資訊
[root@test-centos7-node1 ~]# yum info percona-xtrabackup
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Available Packages
Name : percona-xtrabackup
Arch : x86_64
Version : 2.3.10
Release : 1.el7
Size : 5.0 M
Repo : percona-release-x86_64/7/x86_64
Summary : XtraBackup online backup for MySQL / InnoDB
URL : http://www.percona.com/software/percona-xtrabackup
License : GPLv2
Description : Percona XtraBackup is OpenSource online (non-blockable) backup solution for InnoDB and XtraDB engines
[root@test-centos7-node1 ~]# 說明：預設情況我們添加了xtrabackup的yum源後，預設是安裝的2.3版本，需要安裝2.2或者2.4可在後面添加版本号，如下
[root@test-centos7-node1 ~]# yum info percona-xtrabackup-22
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Installed Packages
Name : percona-xtrabackup-22
Arch : x86_64
Version : 2.2.13
Release : 1.el7
Size : 18 M
Repo : installed
From repo : percona-release-x86_64
Summary : XtraBackup online backup for MySQL / InnoDB
URL : http://www.percona.com/software/percona-xtrabackup
License : GPLv2
Description : Percona XtraBackup is OpenSource online (non-blockable) backup solution for InnoDB and XtraDB engines
[root@test-centos7-node1 ~]# yum info percona-xtrabackup-24
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Available Packages
Name : percona-xtrabackup-24
Arch : x86_64
Version : 2.4.18
Release : 1.el7
Size : 7.6 M
Repo : percona-release-x86_64/7/x86_64
Summary : XtraBackup online backup for MySQL / InnoDB
URL : http://www.percona.com/software/percona-xtrabackup
License : GPLv2
Description : Percona XtraBackup is OpenSource online (non-blockable) backup solution for InnoDB and XtraDB engines
[root@test-centos7-node1 ~]# 3）安裝xtrabackup特定的版本
[root@test-centos7-node1 ~]# yum install -y percona-xtrabackup-24
4）檢視percona-xtrabackup-24包安裝路徑
[root@test-centos7-node1 ~]# rpm -ql percona-xtrabackup-24
/usr/bin/innobackupex
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/lib64/xtrabackup/plugin/keyring_file.so
/usr/lib64/xtrabackup/plugin/keyring_vault.so
/usr/share/doc/percona-xtrabackup-24-2.4.18
/usr/share/doc/percona-xtrabackup-24-2.4.18/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
[root@test-centos7-node1 ~]# file /usr/bin/innobackupex
/usr/bin/innobackupex: symbolic link to `xtrabackup'
[root@test-centos7-node1 ~]# ll /usr/bin/innobackupex
lrwxrwxrwx. 1 root root 10 Jan 17 23:46 /usr/bin/innobackupex -> xtrabackup
[root@test-centos7-node1 ~]# file /usr/bin/xtrabackup
/usr/bin/xtrabackup: ELF 64-bit LSB executable, x86-64, version 1 (GNU/Linux), dynamically linked (uses shared libs), for GNU/Linux 2.6.32, BuildID[sha1]=259960b7e21a0a6a6aab5883cc81be34db03f42c, stripped
[root@test-centos7-node1 ~]# 說明：2.2版本以前/usr/bin/innobackupex 檔案都是一個perl腳本，2.2以後/usr/bin/innobackupex 是一個軟連接配接指向/usr/bin/xtrabackup
[root@test-centos6-node1 ~]# rpm -ql percona-xtrabackup-22
/usr/bin/innobackupex
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/share/doc/percona-xtrabackup-22-2.2.13
/usr/share/doc/percona-xtrabackup-22-2.2.13/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
[root@test-centos6-node1 ~]# file /usr/bin/innobackupex
/usr/bin/innobackupex: a /usr/bin/env perl script text executable
[root@test-centos6-node1 ~]# file /usr/bin/xtrabackup
/usr/bin/xtrabackup: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, stripped
[root@test-centos6-node1 ~]# 二、xtrabackup用法
1）備份
--user：指定備份連接配接資料庫的賬号
--password：指定備份連接配接資料庫賬号的密碼
--host：指定備份連接配接資料庫位址
--databases：該選項接受的參數為資料庫名，如果要指定多個資料庫，彼此間需要以空格隔開；如："xtra_test dba_test"，同時，在指定某資料庫時，也可以隻指定其中的某張表。如："mydatabase.mytable"。該選項對innodb引擎表無效，還是會備份所有innodb表
--defaults-file：該選項指定從哪個檔案讀取MySQL配置，必須放在指令行第一個選項位置
--incremental：該選項表示建立一個增量備份，需要指定--incremental-basedir
--incremental-basedir：該選項指定為前一次全備份或增量備份的目錄，與--incremental同時使用
--incremental-dir：該選項表示還原時增量備份的目錄
--include=name：指定表名，格式：databasename.tablename
2）預處理整理
--apply-log：一般情況下,在備份完成後，資料尚且不能用于恢複操作，因為備份的資料中可能會包含尚未送出的事務或已經送出但尚未同步至資料檔案中的事務。是以，此時資料檔案仍處理不一緻狀态。此選項作用是通過復原未送出的事務及同步已經送出的事務至資料檔案使資料檔案處于一緻性狀态
--use-memory：和--apply-log選項一起使用，當prepare 備份時，做crash recovery配置設定的記憶體大小，機關位元組，也可1MB,1M,1G,1GB等，推薦1G
--export：表示開啟可導出單獨的表之後再導入其他Mysql中
--redo-only：此選項在prepare base full backup，往其中合并增量備份時候使用，但不包括對最後一個增量備份的合并
3）還原
--copy-back：做資料恢複時将備份資料檔案拷貝到MySQL伺服器的datadir
--move-back：這個選項與--copy-back相似，唯一的差別是它不拷貝檔案，而是移動檔案到目的地。這個選項移除backup檔案，用時候必須小心。使用場景：沒有足夠的磁盤空間同僚保留資料檔案和Backup副本
三、xtrabackup全備實作
1）不使用任何壓縮工具複制全備
[root@test-centos7-node1 ~]# xtrabackup --backup --target-dir=/root/backup/
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments: --backup=1 --target-dir=/root/backup/
200118 00:08:03 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: NO).
200118 00:08:03 version_check Connected to MySQL server
200118 00:08:03 version_check Executing a version check against the server...
200118 00:08:03 version_check Done.
200118 00:08:03 Connecting to MySQL server host: localhost, user: not set, password: not set, port: not set, socket: not set
Using server version 5.5.56-MariaDB
xtrabackup version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: Number of pools: 1
200118 00:08:03 >> log scanned up to (1597945)
xtrabackup: Generating a list of tablespaces
200118 00:08:04 [01] Copying ./ibdata1 to /root/backup/ibdata1
200118 00:08:04 [01] ...done
200118 00:08:04 >> log scanned up to (1597945)
200118 00:08:05 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
200118 00:08:05 Executing FLUSH TABLES WITH READ LOCK...
200118 00:08:05 Starting to backup non-InnoDB tables and files
200118 00:08:05 [01] Copying ./mysql/db.frm to /root/backup/mysql/db.frm
200118 00:08:05 [01] ...done
……省略部分内容
200118 00:08:05 [01] ...done
200118 00:08:05 Finished backing up non-InnoDB tables and files
200118 00:08:05 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1597945'
xtrabackup: Stopping log copying thread.
.200118 00:08:05 >> log scanned up to (1597945)
200118 00:08:05 Executing UNLOCK TABLES
200118 00:08:05 All tables unlocked
200118 00:08:05 Backup created in directory '/root/backup/'
200118 00:08:05 [00] Writing /root/backup/backup-my.cnf
200118 00:08:05 [00] ...done
200118 00:08:05 [00] Writing /root/backup/xtrabackup_info
200118 00:08:05 [00] ...done
xtrabackup: Transaction log of lsn (1597945) to (1597945) was copied.
200118 00:08:05 completed OK!
[root@test-centos7-node1 ~]# ll backup/
total 18456
-rw-r-----. 1 root root 431 Jan 18 00:08 backup-my.cnf
-rw-r-----. 1 root root 18874368 Jan 18 00:08 ibdata1
drwxr-x---. 2 root root 4096 Jan 18 00:08 mysql
drwxr-x---. 2 root root 4096 Jan 18 00:08 performance_schema
drwxr-x---. 2 root root 20 Jan 18 00:08 test
-rw-r-----. 1 root root 135 Jan 18 00:08 xtrabackup_checkpoints
-rw-r-----. 1 root root 425 Jan 18 00:08 xtrabackup_info
-rw-r-----. 1 root root 2560 Jan 18 00:08 xtrabackup_logfile
[root@test-centos7-node1 ~]#
說明：生産環境中需要指定使用者名和密碼來備份，這裡需要注意一點xtrabackup備份是基于複制資料檔案的方式來做的備份，它和mysqldump不一樣的是，它不能連接配接到遠端伺服器上把備份檔案拉取到本地，但是它可在遠端伺服器上把備份推到本地來。換句話說xtrabackup 隻能備份本地資料庫的檔案，執行指令需要在本地執行。
2）基于流式傳輸和壓縮備份（--stream=xbstream）
[root@test-centos7-node1 ~]# xtrabackup --stream=xbstream --backup > /root/backup2/all_backup.xbstream
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments: --stream=xbstream --backup=1
200118 00:36:52 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: NO).
200118 00:36:52 version_check Connected to MySQL server
200118 00:36:52 version_check Executing a version check against the server...
200118 00:36:52 version_check Done.
200118 00:36:52 Connecting to MySQL server host: localhost, user: not set, password: not set, port: not set, socket: not set
Using server version 5.5.56-MariaDB
xtrabackup version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: Number of pools: 1
200118 00:36:52 >> log scanned up to (1597945)
xtrabackup: Generating a list of tablespaces
200118 00:36:53 [01] Streaming ./ibdata1
200118 00:36:53 [01] ...done
200118 00:36:53 >> log scanned up to (1597945)
200118 00:36:54 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
200118 00:36:54 Executing FLUSH TABLES WITH READ LOCK...
200118 00:36:54 Starting to backup non-InnoDB tables and files
200118 00:36:54 [01] Streaming ./mysql/db.frm to <STDOUT>
200118 00:36:54 [01] ...done
……省略部分内容
200118 00:36:54 [01] Streaming ./performance_schema/threads.frm to <STDOUT>
200118 00:36:54 [01] ...done
200118 00:36:54 Finished backing up non-InnoDB tables and files
200118 00:36:54 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1597945'
xtrabackup: Stopping log copying thread.
.200118 00:36:54 >> log scanned up to (1597945)
200118 00:36:54 Executing UNLOCK TABLES
200118 00:36:54 All tables unlocked
200118 00:36:54 Backup created in directory '/root/xtrabackup_backupfiles/'
200118 00:36:54 [00] Streaming <STDOUT>
200118 00:36:54 [00] ...done
200118 00:36:54 [00] Streaming <STDOUT>
200118 00:36:54 [00] ...done
xtrabackup: Transaction log of lsn (1597945) to (1597945) was copied.
200118 00:36:54 completed OK!
[root@test-centos7-node1 ~]# ll /root/backup2/
total 32768
-rw-r--r--. 1 root root 19908780 Jan 18 00:36 all_backup.xbstream
[root@test-centos7-node1 ~]#
說明：以上方式是通過流傳輸模式以xbstream的二進制格式将備份輸出到标準輸出，然後通過輸出重定向到一個以.xbstream結尾的檔案。這樣備份有一個好處就是它可以自動加密備份資料，使得我們備份的資料相比複制檔案的方式要安全。要使用流式傳輸功能，必須使用
--stream
，提供流（
tar
或
xbstream
）的格式以及臨時檔案的存儲位置
3）使用流備份并壓縮
[root@test-centos7-node1 ~]# xtrabackup --stream=xbstream --backup --compress > /root/backup2/all_backup2.xbstream
說明：使用--compress選項就表示壓縮資料，以上指令備份的檔案要比不加--compress備份的檔案要小得多
[root@test-centos7-node1 ~]# ll backup2/
total 19984
-rw-r--r--. 1 root root 550056 Jan 18 00:42 all_backup2.xbstream
-rw-r--r--. 1 root root 19908780 Jan 18 00:36 all_backup.xbstream
[root@test-centos7-node1 ~]#
4）将流備份解壓到指定目錄
[root@test-centos7-node1 ~]# xbstream -x < backup2/all_backup.xbstream -C xxxx/
[root@test-centos7-node1 ~]# ll xxxx/
total 18456
-rw-r-----. 1 root root 431 Jan 18 01:00 backup-my.cnf
-rw-r-----. 1 root root 18874368 Jan 18 01:00 ibdata1
drwxr-x---. 2 root root 4096 Jan 18 01:00 mysql
drwxr-x---. 2 root root 4096 Jan 18 01:00 performance_schema
drwxr-x---. 2 root root 20 Jan 18 01:00 test
-rw-r-----. 1 root root 135 Jan 18 01:00 xtrabackup_checkpoints
-rw-r-----. 1 root root 420 Jan 18 01:00 xtrabackup_info
-rw-r-----. 1 root root 2560 Jan 18 01:00 xtrabackup_logfile
[root@test-centos7-node1 ~]
說明：如果備份是通過--compress 備份，用以上指令解開後的檔案是以.qp結尾的檔案，這個檔案是沒法直接使用需要用對應的工具将其再解壓。
[root@test-centos7-node1 ~]# xbstream -x < /root/backup2/all_backup2.xbstream -C /root/xtrabackup_backupfiles/
[root@test-centos7-node1 ~]# ll /root/xtrabackup_backupfiles/
total 292
-rw-r-----. 1 root root 407 Jan 18 00:56 backup-my.cnf.qp
-rw-r-----. 1 root root 270377 Jan 18 00:56 ibdata1.qp
drwxr-x---. 2 root root 4096 Jan 18 00:56 mysql
drwxr-x---. 2 root root 4096 Jan 18 00:56 performance_schema
drwxr-x---. 2 root root 23 Jan 18 00:56 test
-rw-r-----. 1 root root 135 Jan 18 00:56 xtrabackup_checkpoints
-rw-r-----. 1 root root 423 Jan 18 00:56 xtrabackup_info.qp
-rw-r-----. 1 root root 498 Jan 18 00:56 xtrabackup_logfile.qp
[root@test-centos7-node1 ~]# file /root/xtrabackup_backupfiles/backup-my.cnf.qp
/root/xtrabackup_backupfiles/backup-my.cnf.qp: data
[root@test-centos7-node1 ~]#
說明：以上就是通過壓縮後在通過流傳輸将标準輸出到某一個檔案後，解壓該檔案得到的檔案，.qp的檔案如果用cat 檢視是亂碼的，這個時候就需要工具來将其解壓後就可正常檢視。qpress工具就可将其檔案解開。下載下傳連結http://www.quicklz.com/qpress-11-linux-x64.tar。
[root@test-centos7-node1 ~]# ./qpress -d xtrabackup_backupfiles/backup-my.cnf.qp /root/
[root@test-centos7-node1 ~]# ll
total 160
drwxr-xr-x. 5 root root 172 Jan 18 00:08 backup
drwxr-xr-x. 2 root root 61 Jan 18 00:42 backup2
-rw-r--r--. 1 root root 431 Jan 18 01:12 backup-my.cnf
-rwxrwxrwx. 1 root root 75684 Sep 23 2010 qpress
-rw-r--r--. 1 root root 81920 Jan 18 01:09 qpress-11-linux-x64.tar
drwxr-x---. 5 root root 184 Jan 18 00:56 xtrabackup_backupfiles
drwxr-xr-x. 5 root root 172 Jan 18 01:00 xxxx
[root@test-centos7-node1 ~]# cat backup-my.cnf
# This MySQL options file was generated by innobackupex.
# The MySQL server
[mysqld]
innodb_checksum_algorithm=innodb
innodb_log_checksum_algorithm=innodb
innodb_data_file_path=ibdata1:10M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=5242880
innodb_fast_checksum=false
innodb_page_size=16384
innodb_log_block_size=512
innodb_undo_directory=.
innodb_undo_tablespaces=0
server_id=0
redo_log_version=0
master_key_id=0
[root@test-centos7-node1 ~]#
說明：下載下傳下來的tar包解開後就是一個二進制檔案，我們可以将這個二進制檔案直接拷貝到/usr/bin 這樣就不用./去運作。解壓用-d 後面跟要解壓到檔案 和解壓後的檔案存放目錄，這裡需要注意一點，它和gzip不一樣，gzip解壓後原檔案就消失了，而它不會。
5）将壓縮備份發送到另一台主機并解壓縮它
[root@test-centos7-node1 ~]# xtrabackup --stream=xbstream --backup | ssh [email protected] "xbstream -x -C /root/"
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments: --stream=xbstream --backup=1
200118 01:24:07 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: NO).
200118 01:24:07 version_check Connected to MySQL server
200118 01:24:07 version_check Executing a version check against the server...
200118 01:24:07 version_check Done.
200118 01:24:07 Connecting to MySQL server host: localhost, user: not set, password: not set, port: not set, socket: not set
Using server version 5.5.56-MariaDB
xtrabackup version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: Number of pools: 1
200118 01:24:07 >> log scanned up to (1597945)
xtrabackup: Generating a list of tablespaces
200118 01:24:07 [01] Streaming ./ibdata1
200118 01:24:07 [01] ...done
200118 01:24:08 >> log scanned up to (1597945)
200118 01:24:08 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
200118 01:24:08 Executing FLUSH TABLES WITH READ LOCK...
200118 01:24:08 Starting to backup non-InnoDB tables and files
200118 01:24:08 [01] Streaming ./mysql/db.frm to <STDOUT>
200118 01:24:08 [01] ...done
……省略部分内容
200118 01:24:08 [01] Streaming ./performance_schema/threads.frm to <STDOUT>
200118 01:24:08 [01] ...done
200118 01:24:08 Finished backing up non-InnoDB tables and files
200118 01:24:08 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1597945'
xtrabackup: Stopping log copying thread.
.200118 01:24:08 >> log scanned up to (1597945)
200118 01:24:09 Executing UNLOCK TABLES
200118 01:24:09 All tables unlocked
200118 01:24:09 Backup created in directory '/root/xtrabackup_backupfiles/'
200118 01:24:09 [00] Streaming <STDOUT>
200118 01:24:09 [00] ...done
200118 01:24:09 [00] Streaming <STDOUT>
200118 01:24:09 [00] ...done
xtrabackup: Transaction log of lsn (1597945) to (1597945) was copied.
200118 01:24:09 completed OK!
[root@test-centos7-node1 ~]# ssh 192.168.0.11
Last login: Sat Jan 18 22:21:05 2020 from 192.168.0.10
[root@test-centos6-node1 ~]# ll
total 18460
-rw-r-----. 1 root root 431 Jan 18 22:23 backup-my.cnf
-rw-r-----. 1 root root 18874368 Jan 18 22:23 ibdata1
drwx------. 2 root root 4096 Jan 18 22:23 mysql
drwx------. 2 root root 4096 Jan 18 22:23 performance_schema
drwx------. 2 root root 4096 Jan 18 22:23 test
-rw-r-----. 1 root root 135 Jan 18 22:23 xtrabackup_checkpoints
-rw-r-----. 1 root root 420 Jan 18 22:23 xtrabackup_info
-rw-r-----. 1 root root 2560 Jan 18 22:23 xtrabackup_logfile
[root@test-centos6-node1 ~]#
說明：以上指令很容易了解通過标準輸出内容交給管道，通過ssh 遠端執行指令解壓資料。做以上操作前提建議先做好ssh認證免密；有關ssh 基于key驗證免密請參考https://www.cnblogs.com/qiuhom-1874/p/11783371.html
6）基于流式傳輸和壓縮備份（--stream=tar）
将完整備份直接存儲到tar歸檔檔案中
[root@test-centos7-node1 ~]# xtrabackup --stream=tar --backup > /root/all.tar
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments: --stream=tar --backup=1
200118 01:36:41 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: NO).
200118 01:36:41 version_check Connected to MySQL server
200118 01:36:41 version_check Executing a version check against the server...
200118 01:36:41 version_check Done.
200118 01:36:41 Connecting to MySQL server host: localhost, user: not set, password: not set, port: not set, socket: not set
Using server version 5.5.56-MariaDB
xtrabackup version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: Number of pools: 1
200118 01:36:41 >> log scanned up to (1597945)
xtrabackup: Generating a list of tablespaces
200118 01:36:41 [01] Streaming ./ibdata1
200118 01:36:41 [01] ...done
200118 01:36:42 >> log scanned up to (1597945)
200118 01:36:42 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
200118 01:36:42 Executing FLUSH TABLES WITH READ LOCK...
200118 01:36:42 Starting to backup non-InnoDB tables and files
200118 01:36:42 [01] Streaming ./mysql/db.frm to <STDOUT>
200118 01:36:42 [01] ...done
……省略部分内容
200118 01:36:42 [01] Streaming ./performance_schema/threads.frm to <STDOUT>
200118 01:36:42 [01] ...done
200118 01:36:42 Finished backing up non-InnoDB tables and files
200118 01:36:42 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1597945'
xtrabackup: Stopping log copying thread.
.200118 01:36:42 >> log scanned up to (1597945)
200118 01:36:42 Executing UNLOCK TABLES
200118 01:36:42 All tables unlocked
200118 01:36:42 Backup created in directory '/root/xtrabackup_backupfiles/'
200118 01:36:42 [00] Streaming <STDOUT>
200118 01:36:42 [00] ...done
200118 01:36:42 [00] Streaming <STDOUT>
200118 01:36:42 [00] ...done
xtrabackup: Transaction log of lsn (1597945) to (1597945) was copied.
200118 01:36:43 completed OK!
[root@test-centos7-node1 ~]# ll /root/
total 32928
-rw-r--r--. 1 root root 19964416 Jan 18 01:36 all.tar
drwxr-xr-x. 5 root root 172 Jan 18 00:08 backup
drwxr-xr-x. 2 root root 61 Jan 18 00:42 backup2
-rw-r--r--. 1 root root 431 Jan 18 01:12 backup-my.cnf
-rwxrwxrwx. 1 root root 75684 Sep 23 2010 qpress
-rw-r--r--. 1 root root 81920 Jan 18 01:09 qpress-11-linux-x64.tar
drwxr-x---. 5 root root 184 Jan 18 00:56 xtrabackup_backupfiles
drwxr-xr-x. 5 root root 172 Jan 18 01:00 xxxx
[root@test-centos7-node1 ~]#
要将tar存檔發送到另一個主機
[root@test-centos7-node1 ~]# ssh 192.168.0.11
Last login: Sat Jan 18 22:45:34 2020 from 192.168.0.10
[root@test-centos6-node1 ~]# ls
[root@test-centos6-node1 ~]# exit
logout
Connection to 192.168.0.11 closed.
[root@test-centos7-node1 ~]# xtrabackup --stream=tar --backup | ssh [email protected] "cat - > /root/all.tar"
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments: --stream=tar --backup=1
200118 01:47:47 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: NO).
200118 01:47:47 version_check Connected to MySQL server
200118 01:47:47 version_check Executing a version check against the server...
200118 01:47:47 version_check Done.
200118 01:47:47 Connecting to MySQL server host: localhost, user: not set, password: not set, port: not set, socket: not set
Using server version 5.5.56-MariaDB
xtrabackup version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: Number of pools: 1
200118 01:47:47 >> log scanned up to (1597945)
xtrabackup: Generating a list of tablespaces
200118 01:47:47 [01] Streaming ./ibdata1
200118 01:47:47 [01] ...done
200118 01:47:48 >> log scanned up to (1597945)
200118 01:47:48 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
200118 01:47:48 Executing FLUSH TABLES WITH READ LOCK...
200118 01:47:48 Starting to backup non-InnoDB tables and files
200118 01:47:48 [01] Streaming ./mysql/db.frm to <STDOUT>
200118 01:47:48 [01] ...done
……省略部分内容
200118 01:47:48 [01] Streaming ./performance_schema/threads.frm to <STDOUT>
200118 01:47:48 [01] ...done
200118 01:47:48 Finished backing up non-InnoDB tables and files
200118 01:47:48 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1597945'
xtrabackup: Stopping log copying thread.
.200118 01:47:48 >> log scanned up to (1597945)
200118 01:47:48 Executing UNLOCK TABLES
200118 01:47:48 All tables unlocked
200118 01:47:48 Backup created in directory '/root/xtrabackup_backupfiles/'
200118 01:47:48 [00] Streaming <STDOUT>
200118 01:47:48 [00] ...done
200118 01:47:48 [00] Streaming <STDOUT>
200118 01:47:48 [00] ...done
xtrabackup: Transaction log of lsn (1597945) to (1597945) was copied.
200118 01:47:48 completed OK!
[root@test-centos7-node1 ~]# ssh 192.168.0.11
Last login: Sat Jan 18 22:47:19 2020 from 192.168.0.10
[root@test-centos6-node1 ~]# ls
all.tar
[root@test-centos6-node1 ~]# mkdir test
[root@test-centos6-node1 ~]# tar xf all.tar -C test/
[root@test-centos6-node1 ~]# ll test/
total 18460
-rw-rw----. 1 root root 431 Jan 18 14:47 backup-my.cnf
-rw-rw----. 1 root root 18874368 Jan 18 13:06 ibdata1
drwxr-xr-x. 2 root root 4096 Jan 18 22:47 mysql
drwxr-xr-x. 2 root root 4096 Jan 18 22:47 performance_schema
drwxr-xr-x. 2 root root 4096 Jan 18 22:47 test
-rw-rw----. 1 root root 135 Jan 18 14:47 xtrabackup_checkpoints
-rw-rw----. 1 root root 410 Jan 18 14:47 xtrabackup_info
-rw-rw----. 1 root root 2560 Jan 18 14:47 xtrabackup_logfile
[root@test-centos6-node1 ~]#
使用其他壓縮工具壓縮
gzip壓縮
[root@test-centos7-node1 ~]# xtrabackup --stream=tar --backup | gzip - >/root/all.tar.gz
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments: --stream=tar --backup=1
200118 01:51:13 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: NO).
200118 01:51:13 version_check Connected to MySQL server
200118 01:51:13 version_check Executing a version check against the server...
200118 01:51:13 version_check Done.
200118 01:51:13 Connecting to MySQL server host: localhost, user: not set, password: not set, port: not set, socket: not set
Using server version 5.5.56-MariaDB
xtrabackup version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: Number of pools: 1
200118 01:51:13 >> log scanned up to (1597945)
xtrabackup: Generating a list of tablespaces
200118 01:51:13 [01] Streaming ./ibdata1
200118 01:51:14 [01] ...done
200118 01:51:14 >> log scanned up to (1597945)
200118 01:51:14 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
200118 01:51:14 Executing FLUSH TABLES WITH READ LOCK...
200118 01:51:14 Starting to backup non-InnoDB tables and files
200118 01:51:14 [01] Streaming ./mysql/db.frm to <STDOUT>
200118 01:51:14 [01] ...done
……省略部分内容
200118 01:51:15 [01] Streaming ./performance_schema/threads.frm to <STDOUT>
200118 01:51:15 [01] ...done
200118 01:51:15 Finished backing up non-InnoDB tables and files
200118 01:51:15 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1597945'
xtrabackup: Stopping log copying thread.
.200118 01:51:15 >> log scanned up to (1597945)
200118 01:51:15 Executing UNLOCK TABLES
200118 01:51:15 All tables unlocked
200118 01:51:15 Backup created in directory '/root/xtrabackup_backupfiles/'
200118 01:51:15 [00] Streaming <STDOUT>
200118 01:51:15 [00] ...done
200118 01:51:15 [00] Streaming <STDOUT>
200118 01:51:15 [00] ...done
xtrabackup: Transaction log of lsn (1597945) to (1597945) was copied.
200118 01:51:15 completed OK!
[root@test-centos7-node1 ~]# ls
all.tar.gz xtrabackup_backupfiles
[root@test-centos7-node1 ~]# ll xtrabackup_backupfiles/
total 0
[root@test-centos7-node1 ~]# tar xf all.tar.gz -C xtrabackup_backupfiles/
[root@test-centos7-node1 ~]# ll xtrabackup_backupfiles/
total 18456
-rw-rw----. 1 root root 431 Jan 18 01:51 backup-my.cnf
-rw-rw----. 1 root root 18874368 Jan 18 00:06 ibdata1
drwxr-xr-x. 2 root root 4096 Jan 18 01:51 mysql
drwxr-xr-x. 2 root root 4096 Jan 18 01:51 performance_schema
drwxr-xr-x. 2 root root 20 Jan 18 01:51 test
-rw-rw----. 1 root root 135 Jan 18 01:51 xtrabackup_checkpoints
-rw-rw----. 1 root root 410 Jan 18 01:51 xtrabackup_info
-rw-rw----. 1 root root 2560 Jan 18 01:51 xtrabackup_logfile
[root@test-centos7-node1 ~]# ll
total 212
-rw-r--r--. 1 root root 213460 Jan 18 01:51 all.tar.gz
drwxr-x---. 5 root root 172 Jan 18 01:51 xtrabackup_backupfiles
[root@test-centos7-node1 ~]#
bzip2壓縮
[root@test-centos7-node1 ~]# rm -rf *
[root@test-centos7-node1 ~]# ls
[root@test-centos7-node1 ~]# xtrabackup --backup --stream=tar |bzip2 - > /root/all.tar.bz2
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments: --backup=1 --stream=tar
200118 01:57:02 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: NO).
200118 01:57:02 version_check Connected to MySQL server
200118 01:57:02 version_check Executing a version check against the server...
200118 01:57:02 version_check Done.
200118 01:57:02 Connecting to MySQL server host: localhost, user: not set, password: not set, port: not set, socket: not set
Using server version 5.5.56-MariaDB
xtrabackup version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: Number of pools: 1
200118 01:57:02 >> log scanned up to (1597945)
xtrabackup: Generating a list of tablespaces
200118 01:57:03 [01] Streaming ./ibdata1
200118 01:57:03 [01] ...done
200118 01:57:03 >> log scanned up to (1597945)
200118 01:57:04 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
200118 01:57:04 Executing FLUSH TABLES WITH READ LOCK...
200118 01:57:04 Starting to backup non-InnoDB tables and files
200118 01:57:04 [01] Streaming ./mysql/db.frm to <STDOUT>
200118 01:57:04 [01] ...done
……省略部分内容
200118 01:57:04 [01] Streaming ./performance_schema/threads.frm to <STDOUT>
200118 01:57:04 [01] ...done
200118 01:57:04 Finished backing up non-InnoDB tables and files
200118 01:57:04 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1597945'
xtrabackup: Stopping log copying thread.
.200118 01:57:04 >> log scanned up to (1597945)
200118 01:57:04 Executing UNLOCK TABLES
200118 01:57:04 All tables unlocked
200118 01:57:04 Backup created in directory '/root/xtrabackup_backupfiles/'
200118 01:57:04 [00] Streaming <STDOUT>
200118 01:57:04 [00] ...done
200118 01:57:04 [00] Streaming <STDOUT>
200118 01:57:04 [00] ...done
xtrabackup: Transaction log of lsn (1597945) to (1597945) was copied.
200118 01:57:04 completed OK!
[root@test-centos7-node1 ~]# ls
all.tar.bz2 xtrabackup_backupfiles
[root@test-centos7-node1 ~]# ll xtrabackup_backupfiles/
total 0
[root@test-centos7-node1 ~]# tar xf all.tar.bz2 -C xtrabackup_backupfiles/
[root@test-centos7-node1 ~]# ll xtrabackup_backupfiles/
total 18456
-rw-rw----. 1 root root 431 Jan 18 01:57 backup-my.cnf
-rw-rw----. 1 root root 18874368 Jan 18 00:06 ibdata1
drwxr-xr-x. 2 root root 4096 Jan 18 01:57 mysql
drwxr-xr-x. 2 root root 4096 Jan 18 01:57 performance_schema
drwxr-xr-x. 2 root root 20 Jan 18 01:57 test
-rw-rw----. 1 root root 135 Jan 18 01:57 xtrabackup_checkpoints
-rw-rw----. 1 root root 410 Jan 18 01:57 xtrabackup_info
-rw-rw----. 1 root root 2560 Jan 18 01:57 xtrabackup_logfile
[root@test-centos7-node1 ~]#
xz壓縮
[root@test-centos7-node1 ~]# rm -rf *
[root@test-centos7-node1 ~]# ls
[root@test-centos7-node1 ~]# xtrabackup --backup --stream=tar |xz - > /root/all.tar.xz
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments: --backup=1 --stream=tar
200118 01:58:45 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: NO).
200118 01:58:45 version_check Connected to MySQL server
200118 01:58:45 version_check Executing a version check against the server...
200118 01:58:45 version_check Done.
200118 01:58:45 Connecting to MySQL server host: localhost, user: not set, password: not set, port: not set, socket: not set
Using server version 5.5.56-MariaDB
xtrabackup version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: Number of pools: 1
200118 01:58:45 >> log scanned up to (1597945)
xtrabackup: Generating a list of tablespaces
200118 01:58:45 [01] Streaming ./ibdata1
200118 01:58:46 [01] ...done
200118 01:58:46 >> log scanned up to (1597945)
200118 01:58:46 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
200118 01:58:46 Executing FLUSH TABLES WITH READ LOCK...
200118 01:58:46 Starting to backup non-InnoDB tables and files
200118 01:58:46 [01] Streaming ./mysql/db.frm to <STDOUT>
200118 01:58:46 [01] ...done
……省略部分内容
200118 01:58:47 [01] Streaming ./performance_schema/threads.frm to <STDOUT>
200118 01:58:47 [01] ...done
200118 01:58:47 Finished backing up non-InnoDB tables and files
200118 01:58:47 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1597945'
xtrabackup: Stopping log copying thread.
.200118 01:58:47 >> log scanned up to (1597945)
200118 01:58:47 Executing UNLOCK TABLES
200118 01:58:47 All tables unlocked
200118 01:58:47 Backup created in directory '/root/xtrabackup_backupfiles/'
200118 01:58:47 [00] Streaming <STDOUT>
200118 01:58:47 [00] ...done
200118 01:58:47 [00] Streaming <STDOUT>
200118 01:58:47 [00] ...done
xtrabackup: Transaction log of lsn (1597945) to (1597945) was copied.
200118 01:58:47 completed OK!
[root@test-centos7-node1 ~]# ll
total 136
-rw-r--r--. 1 root root 136148 Jan 18 01:58 all.tar.xz
drwxr-x---. 2 root root 6 Jan 18 01:58 xtrabackup_backupfiles
[root@test-centos7-node1 ~]# ll xtrabackup_backupfiles/
total 0
[root@test-centos7-node1 ~]# tar xf all.tar.xz -C xtrabackup_backupfiles/
[root@test-centos7-node1 ~]# ll xtrabackup_backupfiles/
total 18456
-rw-rw----. 1 root root 431 Jan 18 01:58 backup-my.cnf
-rw-rw----. 1 root root 18874368 Jan 18 00:06 ibdata1
drwxr-xr-x. 2 root root 4096 Jan 18 01:59 mysql
drwxr-xr-x. 2 root root 4096 Jan 18 01:59 performance_schema
drwxr-xr-x. 2 root root 20 Jan 18 01:59 test
-rw-rw----. 1 root root 135 Jan 18 01:58 xtrabackup_checkpoints
-rw-rw----. 1 root root 410 Jan 18 01:58 xtrabackup_info
-rw-rw----. 1 root root 2560 Jan 18 01:58 xtrabackup_logfile
[root@test-centos7-node1 ~]#
四、xtrabackup全備還原實作
1）删庫前資料庫裡的庫表
[root@test-centos7-node1 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> use hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
MariaDB [hellodb]> \q
Bye
[root@test-centos7-node1 ~]#
2）全量備份
[root@test-centos7-node1 ~]# xtrabackup --backup --target-dir=/root/backup
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments: --backup=1 --target-dir=/root/backup
200118 02:29:28 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: NO).
200118 02:29:28 version_check Connected to MySQL server
200118 02:29:28 version_check Executing a version check against the server...
200118 02:29:28 version_check Done.
200118 02:29:28 Connecting to MySQL server host: localhost, user: not set, password: not set, port: not set, socket: not set
Using server version 5.5.56-MariaDB
xtrabackup version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: Number of pools: 1
200118 02:29:28 >> log scanned up to (1628321)
xtrabackup: Generating a list of tablespaces
200118 02:29:28 [01] Copying ./ibdata1 to /root/backup/ibdata1
200118 02:29:28 [01] ...done
200118 02:29:29 >> log scanned up to (1628321)
200118 02:29:29 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
200118 02:29:29 Executing FLUSH TABLES WITH READ LOCK...
200118 02:29:29 Starting to backup non-InnoDB tables and files
200118 02:29:29 [01] Copying ./mysql/db.frm to /root/backup/mysql/db.frm
200118 02:29:29 [01] ...done
……省略部分内容
200118 02:29:30 [01] Copying ./hellodb/toc.frm to /root/backup/hellodb/toc.frm
200118 02:29:30 [01] ...done
200118 02:29:30 Finished backing up non-InnoDB tables and files
200118 02:29:30 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1628321'
xtrabackup: Stopping log copying thread.
.200118 02:29:30 >> log scanned up to (1628321)
200118 02:29:30 Executing UNLOCK TABLES
200118 02:29:30 All tables unlocked
200118 02:29:30 Backup created in directory '/root/backup/'
200118 02:29:30 [00] Writing /root/backup/backup-my.cnf
200118 02:29:30 [00] ...done
200118 02:29:30 [00] Writing /root/backup/xtrabackup_info
200118 02:29:30 [00] ...done
xtrabackup: Transaction log of lsn (1628321) to (1628321) was copied.
200118 02:29:30 completed OK!
[root@test-centos7-node1 ~]# ll /root/backup/
total 18456
-rw-r-----. 1 root root 431 Jan 18 02:29 backup-my.cnf
drwxr-x---. 2 root root 146 Jan 18 02:29 hellodb
-rw-r-----. 1 root root 18874368 Jan 18 02:29 ibdata1
drwxr-x---. 2 root root 4096 Jan 18 02:29 mysql
drwxr-x---. 2 root root 4096 Jan 18 02:29 performance_schema
drwxr-x---. 2 root root 20 Jan 18 02:29 test
-rw-r-----. 1 root root 135 Jan 18 02:29 xtrabackup_checkpoints
-rw-r-----. 1 root root 424 Jan 18 02:29 xtrabackup_info
-rw-r-----. 1 root root 2560 Jan 18 02:29 xtrabackup_logfile
[root@test-centos7-node1 ~]#
3）删庫
[root@test-centos7-node1 ~]# ll /var/lib/mysql/
total 28700
-rw-rw----. 1 mysql mysql 16384 Jan 18 02:26 aria_log.00000001
-rw-rw----. 1 mysql mysql 52 Jan 18 02:26 aria_log_control
drwx------. 2 mysql mysql 146 Jan 18 02:27 hellodb
-rw-rw----. 1 mysql mysql 18874368 Jan 18 02:27 ibdata1
-rw-rw----. 1 mysql mysql 5242880 Jan 18 02:27 ib_logfile0
-rw-rw----. 1 mysql mysql 5242880 Jan 18 02:26 ib_logfile1
drwx------. 2 mysql mysql 4096 Jan 18 02:26 mysql
srwxrwxrwx. 1 mysql mysql 0 Jan 18 02:26 mysql.sock
drwx------. 2 mysql mysql 4096 Jan 18 02:26 performance_schema
drwx------. 2 mysql mysql 6 Jan 18 02:26 test
[root@test-centos7-node1 ~]# rm -rf /var/lib/mysql/*
[root@test-centos7-node1 ~]# ll /var/lib/mysql/
total 0
[root@test-centos7-node1 ~]#
4）停掉資料庫進行還原
[root@test-centos7-node1 ~]# systemctl stop mariadb
[root@test-centos7-node1 ~]# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 :::22 :::*
LISTEN 0 100 ::1:25 :::*
[root@test-centos7-node1 ~]# xtrabackup --prepare --target-dir=/root/backup/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:10M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=5242880 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=0 --redo-log-version=0
xtrabackup: recognized client arguments: --prepare=1 --target-dir=/root/backup/
xtrabackup version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
xtrabackup: cd to /root/backup/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(1629224)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M: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:10M: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)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.26 started; log sequence number 1629224
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1629243
InnoDB: Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Setting log file ./ib_logfile101 size to 5 MB
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=1629243
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 1629708
InnoDB: Doing recovery: scanned up to log sequence number 1629717 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.26 started; log sequence number 1629717
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1629736
200118 03:09:08 completed OK!
[root@test-centos7-node1 ~]# xtrabackup --copy-back --target-dir=/root/backup/
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments: --copy-back=1 --target-dir=/root/backup/
xtrabackup version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
200118 03:10:13 [01] Copying ib_logfile0 to /var/lib/mysql/ib_logfile0
200118 03:10:13 [01] ...done
……省略部分内容
200118 03:10:13 [01] Copying ./ibtmp1 to /var/lib/mysql/ibtmp1
200118 03:10:14 [01] ...done
200118 03:10:14 completed OK!
[root@test-centos7-node1 ~]# chown -R mysql.mysql /var/lib/mysql/
[root@test-centos7-node1 ~]# ll /var/lib/mysql/
total 40976
drwxr-x---. 2 mysql mysql 146 Jan 18 03:10 hellodb
-rw-r-----. 1 mysql mysql 18874368 Jan 18 03:10 ibdata1
-rw-r-----. 1 mysql mysql 5242880 Jan 18 03:10 ib_logfile0
-rw-r-----. 1 mysql mysql 5242880 Jan 18 03:10 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jan 18 03:10 ibtmp1
drwxr-x---. 2 mysql mysql 4096 Jan 18 03:10 mysql
drwxr-x---. 2 mysql mysql 4096 Jan 18 03:10 performance_schema
drwxr-x---. 2 mysql mysql 20 Jan 18 03:10 test
-rw-r-----. 1 mysql mysql 425 Jan 18 03:10 xtrabackup_info
-rw-r-----. 1 mysql mysql 1 Jan 18 03:10 xtrabackup_master_key_id
[root@test-centos7-node1 ~]#
說明：還原操作需要進行三步，第一步是預準備，這一步的主要作用是確定資料的一緻性，送出完成的事務，復原未完成的事務。第二步是複制預處理後的資料檔案到mariadb的工作目錄，第三部還原拷貝過去的檔案的屬性為mysql。執行拷貝操作需要確定原資料目錄為空，或者加上--force-non-empty-directorires否則copy的時候會報錯。
5）啟動mariadb，檢視資料庫裡的庫表是否恢複到删除前的狀态
[root@test-centos7-node1 ~]# systemctl start mariadb
[root@test-centos7-node1 ~]# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 50 *:3306 *:*
LISTEN 0 128 :::22 :::*
LISTEN 0 100 ::1:25 :::*
[root@test-centos7-node1 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> use hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.01 sec)
MariaDB [hellodb]>
說明：可看到資料庫已經恢複到備份前的狀态
五、xtrabackup 增量備份還原實作
1）完全備份
[root@test-centos7-node1 ~]# ls
[root@test-centos7-node1 ~]# mkdir /root/{full_bak,incre_bak1,incre_bak2}
[root@test-centos7-node1 ~]# ls
full_bak incre_bak1 incre_bak2
[root@test-centos7-node1 ~]# xtrabackup --backup --target-dir=/root/full_bak/
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments: --backup=1 --target-dir=/root/full_bak/
200118 03:28:20 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: NO).
200118 03:28:20 version_check Connected to MySQL server
200118 03:28:20 version_check Executing a version check against the server...
200118 03:28:20 version_check Done.
200118 03:28:20 Connecting to MySQL server host: localhost, user: not set, password: not set, port: not set, socket: not set
Using server version 5.5.56-MariaDB
xtrabackup version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: Number of pools: 1
200118 03:28:20 >> log scanned up to (1629736)
xtrabackup: Generating a list of tablespaces
200118 03:28:20 [01] Copying ./ibdata1 to /root/full_bak/ibdata1
200118 03:28:20 [01] ...done
200118 03:28:21 >> log scanned up to (1629736)
200118 03:28:21 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
200118 03:28:21 Executing FLUSH TABLES WITH READ LOCK...
200118 03:28:21 Starting to backup non-InnoDB tables and files
200118 03:28:21 [01] Copying ./mysql/db.frm to /root/full_bak/mysql/db.frm
200118 03:28:21 [01] ...done
……省略部分内容
200118 03:28:21 [01] Copying ./hellodb/toc.frm to /root/full_bak/hellodb/toc.frm
200118 03:28:21 [01] ...done
200118 03:28:21 Finished backing up non-InnoDB tables and files
200118 03:28:21 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1629736'
xtrabackup: Stopping log copying thread.
.200118 03:28:21 >> log scanned up to (1629736)
200118 03:28:21 Executing UNLOCK TABLES
200118 03:28:21 All tables unlocked
200118 03:28:21 Backup created in directory '/root/full_bak/'
200118 03:28:21 [00] Writing /root/full_bak/backup-my.cnf
200118 03:28:21 [00] ...done
200118 03:28:21 [00] Writing /root/full_bak/xtrabackup_info
200118 03:28:21 [00] ...done
xtrabackup: Transaction log of lsn (1629736) to (1629736) was copied.
200118 03:28:21 completed OK!
[root@test-centos7-node1 ~]#
2）修改資料，在做基于第一次全量備份做第一次增量備份
[root@test-centos7-node1 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> create database abc;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use abc
Database changed
MariaDB [abc]> create table test(id int );
Query OK, 0 rows affected (0.01 sec)
MariaDB [abc]> insert test(id)value(1);
Query OK, 1 row affected (0.00 sec)
MariaDB [abc]> insert test(id)value(2),(3),(4);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [abc]> select * from test ;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
MariaDB [abc]> \q
Bye
[root@test-centos7-node1 ~]#
[root@test-centos7-node1 ~]# xtrabackup --backup --target-dir=/root/incre_bak1/ --incremental-basedir=/root/full_bak/
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments: --backup=1 --target-dir=/root/incre_bak1/ --incremental-basedir=/root/full_bak/
200118 03:49:15 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: NO).
200118 03:49:15 version_check Connected to MySQL server
200118 03:49:15 version_check Executing a version check against the server...
200118 03:49:15 version_check Done.
200118 03:49:15 Connecting to MySQL server host: localhost, user: not set, password: not set, port: not set, socket: not set
Using server version 5.5.56-MariaDB
xtrabackup version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
incremental backup from 1629736 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: Number of pools: 1
200118 03:49:15 >> log scanned up to (1631702)
xtrabackup: Generating a list of tablespaces
xtrabackup: using the full scan for incremental backup
200118 03:49:15 [01] Copying ./ibdata1 to /root/incre_bak1/ibdata1.delta
200118 03:49:15 [01] ...done
200118 03:49:16 >> log scanned up to (1631702)
200118 03:49:16 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
200118 03:49:16 Executing FLUSH TABLES WITH READ LOCK...
200118 03:49:16 Starting to backup non-InnoDB tables and files
200118 03:49:16 [01] Copying ./mysql/db.frm to /root/incre_bak1/mysql/db.frm
200118 03:49:16 [01] ...done
……省略部分内容
200118 03:49:17 [01] Copying ./abc/test.frm to /root/incre_bak1/abc/test.frm
200118 03:49:17 [01] ...done
200118 03:49:17 Finished backing up non-InnoDB tables and files
200118 03:49:17 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1631702'
xtrabackup: Stopping log copying thread.
.200118 03:49:17 >> log scanned up to (1631702)
200118 03:49:17 Executing UNLOCK TABLES
200118 03:49:17 All tables unlocked
200118 03:49:17 Backup created in directory '/root/incre_bak1/'
200118 03:49:17 [00] Writing /root/incre_bak1/backup-my.cnf
200118 03:49:17 [00] ...done
200118 03:49:17 [00] Writing /root/incre_bak1/xtrabackup_info
200118 03:49:17 [00] ...done
xtrabackup: Transaction log of lsn (1631702) to (1631702) was copied.
200118 03:49:17 completed OK!
[root@test-centos7-node1 ~]# 3）第二次修改資料，做基于第一次增量比分為basedir做第二次整理備份
[root@test-centos7-node1 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.02 sec)
MariaDB [(none)]> create user test;
Query OK, 0 rows affected (0.07 sec)
MariaDB [(none)]> select user,host from mysql.user;
+------+--------------------+
| user | host |
+------+--------------------+
| test | % |
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
| root | test-centos7-node1 |
+------+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> \q
Bye
[root@test-centos7-node1 ~]#
[root@test-centos7-node1 ~]# xtrabackup --backup --target-dir=/root/incre_bak2/ --incremental-basedir=/root/incre_bak1/
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments: --backup=1 --target-dir=/root/incre_bak2/ --incremental-basedir=/root/incre_bak1/
200118 03:52:43 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: NO).
200118 03:52:43 version_check Connected to MySQL server
200118 03:52:43 version_check Executing a version check against the server...
200118 03:52:43 version_check Done.
200118 03:52:43 Connecting to MySQL server host: localhost, user: not set, password: not set, port: not set, socket: not set
Using server version 5.5.56-MariaDB
xtrabackup version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
incremental backup from 1631702 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: Number of pools: 1
200118 03:52:43 >> log scanned up to (1631702)
xtrabackup: Generating a list of tablespaces
xtrabackup: using the full scan for incremental backup
200118 03:52:43 [01] Copying ./ibdata1 to /root/incre_bak2/ibdata1.delta
200118 03:52:43 [01] ...done
200118 03:52:44 >> log scanned up to (1631702)
200118 03:52:44 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
200118 03:52:44 Executing FLUSH TABLES WITH READ LOCK...
200118 03:52:44 Starting to backup non-InnoDB tables and files
200118 03:52:44 [01] Copying ./mysql/db.frm to /root/incre_bak2/mysql/db.frm
200118 03:52:44 [01] ...done
……省略部分内容
200118 03:52:44 [01] Copying ./abc/test.frm to /root/incre_bak2/abc/test.frm
200118 03:52:44 [01] ...done
200118 03:52:44 Finished backing up non-InnoDB tables and files
200118 03:52:44 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1631702'
xtrabackup: Stopping log copying thread.
.200118 03:52:44 >> log scanned up to (1631702)
200118 03:52:45 Executing UNLOCK TABLES
200118 03:52:45 All tables unlocked
200118 03:52:45 Backup created in directory '/root/incre_bak2/'
200118 03:52:45 [00] Writing /root/incre_bak2/backup-my.cnf
200118 03:52:45 [00] ...done
200118 03:52:45 [00] Writing /root/incre_bak2/xtrabackup_info
200118 03:52:45 [00] ...done
xtrabackup: Transaction log of lsn (1631702) to (1631702) was copied.
200118 03:52:45 completed OK!
[root@test-centos7-node1 ~]#
到此兩次增量備份已經全部做好，接下來檢視資料，在删除庫表來進行還原
[root@test-centos7-node1 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.02 sec)
MariaDB [(none)]> use abc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [abc]> show tables;
+---------------+
| Tables_in_abc |
+---------------+
| test |
+---------------+
1 row in set (0.00 sec)
MariaDB [abc]> select * from test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.01 sec)
MariaDB [abc]> select user,host,password from mysql.user;
+------+--------------------+----------+
| user | host | password |
+------+--------------------+----------+
| root | localhost | |
| root | test-centos7-node1 | |
| root | 127.0.0.1 | |
| root | ::1 | |
| test | % | |
+------+--------------------+----------+
5 rows in set (0.01 sec)
MariaDB [abc]> \q
Bye
[root@test-centos7-node1 ~]#
删除前的資料如上，現在我們模拟删除mysql.user表和abc庫，然後利用增量備份将其還原
[root@test-centos7-node1 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]> drop database abc;
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> drop table mysql.user;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
+---------------------------+
23 rows in set (0.00 sec)
MariaDB [mysql]>
還原資料庫
合并全量備份
[root@test-centos7-node1 ~]# xtrabackup --prepare --apply-log-only --target-dir=/root/full_bak/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:10M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=5242880 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=0 --redo-log-version=0
xtrabackup: recognized client arguments: --prepare=1 --apply-log-only=1 --target-dir=/root/full_bak/
xtrabackup version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
xtrabackup: cd to /root/full_bak/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(1629736)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M: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:10M: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)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1629745
InnoDB: Number of pools: 1
200118 04:01:33 completed OK!
說明：合并全量備份要加選項--apply-log-only 表示不會滾未完成的事務，因為後面還有增量備份。
合并第一次增量備份到完全備份裡
[root@test-centos7-node1 ~]# xtrabackup --prepare --apply-log-only --target-dir=/root/full_bak/ --incremental-dir=/root/incre_bak1
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:10M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=5242880 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=0 --redo-log-version=0
xtrabackup: recognized client arguments: --prepare=1 --apply-log-only=1 --target-dir=/root/full_bak/ --incremental-dir=/root/incre_bak1
xtrabackup version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
incremental backup from 1629736 is enabled.
xtrabackup: cd to /root/full_bak/
xtrabackup: This target seems to be already prepared with --apply-log-only.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(1631702)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = /root/incre_bak1/
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Generating a list of tablespaces
xtrabackup: page size for /root/incre_bak1//ibdata1.delta is 16384 bytes space id is 0
Applying /root/incre_bak1//ibdata1.delta to ./ibdata1...
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = /root/incre_bak1/
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)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence number 1629736 in the system tablespace does not match the log sequence number 1631702 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1631711
InnoDB: Number of pools: 1
200118 04:02:37 [01] Copying /root/incre_bak1/mysql/db.frm to ./mysql/db.frm
200118 04:02:37 [01] ...done
……省略部分内容
200118 04:02:37 [01] Copying /root/incre_bak1/abc/test.frm to ./abc/test.frm
200118 04:02:37 [01] ...done
200118 04:02:37 [00] Copying /root/incre_bak1//xtrabackup_info to ./xtrabackup_info
200118 04:02:37 [00] ...done
200118 04:02:37 completed OK! 合并第二次增量備份到完全備份裡
[root@test-centos7-node1 ~]# xtrabackup --prepare --target-dir=/root/full_bak/ --incremental-dir=/root/incre_bak2
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:10M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=5242880 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=0 --redo-log-version=0
xtrabackup: recognized client arguments: --prepare=1 --target-dir=/root/full_bak/ --incremental-dir=/root/incre_bak2
xtrabackup version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
incremental backup from 1631702 is enabled.
xtrabackup: cd to /root/full_bak/
xtrabackup: This target seems to be already prepared with --apply-log-only.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(1631702)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = /root/incre_bak2/
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Generating a list of tablespaces
xtrabackup: page size for /root/incre_bak2//ibdata1.delta is 16384 bytes space id is 0
Applying /root/incre_bak2//ibdata1.delta to ./ibdata1...
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = /root/incre_bak2/
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)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence number 1631711 in the system tablespace does not match the log sequence number 1631702 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.26 started; log sequence number 1631702
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1631721
InnoDB: Number of pools: 1
200118 04:03:22 [01] Copying /root/incre_bak2/mysql/db.frm to ./mysql/db.frm
200118 04:03:22 [01] ...done
……省略部分内容
200118 04:03:23 [00] Copying /root/incre_bak2//xtrabackup_info to ./xtrabackup_info
200118 04:03:23 [00] ...done
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Setting log file ./ib_logfile101 size to 5 MB
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=1631721
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 1631756
InnoDB: Doing recovery: scanned up to log sequence number 1631765 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.26 started; log sequence number 1631765
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1631784
200118 04:03:25 completed OK!
[root@test-centos7-node1 ~]#
說明：最後一次合并不需要加--apply-log-only選項 表示這是最後一次增量備份合并，後續送出已完成對事務，復原未完成的事務，讓資料一緻
清空/var/lib/mysql目錄，然後複制備份檔案到該目錄下
[root@test-centos7-node1 ~]# rm -rf /var/lib/mysql/*
[root@test-centos7-node1 ~]# ll /var/lib/mysql/
total 0
[root@test-centos7-node1 ~]# xtrabackup --copy-back --target-dir=/root/full_bak/
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments: --copy-back=1 --target-dir=/root/full_bak/
xtrabackup version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
200118 04:12:32 [01] Copying ib_logfile0 to /var/lib/mysql/ib_logfile0
200118 04:12:32 [01] ...done
……省略部分内容
200118 04:12:32 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
200118 04:12:32 [01] ...done
200118 04:12:32 [01] Copying ./ibtmp1 to /var/lib/mysql/ibtmp1
200118 04:12:32 [01] ...done
200118 04:12:32 completed OK!
還原屬性
[root@test-centos7-node1 ~]# chown -R mysql.mysql /var/lib/mysql/
[root@test-centos7-node1 ~]# ll /var/lib/mysql/
total 40976
drwxr-x---. 2 mysql mysql 36 Jan 18 04:12 abc
drwxr-x---. 2 mysql mysql 146 Jan 18 04:12 hellodb
-rw-r-----. 1 mysql mysql 18874368 Jan 18 04:12 ibdata1
-rw-r-----. 1 mysql mysql 5242880 Jan 18 04:12 ib_logfile0
-rw-r-----. 1 mysql mysql 5242880 Jan 18 04:12 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jan 18 04:12 ibtmp1
drwxr-x---. 2 mysql mysql 4096 Jan 18 04:12 mysql
drwxr-x---. 2 mysql mysql 4096 Jan 18 04:12 performance_schema
drwxr-x---. 2 mysql mysql 20 Jan 18 04:12 test
-rw-r-----. 1 mysql mysql 475 Jan 18 04:12 xtrabackup_info
-rw-r-----. 1 mysql mysql 1 Jan 18 04:12 xtrabackup_master_key_id
[root@test-centos7-node1 ~]#
重新開機資料庫，進到資料庫檢視資料是否恢複
[root@test-centos7-node1 ~]# systemctl restart mariadb
[root@test-centos7-node1 ~]# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 50 *:3306 *:*
LISTEN 0 128 :::22 :::*
LISTEN 0 100 ::1:25 :::*
[root@test-centos7-node1 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]> use abc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [abc]> show tables;
+---------------+
| Tables_in_abc |
+---------------+
| test |
+---------------+
1 row in set (0.00 sec)
MariaDB [abc]> select * from test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
MariaDB [abc]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.01 sec)
MariaDB [mysql]> select user,host,password from user;
+------+--------------------+----------+
| user | host | password |
+------+--------------------+----------+
| root | localhost | |
| root | test-centos7-node1 | |
| root | 127.0.0.1 | |
| root | ::1 | |
| test | % | |
+------+--------------------+----------+
5 rows in set (0.00 sec)
MariaDB [mysql]> \q
Bye
[root@test-centos7-node1 ~]#
說明：可看到我們删除的abc庫已經恢複，删除的user表也全部恢複
六、xtrabackup單表導出和導入
前期準備
1）啟用innodb_file_per_table選項，并重新開機mariadb服務
[root@test-centos7-node1 ~]# grep -C 2 innodb_file_per_table /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
innodb_file_per_table
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
[root@test-centos7-node1 ~]#
說明：此選項啟用後，mariadb的表空間檔案和資料檔案就單獨存放了，不再全部都存放在ibdata1裡
2）導入資料庫
[root@test-centos7-node1 ~]# rz
rz waiting to receive.
zmodem trl+C ȡ
100% 7 KB 7 KB/s 00:00:01 0 Errors
[root@test-centos7-node1 ~]# ls
full_bak hellodb_innodb.sql incre_bak1 incre_bak2 table
[root@test-centos7-node1 ~]# mysql < hellodb_innodb.sql
[root@test-centos7-node1 ~]# ll /var/lib/mysql/
total 40996
drwxr-x---. 2 mysql mysql 36 Jan 18 05:06 abc
-rw-rw----. 1 mysql mysql 16384 Jan 18 05:12 aria_log.00000001
-rw-rw----. 1 mysql mysql 52 Jan 18 05:12 aria_log_control
drwx------. 2 mysql mysql 272 Jan 18 05:21 hellodb
-rw-r-----. 1 mysql mysql 18874368 Jan 18 05:20 ibdata1
-rw-r-----. 1 mysql mysql 5242880 Jan 18 05:21 ib_logfile0
-rw-r-----. 1 mysql mysql 5242880 Jan 18 05:06 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jan 18 05:06 ibtmp1
drwxr-x---. 2 mysql mysql 4096 Jan 18 05:06 mysql
srwxrwxrwx. 1 mysql mysql 0 Jan 18 05:12 mysql.sock
drwxr-x---. 2 mysql mysql 4096 Jan 18 05:06 performance_schema
drwxr-x---. 2 mysql mysql 20 Jan 18 05:06 test
-rw-r-----. 1 mysql mysql 475 Jan 18 05:06 xtrabackup_info
-rw-r-----. 1 mysql mysql 1 Jan 18 05:06 xtrabackup_master_key_id
[root@test-centos7-node1 ~]# ll /var/lib/mysql/hellodb/
total 1432
-rw-rw----. 1 mysql mysql 8636 Jan 18 05:21 classes.frm
-rw-rw----. 1 mysql mysql 98304 Jan 18 05:22 classes.ibd
-rw-rw----. 1 mysql mysql 8630 Jan 18 05:21 coc.frm
-rw-rw----. 1 mysql mysql 98304 Jan 18 05:22 coc.ibd
-rw-rw----. 1 mysql mysql 8602 Jan 18 05:21 courses.frm
-rw-rw----. 1 mysql mysql 98304 Jan 18 05:22 courses.ibd
-rw-rw----. 1 mysql mysql 61 Jan 18 05:21 db.opt
-rw-rw----. 1 mysql mysql 8658 Jan 18 05:21 scores.frm
-rw-rw----. 1 mysql mysql 98304 Jan 18 05:22 scores.ibd
-rw-rw----. 1 mysql mysql 8736 Jan 18 05:21 students.frm
-rw-rw----. 1 mysql mysql 98304 Jan 18 05:22 students.ibd
-rw-rw----. 1 mysql mysql 8656 Jan 18 05:21 teachers.frm
-rw-rw----. 1 mysql mysql 98304 Jan 18 05:22 teachers.ibd
-rw-rw----. 1 mysql mysql 8622 Jan 18 05:21 toc.frm
-rw-rw----. 1 mysql mysql 98304 Jan 18 05:22 toc.ibd
[root@test-centos7-node1 ~]#
[root@test-centos7-node1 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select * from hellodb.students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
MariaDB [(none)]> \q
Bye
[root@test-centos7-node1 ~]# 說明：可看到hellodb資料庫裡的表都是單獨的資料檔案和表結構檔案
3）單表備份
[root@test-centos7-node1 ~]# innobackupex --include='hellodb.students' /root/table/
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --innodb_file_per_table=1
xtrabackup: recognized client arguments:
200118 05:22:44 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!".
200118 05:22:45 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' (using password: NO).
200118 05:22:45 version_check Connected to MySQL server
200118 05:22:45 version_check Executing a version check against the server...
200118 05:22:45 version_check Done.
200118 05:22:45 Connecting to MySQL server host: localhost, user: not set, password: not set, port: not set, socket: not set
Using server version 5.5.56-MariaDB
innobackupex version 2.4.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: Number of pools: 1
200118 05:22:45 >> log scanned up to (1676617)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 14 for hellodb/students, old maximum was 0
200118 05:22:45 [01] Copying ./ibdata1 to /root/table/2020-01-18_05-22-44/ibdata1
200118 05:22:45 [01] ...done
200118 05:22:45 [01] Copying ./hellodb/students.ibd to /root/table/2020-01-18_05-22-44/hellodb/students.ibd
200118 05:22:45 [01] ...done
200118 05:22:46 >> log scanned up to (1676617)
200118 05:22:46 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
200118 05:22:46 Executing FLUSH TABLES WITH READ LOCK...
200118 05:22:46 Starting to backup non-InnoDB tables and files
200118 05:22:46 [01] Skipping ./ib_logfile0.
……省略部分内容
200118 05:22:46 [01] Skipping ./hellodb/toc.ibd.
200118 05:22:46 Finished backing up non-InnoDB tables and files
200118 05:22:46 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1676617'
xtrabackup: Stopping log copying thread.
.200118 05:22:46 >> log scanned up to (1676617)
200118 05:22:46 Executing UNLOCK TABLES
200118 05:22:46 All tables unlocked
200118 05:22:46 Backup created in directory '/root/table/2020-01-18_05-22-44/'
200118 05:22:46 [00] Writing /root/table/2020-01-18_05-22-44/backup-my.cnf
200118 05:22:46 [00] ...done
200118 05:22:46 [00] Writing /root/table/2020-01-18_05-22-44/xtrabackup_info
200118 05:22:46 [00] ...done
xtrabackup: Transaction log of lsn (1676617) to (1676617) was copied.
200118 05:22:46 completed OK!
[root@test-centos7-node1 ~]#
4）備份表結構
[root@test-centos7-node1 ~]# mysql -e 'show create table hellodb.students' > student.sql
[root@test-centos7-node1 ~]# cat student.sql
Table Create Table
students CREATE TABLE `students` (\n `StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,\n `Name` varchar(50) NOT NULL,\n `Age` tinyint(3) unsigned NOT NULL,\n `Gender` enum('F','M') NOT NULL,\n `ClassID` tinyint(3) unsigned DEFAULT NULL,\n `TeacherID` int(10) unsigned DEFAULT NULL,\n PRIMARY KEY (`StuID`)\n) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8
[root@test-centos7-node1 ~]#
5）删除表
[root@test-centos7-node1 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
MariaDB [hellodb]> drop table students ;
Query OK, 0 rows affected (0.01 sec)
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| teachers |
| toc |
+-------------------+
6 rows in set (0.00 sec)
MariaDB [hellodb]>
6）整理備份檔案
[root@test-centos7-node1 ~]# innobackupex --apply-log --export /root/table/2020-01-18_05-22-44/
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=ibdata1:10M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=5242880 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=0 --redo-log-version=0
xtrabackup: recognized client arguments:
200118 05:31:50 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.18 based on MySQL server 5.7.26 Linux (x86_64) (revision id: 29b4ca5)
xtrabackup: auto-enabling --innodb-file-per-table due to the --export option
xtrabackup: cd to /root/table/2020-01-18_05-22-44/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(1676617)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M: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:10M: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)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence number 1631794 in the system tablespace does not match the log sequence number 1676617 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Removing missing table `hellodb/classes` from InnoDB data dictionary.
InnoDB: Removing missing table `hellodb/coc` from InnoDB data dictionary.
InnoDB: Removing missing table `hellodb/courses` from InnoDB data dictionary.
InnoDB: Removing missing table `hellodb/scores` from InnoDB data dictionary.
InnoDB: Removing missing table `hellodb/teachers` from InnoDB data dictionary.
InnoDB: Removing missing table `hellodb/toc` from InnoDB data dictionary.
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.26 started; log sequence number 1676617
xtrabackup: export option is specified.
xtrabackup: export metadata of table 'hellodb/students' to file `./hellodb/students.exp` (1 indexes)
xtrabackup: name=PRIMARY, id.low=27, page=3
xtrabackup: starting shutdown with innodb_fast_shutdown = 0
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1682140
InnoDB: Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Number of pools: 1
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Setting log file ./ib_logfile101 size to 5 MB
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=1682140
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 1682444
InnoDB: Doing recovery: scanned up to log sequence number 1682453 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.26 started; log sequence number 1682453
xtrabackup: starting shutdown with innodb_fast_shutdown = 0
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1682472
200118 05:31:54 completed OK!
[root@test-centos7-node1 ~]#
7）建立表
[root@test-centos7-node1 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 18
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [hellodb]> CREATE TABLE `students` (\n `StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,\n `Name` varchar(50) NOT NULL,\n `Age` tinyint(3) unsigned NOT NULL,\n `Gender` enum('F','M') NOT NULL,\n `ClassID` tinyint(3) unsigned DEFAULT NULL,\n `TeacherID` int(10) unsigned DEFAULT NULL,\n PRIMARY KEY (`StuID`)\n) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;
PAGER set to stdout
PAGER set to stdout
PAGER set to stdout
PAGER set to stdout
PAGER set to stdout
PAGER set to stdout
PAGER set to stdout
PAGER set to stdout
Query OK, 0 rows affected (0.01 sec)
MariaDB [hellodb]> show tables ;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.01 sec)
MariaDB [hellodb]>
8）删除表空間
MariaDB [hellodb]> alter table students discard tablespace;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> \q
Bye
[root@test-centos7-node1 ~]# ll /var/lib/mysql/hellodb/
total 664
-rw-rw----. 1 mysql mysql 8636 Jan 18 05:21 classes.frm
-rw-rw----. 1 mysql mysql 98304 Jan 18 05:22 classes.ibd
-rw-rw----. 1 mysql mysql 8630 Jan 18 05:21 coc.frm
-rw-rw----. 1 mysql mysql 98304 Jan 18 05:22 coc.ibd
-rw-rw----. 1 mysql mysql 8602 Jan 18 05:21 courses.frm
-rw-rw----. 1 mysql mysql 98304 Jan 18 05:22 courses.ibd
-rw-rw----. 1 mysql mysql 61 Jan 18 05:21 db.opt
-rw-rw----. 1 mysql mysql 8658 Jan 18 05:21 scores.frm
-rw-rw----. 1 mysql mysql 98304 Jan 18 05:22 scores.ibd
-rw-rw----. 1 mysql mysql 8736 Jan 18 05:32 students.frm
-rw-rw----. 1 mysql mysql 8656 Jan 18 05:21 teachers.frm
-rw-rw----. 1 mysql mysql 98304 Jan 18 05:22 teachers.ibd
-rw-rw----. 1 mysql mysql 8622 Jan 18 05:21 toc.frm
-rw-rw----. 1 mysql mysql 98304 Jan 18 05:22 toc.ibd
[root@test-centos7-node1 ~]# ll /var/lib/mysql/hellodb/students*
-rw-rw----. 1 mysql mysql 8736 Jan 18 05:32 /var/lib/mysql/hellodb/students.frm
[root@test-centos7-node1 ~]#
說明：可看到删除了表空間，對應的檔案也被删除了
9）複制整理後的表檔案到hellodb資料庫工作目錄
[root@test-centos7-node1 ~]# ll /root/table/2020-01-18_05-22-44/hellodb/students.*
-rw-r--r--. 1 root root 640 Jan 18 05:31 /root/table/2020-01-18_05-22-44/hellodb/students.cfg
-rw-r-----. 1 root root 16384 Jan 18 05:31 /root/table/2020-01-18_05-22-44/hellodb/students.exp
-rw-r-----. 1 root root 8736 Jan 18 05:22 /root/table/2020-01-18_05-22-44/hellodb/students.frm
-rw-r-----. 1 root root 98304 Jan 18 05:22 /root/table/2020-01-18_05-22-44/hellodb/students.ibd
[root@test-centos7-node1 ~]# cp /root/table/2020-01-18_05-22-44/hellodb/students.{cfg,exp,ibd} /var/lib/mysql/hellodb/
[root@test-centos7-node1 ~]# chown -R mysql.mysql /var/lib/mysql/hellodb/
[root@test-centos7-node1 ~]# ll /var/lib/mysql/hellodb/
total 780
-rw-rw----. 1 mysql mysql 8636 Jan 18 05:21 classes.frm
-rw-rw----. 1 mysql mysql 98304 Jan 18 05:22 classes.ibd
-rw-rw----. 1 mysql mysql 8630 Jan 18 05:21 coc.frm
-rw-rw----. 1 mysql mysql 98304 Jan 18 05:22 coc.ibd
-rw-rw----. 1 mysql mysql 8602 Jan 18 05:21 courses.frm
-rw-rw----. 1 mysql mysql 98304 Jan 18 05:22 courses.ibd
-rw-rw----. 1 mysql mysql 61 Jan 18 05:21 db.opt
-rw-rw----. 1 mysql mysql 8658 Jan 18 05:21 scores.frm
-rw-rw----. 1 mysql mysql 98304 Jan 18 05:22 scores.ibd
-rw-r--r--. 1 mysql mysql 640 Jan 18 05:40 students.cfg
-rw-r-----. 1 mysql mysql 16384 Jan 18 05:40 students.exp
-rw-rw----. 1 mysql mysql 8736 Jan 18 05:32 students.frm
-rw-r-----. 1 mysql mysql 98304 Jan 18 05:40 students.ibd
-rw-rw----. 1 mysql mysql 8656 Jan 18 05:21 teachers.frm
-rw-rw----. 1 mysql mysql 98304 Jan 18 05:22 teachers.ibd
-rw-rw----. 1 mysql mysql 8622 Jan 18 05:21 toc.frm
-rw-rw----. 1 mysql mysql 98304 Jan 18 05:22 toc.ibd
[root@test-centos7-node1 ~]#
說明：整理過後的表檔案會多二個檔案一個是.cfg的檔案，一個是.exp檔案，exp檔案就是可以用于導入至其它伺服器。
10）導入表空間
[root@test-centos7-node1 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]> use hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
MariaDB [hellodb]> select * from students;
ERROR 1030 (HY000): Got error -1 from storage engine
MariaDB [hellodb]> alter table hellodb.students import tablespace;
ERROR 1030 (HY000): Got error -1 from storage engine
MariaDB [hellodb]> show variables like 'innodb_import%';
+-------------------------------------+-------+
| Variable_name | Value |
+-------------------------------------+-------+
| innodb_import_table_from_xtrabackup | 0 |
+-------------------------------------+-------+
1 row in set (0.00 sec)
MariaDB [hellodb]> set global innodb_import_table_from_xtrabackup=1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> show variables like 'innodb_import%';
+-------------------------------------+-------+
| Variable_name | Value |
+-------------------------------------+-------+
| innodb_import_table_from_xtrabackup | 1 |
+-------------------------------------+-------+
1 row in set (0.00 sec)
MariaDB [hellodb]> alter table hellodb.students import tablespace;
Query OK, 0 rows affected (0.01 sec)
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
MariaDB [hellodb]> 說明：最開始不能檢視資料的原因是表空間沒有導入進來，是以不能檢視表裡的内容。開始導入不了表空間的原因是innodb_import_table_from_xtrabackup 變量預設是0 不開啟從xtrabackup導入，設定為1後則允許導入。這裡需要提醒下mysql5.5.10之前需要開啟innodb_expand_import才可以導入表空間，後面的版本将innodb_expand_import變量改名為innodb_import_table_from_xtrabackup 是以5.5.10後需要開啟innodb_import_table_from_xtrabackup=1就可以導入表空間了
作者：Linux-1874
出處：https://www.cnblogs.com/qiuhom-1874/
本文版權歸作者和部落格園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接配接，否則保留追究法律責任的權利.