Issues resetting the MySQL password on an Amazon EC2 'Amazon Linux' instance

Question

Been fighting with resetting the root password for MySQL on an Amazon Linux hosted on EC2. Version:

mysql Ver 14.14 Distrib 5.5.42, for Linux (x86_64) using readline 5.1

When installing, I decided it would be smart to change the root password to something secure! Yay! Security! Except it won’t let me log in with that password now, so I’m trying to reset it.

I followed the instructions here, but it’s not working. I thought it might be permissions on the init file, but they're set to rwxrwxrwt so probably not, but maybe? Not even sure what log files to check to see where it's going wrong either.

So, how can I successfully reset the MySQL password on EC2 Amazon Linux? Is the set of instructions linked to supposed to work, or does Amazon do something differently?

Also I asked this on ServerFault, but it wasn’t “clear” what I was asking. I’d say this is rather off topic for here too, but…

Answer 1

There is nothing magical about an Amazon Linux hosted on EC2 instance. The reason everyone uses some variant of Linux is because Linux is Linux.

That said, this is how I reset MySQL root passwords on Ubuntu servers if I run into trouble like this. The overall concepts should work for any version of Linux with just small tweaks to handle specifics.

First, I would stop MySQL like this; this is the command I would use on Ubuntu:

sudo service mysql stop

With that done, startup MySQL again manually with the --skip-grant-tables option; this is considered risky for some environments since it basically launches MySQL with all grants/permissions given to everyone in the world. But it is what you need to do to fix the root issue:

sudo mysqld --skip-grant-tables &

On some setups you might need to enter the full path to mysqld or mysqld_safe for this to work like this:

sudo /usr/bin/mysqld_safe --skip-grant-tables &

Now with MySQL restarted with it’s grants open to the world, login like this:

mysql -u root mysql

That basically logs you in as the user root to the database mysql which manages all administrative aspects of MySQL access.

Now run this query; of course change YOURNEWPASSWORD to match the desired new password you want to assign to the root user:

UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE user='root'; FLUSH PRIVILEGES; exit;

As you can see that is a concatenated command which runs an UPDATE, then a FLUSH PRIVILEGES to reload the MYSQL grants tables and exit to get you out of MySQL.

With that done you should all be set and root should have the password reset to match whatever YOURNEWPASSWORD you changed it to.

Answer 2

Inspecting the output from running mysqld_safe, it kindly indicates that it's logging to /var/log/mysqld. The notable part it says:

150830 6:32:18 [ERROR] /usr/libexec/mysqld: File '/home/ec2-user/mysql-init' not found (Errcode: 2)

Turns out that root can't access that location. Moved the file to /tmp/mysql-init and then the password reset worked.

Answer 3

I've been struggling with this for a couple hours and I just found out the solution.

In my case I was trying to set a password for the root user. I saw that when I ran select * from mysql.user; the root user had as plugin the value socket. I simply forced the change to use mysql_native_password plugin by running this command:

ALTER USER 'root'@'localhost' identified with mysql_native_password by 'password_goes_here'