MySQL binlog binary Log detailed

Defined:
The binary log contains all the statements that have updated the data or have potentially updated the data (for example, a delete that does not match any of the rows).

Role:
1. The primary purpose of the binary log is to enable the database to be updated most likely as a result of the recovery, because the binary log contains all the updates that were made after the backup.
2. The binary log is also used to record all statements that will be sent to the server from the primary replication server.
Adverse effects:
When running the server, the performance is approximately 1% slower if binary logging is enabled.

How to start:
With the –log-bin=file option, you can enable
(Change My.ini file)
Log location
>> if no filename is specified, MySQL uses the Hostname-bin file.
>> If a relative path is specified, the path is assumed to be relative to the data directory
>>mysql added a numeric index after the filename. So the final form of the file is Filename.number
If you provide an extension (for example, –log-bin=file_name.extension) in the log name, the extension is silently removed and ignored.

Replacement policy:
Use the index to loop the file, and the following conditions will loop to the next index
1. Server reboot
2. Server is updated
3. Log has reached maximum log length max_binlog_size
4. The log is refreshed mysql> flush logs;

Tools Introduction:
shell>>mysqlbinlog [option] binlogfile> NewFile
such as: D:mysqllog>mysqlbinlog binlog.000001 > 1.txt
An example:
log-bin= "D:/mysql/log/binlog" then, in this folder there will be files d:/mysql/log/binlog.000001 and so on

Problems
1. How to clear Binlog
>>> Use the following two commands
PURGE {MASTER | BINARY} LOGS to ' Log_name '//log_name will not be cleared
PURGE {MASTER | BINARY} LOGS before ' date '//date will not be cleared

Examples are as follows:
Mysql> purge master logs to ' binlog.000004′;
Query OK, 0 rows affected (0.01 sec)

Mysql> purge master logs before ' 2009-09-22 00:00:00′;
Query OK, 0 rows affected (0.05 sec)

>>> or use command
RESET MASTER

Remove all previous binlog and regenerate the new Binlog
Suffix starting from 000001

Note: If you have an active secondary server, the server is currently reading one of the logs that you are trying to delete.
This statement will not work, but will fail, along with an error.
However, if the secondary server is inactive and you happen to clean up one of the logs that it wants to read, the secondary server cannot replicate after it is started.
This statement is safe to run when the secondary server is replicating. You do not need to stop them.

2. The content configuration that records to the binary log knows
Binlog-do-db=sales Only Records Sales library
Binlog-ignore-db=sales except the sales library is not recorded, others are recorded

However, if the use $dbname is not used until the database is manipulated, then all SQL will not be logged
If use $dbname is used, the decision rule depends on the $dbname here rather than on the library of operations in SQL

3. Binary log inaccurate processing
By default, the binary logs are not synchronized with the hard disk every time you write. So if the operating system or machine (not just the MySQL server) crashes, it is possible that the last statement in the binary log is lost.
To prevent this, you can use the Sync_binlog global variable (1 is the safest value, but also the slowest) so that the binary log synchronizes with the hard disk after every n binary log write.
Even if the sync_binlog is set to 1, there may be inconsistencies between the table content and the binary log content when a crash occurs.

If the MySQL server discovers that the binary log is shortened (that is, at least a successfully committed INNODB transaction) when the crash is restored,
If Sync_binlog = 1 and the hard disk/file system does not occur as needed (some unwanted), output the error message ("Binary log < name > smaller than expected").
In this case, the binary log is inaccurate and replication should start with a snapshot of the primary server's data.