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

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

How to get a MySQL database running locally, and then how to import your backup. I’ll be using docker as I believe it is the most convenient and OS agnostic method. This will run up an instance containing two containers, one with MariaDB and one with.phpmyadmin**. You don’t need the **phpmy admin**, but without being condescending I figured a. GUI might be helpful for most people. However if you’re sitting on 15 years worth of data like I was with a big fat 800MB+ database file you might encounter some issues.
image
Clint Veasey HackerNoon profile picture

Clint Veasey

From stacking tents for the airforce to working as a full stack developer.

instagram social iconyoutube social iconfacebook social icongithub social icon

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.


image

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 :

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.


image

react to story with heart
react to story with light
react to story with boat
react to story with money
L O A D I N G
. . . comments & more!