Restore MySQL database on bind-mounted directory for rancher/server

readme.md

Restore MySQL database on bind-mounted directory for rancher/server

When you run rancher/server with a bind-mounted directory, both processes (MySQL and cattle) are always running so it can be hard to restore a backup.

Backup

Creating a backup can be done using:

docker exec rancher_server_id mysqldump --default-character-set=utf8 --databases cattle > cattle.sql

This will save the database called cattle (default) to cattle.sql on the host.

Restore

1. Stop the rancher/server container using docker stop rancher_server_id
2. Run a mysql container mapping the same volume and the dump: docker run -d -v $PWD/cattle.sql:/cattle.sql -v /opt/docker/server/mysql-var:/var/lib/mysql mysql:5.5.53 --max-allowed-packet=67108864
3. Drop into a bash shell docker run -ti mysql_server_id bash
4. Drop into the mysql client mysql
5. Drop the database and create a new one DROP DATABASE cattle; and CREATE DATABASE IF NOT EXISTS cattle COLLATE = 'utf8_general_ci' CHARACTER SET = 'utf8';
6. Exit the mysql client exit
7. Restore the database mysql --default-character-set=utf8 cattle < /cattle.sql
8. Check the character set using mysql -e 'SHOW VARIABLES LIKE "character\_set\_database";' cattle, should say utf8.
9. Check the collation using mysql -e 'SHOW VARIABLES LIKE "collation\_database";' cattle, should say utf8_general_ci.
10. Exit the bash shell using exit
11. Stop the mysql container docker stop mysql_server_id
12. Chown the host directory to the correct user chown -R 102:105 /opt/docker/server/mysql-var
13. Start the rancher server mapping this volume as /var/lib/mysql: docker run -d -p 8080:8080 -v /opt/docker/server/mysql-var:/var/lib/mysql rancher/server:v1.6.10

Your article saved my job's life! Thanks.