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?

Hedvig

Probably duplicate of #45587714? – Farsighted

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. – Hedvig

Answer 1

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.

Farsighted

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 :) – Hedvig

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! – Placet

Answer 2

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.

Pantaloon

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. – Berube

Answer 3

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

Boll

Answer 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

Chirography

Answer 5

In my docker-compose config i simply added TZ env var for all containers and they all started to work in one timezone. No extra mysql configuration was needed in my case.

Here is my full docker-compose.yaml file:

version: "3.8"

services:
  www:
    build:
        context: .
        dockerfile: ./build/app/Dockerfile
    ports:
      - "8001:80"
    volumes:
      - ./:/var/www/html/
    links:
      - db
    networks:
      - default
    environment:
      TZ: "Europe/Minsk"
  db:
    platform: linux/x86_64
    image: mysql:5.7.36
    ports:
      - "3306:3306"
    command: --sql_mode=""
    environment:
      MYSQL_DATABASE: bigboomb_bw
      MYSQL_PASSWORD: mysql
      MYSQL_ROOT_PASSWORD: mysql
      TZ: "Europe/Minsk"
    volumes:
      - ./mysql-data:/var/lib/mysql
    networks:
      - default
  phpmyadmin:
    image: phpmyadmin/phpmyadmin
    links:
      - db
    ports:
      - 8000:80
    environment:
      MYSQL_USER: root
      MYSQL_PASSWORD: mysql
      MYSQL_ROOT_PASSWORD: mysql
      UPLOAD_LIMIT: 64M
      TZ: "Europe/Warsaw"

Rich