I have some php applications that runs with MySQL database server. I have setup MySQL database long time ago and I have forgotten root password of MySQL server. How can I recover and reset 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
We will stop MySQL daemon
$ 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
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
Set New Password For Root User
We will set password by updating users tables password column
UPDATE USER SET password=PASSWORD("Thisismypassword") WHERE USER='root';
We will set new password for root user.
Start MySQL Server Daemon Normally Again
We will start MySQL daemon again.
$ /etc/init.d/mysql restart