In MySQL 5.6 Oracle introduced a Transportable Tablespace feature (copying tablespaces to another server) and Percona Server adopted it for partial backups which means you can now take individual database or table backups and your destination server can be a vanilla MySQL server. Moreover, since Percona Server 5.6, innodb_import_table_from_xtrabackup is obsolete as Percona Server also implemented Oracle MySQL’s transportable tablespaces feature which as I mentioned gives you the ability to copy tablespace (table.ibd) between servers. Let me demonstrate this through one example where I am going to take partial backup of selective tables instead of an entire MySQL server and restore it on a running MySQL server on destination without taking it offline.
MySQL 5.6 Transportable Tablespaces with Percona XtraBackup
Percona XtraBackup is open source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases. You can backup up your databases without sacrificing read/write ability and, on top of that, Percona XtraBackup supports partial backup schemas that correspond to backup-only specific databases or tables instead of taking backups of the entire database server.
For partial backups, your source server from where you taking the backup must have the innodb_file_per_table option enabled and the importing server should have innodb_file_per_table and innodb_expand_import enabled – or innodb_import_table_from_xtrabackup (only supported for Percona Server) depends on Percona Server version for the the last option for restoring the database tables. This is all valid till Percona Server version 5.5 and you can find further details about partial backups here. Percona CTO Vadim Tkachenko wrote nice post on it about how to copy InnoDB tables between servers on Percona Server prior to version 5.6.
I am going to use Percona Server 5.6 as it uses the feature of Transportable Tablespace. There are two tables under database irfan named as “test” and “dummy”. I am going to take backup of only test table as partial backup instead taking backup of entire database server.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | mysql> show tables; +----------------+ | Tables_in_irfan| +----------------+ | dummy | | test | +----------------+ mysql> show create table test; +-------+--------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `id` int(11) DEFAULT NULL, `name` char(15) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+--------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(*) FROM test; +----------+ | COUNT(*) | +----------+ | 1000 | +----------+ 1 row in set (0.00 sec) |
I am going to use latest version of Percona XtraBackup which supports multiple ways to take partial backups that are –include option, –tables-file option and –databases option. I am going to use –tables-file option to take backup of specific database table.
1 2 3 4 5 6 7 | irfan@source$ xtrabackup --version xtrabackup version 2.2.5 based on MySQL server 5.6.21 Linux (x86_64) (revision id: ) irfan@source$ mysql --skip-column-names -e "SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables WHERE table_schema IN ('irfan') AND TABLE_NAME = 'test';" > /root/tables_to_backup.txt irfan@source$ cat tables_to_backup.txt irfan.test |
Now as below you need to take backup of irfan.test database table. Note, how –tables-file option passed to backup only irfan.test database table which takes backup of only specified database table in tables_to_backup.txt file.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | irfan@source$ innobackupex --no-timestamp --tables-file=/root/tables_to_backup.txt /root/partial_backup/ > /root/xtrabackup.log 2>&1 irfan@source$ cat /root/xtrabackup.log . . >> log scanned up to (2453801809) >> log scanned up to (2453801809) [01] ...done [01] Copying ./irfan/test.ibd to /root/partial_backup/irfan/test.ibd [01] ...done xtrabackup: Creating suspend file '/root/partial_backup/xtrabackup_suspended_2' with pid '14442' . . 141101 12:37:27 innobackupex: Starting to backup non-InnoDB tables and files innobackupex: in subdirectories of '/var/lib/mysql' innobackupex: Backing up file '/var/lib/mysql/irfan/test.frm' 141101 12:37:27 innobackupex: Finished backing up non-InnoDB tables and files 141101 12:37:27 innobackupex: Executing LOCK BINLOG FOR BACKUP... 141101 12:37:27 innobackupex: Executing FLUSH ENGINE LOGS... 141101 12:37:27 innobackupex: Waiting for log copying to finish . . xtrabackup: Creating suspend file '/root/partial_backup/xtrabackup_log_copied' with pid '14442' xtrabackup: Transaction log of lsn (2453801809) to (2453801809) was copied. 141101 12:37:28 innobackupex: Executing UNLOCK BINLOG 141101 12:37:28 innobackupex: Executing UNLOCK TABLES 141101 12:37:28 innobackupex: All tables unlocked . . 141101 12:37:28 innobackupex: completed OK! |
Successful backup with show you “completed OK” at the end of the backup. If you scripted the backup for automation you can also check backup status to see whether it succeeded or failed by checking exit status of the backup script.
For the next step, we need to prepare the backup because there might be uncomitted transactions that needs to rollback or transactions in the log to be replayed to backup. You need to mention –export option specifically to prepare backup in order to create table.exp and table.cfg files (prior to MySQL/PS 5.6). You can read more on it in documentation. You can prepare the backup as below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | irfan@source$ innobackupex --apply-log --export /root/partial_backup/ > /root/xtrabackup-prepare.log 2>&1 irfan@source$ cat /root/xtrabackup-prepare.log . . xtrabackup version 2.2.5 based on MySQL server 5.6.21 Linux (x86_64) (revision id: ) xtrabackup: auto-enabling --innodb-file-per-table due to the --export option xtrabackup: cd to /root/partial_backup xtrabackup: This target seems to be already prepared. . . xtrabackup: export option is specified. xtrabackup: export metadata of table 'irfan/test' to file `./irfan/test.exp` (1 indexes) xtrabackup: name=GEN_CLUST_INDEX, id.low=5043, page=3 . . xtrabackup: starting shutdown with innodb_fast_shutdown = 0 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 2453817454 141102 11:25:13 innobackupex: completed OK! |
Again a successfully prepared backup should show you “completed OK” at end. Once the backup is prepared it means it’s usable and ready to restore. For that first create the same table structure on destination as source.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | mysql [localhost] {msandbox} (irfan) > CREATE TABLE `test` ( -> `id` int(11) DEFAULT NULL, -> `name` char(15) DEFAULT NULL -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.12 sec) mysql [localhost] {msandbox} (irfan) > show tables; +----------------+ | Tables_in_irfan | +----------------+ | test | +----------------+ 1 row in set (0.00 sec) irfan@destination$ ls -la data/irfan/ total 116 drwx------ 2 root root 4096 Nov 2 16:29 . drwx------ 6 root root 4096 Nov 2 16:23 .. -rw-rw---- 1 root root 8586 Nov 2 16:29 test.frm -rw-rw---- 1 root root 98304 Nov 2 16:29 test.ibd |
Now discard the existing tablespace and copy the test.ibd and test.cfg which Percona XtraBackup produced after preparing the backup and import tablespace back from source to destination as illustrated below. As a side note, you may need to change the ownership of test.cfg file and test.ibd file to mysql in order to make it accessible from MySQL server.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | mysql [localhost] {msandbox} (irfan) > ALTER TABLE test DISCARD TABLESPACE; Query OK, 0 rows affected (0.10 sec) irfan@source$ cd /root/partial_backup/irfan/ irfan@source$ scp test.cfg test.ibd root@destination:/root/sandboxes/msb_PS-5_6_21/data/irfan/ mysql [localhost] {msandbox} (irfan) > ALTER TABLE test IMPORT TABLESPACE; Query OK, 0 rows affected (0.10 sec) irfan@destination$ tail -30 data/msandbox.err 2014-11-02 16:32:53 2037 [Note] InnoDB: Importing tablespace for table 'irfan/test' that was exported from host 'Hostname unknown' 2014-11-02 16:32:53 2037 [Note] InnoDB: Phase I - Update all pages 2014-11-02 16:32:53 2037 [Note] InnoDB: Sync to disk 2014-11-02 16:32:53 2037 [Note] InnoDB: Sync to disk - done! 2014-11-02 16:32:53 2037 [Note] InnoDB: Phase III - Flush changes to disk 2014-11-02 16:32:53 2037 [Note] InnoDB: Phase IV - Flush complete mysql [localhost] {msandbox} (irfan) > SELECt COUNT(*) FROM test; +----------+ | COUNT(*) | +----------+ | 1000 | +----------+ |
NOTE: The .cfg file contains the InnoDB dictionary dump in special(binary) format for corresponding table.The .cfg file is not required to import a tablespace to MySQL 5.6 or Percona Server 5.6. A tablespace can be imported successfully even if it is from another server, but innodb will do schema validation if the corresponding .cfg file is present in the same directory.
Now, as you can see from the error log, that table is imported successfully on test database and changes to innodb tablespace completed correctly. My colleague Miguel Angel Nieto wrote a related post on this titled, “How to recover a single InnoDB table from a Full Backup.”
There is another method to copy a table from a running MySQL instance to another running MySQL server which is described in the MySQL manual. For completeness let me describe to you the procedure quickly.
MySQL 5.6 Transportable Tablespaces with FLUSH TABLES FOR EXPORT
On source server, I have table named “dummy” which i will copy to destination server.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | mysql [localhost] {msandbox} (irfan) > show tables; +----------------+ | Tables_in_test | +----------------+ | dummy | | test | +----------------+ mysql [localhost] {msandbox} (irfan) > SELECT COUNT(*) FROM dummy; +----------+ | COUNT(*) | +----------+ | 100 | +----------+ |
First, create the same table structure on destination server.
1 2 3 4 5 | mysql [localhost] {msandbox} (irfan) > CREATE TABLE `dummy` ( `id` int(11) DEFAULT NULL, `dummy` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.07 sec) |
On the destination server, discard the existing tablespace before importing tablespace from source server.
1 2 | mysql [localhost] {msandbox} (irfan) > ALTER TABLE dummy DISCARD TABLESPACE; Query OK, 0 rows affected (0.00 sec) |
Run FLUSH TABLES FOR EXPORT on the source server to ensure all table changes flushed to the disk. It will block write transactions to the named table while only allowing read-only operations. This can be a problem on high-write workload systems as a table can be blocked for a longer period of time if your table is huge in size. While making backups with Percona XtraBackup you can manage this in a non-blocking way and it will also save binary log coordinates that can be useful in replication and disaster recovery scenario.
FLUSH TABLES FOR EXPORT is only applicable to Oracle MySQL 5.6/Percona Server 5.6 FLUSH TABLES FOR EXPORT and will produce table metadata file .cfg and tablespace file .ibd. Make sure you copy both table.cfg and table.ibd files before releasing lock. It’s worth mentioning that you shouldn’t logout from the mysql server before copying table cfg and table.ibd file otherwise it will release the lock. After copying table metadata file (.cfg) and tablespace (.ibd) file release the lock on source server.
1 2 3 4 5 6 7 8 9 | # Don't terminate session after acquiring lock otherwise lock will be released. mysql [localhost] {msandbox} (irfan) > FLUSH TABLES dummy FOR EXPORT; Query OK, 0 rows affected (0.00 sec) # open another terminal Need another session to copy table files. irfan@source$ scp dummy.cfg dummy.ibd root@destination-server:/var/lib/mysql/irfan/ # Go back to first session. mysql [localhost] {msandbox} (irfan) > UNLOCK TABLES; |
On destination server import the tablespace and verify from mysql error log as below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | mysql [localhost] {msandbox} (test) > ALTER TABLE dummy IMPORT TABLESPACE; Query OK, 0 rows affected (0.04 sec) $ tail -f data/msandbox.err 2014-11-04 13:12:13 2061 [Note] InnoDB: Phase I - Update all pages 2014-11-04 13:12:13 2061 [Note] InnoDB: Sync to disk 2014-11-04 13:12:13 2061 [Note] InnoDB: Sync to disk - done! 2014-11-04 13:12:13 2061 [Note] InnoDB: Phase III - Flush changes to disk 2014-11-04 13:12:13 2061 [Note] InnoDB: Phase IV - Flush complete mysql [localhost] {msandbox} (irfan) > SELECT COUNT(*) FROM dummy; +----------+ | COUNT(*) | +----------+ | 100 | +----------+ |
Take into account that there are some LIMITATIONS when copying tablespace between servers which are briefly outlined in the MySQL manual
Conclusion:
Transportable Tablespace is nice feature introduced in MySQL 5.6 and I described the use cases for that in this post. Prior to MySQL 5.6, you could backup/restore specific database tables via Percona XtraBackup’s partial backup feature (destination server should be Percona Server for this case). Comments are welcome 🙂
That’s great news! Nice write up. I had some hacking to do to import single table from mylvmbackup, it’s way easier to to manage backups with innobackupex and new functions in Percona Server 5.6
Nice article for MySQL DBA and also for our Customer who always prefer on Table recovery.
even you can restore table to the backup point, how to get the incremental binlog for this table?
Hi,
Could you please help me out how to take backup of database tables in mysql version ..?
Regards,
Mithun
Hello Muhammed
This is a realy nice feature. But it turns out it desyncs cluster members if you try to import tablespace to a running cluster.
To restore a table on a running cluster i have to stop the additional kluster members, do the import on one machine and then force a full SST when starting the slave nodes again, elsewise they will have a empty version of the database and a galera kluster that says it’s synched when it’s not.