专栏首页sktjmysql 常用配置及优化

mysql 常用配置及优化

Mysql常用配置及优化

[client]

该目录下的内容常用来进行localhost登陆,一般不需要修改

port = 3306 # 端口号 socket = /var/lib/mysql/mysql.sock # 套接字文件(localhost登陆会自动生成)

[mysql]

包含一些客户端mysql命令行的配置

no-auto-rehash # 默认不自动补全 auto-rehash自动补全

[mysqld]

mysql优化的配置目录,除硬件和环境配置外,全部优化在此配置,

一般服务器安装只有此配置目录

user = mysql #默认启动用户,一般不需要修改,可能出现启动不成功 port = 3306 #端口号 socket = /var/lib/mysql/mysql.sock #套接字文件 (套接字方式登陆比TCP/IP方式连接快) character-set-server = utf8 #设置数据库服务器默认编码 utf-8 basedir = /usr/local/mysql #数据库安装目录--指定此参数可解决相对路径造成的问题 datadir = /data/mysql #数据库目录,数据库目录切换时需要用到 pid-file = /var/run/mysqld/mysqld.pid #mysql进程文件,可指定自己的进程文件 external-locking = FALSE #外部锁定(非多服务器可不设置该选项,默认skip-external-locking) skip-external-locking #跳过外部锁定 (避免多进程环境下的external locking--锁机制) skip-name-resolve #跳过主机名解析,直接IP访问,可提升访问速度 log-error = /data/log/mysqld.log #错误日志文件 log-warnings #默认为1,表示启用警告信息记录日志,不需要置0即可,大于1时表示将错误或者失败连接记录日志 open_files_limit = 10240 #全局只读变量,文件描述符限制 注:上限其实为OS文件描述符上限,小于OS上限时生效 可用lsof查看限制并修改相应配置

以下配置较为重要

back_log = 600 #在MYSQL暂时停止响应新请求之前,短时间内的多少个请求可以被存在堆栈中。不超过TCP/IP监听队列,建议512倍数

如果系统在短时间内有很多连接,则需要增大该参数的值,该参数值指定到来的TCP/IP连接的监听队列的大小。默认值50。

max_connections = 5000 #MySQL允许最大的进程连接数,如果经常出现Too Many Connections的错误提示,则需要增大此值 max_connect_errors = 6000 #设置每个主机的连接请求异常中断的最大次数,当超过该次数,MYSQL服务器将禁止host的连接请求,

直到mysql服务器重启或通过flush hosts命令清空此host的相关信息

max_allowed_packet = 32M #限制接收数据包的大小,单条数据超过该值时插入或更新失败,

show VARIABLES like '%max_allowed_packet%'; 查看当前限制大小

sort_buffer_size = 8M #每个连接独享,用于优化不能通过sql或者索引优化的group和order等,设置的值应适中

比如:500个连接,500*8 = 4G,将消耗4G内存

join_buffer_size = 8M #用于表间关联缓存的大小,每个连接独享

thread_concurrency = 16 #应设为CPU核数的2倍.

thread_cache_size = 600 #线程缓存变量,减少连接的创建,一般设置规则

1G->8;2G->16;3G->32;根据实际情况可适当加大(个人:2^(n+2))

thread_stack = 1024K #设置MYSQL每个线程的堆栈大小,可设置范围为128K至4GB,默认足够大,一般不用修改 query_cache_size = 128M #查询缓存,设置不宜过大,主要是因为缓存越大,设置失效时间越长。如果高并发写,可以禁用该功能 query_cache_limit = 2M #指定单个查询能够使用的缓冲区大小,默认为1M query_cache_min_res_unit = 4k #大数据查询4k,小数据查询2k,目的是提高缓存命中率

数据库引擎相关参数

default-storage-engine = InnoDB #默认数据库引擎 innodb_data_file_path = ibdata1:1024M:autoextend #指定数据文件,初始大小,指定扩展大小 注意与数据库中初始文件大小保持一致

格式:size-allocation[:autoextend[:max-size-allocation]]

innodb_read_io_threads = 16 #read IO线程,根据cpu核心线程数量设置 innodb_write_io_threads = 16 #write IO线程,根据cpu核心线程数量设置 innodb_thread_concurrency = 48 #服务器有几个CPU就设置为几,建议用默认设置,一般为8 innodb_lock_wait_timeout = 120 #事务获取锁的最长等待时间,超时请求失败 innodb_buffer_pool_size = 80G #类似于myisam的key_pool_size 适当增加可提高命中率,专用服务器可设置为70-80%

innodb_flush_log_at_trx_commit = 2 #默认值1 每次提交日志记录磁盘 2 日志写入系统缓存 0 不提交也记录,不安全,不推荐

innodb_flush_method = O_DIRECT #控制着innodb数据文件及redo log的打开、刷写模式

fdatasync(默认),调用fsync()去刷数据文件与redo log的buffer

O_DSYNC,innodb会使用O_SYNC方式打开和刷写redo log,使用fsync()刷写数据文件

O_DIRECT,innodb使用O_DIRECT打开数据文件,使用fsync()刷写数据文件跟redo log

fsync() 作用:数作用是flush时将与fd文件描述符所指文件有关的buffer刷写到磁盘

innodb_log_files_in_group = 2 #以循环方式将日志文件写到多个文件,默认2 innodb_log_file_size = 4G #数据日志文件大小,较大可提升性能, innodb_log_buffer_size = 512M #日志文件所用的内存大小,以M为单位。缓冲区更大能提高性能,但意外的故障将会丢失数据(开发人员推荐1-8M---不知道为什么)

innodb_file_per_table = 1 #独立表空间 innodb 默认一个表空间

innodb_autoinc_lock_mode = 2 #主要作用于自增列

0 这个表示tradition 传统 得到语句级别的锁,具有连续性和重复性,但影响并发的插入

#1 这个表示consecutive 连续 根据一次性插入的数量生成连续的值,

auto_inc锁不需要一直保持到语句的结束,语句得到了相应的值后就可以提前释放锁

#2 这个表示interleaved 交错 没有auto_inc锁,auto_incremant值可能不是连续的

Percona XtraDB Cluster 5.7. 基于同步复制的多主MySQL集群解决方案,只支持Innodb引擎

需要安装wsrep打过补丁的版本

下载地址:https://www.percona.com/downloads/

pxc_strict_mode = ENFORCING wsrep_cluster_address = gcomm://192.168.66.242,192.168.66.241 #集群中的节点地址,可以使用主机名或IP wsrep_node_address = 192.168.66.241 #本机节点地址,可以使用主机名或IP wsrep_provider = /usr/lib64/galera3/libgalera_smm.so wsrep_sst_method = xtrabackup-v2 #指定SST方式,支持rsync(最快,需要锁表),

mysqldump和xtrabackup,从5.5.33-23.7.6起支持xtrabackup-v2

wsrep_slave_threads = 8 #线程数量 wsrep_cluster_name = Cluster #集群名字,必须统一 wsrep_node_name = Node1 #集群中节点名字,必须唯一 wsrep_sst_auth = "sstuser:8jUthGnAUfWEfJ9" #xtrabackup使用的用户名密码,第一台节点启动时创建,详见安装文档

wsrep_sst_donor='node3,' #一个逗号分割的节点串作为状态转移源,

比如wsrep_sst_donor=node5,node3, 如果node5可用,用node5,不可用用node3,

如果node3不可用,最后的逗号表明让提供商自己选择一个最优的。

transaction_isolation = READ-COMMITTED #事务隔离级别

1.READ-UNCOMMITTED-读未提交 --脏读(开发生产均不建议)

2.READ-COMMITTE-读已提交 --非默认

3.REPEATABLE-READ -可重复读 --可能出现幻影行(Innodb 和 Falcon 通过并发解决幻读问题)

4.SERIALIZABLE -串行 --最高级别 可能出现大量超时和锁竞争

tmp_table_size = 10G #查询生成的临时表大小超过该值时会在硬盘生成MyISAM表,如果存在大量group by 等语句,可调整大小 max_heap_table_size = 5G #内存表最大空间限制 表在磁盘中,数据在内存中 explicit_defaults_for_timestamp=1 #主要针对TIMESTAMP列,不指定默认会自动加上notnull属性,第一列会自动添加current timestamp table-definition-cache = 4096 #表定义相关信息缓存 实际存放的是frm与内存的映射关系 table-open-cache = 4096 #打开表缓存 存放已打开的表句柄

同时设置生效

interactive_timeout = 120 #mysql在关闭一个交互的连接之前所要等待的秒数(客户端连接) wait_timeout = 864000 #mysql在关闭一个非交互的连接之前所要等待的秒数 默认8小时(应用程序调用),

根据应用实际情况决定 show processlist; sleep进程多则相应调小

long_query_time = 2 #慢查询超时设置,默认10秒,记录超过查询时间的语句 slow-query-log=1 #注意之前的版本5.6之前的版本为 log-slow-query 是否记录慢查询日志---作为数据库分析 slow-query-log-file=/data/log/query-slow.log #慢查询日志记录文件

相关二进制文件设置

log_bin #数据库操作二进制记录,数据库备份,复制所需 binlog_format = ROW #ROW(基于行的复制--安全,但是注意并发) STATEMENT(基于sql语句的复制),MIXED(混合模式复制) binlog_cache_size = 4M #二进制日志缓存,提高log-bin记录效率 log_bin_trust_function_creators = 1 #主从复制是需要注意,为了保证主从复制完全一致,需要开启此选项,主从默认阻止函数创建 max_binlog_size = 1G #二进制日志文件大小默认1G 要求大于4096 小于1G expire_logs_days = 7 #清除过期日志

binlog_do_db #此参数表示只记录指定数据库的二进制日志

binlog_ignore_db #忽略某数据库记录

key_buffer_size = 2048M #提高查询命中率,最好不要超过4G,可根据缓存命中率适当调整show global status like 'key_read%'; read_buffer_size = 16M #顺序读缓存,每个连接独享 read_rnd_buffer_size = 64M #随机读缓存,每个连接独享 bulk_insert_buffer_size = 1G #批量插入并且只有向非空表添加数据时才使用该缓存,只对myisam表使用。

主从复制相关--必须开启log-bin

server-id = 19911216 #主从复制必须,并且各服务器具有唯一性 log_slave_updates #配置从服务器的更新是否写入二进制日志,默认是不打开的 replicate-ignore-db = mysql #主从复制默认忽略的数据库,可用","分隔或使用多条记录

replicate-do-db=qrs,login #主从复制指定数据库,","号隔开或使用多条记录

[mysqldump] #数据库全量备份 quick #强制mysqldump从服务器一次一行地检索表中的行 max_allowed_packet = 32M #可接收数据包大小

[isamchk] #在mysqld服务器不使用的情况下修复表或在崩溃状态下恢复表 key_buffer = 1024M sort_buff_size =1024M read_buffer = 16M write_buffer = 16M

[myisamchk] #在mysqld服务器不使用的情况下修复表或在崩溃状态下恢复表 key_buffer = 1024M sort_buff_size = 1024M read_buffer = 16M write_buffer = 16M

[mysqld_safe] #safe方式启动数据库,相比于mysqld,会在服务启动后继续监控服务状态,死机时重启 open-files-limit = 8192

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • python 获取itune中存放的手机短信

    import os import sqlite3 import optparse

    用户5760343
  • Docker 搭建mysql5.7

    1、 docker pull mysql:5.7 mkdir -p /mysql/data mkdir -p /mysql/conf mkdir -p ...

    用户5760343
  • 数据库介绍与分类

    1、sql nosql:not only sql 2、mariadb的默认引擎用XtraDB来替代mysql的InnoDB 3、redis string...

    用户5760343
  • 企业环境下MySQL5.5调优

    山山仙人
  • Mysql配置文件的理解

    小俊丶Eternally
  • Mysql配置文件的理解

    小俊丶Eternally
  • MySQL主从数据库配置和常见问题

    公司运维能力不是太好,数据库最近出了一次问题,导致丢失了一天的数据,并且某个服务宕机一晚上。为了避免再次出现类似问题,我决定添加一个Slave服务器,以避免数据...

    ianzhi
  • Elastic认证工程师考试经验分享

    笔者于2019年10月参加并通过了Elastic Certified Engineer Exam, 在准备考试的三四个月的时间内,对考试的要求,考试的准备,考试...

    michelmu
  • 在家进行Salesforce认证考试的经验

    必须要承认我的上一次考试已经是很久之前的事情了。最近当我开始在Salesforce工作并要求通过一些认证时,我非常感兴趣了解IT是如何推动考试变革的?

    臭豆腐
  • 超详细的mysql数据库参数优化,都总结在这里了

    最近在对各个系统的mysql做一些参数上的优化,也开了慢查询,准备后面针对特定sql再进一步优化。下面主要介绍一下一些优化的参数。

    lyb-geek

作者介绍

精选专题

活动推荐

扫码关注云+社区

领取腾讯云代金券