Mysql配置文件 详解
Mysqld 配置文件查找路径: 查找顺序为 都会依次查找 都会生效 值有重复或冲突的 后面的最终生效
/etc/my.cnf --> /etc/mysql/my.cnf --> $MYSQL_HOME/my.cnf --> --default-extra-file=/path/to/somedir/my.cnf --> ~/.my.cnf
$MYSQL_HOME/my.cnf mysql自己的家目录下 一般是安装路径
--default-extra-file 启动mysql进程时会传递一个--default值等于一个路径 指明额外的查找路径
~/.my.cnf 当前用户下的家目录下 隐藏文件
[mysqld] 专用于mysqld这个服务的
[mysqld_safe] 线程安全的mysql专用的信息
[mysqld_multi] 多实例之间可以共享的参数放到此 启动的时候以上三种 通常启动一个
[server] 只要是mysql的服务器端都有效的
[mysql] 专用于mysql客户端
[mysqldump] 专用于mysqldump备份导入导出工具的
[client] 对客户端都有效 用此
[client]
#password = your_password
port = 63306
socket = /data/mysqldb/run/mysql.sock
[mysqld]
port = 63306 \\ 定义端口
socket = /data/mysqldb/run/mysql.sock \\ 套接字
datadir = /data/mysqldb/3306 \\ 数据库目录
log-error = /data/mysqldb/logs/mysql-error.log \\ 错误日志目录
pid_file = /data/mysqldb/run/mysql.pid
skip-external-locking \\ 不使用系统锁定 要使用 myisamchk 必须关闭服务器 避免MySQL的外部锁定 减少出错几率增强稳定性
skip-name-resolve=on \\ 禁止 MySQL 对外部连接进行 DNS 解析 使用这一选项可以消除 MySQL 进行 DNS 解析的时间.但需要注意
\\ ↑ 如果开启该选项 则所有远程主机连接授权都要使用 IP 地址方式 否则 MySQL 将无法正常处理连接请求
character-set-server=utf8 \\ server 级别字符集
collation-server=utf8_bin \\ 字符集配置
default-storage-engine = INNODB \\ 默认存储引擎
wait_timeout= 1800 \\ 超时等待时间 单位秒 即一个connection在若干秒内无响应 则服务器切断与这个客户端的连接
connect_timeout = 30 \\ 客户端与服务器建立连接 服务器返回错误的握手协议前 等待数据包到来的最大时间 单位秒
interactive_timeout = 1800 \\ 关闭连接之前 允许 interactive_timeout（取代了wait_timeout）秒的不活动时间。客户端的会
\\ ↑ 话 wait_timeout 变量被设为会话interactive_timeout 变量的值.如果前端程序采用短连接
\\ ↑ 建议缩短这2个值 如果前端程序采用长连接 可直接注释掉这两个选项 默认配置8小时
back_log = 500 \\ 接受队列 对于没建立 tcp 连接的请求队列放入缓存中 队列大小为 back_log 受限制与 OS
\\ ↑ 参数试图设定back_log高于你的操作系统的限制将是无效的 默认值为50.对于Linux系统
\\ ↑ 荐设置为小于512的整数 如果系统在一个短时间内有很多连接，则需要增大该参数的值
myisam_recover \\ 允许的 GROUP_CONCAT()函数结果的最大长度
event_scheduler =ON \\ 开启了计划任务
server-id = 1 \\ 表示是本机的序号为1 唯一
auto_increment_offset = 1 \\ 表示自增长字段从哪个数开始 指字段一次递增多少 他的取值范围是
auto_increment_increment = 1 \\ 表示自增长字段每次递增的量 指自增字段的起始值 其默认值是1 取值范围是1-65535
transaction_isolation = READ-COMMITTED
#######bin log########
log-bin=/data/mysqldb/mysql-bin/mysql-bin \\ 打开二进制日志功能
binlog_format=mixed \\ binlog日志格式，mysql默认采用statement，建议使用mixed
max_binlog_size = 256M \\ 如果二进制日志写入的内容超出给定值 日志就会发生滚动.你不能将该变量设置为大于1GB或
\\ ↑ 小于4096字节.默认值是1GB.如果你正使用大的事务 二进制日志还会超过max_binlog_size
binlog_cache_size = 2M \\ 在一个事务中 binlog 为了记录 SQL 状态所持有的 cache 大小,如果你经常使用大的,多声明的事务,增加此值来
\\ ↑ 获取更大的性能.所有从事务来的状态都将被缓冲在 binlog 缓冲中然后在提交后一次性写入到binlog中.如果
\\ ↑ 事务比此值大 会使用磁盘上的临时文件来替代.此缓冲在每个连接的事务第一次更新状态时被创建session级别
expire_logs_days=0 #原来为15天 \\ 超过 5 天的 binlog 删除
log-slave-updates \\ 从库做为其他从库的主库时 log-slave-updates参数是必须要添加的
skip_slave_start \\ 备库重启不自动同步
sync_binlog=0 \\ 表示立即同步 控制数据库的binlog刷到磁盘上去。 设定是否启动二进制日志同步功能
relay-log=/data/mysqldb/relay-bin/relay-bin \\ 定义relay_log的位置和名称，如果值为空，则默认位置在数据文件的目录
relay_log_recovery = 1
#####slow log #########
slow_query_log = ON \\ 指定是否开启慢查询日志. 慢查询是指消耗了比 “long_query_time” 定义的更多时间的查询.如果log_long_format
\\ ↑ 被打开.那些没有使用索引的查询也会被记录.如果你经常增加新查询到已有的系统内的话.一般来说这是一个好主意
slow_query_log_file = /data/mysqldb/logs/mysql-slow.log \\ 慢日志存放位置 可以为空 系统会给一个缺省的文件host_name-slow.log
long_query_time = 1
######per_thread_buffers############
max_connections = 3000 \\ 允许的最大连接进程数.如果在访问数据库时经常出现"Too Many Connections"的错误提示 则需要增大该值
max_connect_errors = 5000 \\ 如果某个用户发起的连接 error 超过该数值 则该用户的下次连接将被阻塞 直到管理员执行 flush hosts
\\ ↑ 命令或者服务重启 防止黑客 非法的密码以及其他在链接时的错误会增加此值
key_buffer_size = 384M \\ 指定用于索引的缓冲区大小 增加它可得到更好的索引处理性能 如果是以InnoDB引擎为主的DB 专用于MyISAM引擎的
\\ ↑ key_buffer_size 可以设置较小，8MB 已足够 如果是以MyISAM引擎为主，可设置较大，但不能超过4G.
\\ ↑ 强烈建议不使用MyISAM引擎 默认都是用InnoDB引擎. 注意:该参数值设置的过大反而会是服务器整体效率降低!
max_allowed_packet = 4M \\ 服务所能处理的请求包的最大大小以及服务所能处理的最大的请求大小(当与大的BLOB 字段一起工作时相当必要)
\\ ↑ 每个连接独立的大小.大小动态增加.设置最大包 限制server接受的数据包大小 避免超长SQL的执行有问题
\\ ↑ 当MySQL客户端或mysqld服务器收到大于此数值时将发出'信息包过大'错误 并关闭连接.对于某些客户端
\\ ↑ 如果通信信息包过大 在执行查询期间 可能会遇到“丢失与 MySQL 服务器的连接”错误.默认值 16M.
table_open_cache = 2048 \\ #原来为512
table_definition_cache = 2048 \\ #默认没有该参数
join_buffer_size = 16M \\ 联合查询操作所能使用的缓冲区大小 和 sort_buffer_size 一样 该参数对应的分配内存也是每连接
\\ ↑ 独享此缓冲被使用来优化全联合(full JOINs 不带索引的联合).类似的联合在极大多数情况下有非常
\\ ↑ 糟糕的性能表现, 但是将此值设大能够减轻性能影响.通过 “Select_full_join”状态变量查看全联
\\ ↑ 合的数量 当全联合发生时,在每个线程中分配.
tmp_table_size = 67108864
sort_buffer_size = 16M \\ 查询排序时所能使用的缓冲区大小.排序缓冲被用来处理类似 ORDER BY 以及 GROUP BY 队列所引起的排序.一个用
\\ ↑ 来替代的基于磁盘的合并分类会被使用.查看 “Sort_merge_passes” 状态变量. 在排序发生时由每个线程分配
\\ ↑ 注意：该参数对应的分配内存是每连接独占.如果有 100 个连接 那么实际分配的总共排序缓冲区大小为
\\ ↑ 100 × 6 ＝600MB 所以 对于内存在 4GB 左右的服务器推荐设置为 6-8M.
read_buffer_size = 16M \\ 读查询操作所能使用的缓冲区大小.和 sort_buffer_size 一样 该参数对应的分配内存也是每连接独享
\\ ↑ 用来做 MyISAM 表全表扫描的缓冲大小.当全表扫描需要时 在对应线程中分配
read_rnd_buffer_size = 8M \\ MyISAM 以索引扫描(Random Scan)方式扫描数据的 buffer大小
thread_cache_size = 64 \\ 在 cache 中保留多少线程用于重用.当一个客户端断开连接后,如果 cache 中的线程还少于 thread_cache_size
\\ ↑ 则客户端线程被放入cache 中.可以在你需要大量新连接的时候极大的减少线程创建的开销(一般来说如果你有
\\ ↑ 好的线程模型的话,这不会有明显的性能提升)服务器线程缓存这个值表示可以重新利用保存在缓存中线程的数
\\ ↑ 量,断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求,那么请求将从
\\ ↑ 缓存中读取,如果缓存中是空的或者是新的请求 那么这个线程将被重新创建,如果有很多新的线程,增加这个
\\ ↑ 值可以改善系统性能.通过比较Connections 和 Threads_created状态的变量 可以看到这个变量的作用
\\ ↑ 根据物理内存设置规则如下:1G —> 8 | 2G —> 16 | 3G —> 32 | 大于 3G —> 64
innodb_thread_concurrency = 64 \\ 允许应用程序给予线程系统一个提示在同一时间给予渴望被运行的线程的数量.该参数取值为服务器逻辑
\\ ↑ CPU数量×2.在本例中 服务器有 2 颗物理CPU 所以实际取值为4 × 2 ＝ 8.设置thread_concurrency
\\ ↑ 的值的正确与否 对 mysql 的性能影响很大 在多个 cpu(或多核)的情况下 错误设置
\\ ↑ 了此值 会导致 mysql 不能充分利用多cpu(或多核) 出现同一时刻只能一个cpu(或核)在工作的情况
\\ ↑ thread_concurrency 设为 CPU 核数的 2 倍.比如有一个双核的 CPU 那么thread_concurrency
\\ ↑ 的应该为4; 2 个双核的 cpu,thread_concurrency的值应为8 属重点优化参数
query_cache_size = 0 \\ 指定 MySQL 查询缓冲区的大小。可以通过在 MySQL 控制台执行以下命令观察
query_cache_limit = 0 \\ 不缓存查询大于该值的结果.只有小于此设定值的结果才会被缓冲, 此设置用来保护查询缓冲,
\\ ↑ 防止一个极大的结果集将其他所有的查询结果都覆盖.
myisam_sort_buffer_size = 128M \\ MyISAM 设置恢复表之时使用的缓冲区的尺寸,当在REPAIR TABLE 或用 CREATE INDEX
\\ ↑ 创建索引或 ALTER TABLE 过程中排序 MyISAM 索引分配的缓冲区
############ innoDB ###################
innodb_data_home_dir = /data/mysqldb/innodb \\ 设置此选项是 InnoDB 表空间文件被保存在其他分区.默认保存在MySQL的datadir中
innodb_data_file_path = ibdata1:200M;ibdata2:10M:autoextend \\ InnoDB将数据保存在一个或多个数据文件中成为表空间.只有单个驱动
\\ ↑ 一个单个的自增文件就好了.每个设备一个文件一般都是个好的选择.
innodb_log_group_home_dir = /data/mysqldb/innodb \\ InnoDB 的日志文件所在位置. 默认是 MySQL 的 datadir.
innodb_buffer_pool_size = 2G \\ 包括数据页、索引页、插入缓存、锁信息、自适应哈希所以、数据字典信息.InnoDB 使用一个缓冲池来保存索
\\ ↑ 引和原始数据,设置越大,你在存取表里面数据时所需要的磁盘 I/O 越少.在一个独立使用的数据库服务
\\ ↑ 器上,你可以设置这个变量到服务器物理内存大小的 80%,不要设置过大,否则,由于物理内存的竞争可
\\ ↑ 能导致操作系统的换页颠簸.注意在32 位系统上你每个进程可能被限制在2-3.5G所以不要设置的太高.
innodb_buffer_pool_instances = 3 \\ #默认没有这个参数
innodb_additional_mem_pool_size = 32M \\ 设置InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小 所以当数据库
\\ ↑ 对象非常多的时候 需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率
innodb_log_file_size = 256M \\ 事物日志大小.每个日志文件的大小,应该设置日志文件总合大小到你缓冲池大小的5%~100%,避
\\ ↑ 免在日志文件覆写上不必要的缓冲池刷新行为.一个大的日志文件会增加恢复进程所需要时间.
innodb_log_buffer_size = 64M \\ 用来缓冲日志数据的缓冲区的大小.当此值快满时, InnoDB 将必须刷新数据到磁盘上.由于基
\\ ↑ 本上每秒都会刷新一次,所以没有必要将此值设置的太大(甚至对于长事务而言)
innodb_log_files_in_group = 4 \\ 在日志组中的文件总数.通常来说 2~3 是比较好的.
innodb_flush_log_at_trx_commit = 2 \\ 如果设置为 1 ,InnoDB 会在每次提交后刷新(fsync)事务日志到磁盘上,这提供了完整的 ACID 行为
\\ ↑ 如果你愿意对事务安全折衷, 并且你正在运行一个小的食物, 你可以设置此值到 0 或者 2 来减
\\ ↑ 少由事务日志引起的磁盘 I/O. 0 代表日志只大约每秒写入日志文件并且日志文件刷新到磁盘.
\\ ↑ 2 代表日志写入日志文件在每次提交后,但是日志文件只有大约每秒才会刷新到磁盘上.
innodb_lock_wait_timeout = 10 \\InnoDB 事务在被回滚之前可以等待一个锁定的超时秒数.InnoDB 在它自己的 锁定表中自动检
\\ ↑ 测事务死锁并且回滚事务.InnoDB 用 LOCK TABLES 语句注意到锁定设置.默认值是50秒
#innodb_file_io_threads = 4 \\ 用来同步 IO 操作的 IO 线程的数量. 此值在 Unix 下被硬编码为 4,
\\ ↑ 但是在 Windows 磁盘 I/O 可能在一个大数值下表现的更好.
innodb_file_per_table = 1 \\ InnoDB为独立表空间模式,每个数据库的每个表都会生成一个数据空间
#innodb_read_io_threads = 16 \\ innodb使用后台线程处理数据页上的读写 I/O(输入输出)请求,根据你的 CPU 核数来更改,默认是4
\\ ↑ 这两个参数不支持动态改变,需要把该参数加入到my.cnf里,修改完后重启MySQL,允许值的范围从 1-64
#innodb_write_io_threads = 16 \\ 同上
#innodb_io_capacity = 1000 \\ #默认没有该参数
innodb_file_format = Barracuda \\ #默认没有该参数
innodb_flush_method = O_DIRECT \\ 用来刷新日志的方法.表空间总是使用双重写入刷新方法.默认值是"fdatasync",另一个是"O_DSYNC".
#innodb_lru_scan_depth = 4096 \\ 此参数在mariadb5.5中不好使 所有禁用 在mysql5.6以上好使
[mysqldump]
quick \\ 支持较大数据库的转储,在导出非常巨大的表时需要此项.增加该变量的值十分安全,这是因为仅当需要时才会分配额外内存.
max_allowed_packet = 128M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout