I have some PHP applications that run with the MySQL database server. I have set up MySQL database a long time ago and I have forgotten root password of MySQL server. How can I recover and reset the MySQL Server password? One way to reset MySQL password is stopping MySQL daemon. Starting it with recovery mode without authentication. Login with root and make required password changes.
Stop MySQL Server Daemon
MySQL server locks databases during running. So we need to release the lock by stopping MySQL service.
$ sudo /etc/init.d/mysql stop
Start MySQL Server Daemon Recovery Mode
We will start MySQL server daemon in recovery mode so there will be on authentication to connect MySQL server daemon. We need root privileges to accomplish these operations.
$ sudo mysqld_safe --skip-grant-tables &
Connect MySQL Server Without Password
We can connect to MySQL server without password just providing user name root like below.
$ mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.16-0ubuntu0.16.10.1 (Ubuntu) Copyright (c) 2000, 2016, 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>
Set New Password For Root User
We will set the password by updating users tables password column
USE mysql; UPDATE USER SET password=PASSWORD("Thisismypassword") WHERE USER='root'; FLUSH privileges; quit
We will set a new password for the root user.
Start MySQL Server Daemon Normally Again
We will start MySQL daemon again.
$ /etc/init.d/mysql restart