Taking (backing up) and restoring MySQL dump files

[Jamie Edwards]

Via SSH / MySQL CLI command interface:

Dumping a database into a file:

mysqldump -u username -ppassword databasename > dumpfilename.sql

The above command will dump the contents of databasename into a MySQL dump file named dumpfilename.sql that can later be used to drop back into a MySQL database. The username you give must be for an account that has the correct access privileges to this database.

The file will be saved relative to the location in which you executed the command. So, if you execute the command whilst in the /home/ folder, the dumpfile will be saved to /home/dumpfilename.sql.

Note: If you add the parameter "--opt", it will create the database with options to drop existing tables and data when the dump file is imported into a database.


Restoring a MySQL dump file:

mysql -u username -ppassword databasename < dumpfilename.sql

The above command will drop the contents of the dump file dumpfilename.sql into the database named databasename. Normally, tables will be overwritten using this procedure if they already exist.

[bear]

If you add one thing to that "--opt", it will create the database with options to drop existing tables and data on restore, avoiding errors and complaints from mysql.

mysqldump --opt -u ....

Also, the -p must be followed immediately by the password, no spaces. It would see the space as part of the password and reject it.

[Jamie Edwards]

Thanks for the suggestion, updated!