2

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…

  • You say “Amazon EC2 Linux” but do you know what distro it is? CentOS? RedHat? Ubuntu? Debian? – JakeGould Aug 30 '15 at 17:46
  • 2
    It is Amazon Linux - an EC2 instance. – brandonscript Aug 30 '15 at 17:47
  • Thanks for reminding me about that, “Amazon has their own Linux distribution based on Red Hat Enterprise Linux and later linux-xen-kernel.” – JakeGould Aug 30 '15 at 18:42
13

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.

  • It does not have a default password set. To set one you'll start mysql service then run #sudo mysql_secure_installation – Codex73 Mar 28 '18 at 23:19
1

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.

  • Glad this worked out. Remember, logs always reveal the core issues even if it’s not 100% clear to you at first. And like I say in my answer, Linux is Linux no matter the distro; so any generic instructions on how to reset MySQL passwords should work well on Amazon Linux, Ubuntu, Debian, CentOS, RedHat, etc… If something doesn’t work as expected it is usually something idiosyncratic to your specific setup and not the distro-quirks itself. – JakeGould Aug 30 '15 at 18:01
  • 1
    Yeah - that's exactly why I was going crazy about it. And I couldn't see where it was logging (the way I was executing the commands wasn't printing out tot the console), so I was going doubly insane! – brandonscript Aug 30 '15 at 18:13
0

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'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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