Common MySQL commands-User Password Modification

-- Create user CREATEUSER

-- CREATE USER

-- Create a user

Create user 'user1' @ 'localhost' identified by 'pass1 ';

Grant select, INSERT, UPDATE, delete on *. * TO 'user1' @ 'localhost ';

Grant all on *. * TO 'user1' @ 'localhost ';

1. Modify the root password

Method 1: Use the mysqladmin command

-- Applies to remembering the old root password and modifying the root password

Syntax:

Mysqladmin-u username-p old password New password

For example:

# Mysqladmin-u root-proot password mysql

-- Note: if the old password is incorrectly entered, the following error will be reported:

# Mysqladmin-u root-proot1 password mysql

Mysqladmin: connect to server at 'localhost' failed

Error: 'Access denied for user' root' @ 'localhost' (using password: YES )'

Method 2: directly update the password field of the user table

-- Applies to Resetting the root password when you forget the root password.

Step 1: Modify MySQL Logon Settings

# Vi/etc/my. cnf

-- Windows is my. ini file

-- Add skip-grant-tables to the [mysqld] section. If the [mysqld] field is not displayed, add it manually.

[Mysqld]

Datadir =/var/lib/mysql

Socket =/var/lib/mysql. sock

Skip-name-resolve

Skip-grant-tables

Step 2: restart mysql

[Root @ gc ~] # Service mysql restart

Shutting down MySQL... [OK]

Starting MySQL... [OK]

Step 3: log on to and modify the MySQL root Password

-- In this case, you can directly use mysql to access the database without a password.

[Root @ gc ~] # Mysql

Welcome to the MySQL monitor. Commands end with; or \ g.

Your MySQL connection id is 2

Server version: 5.5.24 MySQL Community Server (GPL)

Copyright (c) 2000,201 1, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help; 'or' \ H' for help. type' \ C' to clear the current input statement.

Mysql> use mysql;

Database changed

Mysql> update user set password = password ('new _ password') where user = 'root ';

Query OK, 5 rows affected (0.00 sec)

Rows matched: 5 Changed: 5 Warnings: 0

Mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

-- Note: If Step 1 is not performed, the following error will be reported when you log on to mysql directly.

[Root @ gc ~] # Mysql

ERROR 1045 (28000): Access denied for user 'root' @ 'localhost' (using password: NO)

Step 4: Modify the MySQL Logon Settings

Delete the skip-grant-tables in the/etc/my. cnf file.

Step 5: restart mysql

[Root @ gc ~] # Service mysql restart

Shutting down MySQL... [OK]

Starting MySQL... [OK]

2. Modify passwords of other mysql users

Similarly, common users can use the preceding method.

-- Use the mysqladmin command

[Root @ njdyw ~] # Mysqladmin-u user1-ppass1 password pass2

-- Directly modify the database table

[Root @ njdyw ~] # Mysql-u user1-ppass1-Dmysql

Mysql> update user set password = password ('pass2') where user = 'user1 ';

Mysql> flush privileges;