Example Analysis of MySQL configuration File my.cnf in production Library

This article mainly introduces the production library MySQL configuration file my.cnf example analysis, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

Os:contos 6.3

DB:5.6.16

Detailed explanation of MySQL database configuration file my.cnf

[client] # client

Port = 3306 # database port 3306

Socket = / my/log/mysql.sock # MySQL socket, which is connected with socket under multiple instances

[mysql]

Default-character-set = utf8 # character set

[mysqld]

# file

Datadir = / my/data # data file path

Pid-file= / my/log/mysql.pid # MySQL process

Log_error=/my/log/mysql.err # MySQL error log path

Port = 3306 # database port 3306

Socket = / my/log/mysql.sock # MySQL socket, which is connected with socket under multiple instances

Character_set_server = utf8 # character set

# skip-grant-tables # when you forget your root password, you can use-- skip-grant-tables to connect to MySQL and reset your password after startup.

Slow_query_log = 1 # enable slow query log

Slow_query_log_file = / my/log/slowq.log # slow query log path

Long_query_time = 1 # slow query time 1 second

# temp

Max_tmp_tables = 64 # maximum number of temporary tables opened

Max_heap_table_size = 1G # the size of the memory table created

Tmp_table_size = 512m # maximum value of memory temporary table

Slave_load_tmpdir = / my/log # Slave temporary directory path

Tmpdir = / my/log # temporary directory path

# session

Back_log = 8192 # MySQL indicates how many requests can be temporarily stacked before stopping new requests

Skip-name-resolve # skips the reverse parsing process

Max_allowed_packet = 32m # client receives maximum byte session value

Max_connections = 16000 # maximum connections

Max_connect_errors = 10000 # maximum number of error connections, which needs to be clarified through flush hosts when it is full

Wait_timeout = 28800 # wait timeout

Binlog_cache_size = 1m # binlog cache size

Table_open_cache = 1024 # buffer area size of table

Thread_concurrency = 4 # thread concurrency

Thread_cache_size = 128 # thread buffer size

Thread_handling = pool-of-threads # Open thread pool

Thread_pool_high_prio_mode = none # New connections are prioritized according to thread_pool_high_prio_mode

Thread_pool_idle_timeout = 28800 # Thread idle timeout

Thread_pool_oversubscribe = 20 # maximum number of tasks supported to run

# max_statement_time = 5000 # controls the maximum execution time of queries in MySQL (in milliseconds)

Query_cache_type = 0 # query whether the buffer is enabled 0: off 1: enable 2:demand

Query_cache_size = 0 # query buffer size

Key_buffer_size = 128m # Index buffer size

Myisam_sort_buffer_size = 16m # MyISAM sort buffer size

Read_buffer_size = 8m # Sequential read buffer impulse size

Read_rnd_buffer_size = 4m # Random read buffer size

Sort_buffer_size = 16m # sort buffer size

Join_buffer_size = 16m # join buffer size

# innodb

Default_storage_engine = InnoDB # default storage engine

Innodb_data_home_dir = / my/log # innodb storage engine shared tablespace path, namely: ibdata

Innodb_data_file_path = ibdata1:256M:autoextend # innodb storage engine size, self-incrementing

Innodb_log_group_home_dir = / my/log # ib_logfile log path

Innodb_log_files_in_group = 2 # ib_logfile, two for each group

Innodb_log_file_size = 512m # ib_logfile size

Innodb_log_buffer_size = 8m # log buffer size

When innodb_flush_log_at_trx_commit = 2 # equals 2, the hard disk is not written but written to the system cache, and the log is still written to the hard disk every second.

Innodb_flush_method = O_DIRECT # writes data to the file, only the data is written to disk, and the write operation is completed (write returns success)

Innodb_lock_wait_timeout = 50 # innodb engine lock wait timeout

# innodb_thread_concurrency = 16 # innodb thread concurrency

Innodb_buffer_pool_size = 44g # innodb storage engine buffer size

Innodb_additional_mem_pool_size = 20m # is used to set the memory pool size of data catalog information and other internal data structures stored by InnoDB

Innodb_io_capacity = 1500 # IO capability when controlling Innodb checkpoint

Innodb_use_native_aio = 1 # controls whether Native AIO is enabled. It is enabled by default. Official tests show that enabling Native AIO can improve recovery speed by 75%.

Innodb_file_per_table = 1 # innodb engine uses separate tablespaces

Innodb_open_files = 3000 # innodb Open Files

Innodb_print_all_deadlocks = 1 # print lock information in error

# others

Memlock = 1 # MySQL whether to use swap partition

# replication

Server-id = 1137 # server-id must be configured and unique when building master and slave

Log-bin = / my/log/mysql-bin # binary log file path

# binlog-ignore-db=mysql # filter the binary logs of the mysql library

Binlog_format = mixed # there are three binary log modes: row,statement,mixed

Expire_logs_days = 7 # time to delete expired log

Relay_log = / my/log/relay-bin # relay-log file path

# replicate_ignore_db = mysql # copy filter MySQL library

# slave_skip_errors = 1062 # there is a problem with master-slave synchronization, ignoring all errors of type 1062 from the slave library

Log_slave_updates = 1 # parameters used for cascade replication to satisfy M-S-S

Skip-slave-start # Slave will not start with the startup of MySQL

# read_only = 1 # read-only

Sync_binlog = 1 # forcibly write data from binlog_cache to disk

[mysqldump]

Default-character-set = utf8 # database character set

Thank you for reading this article carefully. I hope the article "sample Analysis of production Library MySQL configuration File my.cnf" shared by the editor will be helpful to you. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!