Split mysqldump backup file into tables

I had to restore a large MySQL backup file. When restoring one of the table resulted in error. To debug the error, i wanted to split the MySQL backup taken using mysqldump into tables.

You can use csplit command to do this

csplit -s -ftable MYSQLDUMP_BACKUP_FILE_HERE "/-- Table structure for table/" {*}

1	csplit -s -ftable MYSQLDUMP_BACKUP_FILE_HERE "/-- Table structure for table/" {*}

This will generate files with name tableXX. First file table00 contains SQL commands used by all tables. Other files starting with table01 contains table related SQL file.

split mysqldump backup file

To rename the files into table name, run

for FILE in `ls -1 table*`; do TABLE=`head -n 1 $FILE | cut -d$'\x60' -f2`; mv $FILE $TABLE.sql; done

1	for FILE in `ls -1 table*`; do TABLE=`head -n 1 $FILE \| cut -d$'\x60' -f2`; mv $FILE $TABLE.sql; done

If you need to handle larger SQL file, there is a node.js project on github, that do the spliting of mysqldump file into tables.

https://github.com/vekexasia/mysqldumpsplit

See MySQL

Leave a Reply Cancel reply