记一次线上mysql时区错乱

Posted on | In
Words count in article: 716 | Reading time ≈ 3

Mysql查询时间和页面显示时间相差八个小时。

在一次线上程序调用mysql内部函数转化时间戳的时候的bug记录。在本地开发与测试环境都没得问题。但是上线后，程序总是不再状态。

遂开启审阅代码的过程，修bug轻轻松松耗费几个小时。最后在数据库中执行使用的原生sql语句，大概长这样：

1
2
(UNIX_TIMESTAMP(CURRENT_TIMESTAMP) - UNIX_TIMESTAMP(last_spider_time)
                      ) > 600

一直回显不出数据。

猜想应该是时间戳出现了问题。验证一下执行时间戳的内容。

1
2
3
4
SELECT

                    UNIX_TIMESTAMP(CURRENT_TIMESTAMP) ,
                    UNIX_TIMESTAMP('2019-08-28 18:30:35');

当前的时间已经大于18点，但是返回的18点的时间戳确实2点的时间戳。快了8个小时。

1
2
3
1567006237 

1567017035

在本地开发环境执行的结果是

这是一个正常的结果。

输出当前的时区

1
show variables like "%time_zone%";

开发测试环境结果为显示系统时间

1
2
3
4
5
6
7
8
9

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

服务器显示结果也为系统时间

1
2
3
4
5
6
7
8
mysql>  show variables like "%time_zone%";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | UTC    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

我们看下服务器系统的时区

date

发现是utc的时区。我们需要增加8个小时的时区了。

方案

更改系统时区，傻瓜式更改

1
tzselect

选择 4911

1
2
3
cp /usr/share/zoneinfo/Asia/Shanghai  /etc/localtime

ntpdate time.windows.com

重启

或者

设置时区更改为东八区

1
set global time_zone = ‘+8:00’;

刷新权限

1
flush privileges;

结果

时区正常

1
2
3
4
5
6
7
8
9
mysql> SELECT
    -> 
    ->                     UNIX_TIMESTAMP(CURRENT_TIMESTAMP) ,
    ->                     UNIX_TIMESTAMP('2019-08-28 18:30:35');
+-----------------------------------+---------------------------------------+
| UNIX_TIMESTAMP(CURRENT_TIMESTAMP) | UNIX_TIMESTAMP('2019-08-28 18:30:35') |
+-----------------------------------+---------------------------------------+
|                        1567009347 |                            1566988235 |
+-----------------------------------+---------------------------------------+

在docker中创建mysql容器，容器的时间是utc

在拉取mysql官方镜像的时候，创建的容器时间仍然是utc，解决的方式有

1、在创建容器的时候就设置容器的时区以及mysql的默认时区

1
2
# docker启动命令
docker run --name changle-mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=changle@1999 -e MYSQL_USER=user -e MYSQL_PASSWORD=user -e MYSQL_DATABASE=changle  -e TZ=Asia/Shanghai -d mysql:5.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --default-time_zone='+8:00'

参数说明

  • MYSQL_ROOT_PASSWORD ： 设置mysql数据库root的密码
  • MYSQL_DATABASE ： 启动时创建数据库
  • TZ=Asia/shanghai ： 设置容器时区
  • character-set-server ： 服务器字符集，在创建数据库和表时不特别指定字符集，这样统一采用character-set-server字符集。
  • character-set-database ： 数据库字符集
  • character-set-table ： 数据库表字符集
  • collation-server ： 排序规则字符集
  • default-time_zone ： mysql的时区

2、手动进入到容器内部、更改时区

记得重启容器

3、在不重启容器情况向下

可以直接将宿主机文件复制到容器内部

1
docker cp /etc/localtime [containerId]:/etc/localtime

查看容器的时间

1
docker exec [containerId] date