Mysql binlog parameter settings, mysqlbinlog

Source: Internet
Author: User
Tags mysql update

Mysql binlog parameter settings, mysqlbinlog
1. mysql has many system variables that can be set. Different system variables may lead to different system running states. Therefore, mysql provides two groups of commands to view the system settings and running status.

1. system settings:

SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
Show variables shows the values of MySQL system variables.
2. Running status:
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
Show status provides server status information.

Note: show xxx may display a lot of content. For example, if there are too many content in Linux and grep is often required for filtering, mysql can also take this into consideration and use the LIKE clause to filter.


After installing MySQL, you must optimize and adjust various MySQL Parameter options. Although the MySQL system is highly scalable, it can run efficiently in an environment with sufficient hardware resources or in a few resource environments. However, as many hardware resources as possible will always help improve MySQL performance. In this section, we mainly analyze the impact of MySQL logs (mainly Binlog) on system performance, and draw the corresponding optimization ideas based on the characteristics of logs.

Performance impact of logs

The direct performance loss caused by log records is the most expensive IO resource in the database system.

In the previous chapter about MySQL physical architecture, we have learned that MySQL logs include error logs (ErrorLog), update logs (UpdateLog), binary logs (Binlog ), query log and SlowQueryLog. Of course, the update log is only available in the old version of MySQL and has been replaced by binary logs.

By default, the system only opens error logs and closes all other logs to minimize IO consumption and improve system performance. However, in actual application scenarios that are a little more important, you must at least enable the binary log, because this is the basis for many MySQL storage engines to perform Incremental backup and is also the basic condition for MySQL to implement replication. Sometimes, to further optimize the performance, locate slow SQL statements. Many systems also open slow query logs to record SQL statements whose execution time exceeds a specific value (set by ourselves.

Generally, query logs are rarely opened in the production system. Because after the Query log is opened, every Query executed in MySQL is recorded in the log, this system will bring a large IO burden, but the actual benefits are not very large. Generally, this log can be opened for analysis in a short period of time only when some functions use SQL statements in the development and testing environment. Therefore, in the MySQL system, MySQL logs that affect the performance (excluding the logs of each storage engine) are mainly binlogs.


2. Binlog related parameters and optimization strategies.


Binlog_cache_size

Binlog_cache_disk_use

Binlog_cache_use     

Max_binlog_cache_size

Max_binlog_size

Sync_binlog


"Binlog_cache_size": the cache size of the binary log SQL statement during the transaction process. The binary log cache is the memory allocated to each client on the premise that the server supports the transaction storage engine and the server enables the binary log (-log-bin option). Note, is that each Client can allocate binlogcache space of the set size. If the reader's friend's system often shows the trend of Multi-statement transactions, you can try to increase the value to achieve better performance. Of course, we can use the following two state variables of MySQL to determine the current status of binlog_cache_size: Binlog_cache_use and Binlog_cache_disk_use.

Binlog_cache_disk_use: indicates the number of times temporary files are used for cached binary logs due to insufficient memory designed by binlog_cache_size.

Binlog_cache_use: indicates the number of times binlog_cache_size is cached.

When the value of Binlog_cache_disk_use is large, we can increase the value of binlog_cache_size appropriately.

Show global status like 'bin % ';

The preceding statement shows the usage of the current database binlog_cache_size.

Mysql> show status like 'binlog _ % ';
+ ----------------------- + ----------- +
| Variable_name       | Value   |
+ ----------------------- + ----------- +
| Binlog_cache_disk_use | 0       |
| Binlog_cache_use    | 1, 120402264 |
+ ----------------------- + ----------- +

"Max_binlog_cache_size": corresponds to "binlog_cache_size", but 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 the error "Multi-statementtransactionrequiredmorethan 'max _ binlog_cache_size 'bytesofstorage.


"Max_binlog_size": maximum value of Binlog logs. Generally, it is set to m or 1G, but cannot exceed 1G. This size does not strictly control the Binlog size. Especially when a large transaction arrives near the end of the Binlog, the system ensures the integrity of the transaction, it is impossible to switch logs. You can only record all SQL statements of the transaction into the current log until the transaction ends. This is a little different from the Redo log of Oracle, because the Redo log of Oracle records changes in the physical location of the data file and records the Redo and Undo information at the same time, therefore, whether a transaction is in a log is not critical to Oracle. MySQL records database logic changes in the Binlog. MySQL calls this Event as a Query statement such as DML that brings database changes.

"Sync_binlog": this parameter is crucial for the MySQL system. It not only affects the performance loss caused by Binlog on MySQL, but also affects the data integrity in MySQL. The settings of the "sync_binlog" parameter are described as follows:

Sync_binlog = 0. After the transaction is committed, MySQL does not perform Disk Synchronization commands such as fsync to refresh the information in binlog_cache to the disk, and Filesystem determines when to synchronize the data, or, after the cache is full, it is synchronized to the disk.

Sync_binlog = n. After each n transaction commits, MySQL runs a Disk Synchronization command such as fsync to forcibly write data in binlog_cache to the disk.

In MySQL, sync_binlog is set to 0 by default, that is, no mandatory disk refresh command is performed. At this time, the performance is the best, but the risk is also the biggest. Because once the system Crash, all binlog information in binlog_cache will be lost. When it is set to "1", it is the safest but the biggest loss of performance. When set to 1, even if the system Crash is used, a transaction not completed in binlog_cache can be lost at most, without any material impact on the actual data. From past experience and related tests, for high-concurrency transaction systems, the system write performance gap between "sync_binlog" and "1" may be as high as 5 times or more.



Mysql binlog log Switch

This is set in the mysql configuration file ~~~ Set to enable binary log ~~~

Why MySQL binlog

The danger is simple: they don't work the way you think they do. consider the following scenario: you set binlog-ignore-db to "garbage" so data in the garbage database (which doesn't exist on the slave) isn' t replicated. (I'll come back to this in a second, so if you already see the problem, don't rush to the comment form .) now you do the following: $ mysqlmysql delete from garbage. junk; mysq L use garbage; mysql update production. users set disabled = 1 where user = "root"; You just broke replication, twice. once, because your slave is going to execute the first query and there's no such table "garbage. junk "on the slave. the second time, silently, because the update to production. users isn' t replicated, so now the root user isn' t disabled on the slave. the copy will be broketwice. For the first time, because slave tries to go west, you give the first sentence But there is no such table "garbage. junk ", the second time, implicit, because of. users will not be copied because the root account is not disabled on slave. why? Because binlog-ignore-db doesn't do what you think. the phrase I used earlier, "data in the garbage database isn' t replicated," is a fallacy. that's not what it does. in fact, itfilters out binary logging for statements issued from connections whose default database is "garbage. "In other words, filtering is not based on the contents of the query -- it is based on what database you USE. the other configuration options I mentioned work similarly. the binlog-do-db and binlog-ignore ...... remaining full text>

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.