12
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

DockerのMySQLコンテナのタイムゾーンについて

Posted at

やりたいこと。
DockerのMySQLコンテナのタイムゾーンをAsia/Tokyoにしたい。

docker-compose.yml
version: '3'
services:
  db:
    image: mysql:5.7
    container_name: db
    environment:
      MYSQL_ROOT_PASSWORD: rootpass
    command: mysqld
    volumes:
      - ./data:/var/lib/mysql:cached
      - ./my.cnf:/etc/mysql/conf.d/my.cnf
    ports:
      - 3306
my.cnf
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_bin
default-time-zone='Asia/Tokyo'

[client]
default-character-set=utf8mb4
$ docker-compose up -d
Creating network "dbtest_default" with the default driver
Creating db ... done
$ docker logs db
2020-04-24 08:47:21+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.29-1debian10 started.
2020-04-24 08:47:21+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2020-04-24 08:47:21+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.7.29-1debian10 started.
2020-04-24 08:47:22+00:00 [Note] [Entrypoint]: Initializing database files
2020-04-24T08:47:22.068120Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-04-24T08:47:22.732045Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-04-24T08:47:22.872613Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-04-24T08:47:22.939450Z 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: 3708a0f4-8608-11ea-b8d6-0242ac1c0002.
2020-04-24T08:47:22.942865Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-04-24T08:47:24.152354Z 0 [Warning] CA certificate ca.pem is self signed.
2020-04-24T08:47:24.396094Z 0 [ERROR] Fatal error: Illegal or unknown default time zone 'Asia/Tokyo'
2020-04-24T08:47:24.396238Z 0 [ERROR] Fatal error: Failed to initialize ACL/grant/time zones structures or failed to remove temporary table files.
2020-04-24T08:47:24.396283Z 0 [ERROR] Aborting

タイムゾーンのエラーっぽい。

$ docker-compose down
Removing db ... done
Removing network dbtest_default
$ rm -rf data

my.cnfのdefault-time-zoneをコメントアウトしてみる。

my.cnf
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_bin
#default-time-zone='Asia/Tokyo'

[client]
default-character-set=utf8mb4
$ docker-compose up -d
Creating network "dbtest_default" with the default driver
Creating db ... done

正常にコンテナが起動しました。
コンテナにログインしてMySQLのタイムゾーンを確認します。

$ docker-compose exec db bash
# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | UTC    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

これをAsia/Tokyoに変えたい。
my.cnfのコメントアウトを外します。

my.cnf
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_bin
default-time-zone='Asia/Tokyo'

[client]
default-character-set=utf8mb4

dataは残したままコンテナを再起動します。

$ docker-compose down
Stopping db ... done
Removing db ... done
Removing network dbtest_default
$ docker-compose up -d
Creating network "dbtest_default" with the default driver
Creating db ... done
$ docker-compose exec db bash
# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%time_zone%';
+------------------+------------+
| Variable_name    | Value      |
+------------------+------------+
| system_time_zone | UTC        |
| time_zone        | Asia/Tokyo |
+------------------+------------+
2 rows in set (0.01 sec)

変わったは変わったけれど、上記のような複雑な手順になってしまうのは何故だろう。
一度タイムゾーンの設定なしで起動してから、設定ありで再起動しないとうまくいかない。
タイムゾーンのテーブルがないからっぽいが、一度の起動でタイムゾーン変更までいくにはどうすれば??
それともどこかで自分間違っているのだろうか:thinking:

12
6
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
12
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?