Backing up databases is a crucial part of any well-maintained website. Regardless of the problem we may have with our WordPress installation, having the ability to back up (and restore) the MySQL database gives us a solid peace of mind, knowing that we could always have the option to revert all the current problems and start clean.

This article covers all the possible strategies to back up / export MySQL databases in an EasyEngine-powered WordPress environment. I did not find any articles on the web covering all the nitty gritty details of this procedure, so I’ll try to be as comprehensive as possible. Let’s dive in!

Backing up database with mysqldump

Let us first talk about the classic mysqldump client utility. This is the most common, tried-and-true method of backing up MySQL databases. Invoking it is simple, just do this in the command line:

root@li1984-106:~# mysqldump name_of_database > name_of_database_backup.sql

This generates a name_of_database_backup.sql database backup file under the current directory.

Restoring the database backup with WP-CLI

After we obtain the .mysql database backup file, using it to restore the database is easy. I recommend performing this procedure with mysql:

root@li1984-106:~# mysql name_of_database < name_of_database_backup.sql

Be aware that we assume the database name_of_database already exists in MySQL, otherwise the restoration will fail. If it doesn’t exist, simply create the database in mysql:

mysql> create database name_of_database;
Query OK, 1 row affected (0.00 sec)

Why mysqldump doesn’t work with EasyEngine

The mysqldump method would work for the conventional LAMP / LEMP (Linux, Apache / Nginx, MySQL & PHP) WordPress server stacks. However, we may not be able to take advantage of it if our WordPress is installed with EasyEngine.

For example, on my EasyEngine-powered WordPress server, I got this error when I tried to execute it:

root@li1984-106:~# mysqldump name_of_database > dump.sql
mysqldump: Got error: 2002: "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory")" when trying to connect
Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!

In fact, we’ll get the exact same error when executing mysql directly:

root@li1984-106:~# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory")
Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!

The reason for this error is that EasyEngine executes MySQL in an encapsulated Docker container, hence MySQL isn’t running as a globally accessible service. We can verify the status of your MySQL service by executing the following:

root@li1984-106:~# service --status-all
 [ - ]  acpid
 [ + ]  apparmor
 [ + ]  cron
 [ + ]  docker
 [ - ]  mdadm
 [ - ]  mdadm-waitidle
 [ - ]  nginx
...

Above we see a list of all the global services in alphabetical order (I omitted some services above for brevity). The plus sign means it’s running and the minus sign means it’s disabled. In our case, we don’t even have mysql registered as an available service on this Easyengine stack.

Another way of verifying a service’s status is to simply invoke it. An available service would have the following commands:

root@li1984-106:~# service nginx
Usage: nginx {start|stop|restart|reload|force-reload|status|configtest|rotate|upgrade}

Now let’s try to invoke the service named mysql and we’ll see the error:

root@li1984-106:~# service mysql
mysql: unrecognized service

Backing up database with WP-CLI

WP-CLI is the command-line interface for WordPress. Eevery EasyEngine-powered WordPress site comes with WP-CLI already installed.

Backing up databases using WP-CLI is the easiest and my recommended approach for EasyEngine-powered WordPress sites.

To back up the database of a WordPress site, let’s invoke the WP-CLI and execute the following commands:

root@li1984-106:~# ee shell yoursite.net
^_^[www-data@yoursite.net:~/htdocs]$ wp db check
yoursite_net.mt_comments                          OK
yoursite_net.wp_commentmeta                       OK
yoursite_net.wp_comments                          OK
yoursite_net.wp_links                             OK
yoursite_net.wp_options                           OK
yoursite_net.wp_postmeta                          OK
yoursite_net.wp_posts                             OK
yoursite_net.wp_term_relationships                OK
yoursite_net.wp_term_taxonomy                     OK
yoursite_net.wp_termmeta                          OK
yoursite_net.wp_terms                             OK
yoursite_net.wp_usermeta                          OK
yoursite_net.wp_users                             OK
Success: Database checked.
^_^[www-data@yoursite.net:~/htdocs]$ wp db export
Success: Exported to 'yoursite_net-2021-05-17-2421c02.sql'.

The first command wp db check is not required but recommended, as it gives you a handy overview of all tables in the current database. The wp db export actually exports the database to a timestamped sql file, placed under the /opt/easyengine/sites/yoursite.net/app/htdocs/ directory.

What wp db export actually does is that it runs mysqldump utility under the hood. It’s successfully executed because WP-CLI, the executor, lives in the EasyEngine-powered Docker container, and has access to the sibling container that runs the MySQL database instance.

Restoring the database backup with WP-CLI

After we obtain the .mysql database backup file, using it to restore the database is easy. I recommend performing this procedure with WP-CLI also:

root@li1984-106:~# ee shell yoursite.net
^_^[www-data@yoursite.net:~/htdocs]$ wp db import yoursite_net-2021-05-17-2421c02.sql
Success: Imported from 'yoursite_net-2021-05-17-2421c02.sql'.

What if WP-CLI fails when the site isn’t up?

The WP-CLI method works 99% of the time, as long as BOTH the Nginx and MySQL Docker containers are up and running in our EasyEngine-powered WordPress site.

But what if they aren’t? What if something’s so wrong with our setup, that we can’t even get the server in a normal state?

Speaking from experience, I had the most horrific series of accidents a few weeks ago. My EasyEngine-powered WordPress server ran out of space; in a desperate measure to recover disk space, I did the one thing I should never do: I ran docker system prune --all --volumes --force. Yes, I wiped out both the perfectly running Nginx and MySQL docker containers, among many other things. As a result, I could not launch the site with ee site reload because the Docker volumes were missing.

So I manually created new volumes and container instances. This finally got ee site enable to succeed after many retries of jumpstarting the Nginx server. At this point the site was “accessible” but it threw 502 errors to all requests. The reason was, as I found out via docker ps -a, the Nginx container kept restarting itself every 30 seconds, never able to maintain a stable connection with its MySQL container sibling, hence the WordPress site could not be served to the end users.

Thus the WP-CLI method gave me the following error when the site wasn’t up:

root@li1984-106:~# ee shell yoursite.net
^_^[www-data@yoursite.net:~/htdocs]$ wp db export
Error: This does not seem to be a WordPress installation.
Pass --path=`path/to/wordpress` or run `wp core download`.

Do not fret. Let’s talk about the third, foolproof approach of backing up MySQL databases in a faulty or buggy EasyEngine-powered WordPress environment.

Backing up database with table files

When everything else fails, here’s our backup plan: we can still back up MySQL databases of a WordPress site by making a copy of the tables’ raw data. With EasyEngine, these files are located at /opt/easyengine/services/mariadb/data/yoursite_net, for example:

root@li1984-106:/opt/easyengine/services/mariadb/data/yoursite_net# ls -la
total 1692
drwx------ 2 systemd-coredump systemd-coredump   4096 May 14 16:49 .
drwxr-xr-x 5 systemd-coredump systemd-coredump   4096 May 14 16:48 ..
-rw-rw---- 1 systemd-coredump systemd-coredump     67 May 14 16:48 db.opt
-rw-rw---- 1 systemd-coredump systemd-coredump   3033 May 14 16:49 wp_commentmeta.frm
-rw-rw---- 1 systemd-coredump systemd-coredump 131072 May 14 16:49 wp_commentmeta.ibd
-rw-rw---- 1 systemd-coredump systemd-coredump   6679 May 14 16:49 wp_comments.frm
-rw-rw---- 1 systemd-coredump systemd-coredump 180224 May 14 16:49 wp_comments.ibd
-rw-rw---- 1 systemd-coredump systemd-coredump   8102 May 14 16:49 wp_links.frm
-rw-rw---- 1 systemd-coredump systemd-coredump 114688 May 14 16:49 wp_links.ibd
-rw-rw---- 1 systemd-coredump systemd-coredump   2854 May 14 16:49 wp_options.frm
-rw-rw---- 1 systemd-coredump systemd-coredump 147456 May 14 16:49 wp_options.ibd
-rw-rw---- 1 systemd-coredump systemd-coredump   3030 May 14 16:49 wp_postmeta.frm
-rw-rw---- 1 systemd-coredump systemd-coredump 131072 May 14 16:49 wp_postmeta.ibd
-rw-rw---- 1 systemd-coredump systemd-coredump   7211 May 14 16:49 wp_posts.frm
-rw-rw---- 1 systemd-coredump systemd-coredump 163840 May 14 16:49 wp_posts.ibd
-rw-rw---- 1 systemd-coredump systemd-coredump   3030 May 14 16:49 wp_termmeta.frm
-rw-rw---- 1 systemd-coredump systemd-coredump 131072 May 14 16:49 wp_termmeta.ibd
-rw-rw---- 1 systemd-coredump systemd-coredump   1496 May 14 16:49 wp_term_relationships.frm
-rw-rw---- 1 systemd-coredump systemd-coredump 114688 May 14 16:49 wp_term_relationships.ibd
-rw-rw---- 1 systemd-coredump systemd-coredump   3592 May 14 16:49 wp_terms.frm
-rw-rw---- 1 systemd-coredump systemd-coredump 131072 May 14 16:49 wp_terms.ibd
-rw-rw---- 1 systemd-coredump systemd-coredump   2209 May 14 16:49 wp_term_taxonomy.frm
-rw-rw---- 1 systemd-coredump systemd-coredump 131072 May 14 16:49 wp_term_taxonomy.ibd
-rw-rw---- 1 systemd-coredump systemd-coredump   3031 May 14 16:49 wp_usermeta.frm
-rw-rw---- 1 systemd-coredump systemd-coredump 131072 May 14 16:49 wp_usermeta.ibd
-rw-rw---- 1 systemd-coredump systemd-coredump   6962 May 14 16:49 wp_users.frm
-rw-rw---- 1 systemd-coredump systemd-coredump 147456 May 14 16:49 wp_users.ibd

Depends on the version of your EasyEngine setup, this may look different. The earlier versions of EasyEngine used the legacy MyISAM storage engine for the WordPress MySQL databases. Later versions updated them to the faster and more reliable InnoDB format. Above is what the InnoDB format looks like. And here’s a sample of the legacy MyISAM format files:

root@li1984-106:/opt/easyengine/services/mariadb/data/yoursite_net# ls -la
total 3168
drwx------ 2 systemd-coredump systemd-coredump    4096 May 14 17:50 .
drwxr-xr-x 5 systemd-coredump systemd-coredump    4096 May 14 17:47 ..
-rw-rw---- 1 systemd-coredump systemd-coredump      65 May 14 17:50 db.opt
-rw-rw---- 1 systemd-coredump systemd-coredump   13672 May 14 17:50 wp_commentmeta
-rw-rw---- 1 systemd-coredump systemd-coredump    3033 May 14 17:50 wp_commentmeta.frm
-rw-rw---- 1 systemd-coredump systemd-coredump   35828 May 14 17:50 wp_commentmeta.MYD
-rw-rw---- 1 systemd-coredump systemd-coredump   18432 May 14 17:50 wp_commentmeta.MYI
-rw-rw---- 1 systemd-coredump systemd-coredump    6681 May 14 17:50 wp_comments.frm
-rw-rw---- 1 systemd-coredump systemd-coredump   40144 May 14 17:50 wp_comments.MYD
-rw-rw---- 1 systemd-coredump systemd-coredump   17408 May 14 17:50 wp_comments.MYI
...

You get the idea. Instead of .ibd, MyISAM format uses .MYD and .MYI files to store the actual rows for the database tables.

Regardless of the storage engine format, in order to back up the database we simply take all the files in this directory and transfer them to the exact location of our desired database directory.

Now let’s talk about some details we should take care of to ensure a smooth database restoration.

Restoring the database backup with table files

If the files we copied and the destination WordPress database are of them same format (either they are both MyISAM or InnoDB), there’s nothing we need to do. Drop the files in the destination database directory – this could cause a potential override, fret not – and everything should be good to go.

What if the files we copied are of the legacy MyISAM format, and our destination WordPress database is built with InnoDB?

Firstly, let’s navigate to the /opt/easyengine/services/mariadb/data/yoursite_net database folder and make a backup copy of the current InnoDB files before we do anything. Now we can copy paste all the MyISAM files into the database folder.

Be aware that if we are operating this across different servers – meaning, our old and new WordPress databases are not on the same server – the MyISAM files would have the default permission of -rw-r--r-- owned by root from the root group. Look above and we’ll realize we need to fix it with chmod and chown:

root@li1984-106:/opt/easyengine/services/mariadb/data/yoursite_net# chown systemd-coredump:systemd-coredump *
root@li1984-106:/opt/easyengine/services/mariadb/data/yoursite_net# chmod 660 *
root@li1984-106:/opt/easyengine/services/mariadb/data/yoursite_net# ls -la
total 3168
drwx------ 2 systemd-coredump systemd-coredump    4096 May 14 17:50 .
drwxr-xr-x 5 systemd-coredump systemd-coredump    4096 May 14 17:47 ..
-rw-rw---- 1 systemd-coredump systemd-coredump      65 May 14 17:50 db.opt
-rw-rw---- 1 systemd-coredump systemd-coredump   13672 May 14 17:50 wp_commentmeta
-rw-rw---- 1 systemd-coredump systemd-coredump    3033 May 14 17:50 wp_commentmeta.frm
-rw-rw---- 1 systemd-coredump systemd-coredump   35828 May 14 17:50 wp_commentmeta.MYD
-rw-rw---- 1 systemd-coredump systemd-coredump   18432 May 14 17:50 wp_commentmeta.MYI
-rw-rw---- 1 systemd-coredump systemd-coredump    6681 May 14 17:50 wp_comments.frm
-rw-rw---- 1 systemd-coredump systemd-coredump   40144 May 14 17:50 wp_comments.MYD
-rw-rw---- 1 systemd-coredump systemd-coredump   17408 May 14 17:50 wp_comments.MYI
...

Now that the permission and owner properties match exactly to the old ones, great! However, if we jump out and look at the WordPress site in the browser, we may notice that it still has the old data. Seems like our database override hasn’t taken into effect yet.

This could be fixed with one extra step. Let’s first navigate to /opt/easyengine/services directory and get into the MySQL docker container instance with root access (instructions from EasyEngine documentation):

root@li1984-106:/opt/easyengine/services# docker-compose exec global-db bash -c 'mysql -u root -p${MYSQL_ROOT_PASSWORD}'
MariaDB [(none)]> USE yoursite_net;
MariaDB [yoursite_net]> CHECK TABLE wp_commentmeta;
+-----------------------------+-------+----------+----------+
| Table                       | Op    | Msg_type | Msg_text |
+-----------------------------+-------+----------+----------+
| yoursite_net.wp_commentmeta | check | status   | OK       |
+-----------------------------+-------+----------+----------+
1 row in set (0.000 sec)

Now that we are inside MySQL, we could check the status of all the tables, and more importantly, we can rebuild the .MYI files after the MYISAM files are placed in the directory.

According to the official MySQL documentation, this command would restore data from MYISAM tables:

MariaDB [yoursite_net]> REPAIR TABLE wp_commentmeta USE_FRM;
+-----------------------------+--------+----------+---------------------------------------------------------+
| Table                       | Op     | Msg_type | Msg_text                                                |
+-----------------------------+--------+----------+---------------------------------------------------------+
| yoursite_net.wp_commentmeta | repair | note     | The storage engine for the table doesn't support repair |
+-----------------------------+--------+----------+---------------------------------------------------------+

Do not worry about the Msg_text above, as long as you can see this, it means the table has been touched by the repair command.

Now let’s apply this command to all the tables in our WordPress database:

MariaDB [yoursite_net]> REPAIR TABLE wp_posts USE_FRM; REPAIR TABLE wp_commentmeta USE_FRM; REPAIR TABLE wp_comments USE_FRM; REPAIR TABLE wp_links USE_FRM; REPAIR TABLE wp_options USE_FRM; REPAIR TABLE wp_postmeta USE_FRM; REPAIR TABLE wp_posts USE_FRM; REPAIR TABLE wp_term_relationships USE_FRM; REPAIR TABLE wp_term_taxonomy USE_FRM; REPAIR TABLE wp_termmeta USE_FRM; REPAIR TABLE wp_terms USE_FRM; REPAIR TABLE wp_usermeta USE_FRM; REPAIR TABLE wp_users USE_FRM;

After all these commands, we should visit the WordPress site in a browser, and we’ll be greeted with the prompt screen that WordPress has detected a need to update the database. Click the button and the changes would take into effect. It’s that simple!

Congrats, you’ve successfully backed up and restored your WordPress database with EasyEngine!