The Tech Bench: 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 -Q –add-drop-table your_database_name > file.sql
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 -e ‘show databases’ | grep -v Database` ; do mysqldump -Q –add-drop-table $db > /root/database_backups/$db.sql && echo $db >> /root/database_backups/list-of-databases ; done
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.
Restore a single database from command line
If you are logged in as root, simply run:
mysql 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 -p -u 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 $db < /root/database_backups/$db.sql ; done
Once that finishes, you’ll have all your exported databases fully restored back as they once were: Fast, safe, and easy!
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.
The Tech Bench is an ongoing blog series featuring the answers to common questions the ServInt MST fields everyday. You can also find more great tech tips in the ServInt KnowledgeBase.