Change & Reset Mysql Password

Posted by cikul | Posted in Linux, SQL, Tips | Posted on 18-10-2015-05-2008

0

This tutorial explains how to change or reset your MySQL root password under Unix-Like Operating Systems (Linux, FreeBSD, OpenBSD, etc) over SSH. MySQL root account should be managed carefully.

As a system administrator, we must ensure that the password is safe and prevent from unauthorized access.

There are some method to change mysql password :

Type 1 : use mysqladmin

However, if you want to change mysql root/user password, mysql had tool to simplify the process using mysqladmin command, the syntax is :


$ mysqladmin -u root -p'oldpassword' password newpass
$ mysqladmin -u dbuser -p'oldpassword' password 'itsnewpassword';

Type 2 : Use mysql command

MySQL stores user information in their databases, in database ‘mysql’ and table ‘user’, to change the password, login into mysql console, and update the user field in the user table with the  new password


$ mysql -u root -p
mysql> use mysql
mysql> update user set password=PASSWORD("NEWPASS") where user="user";
mysql> flush privileges;

Reset MySQL root Password

Sometimes we do some mistake, for example forget the root password, don’t be panic, as long as you have administrator access to your unix machine, you can reset your mysql password.

MySQL have safe mode access, that mean you can running mysql without privileges, the privileges table will be ignored.

Stop MYSQL

before insert into safe mode, the running mysql must be stop.

$ service mysql stop
$ /etc/init.d/mysql stop
$ /etc/init.d/mysqld stop

You no need to execute all command, it depends on system you are using, for example, Ubuntu and Debian using 2nd command, and CentOS, Fedora, RHEL using 3rd command. But for general you can use the 1rst command.

To ensure all mysql service stopped, do this following command


$ killall -9 myqsl

Starting MYSQL in Safe Mode

To start MYQL in safe mode,


$ mysqld_safe --skip-grant-tables &
[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: started

Now your mysql is running in safe mode, and you can log into your root account without password


$ mysql -u root
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>

Reset root MYSQL password

To change the mysql root password, you can repeat the previous command above :


mysql> use mysql
mysql> update user set password=PASSWORD("NEWPASS") where user="root";
mysql> flush privileges;

Your password is changed

To enable the privileges, restart the mysql

 

Share and Enjoy

Write a comment

*