Running Up MySQL export using Docker: A How-To Guide

Imagine the scenario (or not) that you’ve got an old backup of a site sitting around. Code, imagery, and database exports. Now a colleague just wants to check an old order on that - now depreciated - system.

I’ll attempt to briefly explain how to get a MySQL database running locally, and then how to import your backup.

Run Up Local Database Server

I’ll be using docker as I believe it is the most convenient and OS-agnostic method.

Download docker from the official website:

https://www.docker.com

Once that is installed and running. Create a file (I’d suggest next to where you keep your MySQL backups) called docker-compose.yml.

Paste the following in:

version: '3.1'

services:
  db:
    image: mariadb:10.3
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: notSecureChangeMe

  phpmyadmin:
    image: phpmyadmin
    restart: always
    ports:
      - 8080:80

This will run up an instance containing two containers, one with MariaDB and the other PHPMyAdmin. You don’t need the PHPMyAdmin, but without being condescending I figured a GUI might be helpful for most people especially if you’re googling how to run up a MySQL backup!

Navigate to the directory of docker-compose.yml and run the command:

docker-compose up -d

After a few minutes your new docker instance should be running and accessible when you visit:

http://localhost:8080

Import Backup via PHPMyAdmin

From within PHPMyAdmin accessible via https://localhost:8080 by default you can use the GUI to import your backup.

Import a big file via PHPMyAdmin

The above will work for 90% of you, and hobbyists. However, if you’re sitting on 15 years worth of data like I was with a big fat 800MB+ MySQL file you might encounter some issues.

So in a nutshell we are going to increase the PHP timeout and max file upload limit of your local containers.

See below for a revised

version: '3.1'

services:
  db:
    image: mariadb:10.3
    restart: always

    volumes:    
    - /path/to/backupfiles/mysqlconf:/etc/mysql

    environment:
      MYSQL_ROOT_PASSWORD: notSecureChangeMe

  phpmyadmin:
    image: phpmyadmin
    restart: always
    ports:
      - 8080:80
    environment:
      UPLOAD_LIMIT: 1G

Within that same directory, I also create a directory called mysqlconf containing the file my.cnf.

Paste the below into my.cnf:

[mysqld]
max_allowed_packet = 1G
innodb_log_file_size = 2G
innodb_log_buffer_size = 512M
innodb_strict_mode = 0    
innodb_file_per_table=1
innodb_file_format = Barracuda

Import Backup directly from Command Line

If PHPMyAdmin is not playing then you can bypass it entirely and import via the command line.

Note: this is why I share a volume with the Host OS with the Docker Container above.

Access the cmd line of your docker container (the one running MariaDB, not PHPmyadmin) and import the file manually.

A GIST is available here:

Conclusion & Summary

1. Install Docker
2. Run up containers using docker-compose.yml
3. Import File - either via phpMyAdmin or Manually

I hope that explains things clearly.

If not, get in touch and I can amend the article.

I am serious. If you’re stuck or frustrated, I’d much prefer you to bother me than spend another X amount of house banging your head against your desk.

Here is my Labrador with eyebrows drawn on for some levity after that dry article.