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. 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.

Get Free Email Updates
Like this article? Enter your email address and click "Subscribe" to get the latest articles sent to your email for free. Your email will only be used for this daily update and you can unsubscribe anytime.

* Subscribe via RSS now

Related Tags
backup, FileZilla, FTP, MySQL, mysqldump, PhpMyAdmin, Plesk, PuTTY, shell, SSH

Pinyo is the owner of Moolanomy: personal finance blog and Blogthority, and founder of the M-Network: personal finance blogs network.

All posts by Pinyo

Please share your comment:

3 blogs that link to this article:

If your trackback does not show in 24 hours, please resend to this trackback URI.

1. 10 Ways to Automatically & Manually Backup MySQL Database | Noupe
2. 10 opciones para un backup rápido de tu Base de Datos de MySQL - elWebmaster.com
3. Opciones para copias de seguridad de MySQL, Carrero