Sunday, January 16, 2011

Backup MySQL to a file per database

The objective is to make a backup of a MySQL database server, and end up with a file for each database, named based on the database.

This is part of a server migration where not all of the databases will be created on the target host, and some will be renamed as they are moved to the new host.

It turned out to be a single line of shell to do the task:
mysql --user=root --password=password --batch --skip-column-names --execute 'show databases' |
while read x ; do
 echo "dumping $x..."
 mysqldump --user=root --password=password --all "$x" > "$x".sql
The script was run in the target directory for the backup files.

It was also revealed that one of the databases had some corruption during the backup run, which was fixed with a quick invocation of the mysqlrepair command.

No comments:

Post a Comment