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.
I’ll be using docker as I believe it is the most convenient and OS-agnostic method.
Download docker from the official website:
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:
From within PHPMyAdmin accessible via https://localhost:8080 by default you can use the GUI to import your backup.
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
Paste the below into
[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
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 :
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.