Reset MySQL root password
Recently during writing one of post, i change my MySQL root password on one of my test platforms. But now when i tried login on MySQL, i realised that i forget MySQL root password. So what i can do now, what all my options are to get my MySQL login again.
We have one way in which we can reset MySQL root password without having our old MySQL root password.
SetuP
For this we have MySQL Server 5.7 on Ubuntu 18.04
root@srv2:~# mysqld --version mysqld Ver 5.7.30-0ubuntu0.18.04.1 for Linux on x86_64 ((Ubuntu)) root@srv2:~# lsb_release -d
Now we have MySQL running on this machine with systemctl process like below.
So we have situation where we are not able to login through password and without password
root@srv2:~# mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) root@srv2:~# mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) root@srv2:~# mysql -u root -p 'passw0rd' Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Reset MySQL password
TO reset MySQL password, we need to stop MySQL process first and run it manually with skip all grants (During this MySQL process is not safe, so i suggest please no one user should connect during this time, you can disconnect MySQL port from outside)
To stop MySQL process, please use normal kill method (not kill -9).
root@srv2:~# ps -fC mysqld root@srv2:~# ps aux | grep mysqld mysql 8920 0.7 1.0 1360756 178180 ? Sl 17:59 0:00 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid root@srv2:~# kill 8920
Now when it is stop, we need to create one file that contain one MySQL query which is going to run with skip-grant options.
This File should contain below query. I suggest no-one should able to read this file.
root@srv2:~# cat /var/lib/mysql/mysql-init ALTER USER 'root'@'localhost' IDENTIFIED BY 'Passw0rd'; root@srv2:~# ls -l /var/lib/mysql/mysql-init -rw------- 1 mysql mysql 56 Jul 5 17:54 /var/lib/mysql/mysql-init
As MySQL is stopped,Now we could run MySQL with below command using this file.
root@srv2:~# mysqld --init-file=/var/lib/mysql/mysql-init & [1] 9126
Now if we try to connect this MySQL with same password, we can able to connect.
root@srv2:~# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.30-0ubuntu0.18.04.1 (Ubuntu) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. 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.
Now we need to stop MySQL process running through –skip-grant and start it normally through systemctl. Also please delete file contain password
systemctl start mysql
Manually Query
We can also run these steps manually without saving password in file. like running start MySQL skip-grant without password file.
root@srv2:~# mysqld_safe --skip-grant-tables & [1] 8032 root@srv2:~# 2020-07-05T16:45:08.682726Z mysqld_safe Logging to syslog. 2020-07-05T16:45:08.691762Z mysqld_safe Logging to '/var/log/mysql/error.log'. 2020-07-05T16:45:08.737195Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
Now when we run it with skip-grant, we can connect MySQL root user through linux root user without any password.
root@srv2:~# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.30-0ubuntu0.18.04.1 (Ubuntu) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. 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>
Now we can reset MySQL through MySQL command line Query.
mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> update user set authentication_string=password('passw0rd') where user='root'; Query OK, 1 row affected, 1 warning (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 1
It show one warning like ‘PASSWORD’ is deprecated
mysql> show warnings; +---------+------+-------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------+ | Warning | 1681 | 'PASSWORD' is deprecated and will be removed in a future release. | +---------+------+-------------------------------------------------------------------+ 1 row in set (0.00 sec)
Leave a Reply