Build Your Own Business Website header image

How to Manually Backup and Restore your WordPress Database Using phpMyAdmin

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.

6 Comments… add one

Save $200 on Membership Now!

Start learning today for as little as
$0.82 PER DAY!
THIS IS A LIMITED TIME OFFER!
Subscription Options

Related Posts

6 comments… add one
  • Prajeevan October 17, 2012, 6:38 am

    My client’s Wordpress was hacked… It contained a theme i bought and customized it for their needs….
    My question is….
    can i use this method to backup both the Content and the Theme? or is this restricted to only certain stuff?

    • Rick Anderson October 17, 2012, 6:42 am

      This post shows how to backup the database only. You have 2 things you need to backup, the database and the WordPress files. If you back up both of those then you will have backed up everything including whatever themes and theme customization you have done. I do have a bunch of other videos demonstrating how to do a full manual backup of a site.

  • masa December 23, 2012, 4:48 pm

    Hello,

    Thanks for this great video!
    My website had an internal server error message yesterday. I did contact my host (ipage) and they fixed the issue. However, I lost most of my blog content.

    I’m trying to restore the backup I have in MySQL (especially all my articles), but when I try the way you did in this video, it shows an error message.

    I just want my content back on my website, but I have no idea how to import it using MySQL?

    Thanks for you help,
    Masa

    • Rick Anderson December 31, 2012, 6:59 am

      Masa, I assume you are using PHPMyAdmin. If so, export the database you want to replace, drop all of the tables in the database and import your backup database. If you get an error message please post a screen shot or use Screenr to record a screencast so I can see what is actually happening.

  • Nathan Abraham July 26, 2013, 11:31 am

    Hello – you’ve created the clearest, most detailed and professional looking post on this difficult and Critical subject. For websites I’m working on – I’ve looked over weeks for a reliable way to do this, so I can sleep nights.
    When I opened the phpMyAdmin that my host provides- I saw some differences in it from the views and instructions on the Export page. The line ” Use hexadecimal for BLOB” is missing and other lines seem to be added….I want to be very sure before I try this, as I don’t want to lose a Dababase.

    The phpMyAdmin version through my host (FutureQuest.net – great support) is 3.5.8.1 and I noticed that there is a slightly more recent version 4.0.4.1. I wonder if because your post was on August 10, 2010 there may have been some changes in the lines to be answered in phpMyAdmin? If so, is there a way to use the version of phpMyAdmin you used directly – or do you have any suggestions on what to answer in the version I am now using?

    Thanks for your excellent tutuorial,
    Nathan Abraham

    • Rick Anderson July 26, 2013, 2:29 pm

      Nathan, yes there are more recent versions of PHPMyAdmin and the interface does look somewhat different. However, the concept is still the same. My suggestion would be to first try manually cloning a site and see if the standard default export settings work for you. If your host is like GoDaddy the default export is NOT to a download file but instead to the screen. So you want to test your configuration to see if it works. If it works you’re good to go. If it doesn’t then you’ve learned in a non mission critical setting.

Leave a Comment