MySQL晚上死于12.04.1的Ubuntu

Question

我无法解释为什么，但不知怎么的，在夜间，我的一个运行在Ubuntu12.04.1盒上的MySQL坏了。服务正在运行，但我不能再登录了(到SQL)，以前的密码不再工作了。

看起来服务器并没有被破坏(/var/auth.log中没有)。

看起来某些自动安全升级(服务器被配置为执行这些)已经发生并损坏了一些东西。当错误发生时，MySQL服务器已经在日志中重新启动了几次(当CRON任务失败时，我收到了电子邮件)。

在日志中，它抱怨一个未设置的根密码(我确实让cron作业使用SQL整天运行，因此设置了密码&工作了几个月)。无论如何，我也不能登录没有密码！

你知道会发生什么事吗？我怎样才能拿回我的数据库？

这一行看起来很奇怪：

Nov  6 06:36:12 ns398758 mysqld_safe[6676]: ERROR: 1064  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTER TABLE user ADD column Show_view_priv enum('N','Y') CHARACTER SET utf8 NOT ' at line 1

下面是完整的日志：

Nov  6 06:36:06 ns398758 mysqld_safe[6586]: 
Nov  6 06:36:06 ns398758 mysqld_safe[6586]: PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
Nov  6 06:36:06 ns398758 mysqld_safe[6586]: To do so, start the server, then issue the following commands:
Nov  6 06:36:06 ns398758 mysqld_safe[6586]: 
Nov  6 06:36:06 ns398758 mysqld_safe[6586]: /usr/bin/mysqladmin -u root password 'new-password'
Nov  6 06:36:06 ns398758 mysqld_safe[6586]: /usr/bin/mysqladmin -u root -h ns398758.ovh.net password 'new-password'
Nov  6 06:36:06 ns398758 mysqld_safe[6586]: 
Nov  6 06:36:06 ns398758 mysqld_safe[6586]: Alternatively you can run:
Nov  6 06:36:06 ns398758 mysqld_safe[6586]: /usr/bin/mysql_secure_installation
Nov  6 06:36:06 ns398758 mysqld_safe[6586]: 
Nov  6 06:36:06 ns398758 mysqld_safe[6586]: which will also give you the option of removing the test
Nov  6 06:36:06 ns398758 mysqld_safe[6586]: databases and anonymous user created by default.  This is
Nov  6 06:36:06 ns398758 mysqld_safe[6586]: strongly recommended for production servers.
Nov  6 06:36:06 ns398758 mysqld_safe[6586]: 
Nov  6 06:36:06 ns398758 mysqld_safe[6586]: See the manual for more instructions.
Nov  6 06:36:06 ns398758 mysqld_safe[6586]: 
Nov  6 06:36:06 ns398758 mysqld_safe[6586]: Please report any problems with the /usr/scripts/mysqlbug script!
Nov  6 06:36:06 ns398758 mysqld_safe[6586]: 
Nov  6 06:36:06 ns398758 mysqld_safe[6632]: 121106  6:36:06 [Note] Plugin 'FEDERATED' is disabled.
Nov  6 06:36:06 ns398758 mysqld_safe[6632]: 121106  6:36:06 InnoDB: The InnoDB memory heap is disabled
Nov  6 06:36:06 ns398758 mysqld_safe[6632]: 121106  6:36:06 InnoDB: Mutexes and rw_locks use GCC atomic builtins
Nov  6 06:36:06 ns398758 mysqld_safe[6632]: 121106  6:36:06 InnoDB: Compressed tables use zlib 1.2.3.4
Nov  6 06:36:06 ns398758 mysqld_safe[6632]: 121106  6:36:06 InnoDB: Initializing buffer pool, size = 128.0M
Nov  6 06:36:06 ns398758 mysqld_safe[6632]: 121106  6:36:06 InnoDB: Completed initialization of buffer pool
Nov  6 06:36:06 ns398758 mysqld_safe[6632]: 121106  6:36:06 InnoDB: highest supported file format is Barracuda.
Nov  6 06:36:07 ns398758 mysqld_safe[6632]: 121106  6:36:07  InnoDB: Waiting for the background threads to start
Nov  6 06:36:08 ns398758 mysqld_safe[6632]: 121106  6:36:08 InnoDB: 1.1.8 started; log sequence number 29276459701
Nov  6 06:36:08 ns398758 mysqld_safe[6632]: 121106  6:36:08  InnoDB: Starting shutdown...
Nov  6 06:36:09 ns398758 mysqld_safe[6632]: 121106  6:36:09  InnoDB: Shutdown completed; log sequence number 29276459701
Nov  6 06:36:11 ns398758 mysqld_safe[6676]: 121106  6:36:11 [Note] Plugin 'FEDERATED' is disabled.
Nov  6 06:36:11 ns398758 mysqld_safe[6676]: 121106  6:36:11 InnoDB: The InnoDB memory heap is disabled
Nov  6 06:36:11 ns398758 mysqld_safe[6676]: 121106  6:36:11 InnoDB: Mutexes and rw_locks use GCC atomic builtins
Nov  6 06:36:11 ns398758 mysqld_safe[6676]: 121106  6:36:11 InnoDB: Compressed tables use zlib 1.2.3.4
Nov  6 06:36:11 ns398758 mysqld_safe[6676]: 121106  6:36:11 InnoDB: Initializing buffer pool, size = 128.0M
Nov  6 06:36:11 ns398758 mysqld_safe[6676]: 121106  6:36:11 InnoDB: Completed initialization of buffer pool
Nov  6 06:36:11 ns398758 mysqld_safe[6676]: 121106  6:36:11 InnoDB: highest supported file format is Barracuda.
Nov  6 06:36:11 ns398758 mysqld_safe[6676]: 121106  6:36:11  InnoDB: Waiting for the background threads to start
Nov  6 06:36:12 ns398758 mysqld_safe[6676]: 121106  6:36:12 InnoDB: 1.1.8 started; log sequence number 29276459701
Nov  6 06:36:12 ns398758 mysqld_safe[6676]: ERROR: 1064  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTER TABLE user ADD column Show_view_priv enum('N','Y') CHARACTER SET utf8 NOT ' at line 1
Nov  6 06:36:12 ns398758 mysqld_safe[6676]: 121106  6:36:12 [ERROR] Aborting
Nov  6 06:36:12 ns398758 mysqld_safe[6676]: 
Nov  6 06:36:12 ns398758 mysqld_safe[6676]: 121106  6:36:12  InnoDB: Starting shutdown...
Nov  6 06:36:13 ns398758 mysqld_safe[6676]: 121106  6:36:13  InnoDB: Shutdown completed; log sequence number 29276459701
Nov  6 06:36:13 ns398758 mysqld_safe[6676]: 121106  6:36:13 [Note] /usr/sbin/mysqld: Shutdown complete
Nov  6 06:36:13 ns398758 mysqld_safe[6676]: 
Nov  6 06:36:13 ns398758 mysqld_safe[6697]: 121106  6:36:13 [Note] Plugin 'FEDERATED' is disabled.
Nov  6 06:36:13 ns398758 mysqld_safe[6697]: 121106  6:36:13 InnoDB: The InnoDB memory heap is disabled
Nov  6 06:36:13 ns398758 mysqld_safe[6697]: 121106  6:36:13 InnoDB: Mutexes and rw_locks use GCC atomic builtins
Nov  6 06:36:13 ns398758 mysqld_safe[6697]: 121106  6:36:13 InnoDB: Compressed tables use zlib 1.2.3.4
Nov  6 06:36:13 ns398758 mysqld_safe[6697]: 121106  6:36:13 InnoDB: Initializing buffer pool, size = 128.0M
Nov  6 06:36:13 ns398758 mysqld_safe[6697]: 121106  6:36:13 InnoDB: Completed initialization of buffer pool
Nov  6 06:36:13 ns398758 mysqld_safe[6697]: 121106  6:36:13 InnoDB: highest supported file format is Barracuda.
Nov  6 06:36:13 ns398758 mysqld_safe[6697]: 121106  6:36:13  InnoDB: Waiting for the background threads to start
Nov  6 06:36:14 ns398758 mysqld_safe[6697]: 121106  6:36:14 InnoDB: 1.1.8 started; log sequence number 29276459701
Nov  6 06:36:14 ns398758 mysqld_safe[6697]: 121106  6:36:14  InnoDB: Starting shutdown...
Nov  6 06:36:15 ns398758 mysqld_safe[6697]: 121106  6:36:15  InnoDB: Shutdown completed; log sequence number 29276459701
Nov  6 06:36:15 ns398758 mysqld_safe[6718]: 121106  6:36:15 [Note] Plugin 'FEDERATED' is disabled.
Nov  6 06:36:15 ns398758 mysqld_safe[6718]: 121106  6:36:15 InnoDB: The InnoDB memory heap is disabled
Nov  6 06:36:15 ns398758 mysqld_safe[6718]: 121106  6:36:15 InnoDB: Mutexes and rw_locks use GCC atomic builtins
Nov  6 06:36:15 ns398758 mysqld_safe[6718]: 121106  6:36:15 InnoDB: Compressed tables use zlib 1.2.3.4
Nov  6 06:36:15 ns398758 mysqld_safe[6718]: 121106  6:36:15 InnoDB: Initializing buffer pool, size = 128.0M
Nov  6 06:36:15 ns398758 mysqld_safe[6718]: 121106  6:36:15 InnoDB: Completed initialization of buffer pool
Nov  6 06:36:15 ns398758 mysqld_safe[6718]: 121106  6:36:15 InnoDB: highest supported file format is Barracuda.
Nov  6 06:36:15 ns398758 mysqld_safe[6718]: 121106  6:36:15  InnoDB: Waiting for the background threads to start
Nov  6 06:36:16 ns398758 mysqld_safe[6718]: 121106  6:36:16 InnoDB: 1.1.8 started; log sequence number 29276459701
Nov  6 06:36:16 ns398758 mysqld_safe[6718]: ERROR: 1050  Table 'plugin' already exists
Nov  6 06:36:16 ns398758 mysqld_safe[6718]: 121106  6:36:16 [ERROR] Aborting
Nov  6 06:36:16 ns398758 mysqld_safe[6718]: 
Nov  6 06:36:16 ns398758 mysqld_safe[6718]: 121106  6:36:16  InnoDB: Starting shutdown...
Nov  6 06:36:17 ns398758 mysqld_safe[6718]: 121106  6:36:17  InnoDB: Shutdown completed; log sequence number 29276459701
Nov  6 06:36:17 ns398758 mysqld_safe[6718]: 121106  6:36:17 [Note] /usr/sbin/mysqld: Shutdown complete
Nov  6 06:36:17 ns398758 mysqld_safe[6718]: 
Nov  6 06:36:19 ns398758 /etc/mysql/debian-start[6816]: Upgrading MySQL tables if necessary.
Nov  6 06:36:20 ns398758 /etc/mysql/debian-start[6819]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored
Nov  6 06:36:20 ns398758 /etc/mysql/debian-start[6819]: Looking for 'mysql' as: /usr/bin/mysql
Nov  6 06:36:20 ns398758 /etc/mysql/debian-start[6819]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Nov  6 06:36:20 ns398758 /etc/mysql/debian-start[6819]: Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock' '--host=localhost' '--socket=/var/run/mysqld/mysqld.sock' '--host=localhost' '--socket=/var/run/mysqld/mysqld.sock' 
Nov  6 06:36:20 ns398758 /etc/mysql/debian-start[6819]: Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock' '--host=localhost' '--socket=/var/run/mysqld/mysqld.sock' '--host=localhost' '--socket=/var/run/mysqld/mysqld.sock' 
Nov  6 06:36:20 ns398758 /etc/mysql/debian-start[6819]: col_digitas.acos                                   OK
Nov  6 06:36:20 ns398758 /etc/mysql/debian-start[6819]: col_digitas.aros                                   OK
...

Answer 1

尝试http://blog.mclaughlinsoftware.com/2010/10/21/reset-mysql-root-password/中的步骤

主要步骤是在mysql配置中添加以下行(正确地/etc/mysql/my.cnf)。这基本上禁用了mysql安全和网络访问。

[mysqld]

# These let you safely reset the lost root password.
skip-grant-tables
#enable-named-pipe
#skip-networking

重新启动mysql，您可以在没有密码的情况下执行以下操作。

mysql -u root

记得在重置根密码后将配置文件更改回.

方法2

由于您正在使用ubuntu，所以打开/etc/mysql/debian.cnf

你应该看到下面这样的东西

[client]
host     = localhost
user     = debian-sys-maint
password = xxxxxxxxxx
socket   = /var/run/mysqld/mysqld.sock
...

试着做以下工作

mysql -u debian-sys-maint

文件里有密码。如果这让您进入mysql提示符，您可以重新设置根密码，而不需要使用my.cnf。