MySQL binary logs (binary log)

Like most relational databases, log files are an important part of the MySQL database. MySQL has several different log files, usually including error log files, binary logs, generic logs, slow query logs, and so on. These logs can help us locate events inside mysqld, database performance failures, record data change histories, user recovery databases, and more. Binary logs, also known as binary log, are one of the most important logs in MySQL server, and this article mainly describes binary logs.

1, the MySQL log file system composition
A, error log: Records the issue that occurs when you start, run, or stop mysqld.
B. General log: Records the established client connections and executed statements.
C, update log: The statement that records the change data. The log is no longer used in MySQL 5.1.
D, binary log: A statement that records all changes to the data. Also used for replication.
E, slow query log: Records all queries that have been executed for more than long_query_time seconds, or queries that do not use indexes.
F, InnoDB logs: InnoDB redo Log

By default, all logs are created in the MYSQLD data directory.
You can force mysqld to close and reopen the log file (or, in some cases, switch to a new log) by refreshing the log.
When you execute a FLUSH logs statement or perform mysqladmin flush-logs or mysqladmin refresh, the log is aged.
In the case of MySQL replication, more log files will be maintained from the replication server, known as the replacement log.

2 binary logs (binary log)
A, it contains the content and function as follows:
Contains all data that has been updated or has been potentially updated (such as a delete without matching any rows)
Contains execution time information about each update database (DML) statement
Does not contain statements that do not have any data modified, and if you need to enable this option, you need to turn on the Universal logging feature
The primary purpose is to restore the database to a database failure point as much as possible because the binary log contains all the updates made after the backup
Used to record all statements that will be sent to the slave server on the primary replication server
Enable this option database performance is reduced by 1%, but the database integrity is guaranteed, and the performance of the important database is worth the complete change. Some are similar to Oracle Open archive mode.

B. Methods and properties for opening binary logs
When you use the--log-bin[=file_name] option or specify Log-bin startup in the configuration file, Mysqld writes the log file of the SQL command that contains all the updated data.
For file_name values not given, the default name is the hostname followed by-bin.
In the case where absolute paths are not specified, the default location is saved in the data directory.
Each binary log name adds a numeric extension for log aging, so custom extensions are not supported and are dynamically replaced by MySQL numeric extensions.
If the current log size reaches Max_binlog_size, a new binary log is created automatically.
For large transactions, the binary log will exceed the value set by Max_binlog_size. That is, the transaction simply writes a binary log.
It is known that the binary log file size is close and its size is not exactly equal, which is different from Oracle.
The binary log file will have a binary log index file that contains all the binary logs with the same file name as the binary log with an. index extension.
Binary index file with the--log-bin-index[=file_name] option to specify
The RESET Master statement will delete all binary log files, which will affect the slave library. You can also use purge MASTER logs to delete only part of a binary file.

3. Binary Log related Demo

A, enable the binary log--current Environment [email protected][(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 |+-------------------------+------------------------------+[email protected][(none)]> Show variables like '%log_bin% '; +---------------------------------+-------+| variable_name | Value |+---------------------------------+-------+| Log_bin | OFF | --This parameter is used to set whether binary logging is enabled | Log_bin_trust_function_creators | OFF | | Sql_log_bin | On |+---------------------------------+-------+--The following binary log related parameters [email protected][(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 |+-----------------------------------------+----------------------+--The default location for the current MySQL server data file [email protected][(none)]> Show Variables like '%datadir% '; +---------------+-----------------+| variable_name | Value |+---------------+-----------------+| DataDir |                                               /var/lib/mysql/|+---------------+-----------------+--stop MySQL server suse11b:~ # service MySQL stopshutting down MySQL ....  done--edit my.cnf to set binary log log location (note, after configuring the binary log path and file name, the system variable Log_bin is automatically placed on) suse11b:~ # vi/etc/my.cnfsuse11b:~ # grep-v ^#/etc/my.cnf[mysqld]log-error=/tmp/suse11b.errlog_bin=/var/lib/mysql/binarylog/ binlogsuse11b:~ # mkdir-p/var/lib/mysql/binarylogsuse11b:~ # chown-r mysql:mysql/var/lib/mysql/binarylogsuse11b:~ #/                                                      Etc/init.d/mysql startstarting MySQL. donesuse11b:~ # LS-HLTR/VAR/LIB/MYSQL/BINARYLOG/*-RW-RW----1 mysql mysql 3 Oct 13:41/var/lib/mysql/binarylog/binl Og.index #索引文件-rw-rw----1 mysql mysql 107 OCT 3 13:41/var/lib/mysql/binarylog/binlog.000001 #日志文件b, toggle log suse11b:~ # mysql-uroot-pxxx[email protected][( None)]> flush logs; Query OK, 0 rows affected (0.04 sec) [email protected][(none)]> system ls-hltr/var/lib/mysql/binarylog/*- RW-RW----1 mysql mysql Oct 3 13:43/var/lib/mysql/binarylog/binlog.index-rw-rw----1 mysql mysql 107 Oct 3 13:43/v ar/lib/mysql/binarylog/binlog.000002 #切换后产生了000002-rw-rw----1 mysql mysql 147 Oct 3 13:43/var/lib/mysql/binarylog/bin log.000001[email protected][(None)]> system mysqladmin flush-logs #使用mysqladmin命令行工具flush-logs mode switch log [email  protected][(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:4 5/var/lib/mysql/binarylog/binlog.000002[email protected][(None)]> system mysqladmin Refresh #使用mysqladmin命令行工具refresh方式切换日志 [ email protected][(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 O CT 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, simulating the generation of binary logs and viewing content [email protected][(none)]> CREATE database tempdb; Query OK, 1 row Affected (0.00 sec) [email protected][(none)]> use Tempdbdatabase changed[email protected][ Tempdb]> CREATE TABLE tb1 (ID smallint,val varchar (10)); Query OK, 0 rows Affected (0.00 sec) [email protected][tempdb]> INSERT into TB1 values (1, ' Jack '); Query OK, 1 row affected (0.01 sec) [email protected][tempdb]> system strings/var/lib/mysql/binarylog/binlog.000004bin?8.t5.5.39-logz=. Ttempdbcreate Database tempdbtempdbcreate table tb1 (ID smallint,val varchar) tempdbbegintempdbinsert into TB1 values (1, ' Jack ') [Email protected] [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--using the command line tool Mysqlbinlog directly extracts the contents of the binary log [email protected][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 @[email protected]@ completion_type,completion_type=0*/;D elimiter/*!*/;# 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 ' Pzguva8baaaazwaaagsaaaabaaqAns41ljm5lwxvzwaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaezgnaagaegaebaqeegaavaaegggaaaaicagcaa = = '/*!*/;# at 107#141003 14:08:58 server ID 1 end_log_pos 194 Query thread_id=1 exec_time=0 error_code=0set T imestamp=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_p OS 304 Query thread_id=1 exec_time=0 error_code=0use ' tempdb '/*!*/; SET timestamp=1412316576/*!*/;create table tb1 (ID smallint,val varchar)/*!*/;# at 304#141003 14:09:56 Server ID 1 end_log_pos 374 Query thread_id=1 exec_time=0 error_code=0set 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=0set Timestamp=1412316596/*!*/;insert into TB1 values (1, ' Jack ')/*!*/;# at 471#141003 14:09:56 Server ID 1 end_log_pos 498 X id = 25commit/*!*/;D elimiter; # End of log filerollback/* Added by Mysqlbinlog */;/*!50003 SET [email protected]_com pletion_type*/;/*!50530 SET @ @SESSION. pseudo_slave_mode=0*/;--from the above, we can see that the binary log records the DML statements of all operations and their overhead, as well as information about some system environment variables. D. Manage binary logs-for binary logs, keep in a secure location as far as possible, store separately from the data-use show binary logs to get binary log-related information [email protected][(none)]> help show binary logs; Name: ' SHOW binary LOGS ' Description:Syntax:SHOW binary logsshow MASTER logslists the binary log files on the server. This statement was used aspart of the procedure described in [Help PURGE BINARY LOGS], which showshow to determine which log s can be purged. [Email protected] [tempdb]> Show binary logs;+---------------+-----------+| Log_name | File_size |+---------------+-----------+|       binlog.000001 | 147 | |       binlog.000002 | 147 | |       binlog.000003 | 147 | |       binlog.000004 | 498 |+---------------+-----------+show binlog events are used to display the event in the binary log. If ' Log_name ' is not specified, the first binary log is displayed. [Email protected]  [(None)]> help show Binlog events; --Get Help information 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 don't specify ' Log_name ', the first binary log is displayed. [Email protected] [(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 |+---------------+-----+-------------+-----------+-------------+--------------------  -------------------+[email protected][(none)]> show Binlog events in ' binlog.000005 '; --binlog.000005 does not exist, so error error 1220 (HY000): Error when executing command SHOW binlog events:could not find target log--below In this query, the previously executed DML can be seen here [email protected][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 */|+---------------+-----+-------------+-----------+------------ -+-------------------------------------------------------------+[email protected][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 */|+---------------+-----+------------+-----------+-------------+------------ ------------------------------------+[email protected][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, delete the history log--use purge to manually delete the specified log--use expire-log-days to delete the invalidation log, set the variable expire_logs_days, Deleting all logs before this variable retention period is removed-automatic log deletion usually occurs on server startup as well as log flush--reset master mode [email protected][(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 '; [Email protected] [tempdb]> purge binary logs to ' binlog.000003 '; Query OK, 0 rows affected (0.12 sec) [email protected][tempdb]> Show binary logs;+---------------+-----------+| Log_name | File_size |+---------------+-----------+|       binlog.000003 | 147 | |       binlog.000004 | 498 |+---------------+-----------+[email protected][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 Oct 3 14:23/var/lib/mysql/binarylog/binl og.index--using before clause purge log, binlog.000003 was deleted [email protected][tempdb]> purge binary logs before ' 2014-10-03 14:09:56 '; Query OK, 0 rows affected (0.02 sec) [email protected][tempdb]> Show binary logs;+---------------+-----------+| Log_name | File_size |+---------------+-----------+|       binlog.000004 | 498 |+---------------+-----------+--author:leshami--blog:http://blog.csdn.net/leshami--Reset all logs--reset Master deletes all the log files listed in the index file and resets the index files, and finally generates a new Binlog file. --The operation is backed up Binlog to other locations for later use. [Email protected] [tempdb]> help reset Master;  Name: ' RESET MASTER ' Description:Syntax:RESET masterdeletes all binary log files listed in the ' index file, resets thebinary Log index file to is empty, and creates a new binary log file. This StatemENT is intended to being used only if the master is startedfor the first time. [Email protected] [tempdb]> Reset Master; Query OK, 0 rows affected (0.13 sec) [email protected][tempdb]> Show binary logs;+---------------+-----------+| Log_name | File_size |+---------------+-----------+|       binlog.000001 |  107 | After--reset, a new empty log +---------------+-----------+--expire_log system variable is generated from 000001 to control the number of days the binary log is automatically deleted. The default value is 0, which means "no automatic deletion". May be deleted at startup and binary log loops. [Email protected] [tempdb]> Show variables like ' expire_log% '; +------------------+-------+| variable_name | Value |+------------------+-------+| Expire_logs_days |   0 |+------------------+-------+[email protected][tempdb]> set expire_logs_days=7; --hint that the secondary system variable is a global variable error 1229 (HY000): Variable ' Expire_logs_days ' is a global Variable and should are set with set Global[email   protected][tempdb]> set global expire_logs_days=7; --Set query OK, 0 rows affected (0.01 sec) [email protected][tempdb]> SELECT @ @expire_logs_days;+--------------------+|                  @ @expire_logs_days |+--------------------+| 7 |+--------------------+

MySQL binary logs (binary log)