I've just spun up a CentOS 6 server. I installed mysql on it

yum install mysql-server

And after I started the MySQL service it spat out on STDOUT a bunch of stuff about the necessity of changing the password on my root account. I ran

/usr/bin/mysql_secure_installation

and followed the prompts.

I then realised that the version of MySQL offered by CentOS' repository is wildly out of date, so I uninstalled it

service mysqld stop
yum remove mysql-*

I grabbed the latest MySQL Community Server RPM from Oracle's site and used

yum localinstall mysql-community-server-5.7.10-1.el6.x86_64.rpm
yum install mysql-community-server

But when I started it I didn't get any STDOUT messages about setting a password for my root user and when I ran mysql_secure_installation again it wouldn't accept my details, either with a blank password or with the password I'd set previously:

Error: Access denied for user 'root'@'localhost' (using password: NO)
Error: Access denied for user 'root'@'localhost' (using password: YES)

I've tried uninstalling mysql-community-server, manually deleting /var/lib/mysql, manually deleting /usr/share/mysql and then reinstalling mysql-server, the original package I installed, but I'm still not able to log back in to MySQL.

I also tried using

mysql_safe --init-file=/home/resetPassword.sql

with a file that contained commands to reset my root user's account and even try to make a new root user account, but this just crashed my SSH session and didn't reset my password.

I'm logged in as root so my omission of sudo shouldn't matter.

I don't have any data in the database that I need to preserve, I'd just like to be able to delete it completely and start from scratch.

Thanks,

YM

Okay, managed to solve this one although I'm not entirely sure how.

Things I learned:

  • Make sure all dependencies are the latest version, don't run something at 5.7.x and something else at 5.5.x

  • Using sudo for yum is actually important even when logged in as root, as otherwise file permissions don't seem to be correctly set up

  • Deleting the contents of /var/lib/mysql and running sudo mysqld --initialize resets everything

  • When a MySQL server is reinitialised, it randomly generates a temporary password for the root user, which is saved to a log file. I'm not sure why the first time I installed it the password was not set, perhaps it's because I was using an older version of MySQL Server

  • Deleting /usr/share/mysql is a bad idea, as the errmsg.sys file found in there is hard to reinstall for some reason.

  • When trying to reset your root password using --init-file, make sure the password you are setting it to meets the current password policy requirements.

Hopefully something in that list helps someone else.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.