arch mysql日志位置_MySQL/MariaDB数据库的各种日志管理

最新推荐文章于 2024-06-23 11:48:39 发布

ze ran

最新推荐文章于 2024-06-23 11:48:39 发布

阅读量318

点赞数

文章标签： arch mysql日志位置

本文链接：https://blog.csdn.net/weixin_29762229/article/details/113374451

版权

本文详细介绍了MySQL/MariaDB数据库的事务日志、错误日志和通用日志的管理，包括日志位置配置、日志文件大小调整、日志刷新策略以及日志清理方法，旨在帮助管理员优化数据库性能和保障数据安全。

摘要由CSDN通过智能技术生成

MySQL/MariaDB数据库的各种日志管理

作者：尹正杰

一.事务日志 (transaction log)

1>.Innodb事务日志相关配置

MariaDB [yinzhengjie]> SHOW VARIABLES LIKE '%innodb_log%';+-------------------------------+------------+

| Variable_name | Value |

+-------------------------------+------------+

| innodb_log_arch_dir | |

| innodb_log_arch_expire_sec | 0 |

| innodb_log_archive | OFF |

| innodb_log_block_size | 0 |　　　　　　#数据块大小

| innodb_log_buffer_size | 16777216 |

| innodb_log_checksum_algorithm | DEPRECATED |

| innodb_log_checksums | ON |

| innodb_log_compressed_pages | ON |

| innodb_log_file_size | 50331648 |　　　　　　#每个日志文件总大小，MariaDB 10.2.x默认50M，生产环境可以适当调大。

| innodb_log_files_in_group | 2 |　　　　　　#日志组成员个数

| innodb_log_group_home_dir | ./ |　　　　　　#事务文件路径，是数据目录的相对路径

| innodb_log_optimize_ddl | ON |

| innodb_log_write_ahead_size | 8192 |

+-------------------------------+------------+

13 rows in set (0.00sec)

MariaDB[yinzhengjie]>

2>.事务型存储引擎自行管理和使用(建议和数据文件分开存放)

[root@node105.yinzhengjie.org.cn ~]# install -d /data/logs -o mysql -g mysql　　　　#创建目录并指定属主和属组

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# ll /data/logs/ -d

drwxr-xr-x 2 mysql mysql 6 Nov 4 11:30 /data/logs/[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# ll /data/logs/ -a

total0drwxr-xr-x 2 mysql mysql 6 Nov 4 11:30.

drwxr-xr-x 4 root root 31 Nov 4 11:30..

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn ~]# install -d /data/logs -o mysql -g mysql　　　　#创建目录并指定属主和属组

[root@node105.yinzhengjie.org.cn ~]# vim /mysql/3306/etc/my.cnf

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# cat /mysql/3306/etc/my.cnf

[mysqld]

innodb_log_group_home_dir= /data/logs 　　　#指定事物日志存放路径为"/data/logs"innodb_log_file_size=10M 　　#指定每个日志文件大小为10M

innodb_log_files_in_group= 3#指定日志组成员个数为3个

character-set-server =utf8mb4

default_storage_engine=InnoDB

autocommit= 1skip_name_resolve= 1userstat=ON

port= 3306datadir= /mysql/3306/data

socket= /mysql/3306/socket/mysql.sock

[mysqld_safe]

log-error = /mysql/3306/log/mariadb.log

pid-file = /mysql/3306/pid/mariadb.pid

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# /mysql/3306/mysqld restart

Restarting MySQL...

Stoping MySQL...

Starting MySQL...

[root@node105.yinzhengjie.org.cn~]# ss -ntl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN0 128 *:22 *:*LISTEN0 80 :::3306 :::*LISTEN0 128 :::22 :::*[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# mysql -uroot -pyinzhengjie -S /mysql/3306/socket/mysql.sock

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connectionid is 8Server version:10.2.19-MariaDB MariaDB Server

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

MariaDB [(none)]>MariaDB [(none)]> SHOW VARIABLES LIKE '%innodb_log%';+-------------------------------+------------+

| Variable_name | Value |

+-------------------------------+------------+

| innodb_log_arch_dir | |

| innodb_log_arch_expire_sec | 0 |

| innodb_log_archive | OFF |

| innodb_log_block_size | 0 |

| innodb_log_buffer_size | 16777216 |

| innodb_log_checksum_algorithm | DEPRECATED |

| innodb_log_checksums | ON |

| innodb_log_compressed_pages | ON |

| innodb_log_file_size | 10485760 |

| innodb_log_files_in_group | 3 |

| innodb_log_group_home_dir | /data/logs |

| innodb_log_optimize_ddl | ON |

| innodb_log_write_ahead_size | 8192 |

+-------------------------------+------------+

13 rows in set (0.00sec)

MariaDB [(none)]>

[root@node105.yinzhengjie.org.cn ~]# cat /mysql/3306/etc/my.cnf　　　　　　#修改默认事务日志相关参数

[root@node105.yinzhengjie.org.cn ~]# ll /data/logs/　　　　　　　　#重启MySQL实例后，事务日志被单独存放啦，很显然配置生效啦~total30720

-rw-rw---- 1 mysql mysql 10485760 Nov 4 11:39ib_logfile0-rw-rw---- 1 mysql mysql 10485760 Nov 4 11:39ib_logfile1-rw-rw---- 1 mysql mysql 10485760 Nov 4 11:39ib_logfile2

[root@node105.yinzhengjie.org.cn~]#

3>.刷新日志设置(innodb_flush_log_at_trx_commit)

innodb_flush_log_at_trx_commit

说明：

设置为1，同时sync_binlog=1表示最高级别的容错 innodb_use_global_flush_log_at_trx_commit的值确定是否可以使用SET语句重置此变量

设置为1默认情况下，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。这是完全遵守ACID特性

设置为0提交时没有任何操作; 而是每秒执行一次日志缓冲区写入和刷新。这样可以提供更好的性能，但服务器崩溃可以清除最后一秒的事务

设置为2每次提交后都会写入日志缓冲区，但每秒都会进行一次刷新。性能比0略好一些，但操作系统或停电可能导致最后一秒的交易丢失

设置为3模拟MariaDB5.5组提交(每组提交3个同步)，此项MariaDB 10.0支持

MariaDB [yinzhengjie]> SHOW VARIABLES LIKE '%innodb_flush_log_at_trx_commit%';　　　　#默认设置为1+--------------------------------+-------+

| Variable_name | Value |

+--------------------------------+-------+

| innodb_flush_log_at_trx_commit | 1 |

+--------------------------------+-------+

1 row in set (0.00sec)

MariaDB [yinzhengjie]>

MariaDB [yinzhengjie]> SHOW VARIABLES LIKE '%innodb_flush_log_at_trx_commit%';　　　　#默认设置为1

二.错误日志 (error log)

1>.错误日志记录内容

1.mysqld启动和关闭过程中输出的事件信息;

2.mysqld运行中产生的错误信息;

3.event scheduler运行一个event时产生的日志信息;

4.在主从复制架构中的从服务器上启动从服务器线程时产生的信息;

2>.错误日志相关配置

错误文件路径

log_error=/PATH/TO/LOG_ERROR_FILE

是否记录警告信息至错误日志文件log_warnings 为0，表示不记录告警信息。

log_warnings 为1，表示告警信息写入错误日志。

log_warnings 大于1，表示各类告警信息，例如有关网络故障的信息和重新连接信息写入错误日志(MariaDB 10.2.x版本默认为2)。

[root@node105.yinzhengjie.org.cn ~]# vim /mysql/3306/etc/my.cnf

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# cat /mysql/3306/etc/my.cnf #指定错误日志存放路径，在启动MySQL实例后"log_error"的路径不可被修改，因为它是只读的。

[mysqld]

innodb_log_group_home_dir= /data/logs

innodb_log_file_size=10M

innodb_log_files_in_group= 3log_warnings= 10character-set-server =utf8mb4

default_storage_engine=InnoDB

autocommit= 1skip_name_resolve= 1userstat=ON

port= 3306datadir= /mysql/3306/data

socket= /mysql/3306/socket/mysql.sock

[mysqld_safe]

log-error = /mysql/3306/log/mariadb.log

pid-file = /mysql/3306/pid/mariadb.pid

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# /mysql/3306/mysqld restart

Restarting MySQL...

Stoping MySQL...

Starting MySQL...

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# mysql -uroot -pyinzhengjie -S /mysql/3306/socket/mysql.sock

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connectionid is 8Server version:10.2.19-MariaDB MariaDB Server

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

MariaDB [(none)]>MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'log_error';+---------------+-----------------------------+

| Variable_name | Value |

+---------------+-----------------------------+

| log_error | /mysql/3306/log/mariadb.log |

+---------------+-----------------------------+

1 row in set (0.00sec)

MariaDB [(none)]>MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'log_warnings';+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_warnings | 10 |

+---------------+-------+

1 row in set (0.00sec)

MariaDB [(none)]>

[root@node105.yinzhengjie.org.cn ~]# cat /mysql/3306/etc/my.cnf 　　　　　　　　#指定错误日志存放路径，在启动MySQL实例后"log_error"的路径不可被修改，因为它是只读的。

三.通用日志(general log)

1>.通用日志记录内容

记录对数据库的通用操作，包括错误的SQL语句

存储类型支持三种，即FILE/TABLE/NONE

文件：file，默认值

表：table

用途:

一般不建议开启，除非数据库需要优化，通过该日志分析数据库可能存在的问题，若数据库优化完毕依然建议关闭它，开启该功能存在IO操作，会影响服务器性能。

2>.通用日志相关设置

general_log=ON|OFF

指定通用日志是否开启。

general_log_file=HOSTNAME.log

指定通用日志存放文件格式的名称，默认文件名是:"主机名.log"，存储在MySQL数据库同目录中。

log_output=TABLE|FILE|NONE

指定输出类型,默认为FILE，当然也可以指定为TABLE，表示将日志存放在mysql数据库的默认general_log表中。

MariaDB [yinzhengjie]> SHOW GLOBAL VARIABLES LIKE 'general_log';+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| general_log | OFF |

+---------------+-------+

1 row in set (0.00sec)

MariaDB[yinzhengjie]>MariaDB[yinzhengjie]> SET GLOBAL general_log = ON;　　　　　　#临时开启通用日志功能

Query OK,0 rows affected (0.00sec)

MariaDB[yinzhengjie]>MariaDB[yinzhengjie]> SHOW GLOBAL VARIABLES LIKE 'general_log';+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| general_log | ON |

+---------------+-------+

1 row in set (0.00sec)

MariaDB[yinzhengjie]>MariaDB[yinzhengjie]>

MariaDB [yinzhengjie]> SET GLOBAL general_log = ON;　　　　　　#临时开启通用日志功能

MariaDB [yinzhengjie]> SHOW GLOBAL VARIABLES LIKE 'general_log_file';　　　　　　#查看默认的通用日志名称+------------------+-------------+

| Variable_name | Value |

+------------------+-------------+

| general_log_file | node105.log |

+------------------+-------------+

row in set (0.00sec)

MariaDB[yinzhengjie]>MariaDB[yinzhengjie]>QUIT

Bye[root@node105.yinzhengjie.org.cn ~]#[root@node105.yinzhengjie.org.cn ~]# cat /mysql/3306/data/node105.log

/usr/local/mysql/bin/mysqld, Version: 10.2.19-MariaDB (MariaDB Server). started with:

Tcp port:3306 Unix socket: /mysql/3306/socket/mysql.sock

Time Id Command Argument191104 16:40:38 9 Query SHOW GLOBAL VARIABLES LIKE 'general_log'

191104 16:40:46 10Query SHOW TABLES191104 16:41:39 9 Query SHOW GLOBAL VARIABLES LIKE 'general_log_file'

191104 16:49:52 9 Query SHOW GLOBAL VARIABLES LIKE 'log_output'

191104 16:49:55 9 Query SHOW GLOBAL VARIABLES LIKE 'log_output'

191104 16:50:11 9 Query SET GLOBAL log_output = table

191104 16:50:20 9 Query SHOW GLOBAL VARIABLES LIKE 'log_output'

191104 16:50:53 9 Query SET GLOBAL log_output = 'table'

191104 16:56:22 11Quit191104 16:56:59 10 Query SHOW VARIABLES LIKE '%innodb_log%'

191104 16:57:10 10 Query SHOW GLOBAL VARIABLES LIKE 'log_output'

191104 16:58:17 10 Query SHOW GLOBAL VARIABLES LIKE 'general_log_file'

191104 16:59:26 10Quit[root@node105.yinzhengjie.org.cn ~]#[root@node105.yinzhengjie.org.cn ~]#

MariaDB [yinzhengjie]> SHOW GLOBAL VARIABLES LIKE 'general_log_file';　　　　　　#查看默认的通用日志名称

MariaDB [yinzhengjie]> SHOW GLOBAL VARIABLES LIKE 'log_output';　　　　#查看默认的日志输出类型+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_output | FILE |

+---------------+-------+

1 row in set (0.00sec)

MariaDB[yinzhengjie]>MariaDB[yinzhengjie]> SET GLOBAL log_output = 'table';

Query OK,0 rows affected (0.00sec)

MariaDB[yinzhengjie]>MariaDB[yinzhengjie]> SHOW GLOBAL VARIABLES LIKE 'log_output';+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_output | TABLE |

+---------------+-------+

1 row in set (0.00sec)

MariaDB[yinzhengjie]>MariaDB[yinzhengjie]> SELECT * FROMmysql.general_log\G　　　　#查看表中的通用日志记录信息*************************** 1. row ***************************event_time:2019-11-04 16:50:55.178466user_host: root[root] @ localhost []thread_id:9server_id:1command_type: Query

argument: SHOW GLOBAL VARIABLESLIKE 'log_output'

*************************** 2. row ***************************event_time:2019-11-04 16:51:24.556260user_host: root[root] @ localhost []thread_id:9server_id:1command_type: Query

argument:SELECT * FROMmysql.general_log2 rows in set (0.00sec)

MariaDB[yinzhengjie]>

MariaDB [yinzhengjie]> SHOW GLOBAL VARIABLES LIKE 'log_output';　　　　　　#查看默认的日志输出类型

MariaDB [yinzhengjie]> SELECT * FROMmysql.general_log;+----------------------------+---------------------------+-----------+-----------+--------------+-----------------------------------------+

+----------------------------+---------------------------+-----------+-----------+--------------+-----------------------------------------+

| 2019-11-04 16:50:55.178466 | root[root] @ localhost [] | 9 | 1 | Query | SHOW GLOBAL VARIABLES LIKE 'log_output' |

| 2019-11-04 16:51:24.556260 | root[root] @ localhost [] | 9 | 1 | Query | SELECT * FROM mysql.general_log |

| 2019-11-04 16:54:30.403657 | root[root] @ localhost [] | 9 | 1 | Quit | |

| 2019-11-04 16:55:56.817442 | [root] @ localhost [] | 11 | 1 | Connect | root@localhost as anonymous on |

| 2019-11-04 16:55:56.820207 | root[root] @ localhost [] | 11 | 1 | Query | select @@version_comment limit 1 |

| 2019-11-04 16:56:00.271469 | root[root] @ localhost [] | 11 | 1 | Query | SELECT DATABASE() |

| 2019-11-04 16:56:00.271777 | root[root] @ localhost [] | 11 | 1 | Init DB | yinzhengjie |

| 2019-11-04 16:56:18.084201 | root[root] @ localhost [] | 11 | 1 | Query | SET GLOBAL log_output = 'file' |

+----------------------------+---------------------------+-----------+-----------+--------------+-----------------------------------------+

8 rows in set (0.00sec)

MariaDB[yinzhengjie]>MariaDB[yinzhengjie]> SELECT argument,COUNT(*) FROM mysql.general_log GROUP BYargument;　　　　#找出查询此处最多的语句可以来创建相关索引。+-----------------------------------------+----------+

| argument | COUNT(*) |

+-----------------------------------------+----------+

| | 1 |

| root@localhost as anonymous on | 1 |

| SELECT * FROM mysql.general_log | 1 |

| select @@version_comment limit 1 | 1 |

| SELECT DATABASE() | 1 |

| SET GLOBAL log_output = 'file' | 1 |

| SHOW GLOBAL VARIABLES LIKE 'log_output' | 1 |

| yinzhengjie | 1 |

+-----------------------------------------+----------+

8 rows in set (0.00sec)

MariaDB[yinzhengjie]>

一般不建议开启，除非数据库需要优化，通过该日志分析数据库可能存在的问题，若数据库优化完毕依然建议关闭它，会影响服务器性能。

四.慢查询日志 (slow query log)

1>.慢查询日志记录内容

记录执行查询时长超出指定时长的操作

2>.慢查询相关设置

slow_query_log=ON|OFF开启或关闭慢查询

long_query_time=N

慢查询的阀值，单位秒

slow_query_log_file=HOSTNAME-slow.log慢查询日志文件

log_slow_filter=admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

上述查询类型且查询时长超过long_query_time，则记录日志

log_queries_not_using_indexes=ON不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语句是否记录日志，默认OFF，即不记录

log_slow_rate_limit= 1多少次查询才记录，mariadb特有

log_slow_verbosity=Query_plan,explain

记录内容

log_slow_queries= OFF同slow_query_log 新版已废弃

3>.慢查询相关参数使用案例

[root@node105.yinzhengjie.org.cn ~]# vim /mysql/3306/etc/my.cnf[root@node105.yinzhengjie.org.cn ~]#[root@node105.yinzhengjie.org.cn ~]# cat /mysql/3306/etc/my.cnf[mysqld]innodb_log_group_home_dir= /data/logs

innodb_log_file_size=10M

innodb_log_files_in_group= 3log_warnings= 10general_log= ONslow_query_log= ON#开启慢查询日志

long_query_time= 5#指定慢查询超时时间为5秒就记录到文件character-set-server =utf8mb4

default_storage_engine=InnoDB

autocommit= 1skip_name_resolve= 1userstat= ONport= 3306datadir= /mysql/3306/data

socket= /mysql/3306/socket/mysql.sock[mysqld_safe]

log-error = /mysql/3306/log/mariadb.logpid-file = /mysql/3306/pid/mariadb.pid[root@node105.yinzhengjie.org.cn ~]#[root@node105.yinzhengjie.org.cn ~]# /mysql/3306/mysqld restart

Restarting MySQL...

Stoping MySQL...

Starting MySQL...[root@node105.yinzhengjie.org.cn ~]#[root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql/3306/socket/mysql.sock

Welcometo the MariaDB monitor. Commands end with ; or\g.

Your MariaDB connection idis 8Server version:10.2.19-MariaDB-logMariaDB Server

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

MariaDB[(none)]>MariaDB[(none)]> SELECT SLEEP(1) FROMyinzhengjie.teachers;　　　　#有多少条记录这里就会休眠几秒，这里4条记录仅休眠4秒，而我们定义的慢查询日志超时时间为5秒，因此该条记录不会被记录。+----------+

| SLEEP(1) |

+----------+

| 0 |

+----------+

4 rows in set (4.01sec)

MariaDB[(none)]>MariaDB[(none)]> SELECT SLEEP(1) FROMyinzhengjie.students;　　　　#同理，在yinzhengjie.students表有25行记录，因此休眠5秒，符合我们定义慢日志查询超时时间为5秒，因此该条记录会被记录到慢查询日志中。+----------+

| SLEEP(1) |

+----------+

| 0 |

+----------+

25 rows in set (25.03sec)

MariaDB[(none)]>MariaDB[(none)]>QUIT

Bye[root@node105.yinzhengjie.org.cn ~]#[root@node105.yinzhengjie.org.cn ~]# cat /mysql/3306/data/node105-slow.log#不难发现和我们上面分析的一样，只有一条慢查询日志，当然慢查询日志不仅仅包含SELECT语句哟，包括的是DML语句，存储引擎等的执行超过指定秒数也会被记录呢!/usr/local/mysql/bin/mysqld, Version: 10.2.19-MariaDB-log (MariaDB Server). started with:

Tcp port:3306 Unix socket: /mysql/3306/socket/mysql.sock

Time Id Command Argument

# Time:191104 17:24:14#User@Host: root[root] @ localhost []# Thread_id:8 Schema: QC_hit: No

# Query_time:25.025535 Lock_time: 0.000404 Rows_sent: 25 Rows_examined: 25# Rows_affected:0

SET timestamp=1572859454;SELECT SLEEP(1) FROMyinzhengjie.students;[root@node105.yinzhengjie.org.cn ~]#[root@node105.yinzhengjie.org.cn ~]#

详情请戳我

4>.通过profiling工具分析慢查询日志

MariaDB [yinzhengjie]> SELECT @@profiling;+-------------+

| @@profiling |

+-------------+

| 0 |

+-------------+

1 row in set (0.00sec)

MariaDB[yinzhengjie]>MariaDB[yinzhengjie]> SET profiling = ON;　　　　　　#开启profiling功能

Query OK,0 rows affected (0.00sec)

MariaDB[yinzhengjie]>MariaDB[yinzhengjie]> SELECT @@profiling;+-------------+

| @@profiling |

+-------------+

| 1 |

+-------------+

1 row in set (0.00sec)

MariaDB[yinzhengjie]>

MariaDB [yinzhengjie]> SET profiling = ON;　　　　　　#开启profiling功能

MariaDB [yinzhengjie]>SHOW profiles;　　　　　　　　　　#需要开启profiling功能后，执行的SQL语句会被记录+----------+-------------+-------------------------------------------+

| Query_ID | Duration | Query |

+----------+-------------+-------------------------------------------+

| 1 | 0.00013446 | SELECT @@profiling |

| 2 | 4.00646956 | SELECT SLEEP(1) FROM yinzhengjie.teachers |

| 3 | 25.02319266 | SELECT SLEEP(1) FROM yinzhengjie.students |

+----------+-------------+-------------------------------------------+

3 rows in set (0.00sec)

MariaDB[yinzhengjie]>MariaDB[yinzhengjie]> SHOW PROFILE FOR QUERY 2;　　　　#我们查看上面第2条语句，对其分析查询慢日志的原因+----------------------+----------+

| Status | Duration |

+----------------------+----------+

| starting | 0.000085 |

| checking permissions | 0.000007 |

| Opening tables | 0.000017 |

| After opening tables | 0.000005 |

| System lock | 0.000004 |

| Table lock | 0.000006 |

| init | 0.000011 |

| optimizing | 0.000007 |

| statistics | 0.000020 |

| preparing | 0.000018 |

| executing | 0.000004 |

| Sending data | 0.000022 |

| User sleep | 1.002767 |　　　　　　#不难发现，耗时最长的应该就是在sleep过程啦！

| User sleep | 1.001184 |

| User sleep | 1.000412 |

| User sleep | 1.001772 |

| end | 0.000026 |

| query end | 0.000022 |

| closing tables | 0.000008 |

| Unlocking tables | 0.000023 |

| freeing items | 0.000011 |

| updating status | 0.000031 |

| cleaning up | 0.000008 |

+----------------------+----------+

23 rows in set (0.00sec)

MariaDB[yinzhengjie]>

五.二进制日志 (binary log,Oracle称为归档日志)

1>.二进制日志记录内容

记录内容:

记录导致数据改变或潜在导致数据改变的SQL语句(即只记录增删改操作)

记录已提交的日志

不依赖于存储引擎类型

功能：

通过“重放”日志文件中的事件来生成数据副本

温馨提示：

建议生产环境中二进制日志和数据文件分开存放，当数据文件存放目录损坏，可通过二进制文件恢复。

2>.二进制日志记录格式

二进制日志记录三种格式:

基于“语句”记录：

statement，只记录语句，默认模式。该模式存在弊端，比如执行"UPDATE students SET birth = now();"无法保存具体的时间戳，若按照该语句进行还原数据准确性肯定出现问题。

基于“行”记录：

row，只记录数据，即直接将数据存储下来，但日志量较大。适合数据相对来说重要的场景。推荐使用这种模式，数据恢复时准确的最高，但带来的代价就是得牺牲更多的磁盘空间。建议健康磁盘剩余空间进行及时扩充。

混合模式：

mixed,相对来说比较折中的方式,让系统自行判定该基于哪种方式进行。

格式配置:

SHOW VARIABLESLIKE 'binlog_format';

MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'binlog_format';　　　　`　　　　#查看二进制默认的记录格式+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| binlog_format | MIXED |

+---------------+-------+

1 row in set (0.00sec)

MariaDB[yinzhengjie]>

MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'binlog_format';　　　　`　　　　#查看MariaDB10.2.x二进制默认的记录格式

3>.二进制日志文件的构成

日志文件：

mysql|mariadb-bin.文件名后缀，二进制格式，如： mariadb-bin.000001索引文件：

mysql|mariadb-bin.index，文本格式，记录在当前有效的二进制文件名称

4>.二进制日志相关的服务器变量

sql_log_bin=ON|OFF：

是否记录二进制日志，默认ON,默认启用二进制文件功能，该变量是会话(session)级别无需重启服务就可生效，可很灵活的控制二进制日志的禁用和启用。

在批量导入大量数据时，我们此时可用选择不记录二进制文件从而节省一定的磁盘空间使用，这个时候我们就可用将该值设置为OFF，可用临时禁用二进制日志功能。

log_bin=/PATH/BIN_LOG_FILE：

指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项(sql_log_bin和log_bin)都开启才可

binlog_format=STATEMENT|ROW|MIXED：

二进制日志记录的格式，MariaDB 5.5.x默认STATEMENT,而MariaDB 10.2.x默认为MIXED。

max_binlog_size=1073741824：

单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G

说明：文件达到上限时的大小未必为指定的精确值

sync_binlog=1|0：

设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘

expire_logs_days=N：

二进制日志可以自动删除的天数。默认为0，即不自动删除

[root@node105.yinzhengjie.org.cn ~]# install -d /data/logbin -o mysql -g mysql　　　　#创建二进制日志存放目录

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# ll -d /data/logbin/drwxr-xr-x 2 mysql mysql 6 Nov 4 18:45 /data/logbin/[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn ~]# install -d /data/logbin -o mysql -g mysql　　　　#创建二进制日志存放目录

[root@node105.yinzhengjie.org.cn ~]# vim /mysql/3306/etc/my.cnf

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# cat /mysql/3306/etc/my.cnf　　　　#配置二进制文件的默认存放路径

[mysqld]

log_bin= /data/logbin/mysql-bin　　　　　　#指定二进制日志存放路径及文件名称前缀

character-set-server =utf8mb4

default_storage_engine=InnoDB

port= 3306datadir= /mysql/3306/data

socket= /mysql/3306/socket/mysql.sock

[mysqld_safe]

log-error = /mysql/3306/log/mariadb.log

pid-file = /mysql/3306/pid/mariadb.pid

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# ll /data/logbin/total0[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# /mysql/3306/mysqld start

Starting MySQL...

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# ll /data/logbin/#启动服务后二进制日志存放到指定路径中

total8

-rw-rw---- 1 mysql mysql 328 Nov 4 18:52 mysql-bin.000001

-rw-rw---- 1 mysql mysql 30 Nov 4 18:52 mysql-bin.index

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# cat /data/logbin/mysql-bin.index 　　#查看现在所有可用的二进制文件名称/data/logbin/mysql-bin.000001[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn ~]# cat /mysql/3306/etc/my.cnf　　　　#配置二进制文件的默认存放路径

[root@node105.yinzhengjie.org.cn ~]# vim /mysql/3306/etc/my.cnf

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# cat /mysql/3306/etc/my.cnf　　　　　　#配置二进制文件的默认格式

[mysqld]

log_bin= /data/logbin/mysql-bin

binlog_format=ROW　　　　　　#修改二进制的格式为基于行的，这意味着需要更多的占用磁盘使用空间。

character-set-server =utf8mb4

default_storage_engine=InnoDB

port= 3306datadir= /mysql/3306/data

socket= /mysql/3306/socket/mysql.sock

[mysqld_safe]

log-error = /mysql/3306/log/mariadb.log

pid-file = /mysql/3306/pid/mariadb.pid

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# /mysql/3306/mysqld restart

Restarting MySQL...

Stoping MySQL...

Starting MySQL...

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# ll /data/logbin/#每次重启MySQL实例都会滚动二进制日志文件

total12

-rw-rw---- 1 mysql mysql 351 Nov 4 19:07 mysql-bin.000001

-rw-rw---- 1 mysql mysql 328 Nov 4 19:07 mysql-bin.000002

-rw-rw---- 1 mysql mysql 60 Nov 4 19:07 mysql-bin.index

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# cat /data/logbin/mysql-bin.index 　　　　#查看所有可用的二进制文件/data/logbin/mysql-bin.000001

/data/logbin/mysql-bin.000002[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn ~]# cat /mysql/3306/etc/my.cnf　　　　　　#配置二进制文件的默认格式

5>.二进制日志相关配置

查看mariadb自行管理使用中的二进制日志文件列表，及大小:

SHOW {BINARY|MASTER} LOGS

查看使用中的二进制日志文件:

SHOW MASTER STATUS

切换日志文件:

FLUSH LOGS

查看二进制文件中的指定内容:

SHOW BINLOG EVENTS [IN'log_name'] [FROM pos] [LIMIT [offset,] row_count]

如：SHOW BINLOG EVENTS IN 'mysql-bin.000003' FROM 285 LIMIT 3,4

以上命令查看的信息并不详细推荐使用mysqlbinlog工具进行查看。

MariaDB [yinzhengjie]>SHOW BINARY LOGS;+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 351 |

| mysql-bin.000002 | 328 |

+------------------+-----------+

2 rows in set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>SHOW MASTER LOGS;+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 351 |

| mysql-bin.000002 | 328 |

+------------------+-----------+

2 rows in set (0.00sec)

MariaDB [yinzhengjie]>

MariaDB [yinzhengjie]> SHOW BINARY LOGS;

MariaDB [yinzhengjie]>SHOW MASTER STATUS;+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000002 | 328 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00sec)

MariaDB [yinzhengjie]>

MariaDB [yinzhengjie]> SHOW MASTER STATUS;

MariaDB [yinzhengjie]>SHOW MASTER LOGS;+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 351 |

| mysql-bin.000002 | 328 |

+------------------+-----------+rowsin set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>FLUSH LOGS;

Query OK,0 rows affected (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>SHOW MASTER LOGS;+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 351 |

| mysql-bin.000002 | 375 |

| mysql-bin.000003 | 371 |

+------------------+-----------+rowsin set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/#使用SYSTEM调用系统命令查看操作系统的确也有对应的文件生成啦。

total16

-rw-rw---- 1 mysql mysql 351 Nov 4 19:07 mysql-bin.000001

-rw-rw---- 1 mysql mysql 375 Nov 4 19:16 mysql-bin.000002

-rw-rw---- 1 mysql mysql 371 Nov 4 19:16 mysql-bin.000003

-rw-rw---- 1 mysql mysql 90 Nov 4 19:16 mysql-bin.index

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>

MariaDB [yinzhengjie]> FLUSH LOGS;

MariaDB [yinzhengjie]>SHOW MASTER LOGS;+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 351 |

| mysql-bin.000002 | 375 |

| mysql-bin.000003 | 371 |

+------------------+-----------+

3 rows in set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/total16

-rw-rw---- 1 mysql mysql 351 Nov 4 19:07 mysql-bin.000001

-rw-rw---- 1 mysql mysql 375 Nov 4 19:16 mysql-bin.000002

-rw-rw---- 1 mysql mysql 371 Nov 4 19:16 mysql-bin.000003

-rw-rw---- 1 mysql mysql 90 Nov 4 19:16 mysql-bin.index

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> INSERT INTO teachers (name,age,gender) VALUES ('Jason Yin',26,'M'),('yinzhengjie',18,'M');

Query OK,2 rows affected (0.00sec)

Records:2 Duplicates: 0 Warnings: 0MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> SELECT *FROM teachers;+-----+---------------+-----+--------+

| TID | Name | Age | Gender |

+-----+---------------+-----+--------+

| 1 | Song Jiang | 45 | M |

| 2 | Zhang Sanfeng | 94 | M |

| 3 | Miejue Shitai | 77 | F |

| 4 | Lin Chaoying | 93 | F |

| 5 | Jason Yin | 26 | M |

| 6 | yinzhengjie | 18 | M |

+-----+---------------+-----+--------+

6 rows in set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>SHOW MASTER LOGS;+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 351 |

| mysql-bin.000002 | 375 |

| mysql-bin.000003 | 688 |

+------------------+-----------+

3 rows in set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>SHOW MASTER STATUS;+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000003 | 688 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> SHOW BINLOG EVENTS IN 'mysql-bin.000003';　　　　#查看二进制文件的内容+------------------+-----+-------------------+-----------+-------------+-------------------------------------------------------------------------

------------------+| mysql-bin.000003 | 4 | Format_desc | 1 | 256 | Server ver: 10.2.19-MariaDB-log, Binlog ver: 4

|| mysql-bin.000003 | 256 | Gtid_list | 1 | 285 |[]|| mysql-bin.000003 | 285 | Binlog_checkpoint | 1 | 328 | mysql-bin.000002

|| mysql-bin.000003 | 328 | Binlog_checkpoint | 1 | 371 | mysql-bin.000003

|| mysql-bin.000003 | 371 | Gtid | 1 | 413 | BEGIN GTID 0-1-1

|| mysql-bin.000003 | 413 | Annotate_rows | 1 | 525 | INSERT INTO teachers (name,age,gender) VALUES ('Jason Yin',26,'M'),('yin

zhengjie',18,'M') |

| mysql-bin.000003 | 525 | Table_map | 1 | 590 | table_id: 22(yinzhengjie.teachers)|| mysql-bin.000003 | 590 | Write_rows_v1 | 1 | 657 | table_id: 22flags: STMT_END_F|| mysql-bin.000003 | 657 | Xid | 1 | 688 | COMMIT /*xid=16*/

|+------------------+-----+-------------------+-----------+-------------+-------------------------------------------------------------------------

------------------+

9 rows in set (0.00sec)

MariaDB [yinzhengjie]>

MariaDB [yinzhengjie]> SHOW BINLOG EVENTS IN 'mysql-bin.000003';　　　　#查看二进制文件的内容，查看的内容不详细

MariaDB [yinzhengjie]> SHOW BINLOG EVENTS IN 'mysql-bin.000003';+------------------+-----+-------------------+-----------+-------------+-------------------------------------------------------------------------

------------------+| mysql-bin.000003 | 4 | Format_desc | 1 | 256 | Server ver: 10.2.19-MariaDB-log, Binlog ver: 4

|| mysql-bin.000003 | 256 | Gtid_list | 1 | 285 |[]|| mysql-bin.000003 | 285 | Binlog_checkpoint | 1 | 328 | mysql-bin.000002

|| mysql-bin.000003 | 328 | Binlog_checkpoint | 1 | 371 | mysql-bin.000003

|| mysql-bin.000003 | 371 | Gtid | 1 | 413 | BEGIN GTID 0-1-1

|| mysql-bin.000003 | 413 | Annotate_rows | 1 | 525 | INSERT INTO teachers (name,age,gender) VALUES ('Jason Yin',26,'M'),('yin

zhengjie',18,'M') |

|+------------------+-----+-------------------+-----------+-------------+-------------------------------------------------------------------------

------------------+

9 rows in set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> SHOW BINLOG EVENTS IN 'mysql-bin.000003' FROM 285 LIMIT 2;+------------------+-----+-------------------+-----------+-------------+------------------+

+------------------+-----+-------------------+-----------+-------------+------------------+

| mysql-bin.000003 | 285 | Binlog_checkpoint | 1 | 328 | mysql-bin.000002 |

| mysql-bin.000003 | 328 | Binlog_checkpoint | 1 | 371 | mysql-bin.000003 |

+------------------+-----+-------------------+-----------+-------------+------------------+

2 rows in set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> SHOW BINLOG EVENTS IN 'mysql-bin.000003' FROM 285 LIMIT 3,4;　　　　#从指定位置查看相应的信息+------------------+-----+---------------+-----------+-------------+-----------------------------------------------------------------------------

--------------+| mysql-bin.000003 | 413 | Annotate_rows | 1 | 525 | INSERT INTO teachers (name,age,gender) VALUES ('Jason Yin',26,'M'),('yinzhen

gjie',18,'M') |

|+------------------+-----+---------------+-----------+-------------+-----------------------------------------------------------------------------

--------------+

4 rows in set (0.00sec)

MariaDB [yinzhengjie]>

MariaDB [yinzhengjie]> SHOW BINLOG EVENTS IN 'mysql-bin.000003' FROM 285 LIMIT 3,4;　　#从指定位置查看相应的信息

6>.二进制日志的客户端命令工具(mysqlbinlog)

命令格式：

mysqlbinlog [OPTIONS] log_file…--start-position=# 指定开始位置--stop-position=#--start-datetime=

--stop-datetime=时间格式：YYYY-MM-DD hh:mm:ss--base64-output[=name]-v -vvv

[root@node105.yinzhengjie.org.cn ~]# ls -l /data/logbin/total16

-rw-rw---- 1 mysql mysql 351 Nov 4 19:07 mysql-bin.000001

-rw-rw---- 1 mysql mysql 375 Nov 4 19:16 mysql-bin.000002

-rw-rw---- 1 mysql mysql 688 Nov 4 19:25 mysql-bin.000003

-rw-rw---- 1 mysql mysql 90 Nov 4 19:16 mysql-bin.index

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# mysqlbinlog /data/logbin/mysql-bin.000003#可用查看二进制文件，但有基于Base64编码的加密信息。/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER/*!*/;

# at4#191104 19:16:46 server id 1 end_log_pos 256 CRC32 0x4e4a71e5 Start: binlog v 4, server v 10.2.19-MariaDB-log created 191104 19:16:46# Warning: this binlog is eitherinuse or was not closed properly.

BINLOG'ngjAXQ8BAAAA/AAAAAABAAABAAQAMTAuMi4xOS1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAEEwQADQgICAoKCgHlcUpO'/*!*/;

# at 256#191104 19:16:46 server id 1 end_log_pos 285 CRC32 0x11540620Gtid list []

# at285#191104 19:16:46 server id 1 end_log_pos 328 CRC32 0x3b612022 Binlog checkpoint mysql-bin.000002# at328#191104 19:16:46 server id 1 end_log_pos 371 CRC32 0xce9f3b39 Binlog checkpoint mysql-bin.000003# at371#191104 19:25:44 server id 1 end_log_pos 413 CRC32 0xbc3f3fe1 GTID 0-1-1trans/*!100101 SET @@session.skip_parallel_replication=0*//*!*/;/*!100001 SET @@session.gtid_domain_id=0*//*!*/;/*!100001 SET @@session.server_id=1*//*!*/;/*!100001 SET @@session.gtid_seq_no=1*//*!*/;

BEGIN/*!*/;

# at413# at525#191104 19:25:44 server id 1 end_log_pos 525 CRC32 0xe7b70b5bAnnotate_rows:

#Q> INSERT INTO teachers (name,age,gender) VALUES ('Jason Yin',26,'M'),('yinzhengjie',18,'M')

#191104 19:25:44 server id 1 end_log_pos 590 CRC32 0xd48ca5db Table_map: `yinzhengjie`.`teachers` mapped to number 22# at590#191104 19:25:44 server id 1 end_log_pos 657 CRC32 0x1a95ea95 Write_rows: table id 22flags: STMT_END_F

BINLOG'uArAXRMBAAAAQQAAAE4CAAAAABYAAAAAAAEAC3lpbnpoZW5namllAAh0ZWFjaGVycwAEAg8B/gQs

AfcBCNuljNQ=uArAXRcBAAAAQwAAAJECAAAAABYAAAAAAAEABP/wBQAJAEphc29uIFlpbhoC8AYACwB5aW56aGVu

Z2ppZRICleqVGg==

'/*!*/;

# at 657#191104 19:25:44 server id 1 end_log_pos 688 CRC32 0x6b6a92b0 Xid = 16COMMIT/*!*/;

DELIMITER ;

# End of logfileROLLBACK/*added by mysqlbinlog*/;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn ~]# mysqlbinlog /data/logbin/mysql-bin.000003　　　#可用查看二进制文件

[root@node105.yinzhengjie.org.cn ~]# ls -l /data/logbin/total16

-rw-rw---- 1 mysql mysql 351 Nov 4 19:07 mysql-bin.000001

-rw-rw---- 1 mysql mysql 375 Nov 4 19:16 mysql-bin.000002

-rw-rw---- 1 mysql mysql 688 Nov 4 19:25 mysql-bin.000003

-rw-rw---- 1 mysql mysql 90 Nov 4 19:16 mysql-bin.index

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# mysqlbinlog /data/logbin/mysql-bin.000003 --verbose　　#查看详细信息/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER/*!*/;

BINLOG'ngjAXQ8BAAAA/AAAAAABAAABAAQAMTAuMi4xOS1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAEEwQADQgICAoKCgHlcUpO'/*!*/;

# at 256#191104 19:16:46 server id 1 end_log_pos 285 CRC32 0x11540620Gtid list []

BEGIN/*!*/;

# at413# at525#191104 19:25:44 server id 1 end_log_pos 525 CRC32 0xe7b70b5bAnnotate_rows:

#Q> INSERT INTO teachers (name,age,gender) VALUES ('Jason Yin',26,'M'),('yinzhengjie',18,'M')

BINLOG'uArAXRMBAAAAQQAAAE4CAAAAABYAAAAAAAEAC3lpbnpoZW5namllAAh0ZWFjaGVycwAEAg8B/gQs

AfcBCNuljNQ=uArAXRcBAAAAQwAAAJECAAAAABYAAAAAAAEABP/wBQAJAEphc29uIFlpbhoC8AYACwB5aW56aGVu

Z2ppZRICleqVGg==

'/*!*/;

### INSERT INTO `yinzhengjie`.`teachers`

### SET

### @1=5### @2='Jason Yin'### @3=26### @4=2### INSERT INTO `yinzhengjie`.`teachers`

### SET

### @1=6### @2='yinzhengjie'### @3=18### @4=2# at657#191104 19:25:44 server id 1 end_log_pos 688 CRC32 0x6b6a92b0 Xid = 16COMMIT/*!*/;

DELIMITER ;

# End of logfileROLLBACK/*added by mysqlbinlog*/;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn ~]# mysqlbinlog /data/logbin/mysql-bin.000003 --verbose　　#查看详细信息

MariaDB [yinzhengjie]>SELECT @@binlog_format;+-----------------+

| @@binlog_format |

+-----------------+

| ROW |

+-----------------+

1 row in set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> select *from teachers;+-----+---------------+-----+--------+

| TID | Name | Age | Gender |

+-----+---------------+-----+--------+

| 1 | Song Jiang | 45 | M |

| 2 | Zhang Sanfeng | 94 | M |

| 3 | Miejue Shitai | 77 | F |

| 4 | Lin Chaoying | 93 | F |

| 5 | Jason Yin | 26 | M |

| 6 | yinzhengjie | 18 | M |

+-----+---------------+-----+--------+

6 rows in set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> UPDATE teachers SET gender='M';

Query OK,2 rows affected (0.00sec)

Rows matched:6 Changed: 2 Warnings: 0MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> select *from teachers;+-----+---------------+-----+--------+

| TID | Name | Age | Gender |

+-----+---------------+-----+--------+

| 1 | Song Jiang | 45 | M |

| 2 | Zhang Sanfeng | 94 | M |

| 3 | Miejue Shitai | 77 | M |

| 4 | Lin Chaoying | 93 | M |

| 5 | Jason Yin | 26 | M |

| 6 | yinzhengjie | 18 | M |

+-----+---------------+-----+--------+

6 rows in set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>SHOW MASTER STATUS;+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000003 | 688 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>QUIT

Bye

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# mysqlbinlog /data/logbin/mysql-bin.000003 --verbose/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER/*!*/;

BINLOG'ngjAXQ8BAAAA/AAAAAABAAABAAQAMTAuMi4xOS1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAEEwQADQgICAoKCgHlcUpO'/*!*/;

# at 256#191104 19:16:46 server id 1 end_log_pos 285 CRC32 0x11540620Gtid list []

BEGIN/*!*/;

# at413# at525#191104 19:25:44 server id 1 end_log_pos 525 CRC32 0xe7b70b5bAnnotate_rows:

#Q> INSERT INTO teachers (name,age,gender) VALUES ('Jason Yin',26,'M'),('yinzhengjie',18,'M')

BINLOG'uArAXRMBAAAAQQAAAE4CAAAAABYAAAAAAAEAC3lpbnpoZW5namllAAh0ZWFjaGVycwAEAg8B/gQs

AfcBCNuljNQ=uArAXRcBAAAAQwAAAJECAAAAABYAAAAAAAEABP/wBQAJAEphc29uIFlpbhoC8AYACwB5aW56aGVu

Z2ppZRICleqVGg==

'/*!*/;

### INSERT INTO `yinzhengjie`.`teachers`

### SET

### @1=5### @2='Jason Yin'### @3=26### @4=2### INSERT INTO `yinzhengjie`.`teachers`

### SET

### @1=6### @2='yinzhengjie'### @3=18### @4=2# at657#191104 19:25:44 server id 1 end_log_pos 688 CRC32 0x6b6a92b0 Xid = 16COMMIT/*!*/;

# at688#191104 19:47:27 server id 1 end_log_pos 730 CRC32 0x740f1f3c GTID 0-1-2trans/*!100001 SET @@session.gtid_seq_no=2*//*!*/;

BEGIN/*!*/;

# at730# at783#191104 19:47:27 server id 1 end_log_pos 783 CRC32 0x782ca82dAnnotate_rows:

#Q> UPDATE teachers SET gender='M'#191104 19:47:27 server id 1 end_log_pos 848 CRC32 0x37af4016 Table_map: `yinzhengjie`.`teachers` mapped to number 22# at848#191104 19:47:27 server id 1 end_log_pos 960 CRC32 0xc306f092 Update_rows: table id 22flags: STMT_END_F

BINLOG'zw/AXRMBAAAAQQAAAFADAAAAABYAAAAAAAEAC3lpbnpoZW5namllAAh0ZWFjaGVycwAEAg8B/gQs

AfcBCBZArzc=zw/AXRgBAAAAcAAAAMADAAAAABYAAAAAAAEABP//8AMADQBNaWVqdWUgU2hpdGFpTQHwAwANAE1p

ZWp1ZSBTaGl0YWlNAvAEAAwATGluIENoYW95aW5nXQHwBAAMAExpbiBDaGFveWluZ10CkvAGww==

'/*!*/;

### UPDATE `yinzhengjie`.`teachers`

### WHERE

### @1=3### @2='Miejue Shitai'### @3=77### @4=1### SET

### @1=3### @2='Miejue Shitai'### @3=77### @4=2### UPDATE `yinzhengjie`.`teachers`

### WHERE

### @1=4### @2='Lin Chaoying'### @3=93### @4=1### SET

### @1=4### @2='Lin Chaoying'### @3=93### @4=2# at960#191104 19:47:27 server id 1 end_log_pos 991 CRC32 0x70750a35 Xid = 38COMMIT/*!*/;

DELIMITER ;

# End of logfileROLLBACK/*added by mysqlbinlog*/;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[root@node105.yinzhengjie.org.cn~]#

查看"binlog_format=ROW"格式的二进制文件信息(占用更多磁盘空间，但有利于数据恢复。从数据安全角度来说，生产环境推荐使用该格式)

[root@node105.yinzhengjie.org.cn ~]# vim /mysql/3306/etc/my.cnf

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# cat /mysql/3306/etc/my.cnf

[mysqld]

log_bin= /data/logbin/mysql-bin

binlog_format=STATEMENT

character-set-server =utf8mb4

default_storage_engine=InnoDB

port= 3306datadir= /mysql/3306/data

socket= /mysql/3306/socket/mysql.sock

[mysqld_safe]

log-error = /mysql/3306/log/mariadb.log

pid-file = /mysql/3306/pid/mariadb.pid

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# /mysql/3306/mysqld restart

Restarting MySQL...

Stoping MySQL...

Starting MySQL...

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# mysql -uroot -pyinzhengjie -S /mysql/3306/socket/mysql.sock

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connectionid is 9Server version:10.2.19-MariaDB-log MariaDB Server

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

MariaDB [(none)]>MariaDB [(none)]>use yinzhengjie

Database changed

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> select *from teachers;+-----+---------------+-----+--------+

| TID | Name | Age | Gender |

+-----+---------------+-----+--------+

| 1 | Song Jiang | 45 | M |

| 2 | Zhang Sanfeng | 94 | M |

| 3 | Miejue Shitai | 77 | M |

| 4 | Lin Chaoying | 93 | M |

| 5 | Jason Yin | 26 | M |

| 6 | yinzhengjie | 18 | M |

+-----+---------------+-----+--------+

6 rows in set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> UPDATE teachers SET gender='F';

Query OK,6 rows affected (0.00sec)

Rows matched:6 Changed: 6 Warnings: 0MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> select *from teachers;+-----+---------------+-----+--------+

| TID | Name | Age | Gender |

+-----+---------------+-----+--------+

| 1 | Song Jiang | 45 | F |

| 2 | Zhang Sanfeng | 94 | F |

| 3 | Miejue Shitai | 77 | F |

| 4 | Lin Chaoying | 93 | F |

| 5 | Jason Yin | 26 | F |

| 6 | yinzhengjie | 18 | F |

+-----+---------------+-----+--------+

6 rows in set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>SHOW MASTER LOGS;+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 351 |

| mysql-bin.000002 | 375 |

| mysql-bin.000003 | 1014 |

| mysql-bin.000004 | 519 |

+------------------+-----------+

4 rows in set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>SHOW MASTER STATUS;+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000004 | 519 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>SELECT @@binlog_format;+-----------------+

| @@binlog_format |

+-----------------+

| STATEMENT |

+-----------------+

1 row in set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>QUIT

Bye

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# mysqlbinlog /data/logbin/mysql-bin.000004 --verbose/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER/*!*/;

# at4#191104 19:52:54 server id 1 end_log_pos 256 CRC32 0xdbb83886 Start: binlog v 4, server v 10.2.19-MariaDB-log created 191104 19:52:54at startu

p# Warning: this binlog is eitherinuse or was not closed properly.

ROLLBACK/*!*/;

BINLOG'FhHAXQ8BAAAA/AAAAAABAAABAAQAMTAuMi4xOS1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAWEcBdEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAEEwQADQgICAoKCgGGOLjb'/*!*/;

# at 256#191104 19:52:54 server id 1 end_log_pos 299 CRC32 0x82b331c3 Gtid list [0-1-2]

# at299#191104 19:52:54 server id 1 end_log_pos 342 CRC32 0xc2e3f301 Binlog checkpoint mysql-bin.000004# at342#191104 19:53:18 server id 1 end_log_pos 384 CRC32 0x85f8e293 GTID 0-1-3trans/*!100101 SET @@session.skip_parallel_replication=0*//*!*/;/*!100001 SET @@session.gtid_domain_id=0*//*!*/;/*!100001 SET @@session.server_id=1*//*!*/;/*!100001 SET @@session.gtid_seq_no=3*//*!*/;

BEGIN/*!*/;

# at384#191104 19:53:18 server id 1 end_log_pos 488 CRC32 0xb6fc6c2b Query thread_id=9 exec_time=0 error_code=0use `yinzhengjie`/*!*/;

SET TIMESTAMP=1572868398/*!*/;

SET @@session.pseudo_thread_id=9/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_c

hecks=1/*!*/;SET @@session.sql_mode=1411383296/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C utf8mb4*//*!*/;

SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

UPDATE teachers SET gender='F'

/*!*/;

# at488#191104 19:53:18 server id 1 end_log_pos 519 CRC32 0x636e91ac Xid = 5COMMIT/*!*/;

DELIMITER ;

# End of logfileROLLBACK/*added by mysqlbinlog*/;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[root@node105.yinzhengjie.org.cn~]#

查看"binlog_format=STATEMENT"格式的二进制文件信息(占用较少磁盘空间，但不利于数据恢复)

[root@node105.yinzhengjie.org.cn ~]# mysqlbinlog /data/logbin/mysql-bin.000004 -v --start-position=751 --stop-position=941;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER/*!*/;

# at4#191104 19:52:54 server id 1 end_log_pos 256 CRC32 0xdbb83886 Start: binlog v 4, server v 10.2.19-MariaDB-log created 191104 19:52:54at startu

p# Warning: this binlog is eitherinuse or was not closed properly.

ROLLBACK/*!*/;

BINLOG'FhHAXQ8BAAAA/AAAAAABAAABAAQAMTAuMi4xOS1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAWEcBdEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAEEwQADQgICAoKCgGGOLjb'/*!*/;

# at 751#191104 21:32:38 server id 1 end_log_pos 868 CRC32 0xac80a98f Query thread_id=11 exec_time=0 error_code=0use `yinzhengjie`/*!*/;

SET TIMESTAMP=1572874358/*!*/;

SET @@session.pseudo_thread_id=11/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_c

hecks=1/*!*/;SET @@session.sql_mode=1411383296/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C utf8mb4*//*!*/;

SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

UPDATE teachers SET gender='M' WHERE tid =4

/*!*/;

# at868#191104 21:32:38 server id 1 end_log_pos 899 CRC32 0x3dd0af4d Xid = 20COMMIT/*!*/;

# at899#191104 21:32:41 server id 1 end_log_pos 941 CRC32 0x42fa3228 GTID 0-1-6trans/*!100101 SET @@session.skip_parallel_replication=0*//*!*/;/*!100001 SET @@session.gtid_domain_id=0*//*!*/;/*!100001 SET @@session.server_id=1*//*!*/;/*!100001 SET @@session.gtid_seq_no=6*//*!*/;

BEGIN/*!*/;

DELIMITER ;

# End of logfileROLLBACK/*added by mysqlbinlog*/;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[root@node105.yinzhengjie.org.cn~]#

根据二进制的起始结束位置查看相应日志记录信息

[root@node105.yinzhengjie.org.cn ~]# mysqlbinlog /data/logbin/mysql-bin.000004 -v --start-datetime='2019-11-04 21:35:00'

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER/*!*/;

# at4#191104 19:52:54 server id 1 end_log_pos 256 CRC32 0xdbb83886 Start: binlog v 4, server v 10.2.19-MariaDB-log created 191104 19:52:54at startu

p# Warning: this binlog is eitherinuse or was not closed properly.

ROLLBACK/*!*/;

BINLOG'FhHAXQ8BAAAA/AAAAAABAAABAAQAMTAuMi4xOS1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAWEcBdEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAEEwQADQgICAoKCgGGOLjb'/*!*/;

# at 1089#191104 21:38:40 server id 1 end_log_pos 1131 CRC32 0x7bb72842 GTID 0-1-7trans/*!100101 SET @@session.skip_parallel_replication=0*//*!*/;/*!100001 SET @@session.gtid_domain_id=0*//*!*/;/*!100001 SET @@session.server_id=1*//*!*/;/*!100001 SET @@session.gtid_seq_no=7*//*!*/;

BEGIN/*!*/;

# at1131#191104 21:38:40 server id 1 end_log_pos 1225 CRC32 0x02f0ddbe Query thread_id=11 exec_time=0 error_code=0use `yinzhengjie`/*!*/;

SET TIMESTAMP=1572874720/*!*/;

SET @@session.pseudo_thread_id=11/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_c

hecks=1/*!*/;SET @@session.sql_mode=1411383296/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C utf8mb4*//*!*/;

SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

DELETE FROM teachers/*!*/;

# at1225#191104 21:38:40 server id 1 end_log_pos 1256 CRC32 0x96cf1a5c Xid = 23COMMIT/*!*/;

DELIMITER ;

# End of logfileROLLBACK/*added by mysqlbinlog*/;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[root@node105.yinzhengjie.org.cn~]#

根据二进制日志的时间查看相应的日志记录信息

7>.通过二进制日志数据恢复案例

MariaDB [yinzhengjie]>SHOW MASTER LOGS;+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 351 |

| mysql-bin.000002 | 375 |

| mysql-bin.000003 | 1014 |

| mysql-bin.000004 | 1256 |

+------------------+-----------+

4 rows in set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>FLUSH LOGS;　　　　　　#为了测试方便，我这里世界使用一个新的日志

Query OK,0 rows affected (0.01sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>SHOW MASTER LOGS;+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 351 |

| mysql-bin.000002 | 375 |

| mysql-bin.000003 | 1014 |

| mysql-bin.000004 | 1303 |

| mysql-bin.000005 | 385 |

+------------------+-----------+

5 rows in set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>SHOW MASTER STATUS;+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000005 | 385 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>

MariaDB [yinzhengjie]> FLUSH LOGS;　　　　　　#为了测试方便，我这里世界使用一个新的日志

MariaDB [yinzhengjie]>SHOW MASTER STATUS;+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000005 | 385 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>SELECT @@binlog_format;+-----------------+

| @@binlog_format |

+-----------------+

| STATEMENT |

+-----------------+rowin set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'sql_log_bin';+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| sql_log_bin | ON |

+---------------+-------+rowin set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> SELECT *FROM teachers;

Empty set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> INSERT INTO teachers VALUES (1,'Jason Yin',27,'F');

Query OK,1 row affected (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> INSERT INTO teachers VALUES (2,'YinZhengjie',18,'F');

Query OK,1 row affected (0.01sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> INSERT INTO teachers VALUES (3,'Jenny',20,'M');

Query OK,1 row affected (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> SELECT *FROM teachers;+-----+-------------+-----+--------+

| TID | Name | Age | Gender |

+-----+-------------+-----+--------+

| 1 | Jason Yin | 27 | F |

| 2 | YinZhengjie | 18 | F |

| 3 | Jenny | 20 | M |

+-----+-------------+-----+--------+rowsin set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>DELETE FROM teachers;

Query OK,3 rows affected (0.01sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>SHOW MASTER STATUS;+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000005 | 1141 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00sec)

MariaDB [yinzhengjie]>

执行DML语句

[root@node105.yinzhengjie.org.cn ~]# ls -l /data/logbin/total24

-rw-rw---- 1 mysql mysql 351 Nov 4 19:07 mysql-bin.000001

-rw-rw---- 1 mysql mysql 375 Nov 4 19:16 mysql-bin.000002

-rw-rw---- 1 mysql mysql 1014 Nov 4 19:52 mysql-bin.000003

-rw-rw---- 1 mysql mysql 1303 Nov 4 21:53 mysql-bin.000004

-rw-rw---- 1 mysql mysql 1141 Nov 4 22:06 mysql-bin.000005

-rw-rw---- 1 mysql mysql 150 Nov 4 21:53 mysql-bin.index

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# mysqlbinlog /data/logbin/mysql-bin.000005 -v > /root/binlog.sql

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# vim binlog.sql

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# catbinlog.sql/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER/*!*/;

# at4#191104 21:38:40 server id 1 end_log_pos 256 CRC32 0xe7202c47 Start: binlog v 4, server v 10.2.19-MariaDB-log created 191104 21:38:40# Warning: this binlog is eitherinuse or was not closed properly.

BINLOG'4CnAXQ8BAAAA/AAAAAABAAABAAQAMTAuMi4xOS1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAEEwQADQgICAoKCgFHLCDn'/*!*/;

# at 256#191104 21:38:40 server id 1 end_log_pos 299 CRC32 0xd816dae3 Gtid list [0-1-7]

# at299#191104 21:38:40 server id 1 end_log_pos 342 CRC32 0xd0a61645 Binlog checkpoint mysql-bin.000004# at342#191104 21:53:37 server id 1 end_log_pos 385 CRC32 0x10d221ba Binlog checkpoint mysql-bin.000005# at385#191104 21:38:40 server id 1 end_log_pos 427 CRC32 0x85937970 GTID 0-1-7trans/*!100101 SET @@session.skip_parallel_replication=0*//*!*/;/*!100001 SET @@session.gtid_domain_id=0*//*!*/;/*!100001 SET @@session.server_id=1*//*!*/;/*!100001 SET @@session.gtid_seq_no=7*//*!*/;

BEGIN/*!*/;

# at427#191104 21:38:40 server id 1 end_log_pos 551 CRC32 0x60abf36f Query thread_id=9 exec_time=1636 error_code=0use `yinzhengjie`/*!*/;

SET TIMESTAMP=1572874720/*!*/;

SET @@session.pseudo_thread_id=9/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_c

hecks=1/*!*/;SET @@session.sql_mode=1411383296/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C utf8*//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=45,@@session.collation_server=45/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

INSERT INTO teachers VALUES (1,'Jason Yin',27,'F')/*!*/;

# at551#191104 21:38:40 server id 1 end_log_pos 582 CRC32 0x84f3837a Xid = 106COMMIT/*!*/;

# at582#191104 21:38:40 server id 1 end_log_pos 624 CRC32 0xc9e4ee56 GTID 0-1-8trans/*!100001 SET @@session.gtid_seq_no=8*//*!*/;

BEGIN/*!*/;

# at624#191104 21:38:40 server id 1 end_log_pos 750 CRC32 0x3b86f49f Query thread_id=9 exec_time=1643 error_code=0SET TIMESTAMP=1572874720/*!*/;

INSERT INTO teachers VALUES (2,'YinZhengjie',18,'F')/*!*/;

# at750#191104 21:38:40 server id 1 end_log_pos 781 CRC32 0x9b4e967d Xid = 107COMMIT/*!*/;

# at781#191104 21:38:40 server id 1 end_log_pos 823 CRC32 0xea5e6c78 GTID 0-1-9trans/*!100001 SET @@session.gtid_seq_no=9*//*!*/;

BEGIN/*!*/;

# at823#191104 21:38:40 server id 1 end_log_pos 943 CRC32 0x0a560dec Query thread_id=9 exec_time=1652 error_code=0SET TIMESTAMP=1572874720/*!*/;

INSERT INTO teachers VALUES (3,'Jenny',20,'M')/*!*/;

# at943#191104 21:38:40 server id 1 end_log_pos 974 CRC32 0x2f20a2ac Xid = 108COMMIT/*!*/;

# at974#191104 21:38:40 server id 1 end_log_pos 1016 CRC32 0xbd542f84 GTID 0-1-10trans/*!100001 SET @@session.gtid_seq_no=10*//*!*/;

BEGIN/*!*/;

# at1016#191104 21:38:40 server id 1 end_log_pos 1110 CRC32 0x01e0a619 Query thread_id=9 exec_time=1668 error_code=0SET TIMESTAMP=1572874720/*!*/;

#DELETE FROM teachers　　　　　　　　#将这条删除语句给注释掉，通过当前二进制文件进行还原。/*!*/;

# at1110#191104 21:38:40 server id 1 end_log_pos 1141 CRC32 0x77eb3332 Xid = 110COMMIT/*!*/;

DELIMITER ;

# End of logfileROLLBACK/*added by mysqlbinlog*/;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[root@node105.yinzhengjie.org.cn~]#

查看二进制日志文件，并将DML这种的DELETE语句注释掉

MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'sql_log_bin';+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| sql_log_bin | ON |

+---------------+-------+

1 row in set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> SET sql_log_bin=OFF;　　　　　　　　#将二进制日志关闭，因为我们需要手动还原表中的数据。还原过程无需记录日志

Query OK,0 rows affected (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'sql_log_bin';+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| sql_log_bin | OFF |

+---------------+-------+

1 row in set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> SELECT *FROM teachers;　　　　　　　　#查看该表数据未空

Empty set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>SOURCE binlog.sql　　　　　　　　　　　　#通过咱们修改的数据进行还原

Query OK,0 rows affected (0.00sec)

Database changed

Query OK,0 rows affected (0.00sec)

Charset changed

Query OK,0 rows affected (0.00sec)

Query OK,1 row affected (0.00sec)

Query OK,0 rows affected (0.00sec)

Query OK,1 row affected (0.00sec)

Query OK,0 rows affected (0.00sec)

Query OK,1 row affected (0.00sec)

Query OK,0 rows affected (0.00sec)

ERROR at line81 in file: 'binlog.sql': No query specified

Query OK,0 rows affected (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> SELECT *FROM teachers;　　　　　　　　#还原后发现表中的数据的确存在啦+-----+-------------+-----+--------+

| TID | Name | Age | Gender |

+-----+-------------+-----+--------+

| 1 | Jason Yin | 27 | F |

| 2 | YinZhengjie | 18 | F |

| 3 | Jenny | 20 | M |

+-----+-------------+-----+--------+

3 rows in set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> SET sql_log_bin=ON;　　　　　　　　　　#做完数据恢复操作后记得将二进制日志文件功能打开。

Query OK,0 rows affected (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'sql_log_bin';+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| sql_log_bin | ON |

+---------------+-------+

1 row in set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>

基于编辑后的二进制文件进行数据表的恢复操作

8>.二进制日志事件的格式

[root@node105.yinzhengjie.org.cn ~]# mysqlbinlog /data/logbin/mysql-bin.000005 -v

......

# at 1016#191104 21:38:40 server id 1 end_log_pos 1110 CRC32 0x01e0a619 Query thread_id=9 exec_time=1668 error_code=0SET TIMESTAMP=1572874720/*!*/;

DELETE FROM teachers/*!*/;

......

以上记录观点点说明:

事件发生的日期和时间：191104 21:38:40事件发生的服务器标识：serverid 1事件的结束位置：end_log_pos1110事件的类型：Query

事件发生时所在服务器执行此事件的线程的ID：thread_id=9语句的时间戳与将其写入二进制文件中的时间差：exec_time=1668错误代码：error_code=0事件内容："DELETE FROM teachers"

9>.清除指定二进制日志(生产环境中建议保留半个月以上的日志)

MariaDB [yinzhengjie]>SHOW BINARY LOGS;+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 351 |

| mysql-bin.000002 | 375 |

| mysql-bin.000003 | 1014 |

| mysql-bin.000004 | 1303 |

| mysql-bin.000005 | 1141 |

+------------------+-----------+rowsin set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> PURGE BINARY LOGS TO 'mysql-bin.000003'; 　　　　　　　　#删除"mysql-bin.000003"之前的日志

Query OK,0 rows affected (0.01sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>SHOW BINARY LOGS;+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000003 | 1014 |

| mysql-bin.000004 | 1303 |

| mysql-bin.000005 | 1141 |

+------------------+-----------+rowsin set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/total16

-rw-rw---- 1 mysql mysql 1014 Nov 4 19:52 mysql-bin.000003

-rw-rw---- 1 mysql mysql 1303 Nov 4 21:53 mysql-bin.000004

-rw-rw---- 1 mysql mysql 1141 Nov 4 22:06 mysql-bin.000005

-rw-rw---- 1 mysql mysql 90 Nov 4 22:21 mysql-bin.index

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> SYSTEM cat /data/logbin/mysql-bin.index/data/logbin/mysql-bin.000003

/data/logbin/mysql-bin.000004

/data/logbin/mysql-bin.000005MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>

MariaDB [yinzhengjie]> PURGE BINARY LOGS TO 'mysql-bin.000003'; 　　　　　　　　#删除"mysql-bin.000003"之前的日志

MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/total16

-rw-rw---- 1 mysql mysql 1014 Nov 4 19:52 mysql-bin.000003

-rw-rw---- 1 mysql mysql 1303 Nov 4 21:53 mysql-bin.000004

-rw-rw---- 1 mysql mysql 1141 Nov 4 22:06 mysql-bin.000005

-rw-rw---- 1 mysql mysql 90 Nov 4 22:21 mysql-bin.index

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>SHOW BINARY LOGS;+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000003 | 1014 |

| mysql-bin.000004 | 1303 |

| mysql-bin.000005 | 1141 |

+------------------+-----------+

3 rows in set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> PURGE BINARY LOGS BEFORE '2019-11-4 22:00:00';

Query OK,0 rows affected (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>SHOW BINARY LOGS;+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000005 | 1141 |

+------------------+-----------+

1 row in set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/total8

-rw-rw---- 1 mysql mysql 1141 Nov 4 22:06 mysql-bin.000005

-rw-rw---- 1 mysql mysql 30 Nov 4 22:27 mysql-bin.index

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> SYSTEM cat /data/logbin/mysql-bin.index/data/logbin/mysql-bin.000005MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>

MariaDB [yinzhengjie]> PURGE BINARY LOGS BEFORE '2019-11-4 22:00:00';　　　　　#删除"2019-11-4 22:00:00"之前的日志

10>.删除所有二进制日志，index文件重新记数

RESET MASTER [TO #]; 删除所有二进制日志文件，并重新生成日志文件，文件名从#开始记数，默认从1开始，一般是master主机第一次启动时执行，MariaDB10.1.6开始支持TO #

MariaDB [yinzhengjie]>SHOW BINARY LOGS;+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000005 | 1188 |

| mysql-bin.000006 | 432 |

| mysql-bin.000007 | 432 |

| mysql-bin.000008 | 432 |

| mysql-bin.000009 | 432 |

| mysql-bin.000010 | 432 |

| mysql-bin.000011 | 385 |

+------------------+-----------+

7 rows in set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/total32

-rw-rw---- 1 mysql mysql 1188 Nov 4 22:30 mysql-bin.000005

-rw-rw---- 1 mysql mysql 432 Nov 4 22:30 mysql-bin.000006

-rw-rw---- 1 mysql mysql 432 Nov 4 22:30 mysql-bin.000007

-rw-rw---- 1 mysql mysql 432 Nov 4 22:30 mysql-bin.000008

-rw-rw---- 1 mysql mysql 432 Nov 4 22:30 mysql-bin.000009

-rw-rw---- 1 mysql mysql 432 Nov 4 22:30 mysql-bin.000010

-rw-rw---- 1 mysql mysql 385 Nov 4 22:30 mysql-bin.000011

-rw-rw---- 1 mysql mysql 210 Nov 4 22:30 mysql-bin.index

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>RESET MASTER;　　　　　　　　　　　　　　　　#删除所有二进制文件，并重新生产日志文件，文件名称从默认从1开始计数。

Query OK,0 rows affected (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/total8

-rw-rw---- 1 mysql mysql 328 Nov 4 22:31 mysql-bin.000001

-rw-rw---- 1 mysql mysql 30 Nov 4 22:31 mysql-bin.index

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>SHOW BINARY LOGS;+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 328 |

+------------------+-----------+

1 row in set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>

MariaDB [yinzhengjie]> RESET MASTER;　　　　　　　　#删除所有二进制文件，并重新生产日志文件，文件名称从默认从1开始计数。

MariaDB [yinzhengjie]>SHOW BINARY LOGS;+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 375 |

| mysql-bin.000002 | 418 |

| mysql-bin.000003 | 418 |

| mysql-bin.000004 | 418 |

| mysql-bin.000005 | 418 |

| mysql-bin.000006 | 418 |

| mysql-bin.000007 | 371 |

+------------------+-----------+

7 rows in set (0.00sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/total32

-rw-rw---- 1 mysql mysql 375 Nov 4 22:33 mysql-bin.000001

-rw-rw---- 1 mysql mysql 418 Nov 4 22:33 mysql-bin.000002

-rw-rw---- 1 mysql mysql 418 Nov 4 22:33 mysql-bin.000003

-rw-rw---- 1 mysql mysql 418 Nov 4 22:33 mysql-bin.000004

-rw-rw---- 1 mysql mysql 418 Nov 4 22:33 mysql-bin.000005

-rw-rw---- 1 mysql mysql 418 Nov 4 22:33 mysql-bin.000006

-rw-rw---- 1 mysql mysql 371 Nov 4 22:33 mysql-bin.000007

-rw-rw---- 1 mysql mysql 210 Nov 4 22:33 mysql-bin.index

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> RESET MASTER TO 3;　　　　　　　　#删除所有二进制日志并指定起始文件名称数字为3

Query OK,0 rows affected (0.01sec)

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/total8

-rw-rw---- 1 mysql mysql 328 Nov 4 22:34 mysql-bin.000003

-rw-rw---- 1 mysql mysql 30 Nov 4 22:34 mysql-bin.index

MariaDB [yinzhengjie]>MariaDB [yinzhengjie]>SHOW BINARY LOGS;+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000003 | 328 |

+------------------+-----------+

1 row in set (0.00sec)

MariaDB [yinzhengjie]>

MariaDB [yinzhengjie]> RESET MASTER TO 3;　　　　　　　　#删除所有二进制日志并指定起始文件名称数字为3

六.中继日志(reley log)

主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件。

七.客户端命令默认保存日志

默认再用户家目录，有保存客户端所有执行的SQL命令哟，其名称为"~/.mysql_history"[root@node105.yinzhengjie.org.cn~]# ll ~/.mysql_history-rw------- 1 root root 40956 Nov 5 22:05 /root/.mysql_history

[root@node105.yinzhengjie.org.cn~]#