Arch Linux

odp

Member

Registered: 2012-04-26

Posts: 136

[solved] mysql suddenly access denied for user root ..

Hi folk,

I am in total panic right now!

As of an hour ago i suddenly got 'Error establishing a database connection' from all of my sites

I then first tried to login through phpmyadmin but got #1045 Cannot log in to the MySQL server

I then tried to login through the terminal but got ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

---------------
I am not really sure where to find solutions because primarily this error is on new installs:

I have tried the following :

mysql_safe --skip-grant-tables

[odp@odp ~]$ sudo mysqld_safe --skip-grant-tables &
[4] 3181
[odp@odp ~]$ 140115 19:43:50 mysqld_safe Logging to '/var/lib/mysql/odp.err'.
140115 19:43:50 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140115 19:43:52 mysqld_safe mysqld from pid file /var/lib/mysql/odp.pid ended

[4]   Done                    sudo mysqld_safe --skip-grant-tables

here is the odp.err

140115 19:25:41 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140115 19:25:41 InnoDB: The InnoDB memory heap is disabled
140115 19:25:41 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140115 19:25:41 InnoDB: Compressed tables use zlib 1.2.8
140115 19:25:41 InnoDB: Using Linux native AIO
140115 19:25:41 InnoDB: Initializing buffer pool, size = 128.0M
140115 19:25:41 InnoDB: Completed initialization of buffer pool
140115 19:25:41 InnoDB: highest supported file format is Barracuda.
140115 19:25:41  InnoDB: Waiting for the background threads to start
140115 19:25:42 Percona XtraDB (http://www.percona.com) 5.5.34-MariaDB-31.1 started; log sequence number 38562281
140115 19:25:42 [Note] Plugin 'FEEDBACK' is disabled.
140115 19:25:42 [ERROR] /usr/bin/mysqld: unknown option '--skip-grant-tables'
140115 19:25:42 [ERROR] Aborting

140115 19:25:42  InnoDB: Starting shutdown...
140115 19:25:42  InnoDB: Shutdown completed; log sequence number 38562281
140115 19:25:42 [Note] /usr/bin/mysqld: Shutdown complete

140115 19:25:42 mysqld_safe mysqld from pid file /var/lib/mysql/odp.pid ended
140115 19:35:48 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140115 19:35:48 InnoDB: The InnoDB memory heap is disabled
140115 19:35:48 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140115 19:35:48 InnoDB: Compressed tables use zlib 1.2.8
140115 19:35:48 InnoDB: Using Linux native AIO
140115 19:35:48 InnoDB: Initializing buffer pool, size = 128.0M
140115 19:35:48 InnoDB: Completed initialization of buffer pool
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
140115 19:35:48  InnoDB: Retrying to lock the first data file
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.

...

140115 19:37:28  InnoDB: Unable to open the first data file
InnoDB: Error in opening ./ibdata1
140115 19:37:28  InnoDB: Operating system error number 11 in a file operation.
InnoDB: Error number 11 means 'Resource temporarily unavailable'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html
140115 19:37:28 InnoDB: Could not open or create data files.
140115 19:37:28 InnoDB: If you tried to add new data files, and it failed here,
140115 19:37:28 InnoDB: you should now edit innodb_data_file_path in my.cnf back
140115 19:37:28 InnoDB: to what it was, and remove the new ibdata files InnoDB created
140115 19:37:28 InnoDB: in this failed attempt. InnoDB only wrote those files full of
140115 19:37:28 InnoDB: zeros, but did not yet use them in any way. But be careful: do not
140115 19:37:28 InnoDB: remove old data files which contain your precious data!
140115 19:37:28 [ERROR] Plugin 'InnoDB' init function returned error.
140115 19:37:28 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140115 19:37:28 [ERROR] mysqld: Can't lock aria control file '/var/lib/mysql/aria_log_control' for exclusive use, error: 11. Will retry for 30 seconds

----------------------

I have also tried the solution where you move the ibdata1 and copy it back with -a to no success.

The result of mysqld_safe --skip-grant-tabels

[odp@odp ~]$ mysqld_safe --skip-grant-tabels &
[4] 3555
[odp@odp ~]$ 140115 19:49:21 mysqld_safe Logging to '/var/lib/mysql/odp.err'.
touch: cannot touch '/var/lib/mysql/odp.err': Permission denied
chmod: cannot access '/var/lib/mysql/odp.err': Permission denied
140115 19:49:21 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
/usr/bin/mysqld_safe: line 138: /var/lib/mysql/odp.err: Permission denied
rm: cannot remove '/var/lib/mysql/odp.pid': Permission denied
/usr/bin/mysqld_safe: line 182: /var/lib/mysql/odp.err: Permission denied
touch: cannot touch '/var/lib/mysql/odp.err': Permission denied
chown: cannot access '/var/lib/mysql/odp.err': Permission denied
chmod: cannot access '/var/lib/mysql/odp.err': Permission denied
140115 19:49:21 mysqld_safe mysqld from pid file /var/lib/mysql/odp.pid ended
/usr/bin/mysqld_safe: line 138: /var/lib/mysql/odp.err: Permission denied

[4]   Exit 1                  mysqld_safe --skip-grant-tables

any advice is greatly appreciated

Last edited by odp (2014-01-15 19:46:39)

odp

Member

Registered: 2012-04-26

Posts: 136

Re: [solved] mysql suddenly access denied for user root ..

OK super random, it works now.

First off I too took and added skip-grant-tables to [mysqld] /etc/mysql/my.cnf

restarted mysqld and finally had passwordless login. From within mysql i ran

UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';
FLUSH PRIVILEGES;