（四）MySQL安全机制和日志管理（mysql运维）

最新推荐文章于 2023-07-05 09:12:14 发布

硬核的无脸man~

最新推荐文章于 2023-07-05 09:12:14 发布

阅读量598

点赞数

分类专栏：数据库运维

本文链接：https://blog.csdn.net/Until_U/article/details/105427553

版权

数据库运维专栏收录该内容

14 篇文章 5 订阅

订阅专栏

学习预览：

（一）mysql 运维基础篇（Linux云计算从入门到精通）

（二）mysql 索引和视图（数据库运维基础补充）

（三）mysql 触发器、存储过程和函数（数据库运维基础补充）

（四）MySQL安全机制和日志管理（mysql运维）

（五）mysql数据备份—物理备份(完备+lvm快照+xtrabackup)+逻辑备份(mysqldump+导入导出)

（六）mysql复制技术—M-S主从配置（传统+GTID）+M-M-S-S主从配置（GTID）

（七）mysql中间件mycat配置和部署（基于M-M-S-S）

（八）数据库集群技术—Galera Cluster安装与配置

（九）数据库集群技术Galera+mycat（数据库运维学习终章）

CONTEND

一、mysql安全机制

1.1 MySQL权限表

我们主要负责以下四种权限表的设置：

mysql.user Global level
用户字段
权限字段
安全字段
资源控制字段
mysql.db Database level
用户字段
权限字段
mysql.tables_priv Table level
mysql.columns_priv Column level

1.2 MySQL用户管理

（1）1.登录和退出MySQL

示例:
mysql -h192.168.5.240 -P 3306 -u root -p123 mysql -e 'select user,host from user'
-h 指定主机名 [默认为localhost]
-P MySQL服务器端口    [默认3306]
-u指定用户名    [默认root]
-p指定登录密码    [默认为空密码]
此处mysql为指定登录的数据库
-e接SQL语句

（2）创建用户

以前有的版本好像可以直接用grant同时创建用户并授权的，我这个版本并不支持，不清楚的话用help看看语法。
CREATE USER语句创建，接着授权，后面在介绍授权的细节。对了，由于MySQL8.0之后的加密规则为caching_sha2_password.而在此之前的加密规则为mysql_native_password。要想其他客户端连接的话，需要加上mysql_native_password哦，如下图：

create user 'user1'@'localhost' identified  with mysql_native_password by 'Root@123';--创建用户

grant all on *.* to 'user1'@'localhost' ; --授权然后自己登陆试试

select * from user\G  --可以在user表中查看自己创建的用户信息

（3）删除用户

drop user 'user1'@'localhost';

（4）修改用户密码（我发现不同版本的mysql的设置语法不太一样，不清楚用help）

#root修改自己的密码，直接在终端修改
[root@Centos7-host1 ~]# mysqladmin -uroot -p'Root@123' password 'Root@321'

#或者在mysql里修改,以下两种都可以
mysql> alter user root@localhost identified by 'Root@123';
mysql> set password for root@localhost='Root@123';


#root修改其他用户密码
mysql> create user 'root1'@'localhost' identified  with mysql_native_password by 'Root@123';--创建用户

mysql> grant all on *.* to 'root1'@'localhost' ; --授权然后自己登陆试试

mysql> set password for root1@192.168.119.156='Root@321' --修改密码再重新登录

#用户修改自己的密码
mysql> set password='Root@123';

1.3 MySQL权限管理

权限应用的顺序：user ==> db ==> table_priv ==> columns_priv

示例：

create user 'admin1'@'localhost' identified with mysql_native_password by 'Root@123';

create user 'admin2'@'192.168.119.%' identified with mysql_native_password by 'Root@123';

create user 'admin3'@'192.168.119.156' identified with mysql_native_password by 'Root@123';

create user 'admin4'@'192.168.119.158' identified with mysql_native_password by 'Root@123';

grant all on *.* to admin1@localhost with grant option; --赋予admin所有权限，这个一般不建议

grant all on bbs.* to admin2@'192.168.119.%' ; --赋予admin2对bbs数据库的管理权限

grant all on bbs.user to admin3@'192.168.119.156' ;--只赋予admin3对bbs数据库中user表的管理权限

grant select(id),insert(name,age) on bbs.user to admin4@'192.168.119.158';--针对字段了，不过这个太变态了，很少用

删除用户很简单： drop user admin1@'localhost';

二、MySQL日志管理

2.1 错误日志

配置方式：在配置文件中（my.cnf）中添加log_error=path 即可。错误日志默认就是开启的，默认存放在/var/log/mysqld.log下面，具体位置可通过查看/etc/my.conf文件得知。在运行mysql时候出现问题可通过查看该日志助于排错。

log-error=/var/log/mysqld.log 后面试错误日志的存储路径#

2.2 二进制日志（binlog）

二进制日志作用是增量备份，数据恢复。他记录了数据库所有变化的操作，如DDL、DCL、DML，其实就是SQL语句

（1）配置介绍

server-id=6  #主从复制需要用到
log_bin=/var/log/binlog/mysql-bin #指定存放位置mysql-bin名称前缀
binlog_format=row #日志格式

#这个文件夹需要自己创的，并且授权给mysql用户，重启生效
[root@Centos7-host1 ~]# mkdir /var/log/binlog/
[root@Centos7-host1 ~]# chown mysql.mysql /var/log/binlog/
[root@Centos7-host1 ~]# systemctl restart mysqld

（2）二进制日志格式

DDL和DCL，以statement（语句）方式直接记录SQL
DML（insert、update、delete），记录的是已经提交的事物
SBR ：statement，记录具体语句（5.6以下版本常用）
RBR ：Row ,记录数据行的变化（常用）
MBR ：mixed,混合模式（一般不用）

（3）日志内容

以事件(event) 作为记录的最小单元

以下（截取）为一个事件，以一个at开始，到下一个at结束

[root@Centos7-host1 ~]# mysqlbinlog /var/log/binlog/mysql-bin.000001 
# at 4
#200410 18:05:57 server id 6  end_log_pos 124 CRC32 0x7eccf0b6 	Start: binlog v 4, server v 8.0.19 created 200410 18:05:57 at startup
# at 124

（4）我们可以在mysql客户端查看二进制的基本情况（ctrl+z==>fg在shell和mysql客户端之间切换）

#查看有哪些二进制日志
mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       178 | No        |
| mysql-bin.000002 |       155 | No        |
+------------------+-----------+-----------+

#刷新
mysql> flush logs;

mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       178 | No        |
| mysql-bin.000002 |       202 | No        |
| mysql-bin.000003 |       155 | No        |
+------------------+-----------+-----------+

#查看正在使用的二进制日志
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      155 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

#查看某二进制日志的事件信息
mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                              |
+------------------+-----+----------------+-----------+-------------+-----------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |         6 |         124 | Server ver: 8.0.19, Binlog ver: 4 |
| mysql-bin.000001 | 124 | Previous_gtids |         6 |         155 |                                   |
| mysql-bin.000001 | 155 | Stop           |         6 |         178 |                                   |
+------------------+-----+----------------+-----------+-------------+------------------

（5）二进制日志的截取

注意：重启mysqld和flush logs会截断，reset master会删除所有的binlog，这个要谨慎，否则以后有问题就没法恢复了。

关于截取binlog有以下几种方法，一般选择position截取，然后进行恢复，关于数据恢复我会在在后面备份中学习总结下哦。

截取全部
# mysqlbinlog mysql-bin.000001 
按datetime截取
# mysqlbinlog mysql-bin.000003 --start-datetime="2004-10-01 18:29:59"
# mysqlbinlog mysql-bin.000003 --stop-datetime="2004-10-02 18:29:59"
# mysqlbinlog mysql-bin.000003 --start-datetime="2004-10-01 18:29:59" --stop-datetime="2004-10-02 18:29:59"

按position截取
# mysqlbinlog mysql-bin.000003 --start-position=260
# mysqlbinlog mysql-bin.000003 --stop-position=260
# mysqlbinlog mysql-bin.000003 --start-position=260 --stop-position=360

2.3 slow log（慢日志）

作用：记录MYSQL运行期间执行较慢的语句，这样可以告诉开发者哪里有问题。

（1）配置情况

slow_query_log=1 #打开慢日志
slow_query_log_file=/var/log/mysql_slow/slow.log #默认路径可手动指定
long_query_time=5 #超过多长时间被定义为慢语句

 mkdir /var/log/mysql_slow
 chown mysql.mysql /var/log/mysql_slow/
 systemctl restart mysqld

（2）查看慢日志是否打开，slow_query_log 状态为ON 表示打开。

mysql> show variables like '%slow%';

+---------------------------+------------------------------+
| Variable_name             | Value                        |
+---------------------------+------------------------------+
| log_slow_admin_statements | OFF                          |
| log_slow_extra            | OFF                          |
| log_slow_slave_statements | OFF                          |
| slow_launch_time          | 2                            |
| slow_query_log            | ON                           |
| slow_query_log_file       | /var/log/mysql_slow/slow.log |
+---------------------------+------------------------------+

（3）查看慢日志记录时间

当查询语句大于等于long_query_time这个值得时间(单位S)就会被定义为查询慢的语句，就会被慢日志记录起来

mysql> show variables like '%long%';
+----------------------------------------------------------+----------+
| Variable_name                                            | Value    |
+----------------------------------------------------------+----------+
| long_query_time                                          | 5.000000 |
| performance_schema_events_stages_history_long_size       | 10000    |
| performance_schema_events_statements_history_long_size   | 10000    |
| performance_schema_events_transactions_history_long_size | 10000    |
| performance_schema_events_waits_history_long_size        | 10000    |
+----------------------------------------------------------+----------+

（4）慢日志测试

mysql> select benchmark(500000000,2*3); #就是一个函数，让2*3计算这么多次
+--------------------------+
| benchmark(500000000,2*3) |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (7.88 sec)

#查看慢日志是否有记录
tail /var/log/mysql_slow/slow.log 

#或者用这个查询
mysqldumpslow -s c -t 3 slow.log

先简单了解概念，后面我们在备份恢复中再详细学习一番😀。

硬核的无脸man~

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
打赏
0
评论
（四）MySQL安全机制和日志管理（mysql运维）

CONTEND一、mysql安全机制1.1 MySQL权限表1.2 MySQL用户管理1.3 MySQL权限管理二、MySQL日志管理2.1 错误日志2.2 二进制日志（binlog）2.3 slow log（慢日志）一、mysql安全机制1.1 MySQL权限表我们主要负责以下四种权限表的设置：mysql.user ...
复制链接

扫一扫