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

2017-09-13 10:29:19来源:https://www.crifan.com/mysql_restart_fail_mysqld_unknown_var作者:在路上人点击

分享

折腾:



【已解决】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 Sep9 12:05 /usr/local/mysql/var/crifan.com.err


[root@crifan ~]# ^C



[root@crifan ~]# cat /usr/local/mysql/var/crifan.com.err


1709059:41:02 [Note] Plugin ‘FEDERATED’ is disabled.


1709059:41:02 InnoDB: The InnoDB memory heap is disabled


1709059:41:02 InnoDB: Mutexes and rw_locks use GCC atomic builtins


1709059:41:02 InnoDB: Compressed tables use zlib 1.2.7


1709059:41:02 InnoDB: Initializing buffer pool, size = 256.0M


1709059: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!


1709059:41:02InnoDB: Setting file /usr/local/mysql/var/ibdata1 size to 10 MB


InnoDB: Database physically writes the file full: wait…


1709059:41:02InnoDB: 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…


1709059:41:03InnoDB: 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


1709059:41:03InnoDB: Waiting for the background threads to start


1709059:41:04 InnoDB: 5.5.56 started; log sequence number 0


1709059:41:04 [Note] Server hostname (bind-address): ‘0.0.0.0’; port: 3306


1709059:41:04 [Note]– ‘0.0.0.0’ resolves to ‘0.0.0.0’;


1709059:41:04 [Note] Server socket created on IP: ‘0.0.0.0’.


1709059:41:04 [Note] Event Scheduler: Loaded 0 events


1709059:41:04 [Note] /usr/local/mysql/bin/mysqld: ready for connections.


Version: ‘5.5.56-log’socket: ‘/tmp/mysql.sock’port: 3306Source distribution


1709059:41:04 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown


1709059:41:04 [Note] Event Scheduler: Purging the queue. 0 events


1709059:41:04InnoDB: Starting shutdown…


1709059:41:04InnoDB: Shutdown completed; log sequence number 1595675


1709059:41:04 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete


1709059:41:05 [Note] Plugin ‘FEDERATED’ is disabled.


1709059:41:05 InnoDB: The InnoDB memory heap is disabled


1709059:41:05 InnoDB: Mutexes and rw_locks use GCC atomic builtins


1709059:41:05 InnoDB: Compressed tables use zlib 1.2.7


1709059:41:05 InnoDB: Initializing buffer pool, size = 256.0M


1709059:41:05 InnoDB: Completed initialization of buffer pool


1709059:41:05 InnoDB: highest supported file format is Barracuda.


1709059:41:05InnoDB: Waiting for the background threads to start


1709059:41:06 InnoDB: 5.5.56 started; log sequence number 1595675


1709059:41:06 [Note] Server hostname (bind-address): ‘0.0.0.0’; port: 3306


1709059:41:06 [Note]– ‘0.0.0.0’ resolves to ‘0.0.0.0’;


1709059:41:06 [Note] Server socket created on IP: ‘0.0.0.0’.


1709059:41:06 [Note] Event Scheduler: Loaded 0 events


1709059:41:06 [Note] /usr/local/mysql/bin/mysqld: ready for connections.


Version: ‘5.5.56-log’socket: ‘/tmp/mysql.sock’port: 3306Source distribution


1709059:41:07 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown


1709059:41:07 [Note] Event Scheduler: Purging the queue. 0 events


1709059:41:07InnoDB: Starting shutdown…


1709059:41:08InnoDB: Shutdown completed; log sequence number 1595685


1709059:41:08 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete


1709059:41:09 [Note] Plugin ‘FEDERATED’ is disabled.


1709059:41:09 InnoDB: The InnoDB memory heap is disabled


1709059:41:09 InnoDB: Mutexes and rw_locks use GCC atomic builtins


1709059:41:09 InnoDB: Compressed tables use zlib 1.2.7


1709059:41:09 InnoDB: Initializing buffer pool, size = 256.0M


1709059:41:09 InnoDB: Completed initialization of buffer pool


1709059:41:09 InnoDB: highest supported file format is Barracuda.


1709059:41:10InnoDB: Waiting for the background threads to start


1709059:41:11 InnoDB: 5.5.56 started; log sequence number 1595685


1709059:41:11 [Note] Server hostname (bind-address): ‘0.0.0.0’; port: 3306


1709059:41:11 [Note]– ‘0.0.0.0’ resolves to ‘0.0.0.0’;


1709059:41:11 [Note] Server socket created on IP: ‘0.0.0.0’.


1709059:41:11 [Note] Event Scheduler: Loaded 0 events


1709059:41:11 [Note] /usr/local/mysql/bin/mysqld: ready for connections.


Version: ‘5.5.56-log’socket: ‘/tmp/mysql.sock’port: 3306Source distribution


1709059:41:11 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown


1709059:41:11 [Note] Event Scheduler: Purging the queue. 0 events


1709059:41:11InnoDB: Starting shutdown…


1709059:41:11InnoDB: Shutdown completed; log sequence number 1595685


1709059:41:11 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete


1709059:58:00 [Note] Plugin ‘FEDERATED’ is disabled.


1709059:58:00 InnoDB: The InnoDB memory heap is disabled


1709059:58:00 InnoDB: Mutexes and rw_locks use GCC atomic builtins


1709059:58:00 InnoDB: Compressed tables use zlib 1.2.7


1709059:58:00 InnoDB: Initializing buffer pool, size = 256.0M


1709059:58:00 InnoDB: Completed initialization of buffer pool


1709059:58:00 InnoDB: highest supported file format is Barracuda.


1709059:58:00InnoDB: Waiting for the background threads to start


1709059:58:01 InnoDB: 5.5.56 started; log sequence number 1595685


1709059:58:01 [Note] Server hostname (bind-address): ‘0.0.0.0’; port: 3306


1709059:58:01 [Note]– ‘0.0.0.0’ resolves to ‘0.0.0.0’;


1709059:58:01 [Note] Server socket created on IP: ‘0.0.0.0’.


1709059:58:01 [Note] Event Scheduler: Loaded 0 events


1709059:58:01 [Note] /usr/local/mysql/bin/mysqld: ready for connections.


Version: ‘5.5.56-log’socket: ‘/tmp/mysql.sock’port: 3306Source 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:55InnoDB: Starting shutdown…


170905 20:46:57InnoDB: 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:59InnoDB: 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: 3306Source 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:19InnoDB: Starting shutdown…


170906 11:48:20InnoDB: 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:23InnoDB: 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: 3306Source 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:36InnoDB: Starting shutdown…


170907 17:47:38InnoDB: 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:40InnoDB: 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: 3306Source 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:27InnoDB: Starting shutdown…


170909 12:05:29InnoDB: 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:31InnoDB: 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:32InnoDB: Starting shutdown…


170909 12:05:32InnoDB: 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:54InnoDB: 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:55InnoDB: Starting shutdown…


170909 12:05:55InnoDB: 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 andNULL-value handling inTIMESTAMP
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


中的去掉,就可以了。


最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台