All About Web Hosting

I suspect that most people who work with MySQL database use PHPMyAdmin to interact with their database, and so may not know about how handy the command line program mysqldump is both for one-time backups and for regular use.

I believe that mysqldump comes with the regular installation of mysqldump (at least on linux servers) so it should be available, assuming you have command line access to it, etc.

Using mysqldump is pretty straightforward – the basic command is:
mysqldump [database name]

Which will output the SQL to recreate that database. Obviously, watching all that code stream by on the console is not terribly useful, so we need to dump it to a backup file – for example:
mysqldump [datbase name]  > [backup filename]

The “>” directs that output to the file.
The most common complication of this is the need to send passwords, usernames, etc. That’s easy enough as well. This command adds username, password and host:
mysqldump –user [username] –password –host [host name/IP] [database name] > [backup filename]
When run, mysqldump will ask for the password before proceeding.

If you only need a backup for one table, you can just add the table’s name after the database name in the command, and mysqldump will only dump the SQL for that table.

Then, if you need to recover a database from a dump, it’s pretty easy as well:
mysql [database name] < [backup filename]

You may need to add –user, etc to that command (as with the mysqldump example) to connect.

Hope that’s helpful to someone!