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

mysql有以下几种日志：
   错误日志：    log-err
   查询日志：    log
   慢查询日志:  log-slow-queries
   更新日志:    log-update
   二进制日志： log-bin

要把日志生成在 /var/log 目录下(是系统日志存放的地方，只有 root 账号有写权限)，需要 MySQL 进程对这个目录有读写权限，一般是不这么做的，也考虑到安全问题，包括 MySQL 本身的数据安全，因为对 MySQL 的所有操作，都会记录到常规查询日志。MySQL 的日志就不要用 /var/log/ 目录下。

---------------------------------------------------------------------2 my.cnf

default-character-set = utf8
port    = 3306
socket  = /tmp/mysql.sock

no-auto-rehash

#default-character-set = utf8
user    = mysql
port    = 3306
socket  = /tmp/mysql.sock
basedir = /usr/local/webserver/mysql
datadir = /data0/mysql/3306/data
open_files_limit    = 10240
back_log = 600
max_connections = 3000 实际MySQL服务器允许的最大连接数16384；
max_connect_errors = 6000  可以允许多少个错误连接
table_cache = 1024  高速缓存的大小.table_cache的值在2G内存以下的机器中的值默认时256到 512，如果机器有4G内存,则默认这个值是2048，但这决意味着机器内存越大，这个值应该越大，因为table_cache加大后，使得mysql对 SQL响应的速度更快了，不可避免的会产生更多的死锁（dead lock），这样反而使得数据库整个一套操作慢了下来，严重影响性能。
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 2M 是一个connection级参数，在每个connection第一次需要使用这个buffer的时候，一次性分配设置的内存.并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。
join_buffer_size = 2M
thread_cache_size = 64  重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能
   根据物理内存设置规则如下：
     1G  ---> 8
     2G  ---> 16
     3G  ---> 32
     >3G  ---> 64

thread_concurrency = 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。如果表更改了，那么使用这个表的所有缓冲查询将不再有效，查询缓存值的相关条目被清空
query_cache_limit = 2M 单个查询能够使用的缓冲区大小.缺省为1M
query_cache_min_res_unit = 2k 它指定分配缓冲区空间的最小单位，缺省为4K。检查状态值Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多，这就表明查询结果都比较小，此时需要减小query_cache_min_res_unit
default-storage-engine = MyISAM
default_table_type = MyISAM
thread_stack = 192K
transaction_isolation = READ-COMMITTED  设置所有连接的默认事务隔离级
tmp_table_size = 256M 临时HEAP数据表的最大长度
max_heap_table_size = 256M HEAP数据表(内存表)的最大长度(默认设置是16M);
long_query_time = 1
log_long_format
log-bin = /data0/mysql/3306/binlog
binlog_cache_size = 8M 为binary log指定在查询请求处理过程中SQL 查询语句使用的缓存大小
binlog_format = MIXED
max_binlog_cache_size = 8M
max_binlog_size = 512M
expire_logs_days = 7
key_buffer_size = 256M  索引缓冲区的大小，严格说是它决定了数据库索引处理的速度，尤其 是索引读的速度
read_buffer_size = 1M  读查询操作所能使用的缓冲区大小，该参数对应的分配内存也是每连接独享.
read_rnd_buffer_size = 16M 针对按某种特定顺序(如ORDER BY子句)输出的查询结果(默认256K) 加速排序操作后的读数据，提高读分类行的速度。
bulk_insert_buffer_size = 64M 指定 MyISAM 类型数据表表使用特殊的树形结构的缓存
myisam_sort_buffer_size = 128M myisam引擎的sort_buffer_size
myisam_max_sort_file_size = 10G
#myisam_max_extra_sort_file_size = 10G  这个参数已经不在MySQL
myisam_repair_threads = 1
myisam_recover  自动检查和修复无法正确关闭MyISAM表

skip-name-resolve
master-connect-retry = 10
slave-skip-errors = 1032,1062,126,1114,1146,1048,1396

server-id = 1 设定为master

innodb_additional_mem_pool_size = 16M InnoDB用来存储数据字典和其他内部数据结构的内存池大小。
   应用程序里的表越多就应该分配越多的内存，如果innodb用光了这个内存就会向系统内存要。
   并且写入警告日志,根据MySQL手册，对于2G内存的机器，推荐值是20M。
   缺省值是1M。通常不用太大，只要够用就行，与表结构的复杂度有关系。

innodb_status_file
InnoDB发送诊断输出到stderr或文件，而不是到stdout或者固定尺寸内存缓冲，以避免底层缓冲溢出。作为一个副效果，SHOW INNODB STATUS 的输出每15秒钟写到一个状态文件。这个文件的名字是innodb_status.pid，其中pid是服务器进程ID。这个文件在MySQL数据目录里创建。正常关机之时，InnoDB删除这个文件。如果发生不正常的关机，这些状态文件的实例可能被展示，而且必须被手动删除。在移除它们之前，你可能想要检查它们来看它们是否包含有关不正常关机的原因的有用信息。仅在配置选项innodb_status_file=1被设置之时，innodb_status.pid文件被创建。

 

innodb_buffer_pool_size = 2048M 指定大小的内存来缓冲数据和索引。
   对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%。
   根据MySQL手册，对于2G内存的机器，推荐值是1G（50%）

innodb_data_file_path = ibdata1:1024M:autoextend
innodb_file_io_threads = 4 IO操作(硬盘写操作)的最大线程个数(默认设置是4)。
innodb_thread_concurrency = 16 InnoDB驱动程序能够同时使用的最大线程个数(默认设置是8)。
innodb_flush_log_at_trx_commit = 2  InnoDB记录日志的方式
   如果设置为1，则每个事务提交的时候，MySQL都会将事务日志写入磁盘。
   如果设置为0或者2，则大概每秒中将日志写入磁盘一次。
   实际测试发现，该值对插入数据的速度影响非常大
     设置为2时插入10000条记录只需要2秒，设置为0时只需要1秒，而设置为1时则需要229秒。
   建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。
   在存在丢失最近部分事务的危险的前提下，可以把该值设为0。

innodb_log_buffer_size = 16M 日志缓存的大小
   默认的设置在中等强度写入负载以及较短事务的情况下，一般可以满足服务器的性能要求。
   如果更新操作峰值或者负载较大就应该加大这个值。8-16M即可。

innodb_log_file_size = 128M 日志组中每个日志文件的大小在高写入负载尤其是大数据集的情况下很重要。
   这个值越大性能就越高，但恢复时时间会加长。默认是5M。Javaeye推荐innodb_log_file_size = 64M

innodb_log_files_in_group = 3 日志组中的日志文件数目,推荐使用3
innodb_max_dirty_pages_pct = 90 最大脏页的百分数
innodb_lock_wait_timeout = 120 事务获得资源超时设置,默认50s
innodb_file_per_table = 0  为每一个新数据表创建一个表空间文件而不是把数据表都集中保存在中央表空间里

quick  不缓冲查询，直接导出至stdout
max_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_scheduler = on
#myisam自动修复
myisam-recover=BACKUP,FORCE

# 在 中輸入
log-bin=/var/lib/mysql/mysql-bin
#expire_logs_days = 10
#max_binlog_size = 100M
log=/var/lib/mysql/mysql.log
log-error=/var/lib/mysql/error.log
#log-update=/var/lib/mysql/update.log  未能成功生成
long_query_time=1
log-slow-queries
log-queries-not-using-indexes

是否启用了日志
mysql>show variables like 'log_%';
怎样知道当前的日志
mysql> show master status;
顯示二進制日志數目
mysql> show master logs;
看二进制日志文件用mysqlbinlog
shell>mysqlbinlog mail-bin.000001
或者shell>mysqlbinlog mail-bin.000001 | tail

#详细说明
log-bin=/var/lib/mysql/mysql-bin #二进制LOG,mysqlbinlog直接读取二进制日志文件。二进制日志包含所有更新数据的语句 ，其目的是在恢复数据库时用它来把数据尽可能恢复到最后的状态。另外，如果做同步复制( 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 privileges
;
# at 173
#090814 10:09:22 server id 1  end_log_pos 448  Query  thread_id=51    exec_tim                                            e=0    error_code=0
use 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='卡其'  ,co                                            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=0
SET TIMESTAMP=1250215874;
delete from gll_member  WHERE id = '26'
;
DELIMITER ;
# End of log file
ROLLBACK ;
;
-----------------------------------

log=/var/lib/mysql/mysql.log  #对所有执行语句进行记录
----------------------------格式如下：
090814 10:11:14      59 Connect    sq_szczkj@localhost on
                     59 Init DB    sq_gllutf8
                     59 Query      SET NAMES 'UTF8'
                     59 Query      delete from gll_member  WHERE id = '26'
                     59 Quit
090814 10:11:15      60 Connect    sq_szczkj@localhost on
                     60 Init DB    sq_gllutf8
                     60 Query      SET NAMES 'UTF8'
                     60 Query      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_szczkj@localhost 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
                     61 Quit
---------------------------------

log-error=/var/lib/mysql/error.log
#log-update=/var/lib/mysql/update.log  未能成功生成

long_query_time=1
log-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-not-using-indexes #在mysql的启动配置文件或命令行参数中增加--log-queries-not-using-indexes 参数就可以启用未使用索引查询语句了，日志记录的文件就是log-slow-queries对应的文件。

----------------Mysql的log_slow_queries日志文件设置不当错误
1，容易发生错误的设置：
log_slow_queries = ON
log_slow_queries=/var/log/slow-queries.log
long_query_time=5
这种容易导致slow-queries.log无法读取的错误（权限问题）。
常见错误提示：
Could not use /var/log/slow-queries.log for
logging (error 13). Turning logging off for the whole duration of the
MySQL server process. To turn it on again: fix the cause, shutdown the
MySQL server and restart it.

2，比较妥当的配置：
log_slow_queries = ON
log_slow_queries=slow-log
long_query_time=5
直接在目录/var/lib/mysql/ 下生成 slow-log 文件

 

----------------------------测试慢日志
mysql>select sleep(1);
mysql>select sleep(3);
mysql>select * from gll_products;

打开mysql.log和慢日志文件可以看到
select sleep(3);
select * from gll_products;

 

 

 

---------------------------------------------------------------------3 命令说明

 

show variables like 'long%'; 查询 long_query_time 的值

mysql>show variables like 'log_%';  是否启用了日志
mysql> show variables like 'log_%';
+---------------------------------+--------------------------+
| Variable_name                  | Value                    |
+---------------------------------+--------------------------+
| log_bin                        | ON                      |
| log_bin_trust_function_creators | OFF                      |
| log_error                      | /var/lib/mysql/error.log |
| log_queries_not_using_indexes  | ON                      |
| log_slave_updates              | OFF                      |
| log_slow_queries                | ON                      |
| log_warnings                    | 1                        |
+---------------------------------+--------------------------+

 

mysql> show master logs;  顯示二進制日志數目

mysql>show variables like 'log_bin'; 确认你日志是否启用

mysql> show master status; 怎样知道当前的二进制日志

查看从某一段时间到某一段时间的二进制日志
mysqlbinlog --start-datetime='2008-01-19 00:00:00' --stop-datetime='2008-01-30 00:00:00'  /var/lib/mysql/mysql-bin.000006 > mysqllog1.log

shell>mysqlbinlog mail-bin.000001  看二进制日志文件用mysqlbinlog
或者shell>mysqlbinlog mail-bin.000001 | tail

flush privileges;  刷新数据库

mysql> SHOW STATUS;  运行下列命令可以获取状态变量的值：

mysql> SHOW STATUS LIKE ‘[匹配模式]’; ( 可以使用%、?等 )  如果只要检查某几个状态变量，可以使用下列命令：

select version(); 查看mysql版本