How to Make a Manual WordPress Database Backup

Exclusive WPEngine Coupon Code for AlienWP Visitors!
Get 5 Months Free & 30% Off All Plans.
Click the coupon code to copy and open the link »

---

Click Here for Deal

---

And one of the biggest mistakes that website owners make is not maintaining a regular backup of the site. Most people tend to believe that since nothing has gone wrong so far, nothing will go wrong in the future. But unfortunately this is not true.

It only takes one unlucky circumstance for you to completely lose your WordPress site and start all over again from scratch. Fortunately, this can be prevented. And the most obvious solution is to maintain a regular backup of your site. Just like the way we maintain a backup of the important files on our PC or laptop, we should have a foolproof backup strategy lined up for our site too.

Backing up your site is mandatory. People who have been using WordPress for a long time is well aware of the importance of maintaining a backup. However, for the less experienced web owners, they fail to understand the necessity of maintaining a regular backup of the contents on their site.

It is not uncommon for website owners to not take backups seriously until something goes wrong. It is a hard lesson that they never forget. But if you are smart, you will get into the habit of backing up your website from the beginning.

Let’s put some light on the importance of creating a backup…

Server Failures

A server is a machine just like our computers and it can crash just like our computers do. Failure of our computer’s hard disk may not affect thousands of people. But if a web server crashes, it may create drastic situations. Server failures may occur due to various reasons like power loss, hardware malfunction, hacking attacks like DOS and so on. But if we have a backup, a server crash may not pose that much of a serious threat to a site. Whatever data has been lost, can be recovered back from the stored backup.

Protection against hacking

If you are running a website, you are always a target for the hackers. And WordPress, being the most popular CMS platform in the world, has made itself prone to being a target for the hackers and spammers. Now you may argue that your site is safe because you have installed tons of security plugins. But trust me, your site is never 100% safe from hackers. Even a single security loophole on your site can make it vulnerable to hacking attacks.

So the best precaution that you can adopt is to create a regular backup. That way even if some hacker ruins your site along with your database contents, you can always restore it back from the available backup.

Software Issues

This is another issue that is being overlooked. Software issues can became a major threat to a website. Sometimes even a simple faulty code snippet can have devastating effects on your site. For instance, there is a possibility that something might go wrong with the new plugin installation, which may create hindrance in the website’s data. If you already have the latest backup of the content, then you can tackle such issues.

Data Center Issues

No hosting company in the world can offer 100% uptime and reliably. Even the best WordPress hosting companies have had severe problems with their datacenters. The data center might lose network connectivity due to major fiber cuts. Such issues can cause your website to be unavailable for elongated periods of time. This will surely effect your website’s traffic and make your visitors move away to some other site.

Human Error

Human errors are inevitable and even the most experienced web personnel’s can make mistakes from time to time. This could result in implementing wrong changes to your site or even deleting your key files.

A small slip up from your side, say an accidental file deletion, can set you back by weeks or months, and sometimes, can even throw away all that you’ve worked so hard for.

Mistakes are not something that happens every day, but when they do happen, you will be glad of any recent backups you have

Natural Disasters

Natural disasters are unavoidable. A fire break, flood may completely damage your servers and other hardware peripherals. But if you have a recent backup of your files stored on some other location then you can gladly say that your site escaped even a natural disaster.

Your hosting may or may not provide regular backups for your site. Even if they do, here’s why you shouldn’t rely on them –

1. Hosting company backups aren’t specifically designed for WordPress. This means they may not actually help when you need them.
2. The backups may not be regularly scheduled, and they may not be often enough to be useful in case of a disaster.
3. You may not be able to restore a single site from hosting company backups without restoring all sites in your account.
4. You won’t be notified if the backups fail, so you’ll never know if they’ll actually be there if/when you need them.
5. You’ll be at the mercy of your hosting company if you ever need to restore from backup. They probably won’t be as responsive as you think they should be.

Creating Backups

So by now, you must be well aware of the importance of why you should always keep a backup. Butbefore we get started with the manual backup process, it is important to understand the basics of a WordPress database.

WordPress is a simple database driven PHP web application. Therefore, for a complete WordPress backup you need to –

• Backup the WordPress blog and website files which contain the WordPress web application files, WordPress theme files, plugins files and your uploaded content. Typically, all these files arefound in the root of your website.
• Backup the WordPress MySQL database where the content, user information, passwords and privileges, website settings, theme settings, WordPress plugins settings and other similar data is stored. If you are not sure what is your WordPress database name, you can find it in the wp-config.php file, in the root of your WordPress
• Root Configuration Files

Several root folder files must be backed up

1. Wp-config.php, which contains the settings including database connection strings(more details below).
2. .htaccess –the server configuration file
3. Webmaster tool identification file such as googleCODE.html, BingSiteAuth.html
4. Other files you have added

What is the WordPress wp-config.php File?

The WordPress wp-config.php file includes the database connection details and other configurable parameters, such as the WordPress security keys, debug options and more. As such you can live without backing up this file because you can always retrieve the database connection details from other sources, but it is easier and safer to back it up

A standard WordPress database contains 11 tables, all of which are listed below:

• wp_commentmeta
• wp_comments
• wp_links
• wp_options
• wp_postmeta
• wp_posts
• wp_terms
• wp_term_relationships
• wp_term_taxonomy
• wp_usermeta
• wp__users

Why Create Database Backup Manually?

In case of a hacking attempt or after getting hacked, you are thrown out of your own admin area. In these situations, since you cannot access the admin area, it would be very difficult to use a plugin for backup. This is where knowing how to create a backup manually comes handy. It is same as creating a backup using plugin with the only difference that the process is not automated and requires a human intervention.

Here, we will discuss different ways of how we can create a database backup manually.

Backing Up WordPress database manually using phpMyAdmin

phpMyAdmin is an open source software that provides a web based graphical user interface to manage your MySQL database. Most WordPress hosting providers have phpMyAdmin installed in their control panel. This allows users to easily access the database and perform common database management tasks.

First of all, you need to login to your web hosting control panel(also known as cPanel). Upon login, scroll down a bit and click on the phpMyAdmin, which would be under the Database section.

This will launch the phpMyAdmin window. From the left sidebar, make sure that your WordPress database is selected inside the phpMyAdmin. If it is not, then click on your WordPress database name in order to select it.

Also when you click on the database name, you will be able to see the list of tables, the database contains.Tap on the “Export link” from the top menu bar to export/backup the database to a file.

Next, you will be presented with two options for exporting the database – Quick and Custom

Select the Custom option as it gives you more control about what to export and what not. Clicking on Custom will reveal some more options. First of all, it will show a list of all the tables in your WordPress database.

Sometimes WordPress plugins add their own tables to your database. If there are any tables that you would like to exclude from export, then you can deselect them. If you are unsure, then it is better to keep them all selected

Scroll down to the output section and select the “Save output to file” Select utf-8 for “Character set of file”. For compression, you can select the zipped or gzipped option.

Now scroll down to the bottom of the page and click on the Go. You will now be presented an option to download the file and based on your selection, you will have a zip or gzip

That’s all you need to do to make a database backup for your WordPress site using phpMyAdmin.

Creating a Manual database backup using cPanel

This is another method for creating a database backup using cPanel. The steps are

Login to your web hosting control panel (cPanel). And under the Filessection, click on the “Backup Wizard”.

On the backup wizard page, click on the backup button

The backup button provides two backups, the full backup and partial backup.

Full backup is used when you are shifting to a different webhost and cannot be used to restore within the same host.

The partial backup is used to restore when your site gets down or is infected by malware and you still are continuing within your webhost.

There are three types of items that need to be backed up under each of the above two.

The home directory: it contains your themes, plugins and site’s content.

The MySQL database: it contains your sites set up that helps your site to store the posts and comments etc.

The email forwarders and filters.

Download all the three and save them on your PC or external hard drive.

Note: you cannot download all the three at a time, you need to download one, go back and download the other one. Rinse and repeat

That’s all there is to creating a backup using cPanel. Store the WordPress database file in a secure place, ideally on a different media.

Note: From this page, you can also generate a full website backup and then download it once the process is completed

Creating WordPress database backup using MySQL command line

If you host your own web server and have access to it via SSH or other protocol, you can make a database backup using a standard MySQL tool called mysqldump. As the name implies, themysqldumptool dumps a MySQL database into a text file which can later be used to restore the WordPress database.

Use the MySQL root user to connect to the MySQL server, select the WordPress database that you want to create a backup for(here wpdatabase) and export it to a text file called wpdb_backup.sql.

Mysqldump –u root –p toor wpdatabase > wpdb_backup.sql

Here –u is used to specify the username that will be used to connect with the database. In the code above, we are using the user “root”.

-p is used to specify the password of the account you are using to connect to the MySQL database server.In our case, the password is “toor”.

Wpdatabase is the name of the database that we want to create a backup for.

Wpdb_backup.sql is the name of the file where the database dump will be stored. If the file does not exist it will be automatically generated by the tool. Once the database is dumped into this file, download it to your computer and store it in a secure location, ideally on a separate media.

Scheduling Automatic Backups using cPanel

Maintaining ascheduled backup of your WordPress MySQL database is the most important thing to do when running your own WordPress website, it is required to restore your blog, it needs to urgently migrate to another host or restore your database from a crashed server.

Login to your cPanel and look for the Cron Job icon

Select the time and frequency to run backup command

Under the command field, copy and paste the following command

mysqldump –opt -Q -u dbusername –password=dbpassword dbname | gzip > /path-to-store-the-backup-file/db_backup.sql.gz

Replace dbusername with the database user

Replace dbpassword with the database user password. In some server, you might need to put a pair of single quote ‘dbpassword’ around the dbpassword for it to work.

Replace dbname with the database that you are backing up

Replace the path-to-store-the-backup-file to the file path in your server where you want to save the backup

Test the cron job to check if it works or not

Restoring your MySQL database from a backup

If you want to restore your WordPress database from a backup, it can be easily done using phpMyAdmin. The steps are listed below –

Log in to your cPanel and click the phpMyAdminicon in the Databases section.

Select the database where you would like to import your backup. This can be done from the menu in the left sidebar.

A new page will be loaded showing the structure of the selected database. To import data inside the database, click the Import tab

On the new page that opens, click the Browse button and select the backup that you want to import from your local computer. You have the option to pick the character set of the file from the drop down-menu just below the upload box. If you are not certain about the character set your database is using just leave the default one. Once ready, click the Go button to perform the import

The import will start and once finished you will be redirected to a page with a confirmation notification that the import was successful.

How many backups should you maintain

Well, when it comes to storing your WordPress backups, you generally want to always have these two things readily available:

1. The most recent backup
2. The most recent backup from before the problem that caused you to need a backup

Although these are often the same, sometimes things happen (sites get hacked, plugins conspire to cross wires, your web host changes some settings on you) and you don’t know about it for a week or two, and by then your most recent backup also contains The Problem.

So if you back up your WordPress site weekly, and you save a month or two of backups, you’re fine

Also, schedule your backups with a frequency that makes sense for your site. If you’re posting every day, you probably want daily or weekly database backups.

Something to Remember

• ALWAYS run a backup! You should backup your site database whenever you change its design & content to avoid losing all files and data when the nasty happens.
• Don’t store all your backups in one single location. Don’t just back up on the server level. In fact, WordPress recommends keeping three different backup copies — all in different mediums (so CD, hard drive, desktop, cloud, etc)
• Besides manual backups there are also various plugins that can do the task for you. They are easy to use and requires minimal user interaction.