How to change the MySQL root account password on CentOS 7?

Question

I have installed mySQL on a CentOS 7 VM but I have problems logging in with root.

I tried logging in without password or tried any default ones (like mysql, admin etc) I looked in the my.cnf file and there's no password. I tried changing the password by stopping the service and restarting it with:

mysqld_safe --skip-grant-tables &

But I get this message:

mysqld_safe:command not found

I have no idea what else to do.

Oh

You'll need to specify the full path to the command. If you don't know what that is, find is your friend. The password will not be, should not be expressed in my.cnf. – Beefsteak

Answer 1

What version of mySQL are you using? I''m using 5.7.10 and had the same problem with logging on as root

There is 2 issues - why can't I log in as root to start with, and why can I not use 'mysqld_safe` to start mySQL to reset the root password.

I have no answer to setting up the root password during installation, but here's what you do to reset the root password

Edit: the initial root password on install can be found by running

grep 'temporary password' /var/log/mysqld.log

http://dev.mysql.com/doc/refman/5.7/en/linux-installation-yum-repo.html

---

1. systemd is now used to look after mySQL instead of mysqld_safe (which is why you get the -bash: mysqld_safe: command not found error - it's not installed)

2. The user table structure has changed.

So to reset the root password, you still start mySQL with --skip-grant-tables options and update the user table, but how you do it has changed.

1. Stop MySQL:

   sudo systemctl stop mysqld
   

2. Set the MySQL environment option:

   sudo systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
   

3. Start MySQL using the options you just set:

   sudo systemctl start mysqld
   

4. Login as root

   mysql -u root
   

5. Update the root user password with these mysql commands:

   mysql> UPDATE mysql.user SET authentication_string = PASSWORD('MyNewPassword')
       -> WHERE User = 'root' AND Host = 'localhost';
   mysql> FLUSH PRIVILEGES;
   mysql> quit
   

   Edit: As mentioned my shokulei in the comments, for 5.7.6 and later, you should use:

   mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
   

   Or you'll get a warning

6. Stop MySQL

   sudo systemctl stop mysqld
   

7. Unset the MySQL environment option so it starts normally next time:

   sudo systemctl unset-environment MYSQLD_OPTS
   

8. Start MySQL normally:

   sudo systemctl start mysqld
   

9. Try to login using your new password:

   mysql -u root -p
   

---

Reference

As it says at http://dev.mysql.com/doc/refman/5.7/en/mysqld-safe.html,

Note

As of MySQL 5.7.6, for MySQL installation using an RPM distribution, server startup and shutdown is managed by systemd on several Linux platforms. On these platforms, mysqld_safe is no longer installed because it is unnecessary. For more information, see Section 2.5.10, “Managing MySQL Server with systemd”.

Which takes you to http://dev.mysql.com/doc/refman/5.7/en/server-management-using-systemd.html where it mentions the systemctl set-environment MYSQLD_OPTS= towards the bottom of the page.

The password reset commands are at the bottom of http://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html

Strang

I realise that this is pretty much the same answer as I gave here: #33374814 but I'm not sure of what I should do if it's essentially the same answer to 2 questions - give 2 answers or link from one to the other? – Strang

This was an excellent answer where there really was no unified source for a single answer in this exact situation. If only I had found this sooner, it would have saved a few hours of work. Thank you. – Maxilliped

Thanks!grep 'temporary password' /var/log/mysqld.log – Hyder

Finally, I found clear working instructions. Everywhere mentioned mysqld_safe command, but it is not found on my RedHat. – Yuriyuria

Thank you! I am using MySQL 5.7.18. I have to use [ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';] instead of the [update user..] query. – Ofris

It's worth noting that at least as of time of writing this also works with MariaDB. Just replace mysqld with mariadb.service and it works exactly the same - the options are identical. – Siskin

Thanks a ton,I was struggling from 2-3 hours & no one on stackoverflow mentioned clear steps. – Lanilaniard

MySQL recommends SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass'); for sql < 5.7.5 dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html also for 5.6 dev.mysql.com/doc/refman/5.6/en/resetting-permissions.html – Kioto

Best solution so far. – Surfboard

Thanks. I found the required password with that way. Now, I can reset it. – Smokejumper

Thanks a lot! You should give @KevinJones a medal! Took me like an hour to get this working in CentOs! – Jailbird

@Kevin Jones 'mysql -u root' gives me an error "Access denied for user 'root'@'localhost' (using password: NO)" – Cenacle

In step 5, I had to change "authentication_string" by "Password". Otherwise, it wouldn't let me to log in. MySQL 5.6.46. – Barbaric

This didn't work just now (2-Aug-2020) for CentOS 7 and MySQL CE v8.0.32-1.el7. Neither of the alternatives for step 5 ("UPDATE ..." or "ALTER USER...") worked. Sadly, I'm now unable to get systemctl to start WITHOUT the "--skip-grant-tables" option. At this point the easiest way forward is to delete and rebuild the VM. – Leontine

As Tom says, none of these instructions work. The moment I remove skip-grant-tables it again says "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)". I am using MariaDB version: mysql Ver 15.1 Distrib 10.3.17-MariaDB, which is what YUM installs on CentOS 8. – Mcardle

@PKHunter, Tom - This is specifically for MySQL 5.7.10 on Centos 7. I wouldn't have thought that Centos 8 would make much different, but the security (passwords, etc) is different on MySQL 8, so that will be the problem. Sorry, – Strang

Answer 2

I used the advice of Kevin Jones above with the following --skip-networking change for slightly better security:

sudo systemctl set-environment MYSQLD_OPTS="--skip-grant-tables --skip-networking"

[user@machine ~]$ mysql -u root

Then when attempting to reset the password I received an error, but googling elsewhere suggested I could simply forge ahead. The following worked:

mysql> select user(), current_user();
+--------+-----------------------------------+
| user() | current_user()                    |
+--------+-----------------------------------+
| root@  | skip-grants user@skip-grants host |
+--------+-----------------------------------+
1 row in set (0.00 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'sup3rPw#'
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'sup3rPw#'
Query OK, 0 rows affected (0.08 sec)

mysql> exit
Bye
[user@machine ~]$ systemctl stop mysqld
[user@machine ~]$ sudo systemctl unset-environment MYSQLD_OPTS
[user@machine ~]$ systemctl start mysqld

At that point I was able to log in.

Coontie

@Coontie 'mysql -u root' gives me an error "Access denied for user 'root'@'localhost' (using password: NO)" – Cenacle

Answer 3

Use the below Steps to reset the password.

$ sudo systemctl start mysqld

Reset the MySql server root password.

$sudo grep 'temporary password' /var/log/mysqld.log

Output Something like-:

 10.744785Z 1 [Note] A temporary password is generated for root@localhost: o!5y,oJGALQa

Use the above password during reset mysql_secure_installation process.

$ sudo mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root: 

You have successfully reset the root password of MySql Server. Use the below command to check the mysql server connecting or not.

$ mysql -u root -p

http://gotechnies.com/install-latest-mysql-5-7-rhelcentos-7/

Mcmanus

As far as I can understand, this only works when the server is first created. All that grep command does is find the temporary password, not set it. – Breastplate

Answer 4

For CentOS 7 and MariaDB 10.4, I had success with the following commands:

su -
systemctl set-environment MYSQLD_OPTS="--skip-grant-tables --user=mysql"
systemctl restart mariadb
mysql -u root

flush privileges;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
flush privileges;
quit

systemctl unset-environment MYSQLD_OPTS
systemctl restart mariadb

Outstay

Worked as well ! – Ankara

this worked for me, i dont get why centos has to give you crap just to start MySQL or MariaDB, I went through a lot for me to get here ! – Rioux

Doesn't work. Even after all this, when I remove the MYSQLD_OPTS, and enter the right password, the command line says ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES). Using the latest version - mysql Ver 15.1 Distrib 10.3.17-MariaDB. – Mcardle

@Mcardle Feel free to upload a video of your steps. I'll then tell you where the issue is and how to solve it. Also, keep in mind that these instructions habe been posted for MariaDB 10.4. The version you posted says 10.3. So theses steps don't apply to your version. – Outstay

Also, you'd need to add use mysql to the commands after the first flush privileges. – Mcardle

@Mcardle In addition to you using MariaDB 10.3, your comments on other answers reveal that you are also using CentOS 8, which is clearly not CentOS 7 for which this answers has been written. (Just FYI, I flagged most (not all) of your comments on the various answer to this question for various reasons, to be reviewed and potentially removed. For further information, read the Code of Conduct. In my personal experience, it is usually a good idea to ensure enough blood sugar and give it a couple of hours, before writing questionable comments.) – Outstay

Thank you. I did ask it, and a mod closed my question pointing me to this one. Anyway, I've raised it again. Yes, it's CentOS and Mariadb 10.5 based on their own repo: #65552735 -- thank you for your help. – Mcardle

@Mcardle Thanks for your understanding. I'll take a look at your new question, but it might take an hour or two, because I probably have to setup a VM with CentOS 8 first. – Outstay

Answer 5

Here a little bit twist with mysql-community-server 5.7.

I share some steps, how to reset MySQL 5.7 root password or set password. it will work CentOS 7 and RHEL 7 as well.

1. Stop your MySQL:

   service mysqld stop
   

2. Modify /etc/my.cnf file add skip-grant-tables:

   vi /etc/my.cnf
   [mysqld]
   skip-grant-tables
   

3. Start MySQL:

   service mysqld start
   

4. Select MySQL default database

   mysql -u root
   
   mysql>use mysql;
   

5. Set a new password

   update user set authentication_string=PASSWORD("yourpassword") where User='root';
   

6. Remove skip-grant-tables from /etc/my.cnf file.

7. Restart MySQL:

   service mysqld restart
   
   mysql -u root -p
   

Folium

Please be clear that after it's done, the skip-grant-tables should be removed from my.cnf. It's insecure as it doesn't require password. This is not very sound advice. – Mcardle

Answer 6

Please stop all services MySQL with following command:

/etc/init.d/mysqld stop

After it use this:

mysqld_safe --skip-grant-tables

It may work properly.

Chaisson

Answer 7

For me work like this: 1. Stop mysql: systemctl stop mysqld

2. Set the mySQL environment option systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"

3. Start mysql usig the options you just set systemctl start mysqld

4. Login as root mysql -u root

5. After login I use FLUSH PRIVILEGES; tell the server to reload the grant tables so that account-management statements work. If i don't do that i receive this error trying to update the password: "Can't find any matching row in the user table"

Zwick