Percona XtraBackup: Backup and Restore of a Single Table or Database

The backup and restore of a complete database is an extensive exercise. But what if you need to restore just one table which has been mistakenly modified by an incorrect query? Help is at hand withPercona XtraBackup.

For our purpose, we will take a test database having tables created by the sysbench tool. The 8.0 versions ofPercona XtraBackup and Percona Server for MySQL have been used in this test.

Restore Single Table

Here we will take the backup of the sbtest2 table and restore it. The initial checksum of the table is given below:

8.0.19>CHECKSUM TABLE sbtest2;
+--------------+-----------+
| Table        | Checksum |
+--------------+-----------+
| test.sbtest2 | 905286813 |
+--------------+-----------+
1 row in set (0.01 sec)

Take a backup of a single InnoDB table using the option: --tables

./xtrabackup --user=backup --password='Bspass!4%' --backup --tables=sbtest2 --target-dir=$HOME/dbbackup_PS8_table -S $HOME/PS130320_8_0_19_10_debug/socket.sock --datadir=$HOME/PS130320_8_0_19_10_debug/data

XtraBackup copies the table file sbtest2.ibd in the backup directory (dbbackup_PS8_table/test) along with other files required to prepare the backup.

You can also give patterns in the --tables option and XtraBackup will take backups of all tables matching the pattern. If there are many tables to be backed up, then these can be specified as a list in a text file with the option --tables-file . There is also an option to exclude tables using the --tables-exclude option.

Now prepare the backup with the extra --export option. This is a special option that will prepare the table configuration.

./xtrabackup --prepare --export --target-dir=$HOME/dbbackup_PS8_table

After preparation, the files sbtest2.ibd and sbtest.cfg are available in the backup directory. To restore this table, we have to first remove the existing tablespace from the database.

8.0.19>ALTER TABLE sbtest2 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.20 sec)

Now copy the table files from the backup directory (dbbackup_PS8_table/test/sbtest2.*) to Percona Server data directory (PS130320_8_0_19_10_debug/data/test). 

Note:Before copying the files, disable selinux. After the files are copied, change the ownership of the copied files to the mysql user, if the backup user is different.

Finally, import the tablespace.

8.0.19>ALTER TABLE sbtest2 IMPORT TABLESPACE;
Query OK, 0 rows affected (1.12 sec)

The checksum of the table after the restore is:

8.0.19>CHECKSUM TABLE sbtest2;
+--------------+-----------+
| Table        | Checksum |
+--------------+-----------+
| test.sbtest2 | 905286813 |
+--------------+-----------+
1 row in set (0.02 sec)

The table is restored successfully.

Another method is to take the backup of the whole database and use it to restore one or more tables. Here, backup is simply done using the --backup option.

./xtrabackup --user=backup --password='Bspass!4%' --backup --target-dir=$HOME/dbbackup_PS8 -S $HOME/PS130320_8_0_19_10_debug/socket.sock --datadir=$HOME/PS130320_8_0_19_10_debug/data

Prepare the backup using --export option.

./xtrabackup --prepare --export --target_dir=$HOME/dbbackup_PS8

Next, discard the table’s tablespace, copy the table files from backup directory to the Percona Server data directory, and import the tablespace.

For MyISAM tables, the backup and prepare process is the same as above, and the only difference is that the table needs to be dropped and then restored using the IMPORT TABLE statement.

Restore Entire Schema/Database

We can take the backup of a database schema and restore it using the same process as described above.

Take a backup of the database using the --databases option.

./xtrabackup --user=backup --password='Bspass!4%' --backup --databases=test --target-dir=$HOME/dbbackup_PS8_db -S $HOME/PS130320_8_0_19_10_debug/socket.sock --datadir=$HOME/PS130320_8_0_19_10_debug/data

For more than one database, specify the databases as a list, such as --databases="db1 db2 db3" . The databases can also be specified in a text file and used with the option --databases-file . To exclude a database from the backup, use the option --databases-exclude .

Prepare the backup using --export option.

./xtrabackup --prepare --export --target-dir=$HOME/dbbackup_PS8_db

Now remove the tablespace of all InnoDB tables in the database using ALTER TABLE <table name> DISCARD TABLESPACE.

Copy all table files from the backup dir (dbbackup_PS8_db/test/*) to the mysql data dir(PS130320_8_0_19_10_debug/data/test).

Note:Before copying the files, disable selinux. After the files are copied change the ownership of the copied files to mysql user if the backup user is different.

Finally, restore the tables using ALTER TABLE <table name> IMPORT TABLESPACE;.

This will restore the tables to the time of the backup. For a point in time recovery , binlogs can be further applied to the database, though care should be taken to apply only those transactions which affect the tables being restored.

The advantage of using this method is that the database server need not be stopped. A slight disadvantage is that each table needs to be restored individually, though it can be overcome with the help of a script.

Conclusion

It is easy to backup and restore a table or a database usingPercona XtraBackup, by using just a few commands.