Performing a MySql Backup Via Command Line

A few months back I was running backups of the ol' web server and realized that it was going to be a pain since I was starting to host more and more websites. Logging into phpMyAdmin and exporting every database can be a bit cumbersome when you have a lot of databases. With that, I wanted a good solution for dumping my entire database server. Thankfully, the solution is a really simple one (if you have console access). Many thanks to the MySql devs for creating this feature (as if they wouldn’t what good is a database you can’t back up after all).

As I mentioned, this is really simple. To export all of your databases that you have running, including create statements, run the following command…

mysqldump -u root -p --all-databases > /tmp/dumpfile.sql

So here’s what we just did.

  • -u root specifies the user. In this case, root is who we are logging in.

  • -p makes it ask for a password (it will try to log in without using a password if this isn’t used)

  • --all-databases makes it export all databases (duh)

Ordinarily, this command outputs to the console. It’s not the most useful thing in the world unless you use the greater than. The > makes it write that output to the specified location rather than to your console window.