Recovering MonicaHQ mysql Database

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_mysql and /var/lib/docker/volumes/monica_data were copied. This "backup" contains ibdata1, ib_logfile0, as well as the /monica directory full of .frm and .ibd files.

If mysqldump had been used as the backup method the process would be straightforward.

Fortunately, it is possible to recover the directory with a new mysql instance with the innodb_force_recovery flag set.

$ podman run --rm -it --name=mysql-recovery --env="MYSQL_ROOT_PASSWORD=sekret_root_password" -v /path/to/backup/mysql:/var/lib/mysql docker.io/library/mysql:5.7 --innodb_force_recovery=5

Once the container is running, it is possible to properly back up a mysql database with an InnoDB storage engine:

$ podman exec mysql-recovery /usr/bin/mysqldump --skip-lock-tables -u root --password=sekret_root_password monica > monica_backup.sql

Now, the 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 mysqldump!

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.