I started a mysql docker conainer and I plan to do phisical backups of the database periodically, just by copying the /var/lib/mysql volume. I've binded the volume to a host's folder like so:
version: '2.1'
services:
db:
image: mysql:5.6
restart: always
volumes:
- ./db/data:/var/lib/mysql
- ./db/config:/etc/mysql/mysql.conf.d/
environment:
- MYSQL_ROOT_PASSWORD=${ROOT_PASSWORD}
- MYSQL_DATABASE=db
- MYSQL_USER=user
- MYSQL_PASSWORD=${USER_PASSWORD}
ports:
- "3308:3306"
Of course, before starting to copy the volume the database must be in a read only mode:
FLUSH TABLES WITH READ LOCK;
After the copying is finished the read only mode must be turned off:
UNLOCK TABLES;
Is there any problem with that approach, as long as the database servers operate under Linux? I mean, if try to restore the database from a backup on a Linux system in future given my approach will it work?
My database is too big so restoring it through a logical backup (like generated with mysqldump) would take hours. I need a faster alternative.
In MYSQL's official documentation - dev.mysql.com/doc/refman/8.0/en/backup-methods.html they explain how to make a phisical backup. First FLUSH TABLES WITH READ LOCK and then copy the memory block with a snapshot tool. Copying and moving a docker's volume with the filesystem's tools like cp and rsync is similar to a snapshot, but yet not exactly the same. That's where my hesitation comes from.