How to Backup and Restore MySQL Databases

One of the easiest ways to backup and restore your MySQL database (or even just to move databases between different servers) is to make use of MySQL’s handy little mysqldump functionality.

Note: mysqldump works for both MyISAM and InnoDB tables, so you don’t need to worry about that little detail.

To Backup/Export/Dump a MySQL Database

First off, locate the mysqldump.exe file on your system (it should be in the bin folder of your MySQL installation) and then open the command prompt from that directory. Enter:

mysqldump -u username -ppassword database_name > dump.sql

where username is a valid MySQL user ID and password the valid password for that specified user (NOTE: there is no space between the -p and the password. If you leave a space in there, mysqldump will prompt you for a password and treat your entered password as a database name!). database_name is the name of the actual database that you wish to export and lastly, dump.sql is the name that you have chosen for your generated output SQL dump file.

Executing the application with the above parameters will result in the data, tables and structure of the specified database being backed up into a SQL text file which carries the name you specified at the end of the pipe command. (If you look closely, all you are doing is a pretty standard command line out (or result) redirect.

If you only wanted to save the actual table structure (i.e. you no longer need the data) you can add the –no-data switch into your application call. The switch comes before the database_name, meaning your application call would now look like this:

mysqldump -u username -ppassword –no-data database_name > dump.sql

Conversely, if you wish to only save the data but not the table structures, use the –no-create-info switch.

The –databases option allows you to specify more than one database, meaning that your application call would now look something like this:

mysqldump -u username -ppassword –databases database_name1 [database_name2 ...] > dump.sql

Should you however want to do a complete MySQL dump including all of the databases currently housed in that instance, you can make use of the –all-databases option which forces mysqldump to simply export everything it comes across (which also means that you don’t need to manually specify the database names which could become fairly tedious if you’ve got quite a lot of databases for example!).

mysqldump -u username -ppassword –all-databases > dump.sql

(NOTE: While mysqldump is performing its export function, the entire database acquires a global read lock, meaning that while READ statements can proceed, all INSERT, UPDATE and DELETE statements will be queued until this lock is released. If you’re using InnoDB however, you can minimize this impact by using the –single-transaction to force the system to systematically release the lock as it finished up with a section of data.)

Now Restoring/Importing the SQL dump back into MySQL

In a similar fashion to which you had dumped the data out of MySQL, to read it all back in you simply make use of the bundled mysql.exe application that is also located in the bin folder of your MySQL installation’s home directory.

So to import, your command line call now looks like this:

mysql -u username -ppassword < dump.sql

Pretty simple, you see? (You can of course extend on this by employing the various switches which mysql allows for, more of which can be read about by entering: mysql –help)

Oh., and one last extra: One thing to take into account when moving databases and tables across is to ensure that the character set encoding remains constant across the move. By default the older versions of MySQL used Latin1 as its character set of choice, whilst newer installations tend to use UTF8. There are however switches to help deal with these issues, more about which can be found by employing the –help application switch.