[Client]
# Passwd = your_passwd
Port = 3306
Socket =/data/mysql. sock
[Mysqld]
# Global settings
Port = 3306
Socket =/data/mysql. sock
Basedir =/usr/local/mysql
Tmpdir =/data/mysql
Datadir =/data/mysql
Pid-file =/data/mysql/rongzhong. pid
# Skip external lock only for MyISAM
Skip-external-locking
# Disable tcp socket connections. Only unix/linux socket connections are allowed.
Skip_networking
# Disable DNS resolution to speed up connection
Skip_name_resolve
# Skip the authorization table and use it when you forget the root password
# Skip-grant-tables
# Default storage engine
# Default-storage-engine = MyISAM/INNODB
# Default Character Set
Character_set_server = utf8
# Maximum number of connections
Max_connections = 20480
# Maximum number of connections per user
Max_user_connection= 1024
# Number of seconds before the server closes an interactive connection. The default value is 28800.
Interactive_timeout = 7200
# Number of thread caches. Use idle connections to create a new connection. 1G-> 8 2G-> 16 3G-> 32> 3G-> 64
# You can add this value when many short connections exist.
Thread_cache_size = 64
# Buffer for data sorting (eg: order/group by) in a single thread (connection). One-time allocation during connection creation. The default value is 2 MB. This value can be increased when Sort_merge_passes is large.
Sort_buffer_size = 2 M
# Buffer for join operations on tables without indexes. The default value is 256 kb. One-time allocation during connection Creation
Join_buffer_size = 262144
# Specify the size of the connection listening queue. this parameter is invalid when the value exceeds OS tcp_max_syn_backlog/somaxconn. We recommend that you set this parameter to an integer smaller than 512.
Back_log = 512
# Table_open_cache number of tables that can be opened at the age
Table_open_cache = 256
# The maximum number of messages transmitted at a time, which is the maximum value of net_buffer_length
Max_allowed_packet = 8 M
# Bin-log
# Enable binary log
Log-bin = mysql-bin
Log-bin-index = mysql-bin.index
# Log format. Optional values: row, statement, and mixed. The default value is statement. mixed is recommended.
Binlog_format = mixed
# Maximum value of a binlog
Max_binlog_size = 1G
# Binlog cache size for a single connection. The default value is 1 MB.
Binlog_cache_size = 2 M
# Total binlog cache size
Max_binlog_cache_size = 2G
# Sync_binlog may be 0 or 1
# Whether the binlog is synchronized to the disk. If it is 0, it is handed over to the OS. If it is 1, it is synchronized immediately after the transaction ends, causing high IO consumption.
Sync_binlog = 0
# Slow-log
# Enable slow log
Slow-query-log = 1
# Specify the slow query duration
Long-query-time = 2
# Path
Slow-query-log-file =/data/mysql/slowlog/slow. log
# Queries with no indexes are recorded in slow log
Log_queries_not_using_indexes = 1
# Query cache
# Enable query cache
Query_cache_type = 1
# Maximum value for storing a single query cache. The default value is 1 MB.
Query_cache_limit = 1 M
# Minimum value for storing a single query cache. The default value is 4 K.
Query_cache_min_res_unit = 4096
# Maximum query value
Query_cache_size = 256 M
# MyISAM
# Index cache size, formula: Key_Size = key_number * (key_length + 4)/0.67
Key_buffer_size = 128 M
# Cache for sequential reading by a single thread. The default value is 128 kb.
Read_buffer_size = 2 M
# Cache for random reading by a single thread. The default value is 256 kb.
Read_rnd_buffer_size = 4 M
# Maximum Cache size allowed during index reconstruction. The default value is 8 Mb.
Myisam_sort_buffer_size = 64 M
# Specify the block size in the index cache. The default value is 1 K.
Key_cache_block_size = 1024
# Disabling index refresh
Delay_key_write = 1
# Innodb
# Enable independent tablespace
# Innodb_file_per_table = 1
# Set the innodb memory buffer size. In a mysql-only environment, we recommend that you set it to 60 to the server memory ~ 80%
Innodb_buffer_pool_size = 8G
# Set the number of threads that the OS can enter the innodb kernel. 2 * (number of CPU cores + number of disks) is recommended)
Innodb_thread_concurrency = 16
# Set innodb io behavior. Optional values: fsync/O_DSYNC/O_DIRECT. We recommend O_DIRECT to improve random write efficiency.
Innodb_flush_method = O_DIRECT
# Set the number of dirty pages that the mysql master process fl to the disk per second. The default value is 200.
# For systems with individual 5400 RPM or 7200 RPM drives, you might lower the value to the former default of 100
Innodb_io_capacity = 100
# Set the number of threads for writing dirty pages
Innodb_write_io_threads = 8
# Set the number of threads for reading file blocks from the disk
Innodb_read_io_threads = 8
# Set the transaction log buffer size
# Set the transaction log buffer size
Innodb_log_buffer_size = 8 M
# How to synchronize logs from a log thread to a disk; optional value: 0/1/2
#0: refresh every second 1: refresh every time a transaction is submitted 2: Hand it to OS for synchronization
Innodb_flush_log_at_trx_commit = 0
# Database dictionary information and table structure space
Innodb_additional_mem_pool_size = 16 M
# Enable the Double write Buffer area to ensure data integrity, but consume IO
Innodb_doublewrite = 1
[Mysqldump]
# Do not buffer the whole result set in memory before writing it to file. Required for dumping very large tables
Quick
Max_allowed_packet = 16 M
[Mysql]
No-auto-rehash
[Myisamchk]
Key_buffer_size = 512 M
Sort_buffer_size = 512 M
Read_buffer = 8 M
Write_buffer = 8 M
[Mysqlhotcopy]
Interactive-timeout
[Mysqld_safe]
# Increase the amount of open files allowed per process. Warning: Make
# Sure you have set the global system limit high enough! The high value
# Is required for a large number of opened tables
Open-files-limit = 8192