Mysql之my.cnf配置
LiuSw Lv6
 2022-12-21 09:39:44      1.6k 字  6 分钟  

Mysql之my.cnf配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
[mysqld]
user=mysql
basedir=/mysql
datadir=/mysql/data
socket=/tmp/mysql.sock
secure-file-priv=/tmp
server_id=10
lower_case_table_names=1
log_bin_trust_function_creators=TRUE
sql_mode=NO_ENGINE_SUBSTITUTION
sync_binlog=1  #规定二进制日志从内存写入磁盘的时间点,1为事务提交时写入磁盘
log_bin=/binlog/mysql-bin  #指定二进制日志文件位置及前缀,等于同时设置了开启二进制日志功能
innodb_buffer_pool_size=1300M  #数据缓冲区,测试机内存2G,我按照60%给的
innodb_flush_log_at_trx_commit=1  #控制了redolog数据从内存写入磁盘的时间点,1为事务提交时写入磁盘
innodb_log_buffer_size=32M  #设置redo缓冲区大小
innodb_log_file_size=128M  #设置redolog重做日志的大小
innodb_log_files_in_group=3  #redolog文件数量
transaction_isolation=REPEATABLE-READ  #隔离级别RR
innodb_data_file_path=ibdata1:512M;ibdata2:512M:autoextend  #生成两个存放数据字典和undolog日志文件,一个512M
gtid-mode=on  #开启GTID功能
enforce-gtid-consistency=true  #强制执行GTID启动数据库服务
#log-slave-updates=1  #从库记录relay执行的日志到binlog,适用于从库下面还有从库的环境
#slave-parallel-type=LOGICAL_CLOCK #LOGICAL_CLOCK：基于组提交的并行复制方式
#slave-parallel-workers=8  #slave复制线程数
#master_info_repository=TABLE  #master_info信息保存在表中，性能可以有50%~80%的提升
#relay_log_info_repository=TABLE #relay.info信息保存在表中
#relay_log_recovery=ON  #从库设置，宕机后从主库拿数据保证relay-log的完整性
skip-name-resolve  #禁止DNS反向解析
relay_log_purge=0  #从库关闭relay语句执行后删除功能,为了安全
max_connections=3096  #最大连接数,可以根据测试结果设置
back_log=16  #达到最大连接数后,允许的最大排队用户数量
wait_timeout=60  #处于sleep连接状态的连接线程自动释放时间,默认60秒
#interactive_timeout=7200 #谨慎设置，应与开发人员沟通后设置，如果开发需要长连接，请勿修改
key_buffer_size=16M  #MyISAM索引缓冲区,还和ibtmp临时表有关,减小io和cpu负载,可以设置稍微大一点,根据show status like "created_tmp%";disk使用量调整
query_cache_size=128M  #查询缓存,但是命中率不高,一般不使用
query_cache_type=1  #缓存类型,其代表全部
query_cache_limit=50M  #限制单个查询使用的最大缓存数
max_connect_errors=2000  #最大错误连接数,超过将无法登陆,建议大一点
sort_buffer_size=2M  #排序缓冲区
max_allowed_packet=256M  #允许最大传输包大小
join_buffer_size=2M  #多表联合join缓冲区
thread_cache_size=16  #连接线程缓存池,下次可以直接连接,减小cpu压力,但是较消耗内存
binlog_cache_size=2M  #这个是每个会话分配的内存,酌情给,否则会占用大量内存,二进制日志缓存区大小
max_binlog_cache_size=8M  #最大binlog日志缓存区大小，show global status like 'bin%';查看Binlog_cache_disk_use值比较大的时候，我们可以考虑适当的调高 binlog_cache_size对应的值
max_binlog_size=512M  #规定二进制日志binlog文件最大文件大小,超出就会生成新文件
expire_logs_days=15  #二进制日志保留期限,原则上2个全备周期+1
read_buffer_size=2M  #读入缓冲区的大小
read_rnd_buffer_size=2M  #随机读(查询操作)缓冲区大小
bulk_insert_buffer_size=32M  #批量插入数据缓存大小,可以有效提高插入效率,默认为8M
[mysql]
socket=/tmp/mysql.sock
prompt=mysql5729_db01 [\\d]>
[client]
socket=/tmp/mysql.sock

Mysql配置参数介绍

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
MySQL获取配置信息路径
    1、命令行参数
        mysqld_safe --datadir=/data/sql_data
    2、配置文件
        查看配置文件的命令：
        [root@localhost ~]# mysqld --help --verbose | egrep -A 1 'Default options'
        配置文件的有效路径
            /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

MySQL配置参数的作用域
    1、全局参数
        set global 参数名=参数值;
        set @@global.参数名:=参数值;
    2、会话参数 
        set [session] 参数名=参数值;
        set @@session.参数名:=参数值;

内存配置相关参数
    1、确定可以使用的内存的上限
    2、确定MySQL的每个连接使用的内存
        sort_buffer_size
        join_buffer_size
        read_buffer_size
        read_rnd_buffer_size
    3、确定需要为操作系统保留多少内存
    4、如何为缓存池分配内存
        Innodb_buffer_pool_size
            注：设置缓存池的大小的考量标准为:总内存-(每个编程所以需要的内存*连接数)-系统保留内存
        key_buffer_size
        select sum(index_length) from information_schema.tables where engines='myisam'
    
I/O相关配置参数
    Innodo I/O相关配置
    Innodb_log_file_size 单个事务日志的大小
    Innodb_log_files_in_group 控制文件日子的个数
    事务日志总大小 = Innodb_log_files_in_group * Innodb_log_file_size
    Innodb_log_buffer_size = (32M or 128M)
    Innodb_flush_log_at_trx_commint
        0:每秒进行一次log写入cache,并flush log到磁盘
        1[默认]:在每次事务提交执行log写入cache，并flush log到磁盘
        2[建议]:每次事务提交，执行log数据写入到cache中，每秒执行一次flush log到磁盘
    Innodb_flush_method=O_DIRECT
    Innodb_file_per_table = 1
    Innodb_doublewrite = 1

MyISAM I/O相关配置
    delay_key_write
        OFF:每次写操作后刷新键缓冲中的脏块到磁盘
        ON:只对在键表时指定了delay_key_write选项的表使用延迟刷新
        ALL:对所有的MyISAM表都使用延迟建写入
        
安全相关配置参数
    expire_logs_days 指定自动清理binlog的天数
    max_allowed_packet 控制MySQL可以连接的包大小，建议设置为32M，如果使用了主从复制，参数应该设置成一致的
    skip_name_resolve 禁用DNS查找
    sysdate_is_now 确保sysdate()返回确保性日期
    read_only 禁止非super权限的用户写操作  注：建议在主从复制中的从库开启此功能。以确保不能修改从库中的操作，只能从主库同步过来
    skip_slave_start 禁用Salve自动恢复(从库中的设置使用)
    sql_mode 设置MySQL所使用的SQL模式 （谨慎操作，可能会造成MySQL无法执行）
        ① strict_trans_tables 给定的数据如果不能插入到数据库中，对事务引擎会终端操作，对非事务引擎是没有影响的
        ② no_engine_subitiution 在create table中指定engines的时候，如果引擎不可用，不会使用默认引擎建立表
        ③ no_zero_date 不能再表中插入0年0月0日的日期
        ④ no_zero_in_date 不接受一部分的为0的日期
        ⑤ noly_full_group_by 
    
其他常用的配置参数
    sync_binlog 控制MySQL如何向磁盘刷新binlog
    tmp_table_size 和 max_heap_table_size 控制内存临时表大小(不宜设置的太大，以避免内存的溢出)
    max_connections 控制允许的最大连接数(默认为100，有点小，根据自己的业务适当的调整大小)
 评论