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.
Add a comment
|
1 Answer
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.sqlYou 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
--optflag 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.
-
And to cover the second part of the question, you load that file by passing it to mysql: mysql -u user -ppassword < dumpfile.sql– RoyDec 6, 2009 at 22:39
-
2As 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.– andolDec 6, 2009 at 22:55