my.cnf(my.ini)重要参数优化配置说明

# 以下选项会被MySQL客户端应用读取。 # 注意只有MySQL附带的客户端应用程序保证可以读取这段内容。 # 如果你想你自己的MySQL应用程序获取这些值。 # 需要在MySQL客户端库初始化的时候指定这些选项。[client]port    = 3306socket   = /usr/local/mysql/mysql.sock# MySQL 服务端[mysqld]#默认存储引擎INNODBdefault-storage-engine=INNODB#GROUP_CONCAT长度group_concat_max_len =99999#端口号port    = 3306#socket位置socket   = /usr/local/mysql/mysql.sock #pid写入文件位置pid-file    = /usr/local/mysql/mysqld.pid#数据库文件位置datadir     = /home/data/mysql/datauser    = mysql#SQL模式具体查阅相关资料sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES#当外部锁定（external-locking）起作用时，每个进程若要访问数据表,#则必须等待之前的进程完成操作并解除锁定。由于服务器访问数据表时经常需要等待解锁,#因此在单服务器环境下external locking会让MySQL性能下降。#所以在很多Linux发行版的源中，MySQL配置文件中默认使用了skip-external-locking来避免external locking。skip-external-locking#跳过DNS反向解析skip-name-resolve#关闭TIMESTAMP类型默认值explicit_defaults_for_timestamp#不受client字符集影响，保证sever端字符集skip-character-set-client-handshake#初始连接字符集UTF8init-connect='SET NAMES utf8'#默认数据库字符集character-set-server=utf8#查询缓存0,1,2，分别代表了off、on、demandquery_cache_type = 1#单位秒,握手时间超过connect_timeout,连接请求将会被拒绝connect_timeout = 20#设置在多少秒没收到主库传来的Binary Logs events之后,从库认为网络超时,Slave IO线程会重新连接主库。#该参数的默认值是3600s ，然而时间太久会造成数据库延迟或者主备库直接的链接异常不能及时发现。#将 slave_net_timeout 设得很短会造成 Master 没有数据更新时频繁重连。一般线上设置为5s slave_net_timeout = 30#这个参数用来配置从服务器的更新是否写入二进制日志，这个选项默认是不打开的，#但是，如果这个从服务器B是服务器A的从服务器，同时还作为服务器C的主服务器，那么就需要开发这个选项，#这样它的从服务器C才能获得它的二进制日志进行同步操作log-slave-updates=1#用于slave服务器，io线程会把server id与自己相同的event写入日志，与log-slave-updates选项冲突replicate-same-server-id=0# 对了生成唯一的server_id我想到了，大家ip地址唯一，比如10.112.87.91 ，就直接去掉点号在后面加上编号01或者02或者03（加上2位数编号是怕一台物理机沧岸嗍道,主从复制需要server-id来标识的# 用10112879101做server_id就可以了。server_id=10112879101# 打开二进制日志功能. # 在复制(replication)配置中,作为MASTER主服务器必须打开此项 # 如果你需要从你最后的备份中做基于时间点的恢复,你也同样需要二进制日志log-bin =/home/data/mysql/binlog/mysql-bin.log#relay-log日志relay-log=mysql-relay-bin#master-info-repository以及relay-log-info-repository打开以启用崩溃安全的二进制日志/从服务器功能（在事务表而不是平面文件中存储信息）master-info-repository=TABLErelay-log-info-repository=TABLE#不写入binlog二进制日志中的数据库binlog-ignore-db=mysql       # No sync databasesbinlog-ignore-db=test        # No sync databasesbinlog-ignore-db=information_schema   # No sync databasesbinlog-ignore-db=performance_schema   # No sync databases#写入binlog二进制日志中数据库binlog-do-db=business_dbbinlog-do-db=user_dbbinlog-do-db=plocc_system#15滚动清理binlogexpire-logs-days=15max_binlog_size = 1073741824      # Bin logs size ( 1G )#使binlog在每1000次binlog写入后与硬盘同步sync_binlog = 1000#指定只复制哪个库的数据replicate-do-db=business_dbreplicate-do-db=user_dbreplicate-do-db=plocc_system#开启事件调度器Event Schedulerevent_scheduler=1#MySQL能暂存的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用。#如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，#该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源back_log = 500#整个数据库最大连接（用户）数max_connections = 6000#一个用户的最大连接数max_user_connection=3000 # 每个客户端连接最大的错误允许数量,如果达到了此限制. # 这个客户端将会被MySQL服务阻止直到执行了”FLUSH HOSTS” 或者服务重启 # 非法的密码以及其他在链接时的错误会增加此值. # 查看 “Aborted_connects” 状态来获取全局计数器max_connect_errors = 1844674407370954751#表描述符缓存大小，可减少文件打开/关闭次数table_open_cache = 2048# 服务所能处理的请求包的最大大小以及服务所能处理的最大的请求大小(当与大的BLOB字段一起工作时相当必要) # 每个连接独立的大小.大小动态增加max_allowed_packet = 64M# 在一个事务中binlog为了记录SQL状态所持有的cache大小 # 如果你经常使用大的,多声明的事务,你可以增加此值来获取更大的性能. # 所有从事务来的状态都将被缓冲在binlog缓冲中然后在提交后一次性写入到binlog中 # 如果事务比此值大, 会使用磁盘上的临时文件来替代. # 此缓冲在每个连接的事务第一次更新状态时被创建binlog_cache_size = 1M# 独立的内存表所允许的最大容量. # 此选项为了防止意外创建一个超大的内存表导致用尽所有的内存资源.max_heap_table_size = 1342177280# 排序缓冲被用来处理类似ORDER BY以及GROUP BY队列所引起的排序 # 如果排序后的数据无法放入排序缓冲, # 一个用来替代的基于磁盘的合并分类会被使用 # 查看 “Sort_merge_passes” 状态变量. # 在排序发生时由每个线程分配sort_buffer_size = 8M# 此缓冲被使用来优化全联合(full JOINs 不带索引的联合). # 类似的联合在极大多数情况下有非常糟糕的性能表现, # 但是将此值设大能够减轻性能影响. # 通过 “Select_full_join” 状态变量查看全联合的数量 # 当全联合发生时,在每个线程中分配 join_buffer_size = 8M# 我们在cache中保留多少线程用于重用 # 当一个客户端断开连接后,如果cache中的线程还少于thread_cache_size, # 则客户端线程被放入cache中. # 这可以在你需要大量新连接的时候极大的减少线程创建的开销 # (一般来说如果你有好的线程模型的话,这不会有明显的性能提升.)thread_cache_size = 128# 此允许应用程序给予线程系统一个提示在同一时间给予渴望被运行的线程的数量. # 此值只对于支持 thread_concurrency() 函数的系统有意义( 例如Sun Solaris). # 你可可以尝试使用 [CPU数量]*(2..4) 来作为thread_concurrency的值thread_concurrency = 8# 查询缓冲常被用来缓冲 SELECT 的结果并且在下一次同样查询的时候不再执行直接返回结果. # 打开查询缓冲可以极大的提高服务器速度, 如果你有大量的相同的查询并且很少修改表. # 查看 “Qcache_lowmem_prunes” 状态变量来检查是否当前值对于你的负载来说是否足够高. # 注意: 在你表经常变化的情况下或者如果你的查询原文每次都不同, # 查询缓冲也许引起性能下降而不是性能提升.query_cache_size = 64M# 只有小于此设定值的结果才会被缓冲 # 此设置用来保护查询缓冲,防止一个极大的结果集将其他所有的查询结果都覆盖query_cache_limit = 2M# 被全文检索索引的最小的字长. # 你也许希望减少它,如果你需要搜索更短字的时候. # 注意在你修改此值之后, # 你需要重建你的 FULLTEXT 索引ft_min_word_len = 4# 线程使用的堆大小. 此容量的内存在每次连接时被预留. # MySQL 本身常不会需要超过64K的内存 # 如果你使用你自己的需要大量堆的UDF函数 # 或者你的操作系统对于某些操作需要更多的堆, # 你也许需要将其设置的更高一点. thread_stack = 192K# 设定默认的事务隔离级别.可用的级别如下: # READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLEtransaction_isolation = READ-COMMITTED# 内部(内存中)临时表的最大大小 # 如果一个表增长到比此值更大,将会自动转换为基于磁盘的表. # 此限制是针对单个表的,而不是总和.tmp_table_size = 1342177280#binlog日志类型--混合型binlog_format=mixed#开启慢查询日志slow_query_log#文件格式log_output = FILE# 所有的使用了比这个时间(以秒为单位)更多的查询会被认为是慢速查询. # 不要在这里使用”0″, 否则会导致所有的查询,甚至非常快的查询页被记录下来(由于MySQL 目前时间的精确度只能达到秒的级别). long_query_time = 0.5#慢查询日志位置slow_query_log_file=/usr/local/mysql/mysqld_slow.log#指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度#******************** MyISAM 相关选项********************************# 关键词缓冲的大小, 一般用来缓冲MyISAM表的索引块. # 不要将其设置大于你可用内存的30%, # 因为一部分内存同样被OS用来缓冲行数据 # 甚至在你并不使用MyISAM 表的情况下, 你也需要仍旧设置起 8-64M 内存由于它同样会被内部临时磁盘表使用.key_buffer_size = 32M# 用来做MyISAM表全表扫描的缓冲大小. # 当全表扫描需要时,在对应线程中分配.read_buffer_size = 2M# 当在排序之后,从一个已经排序好的序列中读取行时,行数据将从这个缓冲中读取来防止磁盘寻道. # 如果你增高此值,可以提高很多ORDER BY的性能. # 当需要时由每个线程分配 read_rnd_buffer_size = 8M# MyISAM 使用特殊的类似树的cache来使得突发插入 # (这些插入是,INSERT … SELECT, INSERT … VALUES (…), (…), …, 以及 LOAD DATA # INFILE) 更快. 此变量限制每个进程中缓冲树的字节数. # 设置为 0 会关闭此优化. # 为了最优化不要将此值设置大于 “key_buffer_size”. # 当突发插入被检测到时此缓冲将被分配.bulk_insert_buffer_size = 16M# 此缓冲当MySQL需要在 REPAIR, OPTIMIZE, ALTER 以及 LOAD DATA INFILE 到一个空表中引起重建索引时被分配. # 这在每个线程中被分配.所以在设置大值时需要小心.myisam_sort_buffer_size = 128M# MySQL重建索引时所允许的最大临时文件的大小 (当 REPAIR, ALTER TABLE 或者 LOAD DATA INFILE). # 如果文件大小比此值更大,索引会通过键值缓冲创建(更慢) myisam_max_sort_file_size = 1G# 如果一个表拥有超过一个索引, MyISAM 可以通过并行排序使用超过一个线程去修复他们. # 这对于拥有多个CPU以及大量内存情况的用户,是一个很好的选择.myisam_repair_threads = 1# 自动检查和修复没有适当关闭的 MyISAM 表. myisam_recover# *************** INNODB 相关选项 ********************* # 如果你的MySQL服务包含InnoDB支持但是并不打算使用的话, # 使用此选项会节省内存以及磁盘空间,并且加速某些部分 #skip-innodb# #####[关键项]# InnoDB使用一个缓冲池来保存索引和原始数据, 不像 MyISAM. # 这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少. # 在一个独立使用的数据库服务器上,你可以设置这个变量到服务器物理内存大小的80% # 不要设置过大,否则,由于物理内存的竞争可能导致操作系统的换页颠簸. # 注意在32位系统上你每个进程可能被限制在 2-3.5G 用户层面内存限制, # 所以不要设置的太高.innodb_buffer_pool_size = 700m #1G# InnoDB 将数据保存在一个或者多个数据文件中成为表空间. # 如果你只有单个逻辑驱动保存你的数据,一个单个的自增文件就足够好了. # 其他情况下.每个设备一个文件一般都是个好的选择. # 你也可以配置InnoDB来使用裸盘分区 – 请参考手册来获取更多相关内容innodb_data_file_path = IBdata1:1024M;IBdata2:1024M:autoextend# 设置此选项如果你希望InnoDB表空间文件被保存在其他分区. # 默认保存在MySQL的datadir中. #innodb_data_home_dir = # 用来同步IO操作的IO线程的数量. This value is # 此值在Unix下被硬编码为4,但是在Windows磁盘I/O可能在一个大数值下表现的更好. innodb_file_io_threads = 4# 在InnoDb核心内的允许线程数量. # 最优值依赖于应用程序,硬件以及操作系统的调度方式. # 过高的值可能导致线程的互斥颠簸.innodb_thread_concurrency = 16# #####[关键项]# 如果设置为1 ,InnoDB会在每次提交后刷新(fsync)事务日志到磁盘上, # 这提供了完整的ACID行为. # 如果你愿意对事务安全折衷, 并且你正在运行一个小的事务, 你可以设置此值到0或者2来减少由事务日志引起的磁盘I/O # 0代表日志只大约每秒写入日志文件并且日志文件刷新到磁盘. # 2代表日志写入日志文件在每次提交后,但是日志文件只有大约每秒才会刷新到磁盘上. # --------------------# (说明：如果是游戏服务器，建议此值设置为2；如果是对数据安全要求极高的应用，建议设置为1；# 设置为0性能最高，但如果发生故障，数据可能会有丢失的危险!# 默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入(flush)硬盘，这是很费时的。# 特别是使用电池供电缓存(Battery backed up cache)时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，# 它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。# 设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统挂了时才可能丢数据)innodb_flush_log_at_trx_commit = 2# 用来缓冲日志数据的缓冲区的大小. # 当此值快满时, InnoDB将必须刷新数据到磁盘上. # 由于基本上每秒都会刷新一次,所以没有必要将此值设置的太大(甚至对于长事务而言) innodb_log_buffer_size = 16M# 在日志组中每个日志文件的大小. # 你应该设置日志文件总合大小到你缓冲池大小的25%~100% # 来避免在日志文件覆写上不必要的缓冲池刷新行为. # 不论如何, 请注意一个大的日志文件大小会增加恢复进程所需要的时间innodb_log_file_size = 1024M# 在日志组中的文件总数. # 通常来说2~3是比较好的. innodb_log_files_in_group = 3# InnoDB的日志文件所在位置. 默认是MySQL的datadir. # 你可以将其指定到一个独立的硬盘上或者一个RAID1卷上来提高其性能 #innodb_log_group_home_dir# 在InnoDB缓冲池中最大允许的脏页面的比例. # 如果达到限额, InnoDB会开始刷新他们防止他们妨碍到干净数据页面. # 这是一个软限制,不被保证绝对执行. innodb_max_dirty_pages_pct = 90# InnoDB用来刷新日志的方法. # 表空间总是使用双重写入刷新方法 # 默认值是 “fdatasync”, 另一个是 “O_DSYNC”. innodb_flush_method=O_DSYNC# 在被回滚前,一个InnoDB的事务应该等待一个锁被批准多久. # InnoDB在其拥有的锁表中自动检测事务死锁并且回滚事务. # 如果你使用 LOCK TABLES 指令, 或者在同样事务中使用除了InnoDB以外的其他事务安全的存储引擎 # 那么一个死锁可能发生而InnoDB无法注意到. # 这种情况下这个timeout值对于解决这种问题就非常有帮助. innodb_lock_wait_timeout = 30[mysqldump]# 不要在将内存中的整个结果写入磁盘之前缓存. 在导出非常巨大的表时需要此项quickmax_allowed_packet = 64M[mysql]no-auto-rehash[myisamchk]key_buffer_size = 512Msort_buffer_size = 512Mread_buffer = 8Mwrite_buffer = 8M[mysqlhotcopy]interactive-timeout[mysqld_safe]# 增加每个进程的可打开文件数量. # 警告: 确认你已经将全系统限制设定的足够高! # 打开大量表需要将此值设大open-files-limit = 8192log-error=/usr/local/mysql/mysqld.logpid-file=/usr/local/mysql/mysqld.pid