MySQL日志管理和权限管理（重点）

一、日志管理

日志类型

• 1、错误日志：启动，停止，关闭失败报错。rpm安装日志位置/var/log/mysqld.log 默认开启
• 2、通用查询日志：所有的查询都记下来。默认关闭，一般不开启
• 3、二进制日志(bin log)：实现备份，增量备份。只记录改变数据，除了select都记
• 4、中继日志(Relay log)：读取主服务器的binlog，在slave机器本地回放，保持与主服务器数据一致
• 5、slow log：慢查询日志，指导调优，定义某一个查询语句，执行时间过长，通过日志提供调优建议给开发人员
• 6、DDL log： 数据定义语言的日志

1.错误日志

[root@localhost ~]# mkdir -p /usr/local/mysql/logs/
[root@localhost ~]# touch /usr/local/mysql/logs/mysqld.log
[root@localhost ~]# chown -R mysql:mysql /usr/local/mysql/logs/
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
log-error=/usr/local/mysql/logs/mysqld.log
//log-error=/var/log/mysqld.log
//编译安装的在/usr/local/mysql/，yum安装的在/var/log/mysqld.log
[root@localhost ~]# systemctl restart mysqld

2.二进制日志

//Binary Log：需要提前开启
[root@localhost ~]# mkdir -p /usr/local/mysql/logs/mysql-bin
[root@localhost ~]# chown -R mysql:mysql /usr/local/mysql/logs/mysql-bin
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
log-bin=/usr/local/mysql/logs/mysql-bin/mylog  //如果不指定路径默认在/var/lib/mysql
server-id=1   //AB复制的时候使用，为了防止相互复制，会设置一个ID，来标识谁产生的日志
[root@localhost ~]# systemctl restart mysqld
​
//查看binlog日志
[root@localhost]# cd /usr/local/mysql/logs/mysql-bin/
[root@localhost mysql-bin]# ls
mylog.000001  mylog.index
​
[root@localhost mysql-bin]# mysqlbinlog mylog.000001 -v
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8
​
//取消字符集查看binlog日志
[root@localhost mysql-bin]# mysqlbinlog --no-defaults mylog.000001  -v
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#230706 11:51:53 server id 1  end_log_pos 123 CRC32 0x510185a5  Start: binlog v 4, server v 5.7.41-log created 230706 11:51:53 at startup
#Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
WTqmZA8BAAAAdwAAAHsAAAABAAQANS43LjQxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABZOqZkEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AaWFAVE=
'/*!*/;
# at 123
#230706 11:51:53 server id 1  end_log_pos 154 CRC32 0x8293327a  Previous-GTIDs
#[empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
#End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
#注：
1. 重启mysqld 会截断日志   
2. mysql> flush logs;   //会截断
3. mysql> reset master;   //删除所有binlog，不要轻易使用，相当于：rm -rf /
4. 删除部分
    mysql> PURGE BINARY LOGS TO 'mylog.000002';   //删除mysqllog.000002之前的日志 purge：清除
5. 暂停 仅当前会话
    SET SQL_LOG_BIN=0;  //关闭
    SET SQL_LOG_BIN=1;  //开启
=======================================================================
解决binlog日志不记录insert语句:
1. 登录mysql后，设置binlog的记录格式：
        mysql> set binlog_format=statement;
2. 然后，最好在my.cnf中添加：
        binlog_format=statement
3.修改完配置文件之后记得重启服务

3.慢查询日志

1、Slow Query Log ：慢查询开关打开后，并且执行的SQL语句达到参数设定的阈值后，就会触发慢查询功能打印出日志
2、慢查询日志主要是记录执行时间超过指定阈值的SQL语句，这些执行时间较长的语句称为"慢查询"。
3、作用：监控和分析慢查询，有助于优化SQL和数据库架构。通过分析慢查询，可以了解哪些SQL执行效率低下，哪些表或者索引需要优化。

[root@localhost ~]# mkdir /usr/local/mysql/logs/mysql-slow/
[root@localhost ~]# touch /usr/local/mysql/logs/mysql-slow/slow.log
[root@localhost ~]# chown mysql.mysql /usr/local/mysql/logs/mysql-slow/
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
slow_query_log=1  //开启
slow_query_log_file=/usr/local/mysql/logs/mysql-slow/slow.log
long_query_time=3    //设置慢查询超时间，单位是秒，就是超过3秒的sql查询语句就被认为慢查询sql
[root@localhost ~]# systemctl restart mysqld
//验证查看慢查询日志
mysql> select sleep(6);     //等待 6 秒钟，然后返回结果
​[root@localhost ~]# cat /usr/local/mysql/logs/mysql-slow/slow.log

二、权限管理(重点)

1.用户登录管理

1.1.本地登录

[root@localhost ~]# mysql -uroot -p'Jianglt@123'

1.2.客户端远程登陆
需要注意的是：远程端也需要有mysql的客户端才行
客户端语法：mysql -u 用户名 -P 端口号 -p 密码 -h ip地址
-P端口号：如果是默认端口号就不用-P指定端口

[root@localhost ~]# mysql -h192.168.17.131 -P 3306 -uroot -p'Jianglt@123'
-bash: mysql: 未找到命令
[root@localhost ~]# yum -y install mysql
[root@localhost ~]# mysql -h192.168.17.131 -P 3306 -uroot -p'Jianglt@123'
ERROR 1130 (HY000): Host '192.168.17.128' is not allowed to connect to this MySQL server
如果报错进入server端服务器，登陆mysql执行授权操作
mysql> use mysql;
mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> flush privileges;

1.3.免入库的方法执行sql语句

[root@localhost ~]# mysql -h192.168.17.131 -P 3306 -uroot -p'Jianglt@123' -e 'show databases;'

-h 指定主机名 【默认为localhost】
-大P MySQL服务器端口 【默认3306】
-u 指定用户名 【默认root】
-p 指定登录密码 【默认为空密码】
-e 接SQL语句，可以写多条用;隔开
​

[root@localhost ~]# mysql -h192.168.17.131 -P 3306 -uroot -p'Jianglt@123' -D mysql -e 'select * from user;'
//此处 -D mysql为指定登录的数据库
​
//同时执行2条sql语句
[root@localhost ~]# mysql -h192.168.17.131  -P 3306 -uroot -p'Jianglt@123' -D company -e 'show tables;select  * from employee5;'
+-------------------+
| Tables_in_company |
+-------------------+
| employee5         |
| new_t1            |
| new_t2            |
+-------------------+
+----+---------+--------+------------+------------+-----------------+--------+--------+
| id | name    | sex    | hire_date  | post       | job_description | office | dep_id |
+----+---------+--------+------------+------------+-----------------+--------+--------+
|  3 | jack    | male   | 2018-02-02 | instructor | teach           |    501 |    100 |
|  4 | harry   | male   | 2018-02-02 | hr         | NULL            |    502 |    101 |
|  5 | lili    | female | 2018-02-06 | sale       | salecc          |    503 |    102 |
|  6 | xiaoguo | male   | 2018-02-05 | sale       |                 |    503 |    102 |
+----+---------+--------+------------+------------+-----------------+--------+--------+

​1.4.修改端口（rpm安装）

[root@localhost ~]# vim /etc/my.cnf
port=2222
[root@localhost ~]# systemctl restart mysqld
//在到【mysqld】标签下面添加port=指定端口重启服务

2.创建用户及授权

2.1.方法一：CREATE USER语句创建

#创建用户为tom，并设置密码
mysql> create user tom@'localhost' identified by 'Jianglt@123'; 

​#更新授权表
mysql> flush privileges; 

#查询现有的用户
mysql> select host,user from mysql.user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | root          |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | tom           |
+-----------+---------------+
4 rows in set (0.00 sec)
注：
identified by 是设置密码的
在用户tom@'  ' 这里可以选择：
%：允许所有主机远程登陆包括localhost。也可以指定某个ip，允许某个ip登陆。也可以是一个网段。
localhos：只允许本地用户登录

客户端登录的方式：
            %                       所有主机远程登录
            192.168.246.%           192.168.246.0网段的所有主机
            192.168.246.252         指定主机
            localhost               只允许本地用户登录
mysql> create user jack@'192.168.17.%' identified by'Jianglt@123';
Query OK, 0 rows affected (0.00 sec)
​
mysql> select host,user from mysql.user;
+--------------+---------------+
| host         | user          |
+--------------+---------------+
| %            | root          |
| 192.168.17.% | jack          |
| localhost    | mysql.session |
| localhost    | mysql.sys     |
| localhost    | tom           |
+--------------+---------------+
5 rows in set (0.00 sec)

2.2.方法二：GRANT 创建用户并同时授权
（注：mysql 8.0必须是先创建用户再授权）
语法：grant 权限列表 on 库名.表名 to '用户名'@'客户端主机' IDENTIFIED BY '口令'；
all privileges：授予所有权限，包括SELECT、INSERT、UPDATE、DELETE 等所有权限
on *.*：代表对所有数据库( * )下所有表(.*)

权限列表：

all	所有权限（不包括授权权限）
select,update	查询，更新的权限
select,insert	查询，插入的权限

数据库.表名：

*.*	所有库下的所有表
web.*	web库下的所有表
web.student_info	web库下的student_info表

单独授权

#给刚才创建的用户tom授权：
mysql> grant select,insert on *.* to 'tom'@'localhost' identified by 'Jianglt@123';
mysql> flush privileges;
案例：
#创建test用户，并授权所有权限，只允许本地登录，密码为ABcabc123.com@
mysql> grant all privileges on *.* to 'test'@'localhost' IDENTIFIED BY 'ABcabc123.com@';

#如果用户已存在，则会修改test用户的登录方式（改成所有主机）和修改登陆密码；如果用户不存在，则会创建
mysql> grant all privileges on *.* to test@'%' identified by 'Jianglt@123';
mysql> flush privileges;

备注：
1.无用户时可以创建用户并指定口令
2.口令有密码复杂性要求
3.%表示任意主机
口令复杂性要求出错提示：
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
需要执行两个参数来把mysql默认的密码强度的取消

mysql> set global validate_password_policy=0; 
mysql> set global validate_password_mixed_case_count=2;
set global validate_password_policy=0;

控制MySQL密码强度验证的级别：
0 - 低级别，不做任何密码强度验证
1 - 中等级别，密码必须长度为8字节且不能和用户名相同
2 - 高级别，密码必须符合 medum 级别并且包括数字、大小写字母和特殊字符等
set global validate_password_mixed_case_count=2;
控制密码中必须包含的大小写字符数；设置为2表示要求密码中至少包含2个大小写字符

#修改远程登陆：将原来的localhost修改为%或者ip地址
mysql> select host,user from mysql.user;
+--------------+---------------+
| host         | user          |
+--------------+---------------+
| %            | root          |
| 192.168.17.% | jack          |
| localhost    | mysql.session |
| localhost    | mysql.sys     |
| localhost    | test          |
| localhost    | tom           |
+--------------+---------------+
7 rows in set (0.00 sec)
​
mysql> use mysql
mysql> update user set host = '192.168.17.%' where user = 'tom';
mysql> flush privileges;
​
mysql> select host,user from mysql.user;
+--------------+---------------+
| host         | user          |
+--------------+---------------+
| %            | root          |
| %            | test          |
| 192.168.17.% | jack          |
| 192.168.17.% | tom           |
| localhost    | mysql.session |
| localhost    | mysql.sys     |
| localhost    | test          |
+--------------+---------------+
7 rows in set (0.00 sec)

3.刷新权限

修改表之后需要刷新权限

mysql > flush privileges;

4.权限简介

权限	权限级别	权限说明
CREATE	数据库、表或索引	创建数据库、表或索引权限
DROP	数据库或表	删除数据库或表权限
GRANT OPTION	数据库、表或保存的程序	赋予权限选项
ALTER	表	更改表，比如添加字段、索引等
DELETE	表	删除数据权限
INDEX	表	索引权限
INSERT	表	插入权限
SELECT	表	查询权限
UPDATE	表	更新权限
LOCK TABLES	服务器	管理锁表权限
CREATE USER	服务器	管理创建用户权限
REPLICATION SLAVE	服务器	管理复制权限
SHOW DATABASES	服务器	管理查看数据库权限

5.查看权限

5.1.看自己的权限

mysql> show grants;
+-------------------------------------------------------------+
| Grants for root@%                                           |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
1 row in set (0.00 sec)
​
mysql> show grants\G
*************************** 1. row ***************************
Grants for root@%: GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION

5.2.看别人的权限

mysql> show grants for tom@'localhost';
+--------------------------------------------------+
| Grants for tom@localhost                         |
+--------------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO 'tom'@'localhost' |
+--------------------------------------------------+
1 row in set (0.00 sec)
​
mysql> show grants for tom@'localhost'\G
*************************** 1. row ***************************
Grants for tom@localhost: GRANT SELECT, INSERT ON *.* TO 'tom'@'localhost'  

6.移除用户权限
语法：REVOKE 权限 ON 数据库.数据表 FROM '用户'@'IP地址';
被回收的权限必须存在，否则会出错
整个数据库，使用 ON datebase.*;
特定的表：使用 ON datebase.table;
6.1.回收指定的权限

#先查看一下tom的权限
mysql> show grants for tom@'localhost';
+--------------------------------------------------+
| Grants for tom@localhost                         |
+--------------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO 'tom'@'localhost' |
+--------------------------------------------------+
1 row in set (0.00 sec)
​
#回收指定的权限
mysql> revoke select,insert on *.* from tom@'localhost'; 
​
#回收完后再次查看
mysql> show grants for tom@'localhost';
+-----------------------------------------+
| Grants for tom@localhost                |
+-----------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'localhost' |
+-----------------------------------------+
1 row in set (0.00 sec)
#USAGE的意思：该用户仅被分配了最小的USAGE权限，只允许访问，但无法操作数据
mysql> flush privileges;

6.2.回收所有权限

#​先查看一下test的权限
mysql> show grants for test@'%';
+-------------------------------------------+
| Grants for test@%                         |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' |
+-------------------------------------------+
1 row in set (0.00 sec)
​
#回收所有权限
mysql> revoke all privileges on *.* from test@'%'; 
mysql> show grants for test@'%';
+----------------------------------+
| Grants for test@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%' |
+----------------------------------+
1 row in set (0.00 sec)
mysql> flush privileges;

7.修改密码

7.1.root用户修改自己密码

方法一：#123为旧密码
mysql> mysqladmin  -uroot -p'Jianglt@123' password 'Jianglt@123!'
方法二:
mysql> SET PASSWORD='new_password';
报错：
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
​
#你需要执行两个参数来把mysql默认的密码强度的取消了才行
mysql> set global validate_password_policy=0; 
mysql> set global validate_password_mixed_case_count=2;
#这时你再执行
SET PASSWORD = PASSWORD('12345678');

7.2.root修改其他用户密码

mysql> use mysql
#mysql> SET PASSWORD FOR test@'localhost'='new_password'
#用户     =  新密码                                
mysql> set password for test@'localhost'='12345678';
Query OK, 0 rows affected (0.00 sec) 

8、删除用户

8.1.方法一：DROP USER语句删除

mysql> drop user test;
Query OK, 0 rows affected (0.00 sec)

8.2.方法二：DELETE语句删除

mysql> DELETE FROM mysql.user  WHERE user='tom' AND host='localhost';​
#更新授权表：
mysql> FLUSH PRIVILEGES;

9、查看密码复杂度

MySQL 默认启用了密码复杂度设置，插件名字叫做 validate_password，初始化之后默认是安装的
如果没有安装执行下面的命令会返回空或者没有值，这时需要安装该插件

#启用validate_password插件
mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';
#如果存在，会报错
mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';
ERROR 1125 (HY000): Function 'validate_password' already exists

9.1.查看密码复杂度

mysql> show variables like 'validate%';  #validate：验证
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+

参数解释：
validate_password_length ：密码最少长度，默认值是8最少是0
validate_password_dictionary_file：用于配置密码的字典文件，字典文件中存在的密码不得使用。
validate_password_policy： 代表的密码策略，默认是MEDIUM（中等强度）
LOW： 不进行任何密码强度检查，任何密码都可以被接受。
MEDIUM： 密码长度至少8个字符，并且不能与用户名相同。
STRONG： 除了满足 MEDIUM 之外，密码还必须包含至少一个大写字母，一个小写字母，一个数字和一个特殊字符。
​
validate_password_number_count ：最少数字字符数，默认1最小是0
validate_password_mixed_case_count ：最少的大写字符和小写字符数（同时有大写和小写），默认为1最少是0
validate_password_special_char_count ：最少特殊字符数，默认1最小是0

​9.2.查看密码策略

mysql> select @@validate_password_policy;
+----------------------------+
| @@validate_password_policy |
+----------------------------+
| MEDIUM                     |
+----------------------------+
1 row in set (0.00 sec)

策略：
0 or LOW 设置密码长度（由参数validate_password_length指定）
1 or MEDIUM 满足LOW策略，同时还需满足至少有1个数字，小写字母，大写字母和特殊字符
2 or STRONG 满足MEDIUM策略，同时密码不能存在字典文件（dictionary file）中

9.3.查看密码的长度

mysql> select @@validate_password_length; 
+----------------------------+
| @@validate_password_length |
+----------------------------+
|                          8 |
+----------------------------+
1 row in set (0.00 sec)

​9.4.设置密码复杂度

mysql> set global validate_password_length=1;   #设置密码长度为1个
mysql> set global validate_password_number_count=2;  #设置密码数字最少为2个

9.5.设置密码复杂性策略

mysql> set global validate_password_policy=LOW;     #设置密码策略，数字也可以
mysql> flush privileges; #刷新授权