Backup your MySQL databases manually with mysqldump

I often need to make a quick backup of one mysql database. This happens right before there will be some major changes to the database, or before performing an upgrade on the mysql server software. Now I don’t recommend this to be done regularly, but still, I use this extensively in this kind of situations… If you are looking for an automated solution to backup your databases you might want to check my other post: “Backup your MySQL databases automatically” MySQL provides us the tool required to do this: mysqldump. As the name implies, this can make a dump of one database or even to all the databases on the server. You can see all the options on its help manual or on the mysql site.

Basically this is used like:

mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
mysqldump [OPTIONS] --all-databases [OPTIONS]

based on the fact that you need to backup one database, more than one, or all the databases. All the available options are well documented, so let me show you just a simple example how to backup and restore a single database. Let’s say that the database is called ‘wordpress’ ;).

To backup this database and save it in a file we run:

mysqldump wordpress > wordpress.sql

I am not using any other options; just create a dump of the database.

Now let’s assume that we need to restore this backup. How do we do this? Well as simple as this:

mysql wordpress < wordpress.sql

This will run all the backup file against the existing database as a set of mysql commands (that is what the backup file really is). Note: this will delete all the existing data in the tables of this database and replace them with the data from the saved file.

If you want to save all the databases existing on the server:

mysqldump -A > all_dbs.sql

Now, in the example from above I have assumed that you are able to connect directly to the mysql server, without the need to enter any other parameters (if you have configured properly your .my.cnf, or if the details are the default ones: localhost, root, no password - not recommended… you should have a password!). If this is not the case, add to the command line the usual mysql parameters (the ones that are needed of course):

-h hostname -u username -p password

and the command will look like this:

mysqldump -h hostname -u username -p wordpress > wordpress.sql

This covers the basic usage of saving and restoring one mysql database with mysqldump. Let me know if you are using mysqldump in a special way and if you want to share this with other peoples.