MySQL - dumping and restoring

From WPKG | Open Source Software Deployment and Distribution

To dump all databases:

# mysqldump --all-databases --opt  --password=MySQL-password > all_dbs.sql

To split 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

To dump one database:

#  mysqldump --opt  --password=MySQL-password DATABASE_NAME > database.sql

To restore it:

# mysql -p DATABASE_NAME < database.sql