mysql日志的作用:
-
故障排查与恢复:MySQL日志可以记录数据库的活动,包括查询、更改和错误信息,这些信息对于故障排查和恢复非常关键。通过查看日志,可以找到数据库发生故障的原因,并采取相应的措施来修复问题。
-
性能优化:通过分析MySQL日志,可以了解数据库的运行状况,包括查询的执行时间、频率以及慢查询等信息。这有助于识别性能瓶颈并进行优化,提高数据库的性能。
-
安全性:MySQL日志可以记录用户对数据库的操作,包括登录、查询、更新等,这有助于跟踪用户的行为并确保数据库的安全性。通过审计日志,可以查看谁在何时对数据库进行了操作。
-
复制与恢复:MySQL的二进制日志(binary log)用于数据库的复制和恢复。通过记录所有更改数据的SQL语句,二进制日志可以用于在主数据库和从数据库之间同步数据,以及在灾难恢复时还原数据库。
-
事务管理:MySQL的事务日志(transaction log)用于记录事务的开始和提交情况,以确保数据库的一致性和持久性。在数据库发生故障时,事务日志可以用于回滚未完成的事务,避免数据丢失或损坏。
日志分类:
1、错误日志(Error log):记录MySQL服务器启动、关闭运行过程中出现的错误信息。
2、查询日志(Query log):记录所有客户端连接到MySQL服务器并执行的SQL语句。
3、慢查询日志(Slow Query log):记录执行时间超过指定时间阈值的SQL语句。
4、二进制日志(binary log):记录所有对MySQL数据库进行的更改操纵,包括增删改查。
5、事务日志(transaction log):记录MySQL数据库中的事务操作,用于恢复数据库到某个特定的时间点。
6、中继日志(relay log):在MySQL主从复制中使用,记录主服务器上的二进制日志信息,并在从服务器上重放这些日志。
7、撤销日志(undo log):记录MySQL数据库中的事务操作,用于回滚事务。
mysql程序错误日志的开启:
1、编辑mysql的配置文件:打开mysql的配置文件(my.cnf),通常在/etc/my.cnf或/etc/mysql/my.cnf 目录下查找文件。
2、启用错误日志:在配置文件中找到【mysqld】部分,添加以下启用错误日志。
log_error=/var/log/mysql/error.log
-
可以把错误日志文件存储到/var/log/mysql/error.log位置.需要更改日志文件位置和名称
-
使用SQL语句查看mysql数据目录
-
SHOW VARIABLES LIKE 'datadir';
3、保存并关闭配置文件:保存添加后的配置文件并关闭
4、重启mysql服务器以致mysql配置文件生效
systemctl restart mysql
-
使用命令查看mysql日志的存放位置
-
SHOW VARIABLES LIKE 'log_error';
-
如果mysql日志已经启用可以看出以下内容
-
+---------------+------------------------+ | Variable_name | Value | +---------------+------------------------+ | log_error | /var/log/mysql/error.log| +---------------+------------------------+
-
如果为启用测出现以下内容
-
+---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_error | | +---------------+-------+
查询日志:
1、在my.cnf配置文件中添加配置
[mysqld] general_log=1 log_queries_not_using_indexes=0 log_output=FILE general_log_file=/var/log/mysql/query.log
-
general_log=1
:开启查询日志功能。 -
log_queries_not_using_indexes=0
:不记录没有使用索引的查询操作。 -
log_output=FILE
:将查询日志输出到文件。 -
general_log_file=/var/log/mysql/query.log
:指定查询日志文件的路径和文件名为/var/log/mysql/query.log
。
2、查看MySQL日志文件是否开启
SHOW VARIABLES LIKE 'general_log%';
-
查询日志会对服务器产生压力可以选择性的做查询日志
二进制日志:
1、编辑mysql的配置文件:打开mysql的配置文件(my.cnf),通常在/etc/my.cnf或/etc/mysql/my.cnf 目录下查找文件。
2、在[mysqld]添加二进制文件存放位置
log_bin=/data/binlog/mysql-bin binlog_format=row
-
设置其值为ROW,表示使用行级别的格式记录更改操作
3、我们是指定文件存放位置并设权限,需要创建文件夹
mkdir -p /data/binlog chown -R mysql.mysql /data
4、重启mysql
systemctl restart mysqld
-
查看二进制日志位置:
-
show variables like '%log_bin%';
-
查看所有已存在的二进制日志
-
show binary logs;
-
查看正在使用的二进制文件
-
show master status
二进制恢复数据:
二进制记录了增删改操作,我们通过二进制可以还原数据信息 (1) 模拟数据
create database hehe charset utf8mb4; use hehe; create table t1(id int); insert into t1 values(1); commit;
(2)模拟故障
drop database hehe;
(3)分析和截取binlog
show master status ; --->确认使用的是哪一个日志 show binlog events in 'mysql-bin.000001' ; --->查看事件
说明: 找到起点和终点,进行截取
cd /data/binlog mysqlbinlog --start-position=219 --stop-position=758 /data/binlog/mysql-bin.000001 >/tmp/bin.sql
(4)恢复binlog
set sql_log_bin=0; --->临时关闭恢复时产生的新日志 source /tmp/bin.sql set sql_log_bin=1; --->改回来
慢查询日志开启:
1、在my.cnf主配置文件中添加配置选项
[mysqld] slow_query_log=1 slow_query_log_file=/data/slow.log long_query_time=0.1 log_queries_not_using_indexes
-
slow_query_log = 1
:表示开启慢查询日志功能。 -
slow_query_log_file = /data/slow.log
:指定慢查询日志文件的路径和名称为/data/slow.log
。 -
long_query_time = 0.1
:设置执行时间超过0.1秒的SQL查询语句会被记录到慢查询日志中。 -
log_queries_not_using_indexes
:记录执行未使用索引的查询语句 -
在生产环境中,要根据实际情况调整
long_query_time
的值,确保不会因为设置过低而导致记录过多的查询,影响性能。
2、使用sql查询慢日志是否开启
SHOW VARIABLES LIKE '%slow_query_log%';
-
查询出来value值为on则表示开启,如果出现off则表示关闭
-
slow_query_log_file:存储位置
3、分析慢日志
mysqldumpslow -s c -t 10 /data/slow.log
事务日志的开启:
1、修改my.cnf主配置文件
[mysqld] log_bin=/data/binlog/mysql-bin binlog_format=row
-
log_bin表示开启事务日志功能和存储位置
-
binlog_format表示事务的格式,row表示记录行的修改操作
2、事务日志我们指定位置存储需要创建目录
mkdir -p /data/binglog/ chown -R mysql.mysql /data/binlog
3、使用sql语句查看事务日志
SHOW VARIABLES LIKE 'log_bin';
如果事务日志功能已经开启,您将看到类似以下输出:
+---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+
-
log_bin的值为on,表示事务日志功能开启
show master status;
-
查询日志的文件名和位置
这条命令将显示当前事务日志文件名和位置信息,类似于以下输出:
+------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 107 | | | +------------------+----------+--------------+------------------+
show binlog events in 'mysql-bin.000001';
-
可以详细的查看日志信息
通过事务日志我们可以恢复数据:
mysqlbinlog --start-position=219 --stop-position=758 mysql-bin.000001 >/tmp/a.sql
-
219是起始位置758是结束位置,意思就是从哪里开始恢复到哪里结束,使用show binlog events in 'mysql-bin.000001'; 可以详细查看
故障模拟:
恢复数据 (1) 模拟数据
create database hehe charset utf8mb4; use hehe; create table t1(id int); insert into t1 values(1); commit;
(2)模拟故障
drop database hehe;
(3)分析和截取binlog
show master status ; -
mysql> show binlog events in 'mysql-bin.000001' ; --->查看事件 说明: 找到起点和终点,进行截取
cd /data/binlog mysqlbinlog --start-position=219 --stop-position=758 /data/binlog/mysql-bin.000001 >/tmp/bin.sql
(4)恢复binlog
set sql_log_bin=0; --->临时关闭恢复时产生的新日志 source /tmp/bin.sql set sql_log_bin=1; --->改回来
GTID介绍: 对于binlog中的每一个事务,都会生成一个GTID号码DDL ,DCL 一个event就是一个事务,就会有一个GTID号.DML语句来讲,begin到commit,是一个事务,就是一个GTID号.
2.6.2 GTID的组成 server_uuid:TID
cat /usr/local/mysql/data/auto.cnf [auto] server-uuid=e2e9b01e-9687-11eb-b577-000c29b0384b
TID是一个:自增长的数据,从1开始 e2e9b01e-9687-11eb-b577-000c29b0384b:1-15
GTID的幂等性: 如果拿有GTID的日志去恢复时,检查当前系统中是否有相同GTID号,有相同的就自动跳过 会影响到binlog恢复和主从复制.
1、GTID的开启和配置
vim /etc/my.cnf gtid-mode=on enforce-gtid-consistency=true
查看GTID信息
create database gtid charset utf8mb4; show master status; use gtid; create table t1(id int); show master status; insert into t1 values(1); commit; show master status; drop database gtid;
基于GTID,binlog恢复 (1) 截取日志
cd /usr/local/mysql/data/binlog/ mysqlbinlog --include-gtids='820f8917-d358-11ec-b243-000c29cbdce4:1-3' mysql-bin.000001 >/tmp/gtid.sql
(2)恢复
mysql> set sql_log_bin=0; mysql> source /tmp/gtid.sql mysql> set sql_log_bin=1;
(3) 报错 ERROR 1049 (42000): Unknown database 'gtid' Query OK, 0 rows affected (0.00 sec) ERROR 1046 (3D000): No database selected 为什么报错? 因为幂等性的检查,1-3事务已经做过了.
(4)正确的做法?
mysqlbinlog --skip-gtids --include-gtids='820f8917-d358-11ec-b243-000c29cbdce4:1-3' mysql-bin.000001 >/tmp/gtid.sql
-
--skip-gtids 作用:在导出时,忽略原有的gtid信息,恢复时生成最新的gtid信息
(5) 恢复
set sql_log_bin=0; source /tmp/gtid.sql set sql_log_bin=1;
GTID相关的参数 --skip-gtids --include-gtids='e2e9b01e-9687-11eb-b577-000c29b0384b:6','e2e9b01e-9687-11eb-b577-000c29b0384b:8' --exclude-gtids='e2e9b01e-9687-11eb-b577-000c29b0384b:6','e2e9b01e-9687-11eb-b577-000c29b0384b:8'