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.00 sec) 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 mysql mysql Nov : /data/logs/
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# ll /data/logs/ -a
total
drwxr-xr-x mysql mysql Nov : .
drwxr-xr-x root root Nov : ..
[root@node105.yinzhengjie.org.cn ~]#
[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//etc/my.cnf
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# cat /mysql//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个
character-set-server = utf8mb4
default_storage_engine = InnoDB
autocommit =
skip_name_resolve =
userstat = ON
port =
datadir = /mysql//data
socket = /mysql//socket/mysql.sock [mysqld_safe]
log-error = /mysql//log/mariadb.log
pid-file = /mysql//pid/mariadb.pid [root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# /mysql//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
LISTEN *: *:*
LISTEN ::: :::*
LISTEN ::: :::*
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 10.2.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
MariaDB [(none)]> SHOW VARIABLES LIKE '%innodb_log%';
+-------------------------------+------------+
| Variable_name | Value |
+-------------------------------+------------+
| innodb_log_arch_dir | |
| innodb_log_arch_expire_sec | |
| innodb_log_archive | OFF |
| innodb_log_block_size | |
| innodb_log_buffer_size | |
| innodb_log_checksum_algorithm | DEPRECATED |
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | |
| innodb_log_files_in_group | |
| innodb_log_group_home_dir | /data/logs |
| innodb_log_optimize_ddl | ON |
| innodb_log_write_ahead_size | |
+-------------------------------+------------+
rows in set (0.00 sec) MariaDB [(none)]>
[root@node105.yinzhengjie.org.cn ~]# cat /mysql/3306/etc/my.cnf #修改默认事务日志相关参数
[root@node105.yinzhengjie.org.cn ~]# ll /data/logs/ #重启MySQL实例后，事务日志被单独存放啦，很显然配置生效啦~
total
-rw-rw---- mysql mysql Nov : ib_logfile0
-rw-rw---- mysql mysql Nov : ib_logfile1
-rw-rw---- mysql mysql Nov : ib_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模拟MariaDB .5组提交（每组提交3个同步），此项MariaDB .0支持
MariaDB [yinzhengjie]> SHOW VARIABLES LIKE '%innodb_flush_log_at_trx_commit%'; #默认设置为1
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | |
+--------------------------------+-------+
row in set (0.00 sec) 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//etc/my.cnf
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# cat /mysql//etc/my.cnf #指定错误日志存放路径，在启动MySQL实例后"log_error"的路径不可被修改，因为它是只读的。
[mysqld]
innodb_log_group_home_dir = /data/logs
innodb_log_file_size = 10M
innodb_log_files_in_group =
log_warnings =
character-set-server = utf8mb4
default_storage_engine = InnoDB
autocommit =
skip_name_resolve =
userstat = ON
port =
datadir = /mysql//data
socket = /mysql//socket/mysql.sock [mysqld_safe]
log-error = /mysql//log/mariadb.log
pid-file = /mysql//pid/mariadb.pid
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# /mysql//mysqld restart
Restarting MySQL...
Stoping MySQL...
Starting MySQL...
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 10.2.-MariaDB MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'log_error';
+---------------+-----------------------------+
| Variable_name | Value |
+---------------+-----------------------------+
| log_error | /mysql//log/mariadb.log |
+---------------+-----------------------------+
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'log_warnings';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_warnings | |
+---------------+-------+
row in set (0.00 sec) 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.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SET GLOBAL general_log = ON; #临时开启通用日志功能
Query OK, 0 rows affected (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW GLOBAL VARIABLES LIKE 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | ON |
+---------------+-------+
1 row in set (0.00 sec) 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.00 sec) 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 Argument
191104 16:40:38 9 Query SHOW GLOBAL VARIABLES LIKE 'general_log'
191104 16:40:46 10 Query SHOW TABLES
191104 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 11 Quit
191104 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 10 Quit
[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.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SET GLOBAL log_output = 'table';
Query OK, 0 rows affected (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW GLOBAL VARIABLES LIKE 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | TABLE |
+---------------+-------+
1 row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM mysql.general_log\G #查看表中的通用日志记录信息
*************************** 1. row ***************************
event_time: 2019-11-04 16:50:55.178466
user_host: root[root] @ localhost []
thread_id: 9
server_id: 1
command_type: Query
argument: SHOW GLOBAL VARIABLES LIKE 'log_output'
*************************** 2. row ***************************
event_time: 2019-11-04 16:51:24.556260
user_host: root[root] @ localhost []
thread_id: 9
server_id: 1
command_type: Query
argument: SELECT * FROM mysql.general_log
2 rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW GLOBAL VARIABLES LIKE 'log_output'; #查看默认的日志输出类型
MariaDB [yinzhengjie]> SELECT * FROM mysql.general_log;
+----------------------------+---------------------------+-----------+-----------+--------------+-----------------------------------------+
| event_time | user_host | thread_id | server_id | command_type | argument |
+----------------------------+---------------------------+-----------+-----------+--------------+-----------------------------------------+
| 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.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT argument,COUNT(*) FROM mysql.general_log GROUP BY argument; #找出查询此处最多的语句可以来创建相关索引。
+-----------------------------------------+----------+
| 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.00 sec) 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 = 3
log_warnings = 10
general_log = ON
slow_query_log = ON #开启慢查询日志
long_query_time = 5 #指定慢查询超时时间为5秒就记录到文件
character-set-server = utf8mb4
default_storage_engine = InnoDB
autocommit = 1
skip_name_resolve = 1
userstat = ON
port = 3306
datadir = /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 connection id is 8
Server version: 10.2.19-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
MariaDB [(none)]> SELECT SLEEP(1) FROM yinzhengjie.teachers; #有多少条记录这里就会休眠几秒，这里4条记录仅休眠4秒，而我们定义的慢查询日志超时时间为5秒，因此该条记录不会被记录。
+----------+
| SLEEP(1) |
+----------+
| 0 |
| 0 |
| 0 |
| 0 |
+----------+
4 rows in set (4.01 sec) MariaDB [(none)]>
MariaDB [(none)]> SELECT SLEEP(1) FROM yinzhengjie.students; #同理，在yinzhengjie.students表有25行记录，因此休眠5秒，符合我们定义慢日志查询超时时间为5秒，因此该条记录会被记录到慢查询日志中。
+----------+
| SLEEP(1) |
+----------+
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
+----------+
25 rows in set (25.03 sec) 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) FROM yinzhengjie.students;
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]#
详情请戳我
4>.通过profiling工具分析慢查询日志
MariaDB [yinzhengjie]> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SET profiling = ON; #开启profiling功能
Query OK, 0 rows affected (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec) 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.00 sec) 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.00 sec) MariaDB [yinzhengjie]>
五.二进制日志 (binary log,Oracle称为归档日志)
1>.二进制日志记录内容
记录内容:
记录导致数据改变或潜在导致数据改变的SQL语句(即只记录增删改操作)
记录已提交的日志
不依赖于存储引擎类型
功能：
通过“重放”日志文件中的事件来生成数据副本
温馨提示：
建议生产环境中二进制日志和数据文件分开存放，当数据文件存放目录损坏，可通过二进制文件恢复。
2>.二进制日志记录格式
二进制日志记录三种格式:
基于“语句”记录：
statement，只记录语句，默认模式。该模式存在弊端，比如执行"UPDATE students SET birth = now();"无法保存具体的时间戳，若按照该语句进行还原数据准确性肯定出现问题。
基于“行”记录：
row，只记录数据，即直接将数据存储下来，但日志量较大。适合数据相对来说重要的场景。推荐使用这种模式，数据恢复时准确的最高，但带来的代价就是得牺牲更多的磁盘空间。建议健康磁盘剩余空间进行及时扩充。
混合模式：
mixed,相对来说比较折中的方式,让系统自行判定该基于哪种方式进行。 格式配置:
SHOW VARIABLES LIKE 'binlog_format';
MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'binlog_format'; ` #查看二进制默认的记录格式
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.00 sec) 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 mysql mysql Nov : /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//etc/my.cnf
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# cat /mysql//etc/my.cnf #配置二进制文件的默认存放路径
[mysqld]
log_bin = /data/logbin/mysql-bin #指定二进制日志存放路径及文件名称前缀
character-set-server = utf8mb4
default_storage_engine = InnoDB
port =
datadir = /mysql//data
socket = /mysql//socket/mysql.sock [mysqld_safe]
log-error = /mysql//log/mariadb.log
pid-file = /mysql//pid/mariadb.pid
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# ll /data/logbin/
total
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# /mysql//mysqld start
Starting MySQL...
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# ll /data/logbin/ #启动服务后二进制日志存放到指定路径中
total
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# cat /data/logbin/mysql-bin.index #查看现在所有可用的二进制文件名称
/data/logbin/mysql-bin.
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# cat /mysql/3306/etc/my.cnf #配置二进制文件的默认存放路径
[root@node105.yinzhengjie.org.cn ~]# vim /mysql//etc/my.cnf
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# cat /mysql//etc/my.cnf #配置二进制文件的默认格式
[mysqld]
log_bin = /data/logbin/mysql-bin
binlog_format = ROW #修改二进制的格式为基于行的，这意味着需要更多的占用磁盘使用空间。
character-set-server = utf8mb4
default_storage_engine = InnoDB
port =
datadir = /mysql//data
socket = /mysql//socket/mysql.sock [mysqld_safe]
log-error = /mysql//log/mariadb.log
pid-file = /mysql//pid/mariadb.pid
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# /mysql//mysqld restart
Restarting MySQL...
Stoping MySQL...
Starting MySQL...
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# ll /data/logbin/ #每次重启MySQL实例都会滚动二进制日志文件
total
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# cat /data/logbin/mysql-bin.index #查看所有可用的二进制文件
/data/logbin/mysql-bin.
/data/logbin/mysql-bin.
[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. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW BINARY LOGS;
MariaDB [yinzhengjie]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin. | | | |
+------------------+----------+--------------+------------------+
row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW MASTER STATUS;
MariaDB [yinzhengjie]> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> FLUSH LOGS;
Query OK, rows affected (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/ #使用SYSTEM调用系统命令查看操作系统的确也有对应的文件生成啦。
total
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> FLUSH LOGS;
MariaDB [yinzhengjie]> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/
total
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> INSERT INTO teachers (name,age,gender) VALUES ('Jason Yin',,'M'),('yinzhengjie',,'M');
Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| | Song Jiang | | M |
| | Zhang Sanfeng | | M |
| | Miejue Shitai | | F |
| | Lin Chaoying | | F |
| | Jason Yin | | M |
| | yinzhengjie | | M |
+-----+---------------+-----+--------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin. | | | |
+------------------+----------+--------------+------------------+
row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW BINLOG EVENTS IN 'mysql-bin.000003'; #查看二进制文件的内容
+------------------+-----+-------------------+-----------+-------------+-------------------------------------------------------------------------
------------------+| Log_name | Pos | Event_type | Server_id | End_log_pos | Info
|+------------------+-----+-------------------+-----------+-------------+-------------------------------------------------------------------------
------------------+| mysql-bin. | | Format_desc | | | Server ver: 10.2.-MariaDB-log, Binlog ver:
|| mysql-bin. | | Gtid_list | | | []
|| mysql-bin. | | Binlog_checkpoint | | | mysql-bin.
|| mysql-bin. | | Binlog_checkpoint | | | mysql-bin.
|| mysql-bin. | | Gtid | | | BEGIN GTID --
|| mysql-bin. | | Annotate_rows | | | INSERT INTO teachers (name,age,gender) VALUES ('Jason Yin',,'M'),('yin
zhengjie',18,'M') |
| mysql-bin. | | Table_map | | | table_id: (yinzhengjie.teachers)
|| mysql-bin. | | Write_rows_v1 | | | table_id: flags: STMT_END_F
|| mysql-bin. | | Xid | | | COMMIT /* xid=16 */
|+------------------+-----+-------------------+-----------+-------------+-------------------------------------------------------------------------
------------------+ rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW BINLOG EVENTS IN 'mysql-bin.000003'; #查看二进制文件的内容，查看的内容不详细
MariaDB [yinzhengjie]> SHOW BINLOG EVENTS IN 'mysql-bin.000003';
+------------------+-----+-------------------+-----------+-------------+-------------------------------------------------------------------------
------------------+| Log_name | Pos | Event_type | Server_id | End_log_pos | Info
|+------------------+-----+-------------------+-----------+-------------+-------------------------------------------------------------------------
------------------+| mysql-bin. | | Format_desc | | | Server ver: 10.2.-MariaDB-log, Binlog ver:
|| mysql-bin. | | Gtid_list | | | []
|| mysql-bin. | | Binlog_checkpoint | | | mysql-bin.
|| mysql-bin. | | Binlog_checkpoint | | | mysql-bin.
|| mysql-bin. | | Gtid | | | BEGIN GTID --
|| mysql-bin. | | Annotate_rows | | | INSERT INTO teachers (name,age,gender) VALUES ('Jason Yin',,'M'),('yin
zhengjie',18,'M') |
| mysql-bin. | | Table_map | | | table_id: (yinzhengjie.teachers)
|| mysql-bin. | | Write_rows_v1 | | | table_id: flags: STMT_END_F
|| mysql-bin. | | Xid | | | COMMIT /* xid=16 */
|+------------------+-----+-------------------+-----------+-------------+-------------------------------------------------------------------------
------------------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW BINLOG EVENTS IN 'mysql-bin.000003' FROM LIMIT ;
+------------------+-----+-------------------+-----------+-------------+------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------------+-----------+-------------+------------------+
| mysql-bin. | | Binlog_checkpoint | | | mysql-bin. |
| mysql-bin. | | Binlog_checkpoint | | | mysql-bin. |
+------------------+-----+-------------------+-----------+-------------+------------------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW BINLOG EVENTS IN 'mysql-bin.000003' FROM LIMIT ,; #从指定位置查看相应的信息
+------------------+-----+---------------+-----------+-------------+-----------------------------------------------------------------------------
--------------+| Log_name | Pos | Event_type | Server_id | End_log_pos | Info
|+------------------+-----+---------------+-----------+-------------+-----------------------------------------------------------------------------
--------------+| mysql-bin. | | Annotate_rows | | | INSERT INTO teachers (name,age,gender) VALUES ('Jason Yin',,'M'),('yinzhen
gjie',18,'M') |
| mysql-bin. | | Table_map | | | table_id: (yinzhengjie.teachers)
|| mysql-bin. | | Write_rows_v1 | | | table_id: flags: STMT_END_F
|| mysql-bin. | | Xid | | | COMMIT /* xid=16 */
|+------------------+-----+---------------+-----------+-------------+-----------------------------------------------------------------------------
--------------+
rows in set (0.00 sec) 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/
total
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysqlbinlog /data/logbin/mysql-bin. #可用查看二进制文件，但有基于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 /*!*/;
# at
# :: server id end_log_pos CRC32 0x4e4a71e5 Start: binlog v , server v 10.2.-MariaDB-log created ::
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
ngjAXQ8BAAAA/AAAAAABAAABAAQAMTAuMi4xOS1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQADQgICAoKCgHlcUpO
'/*!*/;
# at
# :: server id end_log_pos CRC32 0x11540620 Gtid list []
# at
# :: server id end_log_pos CRC32 0x3b612022 Binlog checkpoint mysql-bin.
# at
# :: server id end_log_pos CRC32 0xce9f3b39 Binlog checkpoint mysql-bin.
# at
# :: server id end_log_pos CRC32 0xbc3f3fe1 GTID -- trans
/*!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
/*!*/;
# at
# at
# :: server id end_log_pos CRC32 0xe7b70b5b Annotate_rows:
#Q> INSERT INTO teachers (name,age,gender) VALUES ('Jason Yin',,'M'),('yinzhengjie',,'M')
# :: server id end_log_pos CRC32 0xd48ca5db Table_map: `yinzhengjie`.`teachers` mapped to number
# at
# :: server id end_log_pos CRC32 0x1a95ea95 Write_rows: table id flags: STMT_END_F BINLOG '
uArAXRMBAAAAQQAAAE4CAAAAABYAAAAAAAEAC3lpbnpoZW5namllAAh0ZWFjaGVycwAEAg8B/gQs
AfcBCNuljNQ=
uArAXRcBAAAAQwAAAJECAAAAABYAAAAAAAEABP/wBQAJAEphc29uIFlpbhoC8AYACwB5aW56aGVu
Z2ppZRICleqVGg==
'/*!*/;
# at
# :: server id end_log_pos CRC32 0x6b6a92b0 Xid =
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* 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/
total
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysqlbinlog /data/logbin/mysql-bin. --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 /*!*/;
# at
# :: server id end_log_pos CRC32 0x4e4a71e5 Start: binlog v , server v 10.2.-MariaDB-log created ::
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
ngjAXQ8BAAAA/AAAAAABAAABAAQAMTAuMi4xOS1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQADQgICAoKCgHlcUpO
'/*!*/;
# at
# :: server id end_log_pos CRC32 0x11540620 Gtid list []
# at
# :: server id end_log_pos CRC32 0x3b612022 Binlog checkpoint mysql-bin.
# at
# :: server id end_log_pos CRC32 0xce9f3b39 Binlog checkpoint mysql-bin.
# at
# :: server id end_log_pos CRC32 0xbc3f3fe1 GTID -- trans
/*!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
/*!*/;
# at
# at
# :: server id end_log_pos CRC32 0xe7b70b5b Annotate_rows:
#Q> INSERT INTO teachers (name,age,gender) VALUES ('Jason Yin',,'M'),('yinzhengjie',,'M')
# :: server id end_log_pos CRC32 0xd48ca5db Table_map: `yinzhengjie`.`teachers` mapped to number
# at
# :: server id end_log_pos CRC32 0x1a95ea95 Write_rows: table id flags: STMT_END_F BINLOG '
uArAXRMBAAAAQQAAAE4CAAAAABYAAAAAAAEAC3lpbnpoZW5namllAAh0ZWFjaGVycwAEAg8B/gQs
AfcBCNuljNQ=
uArAXRcBAAAAQwAAAJECAAAAABYAAAAAAAEABP/wBQAJAEphc29uIFlpbhoC8AYACwB5aW56aGVu
Z2ppZRICleqVGg==
'/*!*/;
### INSERT INTO `yinzhengjie`.`teachers`
### SET
### @=
### @='Jason Yin'
### @=
### @=
### INSERT INTO `yinzhengjie`.`teachers`
### SET
### @=
### @='yinzhengjie'
### @=
### @=
# at
# :: server id end_log_pos CRC32 0x6b6a92b0 Xid =
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* 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 ~]#
[root@node105.yinzhengjie.org.cn ~]# mysqlbinlog /data/logbin/mysql-bin.000003 --verbose #查看详细信息
MariaDB [yinzhengjie]> SELECT @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+
row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| | Song Jiang | | M |
| | Zhang Sanfeng | | M |
| | Miejue Shitai | | F |
| | Lin Chaoying | | F |
| | Jason Yin | | M |
| | yinzhengjie | | M |
+-----+---------------+-----+--------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> UPDATE teachers SET gender='M';
Query OK, rows affected (0.00 sec)
Rows matched: Changed: Warnings: MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| | Song Jiang | | M |
| | Zhang Sanfeng | | M |
| | Miejue Shitai | | M |
| | Lin Chaoying | | M |
| | Jason Yin | | M |
| | yinzhengjie | | M |
+-----+---------------+-----+--------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin. | | | |
+------------------+----------+--------------+------------------+
row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> QUIT
Bye
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysqlbinlog /data/logbin/mysql-bin. --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 /*!*/;
# at
# :: server id end_log_pos CRC32 0x4e4a71e5 Start: binlog v , server v 10.2.-MariaDB-log created ::
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
ngjAXQ8BAAAA/AAAAAABAAABAAQAMTAuMi4xOS1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQADQgICAoKCgHlcUpO
'/*!*/;
# at
# :: server id end_log_pos CRC32 0x11540620 Gtid list []
# at
# :: server id end_log_pos CRC32 0x3b612022 Binlog checkpoint mysql-bin.
# at
# :: server id end_log_pos CRC32 0xce9f3b39 Binlog checkpoint mysql-bin.
# at
# :: server id end_log_pos CRC32 0xbc3f3fe1 GTID -- trans
/*!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
/*!*/;
# at
# at
# :: server id end_log_pos CRC32 0xe7b70b5b Annotate_rows:
#Q> INSERT INTO teachers (name,age,gender) VALUES ('Jason Yin',,'M'),('yinzhengjie',,'M')
# :: server id end_log_pos CRC32 0xd48ca5db Table_map: `yinzhengjie`.`teachers` mapped to number
# at
# :: server id end_log_pos CRC32 0x1a95ea95 Write_rows: table id flags: STMT_END_F BINLOG '
uArAXRMBAAAAQQAAAE4CAAAAABYAAAAAAAEAC3lpbnpoZW5namllAAh0ZWFjaGVycwAEAg8B/gQs
AfcBCNuljNQ=
uArAXRcBAAAAQwAAAJECAAAAABYAAAAAAAEABP/wBQAJAEphc29uIFlpbhoC8AYACwB5aW56aGVu
Z2ppZRICleqVGg==
'/*!*/;
### INSERT INTO `yinzhengjie`.`teachers`
### SET
### @=
### @='Jason Yin'
### @=
### @=
### INSERT INTO `yinzhengjie`.`teachers`
### SET
### @=
### @='yinzhengjie'
### @=
### @=
# at
# :: server id end_log_pos CRC32 0x6b6a92b0 Xid =
COMMIT/*!*/;
# at
# :: server id end_log_pos CRC32 0x740f1f3c GTID -- trans
/*!100001 SET @@session.gtid_seq_no=2*//*!*/;
BEGIN
/*!*/;
# at
# at
# :: server id end_log_pos CRC32 0x782ca82d Annotate_rows:
#Q> UPDATE teachers SET gender='M'
# :: server id end_log_pos CRC32 0x37af4016 Table_map: `yinzhengjie`.`teachers` mapped to number
# at
# :: server id end_log_pos CRC32 0xc306f092 Update_rows: table id flags: STMT_END_F BINLOG '
zw/AXRMBAAAAQQAAAFADAAAAABYAAAAAAAEAC3lpbnpoZW5namllAAh0ZWFjaGVycwAEAg8B/gQs
AfcBCBZArzc=
zw/AXRgBAAAAcAAAAMADAAAAABYAAAAAAAEABP//8AMADQBNaWVqdWUgU2hpdGFpTQHwAwANAE1p
ZWp1ZSBTaGl0YWlNAvAEAAwATGluIENoYW95aW5nXQHwBAAMAExpbiBDaGFveWluZ10CkvAGww==
'/*!*/;
### UPDATE `yinzhengjie`.`teachers`
### WHERE
### @=
### @='Miejue Shitai'
### @=
### @=
### SET
### @=
### @='Miejue Shitai'
### @=
### @=
### UPDATE `yinzhengjie`.`teachers`
### WHERE
### @=
### @='Lin Chaoying'
### @=
### @=
### SET
### @=
### @='Lin Chaoying'
### @=
### @=
# at
# :: server id end_log_pos CRC32 0x70750a35 Xid =
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* 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//etc/my.cnf
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# cat /mysql//etc/my.cnf
[mysqld]
log_bin = /data/logbin/mysql-bin
binlog_format = STATEMENT
character-set-server = utf8mb4
default_storage_engine = InnoDB
port =
datadir = /mysql//data
socket = /mysql//socket/mysql.sock [mysqld_safe]
log-error = /mysql//log/mariadb.log
pid-file = /mysql//pid/mariadb.pid
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# /mysql//mysqld restart
Restarting MySQL...
Stoping MySQL...
Starting MySQL...
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -S /mysql//socket/mysql.sock
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 10.2.-MariaDB-log MariaDB Server Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
MariaDB [(none)]> use yinzhengjie
Database changed
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| | Song Jiang | | M |
| | Zhang Sanfeng | | M |
| | Miejue Shitai | | M |
| | Lin Chaoying | | M |
| | Jason Yin | | M |
| | yinzhengjie | | M |
+-----+---------------+-----+--------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> UPDATE teachers SET gender='F';
Query OK, rows affected (0.00 sec)
Rows matched: Changed: Warnings: MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| | Song Jiang | | F |
| | Zhang Sanfeng | | F |
| | Miejue Shitai | | F |
| | Lin Chaoying | | F |
| | Jason Yin | | F |
| | yinzhengjie | | F |
+-----+---------------+-----+--------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin. | | | |
+------------------+----------+--------------+------------------+
row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| STATEMENT |
+-----------------+
row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> QUIT
Bye
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysqlbinlog /data/logbin/mysql-bin. --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 /*!*/;
# at
# :: server id end_log_pos CRC32 0xdbb83886 Start: binlog v , server v 10.2.-MariaDB-log created :: at startu
p# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
FhHAXQ8BAAAA/AAAAAABAAABAAQAMTAuMi4xOS1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAWEcBdEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQADQgICAoKCgGGOLjb
'/*!*/;
# at
# :: server id end_log_pos CRC32 0x82b331c3 Gtid list [--]
# at
# :: server id end_log_pos CRC32 0xc2e3f301 Binlog checkpoint mysql-bin.
# at
# :: server id end_log_pos CRC32 0x85f8e293 GTID -- trans
/*!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
/*!*/;
# at
# :: server id end_log_pos CRC32 0xb6fc6c2b Query thread_id= exec_time= error_code=
use `yinzhengjie`/*!*/;
SET TIMESTAMP=/*!*/;
SET @@session.pseudo_thread_id=/*!*/;
SET @@session.foreign_key_checks=, @@session.sql_auto_is_null=, @@session.unique_checks=, @@session.autocommit=, @@session.check_constraint_c
hecks=/*!*/;SET @@session.sql_mode=/*!*/;
SET @@session.auto_increment_increment=, @@session.auto_increment_offset=/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=,@@session.collation_connection=,@@session.collation_server=/*!*/;
SET @@session.lc_time_names=/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
UPDATE teachers SET gender='F'
/*!*/;
# at
# :: server id end_log_pos CRC32 0x636e91ac Xid =
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* 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. -v --start-position= --stop-position=;
/*!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 /*!*/;
# at
# :: server id end_log_pos CRC32 0xdbb83886 Start: binlog v , server v 10.2.-MariaDB-log created :: at startu
p# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
FhHAXQ8BAAAA/AAAAAABAAABAAQAMTAuMi4xOS1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAWEcBdEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQADQgICAoKCgGGOLjb
'/*!*/;
# at
# :: server id end_log_pos CRC32 0xac80a98f Query thread_id= exec_time= error_code=
use `yinzhengjie`/*!*/;
SET TIMESTAMP=/*!*/;
SET @@session.pseudo_thread_id=/*!*/;
SET @@session.foreign_key_checks=, @@session.sql_auto_is_null=, @@session.unique_checks=, @@session.autocommit=, @@session.check_constraint_c
hecks=/*!*/;SET @@session.sql_mode=/*!*/;
SET @@session.auto_increment_increment=, @@session.auto_increment_offset=/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=,@@session.collation_connection=,@@session.collation_server=/*!*/;
SET @@session.lc_time_names=/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
UPDATE teachers SET gender='M' WHERE tid =
/*!*/;
# at
# :: server id end_log_pos CRC32 0x3dd0af4d Xid =
COMMIT/*!*/;
# at
# :: server id end_log_pos CRC32 0x42fa3228 GTID -- trans
/*!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 log file
ROLLBACK /* 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. -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 /*!*/;
# at
# :: server id end_log_pos CRC32 0xdbb83886 Start: binlog v , server v 10.2.-MariaDB-log created :: at startu
p# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
FhHAXQ8BAAAA/AAAAAABAAABAAQAMTAuMi4xOS1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAWEcBdEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQADQgICAoKCgGGOLjb
'/*!*/;
# at
# :: server id end_log_pos CRC32 0x7bb72842 GTID -- trans
/*!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
/*!*/;
# at
# :: server id end_log_pos CRC32 0x02f0ddbe Query thread_id= exec_time= error_code=
use `yinzhengjie`/*!*/;
SET TIMESTAMP=/*!*/;
SET @@session.pseudo_thread_id=/*!*/;
SET @@session.foreign_key_checks=, @@session.sql_auto_is_null=, @@session.unique_checks=, @@session.autocommit=, @@session.check_constraint_c
hecks=/*!*/;SET @@session.sql_mode=/*!*/;
SET @@session.auto_increment_increment=, @@session.auto_increment_offset=/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=,@@session.collation_connection=,@@session.collation_server=/*!*/;
SET @@session.lc_time_names=/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
DELETE FROM teachers
/*!*/;
# at
# :: server id end_log_pos CRC32 0x96cf1a5c Xid =
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* 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 ~]#
根据二进制日志的时间查看相应的日志记录信息
7>.通过二进制日志数据恢复案例
MariaDB [yinzhengjie]> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> FLUSH LOGS; #为了测试方便，我这里世界使用一个新的日志
Query OK, rows affected (0.01 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin. | | | |
+------------------+----------+--------------+------------------+
row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> FLUSH LOGS; #为了测试方便，我这里世界使用一个新的日志
MariaDB [yinzhengjie]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin. | | | |
+------------------+----------+--------------+------------------+
row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| STATEMENT |
+-----------------+
row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON |
+---------------+-------+
row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM teachers;
Empty set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> INSERT INTO teachers VALUES (,'Jason Yin',,'F');
Query OK, row affected (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> INSERT INTO teachers VALUES (,'YinZhengjie',,'F');
Query OK, row affected (0.01 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> INSERT INTO teachers VALUES (,'Jenny',,'M');
Query OK, row affected (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM teachers;
+-----+-------------+-----+--------+
| TID | Name | Age | Gender |
+-----+-------------+-----+--------+
| | Jason Yin | | F |
| | YinZhengjie | | F |
| | Jenny | | M |
+-----+-------------+-----+--------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> DELETE FROM teachers;
Query OK, rows affected (0.01 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin. | | | |
+------------------+----------+--------------+------------------+
row in set (0.00 sec) MariaDB [yinzhengjie]>
执行DML语句
[root@node105.yinzhengjie.org.cn ~]# ls -l /data/logbin/
total
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
[root@node105.yinzhengjie.org.cn ~]#
[root@node105.yinzhengjie.org.cn ~]# mysqlbinlog /data/logbin/mysql-bin. -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 ~]# cat binlog.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 /*!*/;
# at
# :: server id end_log_pos CRC32 0xe7202c47 Start: binlog v , server v 10.2.-MariaDB-log created ::
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
4CnAXQ8BAAAA/AAAAAABAAABAAQAMTAuMi4xOS1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQADQgICAoKCgFHLCDn
'/*!*/;
# at
# :: server id end_log_pos CRC32 0xd816dae3 Gtid list [--]
# at
# :: server id end_log_pos CRC32 0xd0a61645 Binlog checkpoint mysql-bin.
# at
# :: server id end_log_pos CRC32 0x10d221ba Binlog checkpoint mysql-bin.
# at
# :: server id end_log_pos CRC32 0x85937970 GTID -- trans
/*!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
/*!*/;
# at
# :: server id end_log_pos CRC32 0x60abf36f Query thread_id= exec_time= error_code=
use `yinzhengjie`/*!*/;
SET TIMESTAMP=/*!*/;
SET @@session.pseudo_thread_id=/*!*/;
SET @@session.foreign_key_checks=, @@session.sql_auto_is_null=, @@session.unique_checks=, @@session.autocommit=, @@session.check_constraint_c
hecks=/*!*/;SET @@session.sql_mode=/*!*/;
SET @@session.auto_increment_increment=, @@session.auto_increment_offset=/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=,@@session.collation_connection=,@@session.collation_server=/*!*/;
SET @@session.lc_time_names=/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
INSERT INTO teachers VALUES (,'Jason Yin',,'F')
/*!*/;
# at
# :: server id end_log_pos CRC32 0x84f3837a Xid =
COMMIT/*!*/;
# at
# :: server id end_log_pos CRC32 0xc9e4ee56 GTID -- trans
/*!100001 SET @@session.gtid_seq_no=8*//*!*/;
BEGIN
/*!*/;
# at
# :: server id end_log_pos CRC32 0x3b86f49f Query thread_id= exec_time= error_code=
SET TIMESTAMP=/*!*/;
INSERT INTO teachers VALUES (,'YinZhengjie',,'F')
/*!*/;
# at
# :: server id end_log_pos CRC32 0x9b4e967d Xid =
COMMIT/*!*/;
# at
# :: server id end_log_pos CRC32 0xea5e6c78 GTID -- trans
/*!100001 SET @@session.gtid_seq_no=9*//*!*/;
BEGIN
/*!*/;
# at
# :: server id end_log_pos CRC32 0x0a560dec Query thread_id= exec_time= error_code=
SET TIMESTAMP=/*!*/;
INSERT INTO teachers VALUES (,'Jenny',,'M')
/*!*/;
# at
# :: server id end_log_pos CRC32 0x2f20a2ac Xid =
COMMIT/*!*/;
# at
# :: server id end_log_pos CRC32 0xbd542f84 GTID -- trans
/*!100001 SET @@session.gtid_seq_no=10*//*!*/;
BEGIN
/*!*/;
# at
# :: server id end_log_pos CRC32 0x01e0a619 Query thread_id= exec_time= error_code=
SET TIMESTAMP=/*!*/;
#DELETE FROM teachers #将这条删除语句给注释掉，通过当前二进制文件进行还原。
/*!*/;
# at
# :: server id end_log_pos CRC32 0x77eb3332 Xid =
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* 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 ~]#
查看二进制日志文件，并将DML这种的DELETE语句注释掉
MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON |
+---------------+-------+
row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SET sql_log_bin=OFF; #将二进制日志关闭，因为我们需要手动还原表中的数据。还原过程无需记录日志
Query OK, rows affected (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | OFF |
+---------------+-------+
row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM teachers; #查看该表数据未空
Empty set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SOURCE binlog.sql #通过咱们修改的数据进行还原
Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Database changed
Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Charset changed
Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, row affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, row affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, row affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) ERROR at line in file: 'binlog.sql': No query specified Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SELECT * FROM teachers; #还原后发现表中的数据的确存在啦
+-----+-------------+-----+--------+
| TID | Name | Age | Gender |
+-----+-------------+-----+--------+
| | Jason Yin | | F |
| | YinZhengjie | | F |
| | Jenny | | M |
+-----+-------------+-----+--------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SET sql_log_bin=ON; #做完数据恢复操作后记得将二进制日志文件功能打开。
Query OK, rows affected (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW VARIABLES LIKE 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON |
+---------------+-------+
row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
基于编辑后的二进制文件进行数据表的恢复操作
8>.二进制日志事件的格式
[root@node105.yinzhengjie.org.cn ~]# mysqlbinlog /data/logbin/mysql-bin.000005 -v
......
# at
# :: server id end_log_pos CRC32 0x01e0a619 Query thread_id= exec_time= error_code=
SET TIMESTAMP=/*!*/;
DELETE FROM teachers
/*!*/;
......
以上记录观点点说明:
事件发生的日期和时间： ::
事件发生的服务器标识：server id
事件的结束位置：end_log_pos
事件的类型：Query
事件发生时所在服务器执行此事件的线程的ID：thread_id=
语句的时间戳与将其写入二进制文件中的时间差：exec_time=
错误代码：error_code=
事件内容："DELETE FROM teachers"
9>.清除指定二进制日志(生产环境中建议保留半个月以上的日志)
MariaDB [yinzhengjie]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> PURGE BINARY LOGS TO 'mysql-bin.000003'; #删除"mysql-bin.000003"之前的日志
Query OK, rows affected (0.01 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/
total
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SYSTEM cat /data/logbin/mysql-bin.index
/data/logbin/mysql-bin.
/data/logbin/mysql-bin.
/data/logbin/mysql-bin.
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> PURGE BINARY LOGS TO 'mysql-bin.000003'; #删除"mysql-bin.000003"之前的日志
MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/
total
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> PURGE BINARY LOGS BEFORE '2019-11-4 22:00:00';
Query OK, rows affected (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
+------------------+-----------+
row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/
total
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SYSTEM cat /data/logbin/mysql-bin.index
/data/logbin/mysql-bin.
MariaDB [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..6开始支持TO #
MariaDB [yinzhengjie]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/
total
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> RESET MASTER; #删除所有二进制文件，并重新生产日志文件，文件名称从默认从1开始计数。
Query OK, rows affected (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/
total
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
+------------------+-----------+
row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> RESET MASTER; #删除所有二进制文件，并重新生产日志文件，文件名称从默认从1开始计数。
MariaDB [yinzhengjie]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/
total
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> RESET MASTER TO ; #删除所有二进制日志并指定起始文件名称数字为3
Query OK, rows affected (0.01 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SYSTEM ls -l /data/logbin/
total
-rw-rw---- mysql mysql Nov : mysql-bin.
-rw-rw---- mysql mysql Nov : mysql-bin.index
MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
+------------------+-----------+
row in set (0.00 sec) MariaDB [yinzhengjie]>
MariaDB [yinzhengjie]> RESET MASTER TO 3; #删除所有二进制日志并指定起始文件名称数字为3
六.中继日志(reley log)
主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件。
七.客户端命令默认保存日志
默认再用户家目录，有保存客户端所有执行的SQL命令哟，其名称为"~/.mysql_history "
[root@node105.yinzhengjie.org.cn ~]# ll ~/.mysql_history
-rw------- root root Nov : /root/.mysql_history
[root@node105.yinzhengjie.org.cn ~]#