I have a number of questions about logging / backup strategies when using MySQL with Docker specifically and also just general questions.
The particular scenario is MySQL DB's for a small medical clinic, and the current arrangement apparently is using a number of methods to backup data. The host system is UBUNTU, and the application files and data run almost exclusively via a Docker package located on the RAID for the system, so the MySQL engine is docker image: mysql:8.0.26.
A Cloud back-up service that does incremental cloud back-ups of the entire docker-compose project folder, including the DB files that are mapped to volumes on the host. This is intended to be a last resort for disaster recovery. Never actually been tested as a dry run in a mock scenario, like a hurricane or a fire.
CRON job scripts that do daily DB dumps that are thinned on a rotating basis, via a CRON script running from the host. These are stored on the host system SSD as well as well as to a locally located NAS device.
Docker mysql:8.0.26 apparently has bin logging enabled by default, so that has been active, although I think they rotate out every 30 days because the default setting are in effect, and I don't think anyone has ever really looked at those or used them.
General logging was turned off (by default apparently).
It looks general logging can be turned on in docker-compose using:
command: --general-log=TRUE --general-log-file=/var/lib/mysql/mysql-log.log
and one can apparently get a list of available options for docker using:
docker run -it --rm mysql:tag --verbose --help
There is a reference on this site: MySQL Logging Options
that covers some of the logging options for MySQL (below).
Just kind of wondering what the best approach might be given that the DB for the application already keeps some dedicated tables to document some of the more sensitive DB transactions (i.e. who changed what and when, with a user_id, ip in cases, etc.) Seems like the general log and the binlog would not have that information ? A better approach might be to just disable bin logging (no replication servers currently) and leave general logging off as long as there are tables in the DB to record similar information in a more detailed manner ?
I was looking at the existing binlog files and some of those are quite large because some of the tables have blobs, and the same would likely be true if general logging were enabled.
There have been very few issues to date with DB errors, lost data, etc., and it seems like the Cloud backup and the local DB dumps should be sufficient in most cases for now. The dumps have been tested multiple in terms of data integrity and the ability to restore quickly from a dump (currently about 500 MB uncompressed).
So main questions:
1. Is there a way in the compose file to have it add a date for the mysql-log.log filename ?, using --general-log-file=/var/lib/mysql/mysql-log.log and maybe an ENV variable or something ?
2. Is it really necessary to use the binary logs and the general logs.
I have a development system where I can play around with all of that. I'm a little hesitant to turn off binary logging and then just deleting the binlog files themselves, but I guess I can just try on the dev system and see what happens. The would free up quite a bit of space and remove some of the clutter.