Shulou(Shulou.com)06/01 Report--

Test preparation

1. Install the xtrabackup tool

The following test installs the binary package of the xtrabackup backup and recovery tool for testing. The installation path is the mysql installation path, and the test mysql installation path is: / 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. Create a backup user

Create backup user backup@localhost,xtraback can only be used for local operations, so only local operations are authorized.

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

3. Declare PATH environment variables

Add the xtrabackup backup recovery tool to the PATH path.

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

4. Create a test library

Create test library backup_test, test table 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);'

Test innodb

1. Full backup test

The full backup test mainly verifies the data backup and recovery by testing the following scenarios.

1) back up the entire database and restore it for testing.

Make a full library backup:

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

Turn off the database service:

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

Delete the current data file:

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

Restore log files:

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

Restore the data file:

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

Data validation:

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

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

Test results:

It has been verified that the backup recovery is correct.

2) modify the table structure, back up the whole database, restore and test.

Modify the database table structure:

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

Make a full library backup:

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

Turn off the database service:

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

Delete the current data file:

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

Restore log files:

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

Restore the data file:

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

Data validation:

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

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

Test results:

It has been verified that the backup recovery is correct.

3) back up a library and restore the test

Back up the backup_ test library

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

Turn off the database service:

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

Modify the current data file:

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

Mkdir / home/q/percona-server/data

Restore log files:

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

Restore the data file:

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 (copy only files that are not in data)

Data validation:

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

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

Test results:

It has been verified that there is no problem with backing up a single library. The disadvantage is that when restoring, the data directory must be empty. In other words, during the recovery phase, you need to rename the existing data directory to a temporary directory; after the restore, copy the contents of other libraries under the original data directory (now the temporary directory) to the data directory.

4) back up a single table and restore the test

Back up the backup_ test library

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

Turn off the database service:

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

Modify the current data file:

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

Mkdir / home/q/percona-server/data

Restore log files:

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

Restore the data file:

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

Data validation:

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

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

Test results:

It has been verified that there is no problem with backing up a single table. The disadvantage is that when restoring, the data directory must be empty. In other words, during the recovery phase, you need to rename the existing data directory to a temporary directory; after the restore, copy the contents of other libraries under the original data directory (now the temporary directory) to the data directory.

5) innodb_file_per_table parameter verification

Modify the configuration file

Add innodb_file_per_table configuration parameters to the configuration file.

Restart the database 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 &

Create a test library 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);'

Back up the backup_ test library

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

Turn off the database service:

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

Modify the current data file:

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

Mkdir / home/q/percona-server/data

Restore log files:

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

Restore the data file:

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

Data validation:

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

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

Test results:

It has been verified that there is no problem with backing up a single library. The disadvantage is that when restoring, the data directory must be empty. In other words, during the recovery phase, you need to rename the existing data directory to a temporary directory; after the restore, copy the contents of other libraries under the original data directory (now the temporary directory) to the data directory.

2. Incremental backup test

Incremental backup testing mainly validates data backup and recovery by testing the following scenarios.

1) full database backup, database creation, incremental backup, recovery testing

Full database backup

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

Create a database

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);'

Incremental backup

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

Turn off the database service:

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

Delete the current data file:

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

Restore the full backup log files:

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

Restore incremental backup log files:

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

Restore incremental backup data files:

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

Data validation:

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;'

Test results:

It has been verified that the backup recovery is correct.

2) full database backup, modification of data table structure, incremental backup, recovery testing

Full database backup

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

Modify the data table structure

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

Incremental backup

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

Turn off the database service:

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

Delete the current data file:

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

Restore the full backup log files:

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

Restore incremental backup log files:

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

Restore incremental backup data files:

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

Data validation:

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

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

Test results:

It has been verified that there is no problem with incremental backups. The disadvantage is that when restoring, first, the data directory must be empty, and secondly, the table structure of the incremental backup needs to be copied separately. That is, during the recovery phase, the data directory needs to be emptied; after the data is restored, the table structure under the incremental backup folder needs to be copied separately.

3) full database backup, create database, incremental backup, modify table structure, incremental backup, restore testing

Full database backup

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

Create a database

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);'

Incremental backup

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

Modify the data table structure

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

Incremental backup

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

Turn off the database service:

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

Delete the current data file:

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

Restore the full backup log files:

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

Restore incremental backup log files:

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

Restore incremental backup log files:

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

Restore incremental backup data files:

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

Data validation:

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

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

Test results:

It has been verified that there is no problem with incremental backups. The disadvantage is that when restoring, first, the data directory must be empty, and secondly, the table structure of the last incremental backup needs to be copied separately. That is, during the recovery phase, the data directory needs to be emptied; after the data is restored, the table structure under the last incremental backup folder needs to be copied separately.

4) full backup of a library, modification of the library, incremental backup, recovery test

Full backup of a library

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

Modify the library

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

Incremental backup

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

Turn off the database service:

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

Delete the current data file:

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

Restore the full backup log files:

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

Restore incremental backup log files:

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

Restore incremental backup data files:

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

Data validation:

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

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

Test results:

It has been verified that there is no problem with incremental backups. The disadvantage is that when restoring, first, the data directory must be empty, and secondly, the table structure of the incremental backup needs to be copied separately. In other words, during the recovery phase, you need to rename the existing data directory to a temporary directory; after the restore, copy the contents of other libraries under the original data directory (now the temporary directory) to the data directory. After the data is restored, you also need to copy the table structure under the incremental backup folder separately.

5) innodb_file_per_table parameter verification

Modify the configuration file

Add innodb_file_per_table configuration parameters to the configuration file.

Restart the database 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 &

Create a test library 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);'

Back up the backup_ test library

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

Modify the library

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

Incremental backup

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

Turn off the database service:

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

Delete the current data file:

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

Restore the full backup log files:

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

Restore incremental backup log files:

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

Restore incremental backup data files:

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

Data validation:

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

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

Test results:

It has been verified that there is no problem with incremental backups. The disadvantage is that when restoring, first, the data directory must be empty, and secondly, the table structure of the incremental backup needs to be copied separately. In other words, during the recovery phase, you need to rename the existing data directory to a temporary directory; after the restore, copy the contents of other libraries under the original data directory (now the temporary directory) to the data directory. After the data is restored, you also need to copy the table structure under the incremental backup folder separately.

3. Differential backup test

Differential backup is actually an incremental backup operation, which is included in the incremental backup test, and the test is not repeated again.

Test myisam

1. Full backup test

Create an myisam data table

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);'

Full backup data table

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

Turn off the database service:

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

Delete the current data file:

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

Restore log files:

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

Restore the data file:

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

Data validation:

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

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

Test results:

It has been verified that the backup recovery is correct.

2. Incremental backup test

Full backup data table

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

Modify the data table structure

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

Incremental backup

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

Turn off the database service:

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

Delete the current data file:

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

Restore the full backup log files:

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

Restore incremental backup log files

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

Restore the data file:

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

Data validation:

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

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

Test results:

It has been verified that the backup recovery is correct.

3. Differential backup test

Differential backup is actually an incremental backup operation, which is included in the incremental backup test, and the test is not repeated again.

Test summary

Setting up the above scenarios, after testing, it is found that there are still some potential problems in the xtrabackup backup and recovery tool, but these problems can be improved and avoided by evading or improving the script.

Realization principle

Make a backup of InnoDB,XtraBackup 's InnoDB-based crash-recovery feature.

Crash-recovery goes like this: InnoDB maintains a redo log, also known as transaction log, or transaction log, which contains all changes to InnoDB data. When InnoDB starts, it checks datafile and transaction log, then applies all committed transactions and rolls back all uncommitted transactions.

XtraBackup does not lock the table when backing up, but copies InnoDB data page by page. At the same time, XtraBackup has another thread monitoring transactions log. Once the log changes, it copies the changed log pages away (because the transactions log file size is limited, after it is full, it will start all over again, and the new data may overwrite the old data, so as soon as the change is made, it will be copied away immediately). Stop copying logfile after all data files have been copied.

XtraBackup uses its built-in InnoDB library to open InnoDB data files in read-write mode, and then reads and writes 1MB (1MB/16KB=64page) data every time, page by page, while using InnoDB's buf_page_is_corrupted () function to check whether the data on this page is normal, copy if normal, reread if abnormal, re-read up to 10 times, if it still fails, the backup fails to exit. The principle of copying transactions log is the same, except that every time you read and write 512KB (512KB/16KB=32page) data.

Because XtraBackup's built-in InnoDB library opens the file with rw, the user running XtraBackup must have read and write access to InnoDB's data file.

Because XtraBackup replicates large amounts of data from the file system, it uses posix_fadvise () as much as possible to tell OS not to cache read data (because it won't be reused) to improve performance. If you want to cache, a large amount of data will put a lot of pressure on the virtual memory of OS, and other processes (such as mysqld) are likely to be swap, which will cause problems. At the same time, XtraBackup pre-reads as much as possible when reading the data.

Because the table is not locked, the copied data is inconsistent, and the consistency of the data is achieved using crash-recovery at the time of recovery.

For MyISAM,XtraBackup, all tables are locked first, and then all files are copied.

Application scenario

Based on the above principles, xtrabackup backup and recovery tool is more suitable for data growth database. For data growth libraries, the space and time of data backup and recovery are under great pressure due to the growth of data. Xtrabackup has the function of incremental backup, which can ensure the security of data in a short period of time. In the long run, full-library backup is still needed intermittently. In addition, because xtrabackup does not lock innodb's database, it is suitable for data backup and recovery that do not affect online services.

However, for the newer database with no obvious increase in the amount of data and mainly updated, xtrabackup appears to be too complex. The performance of xtrabackup operation is not as high as that of mysqldump.

Suggestion

Through the above tests, there are the following suggestions for DBA reference:

1. Improve and refine the innobackupex script, or write backup and restore scripts. Avoid deficiencies and possible problems in backups.

2. It is recommended to specify a careful backup and recovery strategy according to the type of database.

Reference:

Http://blog.csdn.net/yongsheng0550/article/details/6682162

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.