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

Introduction: this article mainly introduces the parameters that should be paid attention to in MySQL initialization, and these parameters should also be paid attention to for instance migration between different environments.

Note: the parameters described in this article are all in the [mysqld] section of the configuration file.

Server_id and log_bin, binlog_format

These system variables usually appear in pairs, and when we want to specify the log_bin option, we must also specify server_id.

Log_bin is a global variable that cannot be dynamically modified. The default is OFF. When we need to turn on binlog, we can set this parameter to binlog name or absolute path plus name.

Binlog_format specifies binlog format 5.7.7 and above defaults to ROW mode

Recommended settings:

# for each instance of server_id, it is recommended to set different log_bin and default to the data file directory server_id = 213306log_bin = mysqlbinbinlog_format = row or server_id = 213306log_bin = / data/mysql/logs/mysqlbinbinlog_format = rowsql_mode when the path is not specified.

This parameter controls that MySQL server runs in different SQL modes, and there will be different responses to different modes of requests sent by the client.

Sql_mode parameters are divided into global and session levels and can be modified dynamically

# sql_mode defaults to: sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION#. For more information on modification and the role of each mode, please refer to the official document: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

This parameter is recommended to remove ONLY_FULL_GROUP_BY. Strict or non-strict modes can be modified according to requirements. Note that this parameter should be consistent among different instances, otherwise there may be a situation in which a sql can be executed in this environment but not in another environment.

Max_connections

This parameter specifies the maximum number of connections for MySQL, which is a global variable that can be dynamically modified to default to 151s. It is recommended that you set it larger to prevent the error of full connection count.

Character_set_server

This parameter specifies the MySQL server character set, which is divided into global and session levels. The default value of version 5.7 can be dynamically modified to latin1.

It is recommended to set this parameter to utf8 or utf8mb4, which is consistent between different instances, especially master-slave instances.

Lower_case_table_names

This parameter controls whether the MySQL is case-sensitive and mainly affects the library name and table name.

Under Linux, this parameter defaults to 0, which is case-sensitive. Global variables cannot be dynamically modified. It is recommended to set it to 1.

Transaction_isolation

This parameter specifies which transaction isolation level MySQL server uses, and the default is that REPEATABLE-READ can be modified dynamically.

Which isolation level should be selected according to the application requirements can be changed to READ-COMMITTED, and it is recommended to be consistent among different instances.

Innodb_buffer_pool_size

This parameter controls the size of the InnoDB buffer pool. The default value is 134217728 bytes (128MB). Version 5.7.5 or above can be modified dynamically.

A buffer pool is where data and indexes are cached, and a cache pool as large as possible will ensure that memory rather than disk is used for most read operations. The typical values are 5-6GB (8GB RAM), 20-25GB (32GB RAM), and 100-120GB (128GB RAM). On a stand-alone database server, you can set this variable to 80% of the server's physical memory.

Innodb_log_file_size

This parameter defines the size (in bytes) of each log file in the redo log group, and the default is 48m that the global variable cannot be dynamically modified.

When MySQL server reads and writes frequently, it is recommended to increase this parameter to work with the innodb_log_files_in_group parameter.

Innodb_io_capacity and innodb_io_capacity_max

The innodb_io_capacity parameter sets the upper limit of the number of IO operations per second performed by the InnoDB background task. The default value is 200, which can be dynamically modified.

This parameter should be set to approximately the number of IO operations that the system can perform per second, that is, the IOPS of the system. This value depends on your system configuration.

When the MySQL server write operation overlags behind in refreshing dirty pages, the innodb_io_capacity_max parameter is the upper limit of the number of IO operations per second defined by the background task, and innodb_io_capacity_max is usually set to 2 times that of innodb_io_capacity.

If the MySQL server is a SSD high-speed disk, we can set innodb_io_capacity_max= 6000 and innodb_io_capacity = 3000 (50% of the maximum). Of course, it's a good idea to run sysbench or any other benchmark tool to benchmark disk throughput.

Other related parameters

In addition to the parameters listed above, there are some other parameters to pay attention to. I will summarize them as follows:

# disable all DNS parsing recommended that the only limit is that the GRANT statement must only use the IP address skip_name_resolve = 1#MySQL server to close the idle connection. The default number of seconds is 28800interactive_timeout =? Wait_timeout =? # Log recording time is consistent with the system log_timestamps = SYSTEM# some log related parameters log_error = error.logslow_query_log = 1slow_query_log_file = slow.loglong_query_time = 3#binlog log deletion policy unit is day default is 0 and does not automatically clean expire_logs_days = 30 # allow master to create function and synchronize to slave There are potential data security problems log_bin_trust_function_creators = export file security directory defaults to empty secure_file_priv = / tmp

Summary:

This article introduces some parameters that should be paid attention to in the initialization of MySQL, and gives the default values of the relevant parameters and whether they can be modified dynamically. It is recommended that the parameters that cannot be dynamically modified should be set reasonably before startup, which can reduce the number of maintenance restarts later.

Please keep the following points in mind before you modify the parameters:

Change one setting at a time! This is the only way to estimate whether the change is beneficial. Duplicate settings are not allowed in the configuration file. If you want to track changes, use versioning. You should rehearse in the test environment before making changes. Ensure that the position of the parameter is correct, the unit is reasonable, and does not conflict with other parameters. Don't do naive math, such as "my new server has 2x memory, I just need to set all the values to twice as much as before."

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.