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 (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>
