How to change the MySQL root account password on CentOS7?
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.
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
systemd
is now used to look after mySQL instead ofmysqld_safe
(which is why you get the-bash: mysqld_safe: command not found
error - it's not installed)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 usig 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 envitroment option so it starts normally next time
sudo systemctl unset-environment MYSQLD_OPTS
8. Start mysql normally:
sudo systemctl start mysqld
Try to login using your new password:
7. 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
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
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.
<pre>
$ sudo mysql_secure_installation
</pre>
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/