Adventure in restoring WordPress to a Docker container

Today's adventure was harrowing, fraught with errors, and shouts of victory!

Backup

First, I downloaded a backup of my site. The backup included the database and items in wp-content folder (e.g. plugins, themes, and uploads). My backup was compressed into a single file, I extracted the files locally on my machine.

Docker-compose

I followed the quickstart to create my containers for WordPress. After typing docker-compose up I completed the install of WordPress. I now had a clean install to restore my site into.

Plugins, themes, and uploads

Restoring everything in the wp-content folder was a snap. I copied everything over from my download making sure the folder structure was correct. WordPress picked up the themes and plugins right away.

Database

Each table in my database was a separate .sql file. Instead of having to keep track of all the files and import them in the correct order I concatenated them together into a single file.

cat *.sql > backup_db.sql

I now had a problem. How could I transfer this file into my MySQL container?

Volumes

To make my life easier, I decided on creating a new volume for my MySQL container.

I created a local folder called "sql" and added the mapping to a new folder "sql-backup" in my docker-compose.yml file.

  db:
    image: mysql:5.7
    volumes:
      - db_data:/var/lib/mysql
      - ./sql:/sql-backup

I chose "sql-backup" for the folder name as I knew it wouldn't already be in my MySQL container and would not conflict with anything. Docker would create it on the next restart and map anything I had in "sql" to it.

I added the backup_db.sql file to my "sql" folder and restarted my containers.

MySQL

I logged into the MySQL container and confidently entered the following command:

mysql -u root -p wordpress < /sql-backup/backup_db.sql

I entered the root password and then it threw an error!

ERROR 1067 (42000) at line 21

I looked it up and realized quickly that I was dealing with new MySQL defaults. I quickly found a blog with reasonable steps.

I grabbed my sql_mode variables and came to a new problem, most Docker images do not have editors installed.

Containers are suppose to be able to be quickly rebuilt and thus shouldn't need an editor. I didn't want to rebuild mine in order to do the import, so I cheated and installed one.

Apt

Thankfully the MySQL image had apt installed, after a quick update I installed vim and was on my way.

apt update
apt install vim

I edited my config file, restarted the service, and tried again only to get a new error.

ERROR 1215 (HY000): Cannot add foreign key constraint

Foreign keys

The good thing about error 1215 is that it is common and there are many blogs and articles on how to fix it. The bad part is there is around 12 different things that can be wrong. It is a very generic error for MySQL.

I logged back into the MySQL container, looked up once again how to switch databases (it is USE DATABASENAME), and ran SHOW TABLES. I got error 1146 which confirmed that the tables foreign keys were referencing were being created in the wrong order.

I opted to disable foreign key checks instead of hunting through all the tables and creating them in the right order. (See #1 in this list for the commands)

And it worked!