MySQL系列—5.用户管理
目录
1、MySQL用户介绍
2、用户创建
1.正常形式
2.隐式创建
3.查看用户权限
4.root 用户创建
5.DML用户创建
6.备份恢复用户权限
7.复制用户
3、用户删除
1.删除普通用户
2.删除MySQL默认账户
4、修改用户密码
5、密码找回
1、MySQL用户介绍
用户名@'白名单'
比如 test@127.0.0.1 test@192.168.10.10是两个不一样的用户
所有网段: 'dbadmin'@%
指定网段: 'dbadmin'@'10.%' or '192.168.%' or '172.21.120.%'
具体IP: 'dbadmin'@'192.168.120.21' 1
主机名: 'dbamdin'@localhost
#存在哪里？mysql.user即用户权限表
select user,host from mysql.user;2、用户创建
1.正常形式
create user damin@'%' identified by 'xxxxx';grant all privileges on *.* to 'damin'@'%' WITH GRANT OPTION; ; #赋 予超级权限2.隐式创建
grant all privileges on *.* to 'damin'@'%' identified by 'xxxxx' ;3.查看用户权限
mysql> show grants for 'saos_damin'@'%';4.root 用户创建
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'root' WITH GRANT OPTION;mysql> flush privileges;#user和privilige表中的用户信息/权限设置从mysql库(MySQL数据库的内置库)中加载到内存 里,不然其他session无法连接。5.DML用户创建
mysql> GRANT UPDATE, DELETE, INSERT, SELECT ON *.* TO 'test'@'%' identifie d by 'test' ；mysql> flush privileges;6.备份恢复用户权限
mysql> GRANT INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER,CREATE VIE W, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON *.* TO 'res tore'@'localhost' identified by '123456';mysql> flush privileges;7.复制用户
mysql> GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'rep l'@'%' IDENTIFIED BY '123456';mysql> flush privileges;3、用户删除
1.删除普通用户
mysql> drop user 'USERNAME'@'HOST';2.删除MySQL默认账户
mysql> drop user 'root'@'127.0.0.1';4、修改用户密码
# MySQL 5.7mysql> update mysql.user set authentication_string=PASSWORD('xxx') whe re user='xxxxxx';mysql> flush privileges;#8.0 密码修改alter user 'xxx'@'localhost' identified with mysql_native_password by 'xxx';5、密码找回
#1.修改配置文件[mysqld]--skip-grant-tables#2.重启MySQL#3.设置root用户密码mysql> alter user root@'localhost' identified by 'xxxxx';