3
4

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.

MySQLのtime_zoneを設定する方法

Posted at

Windows以外はいつかやった時に書く・・・つもり

Windows

本当はsystem_time_zoneをJSTに設定したかったがわからず・・・

設定前

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone |        |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set, 1 warning (0.03 sec)

方法

  1. MySQL :: Time zone description tablesを表示する
  2. [POSIX standard]のzipファイルをダウンロードする
    • 今回ダウンロードしたのはtimezone_2020b_posix_sql.zip
  3. zipファイルを任意の場所に解凍する
  4. zipから出てきたtimezone_posix.sqlをデータベースmysqlに対して実行する
    • うっかり自分が使っているDBを指定するとERROR 1146 (42S02) at line 1: Table 'mydb.time_zone' doesn't existと怒られる
  5. my.iniの[mysqld]default-time-zone = 'Asia/Tokyo'を追記する
  6. MySQLを再起動する
# zipファイルを任意の場所に解凍する
$ unzip /c/Users/ponsuke/Downloads/timezone_2020b_posix_sql.zip -d /c/apps/mysql-8.0.19-winx64/
Archive:  /c/Users/ponsuke/Downloads/timezone_2020b_posix_sql.zip
   creating: /c/apps/mysql-8.0.19-winx64/timezone_2020b_posix_sql/
  inflating: /c/apps/mysql-8.0.19-winx64/timezone_2020b_posix_sql/timezone_posix.sql

# zipから出てきたtimezone_posix.sqlを実行する(GitBashは対話できないのでパスワードを直指定)
$ mysql -u root -D mysql -ppassword < /c/apps/mysql-8.0.19-winx64/timezone_2020b_posix_sql/timezone_posix.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

# my.iniに追記する
$ cat /c/apps/mysql-8.0.19-winx64/my.ini
[mysqld]
default-time-zone = 'Asia/Tokyo'
# ...省略...

# MySQLを再起動する
$ sc stop MySQL80
SERVICE_NAME: MySQL80
        TYPE               : 10  WIN32_OWN_PROCESS
        STATE              : 3  STOP_PENDING
                                (STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN)
        WIN32_EXIT_CODE    : 0  (0x0)
        SERVICE_EXIT_CODE  : 0  (0x0)
        CHECKPOINT         : 0x1
        WAIT_HINT          : 0x5265c00
$ sc start MySQL80
SERVICE_NAME: MySQL80
        TYPE               : 10  WIN32_OWN_PROCESS
        STATE              : 2  START_PENDING
                                (NOT_STOPPABLE, NOT_PAUSABLE, IGNORES_SHUTDOWN)
        WIN32_EXIT_CODE    : 0  (0x0)
        SERVICE_EXIT_CODE  : 0  (0x0)
        CHECKPOINT         : 0x3
        WAIT_HINT          : 0x3a98
        PID                : 7756
        FLAGS              :

# GitBashを使っているのでwinptyをつけて接続
$ winpty mysql -u ponsuke -D mydb -p
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.19 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 |            |
| time_zone        | Asia/Tokyo |
+------------------+------------+
2 rows in set, 1 warning (0.01 sec)
3
4
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
3
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?