MySQL权限控制及日志管理

MySQL权限控制及日志管理

用户权限管理

创建用户

CREATE USER '用户名'@'IP地址' [ IDENTIFIED BY '密码' ]；

GRANT SELECT ON *.* TO '用户名'@’IP地址’ IDENTIFIED BY "密码"；


--创建一个用户名为Usr1 密码为 Usr1.mysql的用户 并授权
CREATE USER 'Usr1'@'192.168.1.128' IDENTIFIED BY "Usr1.mysql";
GRANT ALL ON *.* TO 'Usr1'@'192.168.1.128' IDENTIFIED BY 'Usr1.mysql';

--查看mysql.user表
mysql> SELECT Host,User FROM user WHERE User = 'Usr1';
+---------------+------+
| Host          | User |
+---------------+------+
| 192.168.1.128 | Usr1 |
+---------------+------+

--格式注意
用户名@IP地址 用户只能在改IP下才能访问
用户名@192.168.1.% 用户只能在改IP段下才能访问(通配符%表示任意)
用户名@%.baidu.com 
用户名@% 用户可以再任意IP下访问(默认IP地址为%)

删除用户

DROP USER '用户名'@'IP地址';

DELETE FROM mysql.user WHERE user='用户名' AND host='IP地址'

--删除Usr1
DROP USER 'Usr1'@'192.168.1.128';
--查看表
mysql> SELECT Host,User FROM user ;
+-----------+---------------+
| Host      | User          |
+-----------+---------------+
| %         | root          |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | rkun18        |
| localhost | root          |
+-----------+---------------+

修改用户

RENAME USER '旧用户名'@'IP地址' TO '新用户名'@'IP地址' ;

--修改rkun18用户让其在192.168.1.网段都可以登录mysql
mysql> RENAME USER 'rkun18'@'localhost' TO 'rkun18'@'192.168.1.%';

mysql> SELECT Host,User FROM user ;
+-------------+---------------+
| Host        | User          |
+-------------+---------------+
| %           | root          |
| 192.168.1.% | rkun18        |
| localhost   | mysql.session |
| localhost   | mysql.sys     |
| localhost   | root          |
+-------------+---------------+

修改密码

--修改完密码一定刷新权限
FLUSH PRIVILEGES;

--多种方法
mysqladmin -uroot -p旧密码 password 'new_password' 

alter user 'root'@'localhost' identified by 'new_pssword';

SET PASSWORD=password(‘new_password’);


--root用户修改其他用户密码
alter user '其他用户名'@'localhost' identified by '密码';

GRANT SELECT ON *.* TO 用户名@’ip地址’ IDENTIFIED BY ‘密码’;

--普通用户修改自己密码
SET password=password(‘new_password’);

找回root密码

• 修改mysql配置文件[mysqld]加上skip-grant-tables

#设置免密登录
[root@mysql ~]# vim /etc/my.cnf
[root@mysql ~]# cat /etc/my.cnf | grep skip
skip-grant-tables

• 重启mysql

#终端输入mysql直接登录mysql数据库
[root@mysql ~]# systemctl restart mysqld
[root@mysql ~]# mysql

--切换系统库mysql
mysql> use mysql;

--设置密码
update user set authentication_string=password('密码') where user='root';

--之后注释掉免密登录 重启mysql 然后使用你设置的密码登录

密码复杂度

• mysql默认启用密码复杂度设置，插件名：validate_password

mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';



#修改配置文件
vim /etc/my.cnf

[mysqld]
plugin-load=validate_password.so
validate_password_policy=0
validate-password=FORCE_PLUS_PERMANENT


systemctl restart mysqld

#查看
mysql> show variables like '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             | LOW   |
| validate_password_special_char_count | 1     |
+--------------------------------------+-------+

--创建一个用户 使用较短的密码 显示没有满足策略要求
mysql> grant all on *.* to admin@'%' identified by '1234';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

--查看密码策略
mysql> select @@validate_password_policy;
+----------------------------+
| @@validate_password_policy |
+----------------------------+
| LOW                        |
+----------------------------+

--查看密码复杂性最低要求
mysql> select @@validate_password_length;
+----------------------------+
| @@validate_password_length |
+----------------------------+
|                          8 |
+----------------------------+

--更换密码复杂性策略 设置为1
mysql> set global validate_password_length=1;

--关闭策略

mysql>  set global validate_password_policy=0;

--再次设置用户和密码 成功
mysql> grant all on *.* to admin@'%' identified by '1234';
Query OK, 0 rows affected, 1 warning (0.00 sec)

--测试登录
[root@mysql ~]# mysql -uadmin -p1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.42 MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
--成功！
--永久修改就修改my.cnf文件

远程登录

mysql -u用户名 -p密码 [ -h主机 ] [ -P端口 ]；
mysql -u用户名 -p密码 [ -h主机 ] [ -P端口 ] [ -e"SQL语句" ]；

日志管理

错误日志

• 记录mysql启动过程中状态，报错，警告等信息。

• 设置错误日志

  #配置错误日志
  [root@mysql ~]# mkdir /logs
  [root@mysql ~]# vim /etc/my.cnf
  #修改权限 
  [root@mysql ~]# chown -R mysql:mysql /logs/
  
  [root@mysql ~]# cat /etc/my.cnf | grep log-error
  log-error=/logs/mysql-error.log
  
  #重启服务
  [root@mysql ~]# systemctl restart mysqld
  
  
  

  --进入mysql查看
  mysql> select @@log_error;
  +-----------------------+
  | @@log_error           |
  +-----------------------+
  | /logs/mysql-error.log |
  +-----------------------+
  
  
  
  

  #进入虚拟机查看日志内容是否存在
  [root@mysql ~]# tail /logs/mysql-error.log 
  2023-06-12T19:50:05.941676Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
  2023-06-12T19:50:05.945078Z 0 [Warning] CA certificate ca.pem is self signed.
  2023-06-12T19:50:05.945468Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
  2023-06-12T19:50:05.947524Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
  2023-06-12T19:50:05.947655Z 0 [Note] IPv6 is available.
  2023-06-12T19:50:05.947754Z 0 [Note]   - '::' resolves to '::';
  2023-06-12T19:50:05.947814Z 0 [Note] Server socket created on IP: '::'.
  2023-06-12T19:50:06.038988Z 0 [Note] Event Scheduler: Loaded 0 events
  2023-06-12T19:50:06.039879Z 0 [Note] /usr/sbin/mysqld: ready for connections.
  Version: '5.7.42'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
  #内容存在 说明配置成功
  
  

二进制日志

• 数据恢复必备日志
• 主从复制依赖

#修改配置文件
[root@mysql ~]# cat /etc/my.cnf | grep server_id 
server_id=6
[root@mysql ~]# cat /etc/my.cnf | grep log_bin
# log_bin
log_bin=/logs/binlogs/mysql-bin

#server_id 5.7版本后开启二进制日志必加参数
#创建文件夹 授权
[root@mysql ~]# mkdir -p /logs/binlogs/
[root@mysql ~]# chown -R mysql:mysql /logs/*

[root@mysql ~]# ll /logs/binlogs/
总用量 8
-rw-r-----. 1 mysql mysql 154 6月  12 16:13 mysql-bin.000001
-rw-r-----. 1 mysql mysql  31 6月  12 16:13 mysql-bin.index
#mysql-bin 配置文件配置的前缀 000001 mysql每次重启重新生成新的

• 二进制文件内容除了查询类的语句，都会记录，即所有数据库变更类语句。

mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+

--DDL、DCL直接以语句（statement）方式记录 .
--DML 语句有三种模式：SBR、RBR、MBR

--statement---->SBR：做什么记录什么，即SQL语句
--row---------->RBR：记录数据行的变化（默认模式，推荐）
--mixed-------->MBR：自动判断记录模式

#修改配置文件
[root@mysql ~]# vi /etc/my.cnf
[root@mysql ~]# cat /etc/my.cnf | grep binlog_format
binlog_format='ROW'
[root@mysql ~]# systemctl restart mysqld

--查看工作模式
mysql> show variables like "binlog%";
+--------------------------------------------+--------------+
| Variable_name                              | Value        |
+--------------------------------------------+--------------+
| binlog_cache_size                          | 32768        |
| binlog_checksum                            | CRC32        |
| binlog_direct_non_transactional_updates    | OFF          |
| binlog_error_action                        | ABORT_SERVER |
| binlog_format                              | ROW          |
| binlog_group_commit_sync_delay             | 0            |
| binlog_group_commit_sync_no_delay_count    | 0            |
| binlog_gtid_simple_recovery                | ON           |
| binlog_max_flush_queue_time                | 0            |
| binlog_order_commits                       | ON           |
| binlog_row_image                           | FULL         |
| binlog_rows_query_log_events               | OFF          |
| binlog_stmt_cache_size                     | 32768        |
| binlog_transaction_dependency_history_size | 25000        |
| binlog_transaction_dependency_tracking     | COMMIT_ORDER |
+--------------------------------------------+--------------+

二进制日志事件

• 事件为最小记录单位
• 对于DDL和DCL，语句即事件
• 对于DML，只记录提交的事务的DML语句

begin ; 事件1
a 事件2
b 事件3
commit; 事件4

查看二进制日志事件

[root@mysql ~]# cd /logs/binlogs/
[root@mysql binlogs]# mysqlbinlog mysql-bin.000001
...
AUIDoJM=
'/*!*/;
# at 123 #事件开始
#230612 16:18:14#事件发生时间 server id 6  end_log_pos #事件结束 154 CRC32 0x9c400eca  Previous-GTIDs
# [empty]
# at 154
#230612 16:33:25 server id 6  end_log_pos 177 CRC32 0x3ccc2292  Stop
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;#事件内容
...

--二进制日志配置
mysql>  show variables like '%log_bin%';
+---------------------------------+-------------------------------+
| Variable_name                   | Value                         |
+---------------------------------+-------------------------------+
| log_bin                         | ON                            |
| log_bin_basename                | /logs/binlogs/mysql-bin       |
| log_bin_index                   | /logs/binlogs/mysql-bin.index |
| log_bin_trust_function_creators | OFF                           |
| log_bin_use_v1_row_events       | OFF                           |
| sql_log_bin                     | ON                            |
+---------------------------------+-------------------------------+

--log_bin 开启二进制日志的开关
--log_bin_basename 位置
--sql_log_bin 临时开启或关闭二进制日志的小开关


--显示当前所有二进制日志文件
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       154 |
+------------------+-----------+

--显示二进制文件事件信息
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 |         123 | Server ver: 5.7.42-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids |         6 |         154 |                                       |
| mysql-bin.000001 | 154 | Stop           |         6 |         177 |                                       |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+

二进制日志文件数据恢复实例

• 操作

create database binlog charset utf8mb4;

use binlog;

create table t1(id int) engine=innodb charset=utf8mb4;

insert into t1 values (1),(2),(3);

insert into t1 values (11),(12),(13);

commit ;
--此时t1数据
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   11 |
|   12 |
|   13 |
+------+


update t1 set id=10 where id >10;

commit ;

--更改后
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   10 |
|   10 |
|   10 |
+------+

• 删除数据

drop database binlog;

• 数据恢复

--确认起点和终点
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     1631 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

mysql> show binlog events in 'mysql-bin.000002';
| mysql-bin.000002 |  154 | Anonymous_Gtid |         6 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  
...                                                                 |
| mysql-bin.000002 |  219 | Query          |         6 |         375 | /* ApplicationName=DataGrip 2023.1.2 */ create database binlog charset utf8mb4   --起点                           |
| mysql-bin.000002 | 1428 | Anonymous_Gtid |         6 |        1493 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' 

| mysql-bin.000002 | 1493 | Query          |         6 |        1631 | /* ApplicationName=DataGrip 2023.1.2 */ drop database binlog        --终点                         

• 截取日志

[root@mysql ~]# mysqlbinlog --start-position=219 --stop-position=1493 /logs/binlogs/mysql-bin.000002 > /data/bin.sql

• 恢复日志

mysql> set sql_log_bin=0; # 临时关闭当前会话的binlog记录
mysql> source /data/bin.sql;
mysql> set sql_log_bin=1; # 打开当前会话的binlog记录

• 查看是否恢复信息

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| binlog             |
| db1                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

mysql> use binlog;
Database changed
--成功恢复
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   10 |
|   10 |
|   10 |
+------+

基于gtid二进制日志管理

• 全局唯一事务编号
• 幂等性
• gtid包括：
  • server_uuid
  • tx_id

gtid配置

--查看gtid
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | OFF       |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | OFF       |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+

#修改配置
[root@mysql ~]# vi /etc/my.cnf

gtid_mode=on # 开启 gtid
enforce_gtid_consistency=true # 强制GTID一致性
log_slave_updates=1 # 主从复制中从库记录 binlog，并统一GTID信息

[root@mysql ~]# systemctl restart mysqld

基于gtid截取日志

• 对于DDL，DCL一个操作就是一个gtid
• 对于DML，一个完整事务就是gtid

mysql>  show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+


mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

• –include-gtids=包含
• –exclude-gtids=排除
• –skip-gtids=跳过

基于gtid截取日志实例

• 环境准备

--准备环境

create database gtid charset utf8mb4;

use gtid;

create table t1(id int) engine=innodb charset=utf8mb4;

insert into t1 values(1),(2),(3);

commit;

insert into t1 values(11),(12),(13);

 commit;
 
 mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   11 |
|   12 |
|   13 |
+------+

--删除数据
drop database gtid;
--查看当前数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

• 找寻起点终点（gtid）

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 |     1324 |              |                  | 5bae2709-0933-11ee-ad07-000c2977e40f:1-5 |
+------------------+----------+--------------+------------------+------------------------------------------+

show binlog events in 'mysql-bin.000001'

...  --省略
mysql-bin.000001,154,Gtid,6,219,SET @@SESSION.GTID_NEXT= '5bae2709-0933-11ee-ad07-000c2977e40f:1' --这里是起点 创建库
mysql-bin.000001,219,Query,6,369,/* ApplicationName=DataGrip 2023.1.2 */ create database gtid charset utf8mb4
... --省略
mysql-bin.000001,1127,Gtid,6,1192,SET @@SESSION.GTID_NEXT= '5bae2709-0933-11ee-ad07-000c2977e40f:5'
mysql-bin.000001,1192,Query,6,1324,/* ApplicationName=DataGrip 2023.1.2 */ drop database gtid --终点删除库  推荐一个数据库操作工具dataGrip很好用

• 截取日志

#5是删除数据库所有只截取到1-4
[root@mysql ~]# mysqlbinlog --skip-gtids --include-gtids='5bae2709-0933-11ee-ad07-000c2977e40f:1-4' /logs/binlogs/mysql-bin.000001 > /data/gtid.sql

• 恢复数据

mysql> set sql_log_bin=0;

mysql> source /data/gtid.sql
mysql> set sql_log_bin=1;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| gtid               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

mysql> use gtid;
Database changed
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   11 |
|   12 |
|   13 |
+------+
--数据恢复成功

二进制日志其他操作

自动清理日志

--查看自动清理周期
show variables like '%expire%';

disconnect_on_expired_password,ON
expire_logs_days,0

--设置自动清理周期
set global expire_logs_days = 7;
show variables like '%expire%';

disconnect_on_expired_password,ON
expire_logs_days,7

#永久生效

[root@mysql ~]# vi /etc/my.cnf
[root@mysql ~]# cat /etc/my.cnf | grep expire
expire_logs_days=15

[root@mysql ~]# systemctl restart mysqld

show variables like '%expire%';

disconnect_on_expired_password,ON
expire_logs_days,15