Backing Up and Restoring MySQL Databases

The best advice I’ve ever heard is: if your data is important to you at all, then you should back it up. What would happen if you suddenly lost it? How devastating would it be? Backing up is such a simple step, and can potentially save you many hours of work, stress, and possibly your job (if the data is that important).

Even if your web host backs up your data, you can never have too many backups.

To backup a database, we simply need to export the database. To restore it, we need to import the database. I’ll show you how to do both using a few different methods.

Backing up / Exporting

Backing up a Single Database from within cPanel

In cPanel, you can download a copy of your database by simply logging into cPanel and clicking on the ‘Backup’ icon.


Then click on the link for your database name. It will prompt you to download your database in a compressed archive (tar.gz) to your local computer.


Backing up a single database with command line

If you prefer the command line and SSH, you can backup your database using a single command:

mysqldump -u root -p your_database_name > file.sql

Don’t know how to log into your server via SSH?

Backing up all databases to separate files

If you would like to play it safe or know that you have many different databases that are all important, then you can run a ‘for’ loop to individually dump all databases to their own file:

First, create a directory where all the dumps will go and create an empty list of all the database names:

mkdir /root/database_backups ; touch /root/database_backups/list-of-databases

Then, run the ‘for’ loop to dump the databases and generate the list of database names:

for db in `mysql -u root -p [password] -e 'show databases' | grep -v Database` ; do mysqldump -u root -p [password] $db > /root/database_backups/$db.sql && echo $db >> /root/database_backups/list-of-databases ; done

*where [password] is the root password for that database.

Can’t find your database password?

Restoring / Importing

In the event that you’ve got missing data, corrupt tables, or generally something went wrong, don’t worry! You have backups that you can restore from. Here’s how you restore databases from the backups you took above.

Restoring from phpMyAdmin

First, you’ll need to log into phpMyAdmin. From cPanel, it is found in Databases section.


Once inside, you’ll select your database on the left-hand side. This is the database that you want to import that data into.

**Warning: This will overwrite your existing tables.**

Once you have the database selected, click on the ‘import’ tab at the top.


Click ‘Browse’ and navigate to the file on your local computer.

Look over the various options. Most times, you can just leave them as is. If you don’t know what the options are, I’d advise just to leave them set to default.

Hit ‘Go’.

Restore a single database from command line

If you are logged in as root, simply run:

mysql -u root -p db_name < file.sql

Make note of the direction that the arrow (greater-than sign) is pointing. This indicates the direction where the data is going. In this case, the arrow points to the left towards the MySQL database name. This indicates that the data is being imported from the .sql file and to the server’s MySQL database, which is exactly what we want.

If you don’t have root access, you can still import, but the command requires a couple more flags and for you to know the database credentials:

mysql -u root -p db_username db_name < file.sql

It’ll prompt you for that user’s password. Enter it in and the import will begin.

Restore all databases from command line

Remember that command we did to export all databases to their own separate dump files? Well, lets say you wanted to easily import all of them back in for whatever reason. We just need to run another simple ‘for’ loop in order to get them back in. Basically, we are doing an individual database import, but for each database that is in that ‘list-of-databases’ that we generated earlier:

for db in `cat /root/database_backups/list-of-databases` ; do mysql -u root -p [password] $db < /root/database_backups/$db.sql ; done

*where [password] is the root password for that database.

Once that finishes, you’ll have all your exported databases fully restored back as they once were: Fast, safe, and easy!

Final thoughts

If you have a huge database, running a mysqldump can cause your entire website to go down. This is because you are locking the tables during the dump (cPanel backups can do the same thing). If you are worried about this, be sure to perform your backup export during a time of low site traffic. With that said, SQL backups are a great idea. An equally good idea is to occasionally try to restore your backups (to make sure they are good). Simply restore them to a different database name if you don’t want to overwrite a running database.

Find out more about ServInt solutions

Starting at $25

  1. There are many ways to backup MySQL database. I have written an article on top 5 five ways to back & restore MySQL database. Here it is
  2. We do backups before any change in the database but unfortunately one occasion, our numerous backups were not helpful to correct the problem and our host (which has been ServInt since 2007) had to run the MySQL repair script on our behalf to resolved the problem or nine years worth of data would be down the drain.
    Steve Pringle /
  3. [...] one I had written for the ServInt blog as part of the 'Tech bench' series. You can view it on the ServInt blog here. They are using my article with my permission. backup, export, import, mysql, mysqldump, [...]
    Backup/Restore MySQL databases | /
  • Hosting Advice
  • Computer World
  • Ars Technica

  • The New York Times
  • The Seattle Times
  • Bloomberg
  • The Hill

To engage with the ServInt Sales Team use the following chat icon. Normal sales hours are Monday-Friday 9am-5pm EST but feel free to leave a message and we will follow up as soon as possible.

Sales Chat

To engage with the ServInt Support Team you must be logged into our Customer Portal for identity verification and have a ticket opened about your request or there will only be limited support offered.

Support Chat