Web Development wordpress

How to Backup WordPress

Read this post to learn how to back up a WordPress site by just using the terminal and why this method is better than installing backup plugins.

thumbnail

Editor’s Note

You certainly know that a timely made backup will help you restore your WordPress website to its working state regardless of the cause of the problem – a hacker’s exploit, inadvertent deletion of a database record, or something else.

While you can use various plugins to create a copy of your site, relying on them can be risky. You never know if the company that made a plugin will keep maintaining it in the long run. There are also compatibility issues: some plugins may negatively impact the operation of others.

The solution? You can make backups by using the terminal. This gives you much finer control over what and how you can copy and removes your dependence on plugins.

If you want to know how to apply this method the right way, read the post by Christoph Schmidl.


Part 1: Creating Backups Manually by Just Using the Terminal

Photo by Denny Müller on Unsplash

(tl;dr)

  1. Create WordPress database backup: mysqldump -u [user] -p [database_name] | gzip > [backup_name]_$(date +%Y-%m-%d_%H-%M-%S).sql.gz
  2. Create WordPress files backup: tar -zcvf [archive_name]_$(date +%Y-%m-%d_%H-%M-%S).tar.gz [target_directory]
  3. Restore WordPress database: gunzip < [backup_name].sql.gz | mysql -u [user] -p [database_name]
  4. Restore WordPress files: tar -zxvf [archive_name].tar.gz --directory [target_directory]

1. Introduction

WordPress is the world’s most popular content management system and according to W3Techs,

WordPress is used by 63.5% of all the websites whose content management system we know. This is 37.8% of all websites.

WordPress Logo

Since WordPress is representing such a big market share, you would think that WordPress is a rock-solid system and that’s why so many people rely on it. However, when you take a closer look at CVE Details you can see that a total number of 294 WordPress security vulnerabilities have been discovered throughout the years.

This leads to the assumption that WordPress is used by so many people because it’s easy to use and because it is supported by a large number of plugins that can extend its functionality; and not because it’s secure.

But this is a dangerous combination. WordPress is often used by the average person who knows how to use it for blogging and how to install plugins. But the installation of additional plugins may also introduce additional vulnerabilities that put the whole WordPress installation at risk.

Security is traded for functionality.

Most people try to mitigate this risk by installing an additional backup plugin to be able to restore the WordPress installation when something goes wrong. However, the more plugins you install, the more complex and vulnerable your WordPress installation becomes. Josh Kaufman put it this way:

“The more complex a system is and the longer it operates, the more likely it is to suffer a major failure.” — Josh Kaufman

This article tries to solve this problem by explaining some of the WordPress internals and how to back up a single WordPress installation manually on a Linux server by using the terminal. Although most backup plugins can solve this task way quicker than described in this article, it will certainly help to know how to create backups manually and getting familiar with the terminal.

“Risk comes from not knowing what you’re doing. “ — Warren Buffett


2. The Goal of This Article

This article should serve as a first starting point for people who want to back up their WordPress installation manually and don’t want to install an additional plugin for that. If you are not afraid of the terminal and your WordPress installation runs on a Linux server (e.g., Ubuntu) then this article might come in handy. By following this article step by step, you will also gain more insights into how most backup plugins work under the hood.

However, I assume the following things:

  • You want to back up a single WordPress installation
  • Your WordPress installation uses a MySQL or MariaDB database
  • You are not afraid of the terminal

There are also other graphical programs available like phpMyAdmin or MySQL Workbench to create a database backup but in this article, we will stick to the terminal to keep things simple.


3. What We Need to Back up

Creating a backup of your WordPress installation is not as hard as you might think. It only boils down to two things:

  • WordPress database
  • Important WordPress files and folders

3.1 WordPress Database

If you take a closer look at the default structure of a WordPress database, you can see that it only contains 12 different tables which are mostly self-explanatory. wp_users contains information regarding the registered users of your WordPress installation, wp_posts contains the posts, and so on… you get the picture.

So, we need to come up with an easy way to create a backup of the whole database that is also easy to restore later on.

mysql> show tables;
+ — — — — — — — — — — — -+
| Tables_in_wp_db |
+ — — — — — — — — — — — -+
| wp_commentmeta |
| wp_comments |
| wp_links |
| wp_options |
| wp_postmeta |
| wp_posts |
| wp_term_relationships |
| wp_term_taxonomy |
| wp_termmeta |
| wp_terms |
| wp_usermeta |
| wp_users |
+ — — — — — — — — — — — -+
12 rows in set (0.00 sec)

3.2 WordPress Files

If you installed WordPress yourself on a Linux server then you probably peeked into its file structure at some point in time. The good news is that we do not have to copy all WordPress files into our backup. Most of its files never change and remain the same from one WordPress installation to another.

The files and directories that we have to back up were highlighted in bold in the file structure excerpt below.

This assumes that you did not add any custom code to standard WordPress files. If you did then make sure that you also include the custom files into your backup.

`-- wordpress
|-- .htaccess
|-- index.html
|-- index.php
|-- license.txt
|-- readme.html
|-- wp-activate.php
|-- wp-admin
|-- wp-blog-header.php
|-- wp-comments-post.php
|-- wp-config.php
|-- wp-config-sample.php
|-- wp-content
| |-- index.php
| |-- plugins
| |-- themes
| |-- upgrade
| `-- uploads
|-- wp-content.tar.gz
|-- wp-cron.php
|-- wp-includes
|-- wp-links-opml.php
|-- wp-load.php
|-- wp-login.php
|-- wp-mail.php
|-- wp-settings.php
|-- wp-signup.php
|-- wp-trackback.php
`-- xmlrpc.php
  • .htaccess: This one is optional and just contains rewrite rules for the Apache webserver.
  • wp-config.php: This one is important and contains information about your database connection and other custom configurations you may have added. It also contains the name of the WordPress database that you want to backup. In my case, the name of the database is wp_db .
// ** MySQL settings — You can get this info from your web host ** //
/** The name of the database for WordPress */
define( ‘DB_NAME’, ‘wp_db’);
/** MySQL database username */
define( ‘DB_USER’, ‘marvin’);
/** MySQL database password */
define( ‘DB_PASSWORD’, ‘the_answer_is_42’);
/** MySQL hostname */
define( ‘DB_HOST’, ‘localhost’);
/** Database Charset to use in creating database tables. */
define( ‘DB_CHARSET’, ‘utf8’);
/** The Database Collate type. Don’t change this if in doubt. */
define( ‘DB_COLLATE’, ‘’);
  • wp-content: This is the most important directory you need to create a backup of. It contains your uploaded assets like images or videos but also your installed WordPress themes you may have altered to your specific needs. You can make a copy of the whole wp_content directory or you just pick its sub-directories that seem most important to you.

If you want to play it safe then just copy the whole wordpress directory. But this may take valuable disk space that you could use otherwise.


4. Creating Backups

Now that we know what we want to include in our backup(s), we can start with the actual process.

In the upcoming examples, I assume that you are already connected to the remote server where WordPress and its database are running on. This is mostly done by using SSH.

You may need some minimal background knowledge about using SSH clients to connect to your Linux server but that should not hinder you from following the process. If your SSH skills are a bit rusty, you can take a look at the following links to get you back on track:

What I like to do is just using the command-line SSH client on my Mac. Here is an example SSH session where I connect to the fake serverexample.com with the username user. Feel free to use any SSH client you like!

SSH login

Let’s start with the actual database backup.

4.1 Database Backup

In this example, I assume that you just want to back up a single database to a single file because this is the most common use case. If you are interested in more options because your specific use case is not covered in this article, then the following link may be of interest to you: How to Back Up and Restore MySQL Databases with Mysqldump.

As soon as we are connected to the remote server we can use the mysqldump program to create a simple backup of the database.

The general syntax for creating a backup looks like this:

mysqldump -u [user] -p [database_name] > [backup_name].sql

Since this command produces the same name for our backup every time, we have to come up with a way to differentiate between backup files.

A smart way would be to incorporate the current time for the creation of the backup. Since we do not want to look up the current time ourselves, we can use the Linux date program.

The date program uses the following syntax:

$date +%[format-option]

where

  • %Y: year
  • %m: month
  • %d: day
  • %H: hour
  • %M: minute
  • %S: second

Incorporating the date program would result in the following command that takes the current time into account.

mysqldump -u [user] -p [database_name] > [backup_name]_$(date +%Y-%m-%d_%H-%M-%S).sql

A concrete example of the above command in action could look like this:

Raw mysqldump with the date command

If disk space is not an issue for you then you can skip the following optimization step.

However, if you like to optimize the current approach a bit further and save some disk space, you can include a compression tool like gzip into the command:

mysqldump -u [user] -p [database_name] | gzip > [backup_name]_$(date +%Y-%m-%d_%H-%M-%S).sql.gz

This command will create a compressed version of the sql dump with the gzip program but you can also take any other compression library like bzip2.

A concrete example of the before mentioned command:

Compressed mysqldump with date command

You may ask yourself if the usage of a compression library makes a significant difference here.

In my case, the compressed SQL dump is only 25% of the size of the raw one as you can see below.

christoph@localhost:/home/backups# ls -alFh
total 644K
drwxr-xr-x 1 christoph christoph 4.0K Aug 3 12:16 ./
drwxr-xr-x 1 christoph christoph 4.0K Aug 2 13:03 ../
-rw-r--r-- 1 christoph christoph 508K Aug 3 11:46 wp_db_backup_2020-08-03_11-4656.sql
-rw-r--r-- 1 christoph christoph 127K Aug 3 12:16 wp_db_backup_2020-08-03_12-16-03.sql.gz

Therefore I would suggest always use a compression library for your SQL dumps.

That’s it! We created a backup of our WordPress database and saved it to our local filesystem.

Next, we will create a backup of the WordPress files.

4.2 Files Backup

As mentioned at the beginning of the article, there are only a couple of important files and directories we have to save. However, if you want to play it safe then just copy all WordPress files and directories!

We will use the Linux tar program to create an archive of our WordPress installation and will also compress everything.

The following command creates a compressed archive of atarget_directory:

tar -zcvf [archive_name]_$(date +%Y-%m-%d_%H-%M-%S).sql.gz [target_directory]
  • -z: Compress archive using gzip
  • -c: Create an archive
  • -v: Verbose. Display progress while creating an archive
  • -f: Archive filename

A concrete example archiving and compressing the whole wordpress directory:

Archiving the whole WordPress directory

We can also use the tar command to archive and compress specific files and folders as mentioned at the beginning of the article. All we have to do is to specify multiple target directories and files as shown below.

If you followed along until this point then you should have similar backup files for your database and your WordPress files as shown below.

Archiving specific WordPress directories and files
christoph@localhost:/home/backups# ls -alF
total 644K
drwxr-xr-x 1 christoph christoph 4.0K Aug 3 12:16 ./
drwxr-xr-x 1 christoph christoph 4.0K Aug 2 13:03 ../
-rw-r--r-- 1 christoph christoph 17990890 Aug 5 19:52 wordpress_backup_2020–08–05_17–52–43.tar.gz
-rw-r--r-- 1 christoph christoph 7942063 Aug 5 19:54 wordpress_backup_2020–08–05_17–53–59.tar.gz
-rw-r--r-- 1 christoph christoph 108750 Aug 4 18:45 wp_db_backup_2020–08–04_16–45–48.sql
-rw-r--r-- 1 christoph christoph 20 Aug 4 18:46 wp_db_backup_2020–08–04_16–46–52.sql.gz

We have now successfully created backups of the WordPress database and its files. You could now use an SFTP client like Cyberduck to copy the backup files from the remote server to an FTP server or any other storage location.

The next section will guide you through the process of restoring the backups.


5. Restoring Backups

Restoring your backups is almost as simple as creating them. We will start by restoring the database first.

5.1 Database Restoration

To restore the database, you have to create a new one on your MySQL or MariaDB server with the same name as the database you want to restore. If you forgot the name of the database you want to restore, you can just take a look at the top of the SQL dump and look for Database: [database-name].

On a Linux system, you can just use the head command to look at the top of the SQL dump but any other text editor will achieve the same result.

head [sql-file]
Head command to inspect sql dump

Step 1: Create a New Database

  1. Login to your MySQL server.
mysql -u [user] -p
MySQL server login

2. Show all existing databases on the server to make sure that you are not overwriting a database that already exists.

mysql> show databases;
Mysql command: show databases

3. Create the database.

mysql> create database [database_name];
Mysql command: create database

3a. (optional) You can check the character set and collation of the database if you are curious.

mysql> show create database [database_name];
Mysql command: show create database

4. Make sure that the database was created successfully.

mysql> show databases;
Mysql command: show databases

5. If everything worked, you can now exit the MySQL server interface.

mysql> exit;
Mysql command: exit

Step 2: Restore the MySQL dump

The default command to restore a raw SQL dump for MySQL looks like this:

mysql -u [user] -p [database_name] < [backup_name].sql
Mysql: restore raw mysqldump

You can also restore a compressed SQL dump in one line without unpacking it to your disk in between operations. This might come in handy when you are dealing with large database dumps.

gunzip < [backup_name].sql.gz | mysql -u [user] -p [database_name]
Mysql: restore compressed mysqldump

Done! Your database should be restored after this step.

5.2 Files Restoration

To unpack the tar archive we can use the tar command again.

To extract a gzipped tar archive into the current directory you can use the following command:

tar -zxvf [archive_name].tar.gz

where

  • -x: Extract files from archive_name

If you want to extract a gzipped tar archive into a target directory, you can use

tar -zxvf [archive_name].tar.gz -C [target_directory]

or

tar -zxvf [archive_name].tar.gz --directory [target_directory]

However, you have to make sure that target_directory already exists before invoking the command. It won’t be created on-the-fly. On Linux, you can achieve this by using the mkdir command.

Untar WordPress archive

Done! We restored the WordPress database and its files. Make sure that all WordPress files get extracted to the proper location of your webserver. Most of the time, that will be something similar to/var/www/[project_name].

Checklist

You may want to check a couple of things after restoring your WordPress installation like:

  • Is the site still reachable? No white screen?
  • Does it use the same theme as before?
  • Is the content still available? Images, videos, posts still available?
  • Is the content still targeting to the right domain? Important when you moved to another domain.
  • Are logins still working?
  • Are plugins still working? For example, Google Captcha uses keys that are bound to a specific domain. When you move to another domain you have to get new keys.

6. Database Migration Issues

This section describes the problem when you want to migrate from one domain to another with your WordPress installation.

Imagine that you want to migrate from old_domain.com to new_domain.com. This does not seem like a big deal at first sight but it is. One could think that links in the WordPress database are represented as relative references like /wp-content/upload/test.pngand not fully referenced static domain names. Unfortunately, that’s not the case. Although you may have switched from old_domain.com to new_domain.com, your links in the database are still containing absolute paths with old_domain.com.

Most backup and migration plugins are handling this situation automatically. However, you can also fix this issue yourself by connecting to your restored database and executing some SQL commands to replace the old domain with the new one.

Connect to your SQL server as shown before using mysql -u root -p. Then select the database you want to use: mysql> use [database_name];

The following four commands will probably fix the issue with the domain names by performing a full-text search on the database tables and then replacing the strings containing the old domain with the new one.

Please change it according to your own domains!

mysql> UPDATE wp_options SET option_value = replace(option_value, 'http://www.old_domain.com', 'http://www.new_domain.com') WHERE option_name = 'home' OR option_name = 'siteurl';
mysql> UPDATE wp_posts SET guid = replace(guid, 'http://www.old_domain.com','http://www.new_domain.com');
# This one is tricky but probably works most of the time.
mysql> UPDATE wp_posts SET post_content = replace(post_content, 'http://www.old_domain.com', 'http://www.new_domain.com');
mysql> UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://www.old_domain.com','http://www.new_domain.com');

More information can also be found at the following link: Change WordPress URLs in MySQL Database when site is moved to new host.


7. Future Work

This article described a very simple backup and restore procedure for WordPress. It is not very efficient and certainly takes some time to execute. However, this first part is intended to be more educational than being used in production.

Given that this is only the first part of a little series about WordPress backups, I would like to build upon this first article and cover the following improvements in the upcoming articles:


I hope you enjoyed this article. If you have any questions or remarks regarding this backup procedure, feel free to leave a comment below.


Top-Quality WordPress Development Services from PSD2HTML

As you see, backing up a WordPress site efficiently is not something that most rank-and-file users can handle easily.

That’s when you might need the assistance of PSD2HTML WordPress developers. With 16+ years of industry experience and thousands of successfully completed WP projects, we know everything about the world’s most popular CMS.

Contact us with any WordPress-related task, from building brand-new custom themes or customizing existing ones to page load speed optimization and security.

Helping your business succeed is our top priority!

Christoph Schmidl

Ph.D. candidate by day, Taekwon-Do instructor by night | Christoph writes about education, science, and technology | Follow him on Twitter | Read him on Medium