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.
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.
mysqldump -u [user] -p [database_name] | gzip > [backup_name]_$(date +%Y-%m-%d_%H-%M-%S).sql.gztar -zcvf [archive_name]_$(date +%Y-%m-%d_%H-%M-%S).tar.gz [target_directory]gunzip < [backup_name].sql.gz | mysql -u [user] -p [database_name]tar -zxvf [archive_name].tar.gz --directory [target_directory]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.
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
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:
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.
Creating a backup of your WordPress installation is not as hard as you might think. It only boils down to two things:
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)
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
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 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.
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!
Let’s start with the actual 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
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:
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:
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.
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]
A concrete example archiving and compressing 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.
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.
Restoring your backups is almost as simple as creating them. We will start by restoring the database first.
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]
Step 1: Create a New Database
mysql -u [user] -p
2. Show all existing databases on the server to make sure that you are not overwriting a database that already exists.
mysql> show databases;
3. Create the database.
mysql> create database [database_name];
3a. (optional) You can check the character set and collation of the database if you are curious.
mysql> show create database [database_name];
4. Make sure that the database was created successfully.
mysql> show databases;
5. If everything worked, you can now exit the MySQL server interface.
mysql> 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
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]
Done! Your database should be restored after this step.
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
archive_nameIf 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.
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:
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.
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:
rsyncI hope you enjoyed this article. If you have any questions or remarks regarding this backup procedure, feel free to leave a comment below.
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 GetDevDone 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!
Learn what headless Drupal is and when you should choose it for your own project. In this post, we discuss decoupled Drupal. We define it, talk about its most common use cases, its advantages and drawbacks, and, finally, give recommendations as to whether you should make your own Drupal website headless. Walking down the decoupled […]
It's hard to imagine a website without dropdowns. It you want to learn how to create and add dropdown menus in WordPress, this post will teach you.
Why choose Angular 2+ framework for your next enterprise project this year and beyond. In this post, we discuss one of the most popular JavaScript frameworks — Angular. We briefly touch upon its history, the transition from the AngularJS framework to Angular 2+, and explain why this tool is particularly suitable for building large-scale applications. […]