Most who use MySQL have a nifty program called PhpMyAdmin. There is a good chance that if you are a user of that program, you’re here because you tried to backup or restore a large database through the web based interface and failed. This is a common situation, lucky for us there’s a very easy, fast, and reliable way to do this through the command line! If you’re a Linux user who has SSH access, follow the steps below.

Backing up the Database
Fire up SSH and type the following. Replace USER with your MySQL username, DBNAME with the name of the database you want to back up, and /path/to/backupname.sql to where you want the database backup file to be saved (directory must exist).

You’ll be prompted for your MySQL password. It’ll get to work after you type that in and press enter, it may take a few moments (or longer if your database is really large).

When you’re returned to the command line you now have a backup file where you specified above. This file is a complete copy of the database at the time you ran the command, the second your MySQL table is updated your backup will be out of date. I suggest you take backups at regular intervals depending on your situation (I will write a guide on how to automatically do backups one day).

Restoring Your Database Backup
Follow these 3 commands to restore your database easily.

First we need to drop your current database so there are no conflicts when we’re restoring

Now we’ll remake the database, because it doesn’t exist anymore

And finally, we’ll populate the fresh new database with your backup file.

The variables you should change above are pretty straight forward. If you get errors importing your data, it’s most commonly because your max_allowed_packet isn’t set high enough in /etc/my.cnf - if you have root access, you can go in and make that variable higher. If you don’t have root access, seek support from your web host.