Introduction
In MySQL, restoring a full backup is relatively straightforward. But what if you only need to restore one specific table from a backup without touching the rest of your data?
This situation often arises in real-world production environments. For example, a developer might accidentally delete or update all the rows in a critical table. Or perhaps you need to roll back a specific table to a previous state for auditing, testing, or recovery purposes, without affecting the rest of the database.
That’s where Percona XtraBackup becomes a powerful tool. While XtraBackup is widely known for creating full and incremental backups of InnoDB databases without locking the database during the process, it also provides a lesser-known but handy feature: the ability to restore a single table using the — export option.
This blog post will guide you through:
- Creating a full backup with XtraBackup.
- Simulating data loss on a single table.
- Using DISCARD TABLESPACE and IMPORT TABLESPACE commands.
- Restoring only the .ibd and .cfg files of a single table.
You’ll learn not only the exact steps but also the why behind each command, giving you a complete understanding of single-table recovery using XtraBackup in a MySQL environment
If we already have a sample table inside the database, it’s not necessary to create a new one; otherwise, we need to create it for our usage. First, we need to create a table inside the test database we have:
1) FULL BACKUP
xtrabackup– backup– user=root –password=’mysql2′ –socket=/home/rajseharann/MYSQL/mysql/mysql8.sock –host=localhost –target-dir=/home/rajseharann/MYSQL/mysql/bin/base
- xtrabackup– backup: Runs a full backup of the MySQL database.
- –user=root: Connects as the MySQL root user.
- –password=’mysql2′: Uses the password ‘mysql2’ for authentication. (It’s recommended to use a secure method instead of plain-text passwords.)
- –socket=/home/rajseharann/MYSQL/mysql/mysql8.sock: Specifies the MySQL socket file for connection (useful for local MySQL instances running on Unix sockets).
- –host=localhost: Connects to the MySQL server running on localhost.
- –target-dir=/home/rajseharann/MYSQL/mysql/bin/base: Stores the backup files in the specified directory (/home/rajseharann/MYSQL/mysql/bin/base).
In the below screenshot, if the full backup has been completed successfully, we will get a message like completed OK! And by using ls -lrth, we can see the list of files generated by taking a full backup.
After taking a full backup, if we create a table to include it in the backup, also check inside the data directory under the test database whether the team.ibd file is present or not; by default, it should be available.
For example, in the team table, we deleted all the records using the command shown in the screenshot. We are manually creating an issue to work on this restoration of the single table option using — export.
2) need to prepare backup using “–export”
— export: This option is used to restore a particular table
xtrabackup –prepare –export –user=root –password=”mysql2″ –socket=/home/rajseharann/MYSQL/mysql/data/mysql8.sock –host=localhost –target-dir=/home/rajseharann/MYSQL/mysql/bin/base
If we restore all the records in the server, we can use –copy-back / –move-back, but to restore a single table, we need to use the –export option.
After we executed the command, as we saw .cfg extension file was additionally generated.
cfg: used to do the schema validation(yes restore can be done without this, but warning will generate)
3) Then need to perform ALTER…..DISCARD to remove the tablespace
Alter table <tablename> discard tablespace (it will remove the tablespace (.ibd) By executing this inside the MySQL server, the team.id(InnoDB .ibd files) file gets removed tablespace from the data-directory because in the running production server, its not possible to use the rm option for removing the file.
Before MySQL 8.0: The table definition is stored in a .frm file. employees.frm (table definition).
MySQL 8.0 and later: The table definition is stored in the MySQL data dictionary inside the mysql.ibd system tablespace. employees.ibd (tablespace for data).
4) Then copy/move the .ibd & .cfg to the MySQL data directory inside the schema directory
cp /home/rajseharann/MYSQL/mysql/bin/base/test/team.ibd /home/rajseharann/MYSQL/mysql/data/test/
cp /home/rajseharann/MYSQL/mysql/bin/base/test/team.cfg /home/rajseharann/MYSQL/mysql/data/test/
After copying the file, we just need to change the owner permission of the files using
chown -R mysqlp:mysqlp base : it’s used to change the user permission for the directory base
After changing the owner permission, log in to the MySQL server and check if the table has values or not. It will show like it’s mentioned in the screenshot, it will throw error 1814: tablespace has been discarded for table ‘team’. Then we need to execute this import command to make the records get imported into the table.
Alter table <tablename> import tablespace
Conclusion
Restoring a single table in MySQL doesn’t have to be complicated. With the help of Percona XtraBackup and the right steps, we can bring back just the table we need, without affecting the rest of the database.
In this guide, we covered how to:
- Take a full backup
- Prepare it using– export
- Remove the damaged table’s tablespace
- Copy back the necessary files
- Import the tablespace to complete the restore
This method is very useful when only one table is lost or needs to be rolled back, especially in large databases where a full restore would take too much time.
Always test the steps in a safe environment before doing it in production. And remember to keep regular backups—just in case.
Reference link