MySQL中几个关于时间/时区的变量

一、log_timestamps

1.1、官方解释

log_timestamps: Log timestamp format. Added in MySQL 5.7.2.
This variable controls the timestamp time zone of error log messages, and of general query log and slow query log messages written to files. It does not affect the time zone of general query log and slow query log messages written to tables.
Permitted log_timestamps values are UTC (the default) and SYSTEM (local system time zone).

简单来说log_timestamps变量控制着error log、general log、slow log这些文件中的时间信息。log_timestamps有效的值为UTC(默认)和SYSTEM(本地系统时区)

1.2、使用样例

可以使用下面方式设置log_timestamps

# 启动命令
--log_timestamps=[UTC|SYSTEM]
# 配置文件
log_timestamps=[UTC|SYSTEM]
# 运行期间
set global log_timestamps = ['UTC'|'SYSTEM']

查看并修改log_timestamps的值

# 查看log_timestamps
mydba@192.168.85.132,3306 [replcrash]> show variables like '%log_timestamps%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| log_timestamps | UTC   |
+----------------+-------+
row in set (0.01 sec)
# 设置log_timestamps为system
mydba@192.168.85.132,3306 [replcrash]> set global log_timestamps = 'system';
Query OK, 0 rows affected (0.03 sec)

# 默认UTC时间的日志信息
2018-01-10T02:45:37.093284Z 21 [Note] Access denied for user 'mydba'@'192.168.85.132' (using password: YES)
# 设置log_timestamps为system(东八区)的日志信息
2018-01-10T10:46:07.031340+08:00 22 [Note] Access denied for user 'mydba'@'192.168.85.132' (using password: YES)

可以看到日志中时间格式为：YYYY-MM-DDThh:mm:ss.uuuuuu+Z(UTC)/±hh:mm(相对UTC的偏移量)

二、time_zone

2.1、官方解释

The current time zone. This variable is used to initialize the time zone for each client that connects. By default, the initial value of this is 'SYSTEM' (which means, “use the value of system_time_zone”). 

2.2、使用样例

可以使用下面方式设置global server time zone

# 启动命令
--default-time-zone=timezone
# 配置文件
default-time-zone=timezone
# 运行期间
set global time_zone = timezone

timezone values can be given in several formats, none of which are case sensitive:
• The value 'SYSTEM' indicates that the time zone should be the same as the system time zone.
• The value can be given as a string indicating an offset from UTC, such as '+10:00' or '-6:00'.
• The value can be given as a named time zone, such as 'Europe/Helsinki', 'US/Eastern', or 'MET'. Named time zones can be used only if the time zone information tables in the mysql database have been created and populated.

查看并修改time_zone的值

# 查看MySQL当前时区、时间
mydba@192.168.85.132,3306 [replcrash]> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.01 sec)

mydba@192.168.85.132,3306 [replcrash]> select now();
+---------------------+
| now()               |
+---------------------+
| 2018-01-10 11:29:25 |
+---------------------+
1 row in set (0.00 sec)
time_zone说明MySQL使用system的时区，system_time_zone说明system使用CST时区


# 修改MySQL全局时区为西八区
mydba@192.168.85.132,3306 [replcrash]> set global time_zone = '-8:00';
Query OK, 0 rows affected (0.00 sec)
# 修改当前会话时区
mydba@192.168.85.132,3306 [replcrash]> set time_zone = '-8:00';
Query OK, 0 rows affected (0.00 sec)
# 查看MySQL当前时区、时间
mydba@192.168.85.132,3306 [replcrash]> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | -08:00 |
+------------------+--------+
2 rows in set (0.02 sec)

mydba@192.168.85.132,3306 [replcrash]> select now();
+---------------------+
| now()               |
+---------------------+
| 2018-01-09 19:31:15 |
+---------------------+
1 row in set (0.00 sec)

# 当前的time_zone
mydba@192.168.85.132,3306 [replcrash]> select @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| -08:00             | -08:00              |
+--------------------+---------------------+
1 row in set (0.00 sec)

The current session time zone setting affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as NOW() or CURTIME(), and values stored in and retrieved from TIMESTAMP columns. Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval.
The current time zone setting does not affect values displayed by functions such as UTC_TIMESTAMP() or values in DATE, TIME, or DATETIME columns. Nor are values in those data types stored in UTC; the time zone applies for them only when converting from TIMESTAMP values. If you want locale-specific arithmetic for DATE, TIME, or DATETIME values, convert them to UTC, perform the arithmetic, and then convert back.
time_zone变量对时区敏感的time values有影响，比如now()、curtime()、timestamp columns(TIMESTAMP列，在库中存储的是TIMESTAMP(一串数字))

三、system_time_zone

3.1、官方解释

The server system time zone. When the server begins executing, it inherits a time zone setting from the machine defaults, possibly modified by the environment of the account used for running the server or the startup script. The value is used to set system_time_zone. Typically the time zone is specified by the TZ environment variable. It also can be specified using the --timezone option of the mysqld_safe script.
The system_time_zone variable differs from time_zone. Although they might have the same value, the latter variable is used to initialize the time zone for each client that connects.

前面提到的log_timestamps可以设置为SYSTEM，time_zone默认初始化为SYSTEM，这些SYSTEM就是指system_time_zone。实例启动时默认会继承当前服务器的时区，我们可以通过TZ环境变量或者mysqld_safe --timezone选项设置system_time_zone

3.2、跨时区主从复制的潜在问题

跨时区主从复制的潜在问题参考这里

# 主库执行(东六区)
mysql> insert into mytable values(now())
mysql> select * from mytable --返回2018-01-10 13:50:33

# 从库应用relay-log(东八区)
mysql> select * from mytable --返回2018-01-10 13:50:33

# 我们希望从库查询的结果是
mysql> select * from mytable --返回2018-01-10 15:50:33

东六区的 2018-01-10 13:50:33 和东八区的 2018-01-10 15:50:33 才是同一时间，我们就是要解决这样的问题

这种问题应该只存在于SBR环境，RBR的binlog中记录的是具体数值，不会记录now()之类的函数
By default, master and slave servers assume that they are in the same time zone. If you are replicating between servers in different time zones, the time zone must be set on both master and slave. Otherwise, statements depending on the local time on the master are not replicated properly, such as statements that use the NOW() or FROM_UNIXTIME() functions. Set the time zone in which MySQL server runs by using the --timezone=timezone_name option of the mysqld_safe script or by setting the TZ environment variable. 
下面测试在主、从设置time zone后是否如你所愿

# mysqld_safe --timezone
shell> /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql3306/my.cnf --timezone=UTC &
shell> /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql3308/my.cnf --timezone=MET &
影响的是system_time_zone变量，主从复制，statement格式下，now()复制到从库
2018-01-10 07:41:17->2018-01-10 08:41:17

# mysqld --default-time-zone
shell> /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my.cnf --default-time-zone=+6:00 &
shell> /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3308/my.cnf --default-time-zone=+8:00 &
影响的是time_zone变量，主从复制，statement格式下，now()复制到从库
2018-01-10 13:50:33->2018-01-10 13:50:33

原因：mysqld_safe --timezone参数，在从库的binlog可以看到
SET TIMESTAMP=1515570077;
SET @@session.time_zone='SYSTEM'; #会根据system_time_zone中的时区去设置
insert ... select now();

mysqld --default-time-zone参数，在从库的binlog可以看到
SET TIMESTAMP=1515570633;
SET @@session.time_zone='+06:00'; #直接复制主库，时间不会变化
insert ... select now();

# time_zone、timestamp对now()的影响
mysql> set time_zone='+6:00';set timestamp=1515570633;select now();
mysql> set time_zone='+8:00';set timestamp=1515570633;select now();
# 恢复timestamp
mysql> set timestamp=0;select now();


# timezone_name需要手动load
mysql> SELECT CONVERT_TZ('2018-01-10 12:00:00','+8:00','+6:00');
mysql> SELECT CONVERT_TZ('2018-01-10 12:00:00','US/Eastern','US/Central');
mysql> SELECT FROM_UNIXTIME(1515570633),UNIX_TIMESTAMP(now());

可以看出只有主、从设置system_time_zone后，之前的潜在问题才会按我们预期的结果显示~

四、参考文档

MySQL Server Time Zone Support：https://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html
Replication and System Functions：https://dev.mysql.com/doc/refman/5.7/en/replication-features-functions.html
Replication and Time Zones：https://dev.mysql.com/doc/refman/5.7/en/replication-features-timezone.html