Binary logs contain all statements that update data or have potentially updated data (for example, no DELETE matching any row ).
Binary logs contain all statements that update data or have potentially updated data (for example, no DELETE matching any row ).
Basic Concepts
Definition:
The binary log contains all statements that update data or have potentially updated data (for example, no DELETE matching any row.
Purpose:
1. The main purpose of the binary log is to restore the database so that the database can be updated as much as possible, because the binary log contains all updates made after the backup.
2. Binary logs are also used to record all statements that will be sent to the slave server on the master replication server.
Adverse effects:
If binary logs are enabled when the server is running, the performance is about 1% slower.
How to start:
You can enable the-log-bin = file option.
(Change the my. ini file)
Log location
> If no file name is specified, Mysql uses the hostname-binfile.
> If a relative path is specified, it is assumed that the path is relative to the data directory.
> Mysql adds a digital index after the file name. Therefore, the final format 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 removed and ignored.
Change Policy:
Use indexes to loop files. The following conditions will be used to loop to the next index.
1. Server restart
2. Server Updated
3. The maximum log length is max_binlog_size.
4. Log refreshed mysql> flush logs;
Tool introduction:
Shell> mysqlbinlog [option] binlogFile> newfile
For example, D: \ mysql \ log> mysqlbinlog binlog.000001> 1.txt
Example:
Log-bin = "D:/mysql/log/binlog", the file D:/mysql/log/binlog.000001 will appear in this folder.
FAQs
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 is not cleared
Example:
Mysql> purge master logs to 'binlog. 100 ′;
Query OK, 0 rows affected (0.01 sec)
Mysql> purge master logs before '2017-09-22 00:00:00 ′;
Query OK, 0 rows affected (0.05 sec)
>>> Or use a command
RESET MASTER
Delete all previous binlogs and generate new binlogs.
The suffix starts from 000001.
Note: If you have an active slave server that is currently reading one of the logs you are trying to delete,
This statement does not work, but fails with an error.
However, if the slave server is slave and you happen to have cleared one of the logs it wants to read, the slave server cannot be copied after it is started.
This statement can be run safely when the slave server is being copied. You do not need to stop them.
2. Record the configuration of binary logs
Binlog-do-db = sales only records the sales database
Binlog-ignore-db = except for the sales database, no records are recorded, and all other records are recorded.
However, if you do not use $ dbname before operating the database, all SQL statements will not be recorded.
If use $ dbname is used, the Judgment Rule depends on $ dbname, instead of the database operated in SQL.
3. Inaccurate processing of binary logs
By default, binary logs are not synchronized with the hard disk each time they are written. Therefore, if the operating system or machine (not just the MySQL server) crashes, the last statement in the binary log may be lost.
To prevent this situation, you can use the sync_binlog global variable (1 is the safest value, but also the slowest) to synchronize binary logs with the hard disk after each N binary logs are written.
Even if sync_binlog is set to 1, the table content and binary log content may be inconsistent in the event of a crash.
If the MySQL server finds that the binary log is shortened when the crash is restored (that is, at least one InnoDB Transaction successfully committed is missing ),
If sync_binlog = 1 and the hard disk/file system can be synchronized as needed (some do not need it), the error message ("binary log <名> Smaller than expected ").
In this case, the binary log is inaccurate and the replication should begin with the data snapshot of the master server.
