为了帮助管理员快速发现数据库的相关运行信息，MySQL为用户提供了几种日志种类，具体见表：
| 解释说明 | |
| 错误日志（error log） | 当数据库启动、运行、停止时产生该日志 |
| 普通查询日志（general query log） | 客户端连接数据库执行语句时产生该日志 |
| 二进制日志（binary log） | 当数据库内容发生改变时产生该日志，也被用来实现主从复制功能 |
| 中继日志（relay log） | 从库上收到主库的数据更新时产生该日志 |
| 慢查询日志（slow query log） | SQL语句在数据库查询超过指定时间产生该日志 |
| DDL日志（metadata log） |
select @@datadir; +-------------+ | @@datadir | +-------------+ | /data/3306/ | +-------------+ 1 row in set (0.00 sec)
ls -l /data/3306/db01.err -rw-r----- 1 mysql mysql 16264 Feb 28 13:46 /data/3306/db01.err
vim /etc/my.cnf
log-error = /data/mysql/error.err
chown -R mysql:mysql /data/mysql chmod -R 755 /data/mysql
show variables like ‘log_error%‘; +---------------------+----------------+ | Variable_name | Value | +---------------------+----------------+ | log_error | /tmp/mysql.log | | log_error_verbosity | 3 | +---------------------+----------------+ 2 rows in set (0.00 sec)
2020-02-25T08:35:07.064225Z 0 [ERROR] InnoDB: The innodb_system data file ‘ibdata1‘ must be writable 2020-02-25T08:35:07.064266Z 0 [ERROR] InnoDB: The innodb_system data file ‘ibdata1‘ must be writable 2020-02-25T08:35:07.064279Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error 2020-02-25T08:35:07.669244Z 0 [ERROR] Plugin ‘InnoDB‘ init function returned error. 2020-02-25T08:35:07.669304Z 0 [ERROR] Plugin ‘InnoDB‘ registration as a STORAGE ENGINE failed. 2020-02-25T08:35:07.669313Z 0 [ERROR] Failed to initialize builtin plugins. 2020-02-25T08:35:07.669329Z 0 [ERROR] Aborting
注：我的错误日志文件在/tmp/mysql.log
[root@db01 ~]# cd /tmp/ [root@db01 /tmp]# mv mysql.log error_$(date +%F).err #移动修改 [root@db01 /tmp]# mysqladmin flush-logs -uroot -p --执行刷新日志命令 Enter password: [root@db01 /tmp]# ls -l mysql.log -rw-r----- 1 mysql mysql 0 Mar 1 18:48 mysql.log
数据恢复：
vim /etc/my.cnf server_id=6 log_bin=/data/binlog/mysql-bin sync_binlog=1 binlog_format=row log-bin=my-binlog-name
log_bin=/data/binlog/mysql-bin # 日志存放目录+日志名前缀，例如： mysql-bin.000001
sync_binlog=1 # binlog日志刷盘策略，双一中的第二个1。每次事务提交立即刷写binlog到磁盘。
binlog_format=row # binlog的记录格式为row模式
log-bin=my-binlog-name
[root@db01 3306]# mkdir -p /data/binlog/ [root@db01 3306]# chown -R mysql.mysql /data/* 重启生效： [root@db01 3306]# /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! [root@db01 ~]# cd /data/binlog/ [root@db01 binlog]# ll total 8 -rw-r----- 1 mysql mysql 154 Feb 25 17:02 mysql-bin.000001 -rw-r----- 1 mysql mysql 30 Feb 25 17:02 mysql-bin.index
show variables like ‘%binlog_format%‘; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+
简介：日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。
优点：能清楚的记录每一行数据修改的细节
缺点：数据量大
statement level（5.6默认）
简介：每一条被修改数据的sql都会记录到master的bin-log中，slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql再次执行。在主从复制中一般是不建议用statement模式的，因为有些语句不支持，比如语句中包含UUID函数，以及LOAD DATA IN FILE语句等
优点：解决了Row level下的缺点，不需要记录每一行的数据变化，减少bin-log日志量，节约磁盘IO，提高性能。
缺点：容易出现主从复制不一致。
Mixed（混合模式）
2. 记录SQL语句种类：
DCL ：原封不动的记录当前DCL(statement语句方式)。
DML ：
二进制日志的最小记录单元，对于DDL,DCL,一个语句就是一个event，对于DML语句来讲:只记录已提交的事务。
事件（event）是MySQL在相应的时刻调用的过程式数据对象。一个事件可调用一次，也可以周期性启动，它由一个特定的线程来管理的，也就是所谓的“事件调度器”。
事件和触发器类似，都是在某些事情发生的时候启动。当数据库上启动一条语句的时候，触发器就启动了，而事件是根据调度事件来启动的。由于他们彼此相似，所以事件也称为临时性触发器。
position start stop begin; 120 - 340 DML1 340 - 460 DML2 460 - 550 commit; 550 - 760
事件的开始标识
事件内容
事件的结束标识
event开启
如果显示OFF,则输入以下语句开启：
SHOW VARIABLES LIKE ‘event_scheduler‘; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | ON | +-----------------+-------+ 1 row in set (0.00 sec) set global event_scheduler=on; #或者1
永久配置event
vim /etc/my.cnf event_scheduler=ON #重启mysql
select @@log_bin; select @@log_bin_basename;
[root@db01 binlog]# ls -l /data/binlog/ -rw-r----- 1 mysql mysql 177 Feb 25 17:08 mysql-bin.000001 -rw-r----- 1 mysql mysql 154 Feb 25 17:08 mysql-bin.000002 -rw-r----- 1 mysql mysql 60 Feb 25 17:08 mysql-bin.index [root@db01 binlog]# [root@db01 binlog]# cat mysql-bin.index /data/binlog/mysql-bin.000001 /data/binlog/mysql-bin.000002 [root@db01 binlog]# file mysql-bin.000001 mysql-bin.000001: MySQL replication log
# 是否启用binlog日志 show variables like ‘log_bin‘; # 查看详细的日志配置信息 show global variables like ‘%log%‘; # mysql数据存储目录 show variables like ‘%dir%‘; # 查看binlog的目录 show global variables like "%log_bin%"; # 查看当前服务器使用的biglog文件及大小 show binary logs; # 查看主服务器使用的biglog文件及大小 # 查看最新一个binlog日志文件名称和Position show master status; # 事件查询命令 # IN ‘log_name‘ ：指定要查询的binlog文件名(不指定就是第一个binlog文件) # FROM pos ：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算) # LIMIT [offset,] ：偏移量(不指定就是0) # row_count ：查询总条数(不指定就是所有行) show binlog events [IN ‘log_name‘] [FROM pos] [LIMIT [offset,] row_count]; # 查看 binlog 内容 show binlog events; # 查看具体一个binlog文件的内容 （in 后面为binlog的文件名） show binlog events in ‘master.000003‘; # 设置binlog文件保存事件，过期删除，单位天 set global expire_log_days=3; # 删除当前的binlog文件 reset master; # 删除slave的中继日志 reset slave; # 删除指定日期前的日志索引中binlog日志文件 purge master logs before ‘2019-03-09 14:00:00‘; # 删除指定日志文件 purge master logs to ‘master.000003‘;
mysql> create database oldguo1 charset utf8mb4; mysql> show binlog events in ‘mysql-bin.000002‘; mysql> grant all on *.* to root@‘10.0.0.%‘ identified by ‘123‘; mysql> show binlog events in ‘mysql-bin.000002‘; mysql> use world mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> delete from city where id<10; Query OK, 8 rows affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec)
如果设置为0，则表示MySQL不控制binlog的刷新，由文件系统去控制它缓存的刷新。
如果设置为不为0的值，则表示每sync_binlog次事务，MySQL调用文件系统的刷新操作刷新binlog到磁盘中。
设为1是最安全的，在系统故障时最多丢失一个事务的更新，但会对性能有所影响。
如果sync_binlog=0或sync_binlog=大于1，当发生电源故障或操作系统崩溃时，可能有一部分已提交但其binlog未被同步到磁盘的事务会被丢失，恢复程序将无法恢复这部分事务。
二进制日志索引文件（文件名后缀为.index）用于记录所有有效的二进制文件
二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML与语句事件
binlog是一个二进制文件集合，每个binlog文件以一个4字节开头的魔数开头，接着是一组Events：
魔数：0xfe62696e对应的是0xfebin；
Event：每个Event包含header和data两个部分；header提供了Event的创建时间，哪个服务器等信息，data部分提供的是针对该Event的具体信息，如具体数据的修改；
第一个Event用于描述binlog文件的格式版本，这个格式就是event写入binlog文件的格式；
其余的Event按照第一个Event的格式版本写入；
最后一个Event用于说明下一个binlog文件；
binlog的索引文件是一个文本文件，其中内容为当前的binlog文件列表
当前以下3中情况时，MySQL会重新生成一个新的日志文件，文件序列号递增:
MySQL服务器停止或重启时
使用flush logs命令；
当binlog文件超过max_binlog_size
[root@db01 ~]# mysql -uroot -p123 -e "show binlog events in ‘mysql-bin.000002‘" |grep DROP
[root@db01 binlog]# mysqlbinlog mysql-bin.000002 >/tmp/a.sql [root@db01 binlog]# vim /tmp/a.sql DDL ： # at 219 #200225 17:52:16 end_log_pos 338 create database oldguo1 charset utf8mb4 # at 338 DML： # at 690 #200225 17:55:53 server id 6 end_log_pos 763 BEGIN # at 763 #200225 17:55:53 server id 6 end_log_pos 821 # at 821 #200225 17:55:53 server id 6 end_log_pos 1107 Ke9UXhMGAAAAOgAAADUDAAAAAGwAAAAAAAEABXdvcmxkAARjaXR5AAUD/v7+Awb+I/4D/hQA/kCR Bw== Ke9UXiAGAAAAHgEAAFMEAAAAAGwAAAAAAAEAAgAF/+ACAAAAB29sZGdpcmwDQUZHCFFhbmRhaGFy vJ8DAOADAAAAB29sZGdpcmwDQUZHBUhlcmF0sNkCAOAEAAAAB29sZGdpcmwDQUZHBUJhbGtoOPMB AOAFAAAAB29sZGdpcmwDTkxEDU5vb3JkLUhvbGxhbmRAKAsA4AYAAAAHb2xkZ2lybANOTEQMWnVp ZC1Ib2xsYW5kqQ0JAOAHAAAAB29sZGdpcmwDTkxEDFp1aWQtSG9sbGFuZES6BgDgCAAAAAdvbGRn aXJsA05MRAdVdHJlY2h0U5MDAOAJAAAAB29sZGdpcmwDTkxEDU5vb3JkLUJyYWJhbnRzFAMAnLn9 +w== # at 1107 #200225 17:55:58 server id 6 end_log_pos 1138 COMMIT/*!*/;
[root@db01 binlog]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000002 >/tmp/b.sql [root@db01 binlog]# vim /tmp/b.sql ### DELETE FROM `world`.`city` ### WHERE ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### @2=‘oldgirl‘ /* STRING(35) meta=65059 nullable=0 is_null=0 */ ### @3=‘AFG‘ /* STRING(3) meta=65027 nullable=0 is_null=0 */ ### @4=‘Qandahar‘ /* STRING(20) meta=65044 nullable=0 is_null=0 */ ### @5=237500 /* INT meta=0 nullable=0 is_null=0 */
mysql> flush logs ;
2. 模拟数据环境
mysql> create database bindb charset utf8mb4; mysql> use bindb mysql> create table t1 (id int); mysql> begin; mysql> insert into t1 values(1),(2),(3); mysql> commit; mysql> begin; mysql> insert into t1 values(11),(22),(33); mysql> commit; mysql> begin; mysql> insert into t1 values(111),(222),(332); mysql> commit; mysql> drop database bindb;
3. 数据恢复
起点：
mysql> show binlog events in ‘mysql-bin.000005‘; | 219 | 332 | create database bindb charset utf8mb4 | 终点： | 1357 | 1452 | drop database bindb
[root@db01 ~]# mysqlbinlog --start-position=219 --stop-position=1357 /data/binlog/mysql-bin.000005 >/tmp/bin.sql
mysql> set sql_log_bin=0; mysql> source /tmp/bin.sql mysql> set sql_log_bin=1;
验证数据：
select * from bindb.t1;
mysqlbinlog -d bindb --start-position=219 --stop-position=1357 /data/binlog/mysql-bin.000005
2. 需要的日志在多个文件中分布
mysqlbinlog --start-datetime= --stop-datetime= mysql-bin.000001 mysql-bin.000002
注：通过时间维度去截取记录
3. 创建了几年，期间一直在用的数据库，插入数据的操作从 bin_log.00000001到 bin_log_.0000121345 之中都有的库，被删了，怎么恢复啊，数据多了咋办，数据行多。
假设： 每周六做全备份23：00，binlog每天备份23:00。 故障点 周三 10点 drop操作。 答：binlog实际上是我们数据恢复时配合备份一起恢复数据的手段。
mysql> flush logs ; mysql> select @@max_binlog_size; mysqladmin -uroot -p123 flush-logs mysqldump -F
注：重启数据库自动滚动
2. 日志的删除
注：不要使用rm命令删除日志
mysql> select @@expire_logs_days; #默认为0，单位是天，代表永不删除。
1. 永久修改删除天数
vim /etc/my.cnf
expire_logs_days = x --二进制日志自动删除的天数。默认值为0,表示“没有自动删除”
注：需要重启启动mysql
2. 不重启修改删除配置
show binary logs; show variables like ‘%log%‘; set global expire_logs_days = 10;
3. 手工删除
Examples: PURGE BINARY LOGS TO ‘mysql-bin.000010‘; --删除mysql-bin.000010日志 PURGE BINARY LOGS BEFORE ‘2019-04-02 22:46:26‘; --清除2019-04-02 22:46:26前的日志
4. 全部清空
mysql> reset master;
注：比较危险,在主库执行此操作，主从必宕。
server_uuid，是在MySQL第一次启动时自动生成并持久化到auto.cnf文件（存放在数据目录下，每台机器的server_uuid都不一样。）
transaction_id，是一个从1开始的自增计数，表示在这个主库上执行的第n个事务，MySQL会保证事务与GTID之间的1:1映射，如：b6af5b5c-666f-11e9-bed3-000c29b85ea6:1
表示在以b6af5b5c-666f-11e9-bed3-000c29b85ea6为唯一标识的MySQL实例上执行的第一个数据库事务。一组连续的事务可以用"_"连接的事务序号范围表示。例如：
简单来说就是
简单来说
slave端的I/O线程将变更的binlog。写入到本地的relay.log中
SQL线程从relay log中获取GTID，然后对比slave端的binlog是否会记录（所有MySQL5.6 slave端必须开启 ，5.7版本中的GTID，即使不开也会自动生成）
如果由记录，说明该GTID的事务已经执行，slave会忽略
如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog
vim /etc/my.cnf gtid-mode=on enforce-gtid-consistency=true
--include-gtids
--exclude-gtids
例子：
# 第一波命令 show master status ; create database gtdb charset utf8mb4; use gtdb; create table t1(id int); begin; insert into t1 values(1),(2),(3); commit; flush logs; show master status ; # 第二波命令 create table t2(id int); begin; insert into t2 values(1),(2),(3); commit; flush logs; show master status ; # 第三波命令 create table t3(id int); begin; insert into t3 values(1),(2),(3); commit; show master status ; drop database gtdb;
#截取日志：
起点： mysql> show binlog events in ‘mysql-bin.000002‘; SET @@SESSION.GTID_NEXT= ‘9b8e7056-4d4c-11ea-a231-000c298e182d:5‘ create database gtdb charset utf8mb4 终点： mysql> show master status; mysql> show binlog events in ‘mysql-bin.000004‘; +++++++++++++++++++++++++++++++++ SET @@SESSION.GTID_NEXT= ‘9b8e7056-4d4c-11ea-a231-000c298e182d:12‘ drop database gtdb +++++++++++++++++++++++++++++++++ gtid : 5-11 截取到12，因为12是删除数据库 文件 ：mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 # 截取： cd /data/binlog/ mysqlbinlog --include-gtids=‘9b8e7056-4d4c-11ea-a231-000c298e182d:5-11‘ mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 >/tmp/gtid.sql
cd /data/binlog/ mysqlbinlog --skip-gtids --include-gtids=‘9b8e7056-4d4c-11ea-a231-000c298e182d:5-11‘ mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 >/tmp/gtid.sql
mysql> select @@slow_query_log; # 是否开启 mysql> select @@slow_query_log_file; # 文件存放位置 mysql> select @@long_query_time; # 慢语句认定时间阈值 mysql> select @@log_queries_not_using_indexes; # 不走索引的语句记录
vim /etc/my.cnf slow_query_log=1 slow_query_log_file=/data/3306/db01-slow.log long_query_time=0.1 log_queries_not_using_indexes=1
注：重启生效
use test; select * from t100w limit 500000,10; select * from t100w limit 600000,10; select * from t100w limit 600000,1; select * from t100w limit 600000,2; select id ,count(num) from t100w group by id limit 10; select id ,count(num) from t100w group by id limit 5; select id ,count(num) from t100w group by id limit 2; select id ,count(num) from t100w group by id limit 2; select id ,count(k1) from t100w group by id limit 1; select id ,count(k2) from t100w group by id limit 1; select k2 ,sum(id) from t100w group by k2 limit 1; select k2 ,sum(id) from t100w group by k2,k1 limit 1; select k2 ,sum(id) from t100w group by k2,k1 limit 1; select k1 ,sum(id) from t100w group by k2,k1 limit 1; select k1,count(id) from t100w group by k1 limit 10;
[root@db01 3306]# mysqldumpslow -s c -t 5 /data/3306/db01-slow.log Reading mysql slow query log from /data/3306/db01-slow.log Count: 8 Time=0.36s (2s) Lock=0.00s (0s) Rows=5.8 (46), root[root]@localhost select * from t100w limit N,N Count: 7 Time=4.55s (31s) Lock=0.00s (0s) Rows=5.1 (36), root[root]@localhost select id ,count(num) from t100w group by id limit N Count: 4 Time=2.73s (10s) Lock=0.00s (0s) Rows=1.0 (4), root[root]@localhost select k2 ,sum(id) from t100w group by k2,k1 limit N Count: 3 Time=1.22s (3s) Lock=0.00s (0s) Rows=7.0 (21), root[root]@localhost select k1,count(id) from t100w group by k1 limit N Count: 2 Time=4.40s (8s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@localhost select id ,count(k2) from t100w group by id limit N
原文：https://www.cnblogs.com/Mercury-linux/p/12405702.html