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.

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 mysql-recovery.

# 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:

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 local_conf.d/mysql.cnf:

$ cat local_conf.d/mysql.cnf 

innodb_force_recovery = 5

The innodb_force_recovery flag is necessary for mysql to ignore that the ibdata1, *.frm, and *.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

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.