17, MySQL production environment my.cnf configuration file parsing

[Client]

Port = 3306 #端口号

Socket =/ryzc/mysql/data/mysql.sock #sock文件路径

[Mysqld]

Port = 3306

Socket =/ryzc/mysql/data/mysql.sock # server-side sock file path

Pid-file =/ryzc/mysql/data/localhost.pid # PID file storage path

DataDir =/ryzc/mysql/data # data Storage directory

Log-error =/ryzc/mysql/data/err.log # Log Error directory

Slow-query-log-file =/ryzc/mysql/data/mysql-slow.log # Slow query log files and directories

Slow-query-log = 0 #开启慢查询日志, 0 is off, 1 is on, this is 5.6 configuration file, 5.5 is different from 5.6 open method

Long_query_time = 2 #慢查询时间 More than 2 seconds is slow query, default is 10 seconds

Innodb_data_home_dir =/ryzc/mysql/data/#这是InnoDB表的目录共用设置

Innodb_log_group_home_dir =/ryzc/mysql/data/#InnoDB The path to the log file

Master-info-file=master.info #master信息文件

Relay-log=relay-bin-2 # Trunk Log path and file name, can be customized

Relay-log-index=relay-bin.index # Trunk Log index file

Relay-log-info-file=relay-log.info # Trunk Log information file can be defined name and path, here only define Name

Replicate-ignore-db=mysql #用来设置不需要同步的库

Replicate-wild-ignore-table=mysql.% #用来设置不需要同步的库

Innodb_data_file_path=ibdata1:1g:autoextend

Log-slave-updates #这个参数用来配置从服务器的更新是否写入二进制日志, this option is not open by default, but if this slave server B is server A from the server, but also as Server C's primary server, then you need to develop this option, So that it can synchronize operations from server C to get its binary log

Binlog_cache_size = 4M # A transaction that, when not committed, logs the resulting log to the cache, and persists the log to disk when the transaction submission needs to be committed. Default Binlog_cache_size size 32K

Server-id = 2 #sever-id, the Lord is not the same.

Binlog-format = ROW

Expire_logs_days = Ten #超过10天的binlog日志文件删除

#max_binlog_cache_size = 8M # maximum Binlog cache size

#max_binlog_size = 512M # maximum Binlog size

Log-bin = mysql-bin # turn on Binlog log

Open_files_limit = 65535 # mysql Open file descriptor limit, default minimum 1024, when Open_files_limit is not configured, compare max_connections*5 and Ulimit-n values, Which big use which, when Open_file_limit is configured, compares the value of Open_files_limit and Max_connections*5, which is big with which.

Sql-mode= "No_auto_create_user,no_engine_substitution"

Skip-external-locking # #MySQL选项以避免外部锁定. This option is turned on by default

#lower_case_table_names #实现表名不区分大小写, use with caution

Skip-name-resolve # # Prevents MySQL from DNS parsing of external connections, and using this option can eliminate the time for DNS resolution for MySQL. However, it is important to note that if this option is turned on, all remote host connection authorizations will use IP address mode, otherwise MySQL will not be able to handle connection requests properly

Sync_binlog=1 #在MySQL中系统默认的设置是sync_binlog = 0, that is, do not make any mandatory disk refresh instructions, the performance is the best, but the risk is also the largest. As soon as the system crash, all binlog information in the Binlog_cache is lost. When set to "1", it is the safest and most performance-depleting setting. Because when set to 1, even if the system is crash, a transaction that is not completed in Binlog_cache is lost at most, without any substantial impact on the actual data.

Event_scheduler = on #开启事件调度器, the syntax of the event scheduler Event_scheduler mainly includes creating, modifying, deleting

Max_allowed_packet = 128M #MySQL根据配置文件会限制Server接受的数据包大小. Sometimes large inserts and updates are limited by the Max_allowed_packet parameter, causing the write or update to fail.

Table_open_cache = 2048 #表描述符缓存大小, can reduce the number of file open/close; default is 64, small

Wait_timeout = #指的是mysql在关闭一个非交互的连接之前所要等待的秒数, the value range is 1-2147483 (Windows), 1-31536000 (Linux), the default value of 28800.

Interactive_timeout = #指的是mysql在关闭一个交互的连接之前所要等待的秒数 (interactive connection, such as a connection in the MySQL GUI tool), whose value range changes with Wait_timeout, the default value is 28800.

Net_buffer_length = 1M #此参数指定了一个缓存区的大小 for storing SQL statements sent by the user.

read_buffer_size = 8M # mysql read-in buffer size.

Read_rnd_buffer_size = 8M #主要用于表顺序扫描的缓存大小

Max_connections = The maximum number of MySQL connections, if the server's concurrent connection requests are large, it is recommended to increase the number of concurrent connections, of course, this is based on the machine can support the case, because if the number of connections between MySQL will provide a connection for each connection Buffer, it will cost more memory, so adjust the value appropriately, you cannot blindly increase the value. The ' conn% ' wildcard character can be used to view the number of connections in the current state to decide the size of the value.

Max_connect_errors = 1000000 # for the same host, if there is an interrupt error connection that exceeds the number of values for the parameter, the host will be blocked from connecting. To unblock the host, execute: FLUSH host.

Max_heap_table_size = 128M # Defines the size of the memory table that the user can create. This value is used to calculate the maximum row value for the memory table. This variable supports dynamic change

Bulk_insert_buffer_size = 64M # BULK INSERT cache size, this parameter is for the MyISAM storage engine. For increased efficiency when inserting 100-1000+ records at once. The default value is 8M. Can be doubled to the size of the data volume.

Thread_cache_size = 1024x768 #线程缓存大小点

Symbolic-links = 0

Query_cache_type = 0 #查询结果放到查询缓存中, 0 is off, 1 is open

Myisam_sort_buffer_size = 8M # MyISAM Sets the size of the buffer that is used when the table is restored, when the buffer allocated by the MyISAM index is sorted in the repair table or in the CREATE INDEX or ALTER TABLE procedure.

Myisam_sort_buffer_size = 8M # MyISAM Sets the size of the buffer that is used when the table is restored, when the buffer allocated by the MyISAM index is sorted in the repair table or in the CREATE INDEX or ALTER TABLE procedure.

Myisam_max_sort_file_size = 10G # If the temporary file becomes more than the index, do not use the Quick Sort index method to create an index. Note: This parameter is given as a byte

Myisam_repair_threads = 1 # If the value is greater than 1, the MyISAM table index (each index in its own line range) is created in parallel during repair by sorting

Myisam_revocer # Controls the way Myisam finds and fixes errors.

Innodb_log_files_in_group = 3 #日志组中的日志文件数目. InnoDB writes the file in Ring mode (circular fashion). The value 3 is recommended for use. Set in number format in MY.CNF.

Thread_concurrency = #默认设置为 0, which means no limit on concurrency and is now set to 12 concurrent

innodb_file_io_threads = 6 file I/O thread in the #InnoDB. is typically set to 4, but under Windows you can set a larger value to increase disk I/O. Set in number format in MY.CNF.

Innodb_write_io_threads = 6 #写线程大小

Innodb_read_io_threads = 6 #读线程大小

Innodb_thread_concurrency = 8 # InnoDB will attempt to use the operating system process of the InnoDB service less than or equal to the value set here. The default value for this parameter is 8. If the computer system performance is low or the innodb_monitor shows a number of threads such as a waiter signal, this value should be set to a smaller point.

Innodb_buffer_pool_size = 15G #这个参数主要作用是缓存innodb表的索引, data, buffering when inserting data, size of dedicated MySQL server settings: OS memory 70%-80% best.

Innodb_log_file_size = 256M i# the size (in megabytes) of each log file in the log group.

Innodb_log_buffer_size = 16M #InnoDB The buffer size before the log is written to the log disk file. The ideal value is 1M to 8M.

Innodb_flush_log_at_trx_commit = 0 #通常设置为 1 means that the log has been written to disk before the transaction commits, the transaction can run longer, and the service will be able to recover after the crash. If you are willing to weaken this security, or if you are running a relatively small transaction, you can set it to 0 to reduce the disk I/O to the Write log file. This option is set to 0 by default.

Innodb_support_xa = 0 #你设置innodb_support_xa为0将减少磁盘刷新, facilitates serialization of disk data and binary logs.

Innodb_flush_method = O_direct #这个参数仅仅与 Unix-related. The default value for this parameter is Fdatasync. The other setting is O_dsync. This affects only the dump of the log file, and the data is dumped under Unix under Fsync.

Innodb_additional_mem_pool_size = 16M #InnoDB memory combination used to store data dictionary information and other internal data structures (internal data structures) (Me Mory Pool) size. The ideal value is 2M

Innodb_sort_buffer_size = 16M #排序缓冲大小

Innodb_open_files = #限制Innodb能打开的表的数据, if the table in the library is particularly numerous, please add this. This value is 300 by default.

Key_buffer_size = 2G #指定用于索引的缓冲区大小, increase it to get better processing index (for all read and multi-write), note: This parameter value setting is too large instead of the overall efficiency of the server is reduced

sort_buffer_size = 8M # MySQL performs sorting using the buffer size. If you want to increase the speed of the order by, first see if you can let MySQL use the index instead of the extra sort stage. If not, you can try increasing the size of the sort_buffer_size variable

Join_buffer_size = 2M # The buffer size that can be used by the Federated query operation, as with Sort_buffer_size, the allocated memory for this parameter is also per connection exclusive

Slave_skip_errors = All #跳过所有错误 to avoid failure from replication due to SQL error.

Query_cache_size = 64M #MySQL查询缓存大小

Query_cache_limit = 4M #指定单个查询能够使用的缓冲区大小, default 1M

tmp_table_size = 256M MySQL heap (stacked) table buffer size.

#default-storage-engine = InnoDB #默认存储引擎

#default-table-type = InnoDB #默认表类型

#innodb_local_wait_timeout = #等待锁超时时间

#innodb_file_per_table = 0 #独立表空间, 0 means off, and 1 means open

#innodb_max_dirty_pages_pct = #是用来控制在 InnoDB Buffer Pool can be used without writing the scale of the dirty Page in the data file (dirty data that has been repaired but has not been written to the data file from memory). The larger the scale value, the smaller the write operation from memory to disk, so that the disk IO of the write operation can be reduced to some extent.

#根据以往的经验, restarting the recovered data if it is more than 1GB, the boot speed will be relatively slow, almost unacceptable, so it is not recommended to 1gb/innodb_buffer_pool_size (GB) *100 this value. Of course, if you can tolerate a long boot time and want to minimize memory to disk flush, you can adjust this value to 90, but not more than 90

[Mysqldump]

Quick #通过quick参数可以加快mysql命令行下导出数据

Max_allowed_packet = 128M #服务器发送和接受的最大包长度

[MySQL]

No-auto-rehash #sql语句没有自动补全

[Myisamchk]

Key_buffer_size = 8M

Sort_buffer_size = 8M

Read_buffer = 8M

Write_buffer = 8M

[Mysqlhotcopy]

Interactive-timeout #服务器关闭交互式连接前等待活动的秒数, default 8 hours

This article is from the "Wsyht blog" blog, make sure to keep this source http://wsyht2015.blog.51cto.com/9014030/1790691

17, MySQL production environment my.cnf configuration file parsing