General Questions about using MySQL with Docker, logging options

Question

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.

1. 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.

2. 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.

3. 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.

4. 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.

Answer 1

(The Question asks too many things. I will give an overview of the answers. Please start a new, more specific, Question if this info below does not provide sufficient information. But first, do some research with the tips I am giving.)

binlog - ON
general log - OFF
slowlog - see below

I would not trust a file backup without stopping mysqld during the backup. There is too much going on in RAM to trust what you can find on disk. At least the server needs to be quiesced during the dump. (That is best done by simply stopping mysqld.)

A backup being in the same building is not safe from a serious "disaster".

There are ways to use the binlog for 'incremental' dumps. But you will need a 'full' dump periodically.

The "general log" fills up the disk very fast; use it only for certain debugging tasks. The "slowlog" can (in general) be left on all the time, with long_query_time = 1. But it is not useful for disaster recovery.

I prefer to put most settings in a "my.cnf" config file, not on the mysqld command line.

The general log records all queries. The binlog records all writes. Each is oblivious to the intent of the queries.

In Linux, you can concoct filenames in a script. See the options available in the date command. Yes, Linux has "variables" in shell scripts. And, yes, it makes sense to set --general-log-file on the commandline instead of my.cnf. But, remember, the general log is not a good tool for backups or disaster protection.

See also SET GLOBAL VARIABLE PERSIST... as a way to make a change without restarting mysqld.

See also FLUSH LOGS; as a way to stop one log file and start another.

To automatically purge 'old' binlogs, set ...expire... to flush after a couple of weeks.