xtrabackup備份恢復測試

2024-09-07 22:12:43

字體：大中小

來源：轉載

供稿：網友

測試準備

1、安裝xtrabackup工具

       以下測試安裝xtrabackup備份恢復工具的二進制包，進行測試。安裝的路徑為mysql安裝路徑，本次測試mysql的安裝路徑為：/home/q/percona-server/。

tar -xzf percona-xtraback-2.0.0-x86-64.tar.gz

cp percona-xtrabackup-2.0.0/bin/* /home/q/percona-server/bin

2、創建備份用戶

       創建備份用戶backup@localhost，xtraback只能用于本地操作，故只授權本地操作。

GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO backup@localhost;

3、聲明PATH環境變量

       為xtrabackup備份恢復工具添加到PATH路徑中。

export PATH=$PATH:/home/q/percona-server/bin

4、創建測試庫

創建測試庫backup_test，測試表test。

mysql -uroot -S/tmp/mysql.sock -e'create database backup_test;'

mysql -uroot -S/tmp/mysql.sock -e'create table backup_test.test (id int);'

mysql -uroot -S/tmp/mysql.sock -e'insert into backup_test.test values(1),(2),(3),(4),(5);'

測試innodb

1、全備份測試

全備份測試主要通過測試以下幾個場景，對數據備份和恢復進行驗證。

1）備份全庫，恢復進行測試。

進行全庫備份：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --user=backup backup/

       關閉數據庫服務：

kill -9 $(ps -ef|grep "mysql"|gawk '$0 !~/grep/ {print $2}' |tr -s '/n' ' ')

刪除當前數據文件：

rm -rf /home/q/percona-server/data/*

       恢復日志文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --apply-log --user=backup backup/

       恢復數據文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --copy-back --user=backup backup/

       數據驗證：

mysqld_safe --defaults-file=/home/q/percona-server/etc/my.cnf &

mysql -uroot -S/tmp/mysql.sock -e'select * from backup_test.test;'

測試結果：

       經驗證，備份恢復正確。

2）修改表結構，備份全庫，恢復進行測試。

       修改數據庫表結構：

mysql -uroot -S/tmp/mysql.sock -e 'alter table backup_test.test add name varchar(20) default "null";'

進行全庫備份：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --user=backup backup/

       關閉數據庫服務：

kill -9 $(ps -ef|grep "mysql"|gawk '$0 !~/grep/ {print $2}' |tr -s '/n' ' ')

刪除當前數據文件：

rm -rf /home/q/percona-server/data/*

       恢復日志文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --apply-log --user=backup backup/

       恢復數據文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --copy-back --user=backup backup/

       數據驗證：

mysqld_safe --defaults-file=/home/q/percona-server/etc/my.cnf &

mysql -uroot -S/tmp/mysql.sock -e'select * from backup_test.test;'

測試結果：

       經驗證，備份恢復正確。

3）備份某個庫，恢復測試

       備份backup_test庫

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --database=backup_test --user=backup backup/

關閉數據庫服務：

kill -9 $(ps -ef|grep "mysql"|gawk '$0 !~/grep/ {print $2}' |tr -s '/n' ' ')

修改當前數據文件：

mv /home/q/percona-server/data /home/q/percona-server/databak

mkdir /home/q/percona-server/data

       恢復日志文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --apply-log --database=backup_test --user=backup backup/

       恢復數據文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --copy-back --database=backup_test --user=backup backup/

cp /home/q/percona-server/databak/* /home/q/percona-server/data(僅拷貝不在data中的文件)

       數據驗證：

mysqld_safe --defaults-file=/home/q/percona-server/etc/my.cnf &

mysql -uroot -S/tmp/mysql.sock -e'select * from backup_test.test;'

測試結果：

       經驗證，備份單個庫沒有問題。存在的不足是，恢復的時候，數據目錄必須為空。也就是說，在恢復階段，需要將現有的數據目錄改名為臨時目錄；在恢復之后，將原數據目錄（現臨時目錄）下的其他庫的內容拷貝到數據目錄下即可。

4）備份單個表，恢復測試

備份backup_test庫

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --database=backup_test.test --user=backup backup/

關閉數據庫服務：

kill -9 $(ps -ef|grep "mysql"|gawk '$0 !~/grep/ {print $2}' |tr -s '/n' ' ')

修改當前數據文件：

mv /home/q/percona-server/data /home/q/percona-server/databak

mkdir /home/q/percona-server/data

       恢復日志文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --apply-log --user=backup backup/

       恢復數據文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --copy-back --user=backup backup/

       數據驗證：

mysqld_safe --defaults-file=/home/q/percona-server/etc/my.cnf &

mysql -uroot -S/tmp/mysql.sock -e'select * from backup_test.test;'

測試結果：

       經驗證，備份單個表沒有問題。存在的不足是，恢復的時候，數據目錄必須為空。也就是說，在恢復階段，需要將現有的數據目錄改名為臨時目錄；在恢復之后，將原數據目錄（現臨時目錄）下的其他庫的內容拷貝到數據目錄下即可。

5）innodb_file_per_table參數驗證

       修改配置文件

在配置文件中添加innodb_file_per_table配置參數。

       重啟數據庫server

kill -9 $(ps -ef|grep "mysql"|gawk '$0 !~/grep/ {print $2}' |tr -s '/n' ' ')

mysqld_safe --defaults-file=/home/q/percona-server/etc/my.cnf &

創建測試庫backup_test

mysql -uroot -S/tmp/mysql.sock -e'create database backup_test;'

mysql -uroot -S/tmp/mysql.sock -e'create table backup_test.test (id int);'

mysql -uroot -S/tmp/mysql.sock -e'insert into backup_test.test values(1),(2),(3),(4),(5);'

備份backup_test庫

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --database=backup_test.test --user=backup backup/

關閉數據庫服務：

kill -9 $(ps -ef|grep "mysql"|gawk '$0 !~/grep/ {print $2}' |tr -s '/n' ' ')

修改當前數據文件：

mv /home/q/percona-server/data /home/q/percona-server/databak

mkdir /home/q/percona-server/data

       恢復日志文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --apply-log --user=backup backup/

       恢復數據文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --copy-back --user=backup backup/

       數據驗證：

mysqld_safe --defaults-file=/home/q/percona-server/etc/my.cnf &

mysql -uroot -S/tmp/mysql.sock -e'select * from backup_test.test;'

測試結果：

       經驗證，備份單個庫沒有問題。存在的不足是，恢復的時候，數據目錄必須為空。也就是說，在恢復階段，需要將現有的數據目錄改名為臨時目錄；在恢復之后，將原數據目錄（現臨時目錄）下的其他庫的內容拷貝到數據目錄下即可。

2、增量備份測試

增量備份測試主要通過測試以下幾個場景，對數據備份和恢復進行驗證。

1）全庫備份，創建數據庫，增量備份，恢復測試

       全庫備份

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --user=backup backup/

       創建數據庫

mysql -uroot -S/tmp/mysql.sock -e'create database backup_test_tmp;'

mysql -uroot -S/tmp/mysql.sock -e'create table backup_test_tmp.test (id int);'

mysql -uroot -S/tmp/mysql.sock -e'insert into backup_test_tmp.test values(1),(2),(3),(4),(5);'

       增量備份

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --user=backup --incremental --incremental-basedir=backup/ backup/

關閉數據庫服務：

kill -9 $(ps -ef|grep "mysql"|gawk '$0 !~/grep/ {print $2}' |tr -s '/n' ' ')

刪除當前數據文件：

rm -rf /home/q/percona-server/data/*

       恢復全備份日志文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --apply-log --user=backup backup/

       恢復增量備份日志文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --apply-log --user=backup backup/ --incremental-dir=backup/

       恢復增量備份數據文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --copy-back --user=backup backup/

數據驗證：

mysqld_safe --defaults-file=/home/q/percona-server/etc/my.cnf &

mysql -uroot -S/tmp/mysql.sock -e'select * from backup_test.test;'

mysql -uroot -S/tmp/mysql.sock -e'select * from backup_test_tmp.test;'

測試結果：

       經驗證，備份恢復正確。

2）全庫備份，修改數據表結構，增量備份，恢復測試

       全庫備份

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --user=backup backup/

       修改數據表結構

mysql -uroot -S/tmp/mysql.sock -e 'alter table backup_test.test add email varchar(20) default "null";'

       增量備份

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --user=backup --incremental --incremental-basedir=backup/ backup/

關閉數據庫服務：

kill -9 $(ps -ef|grep "mysql"|gawk '$0 !~/grep/ {print $2}' |tr -s '/n' ' ')

刪除當前數據文件：

rm -rf /home/q/percona-server/data/*

       恢復全備份日志文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --apply-log --user=backup backup/

       恢復增量備份日志文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --apply-log --user=backup backup/ --incremental-dir=backup/

       恢復增量備份數據文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --copy-back --user=backup backup/

數據驗證：

mysqld_safe --defaults-file=/home/q/percona-server/etc/my.cnf &

mysql -uroot -S/tmp/mysql.sock -e'select * from backup_test.test;'

測試結果：

       經驗證，增量備份沒有問題。存在的不足是，恢復的時候，首先數據目錄必須為空，其次，恢復的時候需要單獨拷貝增量備份的表結構。也就是說，在恢復階段，需要將數據目錄清空；數據恢復后，需要單獨拷貝增量備份文件夾下的表結構。

3）全庫備份，創建數據庫，增量備份，修改表結構，增量備份，恢復測試

       全庫備份

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --user=backup backup/

創建數據庫

mysql -uroot -S/tmp/mysql.sock -e'create database backup_test_tmp;'

mysql -uroot -S/tmp/mysql.sock -e'create table backup_test_tmp.test (id int);'

mysql -uroot -S/tmp/mysql.sock -e'insert into backup_test_tmp.test values(1),(2),(3),(4),(5);'

       增量備份

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --user=backup --incremental --incremental-basedir=backup/ backup/

修改數據表結構

mysql -uroot -S/tmp/mysql.sock -e 'alter table backup_test.test add email varchar(20) default "null";'

       增量備份

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --user=backup --incremental --incremental-basedir=backup/ backup/

關閉數據庫服務：

kill -9 $(ps -ef|grep "mysql"|gawk '$0 !~/grep/ {print $2}' |tr -s '/n' ' ')

刪除當前數據文件：

rm -rf /home/q/percona-server/data/*

       恢復全備份日志文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --apply-log --user=backup backup/

       恢復增量備份1日志文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --apply-log --user=backup backup/ --incremental-dir=backup/

恢復增量備份2日志文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --apply-log --user=backup backup/ --incremental-dir=backup/

       恢復增量備份數據文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --copy-back --user=backup backup/

數據驗證：

mysqld_safe --defaults-file=/home/q/percona-server/etc/my.cnf &

mysql -uroot -S/tmp/mysql.sock -e'select * from backup_test.test;'

測試結果：

       經驗證，增量備份沒有問題。存在的不足是，恢復的時候，首先數據目錄必須為空，其次，恢復的時候需要單獨拷貝最后一次增量備份的表結構。也就是說，在恢復階段，需要將數據目錄清空；數據恢復后，需要單獨拷貝最后一次增量備份文件夾下的表結構。

4）全備份某庫，修改庫，增量備份，恢復測試

       全備份某庫

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --user=backup --database=backup_test backup/

       修改庫

mysql -uroot -S/tmp/mysql.sock -e 'alter table backup_test.test add address varchar(20) default "null";'

       增量備份

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --user=backup --database=backup_test --incremental --incremental-basedir=backup/ backup/

關閉數據庫服務：

kill -9 $(ps -ef|grep "mysql"|gawk '$0 !~/grep/ {print $2}' |tr -s '/n' ' ')

刪除當前數據文件：

rm -rf /home/q/percona-server/data/backup_test/*

       恢復全備份日志文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --database=backup_test --apply-log --user=backup backup/

       恢復增量備份日志文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --database=backup_test --apply-log --user=backup backup/ --incremental-dir=backup/

       恢復增量備份數據文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --database=backup_test --copy-back --user=backup backup/

數據驗證：

mysqld_safe --defaults-file=/home/q/percona-server/etc/my.cnf &

mysql -uroot -S/tmp/mysql.sock -e'select * from backup_test.test;'

測試結果：

       經驗證，增量備份沒有問題。存在的不足是，恢復的時候，首先數據目錄必須為空，其次，恢復的時候需要單獨拷貝增量備份的表結構。也就是說，在恢復階段，需要將現有的數據目錄改名為臨時目錄；在恢復之后，將原數據目錄（現臨時目錄）下的其他庫的內容拷貝到數據目錄下即可。數據恢復后，還需要單獨拷貝增量備份文件夾下的表結構。

5）innodb_file_per_table參數驗證

       修改配置文件

在配置文件中添加innodb_file_per_table配置參數。

       重啟數據庫server

kill -9 $(ps -ef|grep "mysql"|gawk '$0 !~/grep/ {print $2}' |tr -s '/n' ' ')

mysqld_safe --defaults-file=/home/q/percona-server/etc/my.cnf &

創建測試庫backup_test

mysql -uroot -S/tmp/mysql.sock -e'create database backup_test;'

mysql -uroot -S/tmp/mysql.sock -e'create table backup_test.test (id int);'

mysql -uroot -S/tmp/mysql.sock -e'insert into backup_test.test values(1),(2),(3),(4),(5);'

備份backup_test庫

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --database=backup_test.test --user=backup backup/

修改庫

mysql -uroot -S/tmp/mysql.sock -e 'alter table backup_test.test add phone varchar(20) default "null";'

       增量備份

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --user=backup --database=backup_test --incremental --incremental-basedir=backup/ backup/

關閉數據庫服務：

kill -9 $(ps -ef|grep "mysql"|gawk '$0 !~/grep/ {print $2}' |tr -s '/n' ' ')

刪除當前數據文件：

rm -rf /home/q/percona-server/data/backup_test/*

       恢復全備份日志文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --database=backup_test --apply-log --user=backup backup/

       恢復增量備份日志文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --database=backup_test --apply-log --user=backup backup/ --incremental-dir=backup/

       恢復增量備份數據文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --database=backup_test --copy-back --user=backup backup/

數據驗證：

mysqld_safe --defaults-file=/home/q/percona-server/etc/my.cnf &

mysql -uroot -S/tmp/mysql.sock -e'select * from backup_test.test;'

測試結果：

       經驗證，增量備份沒有問題。存在的不足是，恢復的時候，首先數據目錄必須為空，其次，恢復的時候需要單獨拷貝增量備份的表結構。也就是說，在恢復階段，需要將現有的數據目錄改名為臨時目錄；在恢復之后，將原數據目錄（現臨時目錄）下的其他庫的內容拷貝到數據目錄下即可。數據恢復后，還需要單獨拷貝增量備份文件夾下的表結構。

3、差分備份測試

       差分備份實際是一次增量備份操作，該過程包含在增量備份測試中，再次不再重復測試。

測試myisam

1、全備份測試

       創建myisam數據表

mysql -uroot -S/tmp/mysql.sock -e'create table backup_test.test_myisam (id int) engine=myisam;'

mysql -uroot -S/tmp/mysql.sock -e'insert into backup_test.test_myisam values(1),(2),(3),(4),(5);'

       全備份數據表

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --user=backup --database=backup_test.test_myisam backup/

關閉數據庫服務：

kill -9 $(ps -ef|grep "mysql"|gawk '$0 !~/grep/ {print $2}' |tr -s '/n' ' ')

刪除當前數據文件：

rm -rf /home/q/percona-server/data/backup_test/test_myisam*

       恢復日志文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --database=backup_test.test_myisam --apply-log --user=backup backup/

       恢復數據文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --database=backup_test.test_myisam --copy-back --user=backup backup/

數據驗證：

mysqld_safe --defaults-file=/home/q/percona-server/etc/my.cnf &

mysql -uroot -S/tmp/mysql.sock -e'select * from backup_test.test_myisam;'

測試結果：

       經驗證，備份恢復正確。

2、增量備份測試

全備份數據表

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --user=backup --database=backup_test.test_myisam backup/

修改數據表結構

mysql -uroot -S/tmp/mysql.sock -e 'alter table backup_test.test_myisam add name varchar(20) default "null";'

       增量備份

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --user=backup --database=backup_test.test_myisam --incremental --incremental-basedir=backup/backup/

關閉數據庫服務：

kill -9 $(ps -ef|grep "mysql"|gawk '$0 !~/grep/ {print $2}' |tr -s '/n' ' ')

刪除當前數據文件：

rm -rf /home/q/percona-server/data/backup_test/test_myisam*

       恢復全備份日志文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --database=backup_test.test_myisam --apply-log --user=backup backup/

       恢復增量備份日志文件

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --database=backup_test.test_myisam --apply-log --user=backup backup/ --incremental-dir=backup/

       恢復數據文件：

innobackupex --defaults-file=/home/q/percona-server/etc/my.cnf --database=backup_test.test_myisam --copy-back --user=backup backup/

數據驗證：

mysqld_safe --defaults-file=/home/q/percona-server/etc/my.cnf &

mysql -uroot -S/tmp/mysql.sock -e'select * from backup_test.test_myisam;'

測試結果：

       經驗證，備份恢復正確。

3、差分備份測試

差分備份實際是一次增量備份操作，該過程包含在增量備份測試中，再次不再重復測試。

測試總結

設置以上場景，經測試，發現xtrabackup備份恢復工具仍然存在一些潛在的問題，但是這些問題完全可以通過規避或者完善腳本的方式，來改進和避免存在的問題。

實現原理

對于InnoDB，XtraBackup基于InnoDB的crash-recovery功能進行備份。

crash-recovery是這樣的：InnoDB維護了一個redo log，又稱為 transaction log，也叫事務日志，它包含了InnoDB數據的所有改動情況。InnoDB啟動的時候先去檢查datafile和transaction log，然后應用所有已提交的事務并回滾所有未提交的事務。

XtraBackup在備份的時候并不鎖定表，而是一頁一頁地復制InnoDB的數據，與此同時，XtraBackup還有另外一個線程監視著transactions log，一旦log發生變化，就把變化過的log pages復制走(因為transactions log文件大小有限，寫滿之后，就會從頭再開始寫，新數據可能會覆蓋到舊的數據，所以一旦變化就要立刻復制走）。在全部數據文件復制完成之后，停止復制logfile。

XtraBackup采用了其內置的InnoDB庫以read-write模式打開InnoDB的數據文件，然后每次讀寫1MB(1MB/16KB=64page)的數據，一頁一頁地遍歷，同時用InnoDB的buf_page_is_corrupted()函數檢查此頁的數據是否正常，如果正常則進行復制，如不正常則重新讀取，最多重讀10次，如果還是失敗，則備份失敗退出。復制transactions log的原理也是一樣的，只不過每次讀寫512KB(512KB/16KB=32page)的數據。

由于XtraBackup其內置的InnoDB庫打開文件的時候是rw的，所以運行XtraBackup的用戶，必須對InnoDB的數據文件具有讀寫權限。

由于XtraBackup要從文件系統中復制大量的數據，所以它盡可能地使用posix_fadvise()，來告訴OS不要緩存讀取到的數據(因為這些數據不會重用到了)，從而提升性能。如果要緩存的話，大量的數據會對OS的虛擬內存造成很大的壓力，其它進程（如mysqld）很有可能會被swap出去，這樣就出問題了。同時，XtraBackup在讀取數據的時候還盡可能地預讀。

由于不鎖表，所以復制出來的數據是不一致的，數據的一致性是在恢復的時候使用crash-recovery進行實現的。

對于MyISAM,XtraBackup還是首先鎖定所有的表，然后復制所有文件。

應用場景

       基于以上原理，xtrabackup備份恢復工具比較適合數據增長型數據庫。對于數據增長型的庫，由于數據的增長導致數據備份和恢復的空間和時間上的壓力較大。而xtrabackup有增量備份的功能，在短時間內可以通過進行增量備份來保證數據的安全性。而長期來看，仍然需要間斷性的進行全庫備份。此外，由于xtrabackup對innodb的數據庫不進行鎖定，因此對要求不影響線上服務的數據備份和恢復較適合。

       而對于數據量無明顯增長，且更新為主的數據更新型數據庫，xtrabackup顯得過于復雜。xtrabackup操作反而不如mysqldump的性能高。

建議

       通過以上測試，有以下建議，供DBA參考：

1、改進和完善innobackupex腳本，或者編寫備份恢復腳本。避免備份中存在的不足和可能出現的問題。

2、建議根據數據庫的類型，指定周密的備份恢復策略。

（編輯：武林網）

上一篇：一個left join SQL 簡單優化分析

下一篇：MyISAM Table Storage Formats