MySQL binary log (BinaryLog)

Like most relational databases, log files are an important part of MySQL databases. MySQL has several different log files, usually including error log files and binary files.

Like most relational databases, log files are an important part of MySQL databases. MySQL has several different log files, usually including error log files and binary files.

Like most relational databases, log files are an important part of MySQL databases. MySQL has several different log files, such as error log files, binary logs, common logs, and slow query logs. These logs help us locate the internal events of mysqld, database performance faults, record the change history of data, and recover the database. Binary log, also known as binary log, is one of the most important logs in MySQL Server. This document describes binary logs.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

1. Composition of the MySQL Log File System
A. Error Log: records the problems that occur when mysqld is started, running, or stopped.
B. General logs: records established client connections and executed statements.
C. Update logs: statements used to record and change data. This log is no longer used in MySQL 5.1.
D. binary log: records all statements for changing data. It is also used for replication.
E. Slow query log: records all queries whose execution time exceeds long_query_time seconds or where no index is used.
F. Innodb log: 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 by refreshing the log (or switch to a new log in some cases ).
When you execute a flush logs statement or mysqladmin flush-logs or mysqladmin refresh, the log is aging.
In the case of MySQL replication, the slave replication server maintains more log files, which are called replacement logs.

2. Binary log)
A. Its content and functions are as follows:
Contains all updated data or potentially updated data (for example, a DELETE that does not match any row)
Contains execution time information about each statement that updates the database (DML ).
Does not contain statements that do not modify any data. To enable this option, you must enable the common log function.
The main purpose is to restore the database to the database fault point as much as possible, because the binary log contains all updates made after the backup.
Used to record all statements that will be sent to the slave server on the master Replication Server
Enabling this option reduces database performance by 1%, but ensures database integrity and improves the performance of important database values. Some are similar to Oracle's archive mode.

B. Methods and attributes for enabling binary logs
When you use the -- log-bin [= file_name] option or specify log-bin in the configuration file to start, mysqld writes a log file containing all the SQL commands for updating data.
If the file_name value is not given, the default name is the host name followed by-bin.
If no absolute path is specified, it is stored in the data directory by default.
A digital extension is added to each binary log name for log aging. Therefore, custom extensions are not supported and will be dynamically replaced by the mysql digital extension.
If the current log size reaches max_binlog_size, a new binary log is automatically created.
For large transactions, binary logs exceed the value set by max_binlog_size. That is, the transaction only writes a binary log.
We can see that the size of the binary log file is close to that of the binary log file, and its size is not completely equal, which is different from that of oracle.
A binary log file has a binary log index file that contains all binary logs. The file name is the same as that of binary logs. The extension is. index.
The binary index file is specified using the -- log-bin-index [= file_name] Option.
The reset master statement will delete all binary log files, which will affect the slave database. You can also use purge master logs to delete only some binary files.

3. binary log demonstration

A. Enable binary log
-- Current Environment
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 | -- this parameter is used to set whether to enable binary logs.
| Log_bin_trust_function_creators | OFF |
| SQL _log_bin | ON |
+ --------------------------------- + ------- +