A member asked today how to backup and restore his database. I suggested that he watch the videos in Lesson 14 of the Start Building Your Website Here tutorial series. In those videos I demonstrate a couple of different methods of backing up both your site files and your databases. He said that he had watched them but that his host (Dream Host) doesn’t use C-Panel and thus doesn’t have the easy-breezy “manual” backup and restore system that I show in those videos.
In that case you need to be able to backup and restore your database the old fashioned way. It sounds scary and esoteric but it isn’t. It’s quite simple. The tool we will use for this is called “phpMyAdmin” and it is a database management tool that all linux based hosts are going to have. It’s a very powerful tool but today I’m just going to show you the basics of downloading a copy of the database, renaming it and restoring it from a backup.
How to Manually Backup and Restore WordPress Database Using phpMyAdmin
I have demonstrated a backup and restore of your WordPress website in a couple of different places in these tutorials. But one thing I have not done is show you how to backup manually using phpMyAdmin. All of those backup systems were based on using cPanel and if you have a host like Dream Host, or GoDaddy, or I Power, my little method for backing up your system doesn’t work.
The purpose for this lesson is to show you how to manually backup and restore your database using phpMyAdmin which everybody has even if they don’t use cPanel for their customer management.
Locate Your Web Host’s phpMyAdmin
Let’s go over to BlueHost, because that’s who I’m with for hosting, and log in. In BlueHost this is the cPanel configuration. If you don’t have a cPanel you want to look around and try to find the place where you can see this phpMyAdmin. This is the tool that we’re going to use to manually backup our site.
Select phpMyAdmin and it brings up the login screen. You enter your username and your password then select Go and that brings up the screen phpMyAdmin. You can see here that I have eight databases. This test was the one that I used to do a dry run of this tutorial. But I have eight databases here. Four of them are based on the Start Building Your Website Here tutorials and five through seven are for the E-Commerce tutorials.
Save Database as SQL
First, we’re going to backup the ecom5 database. Come over to Export and you can select ecom5 as the one I’m going to save. I want to save it as an SQL. You could actually save all of your databases at the same time if you wished but I think that’s kind of problematic. I’m going to go ahead and save the Comments, Under Structure I want to add, if none exists, an Auto Increment and Enclose table and field names with backquotes. Then I want to complete the inserts and extend the inserts and 50000 is okay for the maximum length of the created query. Finally Use hexadecimal for BLOB.
Zip the File and Finish the Backup
Scroll down to the bottom and it’s going to ask whether or not I want to use Compression. I want to use gzipped. If you don’t have these choices on your host feel free to contact me and I can maybe give you a little bit of additional guidance. But we’re going to pick gzipped and select Go.
You can see here in this corner that it is downloading the zipped up file. If we look in our download folder you can see that there is the database and it’s been gzipped into this little thing. It automatically downloaded it to your computer; in my case it goes into my downloads folder.
Restore Database After a Problem
Let’s say that the database was totally trashed somehow and you need to restore it. Let’s go to the site that we’re going to trash www.estore5.byobtutorial.com. Here’s the website – a list of some products etc. in Lesson 5.
We’re going to pretend that this has a problem. For some reason or other it’s not working and what we’re going to do is we’re going to restore it from our download. First, we are going to save it as a different database since we don’t want to just destroy it just yet. Come over here and select it and now it’s structure has popped up in front of us.
Rename Existing Database
Come over to Operations and rename the database. I’m going to rename it byobtuto in my case and then underscore and then something else. This is going to be byobtuto, underscore, toss. That’s going to tell me that I should throw it away later. Say Yes we definitely want to create a new one and what it’s going to do is it’s going to get rid of the name for the ecom5 database. This tells you that ecom5 has been renamed to byobtutotoss.
Restore Database from the phpMyAdmin Backup File
Now if we come over here and we refresh this nothing’s going to work because the database doesn’t exist any longer. So we’ll restore the using the phpMyAdmin backup. In order to restore, we need to go to our main server then go to Import and then we’re going to chose the file which is this localhost.sql.gz. We’re going to open that up, we’re going to leave these things exactly as they are and we’ll hit Go.
You can see it says Import has successfully finished – 59 queries were executed and you can see ecom5 is back. If we come back over here and refresh again you can see it has all been restored.
It’s that easy to use phpMyAdmin to backup and restore your databases. Any time you’re going to undergo a significant change to your site you should go through this process of a manual backup of your SQL databases. You should also do this regularly – probably once a month or once a week. This concludes our video on how to backup and restore your WordPress database manually using phpMyAdmin.