Mysqldump is an utility provided by MySQL/MariaDB to backup databases and tables. Generally, we take a full dump of database regularly and keep it. But some times we required to recover a single or 2-3 tables from backup. Then the question if how to restore a specific table from the full backup file. This article will help you to how to extract tables backup from the full database backup file. Also, we can extract all tables backup in individual files per table.
Step 1 – Download MySQL Dump Split Script
To split a full mysqldump database backup file in separate table specific files backup. Create a file named splitDB.sh and Copy below script in it.
This script taken from https://gist.github.com/jasny/1608062.
Step 2 – Extract All Tables from Dump
For this example I have a dump file named mydb.sql, which i want to split in small backups per table. For this I have created a new directory /opt/splitdb and copied above script with name splitDB.sh in this directory. Now use following command to extract all tables in individual backup files.
cd /opt/splitdb chmod +x splitDB.sh ./splitDB.sh mydb.sql #Do not run with 'sh' command
Step 3 – Extract Single Table from Dump
If we want to extract only one table, We can use command like below. For example I want to split only table named my_tbl1 and my_tbl2. it will extract backup in current directory with name my_tbl1.sql and my_tbl2.sql.
cd /opt/splitdb chmod +x splitDB.sh sh splitDB.sh mydb.sql my_tbl1 sh splitDB.sh mydb.sql my_tbl2