How to reset MySQL root password and change MySQL user password?


How to reset MySQL root password and change MySQL user password? 1

 


The MySQL root password gives the user full access to MySQL database. If you have not assigned password to MySQL database, you can assign password and secure database following steps given below. The steps to update/assign MySQL root password changes with the operating system. For example, the steps will be slightly different in Windows operating system and in Linux or UNIX like operating system.

 

Steps to change MySQL root password

 

For UNIX like operating systems 

  1. Open a terminal / ssh session
  2. Type the following command at the shell prompt – $mysql -u root -h localhost –p

The above command will let you login as a root user

 

For Windows operating system

You will be required to use mysql.exe to change the password. Before that you need to know the path of the mysql.exe i.e. its directory. The path of mysql.exe is generally “C:\Program Files\mysql\mysql-5.0.77-win32\bin”].Navigate to the directory to confirm the right path to mysql.exe before running the mysql exe file.

  1. Open command prompt
  2. Change to directory where mysql.exe is located
  3. Type the following command – mysql.exe -h localhost –user=root –p

Note – Now that you have logged in MySQL as root user, the steps for Windows and UNIX like operating systems will be similar 

 

SQL command to change root password for MySQL

At mysql prompt, type use mysql

The syntax to assign new password is

SET PASSWORD FOR ‘user-name-here’@’hostname-name-here’ = PASSWORD(‘new-password-here’);

You can also use the SQL syntax to assign new password

UPDATE mysql.user SET Password=PASSWORD(‘new-password-here’) WHERE USER=’user-name-here’ AND Host=’host-name-here’;

For examples to change SQL password for user Harry, the syntax would be

SET PASSWORD FOR ‘harry’@’localhost’ = PASSWORD(‘angel01’);

OR

PDATE mysql.user SET Password=PASSWORD(‘angel01′) WHERE USER=’harry’ AND Host=’localhost’;

After successful creation of password, you should see output as shown below

—————————————————————————–

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

—————————————————————————–

After you see the output above, type the following command to load privileges

 FLUSH PRIVILEGES;

To exit from mysql, type the following command at the mysql prompt

quit; 

 

Steps to Reset MySQL root password

If you have assigned a password to MySQL database but have forgotten it, you can reset password by following the steps given below.

 

Step 1 –  Stop MySQL Service

  1. Open a terminal / ssh session
  2. Type the following command at the shell prompt – # /etc/init.d/mysql stop

The output of the following command should be

Stopping MySQL database server: mysqld

 

Step  2: Start to MySQL server without  password

The following command would help you startg the mysql daemon  and skip the grant table which stores the password.

Type the following command at the shell prompt – # mysqld_safe –skip-grant-tables &

 The output of the following command should be

—————————————————————————–

[1] 5988

Starting mysqld daemon with databases from /var/lib/mysql

mysqld_safe[6025]: started

—————————————————————————–

Step 3: Use mysql client to connect to mysql server

Type the following command at the shell prompt – # mysql -u root

The output of the command should be

—————————————————————————–

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.

———————————————————————————

You should  see the mysql> prompt after the output message

Step 4 – Setup new root password for MySQL database 

At the mysql prompt, type the following commands

mysql> use mysql;

mysql> update user set password=PASSWORD(“NEW-ROOT-PASSWORD”) where User=’root’;

mysql> flush privileges;

mysql> quit

Step  5- Stop MySQL server

Type the following command at mysql prompt – # /etc/init.d/mysql stop

The  output of the command should be

—————————————————————————–

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

—————————————————————————–

 Step 6 – Start MySQL server 

  1. Open a terminal / ssh session
  2. Type following commands

 # /etc/init.d/mysql start

 # mysql -u root –p

Leave a Reply

Your email address will not be published. Required fields are marked *

Security code * Time limit is exhausted. Please reload the CAPTCHA.

This site uses Akismet to reduce spam. Learn how your comment data is processed.