MySQL 配置文档

冷不防 2022-01-07 00:45 137阅读 0赞

1. 硬件环境：

核数：8

内存：32G

磁盘空间：100G

操作系统：centos 6.5

2. my.cnf 文件配置

\[client\]  
port = 3306  
socket = /tmp/mysql.sock  
default-character-set = utf8mb4

\[mysql\]  
prompt="MySQL \[\\d\]> "  
no-auto-rehash

\[mysqld\]  
port = 3306  
socket = /tmp/mysql.sock

basedir = /usr/local/mysql  
datadir = /data/mysql  
pid-file = /data/mysql/mysql.pid  
user = mysql  
bind-address = 0.0.0.0  
server-id = 1

init-connect = 'SET NAMES utf8mb4'  
character-set-server = utf8mb4

skip-name-resolve  
\#skip-networking  
back\_log = 300

max\_connections = 10703  
max\_connect\_errors = 6000  
open\_files\_limit = 65535  
table\_open\_cache = 1024  
max\_allowed\_packet = 500M  
binlog\_cache\_size = 1M  
max\_heap\_table\_size = 8M  
tmp\_table\_size = 128M

read\_buffer\_size = 2M  
read\_rnd\_buffer\_size = 8M  
sort\_buffer\_size = 8M  
join\_buffer\_size = 8M  
key\_buffer\_size = 256M

thread\_cache\_size = 64

query\_cache\_type = 1  
query\_cache\_size = 64M  
query\_cache\_limit = 2M

ft\_min\_word\_len = 4

log\_bin = mysql-bin  
binlog\_format = mixed  
expire\_logs\_days = 7

log\_error = /data/mysql/mysql-error.log  
slow\_query\_log = 1  
long\_query\_time = 1  
slow\_query\_log\_file = /data/mysql/mysql-slow.log

performance\_schema = 0  
explicit\_defaults\_for\_timestamp

\#lower\_case\_table\_names = 1

skip-external-locking

default\_storage\_engine = InnoDB  
\#default-storage-engine = MyISAM  
innodb\_file\_per\_table = 1  
innodb\_open\_files = 500

\# InnoDB使用一个缓冲池来保存索引和原始数据, 不像MyISAM.  
\# 这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少.  
\# 在一个独立使用的数据库服务器上,你可以设置这个变量到服务器物理内存大小的80%  
\# 不要设置过大,否则,由于物理内存的竞争可能导致操作系统的换页颠簸.  
\# 注意在32位系统上你每个进程可能被限制在 2-3.5G 用户层面内存限制,  
\# 所以不要设置的太高.  
innodb\_buffer\_pool\_size = 1024M

\# innodb使用后台线程处理数据页上的读写 I/O(输入输出)请求,根据你的 CPU 核数来更改,默认是4  
innodb\_write\_io\_threads = 4  
innodb\_read\_io\_threads = 4  
innodb\_thread\_concurrency = 0  
innodb\_purge\_threads = 1  
innodb\_flush\_log\_at\_trx\_commit = 2  
innodb\_log\_buffer\_size = 2M

\#在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高，但是要注意到可能会增加恢复时间。  
innodb\_log\_file\_size = 32M  
innodb\_log\_files\_in\_group = 3  
innodb\_max\_dirty\_pages\_pct = 90  
innodb\_lock\_wait\_timeout = 120

bulk\_insert\_buffer\_size = 8M  
myisam\_sort\_buffer\_size = 64M  
myisam\_max\_sort\_file\_size = 10G  
myisam\_repair\_threads = 1

interactive\_timeout = 28800  
wait\_timeout = 28800

\[mysqldump\]  
quick  
max\_allowed\_packet = 500M

\[myisamchk\]  
key\_buffer\_size = 256M  
sort\_buffer\_size = 8M  
read\_buffer = 4M  
write\_buffer = 4M

转载于:https://www.cnblogs.com/duaner92/p/10191924.html