I have a mysql 5.7 docker container. When I run the mysql command:
SELECT now();
It shows the time -3 hours to my current time (which is logical). I want to set the time zone in a config file. Following the documentation in https://hub.docker.com/_/mysql/ I create a volume in my docker-compose.yml
file like the following:
mysqldb:
image: mysql:5.7.21
container_name: mysql_container
ports:
- "3306:3306"
volumes:
- ./.docker/etc/mysql/custom.cnf:/etc/mysql/conf.d/custom.cnf
When I browse the files inside the container the file custom.cnf
is there.
In that file, I tried some of the ways I found as solutions like:
[mysqld]
default_time_zone='Europe/Sofia'
or a compromise solution which is less elegant as the zone will have to be changed twice a year (summer / winter):
[mysqld]
default_time_zone='+03:00'
but none works. I have the sensation the this file is not loaded by mysql at all because if I try to put invalid configuration in there nothing happens either (the container starts normally). Any suggestions on that?
So you need to use a Dockerfile
in this case and handle it like below
FROM mysql:5.7.21
RUN echo "USE mysql;" > /docker-entrypoint-initdb.d/timezones.sql && mysql_tzinfo_to_sql /usr/share/zoneinfo >> /docker-entrypoint-initdb.d/timezones.sql
This makes sure that when the mysql container loads it will load all the timezone info. Now you can use it using environment variables
Environment variable
mysqldb:
#image: mysql:5.7.21
build: .
#container_name: mysql_container
ports:
- "3306:3306"
environment:
- MYSQL_ROOT_PASSWORD=root
- TZ=Europe/Sofia
To use it with a config file is a problem. When you start the DB it will give your an error
mysqldb_1 | 2018-04-24T12:29:43.169214Z 0 [Warning] InnoDB: New log files created, LSN=45790
mysqldb_1 | 2018-04-24T12:29:43.215187Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
mysqldb_1 | 2018-04-24T12:29:43.281229Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 2a87fec6-47bb-11e8-9f1e-0242ac110002.
mysqldb_1 | 2018-04-24T12:29:43.284010Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
mysqldb_1 | 2018-04-24T12:29:43.284404Z 0 [ERROR] Fatal error: Illegal or unknown default time zone 'Europe/Sofia'
mysqldb_1 | 2018-04-24T12:29:43.284567Z 0 [ERROR] Aborting
This is because it needs the timezone to have been already loaded. It is possible to fix this also, but too much of hassle. I will go with the environment variable only as that means when the container starts the timezone is already setup.