MySQL change and recover password


Setting up mysql password is one of the essential task in systems administration

Note: Linux/Unix login root account for your operating system and MySQL root are different

You can use the built-in “mysqladmin” command to change MySQL root password. It can be executed anywhere as long the binary path is set on your Linux or Windows environment

Condition 1: If you have never set a root password for MySQL, the server does not require a password at all for connecting as root. To setup root password for first time, use mysqladmin command at shell prompt as follows:

# mysqladmin -u root password NEWPASSWORD

Condition2 : However, if you want to change (or update) a root password, then you need to use following command

#mysqladmin -u root -p’oldpassword’ password newpass

For example, If old password is abcd, and set new password to 1234, enter:

# mysqladmin -u root -p’abcd’ password ‘1234′

Condition 3: Change MySQL password for other user

To change a normal user password you need to type (let us assume you would like to change password for darwin):

# mysqladmin -u darwin -p oldpassword password newpass

Condition 4: Changing MySQL root user password using MySQL sql command

This is another method. MySQL stores username and passwords in user table inside MySQL database. You can directly update password using the following method to update or change password for user vivek:

1) Login to mysql server, type following command at shell prompt:

# mysql -u root -p

2) Use mysql database (type command at mysql> prompt):

mysql> use mysql;

3) Change password for user darwin:

mysql> update user set password=PASSWORD(”NEWPASSWORD”) where User=’darwin’;

4) Reload privileges:

mysql> flush privileges;

mysql> quit

Condition 5: Recover MySQL root password

You can recover MySQL database server password with 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 password

Step # 3: Connect to mysql server as the root user

Step # 4: Setup new root password

Step # 5: Exit and restart MySQL server

Here are commands you need to type for each step (login as the root user):

Step # 1 : Stop mysql service

# /etc/init.d/mysql stop

Stopping MySQL database server: mysqld.

Step # 2: Start to MySQL server w/o password:

# mysqld_safe –skip-grant-tables &

[1] 5988

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

mysqld_safe[6025]: started

Step # 3: Connect to mysql server using 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: Setup 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 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

Step # 6: Start MySQL server and test it

# /etc/init.d/mysql start

# mysql -u root -p

Sumber

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

%d blogger menyukai ini: