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.
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 32 33 34 35 36 | #!/bin/bash #### # Split MySQL dump SQL file into one file per table # based on http://blog.tty.nl/2011/12/28/splitting-a-database-dump #### if [ $# -lt 1 ] ; then echo "USAGE $0 DUMP_FILE [TABLE]" exit fi if [ $# -ge 2 ] ; then csplit -s -ftable $1 "/-- Table structure for table/" "%-- Table structure for table `$2`%" "/-- Table structure for table/" "%40103 SET TIME_ZONE=@OLD_TIME_ZONE%1" else csplit -s -ftable $1 "/-- Table structure for table/" {*} fi [ $? -eq 0 ] || exit mv table00 head FILE=`ls -1 table* | tail -n 1` if [ $# -ge 2 ] ; then mv $FILE foot else csplit -b '%d' -s -f$FILE $FILE "/40103 SET TIME_ZONE=@OLD_TIME_ZONE/" {*} mv ${FILE}1 foot fi for FILE in `ls -1 table*`; do NAME=`head -n1 $FILE | cut -d$'x60' -f2` cat head $FILE foot > "$NAME.sql" done rm head foot table* |
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
4 Comments
with debian change:
NAME=`head -n1 $FILE | cut -d$’x60′ -f2`
to:
NAME=`head -n1 $FILE | cut -d $(echo -e ‘\x60’) -f2`
this worked for me …
I got this error :
cut: the delimiter must be a single character
Run the script with ‘bash’ command instead of ‘sh’. The tutorial also updated accordingly.
while running this script it show error
splitdb.sh: line 14: ct_olts.sql: command not found
cut: the delimiter must be a single character
Try ‘cut –help’ for more information.