mysql密码修改大全

转载：https://www.cnblogs.com/wangzihong/p/10059819.html

MySQL密码修改

设置及修改MySQL用户密码

安装mysql数据库后，默认管理员为root，密码为空，可以直接登陆。

C:\Users\lenovo>mysql -u root -p
Enter password:

直接回车登陆

针对mysql数据库的用户管理：

1.增加system并提升为超级管理员，即和root等价的用户，只是名字不同。

grant all privileges on *.*  to 'system'@'localhost' identified by '123456' with grant option

2.删除所有mysql中的用户，包括root超级用户。

delete from mysql.user where user != 'system';

修改管理员root用户设置密码

1.命令行修改

[root@localhost ~]# mysqladmin -u root -p"123456" password "123" -S /tmp/mysql_3306.sock 

ps：此处密码用双引号，用单引号和不用会报错
2.sql语句修改

复制代码
#登陆到数据库里面

[root@localhost ~]# mysql -uroot -p123 -S /tmp/mysql_3306.sock 

修改mysql.user用户表中对应密码

mysql> update mysql.user set password=PASSWORD("123456") where user='root';

修改密码时需要用到PASSWORD函数
#刷新权限`

mysql> flush privileges;

用修改后的密码登陆测试

[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock 

复制代码
ps：适合密码丢失后通过

–skip-grant-tables

参数启动数据库后修改密码。

3.mysql安装完毕后，root默认口令为空，修改root口令。

• mysql> set password=PASSWORD(“123”); Query OK, 0 rows affected
  (0.00 sec)

找回丢失的mysqlroot用户密码
1 停止mysql。

2 使用–skip-grant-tables启动MySQL，忽略授权登陆验证。

多实例:mysqld_safe --defaults-flie=/etc/my.cnf --skip-grant-table &

进入：mysql -uroot -p -S /tmp/mysql.sock

复制代码

停止mysql

[root@localhost ~]# killall mysqld

忽略授权

[root@localhost ~]# mysqld_safe --skip-grant-tables --user=mysql &

直接输入mysql进入 mysql -uroot -p

[root@localhost ~]# mysql Welcome to the MySQL monitor. Commands end
with ; or \g. Your MySQL connection id is 1 Server version: 5.6.22
MySQL Community Server (GPL)

Copyright © 2000, 2014, 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>

用上面的方法修改密码

复制代码
创建mysql用户及赋予用户权限
1 查看帮助

mysql> help grant CREATE USER ‘jeffrey’@‘localhost’ IDENTIFIED BY
‘mypass’; GRANT ALL ON db1.* TO ‘jeffrey’@‘localhost’; GRANT SELECT ON
db2.invoice TO ‘jeffrey’@‘localhost’; GRANT USAGE ON . TO
‘jeffrey’@‘localhost’ WITH MAX_QUERIES_PER_HOUR 90;

2 说明

3 操作，创建user01用户，对test库具备所有权限，允许从localhost主机登陆管理，密码123456

mysql> grant all privileges on test.* to ‘user01’@‘localhost’
identified by “123456”; Query OK, 0 rows affected (0.00 sec) mysql>
flush privileges; Query OK, 0 rows affected (0.00 sec) [root@localhost
~]# mysql -uuser01 -p123456 -S /tmp/mysql_3306.sock

4 查看权限

复制代码

mysql> show grants for user01@localhost;
±--------------------------------------------------------------------------------------------------------------+ | Grants for user01@localhost
|
±--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON . TO ‘user01’@‘localhost’ IDENTIFIED BY PASSWORD
'6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9’ | | GRANT ALL PRIVILEGES
ON test. TO ‘user01’@‘localhost’
|
±--------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)

复制代码
5 使用create和grant组合（使用帮助查看help create）

复制代码

mysql> create user user02@localhost identified by “123456”; Query OK,
0 rows affected (0.00 sec) mysql> show grants for user02@localhost;
±--------------------------------------------------------------------------------------------------------------+ | Grants for user02@localhost
|
±--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON . TO ‘user02’@‘localhost’ IDENTIFIED BY PASSWORD
'6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9’ |
±--------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> grant all on test. to
‘user02’@‘localhost’; Query OK, 0 rows affected (0.00 sec)

mysql> show grants for user02@localhost;
±--------------------------------------------------------------------------------------------------------------+ | Grants for user02@localhost
|
±--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON . TO ‘user02’@‘localhost’ IDENTIFIED BY PASSWORD
'6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9’ | | GRANT ALL PRIVILEGES
ON test. TO ‘user02’@‘localhost’
|
±--------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

mysql>

复制代码
6 授权远程连接

GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ IDENTIFIED BY ‘123456’ WITH
GRANT OPTION;

flush privileges;

7 查看所有权限

复制代码
– 先查看到有所有权限的用户

mysql> show grants for user01@localhost;
±--------------------------------------------------------------------------------------------------------------+ | Grants for user01@localhost
|
±--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON . TO ‘user01’@‘localhost’ IDENTIFIED BY PASSWORD
'6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9’ | | GRANT ALL PRIVILEGES
ON test. TO ‘user01’@‘localhost’
|
±--------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)

mysql>

– 收回插入权限

mysql> revoke insert on test.* from ‘user01’@‘localhost’; Query OK, 0
rows affected (0.00 sec)
– 下面就可以看到除insert的所有权限 mysql> show grants for user01@localhost;
±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for user01@localhost
|
±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON . TO ‘user01’@‘localhost’ IDENTIFIED BY PASSWORD
'6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9’
| | GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX,
ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW,
SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON test.
TO ‘user01’@‘localhost’ |
±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)

mysql>