Configure time zone to mysql docker container

Question

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?

Probably duplicate of https://stackoverflow.com/questions/45587214/configure-timezone-in-dockerized-nginx-php-fpm/45587945#45587945? — Tarun Lalwani, Apr 24 '18 at 09:46
It's not duplicate to that question. I don't want to set doker container's time, but prefer to set the mysql time in a .cnf file, just as I was able to do that with the PHP in it's .ini file. — vivanov, Apr 24 '18 at 10:07

score 53 · Accepted Answer · answered Apr 24 '18 at 12:31

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.

Thanks a lot for the solution, it works! I will be allowed to reward the bounty in "17 hours", and I will do. I wanted to have the most elegant and simple solution, because I am not an advanced linux user neither I am advanced in docker... but in this case I'll have some few new things to learn :) — vivanov, Apr 24 '18 at 14:26
After a lot of attempts, trying to bypass using a separate Dockerfile, I gave up and used the first solution which involves a Dockerfile built with docker-compose. Thanks for the solution! — Constantin, Sep 28 '19 at 20:41

score 9 · Answer 2 · answered Feb 16 '21 at 17:33

9

The mariadb:10.3 docker container interprets the TZ environment variable, although it's not mentioned on the Readme.md for the docker image. In my case providing the value of America\New_York worked perfectly.

answered Feb 16 '21 at 17:33

Paul Wieland

765
2
10
29

3

Yup, that worked for me too. It now mentions under the `MARIADB_INITDB_SKIP_TZINFO` / `MYSQL_INITDB_SKIP_TZINFO` variable: "By default, the entrypoint script automatically loads the timezone data needed for the CONVERT_TZ() function. If it is not needed, any non-empty value disables timezone loading." so loading the tz data like in the accepted answer isn't necessary with recent mariadb images, and just setting the TZ variable is enough. – Dario Seidl Aug 01 '22 at 10:11

score 6 · Answer 3 · answered Apr 24 '18 at 12:04

value should be

default_time_zone=Europe/Sofia

check details

But this could give you error like this while restarting the mysql service so you should use below command before editing custom.cnf file.

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p

score 4 · Answer 4 · answered Mar 25 '20 at 05:08

4

For me, I'm using Keramatic https://kitematic.com configure by click on MySQL container General and add TZ specify with your current time show list in https://en.wikipedia.org/wiki/List_of_tz_database_time_zones

Cheers

answered Mar 25 '20 at 05:08

Chea Sambath

1,305
2
13
16

Configure time zone to mysql docker container

4 Answers4