How To Backup MySQL Database Without PhpMyAdmin

Ever since my host upgraded my admin control panel to Plesk 8.3.0, I have been running into problem backing up large MySQL databases using PhpMyAdmin. For whatever reason, the export function will only generate 0 byte SQL backup file. Moreover, it’s becoming quite a chore to backup 9 databases spread across 7 domains — there’s a lot of clicking in Plesk and PhpMyAdmin to accomplish this.

Photo by Melete via Flickr

Backup MySQL Database Via SSH

So I did a little digging today and found an alternative that works really well. The solution is to backup your MySQL databases through SSH. Here are the steps.

• Enable shell access inside your Plesk control panel
• Using utility such as PuTTY, log into your server via SSH
• Change to the directory where you want to save your backup files — e.g., CD wwwroot/dbbackup. If you don’t have a directory created, follow these steps:
  • FTP into your server, using software such as FileZilla Client
  • Go to your domain root directory
  • Create a folder — e.g., call it dbbackup
  • CHMOD the dbbackup folder attribute to 777
• Using mysqldump command to export the database into a file on the server. For example:

mysqldump --add-drop-table -u db_username -p db_name > mybackup.sql

About the command:

• Omit the –add-drop-table argument if you’ll want to merge this backup with an existing database upon restore
• Replace db_username with the name of your database user
• Replace db_name with the name of your database
• Replace mybackup.sql with the name of your backup file

Note: You can find your database name, user, and password information in your wp-config.php file (if you are using WordPress).

• Enter your database password when prompted

That should be it. The command executes very fast and you should be able to FTP into your web site and find the backup file in the folder you created earlier. If you have multiple databases, all you have to do is replace the database name, user, password, and backup filename information.