CODEWITHSUNDEEP
mysqlcentos
KeykoYume
KeykoYume

Reputation: 2645

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

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.

Upvotes: 74

Views: 209608

Answers (7)

Arvind
Arvind

Reputation: 147

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/

Upvotes: 13

sumit Jaiswal
sumit Jaiswal

Reputation: 29

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.

Upvotes: 2

Narendra Kumar
Narendra Kumar

Reputation: 329

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

Upvotes: 9

Kevin Jones
Kevin Jones

Reputation: 3947

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

Upvotes: 334

lauxjpn
lauxjpn

Reputation: 5254

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

Upvotes: 11

user48918
user48918

Reputation: 561

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.

Upvotes: 28

Dragos Alexe
Dragos Alexe

Reputation: 131

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

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

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

  3. Login as root mysql -u root

  4. 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"

Upvotes: 1

Related Questions