Home / server / Questions / 445822
Accepted
Olivier
Asked: 2012-11-07 00:52:36 +0800 CST

MySQL died during the night on a 12.04.1 Ubuntu

  • 772

I can't explain why, but somehow during the night, one of my MySQL running on an Ubuntu 12.04.1 box broke. The service is running but I can't login anymore (to SQL), the previous password is not working anymore.

It does not looks like the server has been compromised (nothing in /var/auth.log)

It looks like some automatic security upgrade (server is configured to perform those) has occured and broke something. The MySQL server has restarted a couple of times in the logs at the time errors started to happen (I get email when CRON task fail).

In the logs it complains about an unset root password (I do have cron job running all day using SQL so the password was set & working for months). Anyway I can't login without password either!

Do you have any idea of what could have happened? How do I get my databases back?

This line looks strange :

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

Here is the full log below :

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 Answers

  1. Best Answer

    Try the steps in http://blog.mclaughlinsoftware.com/2010/10/21/reset-mysql-root-password/

    The main step is add the following lines in your mysql config (properly /etc/mysql/my.cnf). That basically disable mysql security and network access.

    [mysqld]

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

    Restart mysql and you can do following without a password.

    mysql -u root

    Remember to change your config file back after you reset your root password.

    Method 2

    Since you are using ubuntu, open /etc/mysql/debian.cnf

    You should see something like following

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

    Try do following

    mysql -u debian-sys-maint

    with the password in that file. If that get you in the mysql prompt, you can reset your root password without messing around with my.cnf.

    • 1