MySQL morreu durante a noite em um Ubuntu 12.04.1

Question

Eu não posso explicar por que, mas de alguma forma durante a noite, um dos meus MySQL rodando em uma caixa do Ubuntu 12.04.1 quebrou. O serviço está rodando mas não consigo mais logar (para SQL), a senha anterior não está mais funcionando.

Não parece que o servidor tenha sido comprometido (nada em /var/auth.log )

Parece que alguma atualização de segurança automática (o servidor está configurado para realizar isso) ocorreu e quebrou alguma coisa. O servidor MySQL foi reiniciado algumas vezes nos logs no momento em que os erros começaram a acontecer (recebo emails quando a tarefa CRON falha).

Nos logs, ele reclama de uma senha de raiz não definida (eu tenho uma tarefa cron funcionando o dia inteiro usando SQL, então a senha foi definida e está funcionando por meses). De qualquer forma eu não consigo entrar sem senha!

Você tem alguma ideia do que poderia ter acontecido? Como faço para recuperar meus bancos de dados?

Esta linha parece estranha:

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

Aqui está o registro completo abaixo:

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

Experimente os passos no link

O principal passo é adicionar as seguintes linhas na sua configuração do mysql (corretamente /etc/mysql/my.cnf). Isso basicamente desativa a segurança do mysql e o acesso à rede.

[mysqld]

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

Reinicie o mysql e você pode fazer o seguinte sem uma senha.

mysql -u root

Lembre-se de alterar seu arquivo de configuração novamente depois de redefinir sua senha de root.

Método 2

Como você está usando o Ubuntu, abra /etc/mysql/debian.cnf

Você deve ver algo como seguir

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

Tente seguir

mysql -u debian-sys-maint

com a senha nesse arquivo. Se isso te colocar no prompt do mysql, você pode redefinir sua senha root sem mexer no my.cnf.