Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How do I backup a database to a single SQL file? After it's backed up, how can I load it/restore the database with InnoDB? It must be InnoDB.

share|improve this question

Backing up MySQL

MySQL backups are performed using the common mysqldump tool. This is a command line utility that ships with MySQL and you use at as follows:

% mysqldump --user=user --password=pass --opt DBNAME > dumpfile.sql

You may also need to specify the --host= parameter to force the hostname you are connecting to. This depends largely on how you've setup your user security. This will produce a text file with a series of INSERT/DROP/CREATE SQL statements that will recreate the database.

The --opt flag is very important. This is shorthand to pass in many flags at once; --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. This ensures that your database is in a good state while the backup is performed, including restricting all write access while the backup is in operation. Any locks placed will be automatically removed when this utility finishes.


share|improve this answer
And to cover the second part of the question, you load that file by passing it to mysql: mysql -u user -ppassword < dumpfile.sql – Roy Dec 6 '09 at 22:39
As fretje points out, mysqldump is probably the tool you want to use. Would just like to add a couple of details. 1) Specifying as password in the command line is not always optimal security wise, as there are scenarios where it might be picked up in the process list. A safer option is to put the password in a properly protected config file, and point mysqldump towards it using --defaults-file= 2) In case you are going to run the backup job regularly you might want to create a dedicated backup user. In simple cases it should only need the permissions SELECT and LOCK TABLES. – andol Dec 6 '09 at 22:55

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.