目录
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.7
mysql> 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';