MySQL binlog Log Optimizer

MySQL log type has slow query log, binary log, error log, by default, the system only open the error log, because the open log will produce a large IO performance consumption.

  Generally, the build system rarely opens the binary log (bin log), the bin log optimization strategy:mysql> show variables like '%binlog% '; +----------------------- ------------------+----------------------+| Variable_name                           | Value                |+-----------------------------------------+------------- ---------+| Binlog_cache_size                       | 32768                | | Binlog_checksum                         | CRC32                | | Binlog_direct_non_transactional_updates | OFF                  | | Binlog_format                           | ROW                  | | 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     &NBSp            | | Innodb_locks_unsafe_for_binlog          | OFF                  | | Max_binlog_cache_size                   | 18446744073709547520 | | Max_binlog_size                         | 1073741824           | | Max_binlog_stmt_cache_size              | 18446744073709547520 | | Sync_binlog                           | 0                    |+-----------------------------------------+----- -----------------+ binlog_cache_size: The cache size used to hold binary SQL statements during a transaction. The binary log cache is used as a precondition for the server side to use the supporting transaction engine and to turn on the bin log function. This parameter allocates a cache of binlog_cache_size size for each client, and if the user frequently uses multi-statement transactions, you can increase the binlog_cache_size size, get better performance, and determine whether binlog_cache_size settingsIs it reasonable? You can judge: mysql> show status like ' binlog% ' by the following two states, +----------------------------+-------+| Variable_name              | Value |+----------------------------+-------+| Binlog_cache_disk_use      | 1     | | Binlog_cache_use           |    |+----------------------------+-------The maximum size of the + max_binlog_size:binlog, typically 512M or 1G, but not more than 1G, This size does not strictly control the size of the Binlog log, if a large transaction is performed at the critical point of the binlog size, in order to ensure transaction integrity, no log switchover can be made, only all SQL of the transaction is logged in the current log. In fact, MySQL's binlog log is a DML,DDL operation that brings data changes  sync_binlog: This parameter is used to control how the records in the Binlog cache are synchronized (Fsync) to disk. The following is a description of its parameter value; Sync_binlog=0: Indicates that when a transaction commits, it does not immediately synchronize the records in the Binlog cache to disk, when it is synchronized, so that the file system decides itself, or the cache is full before synchronizing. This way the best performance, are the greatest risk, once the system down, all the records in the Binlog cache will be lost, if the Sync_binlog value is set to 1, the most secure, because even if the system down, but also lost only one transaction operation record, are the worst performance, Each time a transaction is submitted, the records in Binlog_cache are synchronized to disk. Sync_binlog=n: The master-slave synchronization that indicates how many times a transaction is committed before the records in the Binlog cache are synchronized to the disk  mysql is the slave side synchronizes the log generated by the master side to the trunk log on the slave side through the IO thread. The relay logs are then replayed on the slave side through the SQL thread. So the size of the log on the master side directly affects the SALThe performance of VE-side synchronization, typically, the Binlog log volume generated by the master side is unavoidable, but you can reduce Binlog log synchronization by setting parameters to selectively synchronize which databases or tables generate Binlog logs.  BINLOG_DO_DB: Set which databases need to be logged Binlog (Master) binlog_ignore_db: Set which databases do not require logging Binlog (master side) Replicate_do_ DB: Set which databases need to synchronize Binlog, multiple databases are separated by commas "," replicate_ignore_db: Set which databases do not require synchronization binlog, multiple databases are separated by commas "," replicate_do_table: Set which tables need to be synchronized binlogreplicate_ignore_table: Set which tables do not require synchronization binlogreplicate_wild_do_table: Set which tables need to be synchronized binlog, and Replicate_do_ The difference between tables is that you can use wildcards to specify Table replicate_wild_ignore_table: Set which tables do not require synchronous binlog, and replicate_ignore_table is the difference between using wildcards to specify tables   If the above two parameters are set on the master side. Reduces the number of records on the master-side Binlog log, lowers the master-side log io, reduces network traffic when synchronizing to the slave end, and reduces the lave-side IO Thread synchronization log volume and SQL thread application relay log volume. However, it is important to note that MySQL determines whether copying an event is not recorded according to the DB name specified in the query that generated the event, but is based on whether the db (using dbname) in which the query was executed is a binlog_do_db specified db. If, then record, otherwise not recorded. Consider the following scenario: There are three databases: A,b,c, where the default db for Binlog_do_db=b,c app login is a, which is now performed as follows Query:update b.t1 set c1 = XXX; In this case, MySQL is not recorded Binlog, Because the update operation of the T1 table under the B library does not log binlog, it cannot be synchronized to the slave side, which in turn causes the master to be inconsistent with the slave data.   If the db equals binlog_do_db, the table under the DB that does not need to be synchronized will also record the Binlog log, that is, all changes in db under that DBThe data query will record binlog that the application login db is B, execute query:update a.t1 set c1=xxx, and the Binlog log is also logged. Because there is not a library in the binlog_do_db, so the slave end will not have a library, resulting in the slave end can not find a library and error.   If the following six parameters are set at the slave end, regardless of whether master or the event that should not be copied will be synchronized to the slave side, the negative effect is IO, network pressure, and slave-side IO thread writing relay log pressure, However, the slave-side SQL thread can be reduced to the amount of relay log, and the default db is not equal to binlog_do_db because the replicate_do_db,replicate_ignore_db is set to filter the corresponding log.

MySQL binlog Log optimization