同大多数关系型数据库一样，日志文件是MySQL数据库的重要组成部分。MySQL有几种不同的日志文件，通常包括错误日志文件，二进制日志，通用日志，慢查询日志，等等。这些日志可以帮助我们定位mysqld内部发生的事件，数据库性能故障，记录数据的变更历史，用户恢复数据库等等。二进制日志，也叫binary log，是MySQL Server中最为重要的日志之一，本文主要描述二进制日志。
1、MySQL日志文件系统的组成
2、二进制日志(Binary log)
3、二进制日志相关演示
a、启用二进制日志
--当前环境
root@localhost[(none)]> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.5.39 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.5.39 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+------------------------------+
root@localhost[(none)]> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF | --该参数用于设定是否启用二进制日志
| log_bin_trust_function_creators | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
--以下为binary log相关参数
root@localhost[(none)]> show variables like '%binlog%';
+-----------------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------+
| binlog_cache_size | 32768 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | STATEMENT |
| binlog_stmt_cache_size | 32768 |
| innodb_locks_unsafe_for_binlog | OFF |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| sync_binlog | 0 |
+-----------------------------------------+----------------------+
--当前mysql服务器数据文件的缺省位置
root@localhost[(none)]> show variables like '%datadir%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| datadir | /var/lib/mysql/ |
+---------------+-----------------+
--停止mysql服务器
suse11b:~ # service mysql stop
Shutting down MySQL.... done
--编辑my.cnf来设定binary log日志位置(注，配置二进制日志路径及文件名后，系统变量log_bin被自动置为on)
suse11b:~ # vi /etc/my.cnf
suse11b:~ # grep -v ^# /etc/my.cnf
[mysqld]
log-error=/tmp/suse11b.err
log_bin=/var/lib/mysql/binarylog/binlog
suse11b:~ # mkdir -p /var/lib/mysql/binarylog
suse11b:~ # chown -R mysql:mysql /var/lib/mysql/binarylog
suse11b:~ # /etc/init.d/mysql start
Starting MySQL.. done
suse11b:~ # ls -hltr /var/lib/mysql/binarylog/*
-rw-rw---- 1 mysql mysql 39 Oct 3 13:41 /var/lib/mysql/binarylog/binlog.index #索引文件
-rw-rw---- 1 mysql mysql 107 Oct 3 13:41 /var/lib/mysql/binarylog/binlog.000001 #日志文件
b、切换日志
suse11b:~ # mysql -uroot -pxxx
root@localhost[(none)]> flush logs;
Query OK, 0 rows affected (0.04 sec)
root@localhost[(none)]> system ls -hltr /var/lib/mysql/binarylog/*
-rw-rw---- 1 mysql mysql 78 Oct 3 13:43 /var/lib/mysql/binarylog/binlog.index
-rw-rw---- 1 mysql mysql 107 Oct 3 13:43 /var/lib/mysql/binarylog/binlog.000002 #切换后产生了000002
-rw-rw---- 1 mysql mysql 147 Oct 3 13:43 /var/lib/mysql/binarylog/binlog.000001
root@localhost[(none)]> system mysqladmin flush-logs #使用mysqladmin命令行工具flush-logs方式切换日志
root@localhost[(none)]> system ls -hltr /var/lib/mysql/binarylog/*
-rw-rw---- 1 mysql mysql 147 Oct 3 13:43 /var/lib/mysql/binarylog/binlog.000001
-rw-rw---- 1 mysql mysql 117 Oct 3 13:45 /var/lib/mysql/binarylog/binlog.index
-rw-rw---- 1 mysql mysql 107 Oct 3 13:45 /var/lib/mysql/binarylog/binlog.000003 #切换后产生了000003
-rw-rw---- 1 mysql mysql 147 Oct 3 13:45 /var/lib/mysql/binarylog/binlog.000002
root@localhost[(none)]> system mysqladmin refresh #使用mysqladmin命令行工具refresh方式切换日志
root@localhost[(none)]> system ls -hltr /var/lib/mysql/binarylog/*
-rw-rw---- 1 mysql mysql 147 Oct 3 13:43 /var/lib/mysql/binarylog/binlog.000001
-rw-rw---- 1 mysql mysql 147 Oct 3 13:45 /var/lib/mysql/binarylog/binlog.000002
-rw-rw---- 1 mysql mysql 156 Oct 3 13:46 /var/lib/mysql/binarylog/binlog.index
-rw-rw---- 1 mysql mysql 107 Oct 3 13:46 /var/lib/mysql/binarylog/binlog.000004 #切换后产生了000004
-rw-rw---- 1 mysql mysql 147 Oct 3 13:46 /var/lib/mysql/binarylog/binlog.000003
c、模拟产生二进制日志及查看内容
root@localhost[(none)]> create database tempdb;
Query OK, 1 row affected (0.00 sec)
root@localhost[(none)]> use tempdb
Database changed
root@localhost[tempdb]> create table tb1(id smallint,val varchar(10));
Query OK, 0 rows affected (0.00 sec)
root@localhost[tempdb]> insert into tb1 values(1,'jack');
Query OK, 1 row affected (0.01 sec)
root@localhost[tempdb]> system strings /var/lib/mysql/binarylog/binlog.000004
bin?8.T
5.5.39-log
z=.T
tempdb
create database tempdb
tempdb
create table tb1(id smallint,val varchar(10))
tempdb
BEGIN
tempdb
insert into tb1 values(1,'jack')
root@localhost[tempdb]> system more /var/lib/mysql/binarylog/binlog.index
/var/lib/mysql/binarylog/binlog.000001
/var/lib/mysql/binarylog/binlog.000002
/var/lib/mysql/binarylog/binlog.000003
/var/lib/mysql/binarylog/binlog.000004
--使用命令行工具mysqlbinlog直接提取二进制日志的内容
root@localhost[tempdb]> system mysqlbinlog /var/lib/mysql/binarylog/binlog.000004
/*!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 4
#141003 13:46:39 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.39-log created 141003 13:46:39
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
PzguVA8BAAAAZwAAAGsAAAABAAQANS41LjM5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#141003 14:08:58 server id 1 end_log_pos 194 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1412316538/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
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=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database tempdb
/*!*/;
# at 194
#141003 14:09:36 server id 1 end_log_pos 304 Query thread_id=1 exec_time=0 error_code=0
use `tempdb`/*!*/;
SET TIMESTAMP=1412316576/*!*/;
create table tb1(id smallint,val varchar(10))
/*!*/;
# at 304
#141003 14:09:56 server id 1 end_log_pos 374 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1412316596/*!*/;
BEGIN
/*!*/;
# at 374
#141003 14:09:56 server id 1 end_log_pos 471 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1412316596/*!*/;
insert into tb1 values(1,'jack')
/*!*/;
# at 471
#141003 14:09:56 server id 1 end_log_pos 498 Xid = 25
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
--从以上的内容可以看出二进制日志记录了所有操作的DML语句及其开销，以及一些系统环境变量的信息。
d、管理二进制日志
--对于二进制日志，应尽可能保存在安全的位置，与数据分开存储
--使用show binary logs获取二进制日志相关信息
root@localhost[(none)]> help show binary logs;
Name: 'SHOW BINARY LOGS'
Description:
Syntax:
SHOW BINARY LOGS
SHOW MASTER LOGS
Lists the binary log files on the server. This statement is used as
part of the procedure described in [HELP PURGE BINARY LOGS], that shows
how to determine which logs can be purged.
root@localhost[tempdb]> show binary logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000001 | 147 |
| binlog.000002 | 147 |
| binlog.000003 | 147 |
| binlog.000004 | 498 |
+---------------+-----------+
show binlog events用于在二进制日志中显示事件。如果未指定'log_name'，则显示第一个二进制日志。
root@localhost[(none)]> help show binlog events; --获取帮助信息
Name: 'SHOW BINLOG EVENTS'
Description:
Syntax:
SHOW BINLOG EVENTS
[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
Shows the events in the binary log. If you do not specify 'log_name',
the first binary log is displayed.
root@localhost[(none)]> show binlog events;
+---------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+-------------+-----------+-------------+---------------------------------------+
| binlog.000001 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.39-log, Binlog ver: 4 |
| binlog.000001 | 107 | Rotate | 1 | 147 | binlog.000002;pos=4 |
+---------------+-----+-------------+-----------+-------------+---------------------------------------+
root@localhost[(none)]> show binlog events in 'binlog.000005'; --binlog.000005不存在，所以报错
ERROR 1220 (HY000): Error when executing command SHOW BINLOG EVENTS: Could not find target log
--下面的这个查询中，前面执行的DML在这里均可以看到
root@localhost[tempdb]> show binlog events in 'binlog.000004';
+---------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+
| binlog.000004 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.39-log, Binlog ver: 4 |
| binlog.000004 | 107 | Query | 1 | 194 | create database tempdb |
| binlog.000004 | 194 | Query | 1 | 304 | use `tempdb`; create table tb1(id smallint,val varchar(10)) |
| binlog.000004 | 304 | Query | 1 | 374 | BEGIN |
| binlog.000004 | 374 | Query | 1 | 471 | use `tempdb`; insert into tb1 values(1,'jack') |
| binlog.000004 | 471 | Xid | 1 | 498 | COMMIT /* xid=25 */ |
+---------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+
root@localhost[tempdb]> show binlog events in 'binlog.000004' from 374;
+---------------+-----+------------+-----------+-------------+------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+------------+-----------+-------------+------------------------------------------------+
| binlog.000004 | 374 | Query | 1 | 471 | use `tempdb`; insert into tb1 values(1,'jack') |
| binlog.000004 | 471 | Xid | 1 | 498 | COMMIT /* xid=25 */ |
+---------------+-----+------------+-----------+-------------+------------------------------------------------+
root@localhost[tempdb]> show binlog events in 'binlog.000004' from 374 limit 1;
+---------------+-----+------------+-----------+-------------+------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+------------+-----------+-------------+------------------------------------------------+
| binlog.000004 | 374 | Query | 1 | 471 | use `tempdb`; insert into tb1 values(1,'jack') |
+---------------+-----+------------+-----------+-------------+------------------------------------------------+
d、删除历史日志
--使用purge手动删除指定日志
--使用expire-log-days删除失效日志，设置变量expire_logs_days，删除超出这个变量保留期之前的所有日志被删除
--自动日志删除通常发生在服务器启动以及日志flush
--reset master方式
root@localhost[(none)]> help purge;
Name: 'PURGE BINARY LOGS'
Description:
Syntax:
PURGE { BINARY | MASTER } LOGS
{ TO 'log_name' | BEFORE datetime_expr }
Examples:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';
root@localhost[tempdb]> purge binary logs to 'binlog.000003';
Query OK, 0 rows affected (0.12 sec)
root@localhost[tempdb]> show binary logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000003 | 147 |
| binlog.000004 | 498 |
+---------------+-----------+
root@localhost[tempdb]> system ls -hltr /var/lib/mysql/binarylog/*
-rw-rw---- 1 mysql mysql 147 Oct 3 13:46 /var/lib/mysql/binarylog/binlog.000003
-rw-rw---- 1 mysql mysql 498 Oct 3 14:09 /var/lib/mysql/binarylog/binlog.000004
-rw-rw---- 1 mysql mysql 78 Oct 3 14:23 /var/lib/mysql/binarylog/binlog.index
--使用before子句purge日志，binlog.000003被删除
root@localhost[tempdb]> purge binary logs before '2014-10-03 14:09:56';
Query OK, 0 rows affected (0.02 sec)
root@localhost[tempdb]> show binary logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000004 | 498 |
+---------------+-----------+
--Author: Leshami
--Blog : http://blog.csdn.net/leshami
--重置所有日志
--reset master将删除在索引文件中列出所有的日志文件并重置索引文件，最后生成一个新的binlog文件。
--该操作之前先备份binlog至其它位置以备以后需要。
root@localhost[tempdb]> help reset master;
Name: 'RESET MASTER'
Description:
Syntax:
RESET MASTER
Deletes all binary log files listed in the index file, resets the
binary log index file to be empty, and creates a new binary log file.
This statement is intended to be used only when the master is started
for the first time.
root@localhost[tempdb]> reset master;
Query OK, 0 rows affected (0.13 sec)
root@localhost[tempdb]> show binary logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000001 | 107 | --reset之后，从000001开始生成全新空日志
+---------------+-----------+
--expire_log系统变量控制二进制日志自动删除的天数。默认值为0,表示“没有自动删除”。启动时和二进制日志循环时可能删除。
root@localhost[tempdb]> show variables like 'expire_log%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 0 |
+------------------+-------+
root@localhost[tempdb]> set expire_logs_days=7; --提示次系统变量为全局变量
ERROR 1229 (HY000): Variable 'expire_logs_days' is a GLOBAL variable and should be set with SET GLOBAL
root@localhost[tempdb]> set global expire_logs_days=7; --设置
Query OK, 0 rows affected (0.01 sec)
root@localhost[tempdb]> select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
| 7 |
+--------------------+
Der Hauptgrund, warum Computer Binärdateien verwenden
Binäre Darstellung negativer Zahlen
Wie man negative Zahlen binär darstellt
binäre Arithmetik
So stellen Sie aus dem Papierkorb geleerte Dateien wieder her
Was ist der Befehl zum Löschen einer Spalte in SQL?
Was bedeutet Ping?
Was bedeutet Webserver?
![[Web-Frontend] Node.js-Schnellstart](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
