Mysql配置参数详解以及优化配置分析

1、mysql有以下几种日志： 错误日志： log-err 查询日志： log 慢查询日志: log-slow-queries 更新日志: log-update 二进制日志： log-bin要把日志生成在 /var/log 目录下(是系统日志存放的地方，只有 root 账号有写权限)，需要 MySQL 进程对这个目录有读写权限，一般是不这么做的，也考虑到安全问题，包括 MySQL 本身的数据安全，因为对 MySQL 的所有操作，都会记录到常规查询日志。MySQL 的日志就不要用 /var/log/ 目录下。-2 fdefault-character-set = utf8port = 3306soc

2、ket = /tmp/mysql.sockno-auto-rehash#default-character-set = utf8user = mysqlport = 3306socket = /tmp/mysql.sockbasedir = /usr/local/webserver/mysqldatadir = /data0/mysql/3306/dataopen_files_limit = 10240back_log = 600max_connections = 3000 实际MySQL服务器允许的最大连接数16384；max_connect_errors = 6000 可以允许多少个错误连

3、接table_cache = 1024 高速缓存的大小.table_cache的值在2G内存以下的机器中的值默认时256到 512，如果机器有4G内存,则默认这个值是2048，但这决意味着机器内存越大，这个值应该越大，因为table_cache加大后，使得mysql对 SQL响应的速度更快了，不可避免的会产生更多的死锁（dead lock），这样反而使得数据库整个一套操作慢了下来，严重影响性能。external-locking = FALSEmax_allowed_packet = 32Msort_buffer_size = 2M 是一个connection级参数，在每个connection第

4、一次需要使用这个buffer的时候，一次性分配设置的内存.并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。join_buffer_size = 2Mthread_cache_size = 64 重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能 根据物理内存设置规则如下： 1G - 8 2G - 16 3G - 32 3G - 64thread_concur

5、rency = 16 对mysql的性能影响很大, 在多个cpu(或多核)的情况下，错误设置了thread_concurrency的值, 会导致mysql不能充分利用多cpu(或多核), 出现同一时刻只能一个cpu(或核)在工作的情况。 应设为CPU核数的2倍. 比如有一个双核的CPU, 那么thread_concurrency的应该为4; 2个双核的cpu, thread_concurrency的值应为8.query_cache_size = 32M 缓存机制简单的说就是缓存sql文本及查询结果，如果运行相同的sql，服务器直接从缓存中取到结果，而不需要再去解析和执行sql。如果表更改了，那

6、么使用这个表的所有缓冲查询将不再有效，查询缓存值的相关条目被清空query_cache_limit = 2M 单个查询能够使用的缓冲区大小.缺省为1Mquery_cache_min_res_unit = 2k 它指定分配缓冲区空间的最小单位，缺省为4K。检查状态值Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多，这就表明查询结果都比较小，此时需要减小query_cache_min_res_unitdefault-storage-engine = MyISAMdefault_table_type = MyISAMthread_stack = 192Ktransacti

7、on_isolation = READ-COMMITTED 设置所有连接的默认事务隔离级tmp_table_size = 256M 临时HEAP数据表的最大长度max_heap_table_size = 256M HEAP数据表(内存表)的最大长度(默认设置是16M);long_query_time = 1log_long_formatlog-bin = /data0/mysql/3306/binlogbinlog_cache_size = 8M 为binary log指定在查询请求处理过程中SQL 查询语句使用的缓存大小binlog_format = MIXEDmax_binlog_cach

8、e_size = 8Mmax_binlog_size = 512Mexpire_logs_days = 7key_buffer_size = 256M 索引缓冲区的大小，严格说是它决定了数据库索引处理的速度，尤其 是索引读的速度read_buffer_size = 1M 读查询操作所能使用的缓冲区大小，该参数对应的分配内存也是每连接独享.read_rnd_buffer_size = 16M 针对按某种特定顺序(如ORDER BY子句)输出的查询结果(默认256K) 加速排序操作后的读数据，提高读分类行的速度。bulk_insert_buffer_size = 64M 指定 MyISAM 类型数

9、据表表使用特殊的树形结构的缓存myisam_sort_buffer_size = 128M myisam引擎的sort_buffer_sizemyisam_max_sort_file_size = 10G#myisam_max_extra_sort_file_size = 10G 这个参数已经不在MySQLmyisam_repair_threads = 1myisam_recover 自动检查和修复无法正确关闭MyISAM表skip-name-resolvemaster-connect-retry = 10slave-skip-errors = 1032,1062,126,1114,1146,

10、1048,1396server-id = 1 设定为masterinnodb_additional_mem_pool_size = 16M InnoDB用来存储数据字典和其他内部数据结构的内存池大小。 应用程序里的表越多就应该分配越多的内存，如果innodb用光了这个内存就会向系统内存要。 并且写入警告日志,根据MySQL手册，对于2G内存的机器，推荐值是20M。 缺省值是1M。通常不用太大，只要够用就行，与表结构的复杂度有关系。innodb_status_fileInnoDB发送诊断输出到stderr或文件，而不是到stdout或者固定尺寸内存缓冲，以避免底层缓冲溢出。作为一个副效果，SHO

11、W INNODB STATUS 的输出每15秒钟写到一个状态文件。这个文件的名字是innodb_status.pid，其中pid是服务器进程ID。这个文件在MySQL数据目录里创建。正常关机之时，InnoDB删除这个文件。如果发生不正常的关机，这些状态文件的实例可能被展示，而且必须被手动删除。在移除它们之前，你可能想要检查它们来看它们是否包含有关不正常关机的原因的有用信息。仅在配置选项innodb_status_file=1被设置之时，innodb_status.pid文件被创建。innodb_buffer_pool_size = 2048M 指定大小的内存来缓冲数据和索引。 对于单独的MyS

12、QL数据库服务器，最大可以把该值设置成物理内存的80%。 根据MySQL手册，对于2G内存的机器，推荐值是1G（50%）innodb_data_file_path = ibdata1:1024M:autoextendinnodb_file_io_threads = 4 IO操作(硬盘写操作)的最大线程个数(默认设置是4)。innodb_thread_concurrency = 16 InnoDB驱动程序能够同时使用的最大线程个数(默认设置是8)。innodb_flush_log_at_trx_commit = 2 InnoDB记录日志的方式 如果设置为1，则每个事务提交的时候，MySQL都会将

13、事务日志写入磁盘。 如果设置为0或者2，则大概每秒中将日志写入磁盘一次。 实际测试发现，该值对插入数据的速度影响非常大 设置为2时插入10000条记录只需要2秒，设置为0时只需要1秒，而设置为1时则需要229秒。 建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。 在存在丢失最近部分事务的危险的前提下，可以把该值设为0。innodb_log_buffer_size = 16M 日志缓存的大小 默认的设置在中等强度写入负载以及较短事务的情况下，一般可以满足服务器的性能要求。 如果更新操作峰值或者负载较大就应该加大这个值。8-16M即可。innodb_log_file_size = 128M

14、 日志组中每个日志文件的大小在高写入负载尤其是大数据集的情况下很重要。 这个值越大性能就越高，但恢复时时间会加长。默认是5M。Javaeye推荐innodb_log_file_size = 64Minnodb_log_files_in_group = 3 日志组中的日志文件数目,推荐使用3innodb_max_dirty_pages_pct = 90 最大脏页的百分数innodb_lock_wait_timeout = 120 事务获得资源超时设置,默认50sinnodb_file_per_table = 0 为每一个新数据表创建一个表空间文件而不是把数据表都集中保存在中央表空间里quick

15、不缓冲查询，直接导出至stdoutmax_allowed_packet = 32M停用mysql的严格验证STRICT_TRANS_TABLES#把sql-mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 中STRICT_TRANS_TABLES,去掉，重启MySQL服务，问题解决汗颜经过此役，总结数据库设计中的一个注意点，也即避免此现象的两种方法：1、在表的设计过程中，对所有不能为空的字段设置默认值2、配置MySQL服务，如上所述将STRICT_TRANS_TABLES去掉#开启event#event_sc

16、heduler = on#myisam自动修复myisam-recover=BACKUP,FORCE# 在 中輸入log-bin=/var/lib/mysql/mysql-bin#expire_logs_days = 10#max_binlog_size = 100Mlog=/var/lib/mysql/mysql.loglog-error=/var/lib/mysql/error.log#log-update=/var/lib/mysql/update.log 未能成功生成long_query_time=1log-slow-querieslog-queries-not-using-index

17、es是否启用了日志mysqlshow variables like log_%;怎样知道当前的日志mysql show master status;顯示二進制日志數目mysql show master logs;看二进制日志文件用mysqlbinlogshellmysqlbinlog mail-bin.000001或者shellmysqlbinlog mail-bin.000001 | tail#详细说明log-bin=/var/lib/mysql/mysql-bin #二进制LOG,mysqlbinlog直接读取二进制日志文件。二进制日志包含所有更新数据的语句 ，其目的是在恢复数据库时用它来

18、把数据尽可能恢复到最后的状态。另外，如果做同步复制( Replication )的话，也需要使用二进制日志传送修改情况。log_bin指定日志文件，如果不提供文件名，MySQL将自己产生缺省文件名。MySQL会在文件名后面自动添加数字索引，每次启动服务时，都会重新生成一个新的二进制文件。时间长了log bin也会占很大的硬盘空间，所以在my.ini中加上对其的限制。#expire_logs_days = 10#max_binlog_size = 100M-# show master status;#mysqlbinlog mysql-bin.000052-格式如下：flush privileg

19、es;# at 173#090814 10:09:22 server id 1 end_log_pos 448 Query thread_id=51 exec_tim e=0 error_code=0use sq_gllutf8;SET TIMESTAMP=1250215762;SET session.character_set_client=33,session.collation_connection=33,sessio n.collation_server=8;update gll_products set sn_name=休闲裤 ,ch_name=H957-3 ,color=卡其 ,c

20、o mposition=60%马棉44 ,huoqi= ,p_remarks= ,utime=2009-08-14 10:09:22 , uip=192.168.200.96 WHERE id = 339;# at 448#090814 10:11:14 server id 1 end_log_pos 556 Query thread_id=59 exec_tim e=0 error_code=0SET TIMESTAMP=1250215874;delete from gll_member WHERE id = 26;DELIMITER ;# End of log fileROLLBACK ;

21、-log=/var/lib/mysql/mysql.log #对所有执行语句进行记录-格式如下：090814 10:11:14 59 Connect sq_szczkjlocalhost on 59 Init DB sq_gllutf8 59 Query SET NAMES UTF8 59 Query delete from gll_member WHERE id = 26 59 Quit090814 10:11:15 60 Connect sq_szczkjlocalhost on 60 Init DB sq_gllutf8 60 Query SET NAMES UTF8 60 Query

22、select * from gll_member ORDER BY id DESC 60 Query select * from gll_member ORDER BY id DESC limit 0,50 60 Quit 61 Connect sq_szczkjlocalhost on 61 Init DB sq_gllutf8 61 Query SET NAMES UTF8 61 Query select * from gll_zone ORDER BY id DESC 61 Query select * from gll_zone ORDER BY id DESC limit 0,99

23、61 Quit-log-error=/var/lib/mysql/error.log#log-update=/var/lib/mysql/update.log 未能成功生成long_query_time=1log-slow-queries#-log-slow-queries=file_name 的file_name参数可选，缺省值是host_name-slow.log ，如果指定了file_name参数的话，mysql就会把慢查询的日志记录到file_name所设定的文件中，如果file_name提供的是一个相对路径，mysql会把日志记录到mysql的data目录中。log-queries-

24、not-using-indexes #在mysql的启动配置文件或命令行参数中增加-log-queries-not-using-indexes 参数就可以启用未使用索引查询语句了，日志记录的文件就是log-slow-queries对应的文件。-Mysql的log_slow_queries日志文件设置不当错误1，容易发生错误的设置：log_slow_queries = ONlog_slow_queries=/var/log/slow-queries.loglong_query_time=5这种容易导致slow-queries.log无法读取的错误（权限问题）。常见错误提示：Could not u

25、se /var/log/slow-queries.log forlogging (error 13). Turning logging off for the whole duration of theMySQL server process. To turn it on again: fix the cause, shutdown theMySQL server and restart it.2，比较妥当的配置：log_slow_queries = ONlog_slow_queries=slow-loglong_query_time=5直接在目录/var/lib/mysql/ 下生成 slow-log 文件-测试慢日志mysqlselect sleep(1);mysqlselect sleep(3);mysqlselect * from gll_products;打开mysql.log和慢日志文件可以看到select sleep(3);select * from gll_products;-3 命令说明show variables like long