日志配置官方文档 : https://dev.mysql.com/doc/refman/5.7＋/en/server-logs.html
启动日志功能会降低MySQL数据库的执行速度，且会占用大量磁盘空间和使用很多内存，一般不开启MySQL数据库的日志功能
MySQL有几种不同的日志文件，通常包括错误日志文件，二进制日志，通用日志，慢查询日志，等等。这些日志可以帮助定位 mysqld 内部发生的事件，数据库性能故障，记录数据的变更历史，用户恢复数据库等等
MySQL日志文件系统的组成
通用日志 : 记录建立的客户端连接和执行的语句(包括查询)
更新日志 : 记录更改数据的语句，该日志在 MySQL 5.1+ 中已不再使用
慢查询日志 : 记录所有执行时间超过 long_query_time秒 的所有查询或不使用索引的查询
错误日志 : 记录启动、运行或停止mysqld时出现的问题
二进制日志 : 以二进制文件形式记录数据库中的操作，也就是进行增量备份，但不记录查询语句，用于记录所有更改数据的语句，还用于复制
Innodb日志 : innodb redo log
FLUSH LOGS 语句或执行 mysqladmin flush-logs或 mysqladmin refresh
通用查询日志
通用查询日志可以存放到一个文本文件或者表中，所有连接和语句被记录到该日志文件或表，缺省未开启该日志
log-output=[none|file|table|file,table] 日志输出格式，用于 通用查询日志和慢查询日志，如果不指定将会在数据目录中作为日志存储目录
general_log[={0|1}] 不添加参数或者参数为1，将启动查询日志，0或者没有改选项表示不启动查询日志，0|1也可以对应 on|off
general_log_file[=file_name] 指定日志文件名以及位置，如果不指定则会以 host_name.log 创建日志文件
mysqld 按照它接收的顺序记录语句到查询日志。这可能与执行的顺序不同，不同于更新日志和二进制日志，它们在查询执行后，但是任何一个锁释放之前记录日志。查询日志包含所有语句，而二进制日志不包含只查询数据的语句。服务器重新启动和日志刷新不会产生新的一般查询日志文件
# vi /etc/my.cnf
log_output=file,table
general_log=ON
general_log_file=/opt/logs/mysql/mysql.general_log
# cd /opt/logs/mysql
# touch mysql.general_log
# chown mysql:mysql mysql.general_log ## 一定要修改为 mysql 用户，否则将无法开启通用查询日志
注 : mysql 数据库在安装后会创建一个名称为 mysql 的用户，该用户就是 mysql 所有操作的用户因此在访问外部文件时需要修改外部文件的权限
此时还无法向文件中写入数据，这是由于该文件目录不是 mysql用户目录，可以使用如下方式关闭
# sudo vi /etc/sysconfig/selinux
将 SELINUX=enforcing 改为 SELINUX=disabled
重启机器即可
# reboot
也可以使用直接在 /var/lib/mysql 创建日志文件，就不需要关闭 SELINUX
重新启动 MySQL服务使配置生效 :
# systemctl restart mysqld
查看系统变量
mysql> show variables like '%general%';
+------------------+-----------------------------------+
| Variable_name | Value |
+------------------+-----------------------------------+
| general_log | ON |
| general_log_file | /opt/logs/mysql/mysql.general_log |
+------------------+-----------------------------------+
2 rows in set (0.00 sec)
可以看到已经开启 通用查询日志 功能
注 : 如果还未开启则可以直接使用指令 mysql> set @@global.general_log=1;
显示日志输出格式
mysql> show variables like '%log_output%';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| log_output | FILE,TABLE |
+---------------+------------+
1 row in set (0.01 sec)
慢查询日志
记录所有执行时间超过 long_query_time秒的所有查询或者不适用索引的查询。默认情况下，MySQL不开启慢查询日志，long_query_time的默认值为10，即运行时间超过10s的语句是慢查询语句。一般来说，慢查询发生在大表中，且查询的字段没有建立索引，此时，要匹配查询的字段会对全表进行扫描，耗时查 long_query_time表
查看当前慢查询日志的开启情况
mysql> show variables like '%query%';
+------------------------------+--------------------------------------+
| Variable_name | Value |
+------------------------------+--------------------------------------+
| binlog_rows_query_log_events | OFF |
| ft_query_expansion_limit | 20 |
| have_query_cache | YES |
| long_query_time | 10.000000 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/e22bfd39915d-slow.log |
+------------------------------+--------------------------------------+
13 rows in set (0.01 sec)
slow_query_log : ON 为开启慢查询日志，OFF 表示关闭慢查询日志
slow_query_log_file : 记录的慢查询日志到文件中 (默认为 主机名-slow.log)
long_query_time : 指定慢查询的阈值，即执行语句的时间若超过这个值则为慢查询语句
log_queries_not_using_indexes : 如果该值是ON，则会记录所有没有利用索引来进行查询的语句，前提是slow_query_log 的值也是ON，否则，不会奏效
查询当前慢查询的语句个数
mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
+---------------------+-------+
2 rows in set (0.02 sec)
开启 MySQL 慢查询日志
# vi /etc/my.cnf
# 在 MySQL 配置文件中增加查询信息
slow-query-log=1 # 指定开启慢查询日志
slow_query_log_file="mysql-slow.log" # 指定慢查询日志的路径
long_query_time=10 # 指定查询时间大于多少的才进行记录，但是是毫秒，也就是操作大于 10ms 的操作都会被记录
# 重启 MySQL 服务之后生效
# systemctl restart mysqld
查看慢查询日志信息
mysql> show variables like '%query%';
+------------------------------+----------------+
| Variable_name | Value |
+------------------------------+----------------+
| binlog_rows_query_log_events | OFF |
| ft_query_expansion_limit | 20 |
| have_query_cache | YES |
| long_query_time | 10.000000 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| slow_query_log | ON |
| slow_query_log_file | mysql-slow.log |
+------------------------------+----------------+
13 rows in set (0.00 sec)
如下为一段慢查询日志
# Time: 2017-07-07T06:35:46.995201Z
# User@Host: root[root] @ localhost [::1] Id: 10
# Query_time: 12.522116 Lock_time: 0.000501 Rows_sent: 0 Rows_examined: 483968
use test;
SET timestamp=1499409346;
insert into test (id,name) (select uuid() id,name from test);
错误日志
MySQL 错误日志记录MySQL运行过程中较为严重的警告和错误信息，以及MySQL每次启动和关闭的详细信息，默认在数据库中已经存在开启该日志配置信息
查看错误日志的详细信息
mysql> show variables like '%log_error%';
+---------------------+---------------------+
| Variable_name | Value |
+---------------------+---------------------+
| binlog_error_action | ABORT_SERVER |
| log_error | /var/log/mysqld.log |
| log_error_verbosity | 3 |
+---------------------+---------------------+
3 rows in set (0.00 sec)
二进制日志
包含所有更新的数据或者潜在更新的数据，以及关于每个更新数据库的语句的执行时间信息，简单说就是所有的 dml 操作都会被记录到 binlog 文件中。可用于尽可能将数据库恢复到故障点，因为二进制日志包含备份后进行的所有更新，用于在主复制服务器上记录所有将发生送给从服务器的语句
mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
6 rows in set (0.00 sec)
mysql> show variables like '%binlog%';
+-----------------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------+
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_format | ROW |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| innodb_api_enable_binlog | OFF |
| innodb_locks_unsafe_for_binlog | OFF |
| log_statements_unsafe_for_binlog | ON |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| sync_binlog | 1 |
+-----------------------------------------+----------------------+
20 rows in set (0.01 sec)
查看文件的位置
mysql> show variables like '%datadir%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| datadir | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)
开启 binlog 日志
在 my.inf 主配置文件中直接添加三行
log_bin=ON # 打开 binlog 日志
log_bin_basename=/var/lib/mysql/mysql-bin # binlog 日志的基本文件名，后面会追加标识来表示每一个文件
log_bin_index=/var/lib/mysql/mysql-bin.index # binlog 文件的索引文件，这个文件管理了所有的 binlog 文件的目录
当然也有一种简单的配置，一个参数就可以搞定
log-bin=/var/lib/mysql/mysql-bin
这一个参数的作用和上面三个的作用是相同的，mysql 会根据这个配置自动设置 log_bin 为 on 状态，自动设置 log_bin_index 文件为你指定的文件名后跟 .index
这些配置完毕之后对于 5.7 以下版本就可以了，但是如果是 5.7 及以上版本的话，重启 mysql 服务会报错，这个时候必须还要指定一个参数
server-id=123454
随机指定一个不能和其他集群中机器重名的字符串，如果只有一台机器，那就可以随便指定
如下为详细操作
# vi /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
character-set-server=utf8
collation-server=utf8_general_ci
validate_password=off
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log_output=file,table
general_log=ON
general_log_file=/var/lib/mysql/mysql.general_log
log-bin=mysql-bin
# 启用二进制日志，可以是 /var/lib/mysql/mysql-bin
server-id=131
# 服务器唯一ID，可以取IP最后一段
之后重启 mysql 服务，就可以使配置生效
# systemctl restart mysqld
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)
mysql-bin.000001 : 日志文件的数据文件
mysql-bin.index : 日志文件的索引文件
查看 binlog 的运行状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
在 Position 字段，其值为 154 表示 binlog 的当前位置，每次执行 dml 操作， position 都会改变
每次重启 mysqld 服务会自动生成一个 binlog 文件 (重新启动主机也会重启 mysql 服务)
# 重启 mysqld 服务 会发现 binlog 也发生变化
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
也可以手动刷新 binlog 文件，通过 flush logs，同样会新创建一个 binlog 文件
mysql> flush logs;
Query OK, 0 rows affected (0.31 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
可以使用 reset master 来清空所有的 binlog 数据，在生产环境中这个操作是非常危险的
mysql> reset master;
Query OK, 0 rows affected (0.40 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
binlog 文件里面存储的二进制数据，是无法直接以文本方式打开，可以通过 mysql 的 mysqlbinlog 工具查看
# cd /var/lib/mysql
# mysqlbinlog mysql-bin.000001
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'
这个问题是 mysqlbinlog 这个工具无法识别 binlog 中的配置中的 default-character-set=utf8 这个指令，有两种解决方案 :
1> 在 MySQL 配置 /etc/my.cnf 中将 default-character-set=utf8 修改为 character-set-server = utf8，再重启 mysqld 服务
2> 用 mysqlbinlog --no-defaults mysql-bin.000001 命令打开
# cd /var/lib/mysql
# ls
auto.cnf client-cert.pem ibdata1 ibtmp1 mysql-bin.000001 mysql.sock private_key.pem server-key.pem test
ca-key.pem client-key.pem ib_logfile0 metastore mysql-bin.index mysql.sock.lock public_key.pem solr
ca.pem ib_buffer_pool ib_logfile1 mysql mysql.general_log performance_schema server-cert.pem sys
# cat mysql-bin.index
./mysql-bin.000001
# mysqlbinlog --no-defaults mysql-bin.000001 # 通过 mysqlbinlog 来查看 binary log 数据
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180316 7:53:17 server id 1 end_log_pos 123 CRC32 0x39950bab Start: binlog v 4, server v 5.7.21-log created 180316 7:53:17 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
7XerWg8BAAAAdwAAAHsAAAABAAQANS43LjIxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADtd6taEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AasLlTk=
'/*!*/;
# at 123
#180316 7:53:17 server id 1 end_log_pos 154 CRC32 0x52b32896 Previous-GTIDs
# [empty]
# at 154
#180316 7:59:01 server id 1 end_log_pos 219 CRC32 0x050d9613 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#180316 7:59:01 server id 1 end_log_pos 359 CRC32 0x09315859 Query thread_id=2 exec_time=1 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1521187141/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table a(id int primary key auto_increment,name varchar(50))
/*!*/;
# at 359
#180316 7:59:25 server id 1 end_log_pos 424 CRC32 0x9867558f Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 424
#180316 7:59:25 server id 1 end_log_pos 496 CRC32 0x8c789c32 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1521187165/*!*/;
BEGIN
/*!*/;
# at 496
#180316 7:59:25 server id 1 end_log_pos 543 CRC32 0x246e3c35 Table_map: `test`.`a` mapped to number 116
# at 543
#180316 7:59:25 server id 1 end_log_pos 585 CRC32 0x248c7836 Write_rows: table id 116 flags: STMT_END_F
BINLOG '
XXmrWhMBAAAALwAAAB8CAAAAAHQAAAAAAAEABHRlc3QAAWEAAgMPAjIAAjU8biQ=
XXmrWh4BAAAAKgAAAEkCAAAAAHQAAAAAAAEAAgAC//wBAAAAAWE2eIwk
'/*!*/;
# at 585
#180316 7:59:25 server id 1 end_log_pos 616 CRC32 0x1e67cfdb Xid = 27
COMMIT/*!*/;
# at 616
#180316 7:59:27 server id 1 end_log_pos 681 CRC32 0x00a58aee Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 681
#180316 7:59:27 server id 1 end_log_pos 753 CRC32 0x66eee5bc Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1521187167/*!*/;
BEGIN
/*!*/;
# at 753
#180316 7:59:27 server id 1 end_log_pos 800 CRC32 0xd52efbd8 Table_map: `test`.`a` mapped to number 116
# at 800
#180316 7:59:27 server id 1 end_log_pos 842 CRC32 0x4fb975d3 Write_rows: table id 116 flags: STMT_END_F
BINLOG '
X3mrWhMBAAAALwAAACADAAAAAHQAAAAAAAEABHRlc3QAAWEAAgMPAjIAAtj7LtU=
X3mrWh4BAAAAKgAAAEoDAAAAAHQAAAAAAAEAAgAC//wCAAAAAWLTdblP
'/*!*/;
# at 842
#180316 7:59:27 server id 1 end_log_pos 873 CRC32 0x8923c8b3 Xid = 28
COMMIT/*!*/;
# at 873
#180316 7:59:31 server id 1 end_log_pos 938 CRC32 0xf72f22fd Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 938
#180316 7:59:31 server id 1 end_log_pos 1010 CRC32 0x2502938f Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1521187171/*!*/;
BEGIN
/*!*/;
# at 1010
#180316 7:59:31 server id 1 end_log_pos 1057 CRC32 0xac40f9c0 Table_map: `test`.`a` mapped to number 116
# at 1057
#180316 7:59:31 server id 1 end_log_pos 1099 CRC32 0x8e0625b2 Write_rows: table id 116 flags: STMT_END_F
BINLOG '
Y3mrWhMBAAAALwAAACEEAAAAAHQAAAAAAAEABHRlc3QAAWEAAgMPAjIAAsD5QKw=
Y3mrWh4BAAAAKgAAAEsEAAAAAHQAAAAAAAEAAgAC//wDAAAAAWOyJQaO
'/*!*/;
# at 1099
#180316 7:59:31 server id 1 end_log_pos 1130 CRC32 0x05c5ab9e Xid = 29
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
mysql> SHOW BINLOG EVENTS;
# 也可用于查看二进制日志 数据
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 131 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 131 | 154 | |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000002';
# 查看指定 binlog 中的日志信息
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 3
Current database: test
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 131 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| mysql-bin.000002 | 123 | Previous_gtids | 131 | 154 | |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
mysql> SHOW MASTER STATUS;
#
查看当前日志的状态
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> SHOW MASTER LOGS; #
查看所有的日志文件(相当于查看索引文件)
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1496 |
| mysql-bin.000002 | 154 |
+------------------+-----------+
2 rows in set (0.00 sec)
mysql> RESET MASTER; #
清空所有的日志文件
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
binlog 有两大作用，一个是使用 binlog 恢复数据，另一个就是用来做主从复制。使用 binlog 日志只能回复一部分数据，要做数据库完全备份还是使用 mysqldump
mysql> reset master;
Query OK, 0 rows affected (0.33 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> create database mytest;
Query OK, 1 row affected (0.00 sec)
mysql> use mytest;
Database changed
mysql> create table t1(id int ,name varchar(20));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into t1 values (1,'xiaoming');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values (2,'xiaohong');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 values (3,'xiaozhu');
Query OK, 1 row affected (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 1309 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
mysql> select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | xiaoming |
| 2 | xiaohong |
+------+----------+
2 rows in set (0.00 sec)
mysql> delete from t1 where id > 1;
Query OK, 2 rows affected (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 1591 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
在进行其他的处理之前，我们建议，马上再执行一次 flush logs，也就是让出错的部分就集中在这么一个 binlog 日志文件中
mysql> flush logs; # 刷新日志文件，会产生一个新的日志文件
Query OK, 0 rows affected (0.01 sec)
mysql> show binlog events in 'mysql-bin.000001'; # binary log 会记录数据库的所有操作
+------------------+------+----------------+-----------+-------------+---------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+---------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.21-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000001 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 219 | Query | 1 | 319 | create database mytest |
| mysql-bin.000001 | 319 | Anonymous_Gtid | 1 | 384 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 384 | Query | 1 | 503 | use `mytest`; create table t1(id int ,name varchar(20)) |
| mysql-bin.000001 | 503 | Anonymous_Gtid | 1 | 568 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 568 | Query | 1 | 642 | BEGIN |
| mysql-bin.000001 | 642 | Table_map | 1 | 692 | table_id: 127 (mytest.t1) |
| mysql-bin.000001 | 692 | Write_rows
| mysql-bin.000001 | 741 | Xid | 1 | 772 | COMMIT
| mysql-bin.000001 | 772 | Anonymous_Gtid | 1 | 837 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 837 | Query | 1 | 911 | BEGIN |
| mysql-bin.000001 | 911 | Table_map | 1 | 961 | table_id: 127 (mytest.t1) |
| mysql-bin.000001 | 961 | Write_rows
| mysql-bin.000001 | 1010 | Xid | 1 | 1041 | COMMIT
| mysql-bin.000001 | 1041 | Anonymous_Gtid | 1 | 1106 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 1106 | Query | 1 | 1180 | BEGIN |
| mysql-bin.000001 | 1180 | Table_map | 1 | 1230 | table_id: 127 (mytest.t1) |
| mysql-bin.000001 | 1230 | Write_rows | 1 | 1278 | table_id: 127 flags: STMT_END_F |
| mysql-bin.000001 | 1278 | Xid | 1 | 1309 | COMMIT /* xid=158 */ |
| mysql-bin.000001 | 1309 | Anonymous_Gtid | 1 | 1374 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 1374 | Query | 1 | 1448 | BEGIN |
| mysql-bin.000001 | 1448 | Table_map | 1 | 1498 | table_id: 127 (mytest.t1) |
| mysql-bin.000001 | 1498 | Delete_rows
| mysql-bin.000001 | 1560 | Xid | 1 | 1591 | COMMIT
| mysql-bin.000001 | 1591 | Rotate | 1 | 1638 | mysql-bin.000002;pos=4 |
+------------------+------+----------------+-----------+-------------+---------------------------------------------------------+
27 rows in set (0.00 sec)
# 查看 binlog 数据文件
# mysqlbinlog --no-defaults mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4 # 这一整块就是一个事件
#180316 9:21:38 server id 1 end_log_pos 123 CRC32 0xdfcc68ee Start: binlog v 4, server v 5.7.21-log created 180316 9:21:38 at startup
ROLLBACK/*!*/;
BINLOG '
ooyrWg8BAAAAdwAAAHsAAAAAAAQANS43LjIxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACijKtaEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
Ae5ozN8=
'/*!*/;
# at 123
#180316 9:21:38 server id 1 end_log_pos 154 CRC32 0xf4a19726 Previous-GTIDs
# [empty]
# at 154
#180316 9:21:50 server id 1 end_log_pos 219 CRC32 0x8c09dbbd Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#180316 9:21:50 server id 1 end_log_pos 319 CRC32 0xcc7fd98a Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1521192110/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database mytest
/*!*/;
# at 319
#180316 9:22:01 server id 1 end_log_pos 384 CRC32 0xafc6fe6c Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 384
#180316 9:22:01 server id 1 end_log_pos 503 CRC32 0xcaa15d9d Query thread_id=3 exec_time=0 error_code=0
use `mytest`/*!*/;
SET TIMESTAMP=1521192121/*!*/;
create table t1(id int ,name varchar(20))
/*!*/;
# at 503
#180316 9:22:07 server id 1 end_log_pos 568 CRC32 0xde375e77 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 568
#180316 9:22:07 server id 1 end_log_pos 642 CRC32 0xd82d3bb6 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1521192127/*!*/;
BEGIN
/*!*/;
# at 642
#180316 9:22:07 server id 1 end_log_pos 692 CRC32 0xa45b6540 Table_map: `mytest`.`t1` mapped to number 127
# at 692
#180316 9:22:07 server id 1 end_log_pos 741 CRC32 0x049a0150 Write_rows: table id 127 flags: STMT_END_F
BINLOG '
v4yrWhMBAAAAMgAAALQCAAAAAH8AAAAAAAEABm15dGVzdAACdDEAAgMPAjwAA0BlW6Q=
v4yrWh4BAAAAMQAAAOUCAAAAAH8AAAAAAAEAAgAC//wBAAAACHhpYW9taW5nUAGaBA==
'/*!*/;
# at 741
#180316 9:22:07 server id 1 end_log_pos 772 CRC32 0x1f526b6b Xid = 156
COMMIT/*!*/;
# at 772
#180316 9:22:11 server id 1 end_log_pos 837 CRC32 0xe1b09d91 Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 837
#180316 9:22:11 server id 1 end_log_pos 911 CRC32 0x36c9e804 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1521192131/*!*/;
BEGIN
/*!*/;
# at 911
#180316 9:22:11 server id 1 end_log_pos 961 CRC32 0xf4d06711 Table_map: `mytest`.`t1` mapped to number 127
# at 961
#180316 9:22:11 server id 1 end_log_pos 1010 CRC32 0x7362e70d Write_rows: table id 127 flags: STMT_END_F
BINLOG '
w4yrWhMBAAAAMgAAAMEDAAAAAH8AAAAAAAEABm15dGVzdAACdDEAAgMPAjwAAxFn0PQ=
w4yrWh4BAAAAMQAAAPIDAAAAAH8AAAAAAAEAAgAC//wCAAAACHhpYW9ob25nDedicw==
'/*!*/;
# at 1010
#180316 9:22:11 server id 1 end_log_pos 1041 CRC32 0xaf67d489 Xid = 157
COMMIT /*!*/;
# at 1041
#180316 9:22:34 server id 1 end_log_pos 1106 CRC32 0x5c41f6e7 Anonymous_GTID last_committed=4 sequence_number=5 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1106
#180316 9:22:34 server id 1 end_log_pos 1180 CRC32 0x77f1a10b Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1521192154/*!*/;
BEGIN
/*!*/;
# at 1180
#180316 9:22:34 server id 1 end_log_pos 1230 CRC32 0x0647ed39 Table_map: `mytest`.`t1` mapped to number 127
# at 1230
#180316 9:22:34 server id 1 end_log_pos 1278 CRC32 0x708ca2dc Write_rows: table id 127 flags: STMT_END_F
BINLOG '
2oyrWhMBAAAAMgAAAM4EAAAAAH8AAAAAAAEABm15dGVzdAACdDEAAgMPAjwAAzntRwY=
2oyrWh4BAAAAMAAAAP4EAAAAAH8AAAAAAAEAAgAC//wDAAAAB3hpYW96aHXcooxw
'/*!*/;
# at 1278
#180316 9:22:34 server id 1 end_log_pos 1309 CRC32 0x24f1fd5c Xid = 158
COMMIT/*!*/;
# at 1309
#180316 9:23:24 server id 1 end_log_pos 1374 CRC32 0x58fa31ed Anonymous_GTID last_committed=5 sequence_number=6 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1374
#180316 9:23:24 server id 1 end_log_pos 1448 CRC32 0xea7c045e Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1521192204/*!*/;
BEGIN
/*!*/;
# at 1448
#180316 9:23:24 server id 1 end_log_pos 1498 CRC32 0x65bde18b Table_map: `mytest`.`t1` mapped to number 127
# at 1498
#180316 9:23:24 server id 1 end_log_pos 1560 CRC32 0x67eef7f8 Delete_rows: table id 127 flags: STMT_END_F
BINLOG '
DI2rWhMBAAAAMgAAANoFAAAAAH8AAAAAAAEABm15dGVzdAACdDEAAgMPAjwAA4vhvWU=
DI2rWiABAAAAPgAAABgGAAAAAH8AAAAAAAEAAgAC//wCAAAACHhpYW9ob25n/AMAAAAHeGlhb3po
dfj37mc=
'/*!*/;
# at 1560
#180316 9:23:24 server id 1 end_log_pos 1591 CRC32 0xe320d97b Xid = 161
COMMIT/*!*/;
# at 1591
#180316 9:26:32 server id 1 end_log_pos 1638 CRC32 0x2893b6fc Rotate to mysql-bin.000002 pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
如上蓝色标记所示，就是一个事件，事件由两部分构成 :
<1> 事件头
<2> 事件体
可以看到 Write_rows 这个操作是从 692 开始到 772 结束，以及从 961 开始到 1041 结束，而 Delete_rows 这个操作是从 1498 开始到 1591 结束，只要恢复到 1498 点之前数据就会恢复
使用 binlog 日志恢复数据，语法格式 : mysqlbinlog mysql-bin.0000xx | mysql -u 用户名 -p 密码 数据库名
$ cd /var/lib/mysql
$ mysqlbinlog --no-defaults mysql-bin.000001 --start-position 692 --stop-position 1041 | mysql -uroot -p
# 在 mysql 中就会发现数据被恢复过来
mysql> select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | xiaoming |
| 2 | xiaohong |
+------+----------+
2 rows in set (0.00 sec)
# 上面是进行指定范围操作，如果想一次恢复所有被删除的数据就不需要加额外参数
# mysqlbinlog mysql-bin.000001 | mysql -uroot -p # 该操作将会恢复所有误删除的数据
Enter password:
--start-datetime : 从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间
--stop-datetime : 从二进制日志中读取指定小于时间戳或者等于本地计算机的时间 取值和上述一样
--start-position : 从二进制日志中读取指定 position 事件位置作为开始
--stop-position : 从二进制日志中读取指定 position 事件位置作为事件截至
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
