This tutorial explains how you can set, change and reset (if you’ve forgotten the password) MySQL root passwords. Time and again I see problems like mysqladmin: connect to server at ‘localhost’ failed error: ‘Access denied for user ‘root’@’localhost’ (using password: YES)’. So I thought it’s time to remind you how to solve MySQL related password problems. If you are just looking for a quick fix how to reset a MySQL root password you can find that at the bottom of this tutorial.
mysqladmin Command To Change Root Password
Method 1 – Set up root password for the first time
If you have never set a root password for MySQL, the server does not require a password at all for connecting as root. To set up a root password for the first time, use the mysqladmin command at the shell prompt as follows:
$ mysqladmin -u root password newpass
If you want to change (or update) a root password, then you need to use the following command:
$ mysqladmin -u root -p oldpassword newpass
Enter password:
If you get…
mysqladmin: connect to server at ‘localhost’ failed
error: ‘Access denied for user ‘root’@’localhost’ (using password: YES)’
then follow the instructions below on how to recover your MySQL password.
Change MySQL password for other users
To change a normal user password you need to type:
$ mysqladmin -u user-name -p oldpassword newpass
Method 2 – Update or change password
MySQL stores usernames and passwords in the user table inside the MySQL database. You can directly update a password using the following method to update or change passwords:
1) Login to the MySQL server, type the following command at the shell prompt:
$ mysql -u root -p
2) Use the mysql database (type commands at the mysql> prompt):
mysql> use mysql;
3) Change password for a user:
mysql> update user set password=PASSWORD(“newpass”) where User=’ENTER-USER-NAME-HERE’;
4) Reload privileges:
mysql> flush privileges;
mysql> quit
This method you need to use while using PHP or Perl scripting.
Recover MySQL root password
You can recover a MySQL database server password with the following five easy steps:
Step # 1: Stop the MySQL server process.
Step # 2: Start the MySQL (mysqld) server/daemon process with the –skip-grant-tables option so that it will not prompt for a password.
Step # 3: Connect to the MySQL server as the root user.
Step # 4: Set a new root password.
Step # 5: Exit and restart the MySQL server.
Here are the commands you need to type for each step (log in as the root user):
Step # 1 : Stop the MySQL service:
# /etc/init.d/mysql stop
Output:
Stopping MySQL database server: mysqld.
Step # 2: Start the MySQL server w/o password:
# mysqld_safe –skip-grant-tables &
Output:
[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: started
Step # 3: Connect to the MySQL server using the MySQL client:
# mysql -u root
Output:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql>
Step # 4: Set a new MySQL root user password:
mysql> use mysql;
mysql> update user set password=PASSWORD(“NEW-ROOT-PASSWORD”) where User=’root’;
mysql> flush privileges;
mysql> quit
Step # 5: Stop the MySQL server:
# /etc/init.d/mysql stop
Output:
Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended
[1]+ Done mysqld_safe –skip-grant-tables
Start the MySQL server and test it:
# /etc/init.d/mysql start
# mysql -u root -p