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. As such, I had to look for and easy way to backup large MySQL database that doesn’t require PhpMyAdmin.
Photo by Melete via Flickr
Backup MySQL Database Via SSH
So I did a little digging today and found an alternative that helps you easily backup large MySQL databases. 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.
{ 3 trackbacks }
{ 1 comment… read it below or add one }
I am having similar problem. Thank you for sharing this trick. It works perfectly!