MySQL configuration file my.cnf case with explanation

[Client]

Port = 3306

Socket =/tmp/mysql.sock

[Mysqld]

Port = 3306

Socket =/tmp/mysql.sock

Log-error =/data/mysql/data/mysql.err

#默认存储引擎, the default is InnoDB after 5.5.

Default-storage-engine=innodb

#默认表的存储引擎, the default is InnoDB after 5.5.

Default_table_type = InnoDB

#安装MySQL的路径, code call path

Basedir=/usr/local/services/mysql

#全局数据文件及结构的存放位置, table data (including the InnoDB engine), indexes, logs (unless set separately) and other files will be stored here.

Datadir=/data/mysql/data

#innodb引擎的共享表空间数据文件根目录, if not set, the default is to use DataDir

#innodb_data_home_dir =/data/mysql/data

#默认字符集类型, set on demand

Character-set-server = UTF8

#关闭一个非交互的连接之前所要等待的秒数, the value range is 1-31536000 (Linux), the default value is 28800. Too long is not conducive to efficient use.

wait_timeout=100

#禁用DNS查找, but you cannot use the hostname in the MySQL authorization table, only use the IP

Skip-name-resolve

#跳过外部锁定 to prevent file directories from being available

Skip-external-locking

#使用InnoDB引擎独立表空间, each table is independent of the table space and does not use shared tablespace

innodb_file_per_table = 1

#在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中, the default value is 50. Concurrency high requires this value to be increased, but is too high to account for memory and cannot exceed the system setpoint.

Back_log = 103

#安全参数, performance-independent, prevent the setting value of the number of attempts to login after the failure of the client to prevent brute force password, low intranet security requirements can be appropriately increased

Max_connect_errors = 100000

#线程处理模式, No-threads (single-threaded processing), one-thread-per-connection (one thread per request), Dynamically-loaded/pool-of-threads (thread pool mode, Official MySQL and Percona parameter names are not the same)

#thread_handling = Pool-of-threads

#线程池中连接超时的时间, the default 500ms, lowering can improve the utilization of the connection pool

#thread_pool_stall_limit = 200

#索引缓冲区的大小, only works on the MyISAM table, but the system tables and the temporary disk tables are still MyISAM tables, and to speed up these operations, this value is still not too low.

Key_buffer_size = 32M

#批量插入缓存大小, only works on the MyISAM table and is useful for efficiency when inserting 100-1000+ records at once. The default value is 8M

Bulk_insert_buffer_size = 64M

#有时候较大的插入和更新会受max_allowed_packet parameter limit, which causes the write or update to fail, is configured according to the actual situation.

Max_allowed_packet = 32M

#允许存到缓存里的表的数量, increasing the operations that can improve the frequent opening and closing of tables, has the meaning of accelerating SQL speed, the default is 64

Table_open_cache = 512

#排序缓存, session-level memory caching, general OLTP more than 512K performance increase is not obvious, the recommended value is 56k-1m, if it is Data Warehouse (OLAP) Set 8M above can further improve the effect

Sort_buffer_size = 512K

#TCP/IP and socket communication buffer size, creating rows up to net_buffer_length, primarily affecting import and export

Net_buffer_length = 8K

#会话级别内存缓存, the use of buffer size when scanning table data in sequential read scan, if many sequential scans, can be appropriately increased, only the MyISAM table function,

Read_buffer_size = 1M

#会话级别内存缓存, the size of the buffer is used when scanning the table data in a random read-scan manner, and it will also work as a new feature MRR buffer

Read_rnd_buffer_size = 16M

#MyISAM表发生变化时重新排序所需的缓冲, the Temporary disk table is a MyISAM table, which is not low, especially for data warehousing (OLAP), to speed up the ordering of temporary tables.

Myisam_sort_buffer_size = 128M

#MySQL重建索引时所允许的最大临时文件的最大大小

Myisam_max_sort_file_size = 10G

#自动检查和修复没有适当关闭的 MyISAM Table

Myisam_repair_threads = 1

#MyISAM表在打开的时候被自动检查, the table is repaired if the table is improperly closed.

Myisam_recover

#启用binlog, and specify the storage location, default to the data directory, the main library must be open, from the library can be properly closed

Log-bin=/data/mysql/data/mysql-bin

#binlog类型, it is recommended to use mixed or row

Binlog_format=mixed

#数据库ID号, mainly for master-slave replication related

Server-id = 1

#共享表空间, at least 1G

Innodb_data_file_path = Ibdata1:1g:autoextend

#实例级别内存参数innodb缓存总量 (including some other caches), single-instance recommendation 50%-70%, multi-instance recommendation 20% or so

Innodb_buffer_pool_size = 830M

#设置 the memory pool size of the data directory information stored by the InnoDB and other internal data structures. The more tables you have, the more memory you need to allocate. If the set value is exceeded, a warning message is written in the error log. The default value is 1MB.

Innodb_additional_mem_pool_size = 2M

#默认为关闭OFF. If this parameter is turned on, MySQL loads the local hot data into the InnoDB buffer pool when the MySQL service is started.

Innodb_buffer_pool_load_at_startup = 1

#默认为关闭OFF. Dumps the hot data to the local disk when it is closed.

Innodb_buffer_pool_dump_at_shutdown = 1

#redolog硬盘参数, recommended 1-2g or above

Innodb_log_file_size = 1024M

#redolog内存参数, 8-32m can

Innodb_log_buffer_size = 16M

#DB中设置几组redolog, the default is 2, setting more can reduce the disk pressure, especially the case of large transactions

Innodb_log_files_in_group = 3

#redolog存放目录, it is recommended not to set, ib_logfile default exists in the data file directory, using it is mainly distributed disk pressure

#innodb_log_group_home_dir =/data/mysql/data

#控制innodb数据文件及redo log Open, brush write mode, there are three values: Fdatasync (Default and fastest), O_dsync,o_direct (Disable the system cache, slow but most secure), see the requirements set

#innodb_flush_method = O_direct

#事务隔离级别, a total of four kinds: read-uncommitted, read-committed, Repeatable-read, SERIALIZABLE, default is RR

Transaction_isolation = Repeatable-read

#事务提交写到硬盘的频率, 0 is a quantitative re-write, 1 is immediately written (the slowest speed is the safest), and 2 is determined by the system

Innodb_flush_log_at_trx_commit = 2

#刷新binlog的频率, 0 is determined by the system when the refresh, 1 each transaction is refreshed once (the most secure), N (the other number) every n transactions are refreshed once, if the group commit is not supported then the N event is flushed once, if it is support group commit, Then it means saving up to N Binlog group to refresh once, that is, the performance of support group commit is better and the number of IO is reduced.

Sync_binlog = 0

#行格式定义, there are antelope (support redundancy, compact row format) and Barracuda (support data compression, dynamic row format, improve blob,text field storage layout), to support the compression type, you must first define the Barracuda.

Innodb_file_format_max = Barracuda

#当脏页占了多少百分比后就开始刷新脏页, the more you write, the lower you should set the gap between the Log sequence Number,log flushed up and the LSN.

innodb_max_dirty_pages_pct = 35

#刷新到磁盘的脏页数量, according to the performance of hard disk, mechanical hard disk 1000 or so, SSD hard disk 4-5 or so, PCIE-SSD can be more than 120,000

innodb_io_capacity = 1000

#最大刷新到磁盘的脏页数量, set according to the above parameters, but must be more than he

Innodb_io_capacity_max = 2000

#doublewrite开关, if the storage device supports atomic write, can be turned off, the write speed increases significantly, other cases are recommended to turn on

Innodb_doublewrite = 1

#限制并发线程的数量, once the number of execution threads reaches this limit, additional threads sleep for microseconds before they are placed in the queue, and can be configured to configure the sleep time by setting the parameter Innodb_thread_sleep_delay. The default value is 0, which means that the number of concurrent execution of threads is not limited by default.

innodb_thread_concurrency = 0

#在新版本mysql中, Query_cache has lost meaning and occasionally causes query_cache locks, additional overhead, and recommends direct shutdown

Query_cache_size=0

Query_cache_type=0

#数据结构ddl操作的锁的等待时间

Lock_wait_timeout = 300

#innodb的dml操作的行级锁的等待时间, the InnoDB has a spin lock, and the wait time is too long to consume resources

Innodb_lock_wait_timeout = 10

#默认值为OFF, if a transaction is Gartha, the action performed on the previous statement is rolled back. If you set on, the entire transaction is rolled back. The need to focus on transactional consistency opens

Innodb_rollback_on_timeout = 1

#用来配置从服务器的更新是否写入二进制日志, if there is a hierarchy from the library must be open, not recommended to close, can effectively improve performance

Log_slave_updates

#当slave从库宕机后, if Relay-log is damaged, causing some relay logs to not be processed, all relay-log that are not executed are automatically discarded and the log is retrieved from master again, which guarantees the integrity of the relay-log. This feature is turned off by default and is recommended for effective data consistency.

Relay_log_recovery = 1

#临时表缓存, session-level memory cache, after which the temporary table will go to disk, depending on the size of the table to measure the setting, the temporary table engine on the disk is MyISAM by default, the memory temp table engine is not read and write

tmp_table_size=320m

#临时表缓存, session-level memory cache, and tmp_table_size together limit the size of the temporary table, the maximum number of rows of memory tables that can be created, usually by default 16M (not set).

#max_heap_table_size =96m

#如果临时表实在太大, the disk space pressure increases, you need to consider setting the temporary table storage location, the default is/tmp

#tmpdir =/tmp

#最大连接数限制, it is recommended to set it to 80% of the history's maximum number of connections, or it may all be jammed.

max_connections=638

#可以重新利用保存在缓存中线程的数量. When a thread is disconnected and does not shut down immediately, it is saved to the cache waiting to be re-used, the concurrency is highly recommended, and is set to threads_connected, preferably less than 200, because it consumes memory.

Thread_cache_size = 51

#会话级别缓存, according to the actual situation of configuration, join more on the big increase.

Join_buffer_size = 32M

#开启慢查询参数, 0 off, 1 open

Slow_query_log = 0

#设置慢查询存储路径

Slow_query_log_file =/tmp/slow_querys.log

#慢查询记录时间, less than 0.1 seconds recommended

Long_query_time = 0.1

#用来设置是否记录没有使用索引的查询到慢查询记录, look for the need to open.

#log_queries_not_using_indexes = OFF

#管理指令也会被记录到慢查询. Like Optimeze table, ALTER table, see demand Open

#log-slow-admin-statements = OFF

#全局最大打开文件数, can not exceed the maximum number of files set by the system, otherwise invalid

Open_files_limit = 65535

#innodb引擎最大打开文件数, you cannot exceed the global maximum number of files.

Innodb_open_files = 65535

#binlog会话级别内存缓存, when a transaction is not committed, the resulting log is logged to the cache, and the log is persisted to disk when the transaction needs to be committed.

Binlog_cache_size = 4M

#最大binlog日志的缓存区大小, the total number of binlog_cache_size for all sessions cannot exceed this value, the default is the maximum value, in a 32-bit system is the 4g,64 bit is 16P, set it to prevent MySQL memory overflow

Max_binlog_cache_size = 4G

#binlog二进制日志写入给定值, the default value is 1GB, the maximum can not be greater than 1G, according to the actual situation settings, the file is too large to open, but if you are using large transactions, the binary log will still exceed the limit

Max_binlog_size = 500M

#binlog保存天数, expired automatic deletion

Expire_logs_days=7

#mysql5.6 After the parameter is turned on, all deadlock will be recorded in Error_log, and as a DBA you should know how much deadlock information

#innodb_print_all_deadlocks = 1

Prior to #在5.6.2, the Slave recorded master information and slave application Binlog information are stored in the file, namely Master.info and Relay-log.info. After version 5.6.2, allow logging to table

#master_info_repository = TABLE

#同上信息

#relay_log_info_repository = TABLE

#GTID模式, 5.6 new features, new replication mode, need to open, binlog to change to row

#gtid_mode = On

#强制GTID的一致性, commonly used with the above parameters, but be cautious to open, only allow to ensure transaction security, and can be logged SQL statements are executed, like the CREATE TABLE ... select and create temporarytable statements, and SQL statements or transactions that update both the transaction table and the non-transactional table are not allowed to execute.

#enforce_gtid_consistency = 1

#关闭numa功能, 5.6.27 new parameters

#innodb_numa_interleave = 1

#用于从flush队列中取事务的超时时间, this is primarily to prevent concurrent transactions from being too high, causing the rt of some transactions to rise, in microseconds, by default to 0.

#binlog_max_flush_queue_time = 0

[Mysqldump]

Quick

Max_allowed_packet = 32M

[MySQL]

No-auto-rehash

prompt= "\[email protected]\h \r:\m:\s [\d]>"

[Myisamchk]

Key_buffer_size = 20M

Sort_buffer_size = 20M

Read_buffer = 2M

Write_buffer = 2M

[Mysqlhotcopy]

Interactive-timeout

MySQL configuration file my.cnf case with explanation