一、mysql8修改了安全规则,不能像mysql5.7 一次性创建用户并授权,需要分批创建。
1、注意在MySQL8.0版本中创建用户一定要在配置文件中增加如下内容,来兼容旧的程序运行。
default_authentication_plugin=mysql_native_password
2、创建用户选项说明
创建用户
create user 'user1'@'%' identified by '123456';
创建用户说明:
user1为自定义的用户名。
host为登录域名。为% 时表示为任意ip,为localhost时表示本机,或者填写指定的ip地址。
password 为密码
二、、MySQL8的权限分级
1、授予 MySQL 帐户的权限决定了该帐户可以执行哪些操作。MySQL 特权在它们应用的上下文和不同的操作级别上有所不同:
-
管理权限使用户能够管理 MySQL 服务器的操作。这些权限是全局的,因为它们不特定于特定的数据库。
-
数据库权限适用于数据库及其中的所有对象。可以为特定数据库或全局授予这些权限,以便它们适用于所有数据库。
-
可以为数据库中的特定对象、数据库中给定类型的所有对象(例如,数据库中的所有表)或全局为所有对象授予表、索引、视图和存储例程等数据库对象的权限所有数据库中给定类型的对象。
-
database-all : 全库级别 *.* ---> 管理员
-
单库级别 : test.* ---> 业务层面
-
tables : 单表级别 test.t1
-
columns : select(id,name)
2、MySQL8权限的类型
权限列表参考MySQL8官方文档
3、授权基本规则
开发用户 ---》 测试库,无drop权限
测试人员 ---》 测试库,无drop权限
维护DBA ---》生产库
稽核人员 ---》 生产库
业务访问 ---》 查、增、删特定的数据库表
管理员 : ALL
开发 : Create ,Create routine,Create temporary tables,Create view,Delete ,Event
,Execute,Insert ,References,Select,Show databases ,Show view ,Trigger,Update
监控 : select , replication slave , client supper
备份 : ALL
主从 : replication slave
业务 : insert , update , delete ,select
三、MySQL8授权
1、管理员授权
mysql> create user 'admin'@'%' identified by '123456';
mysql> grant all on *.* to admin@'%';
2、普通用户单库授权
mysql> grant select,update,delete,insert on dev.* to 'dev'@'192.168.0.%';
查看权限
mysql> show grants for dev@'192.168.0.%';
回收用户指定的权限
mysql> REVOKE DELETE ON dev.* from dev@'192.168.0.%';
回收用户全部的权限
REVOKE ALL ON dev.* from dev@'192.168.0.%';
四、MySQL8角色管理:角色就是一组权限的集合,方便权限集中管理的授予与回收;
查看MySQL中已有的角色
注意:创建角色时有命名规则,方便区分。
查看角色拥有的权限
SELECT `User`, `Host`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Grant_priv`, `Index_priv`, `Alter_priv`, `Show_db_priv`, `Super_priv`, `Execute_priv`, `Trigger_priv` FROM `mysql`.`user`;
这个查询会返回用户、主机、以及每个用户的SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT, INDEX, ALTER, SHOW DATABASES, SUPER, EXECUTE, TRIGGER等权限状态。这里的值可以是'Y'(表示具有权限), N或空(表示没有权限)。
1、创建角色并赋权
创建角色使用 CREATE ROLE 语句,语法如下:
CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...
示例:创建role_dev的角色
mysql> CREATE ROLE 'role_dev'@'%';
Query OK, 0 rows affected (0.02 sec)
给role_dev角色赋权
语法:
GRANT privileges ON table_name TO 'role_name'[@'host_name'];
角色赋权
mysql> GRANT SELECT,INSERT,DELETE,UPDATE ON dev.* TO role_dev@'%';
角色权限回收
mysql> REVOKE DELETE ON dev.* FROM 'role_dev';
2、将角色赋权给用户
mysql> GRANT role_dev to 'dev'@'192.168.0.%';
设置用户角色生效(激活)
mysql> SET DEFAULT ROLE role_dev TO 'dev'@'192.168.0.%';
Query OK, 0 rows affected (0.00 sec)
3、查看用户权限
SHOW GRANTS FOR 'dev'@'192.168.0.%';
4、撤销用户的角色SQL语法如下:
mysql> REVOKE role_dev FROM 'dev'@'192.168.0.%';
5、删除角色
mysql> drop role role_dev;
四、MySQL8 多种连接方式介绍
1、Socket本地登录
前提: 必须创建和授权localhost相关用户
mysql> create user test4@'localhost' identified by '123';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all on *.* to test4@'localhost' with grant option;
Query OK, 0 rows affected (0.01 sec)
注意:如果使用Socket登录如果默认目录 /tmp/mysql.sock变更,需要使用-S 参数指定mysql.sock文件目录。
2、 TCP/IP远程登录
前提:关闭防火墙墙或者开启白名单
mysql> create user test5@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to test5@'%' with grant option;
Query OK, 0 rows affected (0.01 sec)
注意:如果创建用户主机使用‘%’,可以从任何网段登录MySQL。
mysql> create user test6@'192.168.87.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all on *.* to test6@'192.168.87.%' with grant option;
Query OK, 0 rows affected (0.01 sec)
标红为建立用户指定网段登录
远程主机测试:
修改用户连接方式,以root 用户为案例
mysql> update user set host='%' where user='root';
远程客户端工具登录
五、MySQL8配置文件解析
1、初始化配置文件应用
[标签]
配置=xxx
配置=xxx
配置=xxx
[标签]
...
标签:
server:
[server]
[mysqld]
[mysqld_safe]
client(不影响远程):
[client]
[mysql]
[mysqldump]
2、配置示例:
[root@localhost ~]# more /etc/my.cnf
[mysqld]
user=mysql # 管理用户
basedir=/usr/local/mysql8 # 软件路径
datadir=/data/3306/data #数据路径
socket=/tmp/mysql.sock #socket文件位置
server_id=1 #服务器ID,主从时标识不同主机
log_bin=/data/mysql/binlog_3306 #二进制日志
port=3306 #端口
[mysql]
socket=/tmp/mysql.sock
3、修改默认配置文件读取路径
调用非默认路径配置文件方法:
mysqld_safe --defaults-file=/opt/oldguo.cnf &
配置文件默认读取路径:
[root@db01 ~]# mysqld --help --verbose |grep my.cnf /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
修改默认读取路径
mysqld_safe --defaults-file=/opt/oldguo.cnf &
六、MySQL8启动方式介绍
六、MySQL8日志类型
1、日志类型
error log : 错误日志
genernal log : 普通日志
binlog : 二进制日志
slow log : 慢日志
2、error log : 错误日志配置
作用:
从启动开始,发生过的error,warning,note信息。
定位数据库问题:报错,异常(死锁)。
配置:
默认位置:
log_error=$DATDDIR/hostname.err
看日志: 主要关注 [ERROR],deadlock
2、genernal log : 普通日志
普通日志,会记录所有数据库发生的事件及语句。
select @@general_log;
set global general_log=1;
3、binlog : 二进制日志
基础参数:
server_id = 大于0的值,如果是主从复制,需各节点不同Server_id
log_bin= /data/mysql/binlog3306/mysql-bin
定制参数:
max_binlog_size --> 单个binlog文件大小,默认是1G,512M ,256M
max_binlog_cache_size
binlog_expire_logs_seconds ---> 8.0之后的参数,默认30天。
binlog_format ——--> ROW
sync_binlog=1 ---->
面试题:
sync_binlog=1
控制biong 从 内存到磁盘刷新的机制
1代表的是,每次事务提交都立即刷新到磁盘区域。
0代表的是,由OS来决定什么时候刷新
2、 binlog_format = row , statement , mixed
基础操作:
show binary logs;
flush logs;
show master status ;
4、slow log : 慢日志
记录MySQL工作中,运行较慢的语句。用来定位SQL语句性能问题
开关:
slow_query_log=1
slow_query_log_file=
维度:
set global long_query_time=0.5
set global log_queries_not_using_indexes=1
set global log_throttle_queries_not_using_indexes=1000;
[玩转MySQL之八]MySQL日志分类及简介 - 知乎