paint-brush
Running Up MySQL export using Docker: A How-To Guideby@cveasey
4,898 reads
4,898 reads

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

by Clint VeaseyJanuary 2nd, 2022
Read on Terminal Reader
Read this story w/o Javascript
tldt arrow

Too Long; Didn't Read

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.

Companies Mentioned

Mention Thumbnail
Mention Thumbnail
featured image - Running Up MySQL export using Docker: A How-To Guide
Clint Veasey HackerNoon profile picture

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.