一、数据库用户管理
1.1 新建用户
CREATE USER '用户名'@'来源地址' [IDENTIFIED BY [PASSWORD] '密码']； ------------------------------------------------------------------------------- '用户名'：指定将创建的用户名 '来源地址'：指定新创建的用户可在哪些主机上登录，可使用p地址、网段、主机名的形式，本地用户可用localhost，允许任意主机登录可用通配符% '密码'： 若使用明文密码，直接输入'密码'，插入到数据库时由Mysq1自动加密； 若使用加密密码，需要先使用SELECT PASSWORD（'密码'）；获取密文，再在语句中添加PASSWORD'密文'； 若省略"IDENTIEIEDBY"部分，则用户的密码将为空（不建议使用）
1.2 查看用户
用户数据位于mysql库->user表中
mysql> USE mysql; mysql> select user,host,authentication_string from user; +---------------+-----------+-------------------------------------------+ | user | host | authentication_string | +---------------+-----------+-------------------------------------------+ | root | localhost | *032197AE5731D4664921A6CCAC7CFCE6A0698693 | | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | zhangsan | localhost | *032197AE5731D4664921A6CCAC7CFCE6A0698693 | | lisi | localhost | *032197AE5731D4664921A6CCAC7CFCE6A0698693 | +---------------+-----------+-------------------------------------------+ 5 rows in set (0.00 sec)
1.3 重命名用户rename
用户完整名称’zhangsan’@localhost
mysql> rename user 'zhangsan'@'localhost' to 'stevelu'@'localhost'; mysql> select user,host,authentication_string from user; +---------------+-----------+-------------------------------------------+ | user | host | authentication_string | +---------------+-----------+-------------------------------------------+ | root | localhost | *032197AE5731D4664921A6CCAC7CFCE6A0698693 | | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | stevelu | localhost | *032197AE5731D4664921A6CCAC7CFCE6A0698693 | | lisi | localhost | *032197AE5731D4664921A6CCAC7CFCE6A0698693 | +---------------+-----------+-------------------------------------------+ 5 rows in set (0.00 sec)
1.4 删除用户drop
mysql> drop user 'lisi'@'localhost'; mysql> select user,host,authentication_string from user; +---------------+-----------+-------------------------------------------+ | user | host | authentication_string | +---------------+-----------+-------------------------------------------+ | root | localhost | *032197AE5731D4664921A6CCAC7CFCE6A0698693 | | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | stevelu | localhost | *032197AE5731D4664921A6CCAC7CFCE6A0698693 | +---------------+-----------+-------------------------------------------+ 4 rows in set (0.00 sec)
1.5 修改当前用户登录密码
mysql> set password = password('123456'); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> select user,host,authentication_string from user; +---------------+-----------+-------------------------------------------+ | user | host | authentication_string | +---------------+-----------+-------------------------------------------+ | root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | stevelu | localhost | *032197AE5731D4664921A6CCAC7CFCE6A0698693 | +---------------+-----------+-------------------------------------------+ 4 rows in set (0.00 sec)
1.6 修改其他用户密码
mysql> set password for 'stevelu'@'localhost'= password('123456');
1.7 忘记root密码
（1）修改/etc/my.cnf配置文件，不使用密码直接登录到mysql
vim /etc/my.cnf [mysqld] skip-grant-tables #添加，使登录mysq1不使用授权表 systemctl restart mysqld
（2）使用update 修改root密码，刷新数据库
UPDATE mysql.user SET AUTHENTICATION_STRING=PASSWORD（'000000'）where user='root'； FLUSH PRIVILEGES； quit mysql -u root -p000000
注意：最后再把/etc/my.cnf 配置文件里的skip-grant-tables删除，并重启mysql服务。
1.8 小结
create user ' username'@' address' identified by ' password'; rename user old user to new user; drop user select user, host, authentication string from mysql. user; set password=password(' XXXX'); set password for
