修改主机时间对MySQL影响

最新推荐文章于 2022-07-26 16:21:34 发布

aecuhty88306453

最新推荐文章于 2022-07-26 16:21:34 发布

阅读量447

点赞数

文章标签：数据库操作系统

原文链接：http://www.cnblogs.com/DataArt/p/10312035.html

版权

背景

在装机实施时，BIOS忘记调整时间，导致服务器时间与CST不符合；待发现问题时，MySQL环境已经在运行，所以只能通过操作系统进行更改；但是更改完成后，MySQL进行重启时发生了问题。以下为问题复现和解决过程

测试环境

MySQL 5.7.24 CentOS 7.4

root@localhost : (none) 12:00:54> show variables like '%time_zone'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | CST | | time_zone | +08:00 | +------------------+--------+ 2 rows in set (0.01 sec) root@localhost : (none) 12:00:56> show variables like 'log_time%'; +----------------+--------+ | Variable_name | Value | +----------------+--------+ | log_timestamps | SYSTEM | +----------------+--------+ 1 row in set (0.00 sec) root@localhost : (none) 02:20:54> show variables like '%fast_shutdown'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | innodb_fast_shutdown | 2 | +----------------------+-------+ 1 row in set (0.00 sec)

测试过程

[root@localhost ~]# date
Tue Dec 4 11:06:55 CST 2018 [root@localhost ~]# ps -ef|grep mysql mysql 5113 1 4 11:06 ? 00:00:00 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid root 5160 1403 0 11:06 pts/0 00:00:00 grep --color=auto mysql ###主机向前修改时间 [root@localhost ~]# date -s 10:00 Tue Dec 4 10:00:00 CST 2018 [root@localhost ~]# date Tue Dec 4 10:00:01 CST 2018 ###停止MySQL [root@localhost ~]# service mysqld stop Redirecting to /bin/systemctl stop mysqld.service ###查看错误日志，时间由11：00到10:00，出现了hang [root@localhost ~]# tail -f /var/log/mysql/error.log 2018-12-04T11:06:52.731556+08:00 0 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode. 2018-12-04T11:06:52.731564+08:00 0 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode. ... 2018-12-04T10:00:07.198925+08:00 0 [Note] InnoDB: FTS optimize thread exiting. 2018-12-04T10:00:07.199255+08:00 0 [Note] InnoDB: Starting shutdown... 2018-12-04T10:01:15.036504+08:00 0 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool 2018-12-04T10:02:22.191904+08:00 0 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool 2018-12-04T10:03:32.160278+08:00 0 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool

解决1

###手动kill掉
[root@localhost ~]# ps -ef|grep mysqld
mysql 5234 1 0 12:00 ? 00:00:00 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid root 5300 1886 0 12:04 pts/1 00:00:00 grep --color=auto mysqld [root@localhost ~]# kill -9 5234 1

解决2

###手动修改时间（大于error.log最大的time）
[root@localhost ~]# date -s 12:00         
Tue Dec 4 12:00:00 CST 2018 ###查看error日志，正常关闭 [root@localhost ~]# tail -f /var/log/mysql/error.log 2018-12-04T10:00:07.198780+08:00 0 [Note] Shutting down plugin 'INNODB_LOCKS' 2018-12-04T10:00:07.198783+08:00 0 [Note] Shutting down plugin 'INNODB_TRX' 2018-12-04T10:00:07.198786+08:00 0 [Note] Shutting down plugin 'InnoDB' 2018-12-04T10:00:07.198925+08:00 0 [Note] InnoDB: FTS optimize thread exiting. 2018-12-04T10:00:07.199255+08:00 0 [Note] InnoDB: Starting shutdown... 2018-12-04T10:01:15.036504+08:00 0 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool 2018-12-04T10:02:22.191904+08:00 0 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool 2018-12-04T10:03:32.160278+08:00 0 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool 2018-12-04T12:00:00.032698+08:00 0 [Note] InnoDB: MySQL has requested a very fast shutdown without flushing the InnoDB buffer pool to data files. At the next mysqld startup InnoDB will do a crash recovery! 2018-12-04T12:00:00.847336+08:00 0 [Note] InnoDB: Shutdown completed; log sequence number 0 2018-12-04T12:00:00.849656+08:00 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1" 2018-12-04T12:00:00.849693+08:00 0 [Note] Shutting down plugin 'MEMORY' 2018-12-04T12:00:00.849701+08:00 0 [Note] Shutting down plugin 'CSV' 2018-12-04T12:00:00.849706+08:00 0 [Note] Shutting down plugin 'sha256_password' 2018-12-04T12:00:00.849708+08:00 0 [Note] Shutting down plugin 'mysql_native_password' 2018-12-04T12:00:00.849808+08:00 0 [Note] Shutting down plugin 'binlog' 2018-12-04T12:00:00.850080+08:00 0 [Note] /usr/sbin/mysqld: Shutdown complete

原因

MySQL所在的服务器的时间更改，MySQL的缓存的时间戳依据的是主机的时间；在我们手动向前修改时间，会出现MySQL退出时要求清空比生成时间“还早”的缓存而导致了锁死。

验证

[root@yuelei1 etc]# date;date -s 14:07
Fri Dec 14 14:09:39 CST 2018 Fri Dec 14 14:07:00 CST 2018 [root@yuelei1 etc]# date;service mysqld stop Fri Dec 14 14:07:02 CST 2018 Redirecting to /bin/systemctl stop mysqld.service [root@yuelei1 ~]# tail -f /var/log/mysql/error.log ... 2018-12-14T14:07:03.272305+08:00 0 [Note] Shutting down plugin 'INNODB_TRX' 2018-12-14T14:07:03.272552+08:00 0 [Note] Shutting down plugin 'InnoDB' 2018-12-14T14:07:03.273119+08:00 0 [Note] InnoDB: FTS optimize thread exiting. 2018-12-14T14:07:03.273566+08:00 0 [Note] InnoDB: Starting shutdown... 2018-12-14T14:08:10.771410+08:00 0 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool 2018-12-14T14:09:18.510532+08:00 0 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool 2018-12-14T14:09:40.432857+08:00 0 [Note] InnoDB: MySQL has requested a very fast shutdown without flushing the InnoDB buffer pool to data files. At the next mysqld startup InnoDB will do a crash recovery! 2018-12-14T14:09:40.982834+08:00 0 [Note] InnoDB: Shutdown completed; log sequence number 0 2018-12-14T14:09:40.986140+08:00 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1" 2018-12-14T14:09:40.986185+08:00 0 [Note] Shutting down plugin 'MEMORY' 2018-12-14T14:09:40.986196+08:00 0 [Note] Shutting down plugin 'CSV' 2018-12-14T14:09:40.986200+08:00 0 [Note] Shutting down plugin 'sha256_password' 2018-12-14T14:09:40.986203+08:00 0 [Note] Shutting down plugin 'mysql_native_password' 2018-12-14T14:09:40.986303+08:00 0 [Note] Shutting down plugin 'binlog' 2018-12-14T14:09:40.986775+08:00 0 [Note] /usr/sbin/mysqld: Shutdown complete