MonicaHQ released 2.17 for self-hosting and it seemed like a fairly straightforward upgrade (
docker-compose pull; docker-compose restart) up until logging into the updated instance presented a gut-wrenching:
The whole database is gone!
As called out in the release notes, the monica mysql database was backed up, but not backed up properly. The contents of
/var/lib/docker/volumes/monica_data were copied. This "backup" contains
ib_logfile0, as well as the
/monica directory full of
mysqldump had been used as the backup method the process would be straightforward.
The first step for database recovery is start a new
mysql instance to get a copy of the mysql configuration files, which need modified and remounted:
# docker images REPOSITORY TAG IMAGE ID CREATED SIZE monicahq/monicahq latest 48faa163cf90 2 weeks ago 392MB mysql 5.7 84164b03fa2e 4 weeks ago 456MB $ docker run --name=mysql-recovery -d --env="MYSQL_ROOT_PASSWORD=sekret_root_password" mysql:5.7
A docker container based on the
mysql:5.7 image is now spun up named
# docker ps CONTAINER ID IMAGE ... NAMES 97afedbae569 mysql:5.7 ... mysql-recovery
In order to recover the database, the following block needs to exist in one of the numerous
mysql configuration files:
[mysqld] innodb_force_recovery = 5
An easy target file to modify is
/etc/mysql/conf.d/mysql.cnf. Copy the
/etc/mysql/conf.d directory to enable local editing:
# docker cp mysql-recovery:/etc/mysql/conf.d local_conf.d
Modify the fresh
$ cat local_conf.d/mysql.cnf [mysql] [mysqld] innodb_force_recovery = 5
The innodb_force_recovery flag is necessary for mysql to ignore that the
*.ibd files were copied from another instance.
With the files copied and modified, take the existing
mysql-recovery instance down to bring it up with the modified configuration files and database files mounted as volumes:
# docker stop mysql-recovery # docker rm mysql-recovery # docker run --name=mysql-recovery -d --env="MYSQL_ROOT_PASSWORD=sekret_root_password" -v /path/to/monica/mysql/files:/var/lib/mysql -v /path/to/local_conf.d/:/etc/mysql/conf.d mysql:5.7
Once the container is running, it is possible to properly back up a mysql database with an InnoDB storage engine:
# docker exec mysql-recovery /usr/bin/mysqldump --skip-lock-tables -u root --password=sekret_root_password monica > monica_backup.sql
monica_backup.sql can be used to restore the database in the monica mysql docker container. First, copy it into the
monica_mysql_1 docker container:
# docker cp monica_backup.sql monica_mysql_1:/home/monica_backup.sql
Launch a mysql shell in the mysql docker instance and source the database file:
# docker exec -it monica_mysql_1 mysql -u root --password=sekret_root_password ... mysql> use monica; mysql> source /home/monica_backup.sql
Once the import finishes, the contacts from the original database are visible!
mysql> select first_name from contacts; +------------+ | first_name | +------------+ | Brian | | Dennis | | ... | +------------+
If no contacts appear in the monica web interface, restart the monica docker instance and remember to back up the mysql database using
Did this post just recover years worth of contact information? Consider saying thanks by using my Amazon Affilliate URL and help to keep this site ad & analytics free.