MySql - Access denied for user root

[delykj]

Hello,

I'm getting many MySql access denied errors in syslog.
"mysqld: 150303 16:23:21 [Warning] Access denied for user 'root'@'localhost' (using password: YES)"

I have checked and the root password indeed not working.
I will try to change MySQL root password, but the MySQL process is restarted very fast if I stop the service.
What is the best way to stop the mysql health check and change the MySQL password?

For changing the password I have found this guide:
https://help.ubuntu.com/community/MysqlPasswordReset

Thanks.

[angel0d]

the mysql root password created during installation can be found in /root/.my.cnf
have you previously changed the mysql root password ?

i think that you need:
to change it also in .my.cnf file
or
change the mysql root password back to the one found in the .my.cnf

[delykj]

I didn't changed the password, but the mentioned error started to present in the log files.
Later I tried to login via the /root/.my.cnf, without success.
So I tried to change the root password, but the health check process restarted the stopped mysql service very fast. So I suppose that I need to bypass the health check for mysql some way.
Thank your help.

[angel0d]

you can comment out all crons to get your job done

not sure if this is the right way to solve this though

[omega8cc]

This could be a result of broken or incomplete or canceled upgrade procedure, so current mysql root password does not match /root/.my.pass.txt and/or /root/.my.cnf

Please back up both files and then sync them, so /root/.my.cnf will have the same password as visible in /root/.my.pass.txt

If this still doesn't work, check previous copies with names .my.pass.txt-pre-BOA* and .my.cnf-pre-BOA* and try to use them instead.

[yaazkal]

Hi. I have the same issue here.
Password match in both files.
I use key authentication and I tought it was maybe causing the issue. Just enable the Password Authentication again on my sshd config file, but the error persist.

PS: This happens when trying to update BOA (on step octopus up-stable all both)

[omega8cc]

OK, so it definitely sounds like a mysterious bug we need to find and fix. I can only guess that it is related to something changed in recent MariaDB versions, because it never happened before, but now we have experienced this on two our own instances. The problem is that it is still very rare, but once the system is affected, it happens on every subsequent upgrade on that system.

[omega8cc]

It is a release blocker, unfortunately, so let's hope we can identify and fix it quickly.

[omega8cc]

OK, so we couldn't determine the source of the problem and reproduce it reliably after all, so let's just switch the default to not change mysql root password on every barracuda upgrade instead. It will be a temporary workaround until we will be able to track and fix the real problem.

[omega8cc]

So, just in case this happened to anyone else, here is a quick manual recovery how-to:

===
### touch control file
touch /root/.mysql.no.new.password.cnf

### make sure that your session will continue on disconect
screen

### stop sytem cron and wait 1-2 minutes
service cron stop
sleep 60

### run this until you no longer see xdrago cron tasks running
### to avoid mysql auto-healing restart too early
ps axf

### check or set new mysql root password
cat /root/.my.cnf

nano /etc/mysql/.temp.init
### paste the single line shown below
### replace FOO!BAR with password copied from /root/.my.cnf
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('FOO!BAR');

### stop mysql server
service mysql stop

### start mysql server with tmp init and hit Enter
mysqld_safe --init-file=/etc/mysql/.temp.init &

### update mysql root password
### replace FOO!BAR with password copied from /root/.my.cnf
mysql -u root -e "UPDATE mysql.user SET Password=PASSWORD('FOO!BAR') WHERE User='root';"
mysql -u root -e "FLUSH PRIVILEGES;"

### restart mysql server again
service mysql restart

### make sure that password from /root/.my.cnf works
mysql

### if everything works, start cron again
service cron start

### optionally run barracuda and octopus upgrade
barracuda up-stable log
octopus up-stable all both log
===

[omega8cc]

Workaround will be included in 2.4.4 and we will continue debugging.

[yaazkal]

Hi. The manual recovery how-to works pretty well.
This error was causing lot of aborted connections and because of that making the sites slow for the logged users (usually with timeouts).

[omega8cc]

SQL root password is no longer updated on every upgrade and we can't even reproduce the original issue, but there is some nice how-to here we could add to BOA docs.