You created a MySQL backup of a large server installation with dozens of databases and wish to get the schema and data for one of them. You now have to deal with a file of hundreds of MB in a text editor. How convenient.
Split a dump into several files
You can quickly split this dump in several files (one per database) with awk or csplit. With GNU awk (gawk on FreeBSD), this is a oneliner:
|
1 |
awk '/Current Database\: .*/{g++} { print $0 > g".sql" }' yourdump.sql |
Get database.sql files
To rename these files with actual database names, the following bash script could be useful. It assumes you don’t have the main dump in the same directory.
|
1 2 3 4 5 6 7 8 9 10 |
#!/usr/bin/env bash regex="Current Database: `(.*)`" for f in *.sql do DATABASE_LINE=`head -n1 $f` [[ $DATABASE_LINE =~ $regex ]] mv $f ${BASH_REMATCH[1]}.sql done |
2 Replies to “Split a large SQL dump by database”