Backing up and restoring all MySQL databases

This is another one of those “lest I forget” type of blog entries. There are times one has to migrate all MySQL databases to another box (like now when I have to give my Mac for repair and I have to work in a Windows machine!) This is the command to backup all MySQL databases

mysqldump –all-databases -u [username] -p -C > alldatabases.sql

Replace [username] with the name of the MySQL user. I used “root” as it has access to all databases. This will create a file called “alldatabases.sql” containing all the SQL commands for creating the databases and inserting data into them. To restore from this SQL file, use this command

mysql -h localhost -u [username] -p < alldatabases.sql

Now, I won’t have to rummage through the big wild web for this!

Leave a Reply

Your email address will not be published. Required fields are marked *