Modification of time zone problem of MySQL container in Docker

Preface

When Ahhang was developing the Springboot project, the front-end told him that the verification code was always invalid. There was no problem with the local test, but when I looked at the database time of the remote server, wow - it was 8 hours early. Obviously, it was a MySQL time zone problem. This article will record how to change the time zone of the MySQL container in Docker.

Solution

Let's first verify whether the database time zone is really incorrect. Enter the MySQL database and run the statement:

SELECT NOW();

Will return data similar to this:

mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2020-07-04 15:46:46 |
+---------------------+
1 row in set (0.09 sec)

To check the current time zone, enter the following command:

SHOW VARIABLES LIKE '%time_zone%';

Will return data similar to this:

mysql> SHOW VARIABLES LIKE '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | UTC |
| time_zone | +00:00 |
+------------------+--------+
2 rows in set (0.12 sec)

If the returned time is several hours different from yours, and the time zone is incorrect (not +08:00), then it means you need to look down.

Method 1: Temporary modification

When our needs are urgent, we can make this temporary modification. Run the command:

SET GLOBAL time_zone = '+8:00';

Run the following command again to verify that the returned result is the current time:

SELECT NOW();

Returning the current time proves that the modification was successful.

The reason why this method is called "temporary modification" is that the modification will become invalid after restarting MySQL.

Method 2: Add parameters at startup

This method applies to conditions that allow us to recreate a MySQL container.

When creating a container, we need to add a command to specify the time zone (Eastern Time Zone 8 is Shanghai, you can change it to your own time zone as needed):

-e TZ=Asia/Shanghai

So, our complete docker run command should be (for reference only, your run command may be slightly different):

docker run -p 3306:3306 --name mymysql -v $PWD/conf:/etc/mysql/conf.d -v $PWD/logs:/logs -e TZ=Asia/Shanghai -v $PWD/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0

To see the purpose of each parameter, please check out my article: Install MySQL with Docker.

Method 3: Modify the configuration in the container

Enter the mysql container by typing:

docker exec -it container ID bash

Modify the MySQL configuration file (two cases):

vim /etc/mysql/mysql.conf.d

or

vim /etc/mysql/my.cnf

If the above command returns bash: vim: command not found, please read the solution of no vim command in Docker container first.

After entering the configuration file, click i to enter the edit mode and add a line of configuration file:

default-time-zone = '+08:00'

As shown:

Add a profile

When finished, press ESC and enter :wq to save and exit.

Then enter exit to exit the docker container.

Next we need to restart the mysql container, enter the command:

docker restart container id

So far, the time zone configuration has been successfully modified.

After restarting, enter the following command to verify success:

SELECT NOW();

If the returned time is consistent with the current time, the modification is successful.

Conclusion

If you are in a hurry, we recommend method 1. If you are not in a hurry, we recommend method 2. Please choose the one you need.

This is the end of this article about how to fix the time zone issue in Docker's MySQL container. For more information about Docker's MySQL container time zone, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!