How To Change MySQL User Password

28/12/2020
After installing MySQL server, we can make connection with the server as a root user by default. For security purpose, sometimes we need to set or change the password for root or any existing user. How you can install and use MySQL on Ubuntu is shown in one of the previously published tutorial. In this tutorial, you will learn how you can set or change the password of any existing MySQL user on Ubuntu.

If no password is set for the root user then you can connect with the MySQL database server by using the following command.

$ mysql -u root

or

$ sudo  mysql -u root

It is necessary to set strong password for root or any other user to provide database security. Two ways are shown in this tutorial to change MySQL user’s password. You can follow any one from these.

Changing Password using SET:

The syntax for changing password of any MySQL user by using set statement is,

SET PASSWORD FOR ‘username’@‘hostname’ = PASSWORD(‘password’);

‘hostname’ for local server is ‘localhost’. Run the following MySQL statement to set/reset password for root user. Here, ‘abc890def’ is set as root password.

> SET PASSWORD FOR ‘root’@‘localhost’ = PASSWORD(‘abc890def’);

Exit from the server and try to connect without any password. The following access denied error will appear in the terminal.

Run the command with -p option and test the connection by applying wrong password. Then the following error message will appear.

Now, run the command with correct root password which is set in previous step. If you provide the correct password then it will be able to connect successfully with the server.

$ mysql u root p

Changing Password using UPDATE:

The syntax for changing MySQL password using update query is given below. All user’s information is stored in user table of mysql database. So, if you can update the value of Password field of user table for any particular user then the password of that user will be changed properly.

UPDATE mysql.user SET Password=PASSWORD(‘newpassword’) WHERE
USER=‘username’ AND Host=‘hostname’;

To change the password of root user on local server, you have to run the following SQL command. Here, ‘mynewpassword’ is set as new password.

> UPDATE mysql.user SET Password=PASSWORD(‘mynewpassword’) WHERE
 USER=‘root’ AND Host=‘localhost’

Again, exit from the server and restart the database server before testing the new password.

$ sudo service mysql restart

Now, check the new password is working or not for root user.

$ mysql u root p

By following any of the above SQL statements, you can easily change the password of any existing MySQL user.

ONET IDC thành lập vào năm 2012, là công ty chuyên nghiệp tại Việt Nam trong lĩnh vực cung cấp dịch vụ Hosting, VPS, máy chủ vật lý, dịch vụ Firewall Anti DDoS, SSL… Với 10 năm xây dựng và phát triển, ứng dụng nhiều công nghệ hiện đại, ONET IDC đã giúp hàng ngàn khách hàng tin tưởng lựa chọn, mang lại sự ổn định tuyệt đối cho website của khách hàng để thúc đẩy việc kinh doanh đạt được hiệu quả và thành công.
Bài viết liên quan

Installing MySQL/MariaDB on Debian 10

In this article, I am going to show you how to install MySQL/MariaDB on Debian 10. So, let’s get started. Installing...
29/12/2020

MySQL Commands Tutorial

MYSQL COMMANDS FROM THE LINUX TERMINAL MySQL is one of the most popular relational databases utilized in  applications...
28/12/2020

How to install MySql on Ubuntu

What is Mysql? MySQL is a widely accessible open source Relational Database Management System (RDBMS) that uses Structured...
28/12/2020