Docker MySQL restore

Before you begin

In this tutorial, we’ll learn how to restore a MySQL database. A machine with Docker installed and a database dump file will be required to follow this tutorial.

If you don’t have backup dump file you can have a look at this post to easily create a dump file from your MySQL database.

Restore backup to a local or remote MySQL server

Command to restore a local or remote MySQL server database using Docker:

$ cat backup.sql | docker run -i mysql \
  -h [MYSQL_HOST] -u [MYSQL_USER] \
  --password=[MYSQL_PASSWORD] [MYSQL_DATABASE]

Command to restore a local or remote MySQL database using Docker with compression (using gzip):

$ gunzip < backup.sql.gz | docker run -i \
  mysql /usr/bin/mysqldump \
  -h [MYSQL_HOST] -u [MYSQL_USER] \
  --password=[MYSQL_PASSWORD] [MYSQL_DATABASE]

Restore backup into a MySQL Server Docker container

Command to restore a database from plain SQL file:

$ cat backup.sql | docker exec -i [MYSQL_CONTAINER] \
  /usr/bin/mysql -u [MYSQL_USER] \
  --password=[MYSQL_PASSWORD] [MYSQL_DATABASE]

Command to restore a database from compressed gz file:

$ gunzip < backup.sql.gz | docker exec -i [MYSQL_CONTAINER] \
  /usr/bin/mysql -u [MYSQL_USER] \
  --password=[MYSQL_PASSWORD] [MYSQL_DATABASE]

Bonus track: Import a file into a MySQL table

We have a connection to a “remote” MySQL server with a database called company and a table employee. We are going to use a Docker container with IP 172.17.0.2 to simulate the remote connection. Finally, we need to insert data from a local txt file employee.txt(tab separated values) to the remote database using a local Docker client.

Use MySQL statement truncate table [company.]employee in case you need to delete existing values in table employee.

MySQL table:

create table employee
(
id int,
name varchar(15),
role varchar(10)
);

./data/employee.txt:

1  Jane Doe  Sysadmin
2  Mario Rossi  Developer
3  Zhang San  DBA

At this point we have four options to achieve previous objective:

1) Using MySQL LOAD DATA INFILE providing the file through a Docker volume. Example:

$ docker run -it -v $(pwd)/data:/tmp mysql:5.7 mysql -h 172.17.0.2 \
  company -e "LOAD DATA LOCAL INFILE '/tmp/employee.txt' INTO TABLE employee;SHOW WARNINGS"
2019-12-17 08:00:00
$ docker run -it mysql:5.7 mysql -h 172.17.0.2 company \
  -e "select count(1) from employee;"
+----------+
| count(1) |
+----------+
|        3 |
+----------+

2) Using MySQL LOAD DATA INFILE providing the file via STDIN. Example:

$ cat ./employee.txt | docker run -i mysql:5.7 mysql -h 172.17.0.2 \
  company -e "LOAD DATA LOCAL INFILE '/dev/stdin' INTO TABLE employee;SHOW WARNINGS"

$ docker run -it mysql:5.7 mysql -h 172.17.0.2 company \
  -e "select count(1) from employee;"
+----------+
| count(1) |
+----------+
|        6 |
+----------+

3) Using MySQL mysqlimport providing the file through a Docker volume. Example:

$ docker run -it -v $(pwd)/data:/tmp mysql:5.7 mysqlimport \
  -h 172.17.0.2 --verbose --local company /tmp/employee.txt
Connecting to 172.17.0.2
Selecting database company
Loading data from LOCAL file: /tmp/employee.txt into employee
company.employee: Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
Disconnecting from 172.17.0.2

$ docker run -it mysql:5.7 mysql -h 172.17.0.2 company \
  -e "select count(1) from employee;"
+----------+
| count(1) |
+----------+
|        9 |
+----------+

This option is less recommended because there is no SHOW WARNINGS parameter for mysqlimport. Although we can use the option --debug or --debug-info, these options depend on the remote MySQL server to have debug enabled.

4) Or doing a simple MySQL insert query. Example:

$ docker run -i mysql:5.7 mysql -h 172.17.0.2 \
  company -e "INSERT INTO employee VALUES ('10', 'Sven Svensson', 'CEO');"

$ docker run -it mysql:5.7 mysql -h 172.17.0.2 company \
  -e "select count(1) from employee;"
+----------+
| count(1) |
+----------+
|        10 |
+----------+

or even doing a MYSQL update to change an existing row, in this case we are changing the CEO of the company :)

$ docker run -i mysql:5.7 mysql -h 172.17.0.2 \
  company -e "UPDATE employee SET name='Janina Kowalska' where role='CEO';"

Finally, once we have all the rows inserted, then we can update our previous query in MySQL and use MySQL CONCAT() to concatenate various fields and provide a more human-readable result.

$ docker run -i mysql:5.7 mysql -h 172.17.0.2 \
  company -e "select CONCAT('Our', ' ', role, ' ', name, ' ',\
  'is ready for her first annual speech to employees.') example from employee\
  where role='CEO';"
+----------------------------------------------------------------------------+
| example                                                                    |
+----------------------------------------------------------------------------+
| Our CEO Janina Kowalska is ready for her first annual speech to employees. |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)


Recommended books to expand your MySQL knowledge:

Finally, you should definitely take a look at these books to fuel your Docker knowledge: