MySQL Password detailed

1. Set the password for MySQL (when the password is empty):

Mysqladmin-u Root passwd 123456

#passwd后无需添加单引号或双引号;

Multi-instance:

mysqladmin-u Root Password oldboy-s/data/3307/mysql.sock

2. Replace the old password with the new password of the single instance command line:

mysqladmin-uroot-p123456 Password 654321

Do not have space and punctuation after #-p;

is password not passwd;

Replace the old password with the new password for the multi-instance command line:

Mysqladmin-u root-poldboy Password 123456-s/data/3307/mysql.sock

#多实例就是多了一个指定-S/data/3307/mysql.sock

3. SQL Statement Password modification:

Multi-Instance Login: Mysql-uroot-p123456-s/data/3307/mysql.sock

PlainText Password:-----------------------can not log in directly--------------------

Update mysql.user set password= ' 654321 ' where user= ' root ' and host= ' localhost ';

Permission refresh:

Flush privileges;

View Password:

Select User,host,password from Mysql.user;

Specify the function encryption password:

Update Mysql.user set Password=password (123456) where user= ' root ' and host= ' localhost ';

Note where the condition must be specified, otherwise it will all be changed; password () function change password;

Refresh permissions:

Flush privileges;

4, retrieve the Lost password (thought is to skip the authorization form):

Single-time Example:

Ⅰ: Stop the database;

It's best to gracefully close the database: Mysqladmin-uroot-p shutdown

Second:/etc/init.d/mysqld stop

It is best not to use Pkill, Killall, kill-9 in case of last resort. When you forcibly terminate the database server, it causes the database to generate more file fragments, and the database crashes when the fragmentation reaches a certain level. Bad violence!

Ⅱ: In Ps-ef | grep mysql check after database stops;

/application/mysql/bin/mysqld_safe --skip-grant-tables & #跳过授权表

Ⅲ:mysql Direct Login

Ⅳ:update Mysql.user set Password=password (123456) where user= ' root ' and host= ' localhost ';

Flush privileges;

#设置密码即可;

Restart the database and log back in.

Multiple instances (the idea and the single instance are roughly the same):

Ⅰ: Stop the database;

Ⅱ:/bin/sh/application/mysql/bin/mysqld_safe--defaults-file=/data/3307/my.cnf--skip-grant-tables &

Ⅲ: Login: Mysql-s/data/3307/mysql.sock

Ⅳ:update Mysql.user set Password=password (123456) where user= ' root ' and

host= ' localhost ';

Flush privileges;

#设置密码即可;

Restart the database and log back in.

-------------------------------------------------------------------------------------------

In the production environment, it is common to delete the user account in the useless MySQL library, and delete the default test database;
1), delete all users in MySQL, including root super user;
Mysql> Delete from Mysql.user;
Query OK, 8 rows Affected (0.00 sec)
2), add system and upgrade to super management, and root equivalent user, but the name is different;
Mysql> Grant all privileges on * * to [e-mail protected] ' localhost ' identified by ' oldboy123 ' with GRANT option;
Query OK, 0 rows Affected (0.00 sec)
mysql> flush Privileges;
Query OK, 0 rows Affected (0.00 sec)

-------------------------------------------------------------------------------------------

Note: Here to learn about the creation of the previous Bovendo instance

This article from "As always" blog, declined reprint!

MySQL Password detailed