MySQL密码修改

设置及修改MySQL用户密码

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

C:Userslenovo>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 (c) 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>