Shulou(Shulou.com)06/01 Report--

1. Brief introduction to binlog:

1. What is binlog:

Binlog logs are used to record all statements that have updated data or have potentially updated data (for example, a DELETE that does not match any rows). Statement is saved in the form of an event, which describes data changes.

2. Record format of binlog:

Mysql binlog logs are available in three formats: Statement, MiXED, and ROW

(after the MySQL5.7.7 version, the default value of binlog_format was changed to ROW. Master writes the event of the modified table to the binlog, and master sends the binlog information to the event in the slave,slave playback binlog. Replication based on ROW format is the safest replication, and slave requires fewer row locks; but there are some disadvantages, that is, replication based on ROW format, binlog will record more data. And the statements obtained from master cannot be seen on slave because they are all event. However, in ROW format, you can turn on the binlog_rows_query_log_events parameter, which allows binlog to record the events as well as the original sql statement to facilitate subsequent queries.)

①: Statement: every sql that modifies the data is recorded in the binlog.

Advantages: no need to record the changes of each line, reduce the number of binlog logs, save IO, and improve performance. (how much performance and log volume can be saved compared with row, which depends on the SQL of the application. The log amount generated by modifying or inserting the row format of the same record is still less than that generated by Statement, but considering that if the conditional update operation, as well as the whole table deletion, alter table and other operations, ROW format will generate a large number of logs, so when considering whether to use ROW format logs should follow the actual situation of the application. How much more logs will be generated, and the resulting IO performance problems.)

Disadvantages: since only execution statements are recorded, in order for these statements to run correctly on the slave, it is also necessary to record some information about the execution of each statement to ensure that all statements get the same results in slave as they are executed on the masterside. In addition, the replication of mysql, like some specific functions, slave can be consistent with the master will have a lot of related problems (such as sleep () function, last_insert_id (), and user-defined functions (udf) will have problems).

Statements that use the following functions cannot be copied either:

* LOAD_FILE ()

* UUID ()

* USER ()

* FOUND_ROWS ()

* SYSDATE () (unless the-- sysdate-is-now option is enabled at startup)

-at the same time, INSERT... SELECT produces more row-level locks than RBR.

②: Row: does not record the context-sensitive information of the sql statement, only saves which record is modified.

Pros: binlog does not record context-sensitive information about executed sql statements, but only needs to record what that record has been modified to. So the log content of row level will clearly record the details of each line of data modification. And there will be no problems that stored procedures, or function, and the calls and triggers of trigger can not be copied correctly in certain cases.

Disadvantages: when all executed statements are recorded in the log, they will be recorded as changes in each row, which may result in a large amount of log content, such as a update statement. If multiple records are modified, each change in binlog will be recorded, resulting in a large number of binlog logs, especially when executing statements such as alter table, each record will be changed due to table structure changes. Then each record in the table is recorded in the log.

③: Mixedlevel: is a mixed use of the above two kinds of level

General statement modification uses statment format to save binlog, such as some functions, statement can not complete the master-slave copy operation, then saving binlog,MySQL in row format will distinguish the log form of records according to each specific sql statement executed, that is, choose one between Statement and Row. The new version of MySQL Squadron row level mode is also optimized. Not all changes are recorded in row level, such as statement mode in the event of table structure changes. Statements that modify data, such as update or delete, still record changes to all rows.

II. Basic configuration and format setting of Binlog

1. Basic preparation

The Mysql BInlog log format can be specified through the property binlog_format of mysql's my.cnf file. As follows:

Binlog_format = ROW-binlog log format log_bin = / mysql/mysql-bin.log-binlog log name expire_logs_days = 7-binlog expiration cleanup time max_binlog_size 100m-binlog each log file size

2.Binlog log format selection

Mysql uses Statement log format by default, and ROW is recommended.

Due to some special uses, you can consider using ROWED, such as synchronizing data changes through binlog logs, which will save a lot of related operations. Binlog data processing will be very easy, and parsing will be easy compared to mixed (of course, provided that the IO overhead caused by the increased log volume is tolerated).

3.mysqlbinlog format selection

Mysql's principle for selecting the log format: if the table is directly manipulated by INSERT,UPDATE,DELETE, the log format is recorded according to the setting of binlog_format, and if it is done by management statements such as GRANT,REVOKE,SET PASSWORD, then the SBR mode is used anyway.

3. Related parameters of binlog:

-- log_bin: setting this parameter means enabling the binlog feature And specify the path name-- log_bin_index set this parameter is the path and name of the specified binary index file-- binlog_do_db this parameter indicates that only the binary log of the specified database is recorded-- binlog_ignore_db this parameter indicates that the binary log of the specified database is not recorded-- max_binlog_cache_size this parameter represents the maximum size of memory used by binlog-- binlog_cache_size. This parameter indicates the amount of memory used by binlog You can use the state variables binlog_cache_use and binlog_cache_disk_use to help with the test. Binlog_cache_use: the number of transactions that use binary log caching binlog_cache_disk_use: the number of transactions that use binary log caching but exceed the binlog_cache_ size value and use temporary files to save statements in the transaction-max_binlog_sizeBinlog maximum, maximum and default value 1GB, this setting does not strictly control the size of Binlog, especially when Binlog is close to the maximum and encounters a larger transaction In order to ensure the integrity of the transaction, it is impossible to switch the log. All SQL of the transaction can only be recorded in the current log until the end of the transaction-- sync_binlog this parameter directly affects the performance and integrity of the mysql-- sync_binlog=0: when the transaction commits, Mysql simply writes the data in the binlog_cache to the Binlog file. However, instead of executing disk synchronization instructions such as fsync to tell the file system to flush the cache to disk, it is best to let Filesystem decide when to synchronize. -- sync_binlog=n, after n transaction commits, Mysql will execute a disk synchronization instruction such as fsync, and the gay file system will flush the Binlog file cache to disk. Note: the default setting in Mysql is sync_binlog=0, that is, no mandatory disk refresh instructions are made, and the performance is best, but the risk is also the greatest. Once the system tightens the Crash, all Binlog information in the file system cache will be lost

4. Deletion of binlog:

The deletion of binlog can be deleted manually or automatically

1. Delete binlog automatically

Automatically delete binlogmysql > show binary logs;mysql > show variables like 'expire_logs_days';mysql > set global expire_logs_days=3 by mysql through binlog parameter (expire_logs_days)

2. Delete binlog manually

Mysql > reset master;-delete binlogmysql > reset slave; of master-delete relay log mysql > purge master logs before '2017-03-30 17 of slave;-delete binlog log file mysql > purge master logs to' log in the log index before the specified date -delete the binlog log file in the log index of the specified log file or delete mysql > set sql_log_bin=1/0; directly with the operating system command-if the user has super permission, you can enable or disable binlog logging for the current session mysql > show master logs;-View master's binlog log mysql > show binary logs;-View master's binlog log mysql > show master status -used to provide status information for master binary log files mysql > show slave hosts;-displays a list of currently registered slave. Slave that does not begin with the-- report-host=slave_name option will not appear in this list

3. View of binglog

You can view the contents of binlog through the mysqlbinlog command

[root@localhost ~] # mysqlbinlog / home/mysql/binlog/binlog.000003 | more account account 40019 SET @ @ session.Maxially inserted delayedthreads50003 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 16 provision51 virtual Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.# at 1960120330 17:54:15 server id 1 end_log_pos 294TIMESTAMP 1333101255Universe stop into tt7 select insert into tt7 select * from tt7Universe server id 1end_log_pos 120330 17:54:46 server id 1 end_log_pos 388 Query thread_id=3 exec_time=28 error_code=0SET timestamp 1333101286 alter table tt7 alter engineered innobance

3.1.Parse binlog format

-location

Located in the file, "at 196" indicates that the start of the "event" begins with 196 bytes, and the description of "end_log_pos 294" ends with 294 bytes.

-- timestamp

Time stamp of the event: "120330 17:54:46"

-- event execution time

Time spent on event execution: "exec_time=28"

-- error code

The error code is "error_code=0"

-- Identification of the server

Server identity id: "server id 1"

Note:

1. The timestamp in the binlog event is inherited from the statement. If a statement generates multiple events, the timestamps of these events are all the same and consistent with the first event.

2. Sometimes we find that the execution statement is inconsistent with the commit time in binlog because some transactions are committed automatically, and only one statement in this transaction has been executed for a certain period of time

The impact of rowid on binlog in innodb:

1. On galera cluster, it is best not to let a transaction update too much data, which can be properly controlled within 10,000 rows. There is no problem, because the verification and commit of galera cluster are serial, and a transaction is too large, which will cause other transactions in the cluster to wait for the transaction to complete, resulting in cluster fake death.

2. If no primary key is specified in innodb, a rowid will be created, but the binlog of MySQL is the server layer, while the rowid in innodb is something of the storage engine, and the server layer is not aware of the existence of rowid at all

3. Rowid is a column defined by innodb itself, which is added to the table only if there is no primary key defined in the table, but this column is only for storage to form a clustered index, but it is not exposed to the logical layer, and the upper layer does not need it, so you can just ignore it.

4. In the use of MySQL database, the primary key must be defined. If there is no primary key and it is replicated in row mode, it will inevitably cause such a problem. Unlike other databases, if there is no primary key, you can also use rowid to manipulate the table.

5. In galera cluster, you should define the primary key. If there is no definition, it will inevitably cause a failure. This is not the problem of galera cluster, but it will magnify the problem.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.