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.

Tags: , , , , , , , , ,

5 Responses to “How To Backup MySQL Database Without PhpMyAdmin”

  1. Tom says:

    I am having similar problem. Thank you for sharing this trick. It works perfectly!

  2. […] Una solución simple para realizar un backup de bases de datos MySQL grandes es a través de SSH. Necesitarás permitir el acceso Shell dentro de tu panel de control Plesk y usar una utilidad como PuTTy para ingresar en tu servidor por medio de SSH. (Más info) […]

  3. […] Copias de seguridad mediante SSH. Si tienes una gran base de datos a través de programación tendrás problemas de tiempo de espera (timeout) por lo que hacer las copias a través de SSH es una buena opción. Para ello debes entrar a la consola con algun cliente ssh como PuTTy o SecureCRT en Windows, o el comando ssh en Linux o Mac. (más información). […]

  4. Jon Horton says:

    Thank you! I’ve been having problems backing up a 17+ MB database with phpMyAdmin, and was getting nervous since it’d been so long since I last backed up. It worked perfectly on the first try!