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

This article mainly gives you a brief talk about MySQL5.7 startup parameter tuning. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here. Let's go straight to the topic of MySQL5.7 startup parameter tuning. I hope it can bring you some practical help.

1mai Mui Mui Log Mutual, slaveMutual updates

System variable name: log_slave_updates

In general, slave will not write the binlog records received from master to its own binlog. This parameter will cause slave to write the binlog received from master into its own binlog through the SQL thread, but the premise is that slave must enable its own binlog. This parameter is generally used for cascading replication. For example, if you need A to copy from B to B to C, then B should enable this parameter.

2maxim Meimei MasterUp infoMutual fileholders fileholders name

The name of the file that slave records about master information, default master.info, under datadir.

3mai Mui Maxi relayLog Mutual sizeable size

System variable name: max_relay_log_size

If the size of the staging log file that is automatically recorded in rotation is set to non-0, the next staging log file is recorded when the size of the staging log file exceeds this value. If this value is 0 (the default), the size of the staging log is determined by max_binlog_size.

4furowit, relative, copyright, logically, fileworthy name

System variable name: relay_log

The name of the transfer log file prefix (base name). For the default replication channel, the default value is host_name-relay-bin. For the non-default replication channel, the default value is host_name-channel-relay-bin, where channel is the name of the replication channel recorded in the transit log. If the file name is not preceded by an absolute path, the transfer file path is under datadir. When the CVM creates a transfer file, it will add a numeric sequence number to the end of the file. If this parameter is specified but no value is specified, the file name depends on the other parameters, the order of the parameters, and whether they are specified on the command line or in the configuration file. This parameter is also used to generate transit log index files, but can be overridden by specifying the-- relay-log-index parameter.

This parameter is useful in the following situations:

1. Create a transit log file independent of the mastername.

2, if you want to put the transit log somewhere else instead of under datadir.

3. Improve the speed of load balancing between disks

The name and path of the transit log file can be obtained from the relay_log_basename system variable.

5mai MurrelayLog indexation filewriting name

System parameter name: relay_log_index

Transfer log suffix, default host_name-relay-bin.index, in the datadir directory.

6furowrelayLog color in color filewriting filewriting name

The name of the file used to record transit log information. The default relay-log.info is in the datadir directory.

7MurrelayLog color purege = {0 | 1}

System parameter name: relay_log_purge

Turn on or off automatic deletion of transit logs when not needed. It is enabled by default (1), which is a global variable and can be accessed through the

SET GLOBAL relay_log_purge = N dynamic setting.

8mai Murray relayLog recovery

Enable and restore the transfer log immediately after the CVM is started. The recovery process creates a new staging log file and causes the binlog thread to read from master to the staging file and the SQL thread to read the staging file. The main purpose of this parameter is to prevent SQL threads from reading corrupted transit logs after slave downtime (if so, it may cause data inconsistencies with master). However, in order to achieve this purpose, you also need to set the-- relay-log-info-repository parameter to TABLE (that is, record the information of the transit log in the form of a table) and enable-- relay-log-purege (enabled by default). This parameter defaults to 0 and is not enabled. When multithreaded replication is used, data discontinuity may occur. If this parameter is enabled and cannot be solved, you can solve it by using the following command:

START SLAVE UNTILSQL_AFTER_MTS_GSPS, which makes the CVM in a more continuous state, and then performs RESET SLAVE to delete the transfer log.

9mai Murrelayi Log Muzuki spaceFlavingsize

System variable name: relay_log_space_limit

Set the upper limit (in bytes) for the total size of all transit log files. If set to 0, there is no limit. This parameter is useful in cases where there is not much disk space for the service. When the staging file is greater than this value, the binlog O thread stops reading the SQL from the master until the SQL thread catches up and deletes the useless staging log. However, it is not absolute. When the SQL thread needs more events before deleting the transit log, the I / O thread will exceed this value, otherwise it will cause a deadlock (SQL thread and I / O thread wait for each other)

In addition, this value should not be less than twice the value of-- max-relay-log-size (when-- max-relay-log-size is 0, it should not be less than twice-- max-binlog-size). In such a case, when the SQL thread waits for an idle disk because it exceeds the value of-- relay-log-space-limit, but the SQL thread does not relay log deletion and does not satisfy the Iamp O thread, it will temporarily be ignored by the lightweight IAccord O-relay-log-space-limit.

10mai Mui Mutual replicateMutual dou Mutual dbProductionName

Create a filter in slave to copy the specified database, which can be created using the CHANGE REPLICATION FILTER REPLICATION_DO_DB command in version 5.7.3 and later. The replication details vary with different binlog, as shown below:

Statement-based replication (Statement-based replication): tells the SQL thread which libraries need to be replicated, and if multiple databases use this parameter multiple times, the SQL thread determines whether replication is needed based on the default database (USER DB1). If slave is set to-- replicate-do-db=sales, then the following statement does not update the january table in the sales library:

USE prices;UPDATE sales.january SET amount=amount+1000

Because the SQL thread determines whether replication is needed only based on the default database, none of the following SQL will be executed if it is found that the default database is not sales.

Row-based replication (Row-based replication): row-based replication is the opposite of statement-based replication. The SQL thread does not care what the default library is. It only cares whether the sql statement affects the specified database. If slave is set to-- replicate-do-db=sales, the following statement updates the february table in the sales library:

USE prices;UPDATE sales.february SET amount=amount+1000

This strategy has some minor side effects, such as the following sql:

USE db1;UPDATE db1.table1 SET col1 = 10, db2.table2 SET col2 = 20

If slave is set to-- replicate-do-db=db1 at this time, not only the db1.table1 table will change, but the db2.table2 will also change.

To sum up, considering database replication, cross-database sql must be cautious.

11mam Mustang, Mutual replicateMutual, Mustang, etc.

Create a filter on slave that specifies which databases are ignored and can be created using the CHANGE REPLICATION FILTER REPLICATION_IGNORE_DB command in version 5.7.3 and later. The rules for different formats of binlog are similar to-- replicate-do-db=db_name.

12mam Mutual replicateMutual, Tablekeeper dbroomname.tblroomname

Create a table-level replication filter that tells the SQL thread which tables in that library need to be replicated, whether cross-database updates or default data updates, and can be created using the CHANGE REPLICATION FILTER REPLICATION_DO_TABLE command in version 5.7.3 and later. Works only on sql statements, not on other objects in the database, such as stored programs.

13Mai Mutual replicateMutual replay tablehammer dbroomname.tblroomname

Creating filters for which tables are ignored, cross-database updates are still valid, and can be created using the CHANGE REPLICATION FILTER REPLICATION_IGNORE_TABLE command in version 5.7.3 and later. Works only on sql statements, not on other objects in the database, such as stored programs.

MySQL5.7 startup parameter tuning will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.