How to Create a WordPress Database Backup

One of the most important tasks anyone running a WordPress website can do is create a WordPress Database backup. How often is up to you – on a low traffic website, you can get away with a backup once a week. On a high traffic website with a large number of posts and comments being added daily, then you should have a daily backup schedule running. A proper backup schedule is essential if your business depends on your website running smoothly.

What is a WordPress Database Backup?

As you are probably aware, when you create a post, page or product on a WordPress website stores the information you enter in a Database. A Database is essentially just a big store of information which WordPress queries to create each page. Over time the size of the Database increases and keeping a separate copy of this information rises in importance. Ask yourself a simple question; if you lost your website or it was damaged for whatever reason, how much work will it be to re-create it? If your website has been up for more than a couple of days, then the work to recover will be enormous.

Luckily you can create a Database backup quite easily. There are several WordPress plugins available for creating scheduled backups of your Database. Some of them are:

This is a very short list of the most popular and one isn’t even dedicated to the task! They each have their pros and cons – you need to choose the best one for you. For the purpose of this series, we are not going to use any of these plugins as we want to grab a copy of the database with some specific options chosen. Instead, we are going to use phpMyAdmin which most web hosts offer.

What is phpMyAdmin?

phpMyAdmin - MySQL Database Admin Tool

MySQL Database Admin Tool

phpMyAdmin is one of the most used software tools to manage a MySQL Database server. Written in PHP (the same programming language as WordPress) it allows you to graphically perform tasks which would normally entail long, complicated and often arcane commands. One word of caution when using phpMyAdmin – be very careful as you can wipe out your entire database with a few clicks of the mouse. But don’t worry, phpMyAdmin will warn you several times in increasingly strong warnings before it goes ahead and does that! Essentially, you would be incredibly, um, what’s a polite word, er, unfortunate to do this without meaning to. (By unfortunate I actually mean dumb. Really, really dumb. The final warning after 3 clicks is “You are about to DESTROY a complete database! Do you really want to execute “DROP DATABASE test”?” If you manage to delete your database after that warning, well …)

Steps to Backup a Database in phpMyAdmin

The following steps are the options I have found the best for manually cloning a WordPress website. What these do is create a file which will allow you to recreate the exact structure of the Database for your live WordPress website.  Also, by doing the steps exactly as shown you will have no chance of deleting your database.

  1. After logging in, click on the name of the WordPress database in the left menu.
  2. Click Export
  3. Choose Custom – display all possible options
  4. Click Select All
  5. Under Output: leave Save output to a file ticked
  6. Optionally set Compression: to zipped if you have a large site
  7. Under Format-specific options: make sure structure and data is selected
  8. Under Object creation options, tick the following:
    • Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT  statement
    • CREATE TABLE options:
      • IF NOT EXISTS
      • AUTO_INCREMENT
    • Enclose table and column names with backquotes …
  9. Under Data creation options set
    • Syntax to use when inserting data:
      • both of the above
    • Tick Dump binary columns in hexadecimal notation …
    • Tick Dump TIMESTAMP columns in UTC …

Click go. You will then be prompted to save your database backup somewhere. I would suggest creating a new directory to keep the related files we create together. Depending on the size of your database, it may take a few seconds to several minutes to download the entire backup. This step is now complete.

See, I told you our cloning was easy when you approach it in stages. The next step is backing up all the WordPress files.

This is the second post in a series about Cloning a WordPress Website.

Leave a Reply

WordPress Anti-Spam by WP-SpamShield