How to Copy (Backup) a MySQL Database

MySQL databases are tricky files to copy - you must be careful with them. However once you understand the issues and have procedures in place you shouldn't experience any problems. MySQL is actually designed to make backing up a smooth and reliable process.

Simple Copy

The easiest method is to simply copy the binary database files. However this may create problems and is not the recommended copying method. For example, the different ways of handling case-sensitivity between Unix and Windows means that a database copied from one system to the other may become corrupt.

Using the mysqldump Command

The mysqldump command creates a text version of the database. Specifically, it creates a list of SQL statements which can be used to restore/recreate the original database.

The syntax is:

$ mysqldump -u [uname] -p[pass] [dbname] > [backupfile.sql]

[uname]	Your database username
[pass]	The password for your database
[dbname]	The name of your database
[backupfile.sql]	The filename for your database backup

You can dump a table, a database, or all databases.

To dump all MySQL databases on the system, use the --all-databases shortcut:

$ mysqldump -u root -p --all-databases > [backupfile.sql]

Restoring a MySQL Database

Use this method to rebuild a database from scratch:

$ mysql -u [username] -p [password] [database_to_restore] < [backupfile]

Use this method to import into an existing database (i.e. to restore a database that already exists):

$ mysqlimport [options] database textfile1

To restore your previously created custback.sql dump back to your 'Customers' MySQL database, you'd use:

$ mysqlimport -u sadmin -p pass21 Customers custback.sql