• notice
  • Congratulations on the launch of the Sought Tech site

Binlog log using the mysql database (must-see)

The importance of the binlog binary log to the mysql database, I won’t say much here.Based on my daily operating experience, combined with online reference materials, I will sort out the use of binlog logs:

1.Binlog log introduction

1) What is binlog
The binlog log is used to record all statements that have updated data or have potentially updated data (for example, a DELETE that does not match any row).Statements are stored in the form of "events", which describe data changes.

2) Binlog function
Because of the binlog for data update, it can be used for real-time backup, combined with master/slave master-slave replication.

3) Parameters related to binlog
log_bin

Setting this parameter means to enable the binlog function and specify the path name

log_bin_index

Set this parameter to specify the path and name of the binary index file

binlog_do_db

This parameter means that only the binary log of the specified database is recorded.

binlog_ignore_db
This parameter means do not record the binary log of the specified database

max_binlog_cache_size

This parameter indicates the maximum size of memory used by binlog

binlog_cache_size

This parameter indicates the memory size used by binlog, which can be used to help test through the state variables binlog_cache_use and binlog_cache_disk_use.

binlog_cache_use:Number of transactions using the binary log cache

binlog_cache_disk_use:Use binary log cache but exceed binlog_cache_size value and use temporary files to save the number of transactions in the transaction

max_binlog_size

The maximum value of Binlog, the maximum and default value is 1GB.This setting does not strictly control the size of Binlog, especially when Binlog is close to the maximum value and encounters a relatively large transaction, in order to ensure the integrity of the transaction, it is impossible To switch the log, you can only record all the SQL of the transaction into the current log until the end of the transaction

sync_binlog

This parameter directly affects the performance and integrity of mysql

sync_binlog=0

When the transaction is submitted, Mysql only writes the data in binlog_cache to the Binlog file, but does not execute disk synchronization instructions such as fsync to notify the file system to flush the cache to the disk, and let Filesystem decide when to do the synchronization., This is the best performance.

sync_binlog=n, after n transaction commits, Mysql will execute a disk synchronization command such as fsync, and the gay file system will refresh the Binlog file cache To the disk.

The default setting in Mysql is sync_binlog=0, that is, no mandatory disk refresh command is made.At this time, the performance is the best, but the risk is also the greatest.Once the system crashes, all Binlog information in the file system cache will be lost

4) Delete binlog

The binlog can be deleted manually or automatically:

a) Automatically delete binlog

Use the binlog parameter (expire_logs_days) to automatically delete binlog from mysql

mysql> show binary logs;
mysql> show variables like'expire_logs_days';      //This parameter indicates the number of days that the binlog log is automatically deleted/expired.The default value is 0, which means that it is not automatically deleted.
mysql> set global expire_logs_days=3;        //means that the log will be kept for 3 days and will expire automatically after 3 days.

b) Manually delete binlog

mysql> reset master;        //Delete master's binlog, that is, manually delete all binlog logs
mysql> reset slave;          //delete slave's relay log
mysql> purge master logs before '2012-03-30 17:20:00';         //Delete binlog logs in the log index before the specified date File
mysql> purge master logs to'binlog.000002';       //Delete the binlog log file in the log index of the specified log file

mysql> set sql_log_bin=1/0;       //If the user has super Permission, you canenable or disablebinlog record of the current session
mysql> show master logs;          //View master's binlog log list
mysql> show binary logs;           //View master's binlog log file size
mysql> show master status;    //Used to provide status information of the master binary log file
mysql> show slave hosts;        //Display the list of currently registered slaves.Slaves that do not start with the --report-host=slave_name option will not be displayed in this list

mysql> flush logs;     //Generate a new binlog log file

 

Automatic cleaning and manual deletion of mysql binlog log case description:

When the MySQL database master-slave is turned on, a large number of files such as mysql-bin.00000* log will be generated, which will consume a lot of your hard disk space.
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004
mysql-bin.000005
…
 
There are three solutions to delete these binlog logs:
1.Close mysql master and slave, close binlog;
The example operation is as follows:
[root@huqniupc ~]# vim/etc/my.cnf//Comment out log-bin and binlog_format
# Replication Master Server (default)
# binary logging is required for replication
# log-bin=mysql-bin
# binary logging format-mixed recommended
# binlog_format=mixed
Then restart the database
 
2.Open mysql master and slave, set expire_logs_days;
The example operation is as follows:
[root@huqniupc ~]# vim/etc/my.cnf//Modify expire_logs_days, x is the number of days automatically deleted, generally set x to a short point, such as 10
expire_logs_days = x//The number of days that the binary log is automatically deleted.The default value is 0, which means "no automatic deletion"
This method requires restarting mysql
 
Of course, it is not necessary to restart mysql and open the mysql masterFrom, set expire_logs_days directly in mysql
> show binary logs;
> show variables like'%log%';
> set global expire_logs_days = 10;
 
 
3.Manually clear the binlog file, (such as Mysql> PURGE MASTER LOGS TO ‘MySQL-bin.010’;)
The example operation is as follows:
[root@huqniupc ~]#/usr/local/mysql/bin/mysql -u root -p
> PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);//Delete the MySQL binlog log 10 days ago.Appendix 2 contains usage and examples of manual deletion of PURGE MASTER LOGS
> show master logs;
  
You can also reset the master and delete all binlog files:
#/usr/local/mysql/bin/mysql -u root -p
> reset master;//Appendix 3 explains the impact on slave mysql when binlog is cleared
  
-------------------------------------------------- -------------
PURGE MASTER LOGS manually delete usage and examples, MASTER and BINARY are synonyms
> PURGE {MASTER | BINARY} LOGS TO'log_name'
> PURGE {MASTER | BINARY} LOGS BEFORE'date'
Delete all binary logs in the specified log or log index before the date.These logs will also be deleted from the list recorded in the log index file.MySQL BIN-LOG logs, so that the given log becomes the first one.
 
Examples:
> PURGE MASTER LOGS TO'MySQL-bin.010';//Clear MySQL-bin.010 log
> PURGE MASTER LOGS BEFORE '2008-06-22 13:00:00';//Clear the binlog before 2008-06-22 13:00:00
> PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);//Clear the binlog log BEFORE 3 days ago.The date argument of the variable can be in the format of'YYYY-MM-DD hh:mm:ss'.
-------------------------------------------------- ---

5) When clearing binlog, the impact on slave mysql

If there is an active slave slave server that is currently reading one of the logs you are trying to delete, this statement will not work, but will fail with an error; but if the slave slave server If it is closed (or the master-slave relationship is closed), and it happens to clean up one of the logs it wants to read, the slave server cannot be copied after it is started; when the slave server is copying, this statement can run safely, No need to stop them.

6) View binglog

The contents of binlog can be viewed through the mysqlbinlog command

[root@localhost ~]# mysqlbinlog/home/mysql/binlog/binlog.000003 | more
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*! 50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER/*!*/;
# at 4
#120330 16:51:46 server id 1 end_log_pos 98 Start: binlog v 4, server v 5.0.45-log created 120330 1
6:51:46
# Warning: this binlog was not closed properly.Most probably mysqld crashed writing it.
# at 196
#120330 17:54:15 server id 1 end_log_pos 294 Query thread_id=3 exec_time=2 error_code=0
SET TIMESTAMP=1333101255/*!*/;
insert into tt7 select * from tt7/*!*/;
# at 294
#120330 17:54:46 server id 1 end_log_pos 388 Query thread_id=3 exec_time=28 error_code=0
SET TIMESTAMP=1333101286/*!*/;
alter table tt7 engine=innodb/*!*/;

Parse the binlog format:

Location

Located at the position in the file, "at 196" indicates the starting point of the "event", starting with the 196th byte; "end_log_pos 294" indicates the ending with the 294th byte

Time stamp

Time stamp when the event occurred: "120330 17:54:46"

Event execution time

Time spent on event execution: "exec_time=28"

Error code

The error code is: "error_code=0"

Server ID

The identification id of the server: "server id 1"

Note the following:

1.It is inconceivable that the mysql log can be restored to a state at any time.This restoration has a prerequisite!
At least there must be a database backup from the beginning of the log record, and restoring the database through the log is actually just a playback process of the previous operation, don't think about it too complicated.

Since it is a playback operation, you must pay attention.If you perform two recovery operations, it is equivalent to two playbacks.The consequences can be imagined.

So:

1) Be sure to back up the data before restoring.

2) Due to the large number of binary files and the large span of data that needs to be recovered, consider combining log files for recovery.

2.Turn on the binlog log function

If you want to restore the database through the log, must first be defined in the my.cnf file, log-bin=mysql-bin, so the generated binlog log name is Named after mysql-bin

3.When will a new binlog file be generated

1) Add --flush-logs during backup

2) When restarting the mysql service

Special reminder, mysql will regenerate a file similar to mysql-bin.00000n every time it is started.If your mysql has to be restarted once a day, you must pay special attention not to select the wrong log file.

Two, binlog log format introduction 

(1) Mysql binlog log has three formats, namely Statement, MiXED, ROW

1) Statement: Every sql that will modify data will be recorded in the binlog

Advantages: There is no need to record the changes of each line, which reduces the amount of binlog logs, saves IO, and improves performance.(How much performance and log volume can be saved compared to row.This depends on the SQL situation of the application.Normally, the log volume generated by the same record modification or insertion row format is less than the log volume generated by the Statement, but considering the conditional update Operations, as well as operations such as deleting entire tables, alter tables, etc., ROW format will generate a lot of logs, so when considering whether to use ROW format logs, you should follow the actual situation of the application, how much the amount of logs generated will increase, and the resulting IO performance issues.)

Disadvantages: Since only the executed statements are recorded, in order for these statements to run correctly on the slave, some related information must be recorded when each statement is executed.Ensure that all statements can get the same result on the slave as when executed on the master side.In addition, the replication of mysql, like some specific functions, the slave can be consistent with the master, there will be many related problems (such as sleep() function, last_insert_id(), and user-defined functions(udf) will cause problems).

Statements using the following functions cannot be copied:

* LOAD_FILE()
* UUID()
* USER()
* FOUND_ROWS()
* SYSDATE() (unless the --sysdate-is-now option is enabled at startup)

At the same time INSERT...SELECT will generate more row-level locks than RBR

2) Row: does not record the context-related information of the sql statement, only save which record is modified

Advantages: Binlog does not need to record the context-related information of the executed sql statement, and only needs to record which record has been modified into.Therefore, the log content of rowlevel will clearly record the details of each row of data modification.And there will be no problems with stored procedures, or functions, and trigger calls and triggers that cannot be copied correctly under certain specific circumstances.

Disadvantages: When all executed statements are recorded in the log, they will be recorded with the modification of each line record, which may generate a large number of Log content, such as an update statement, modify multiple records, each modification in the binlog will be recorded, which will cause a large amount of binlog log, especially when the statement such as alter table is executed, due to the modification of the table structure, Every record changes, then every record in the table will be recorded in the log.

3) Mixedlevel: is a mixed use of the above two levels, general statement modification uses statment format to save binlog, such as some Function, statement cannot complete the master-slave replication operation, save the binlog in row format, MySQL will distinguish the log format to be recorded according to each specific SQL statement executed, that is, choose one between Statement and Row.New The row level mode of the MySQL squadron has also been optimized.Not all changes will be recorded in the row level.For example, when the table structure changes, it will be recorded in the statement mode.As for the statements that modify data, such as update or delete, all row changes are still recorded.

Mixed log description:

In the process of slave log synchronization, for the use of time functions such as now, the MIXED log format will generate a corresponding unix_timestamp()*1000 time string in the log.When the slave completes the synchronization, it will use sqlEvent The time of occurrence is used to ensure the accuracy of the data.In addition, for some functional functions slaves can complete the corresponding data synchronization, and for some of the above-specified UDF functions that cause the Slave to be unable to know, these Binlogs will be stored in the ROW format to ensure that the generated Binlogs can be completed by the Slave.data synchronization.

(2) Basic binlog configuration and format setting

1) Basic configuration

The format of the binlog log can be specified by the attribute binlog_format of the my.cnf file of mysql.Such as the following:
binlog_format = MIXED              //binlog log format
log_bin = directory/mysql-bin.log       //binlog log name
expire_logs_days = 7                //binlog expired cleanup time
max_binlog_size 100m              //binlog each log file size

binlog-do-db=The name of the database to be backed up.If you back up multiple databases, you can set this option repeatedly
binlog-ignore-db=Databases that do not need to be backed up are suffering.If you back up multiple databases, you can set this option repeatedly.

2) Binlog log format selection

Mysql uses Statement log format by default, MIXED is recommended.
Due to some special uses, you can consider using ROWED, such as synchronizing data modification through the binlog log, which will save a lot of related operations.For binlog data processing will become very easy, relatively mixed, analysis will also be very easy (of course, the premise is that the IO overhead caused by the increased log volume is within a tolerable range).

3) mysqlbinlog format selection

The principle of mysql's selection of log format: if INSERT, UPDATE, DELETE, etc.are used to directly manipulate the table, the log format will be recorded according to the setting of binlog_format, if it is using GRANT, REVOKE, SET PASSWORD and other management statements If you do, then use SBR mode to record anyway

(3) Mysql Binlog log analysis

Check the specific mysql log through the MysqlBinlog command, as follows:

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

SET TIMESTAMP=1350355892/*!*/;

BEGIN

/*!*/;

# at 1643330

#121016 10:51:32 server id 1 end_log_pos 1643885 Query thread_id=272571 exec_time=0 error_code=0

SET TIMESTAMP=1350355892/*!*/;

Insert into T_test....)

/*!*/;

# at 1643885

#121016 10:51:32 server id 1 end_log_pos 1643912 Xid = 0

COMMIT/*!*/;

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

1.Time to start things:

SET TIMESTAMP=1350355892/*!*/;

BEGIN

2.sqlevent starting point

#at 1643330: is the starting point of the event, starting with 1643330 bytes.

3.The time point when sqlevent occurred

#121016 10:51:32: is the time when the event occurred,

4.serverId

server id 1: is the serverId of the master

5.sqlevent end point and time spent, error code

end_log_pos 1643885: is the end of the event, ending with 1643885 bytes.

execTime 0: time spent

error_code=0: error code

Xid: The event indicates the submitted XA transaction

Three, mysql log (key binlog log) optimization instructions

The MySQL system is highly scalable.It can run efficiently in an environment with sufficient hardware resources, and it can also run well in an environment with few resources.
But in any case, as sufficient hardware resources as possible is always helpful to improve the performance of MySQL.

The following will focus on analyzing the impact of MySQL logs (mainly Binlog) on ​​system performance, and get corresponding optimization ideas based on the relevant characteristics of the logs.

1) The performance impact of logs
The direct performance loss due to log recording is the most expensive IO resource in the database system.

MySQL logs mainly include error log (ErrorLog), update log (UpdateLog), binary log (Binlog), query log (QueryLog), slow query log (SlowQueryLog) etc.
Special attention: The update log is only available in the old version of MySQL, and it has been replaced by the binary log.

By default, the system only opens the error log and closes all other logs to achieve the goal of minimizing IO loss and improving system performance.
However, in practical application scenarios that are generally slightly more important, at least the binary log needs to be turned on, because this is the basis for many MySQL storage engines to perform incremental backups, and it is also the basic condition for MySQL to achieve replication.
Sometimes in order to further optimize the performance of mysql and locate the slower SQL statement, many systems will also open the slow query log to record the SQL statement whose execution time exceeds a certain value (set by us).

Under normal circumstances, few systems in a production system will open the query log.After the query log is opened, every query executed in MySQL will be recorded in the log, which will bring a relatively large IO burden to the system, but the actual benefits brought by it are not very large.Generally, only in the development and test environment, in order to locate which SQL statements are used for certain functions, the log will be opened in a short period of time for corresponding analysis.
Therefore, in the MySQL system, the MySQL log (not including the log of each storage engine) that affects performance is mainly Binlog.

2) Binlog related parameters and optimization strategies

Let’s first look at the relevant parameters of Binlog.You can get the relevant parameters of Binlog by executing the following commands.
Of course, it also shows "innodb_locks_unsafe_for_binlog", the Binlog-related parameters unique to the Innodb storage engine:

mysql> show variables like'%binlog%';
+-----------------------------------------+------- ---------------+
| Variable_name | Value |
+-----------------------------------------+------- ---------------+
| binlog_cache_size | 16777216 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | IGNORE_ERROR |
| binlog_format | MIXED |
| binlog_gtid_simple_recovery | OFF |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlogging_impossible_mode | IGNORE_ERROR |
| innodb_api_enable_binlog | OFF |
| innodb_locks_unsafe_for_binlog | OFF |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| simplified_binlog_gtid_recovery | OFF |
| sync_binlog | 1 |
+-----------------------------------------+------- ---------------+
19 rows in set (0.00 sec)

"binlog_cache_size": The size of the cache to accommodate binary log SQL statements during the transaction.Binary log cache is the memory allocated for each client under the premise that the server supports the transaction storage engine and the server has enabled the binary log (-log-bin option).Note that each client can allocate a set size of binlogcache space.If the system of readers and friends often appears in multi-sentence transactions, you can try to increase the size of the value to get more performance.Of course, we can judge the current binlog_cache_size status through the following two status variables of MySQL: Binlog_cache_use and Binlog_cache_disk_use.

"max_binlog_cache_size": Corresponds to "binlog_cache_size", but it represents the maximum cache memory size that binlog can use.When we execute multi-statement transactions, if max_binlog_cache_size is not large enough, the system may report "Multi-statementtransactionrequiredmore than'max_binlog_cache_size'bytesofstorage" error.

"max_binlog_size": The maximum value of Binlog log, generally set to 512M or 1G, but cannot exceed 1G.This size does not strictly control the size of Binlog, especially when it reaches the end of Binlog and encounters a larger transaction.In order to ensure the integrity of the transaction, it is impossible for the system to switch the log.It can only be the transaction All of the SQL are recorded into the current log until the end of the transaction.This is a bit different from Oracle's Redo log, because Oracle's Redo log records changes in the physical location of the data file, and it also records Redo and Undo related information, so whether the same transaction is in the same log It is not critical for Oracle.What MySQL records in Binlog is database logic change information, which MySQL calls Event, which is actually a Query statement such as DML that brings database changes.

"sync_binlog": This parameter is very important to the MySQL system.It not only affects the performance loss of Binlog to MySQL, but also It also affects the integrity of the data in MySQL.The description of the various settings of the "sync_binlog" parameter is as follows:

sync_binlog=0, when the transaction is submitted, MySQL does not do fsync and other disk synchronization instructions to refresh the information in binlog_cache to disk, and let Filesystem decide by itself When to do the synchronization, or to synchronize to the disk after the cache is full.

sync_binlog=n, when every n transactions are committed, MySQL will perform a disk synchronization command such as fsync to force the data in binlog_cache to be written Into the disk.

The default setting of the system in MySQL is sync_binlog=0, that is, no mandatory disk refresh command is performed.At this time, the performance is the best, but the risk is also the greatest.Because once the system crashes, all binlog information in binlog_cache will be lost.When it is set to "1", it is the safest setting but with the greatest performance loss.Because when set to 1, even if the system crashes, at most one unfinished transaction in binlog_cache will be lost, which has no substantial impact on the actual data.Judging from past experience and related tests, for systems with high concurrent transactions, the write performance gap between "sync_binlog" set to 0 and set to 1 may be as high as 5 times or more.

Also:

Replication of MySQL is actually to copy the Binlog on the Master side to the Slave side through the network by using the IO thread, and then parse the log in Binlog through the SQL thread And then applied to the database to achieve.Therefore, the size of Binlog will have a direct impact on the IO thread and the network between Msater and Slave.

The amount of Binlog generated in MySQL cannot be changed.As long as our Query changes the data in the database, then the Event corresponding to the Query must be recorded in Binlog.So is there no way for us to optimize replication? Of course not.In the MySQL replication environment, there are actually 8 parameters that allow us to control the DB or Table that needs to be replicated or that need to be ignored and not replicated.They are:

Binlog_Do_DB: Set which databases (Schema) need to record Binlog;

Binlog_Ignore_DB: set which databases (Schema) not to record Binlog;

Replicate_Do_DB: Set the database to be replicated (Schema), multiple DBs are separated by commas (",");

Replicate_Ignore_DB: Set the database that can be ignored (Schema);

Replicate_Do_Table: Set the Table to be copied;

Replicate_Ignore_Table: set a table that can be ignored;

Replicate_Wild_Do_Table: The function is the same as Replicate_Do_Table, but it can be set with wildcards;

Replicate_Wild_Ignore_Table: The function is the same as Replicate_Ignore_Table, with wildcard settings available;


Through the above eight parameters, we can easily control the amount of Binlog from the Master end to the Slave end as small as possible according to actual needs, thereby reducing the network traffic from the Master end to the Slave end, reducing the IO amount of the IO thread, and Reduce the number of SQL threads to parse and apply SQL, and ultimately improve the data delay problem on the Slave.

Actually, the first two of the above eight parameters are set on the Master side, and the last six parameters are set on the Slave side.Although the first two parameters and the following six parameters are not directly related in function, similar functions can be enabled for optimizing MySQL Replication.Of course there are certain differences, the main differences are as follows:

If you set the first two parameters on the Master side, not only will the amount of IO brought by the Binlog record on the Master side be reduced, but also the IO thread on the Master side can reduce the amount of Binlog reads that are passed to the IO on the Slave side.Th

Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+