MySQL: split whole database dump into separate databases

From lxadm | Linux administration tips, tutorials, HOWTOs and articles
Jump to: navigation, search

If you have dumped all MySQL databases into one file (mysqldump --all-databases), you may have issues restoring a single database from that file.


You can use awk to split the whole database dump into separate databases.


  • first save this script
 cat $1 | awk 'BEGIN { outfile = "preamble"; x = 1 }
 $0 ~ /^-- Current Database/ {
    close(outfile); x = x+1
        outfile = x"-"substr($3,2,length($3)-2); }
        { print $0 >> outfile }'


  • and then run it against all_dbs.sql file:
# mysql_split all_dbs.sql