Backup and Restore a MySQL Database

Web hosting is cheap and expensive. Cheap because you can get a small VPS on DigitalOcean for $5 a month and expensive because when you use a VPS you either need to spend time or money to keep essential services like email running. I’m in the process of moving some sites off of MediaTemple and on to Linode and DigitalOcean to save some money. Part of the process requires moving databases around. So, for my own personal future reference and anyone else who may want to bookmark a refresher, here’s how to backup and restore a MySQL database and move it between hosts.

Backup a Database

This guide assumes we’re all using MySQL 5 and have shell access. Before someone asks, I’ll tell you now that the same thing applies to both local MySQL on your own computer and on a server. Locally, things may be different depending on if you use MAMP, XAMPP, or something similar but if you have MySQL installed “natively” (for lack of a better word) then you’re good to go here.

Log in to your server – A quick ssh will get you right in.

Drop into MySQL – Another quick mysql -u username -p<password_here> will get you in. I usually do backups as root so my command would be mysql -u root -psomepasswordhere (note that there’s no space between the -p and the actual password you use to log in)

Get a list of databases – You might know which one you want to backup. I don’t. I need a list so I can get them all. Run show databases; to get a list of available databases. The “information_schema” database can be ignored. You’ll get a list that looks a lot like this:

| Database            |
| information_schema  |
| my_first_app        |
| another_database    |
| some_wordpress_site |
7 rows in set (0.00 sec)

Those are your databases. Now its time to choose the ones you want to back up.

Start backing up – Now that you have a list of your available databases it’s time to take a note of the names of the ones you want to back up. Once you have your list, exit the MySQL prompt with Ctrl + D

Now that you’re back in your regular shell, navigate to your home folder or whichever folder you want to save your backups to. I use my home folder, create a folder for the backups, then use scp to download my files from there. Here’s how we do this:

  • cd ~/ will bring you home
  • mkdir db_backups && cd $_ will create a ‘db_backups’ folder and drop you into it
  • mysqldump -u root -pexamplepassword1 my_first_app > my_first_app_backup.sql creates a database dump of your “my_first_app” database at ~/db_backups/my_first_app_backup.sql

Just repeat those steps for each of your databases and you’re all set. Remember to be smart about naming the database dumps so they don’t overwrite each other and you’re able to tell at-a-glance which one is which.

Restoring databases

For each of the SQL files you’ve created just run this one simple command for each of them:

mysql -u root -pexamplepassword1 name_of_database_to_restore < my_backupfile.sql

Boom. Done. Simple. Enjoy.

A word on hosts and the reason for the switch

I like MediaTemple. It’s where I first got started with “big boy” web hosting. MediaTemple runs a premium web hosting service. They provide equivalent or better services than any other provider but you often end up paying more than you would elsewhere. The first and only service I ever bought was their (gs) Grid hosting service. That’s their fancy name for shared web hosting. I run a VPS on DigitalOcean for a quarter of what Media Temple’s shared hosting costs but at the time, and arguably still today, Media Temple’s shared offering is far beyond what you can get at Hostgator and other companies. While the Hostgators of the world are selling you a cheap service that works, Media Temple is all about the details. Their admin interface is (mostly) a joy to use (compared to other hosts), their uptime and stability are way above average, support is responsive, and they have lots of features you don’t get elsewhere. My (gs) service had SSH access and supported Git on the command line. I was able to host my web design company’s clients on it too at times. You can host 100 domains, 100 MySQL databases, and 100 email addresses per domain all for $20/month. I ended up using it for DNS and email hosting for a lot of my own personal sites and for clients.

My point here is that Media Temple is a great web host. My issue is that I’m able to run my own infrastructure for cheaper now. I’ll be spending a little more time but it’s worth it to pay $5 a month for a couple of email hosting accounts at Rackspace and to have the rest of my websites and DNS routed through a Linode or DigitalOcean Droplet.

Databases, Server administration, Web development

« Quick and Dirty Option Parsing in Node (or Why Eval() is Not Evil) Apache 2.4 and 500 Errors in htaccess »