When I check my personal blog website, I found 500 error page, and a 500 error means internal server error, but last time I checked everything was working nicely and smoothly.One possible explanation is the database down, because it happened to me one time, but to be just sure, I started by checking log file and see if there's an error related to a database.
Seeing that log file that big made me regret why I didn't setup rotation setup for logs, but this for another blog.
A reverse log scan quickly revealed a null reference error when accessing a key, but this doesn't make any since to me since I use always null safety for all the code, I continued inspecting by checking my container that hold the database, looking at it, the container status is ok, but when I execute shell commands for the database, all I can see is one table for the backup where other tables appear to have been deleted, the error then originates from the database.
What is the root cause of this outcome? Writing this post a few weeks after deciding to containerize the database, I'm not 100% certain, but I suspect the container restarted (containers have limited lifecycles), which would explain the complete data loss. Turning to the Docker documentation, I found this [1]:
When a container starts, it uses the files and configuration provided by the image. Each container is able to create, modify, and delete files and does so without affecting any other containers. When the container is deleted, these file changes are also deleted. While this ephemeral nature of containers is great, it poses a challenge when you want to persist the data. For example, if you restart a database container, you might not want to start with an empty database. So, how do you persist files?
One of the solution mentioned in there documentation for avoiding that to happen in future, is by using container volumes:
Volumes are a storage mechanism that provide the ability to persist data beyond the lifecycle of an individual container. Think of it like providing a shortcut or symlink from inside the container to outside the container.
However, this introduces the headache of managing volumes and removing them. Therefore, I decided to use another container responsible for backing up my database. Here is a simple implementation:
db-backup:
image: mysql:8
container_name: db-backup
restart: unless-stopped
depends_on:
- mysql
environment:
MYSQL_ROOT_PASSWORD: {{placeholder}}
MYSQL_DATABASE: {{placeholder}}
MYSQL_USER: {{placeholder}}
MYSQL_PASSWORD: {{placeholder}}
volumes:
- ./backups:/backups
entrypoint: >
sh -c "while true; do
mysqldump -h mysql -u$MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE | gzip > /backups/backup_$(date +'%Y%m%d').sql.gz 2>> /backups/backup.log;
echo 'Backup done at $(date)' >> /backups/backup.log;
find /backups -type f -name '*.sql.gz' -mtime +30 -delete;
sleep 86400;
done"
networks:
- app
This container will create a fresh compressed backup every 24hours and cleaning up backups older then 30days .
While I'm writing this post, I remembered that I didn't setup a logic for the container that holding the database when will stopped because now I will loose my data, but I do have backup now.
To solve this, I did a logic that on every mysql container restart, a script waits until MySQL is ready, then checks if the database has tables , then it will skip restore, but if no tables found, automatically restores from latest backup in /backups.
I'm not proud of making these mistakes, but I'm sharing them so you don't have to. I learned the hard way that containers are not magic—they need care, configuration, and yes, backup logic. Hope this helps someone out there.
That's it. That's the post. Go set up your backups.