mysql binlog binary log details
- 2020-05-12 06:19:13
- OfStack
The basic concept
Definition:
The base 2 log contains all statements that have updated or potentially updated data (for example, one DELETE that does not match any row).
Function:
1. The primary purpose of binary logging is to make it possible to update the database as much as possible after recovery, since the binary log contains all updates made after the backup.
2. The hexadecimal log is also used on the master replication server to record all statements that will be sent to the slave server.
Adverse effects:
When running the server with binary logging enabled, performance is approximately 1% slower.
How to start:
Enabled through the wok with the option log-bin =file
(change my.ini file)
Log position
> > If no file name is specified, Mysql USES the hostname-bin file.
> > If a relative path is specified, it is assumed that the path is relative to the data directory
> > Mysql adds a numeric index after the file name, so the final form of the file is filename.number
If you provide an extension in the name of the log (for example, if you provide an extension in the name of the log (e.g.
Replacement strategy:
Use the index to loop through the file, and loop to the next index under the following conditions
1. Server restart
2. The server is updated
3. The log reached the maximum log length max_binlog_size
4. The log is refreshed mysql > flush logs;
Tool introduction:
shell > > mysqlbinlog [option] binlogFile > newfile
Such as: D: \ mysql \ log > mysqlbinlog binlog.000001 > 1.txt
One example:
log - bin = "D: / mysql/log/binlog", then, there will be a file under the folder D: / mysql log/binlog. 000001, etc
Q&A
1. How do I 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 a command
RESET MASTER
Delete all previous binlog and regenerate the new binlog
The suffix begins with 000001
Note: if you have an active slave server that is currently reading log 1 that you are trying to delete,
This statement will not work, but will fail with 1 error.
However, if the slave server is inactive and you happen to clean up one of the logs it wants to read, the slave server cannot replicate after it is started.
This statement is safe to run while the slave server is replicating. You don't need to stop them.
2. Record the content configuration to the base 2 log
binlog-do-db =sales records only sales libraries
binlog-ignore-db =sales all records except sales library
However, if you do not use use $dbname before manipulating the database, then all SQL will not be recorded
If use $dbname is used, the rule depends on the $dbname here, not on the library operating in SQL
3.2 incorrect processing of base log
By default, the binary log is not synchronized with the hard disk every time it is written. 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) to synchronize the binary log with the hard disk after every N binary log writes.
Even if sync_binlog is set to 1, it is possible that there is a discrepancy between the table contents and the binary log contents in the event of a crash.
If the MySQL server finds that the binary log is shorter (i.e., at least one InnoDB transaction is missing for successful commit) at crash recovery,
If sync_binlog =1 and the hard disk/file system does synchronize as needed (and some do not), then an error message is printed (" binary log ") < The name > Less than expected ").
In this case, the base 2 log is not accurate and replication should begin with the data snapshot from the primary server.
Definition:
The base 2 log contains all statements that have updated or potentially updated data (for example, one DELETE that does not match any row).
Function:
1. The primary purpose of binary logging is to make it possible to update the database as much as possible after recovery, since the binary log contains all updates made after the backup.
2. The hexadecimal log is also used on the master replication server to record all statements that will be sent to the slave server.
Adverse effects:
When running the server with binary logging enabled, performance is approximately 1% slower.
How to start:
Enabled through the wok with the option log-bin =file
(change my.ini file)
Log position
> > If no file name is specified, Mysql USES the hostname-bin file.
> > If a relative path is specified, it is assumed that the path is relative to the data directory
> > Mysql adds a numeric index after the file name, so the final form of the file is filename.number
If you provide an extension in the name of the log (for example, if you provide an extension in the name of the log (e.g.
Replacement strategy:
Use the index to loop through the file, and loop to the next index under the following conditions
1. Server restart
2. The server is updated
3. The log reached the maximum log length max_binlog_size
4. The log is refreshed mysql > flush logs;
Tool introduction:
shell > > mysqlbinlog [option] binlogFile > newfile
Such as: D: \ mysql \ log > mysqlbinlog binlog.000001 > 1.txt
One example:
log - bin = "D: / mysql/log/binlog", then, there will be a file under the folder D: / mysql log/binlog. 000001, etc
Q&A
1. How do I 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 a command
RESET MASTER
Delete all previous binlog and regenerate the new binlog
The suffix begins with 000001
Note: if you have an active slave server that is currently reading log 1 that you are trying to delete,
This statement will not work, but will fail with 1 error.
However, if the slave server is inactive and you happen to clean up one of the logs it wants to read, the slave server cannot replicate after it is started.
This statement is safe to run while the slave server is replicating. You don't need to stop them.
2. Record the content configuration to the base 2 log
binlog-do-db =sales records only sales libraries
binlog-ignore-db =sales all records except sales library
However, if you do not use use $dbname before manipulating the database, then all SQL will not be recorded
If use $dbname is used, the rule depends on the $dbname here, not on the library operating in SQL
3.2 incorrect processing of base log
By default, the binary log is not synchronized with the hard disk every time it is written. 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) to synchronize the binary log with the hard disk after every N binary log writes.
Even if sync_binlog is set to 1, it is possible that there is a discrepancy between the table contents and the binary log contents in the event of a crash.
If the MySQL server finds that the binary log is shorter (i.e., at least one InnoDB transaction is missing for successful commit) at crash recovery,
If sync_binlog =1 and the hard disk/file system does synchronize as needed (and some do not), then an error message is printed (" binary log ") < The name > Less than expected ").
In this case, the base 2 log is not accurate and replication should begin with the data snapshot from the primary server.