One of the most common questions according to Google is how to change the root password in MySQL. This blog is an attempt to answer this question. We will try to consider different scenarios that you may encounter in real life – how to change passwords in general, if you know it and, which is what most likely led you here, how to change the root password if you forgot it. Let’s go ahead and see how we can change the root password in MySQL 8.0?
How to change root password if we know the old one?
We’ll start with the simplest scenario. We just want to change the password. The old one is known and works just fine. How to replace it with a new one?
mysql> ALTER USER root@localhost IDENTIFIED BY 'Pa55';
Query OK, 0 rows affected (0.16 sec)
Alternatively we can run:
mysql> SET PASSWORD FOR root@localhost = 'pa55';
Query OK, 0 rows affected (0.06 sec)
ALTER is recommended though.
If you are using MySQL 8.0 in version more recent than 8.0.13, you can benefit from two password support. It is possible to assign a new password while retaining the old one. Thanks to that you can change the password in any kind of scripts and tools that you use separately from changing the password in the database. This is more practical for regular users that applications use as you should always think twice before you use root account in any kind of tool or application but it also works for the root user:
mysql> ALTER USER root@localhost IDENTIFIED BY 'Pa55' RETAIN CURRENT PASSWORD;
Query OK, 0 rows affected (0.12 sec)
Now we can use both passwords to connect to the database:
root@vagrant:~# mysql -ppa55 -e "SELECT 1" mysql: [Warning] Using a password on the command line interface can be insecure. +---+ | 1 | +---+ | 1 | +---+ root@vagrant:~# mysql -pPa55 -e "SELECT 1" mysql: [Warning] Using a password on the command line interface can be insecure. +---+ | 1 | +---+ | 1 | +---+
As you can see, both passwords work just fine. You can migrate your scripts to use the new password over time, there’s no need to do it immediately after the password change. Once you are done with the transition, you can discard the old password:
mysql> ALTER USER root@localhost DISCARD OLD PASSWORD; Query OK, 0 rows affected (0.01 sec) root@vagrant:~# mysql -pPa55 -e "SELECT 1" mysql: [Warning] Using a password on the command line interface can be insecure. +---+ | 1 | +---+ | 1 | +---+ root@vagrant:~# mysql -ppa55 -e "SELECT 1" mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
As you can see, only the new password works right now.
How to change forgotten root password?
Alright, now it’s time to look at the problem that most likely brought you here. We have forgotten the root password to our MySQL database. How can we change it? There are a couple of scenarios here as well.
You have an existing user with SYSTEM_USER and CREATE USER privileges
If this is the case, changing the root password is as easy as logging to such user and executing the ALTER command:
mysql> SHOW GRANTS; +-------------------------------------------------------------------+ | Grants for admin@127.0.0.1 | +-------------------------------------------------------------------+ | GRANT CREATE USER ON *.* TO `admin`@`127.0.0.1` WITH GRANT OPTION | | GRANT SYSTEM_USER ON *.* TO `admin`@`127.0.0.1` | +-------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> ALTER USER root@localhost IDENTIFIED BY 'NewPa55'; Query OK, 0 rows affected (0.00 sec) mysql> ^DBye root@vagrant:~# mysql -uroot -pNewPa55 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 38 Server version: 8.0.25-15.1 Percona XtraDB Cluster (GPL), Release rel15, Revision 8638bb0, WSREP version 26.4.3 Copyright (c) 2009-2021 Percona LLC and/or its affiliates Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
You don’t have any additional system user
Change root password using –skip-grant-tables
Ok, now things are more tricky than they were in previous examples. Let’s see what we can do to recover from such a condition. There are two options. First has existed since always and involves starting MySQL with –skip-grant-tables. What you can do is:
- Stop MySQL server or kill it if needed.
- Edit the configuration file adding skip_grant_tables
- Start MySQL server again. It will not be available for external connections (only localhost) as skip_networking will be automatically enabled as well.
- Login using root@localhost, reload the privileges and then execute ALTER:
root@vagrant:~# mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 Server version: 8.0.25-15.1 Percona XtraDB Cluster (GPL), Release rel15, Revision 8638bb0, WSREP version 26.4.3 Copyright (c) 2009-2021 Percona LLC and/or its affiliates Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.05 sec) mysql> ALTER USER root@localhost IDENTIFIED BY 'NewrootPass'; Query OK, 0 rows affected (0.04 sec) mysql> ^DBye
- Remove skip_grant_tables from the configuration file.
- Restart MySQL
Now you should be able to log in to the database using the new password:
root@vagrant:~# mysql -uroot -pNewrootPass mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 15 Server version: 8.0.25-15.1 Percona XtraDB Cluster (GPL), Release rel15, Revision 8638bb0, WSREP version 26.4.3 Copyright (c) 2009-2021 Percona LLC and/or its affiliates Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
Change root password using the file
Alternative option would be to use init file to change the root password for MySQL. The process is also quite simple\
.
- We should kill MySQL: kill $(pidof mysqld)
- Create a new file, accessible by MySQL, that will contain the ALTER command:
root@vagrant:~# echo "ALTER USER root@localhost IDENTIFIED BY 'NewRootPa55';" > /root/init.sql
root@vagrant:~# chown mysql.mysql /etc/mysql/init.sql
Edit MySQL configuration and add mysql-init directive with proper path:
init_file=/etc/mysql/init.sql
Start MySQL:
root@vagrant:~# systemctl start mysql root@vagrant:~# mysql -uroot -pNewRootPa55 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 16 Server version: 8.0.25-15.1 Percona XtraDB Cluster (GPL), Release rel15, Revision 8638bb0, WSREP version 26.4.3 Copyright (c) 2009-2021 Percona LLC and/or its affiliates Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> ^DBye
- Remove the init_file from MySQL configuration
As you can see, one way or the other, changing the lost root password in MySQL is relatively straightforward. We hope you found this blog informative and helpful. If you have different experience with dealing with root user lost password in MySQL, we would love to hear from you in a comment section.
