最新消息:20210816 当前crifan.com域名已被污染,为防止失联,请关注(页面右下角的)公众号

【已解决】mysql重启出错:mysqld unknown variable explicit_defaults_for_timestamp=true

MySQL crifan 9646浏览 0评论

折腾:

【已解决】lnmp中修改了mysql的配置文件/etc/my.cnf后重启失败:ERROR The server quit without updating PID file

期间,去找错误日志:

[root@crifan ~]# ls /usr/local/mysql/var/crifan.com.err
/usr/local/mysql/var/crifan.com.err
[root@crifan ~]# ls /usr/local/mysql/var/crifan.com.err  -lha
-rw-rw—- 1 mysql mysql 12K Sep  9 12:05 /usr/local/mysql/var/crifan.com.err
[root@crifan ~]# ^C
[root@crifan ~]# cat /usr/local/mysql/var/crifan.com.err
170905  9:41:02 [Note] Plugin ‘FEDERATED’ is disabled.
170905  9:41:02 InnoDB: The InnoDB memory heap is disabled
170905  9:41:02 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170905  9:41:02 InnoDB: Compressed tables use zlib 1.2.7
170905  9:41:02 InnoDB: Initializing buffer pool, size = 256.0M
170905  9:41:02 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file /usr/local/mysql/var/ibdata1 did not exist:
InnoDB: a new database to be created!
170905  9:41:02  InnoDB: Setting file /usr/local/mysql/var/ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait…
170905  9:41:02  InnoDB: Log file /usr/local/mysql/var/ib_logfile0 did not exist: new to be created
InnoDB: Setting log file /usr/local/mysql/var/ib_logfile0 size to 64 MB
InnoDB: Database physically writes the file full: wait…
170905  9:41:03  InnoDB: Log file /usr/local/mysql/var/ib_logfile1 did not exist: new to be created
InnoDB: Setting log file /usr/local/mysql/var/ib_logfile1 size to 64 MB
InnoDB: Database physically writes the file full: wait…
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: 127 rollback segment(s) active.
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
170905  9:41:03  InnoDB: Waiting for the background threads to start
170905  9:41:04 InnoDB: 5.5.56 started; log sequence number 0
170905  9:41:04 [Note] Server hostname (bind-address): ‘0.0.0.0’; port: 3306
170905  9:41:04 [Note]   – ‘0.0.0.0’ resolves to ‘0.0.0.0’;
170905  9:41:04 [Note] Server socket created on IP: ‘0.0.0.0’.
170905  9:41:04 [Note] Event Scheduler: Loaded 0 events
170905  9:41:04 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: ‘5.5.56-log’  socket: ‘/tmp/mysql.sock’  port: 3306  Source distribution
170905  9:41:04 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown
170905  9:41:04 [Note] Event Scheduler: Purging the queue. 0 events
170905  9:41:04  InnoDB: Starting shutdown…
170905  9:41:04  InnoDB: Shutdown completed; log sequence number 1595675
170905  9:41:04 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
170905  9:41:05 [Note] Plugin ‘FEDERATED’ is disabled.
170905  9:41:05 InnoDB: The InnoDB memory heap is disabled
170905  9:41:05 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170905  9:41:05 InnoDB: Compressed tables use zlib 1.2.7
170905  9:41:05 InnoDB: Initializing buffer pool, size = 256.0M
170905  9:41:05 InnoDB: Completed initialization of buffer pool
170905  9:41:05 InnoDB: highest supported file format is Barracuda.
170905  9:41:05  InnoDB: Waiting for the background threads to start
170905  9:41:06 InnoDB: 5.5.56 started; log sequence number 1595675
170905  9:41:06 [Note] Server hostname (bind-address): ‘0.0.0.0’; port: 3306
170905  9:41:06 [Note]   – ‘0.0.0.0’ resolves to ‘0.0.0.0’;
170905  9:41:06 [Note] Server socket created on IP: ‘0.0.0.0’.
170905  9:41:06 [Note] Event Scheduler: Loaded 0 events
170905  9:41:06 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: ‘5.5.56-log’  socket: ‘/tmp/mysql.sock’  port: 3306  Source distribution
170905  9:41:07 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown
170905  9:41:07 [Note] Event Scheduler: Purging the queue. 0 events
170905  9:41:07  InnoDB: Starting shutdown…
170905  9:41:08  InnoDB: Shutdown completed; log sequence number 1595685
170905  9:41:08 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
170905  9:41:09 [Note] Plugin ‘FEDERATED’ is disabled.
170905  9:41:09 InnoDB: The InnoDB memory heap is disabled
170905  9:41:09 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170905  9:41:09 InnoDB: Compressed tables use zlib 1.2.7
170905  9:41:09 InnoDB: Initializing buffer pool, size = 256.0M
170905  9:41:09 InnoDB: Completed initialization of buffer pool
170905  9:41:09 InnoDB: highest supported file format is Barracuda.
170905  9:41:10  InnoDB: Waiting for the background threads to start
170905  9:41:11 InnoDB: 5.5.56 started; log sequence number 1595685
170905  9:41:11 [Note] Server hostname (bind-address): ‘0.0.0.0’; port: 3306
170905  9:41:11 [Note]   – ‘0.0.0.0’ resolves to ‘0.0.0.0’;
170905  9:41:11 [Note] Server socket created on IP: ‘0.0.0.0’.
170905  9:41:11 [Note] Event Scheduler: Loaded 0 events
170905  9:41:11 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: ‘5.5.56-log’  socket: ‘/tmp/mysql.sock’  port: 3306  Source distribution
170905  9:41:11 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown
170905  9:41:11 [Note] Event Scheduler: Purging the queue. 0 events
170905  9:41:11  InnoDB: Starting shutdown…
170905  9:41:11  InnoDB: Shutdown completed; log sequence number 1595685
170905  9:41:11 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
170905  9:58:00 [Note] Plugin ‘FEDERATED’ is disabled.
170905  9:58:00 InnoDB: The InnoDB memory heap is disabled
170905  9:58:00 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170905  9:58:00 InnoDB: Compressed tables use zlib 1.2.7
170905  9:58:00 InnoDB: Initializing buffer pool, size = 256.0M
170905  9:58:00 InnoDB: Completed initialization of buffer pool
170905  9:58:00 InnoDB: highest supported file format is Barracuda.
170905  9:58:00  InnoDB: Waiting for the background threads to start
170905  9:58:01 InnoDB: 5.5.56 started; log sequence number 1595685
170905  9:58:01 [Note] Server hostname (bind-address): ‘0.0.0.0’; port: 3306
170905  9:58:01 [Note]   – ‘0.0.0.0’ resolves to ‘0.0.0.0’;
170905  9:58:01 [Note] Server socket created on IP: ‘0.0.0.0’.
170905  9:58:01 [Note] Event Scheduler: Loaded 0 events
170905  9:58:01 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: ‘5.5.56-log’  socket: ‘/tmp/mysql.sock’  port: 3306  Source distribution
170905 20:46:55 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown
170905 20:46:55 [Note] Event Scheduler: Purging the queue. 0 events
170905 20:46:55  InnoDB: Starting shutdown…
170905 20:46:57  InnoDB: Shutdown completed; log sequence number 1607412
170905 20:46:57 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
170905 20:46:59 [Note] Plugin ‘FEDERATED’ is disabled.
170905 20:46:59 InnoDB: The InnoDB memory heap is disabled
170905 20:46:59 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170905 20:46:59 InnoDB: Compressed tables use zlib 1.2.7
170905 20:46:59 InnoDB: Initializing buffer pool, size = 256.0M
170905 20:46:59 InnoDB: Completed initialization of buffer pool
170905 20:46:59 InnoDB: highest supported file format is Barracuda.
170905 20:46:59  InnoDB: Waiting for the background threads to start
170905 20:47:00 InnoDB: 5.5.56 started; log sequence number 1607412
170905 20:47:00 [Note] Server hostname (bind-address): ‘0.0.0.0’; port: 3306
170905 20:47:00 [Note]   – ‘0.0.0.0’ resolves to ‘0.0.0.0’;
170905 20:47:00 [Note] Server socket created on IP: ‘0.0.0.0’.
170905 20:47:00 [Note] Event Scheduler: Loaded 0 events
170905 20:47:00 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: ‘5.5.56-log’  socket: ‘/tmp/mysql.sock’  port: 3306  Source distribution
170906 11:48:19 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown
170906 11:48:19 [Note] Event Scheduler: Purging the queue. 0 events
170906 11:48:19  InnoDB: Starting shutdown…
170906 11:48:20  InnoDB: Shutdown completed; log sequence number 1607422
170906 11:48:20 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
170906 11:48:22 [Note] Plugin ‘FEDERATED’ is disabled.
170906 11:48:22 InnoDB: The InnoDB memory heap is disabled
170906 11:48:22 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170906 11:48:22 InnoDB: Compressed tables use zlib 1.2.7
170906 11:48:23 InnoDB: Initializing buffer pool, size = 256.0M
170906 11:48:23 InnoDB: Completed initialization of buffer pool
170906 11:48:23 InnoDB: highest supported file format is Barracuda.
170906 11:48:23  InnoDB: Waiting for the background threads to start
170906 11:48:24 InnoDB: 5.5.56 started; log sequence number 1607422
170906 11:48:24 [Note] Server hostname (bind-address): ‘0.0.0.0’; port: 3306
170906 11:48:24 [Note]   – ‘0.0.0.0’ resolves to ‘0.0.0.0’;
170906 11:48:24 [Note] Server socket created on IP: ‘0.0.0.0’.
170906 11:48:24 [Note] Event Scheduler: Loaded 0 events
170906 11:48:24 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: ‘5.5.56-log’  socket: ‘/tmp/mysql.sock’  port: 3306  Source distribution
170907 17:47:36 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown
170907 17:47:36 [Note] Event Scheduler: Purging the queue. 0 events
170907 17:47:36  InnoDB: Starting shutdown…
170907 17:47:38  InnoDB: Shutdown completed; log sequence number 1607432
170907 17:47:38 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
170907 17:47:40 [Note] Plugin ‘FEDERATED’ is disabled.
170907 17:47:40 InnoDB: The InnoDB memory heap is disabled
170907 17:47:40 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170907 17:47:40 InnoDB: Compressed tables use zlib 1.2.7
170907 17:47:40 InnoDB: Initializing buffer pool, size = 256.0M
170907 17:47:40 InnoDB: Completed initialization of buffer pool
170907 17:47:40 InnoDB: highest supported file format is Barracuda.
170907 17:47:40  InnoDB: Waiting for the background threads to start
170907 17:47:41 InnoDB: 5.5.56 started; log sequence number 1607432
170907 17:47:41 [Note] Server hostname (bind-address): ‘0.0.0.0’; port: 3306
170907 17:47:41 [Note]   – ‘0.0.0.0’ resolves to ‘0.0.0.0’;
170907 17:47:41 [Note] Server socket created on IP: ‘0.0.0.0’.
170907 17:47:41 [Note] Event Scheduler: Loaded 0 events
170907 17:47:41 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: ‘5.5.56-log’  socket: ‘/tmp/mysql.sock’  port: 3306  Source distribution
170909 12:05:25 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown
170909 12:05:25 [Note] Event Scheduler: Purging the queue. 0 events
170909 12:05:27  InnoDB: Starting shutdown…
170909 12:05:29  InnoDB: Shutdown completed; log sequence number 1607452
170909 12:05:29 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
170909 12:05:31 [Note] Plugin ‘FEDERATED’ is disabled.
170909 12:05:31 InnoDB: The InnoDB memory heap is disabled
170909 12:05:31 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170909 12:05:31 InnoDB: Compressed tables use zlib 1.2.7
170909 12:05:31 InnoDB: Initializing buffer pool, size = 256.0M
170909 12:05:31 InnoDB: Completed initialization of buffer pool
170909 12:05:31 InnoDB: highest supported file format is Barracuda.
170909 12:05:31  InnoDB: Waiting for the background threads to start
170909 12:05:32 InnoDB: 5.5.56 started; log sequence number 1607452
170909 12:05:32 [ERROR] /usr/local/mysql/bin/mysqld: unknown variable ‘explicit_defaults_for_timestamp=true’
170909 12:05:32 [ERROR] Aborting
170909 12:05:32  InnoDB: Starting shutdown…
170909 12:05:32  InnoDB: Shutdown completed; log sequence number 1607452
170909 12:05:32 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
170909 12:05:54 [Note] Plugin ‘FEDERATED’ is disabled.
170909 12:05:54 InnoDB: The InnoDB memory heap is disabled
170909 12:05:54 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170909 12:05:54 InnoDB: Compressed tables use zlib 1.2.7
170909 12:05:54 InnoDB: Initializing buffer pool, size = 256.0M
170909 12:05:54 InnoDB: Completed initialization of buffer pool
170909 12:05:54 InnoDB: highest supported file format is Barracuda.
170909 12:05:54  InnoDB: Waiting for the background threads to start
170909 12:05:55 InnoDB: 5.5.56 started; log sequence number 1607452
170909 12:05:55 [ERROR] /usr/local/mysql/bin/mysqld: unknown variable ‘explicit_defaults_for_timestamp=true’
170909 12:05:55 [ERROR] Aborting
170909 12:05:55  InnoDB: Starting shutdown…
170909 12:05:55  InnoDB: Shutdown completed; log sequence number 1607452
170909 12:05:55 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
[root@crifan ~]#

看到错误原因了:

170909 12:05:55 [ERROR] /usr/local/mysql/bin/mysqld: unknown variable ‘explicit_defaults_for_timestamp=true’

所以去搜索这个:

mysqld: unknown variable ‘explicit_defaults_for_timestamp=true’

其中:

这个参数是之前fzhost中的mysql配置,之前mysql记得址5.0.1的版本

而我此处linode中的mysql是5.5.56了。

所以估计是废弃的参数了。

Mysql启动失败 MYSQL:The server quit without updating PID file – Youn丶 – 博客园

MySQL :: Re: TIMESTAMP with implicit DEFAULT value is deprecated

mysql – How to enable explicit_defaults_for_timestamp? – Stack Overflow

explicit_defaults_for_timestamp

Introduced
5.6.6
Deprecated
5.6.6

This system variable determines whether the server enables certain nonstandard behaviors for default values and NULL-value handling in TIMESTAMP columns. By default, explicit_defaults_for_timestamp is disabled, which enables the nonstandard behaviors.

不管了:

5.6.6中,引入了explicit_defaults_for_timestamp,但是同时新版中就又废除了:

explicit_defaults_for_timestamp

看起来,感觉这个参数也没啥太大价值,所以直接去掉,就可以了。

【总结】

vi /etc/my.cnf

把之前加入的:

explicit_defaults_for_timestamp = true

中的去掉,就可以了。

转载请注明:在路上 » 【已解决】mysql重启出错:mysqld unknown variable explicit_defaults_for_timestamp=true

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
89 queries in 0.194 seconds, using 22.24MB memory