Ubuntu Terminal: How to Quickly Create a SQL Dump File from a MySQL Database CodeUnit 05 JUL 2010

Backing up your MySQL database or generating a copy of it to shift around is quite a simple affair thanks to the powerful mysqldump command that ships with MySQL.

To generate a backup sqldump, simply execute:

mysqldump -h localhost -u [MySQL user, e.g. root] -p[database password] -c --add-drop-table --add-locks --all --quick --lock-tables [name of the database] > sqldump.sql

Note the lack of a space between -p and the password! Obviously if you don’t have a password assigned, simply omit the -p switch.

And that is it, all done! :)

Note, restoring a database from a mysqldump is as simple as: mysql -u [MySQL user, e.g. root] -p[database password] < sqldump.sql

Related Posts:

About Craig Lotter

Software developer, husband and dad to two little girls. Writer behind An Exploring South African. I don't have time for myself any more.

  • Just as a quick note — --opt replaces and automatically sets (by default, by the way) --add-drop-table, --add-locks, --quick, --lock-tablesand auto-adds --create-options and --set-charset (which is quite useful), so a rewrite would be:

    mysqldump -h HOST -u USER -pPASSWORD -c --opt --all DATABASE > sqldump.sql

  • Fantastic. Thanks for the heads up on the shortened form then Mr. Sven! :)