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

The following is a detailed description of the configuration of MySQL in my production environment:

[mysqld]

Mysql server configuration

Server-id=1

Service ID. The service ID of each service is different.

Log-bin=mysql-bin

Define the location and name of bin_log, and mysql operations record binary logs

Relay_log=mysql-realy-bin

Define the location and name of the relay_log, and the files recorded after parsing the master server binaries from the server

Relay_log_index=slave-mysql-realy-bin.index

Define the location and name of the relay_log_index

Expire_logs_days=15

Binlog deletion for more than 15 days

Binlog_format=mixed

Mysql replication mode is mixed mode

There are three main ways of mysql replication: SQL statement-based replication (statement-based replication, SBR), row-based replication (row-based replication, RBR), mixed mode replication (mixed-based replication, MBR). Accordingly, there are three formats for binlog: STATEMENT,ROW,MIXED.

① STATEMENT Mode (SBR)

Each sql statement that modifies the data is recorded in binlog. The advantage is that there is no need to record the data changes of every sql statement and every row, which reduces the amount of binlog log, saves IO, and improves performance. The disadvantage is that in some cases, it can lead to data inconsistency in master-slave (such as sleep () function, last_insert_id (), and user-defined functions (udf), etc.)

② ROW Mode (RBR)

Instead of recording the context of each sql statement, you only need to record which piece of data has been modified and how it has been modified. And there is no problem that the calls and triggers of stored procedures, or function, or trigger, in certain cases can not be copied correctly. The disadvantage is that a large number of logs will be generated, especially in the case of alter table.

③ MIXED Mode (MBR)

With the mixed use of the above two modes, the general replication uses STATEMENT mode to save binlog, and for operations that cannot be replicated in STATEMENT mode, use ROW mode to save binlog,MySQL will choose the log preservation method according to the SQL statement executed.

Auto-increment-increment=2

The value of each increment is changed to 2.

Auto-increment-offset= 1

The offset value when the numeric value is loaded for the first time is 1.

Innodb_flush_log_at_trx_commit=1

# 0: if the value of innodb_flush_log_at_trx_commit is 0MagneLog buffer, the log file will be written to disk every second, and no action will be done when the transaction is committed (the execution is performed by the master thread thread of mysql.

Every second in the main thread, the redo log buffer is written to the redo log file (REDO LOG) on disk. Regardless of whether the transaction has been committed or not) the default log file is ib_logfile0,ib_logfile1

# 1: when set to the default value of 1, log buffer is written to the log every time the transaction is committed.

# 2: if set to 2, each commit transaction will be logged, but the brush operation will not be performed. The log file is brushed at the same time every second. It is important to note that there is no guarantee that 100% will be flushed to disk every second, depending on the scheduling of the process.

# data is written to the transaction log every time a transaction is committed, and the write here only invokes the write operation of the file system, which is cached, so this write does not guarantee that the data has been written to the physical disk

The default value of 1 is to ensure a complete ACID. Of course, you can set this configuration item to a value other than 1 in exchange for higher performance, but you will lose 1 second of data in the event of a system crash.

# if set to 0, the last second transaction will be lost when the mysqld process crashes. Set to 2, the last second of data will be lost only in the event of an operating system crash or power outage. InnoDB ignores this value when doing a restore.

# Summary

Setting # to 1 is of course the safest, but the performance page is the worst (compared to the other two parameters, but not unacceptable). If the requirements for data consistency and integrity are not high, you can set it to 2. If you only want the most performance, such as a log server with high concurrent writes, set it to 0 to get higher performance.

Replicate-ignore-db=sys

Database ignored during replication

Datadir=/data/mysql_data

Data storage directory

Socket=/var/lib/mysql/mysql.sock

Specify the socket file path to the server

For mysql.sock, the purpose is that the program is on the same machine as mysqlserver and is available when initiating a local connection.

Character_set_server=utf8

Set character set

Sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

# there is an environment variable sql_mode in the mysql database, which defines the sql syntax, data parity, etc., that mysql should support.

# STRICT_TRANS_TABLES: in this mode, if a value cannot be inserted into a transaction table, the current operation is interrupted and there are no restrictions on non-transaction tables

# NO_ZERO_IN_DATE: this pattern affects whether months and days in dates are allowed to contain zeros. If this mode is turned on, 2016-01-00 is not allowed, but 0000-02-01 is allowed. Its actual behavior is affected by whether strict mode is enabled or not.

# NO_ZERO_DATE: set this value. Zero date is not allowed to be inserted in mysql database. Its actual behavior is affected by whether strictmode is enabled or not.

# ERROR_FOR_DIVISION_BY_ZERO: in the INSERT or UPDATE process, if the data is divided by zero, an error is generated instead of a warning. If the pattern is not given, MySQL returns NULL when the data is divided by zero

# NO_AUTO_CREATE_USER: prohibit GRANT from creating users with empty passwords

# NO_ENGINE_SUBSTITUTION: throw an error if the required storage engine is disabled or uncompiled. When this value is not set, it is replaced with the default storage engine and an exception is thrown

Symbolic-links=0

Symbolic links are not supported

Log-error=/var/log/mysqld.log

Error log file directory

Pid-file=/var/run/mysqld/mysqld.pid

Process file directory

Innodb_buffer_pool_size=8G

InnoDB buffer pool size (set here to half the memory size)

# InnoDB uses a buffer pool to hold indexes and raw data, unlike MyISAM

The larger the setting here, the less disk Imax O you need to access the data in the table.

# on a stand-alone database server, you can set this variable to 80% of the server's physical memory

# do not set too large, otherwise, the page change of the operating system may be bumpy due to the competition of physical memory.

# Note that on a 32-bit system, each process may be limited to 2-3.5G user-level memory limits.

# so don't set it too high.

Innodb_log_file_size=256M

Determine the size of the data log file, and larger settings can improve performance, but also increase the time it takes to recover the failed database

Innodb_flush_method=O_DIRECT

The method used by InnoDB to refresh the log is O_DSYNC

# tablespaces always use double write refresh method

# the default value is "fdatasync" and the other is "O_DSYNC".

# generally speaking, if you have a hardware RAID controller, and its independent cache uses write-back mechanism, and has battery power-off protection, then you should set it to O_DIRECT

# otherwise, it should be set to fdatasync in most cases

Max_connections=500

Maximum number of connections for MySQL

# if the number of concurrent connection requests from the server is relatively large, it is recommended to increase this value to increase the number of parallel connections. Of course, this is based on the case that the machine can support it, because if the number of connections is more, since MySQL will provide a connection buffer for each connection, the more memory will be spent, so you should adjust this value appropriately, not blindly increase the setting value. You can view the number of connections in the current state through the 'conn%' wildcard to determine the size of the value.

Innodb_autoextend_increment=128

Increment size of each automatic expansion

# the easiest way to increase the InnoDB system space is to configure autoextend.

# # this innodb_autoextend_increment setting does not affect individual tablespace files or regular tablespace files for each table. These files are automatically extended regardless of the innodb_autoextend_increment settings. The initial extension is small, and then it is extended in increments of 4MB.

The default is 8 MB. If there is a large amount of insert data at a time, it can be increased appropriately.

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.