主页 / server / 问题 / 445822
Accepted
Olivier
Asked: 2012-11-07 00:52:36 +0800 CST

MySQL 在 12.04.1 Ubuntu 上夜间死机

  • 772

我无法解释原因，但不知何故，在夜间，我在 Ubuntu 12.04.1 机器上运行的 MySQL 之一坏了。该服务正在运行，但我无法再登录（到 SQL），之前的密码不再有效。

看起来服务器没有受到威胁（没有任何内容/var/auth.log）

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

在日志中，它抱怨未设置 root 密码（我确实使用 SQL 全天运行 cron 作业，因此密码已设置并工作了几个月）。无论如何，我也不能没有密码登录！

你知道会发生什么吗？如何取回我的数据库？

这一行看起来很奇怪：

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
...
ubuntu

1 个回答

  1. Best Answer

    尝试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

    请记住在重置 root 密码后将配置文件更改回来。

    方法二

    由于你使用的是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 提示符，你可以重置你的 root 密码而不用乱用 my.cnf。

    • 1

相关问题