MySQL权限及用户管理

mysql用户和权限管理

用户的增查改删
create user dev@'10.0.0.%' identified by '123';
select user, host, authentication_string from mysql.user;
alter user dev@'10.0.0.%' identified by '456';
drop user dev@'10.0.0.%';
权限的管理
#grant '权限' on '作用域' to '用户';
grant all on *.* to 'test'@'%';

常用权限介绍：

select：可以从数据库中的表中查询数据
insert：可以插入数据到数据库的表中
update：可以对数据库中的表进行更新数据
delete：可以从数据库中的表中删除数据
alter：可以使用 alter table 来改变表的结构
create：可以创建新的数据库或表的语句
drop：能够删除现有数据库，表和视图
grant option：可以向其它用户授予或移除权限
index：可以创建和删除索引
all或all privileges：代表指定权限等级的所有权限

作用域：

*.* —->代表所有数据库的权限
school.* —-> school 数据库下所有表
school.t1 —-> school 数据库下 t1 表

# 创建一个普通管理员账号，具有所有库的所有权限
mysql> grant all on *.* to root@'10.0.0.%' identified by '123';

#创建一个管理员账号，并能给其它账号授权
mysql> grant all on *.* to root@'10.0.0.%' identified by '123' with grant option;

#创建一个程序用户，具备 app 库下增删查改权限
mysql> grant select, update, insert, delete on app.* to app@'10.0.0.%' identified by '123';

# 查看权限
mysql> show grants for app@'10.0.0.%';

# 回收权限
mysql> revoke delete on wordpress.*  from app@'10.0.0.%';

# 和用户相关操作，执行完后都需要刷新权限
mysql> flush privileges;

mysql8.0在grant命令添加特性：建用户和授权分开了，grant 不再支持自动创建用户了，不支持改密码，授权之前，必须要提前创建用户。

如：

mysql> create user 'test'@'%' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> grant select, insert on *.* to 'test'@'%';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

忘记本地root管理员密码需要设置下面参数进行维护

--skip-grant-tables   #跳过授权表

--skip-networking     #跳过远程登录，防止外面人连进来

--defaults-file       #指定启动配置文件位置

[root@db01 ~]# systemctl stop mysqld.service

[root@db01 ~]# mysqld_safe --defaults-file=/data/my.cnf --skip-grant-tables --skip-networking &

mysql> flush privileges;    #加载权限表

mysql> alter user root@'localhost' identified by '123456';

mysql> exit

mysql 建库建表编码问题

建库

CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>] 
[[DEFAULT] COLLATE <校对规则名>];

CREATE DATABASE `testdb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

建表

create table tablename(
col_name1 type auto_increment,
col_name2 type not null|null default val,
col_name3 type unique,
… ,
primary key(col_name),
index idx_name(col_name1,col_name2,…),
foreign key(col_name) references tablename(col_name)
) engine=innoDB|myISAM default charset=字符集;

建表说明

type：定义字段的数据类型。其中：字符串类型（char，varchar）需要指定长度；整型（int，biging，tinyint等）和日期类型（datetime，timestamp等）只需要指定类型，不需要指定长度；decimal类型需要指定精度和小数位数。
primary key参数指定表的主键。
auto_increment参数指定字段为自增字段，该字段的类型必须为int或bigint才能设置为自增字段。
not null|null参数设置字段能否取空值。
default参数设置字段的默认值。
index参数用来创建索引。idx_name指定索引名，该参数可以省略，如果省略则索引名就是字段名。
foreign key参数用来指定外键。
engine参数用来设置引擎类型，常用的有innoDB和myISAM引擎。
default charset参数设置表所用的字符集

编码格式说明

utf8_bin:将字符串中的每一个字符用二进制数据存储，区分大小写。
utf8_genera_ci:不区分大小写，即大小写不敏感。
utf8_general_cs:区分大小写，即大小写敏感
utf8_unicode_ci:不能完全支持组合的记号
mysql的charset用于给数据库确定使用哪种编码方式进行编码
mysql的collate叫做数据库的校验，是一种对字符串进行比较的规则，能够影响排序结果

utf8mb4编码

MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换，大部分情况下utf8可以满足需求。

编码对应的排序规则

每个编码都会有一个MySQL自己默认的对比规则，如上面show charset;显示的默认collation。 同一个编码下，不同的规则对应的大小对比结果也不同。如后缀为ci的比较规则会忽略字符串的大小写，而cs的后缀规则会考虑大小写比较。

COLLATE设置

我们可以在MySQL中设置实例级别，库级别，表级别，列级别以及查询SQL级别的COLLATE规则。规则级别的选取优先级为SQL查询设置 > 列级别设置 > 表级别设置 > 库级别设置 > 实例级别设置，例如我们设置了实例级别的charset和collate，如果创建表的时候指定了表级别的charset和collate，就用表级别的设置。

COLLATE设置问题

在MySQL collate设置中有一个容易出问题的地方，就是如果设置了高级别的默认charset和collate，但是低级别又指定了新的charset，那么最后生效的是新的charset以及对应的MySQL内部默认的collate，而不是设置好的高级别collate。在MySQL中，其根据collate在内核代码中的注册先后选取最新的collate。