较为全面的企业级 MySQL 配置调优

落日映苍穹つ 2021-10-19 03:26 275阅读 0赞

#### MySQL配置my.cnf配置优化详解 ####

**用 yum 安装的 MySQL 选用的路径为默认路径，不需要创建，需要创建的路径会特殊备注**

vim /etc/my.cnf
    [client]
    port = 63306		#自定义客户端端口
    socket = /var/lib/mysql/mysql.sock	 #启动是的sock文件位置
    default-character-set = utf8mb4		 #默认字符集 
    
    [mysql]
    auto-rehash 	#auto-rehash 自动补全，输入MySQL命令时候可以自动补全(默认不使用补全no-auto-rehash)
    default-character-set = utf8mb4		#默认字符集
    
    [mysqld]
    ####################### Basic Settings 基本设置 #######################
    port = 63306
    server-id = 50			#MySQL区别的ID号(主从同步的时候不能一致)
    bind-address = 0.0.0.0	#监听的地址(如果做主从不要写为本地地址)
    user = mysql			#用户为MySQL
    datadir = /var/lib/mysql			   #MySQL本身的路径
    socket = /var/lib/mysql/mysql.sock	   #启动是的sock文件位置
    log-error = /var/log/mysqld.log	       #错误日志存放的位置
    pid-file = /var/run/mysqld/mysqld.pid  #记录当前MySQL进程
    skip-locking  #避免MySQL的外部锁定，减少出错几率增强稳定性
    skip_ssl  	  #禁止止掉SSL功能
    max_user_connections= 0 #连接参数值为0,表示没有限制
    
    
    ################### Modify parameter item 针对缓冲区调整配置 #############
    back_log = 512
    #back_log 参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。
    如果系统在一个短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的TCP/IP连接的侦听队列的大小。
    不同的操作系统在这个队列大小上有它自 己的限制。 试图设定back_log高于你的操作系统的限制将是无效的。默认值为80。对于Linux系统推荐设置为小于512的整数。
    
    key_buffer_size = 256M
    #key_buffer_size 指定用于索引的缓冲区大小，增加它可得到更好的索引处理性能。对于内存在4GB左右的服务器该参数可设置为256M或384M。注意：该参数值设置的过大反而会是服务器整体效率降低！
    
    thread_stack = 256K
    #thread_stack 表示每个连接线程被创建时,MySQL给它分配的内存大小,对于8-16G的服务器器设置成256K就可以了了,再大一点的,可以适当增加
    	
    max_allowed_packet = 16M
    #max_allowed_packet 这个参数mysql消息缓冲区的大小,一般设置成 4-16M
    
    sort_buffer_size = 16M
    #sort_buffer_size 查询排序时所能使用用的缓冲区大小,注意：该参数对应的分配内存是每连接独占，如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 6 ＝ 600MB。所以，对于内存在4GB左右的服务器推荐设置为6-8M
    
    join_buffer_size = 8M
    #join_buffer_size 联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享
    
    table_open_cache = 1024 
    #table_open_cache 设置table高速缓存的数量量,可以通过命令show variables like '%open%'; 查看open_files_limit参数,调到512-1024最佳 
    
    net_buffer_length = 16K
    #net_buffer_length 每个客户端连接时。用于维持连接缓冲,最小1k最大1m默认16k 
    
    query_cache_size = 128M 
    #query_cache_size 表示mysql查询结果的缓冲区大小,一般不建议设置太大,一般设置成32M-256M左右为2的倍数
    
    tmp_table_size = 256M 
    #tmp_table_size 该值默认为16M,可调到64-256最佳,线程独占, 太大可能内存不够造成I/O堵塞,如果动态⻚页面面可以适当调大大点 
    
    
    ################### MyISAM parameters MyISAM 引擎参数设置 ###################
    myisam_sort_buffer_size = 128M  
    #myisam_sort_buffer_size MyISAM表发生变化时重新排序所需的缓冲默认8M
    
    key_buffer_size = 32M
    #key_buffer_size 这对MyISAM表来说非常重要。MyISAM表会使用操作系统的缓存来缓存数据，低于16-32MB的，以适应给予磁盘的临时表索引所需
    
    query_cache = 32M
    #query_cache 应用程序有大量读，而且没有应用程序级别的缓存，那么这很有用，常设置为 32-512Mb；在一定的负载压力下，如果缓存命中率太低了，就启用它
    
    read_rnd_buffer_size = 32M
    #read_rnd_buffer_size MySql的随机读（查询操作）缓冲区大小,对于4G到16G内存的服务器器2M-8M；如果你增高此值,可以提高很多ORDER BY的性能.当需要时由每个线程分配
    
    thread_cache_size = 256
    #thread_cache_size 线程缓存，默认256；当一个客户端断开连接后,如果cache中的线程还少于thread_cache_size,则客户端线程被放入cache中.这可以在你需要大量新连接的时候极大的减少线程创建的开销
    
    bulk_insert_buffer_size = 64M
    #bulk_insert_buffer_size 批量插入数据缓存大小，可以有效提高插入效率，默认为8M；(这些插入是,INSERT … SELECT, INSERT … VALUES (…), (…), …, 以及 LOAD DATA INFILE) 更快
    
    
    ################### Innodb parameters Innodb引擎参数设置 ###################
    innodb_thread_concurrency = 8  
    #innodb_thread_concurrency 服务器有几个CPU就设置为几，建议用默认设置，一般为8
    
    innodb_flush_log_at_trx_commit = 2  
    #innodb_flush_log_at_trx_commit 这参数是控制MySQL 磁盘写入策略以及数据安全性的关键参数1最慢的，方式2速度较快也比0安全，
    
    innodb_log_buffer_size = 8M
    #innodb_log_buffer_size 使用默认8M，此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，但意外的故障将会丢失数据.MySQL开发人员建议设置为1－8M之间，
    
    innodb_log_file_size = 4G
    #innodb_log_file_size 使用默认48M，此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能，但也会增加恢复故障数据库所需的时
    通过命令SHOW GLOBAL STATUS的输出看 Innodb_os_log_written的值,把该值除以1024*1024 得到的结果是每分钟处理的 redo日日志大大小小,然后再乘以60得到每小时处理的日志大小, 可以设置允许大大于4G
    
    innodb_buffer_pool_size = 20G
    #innodb_buffer_pool_size #对Innodb表来说非非常重要是缓存innodb表的索 引,一般配置的值是5GB(8GB内存),20GB(32GB内存),40GB(64GB内存) 
    
    innodb_lock_wait_timeout = 100 
    #innodb_lock_wait_timeout 指示 MySQL在允许其他事务修改那些最终受事务回滚的数据之前要等待多长时间(秒数)
    
    innodb_file_per_table = ON
    #innodb_file_per_table 使得每个表都有自自已独立立的表空间
    
    
    ########################## log settings 日志设置 #########################
    开启慢查询日志(需要创建对应的文件路径)
    long_query_time = 2	 #指执行超过2S的SQL会被记录下来 
    slow_query_log=1		#1表示开启慢查询，0表示关闭慢查询 
    slow-query-log-file = /home/mysql/slow_log/slow.log   #慢查询日志存储的路径 
    
    开启二进制日志(在启用主从同步时才开启；还得创建对应的文件路径)
    log_bin=ON		#打开二进制文件
    log_bin_basename=/home/mysql/bin_log/mysql-bin  #二进制文件存放的位置
    log_bin_index=/var/lib/mysql/mysql-bin.index	#指定的是binlog文件的索引文件，文件管理了所有的binlog文件的目录
    
    
    ####################### AB Replication 主从复制配置 #######################
    开启主配置从同步配置(不配置主从同步的时候不需要配置)
    binlog-do-db = db
    #binlog-do-db 需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可 
    
    binlog-ignore-db = information_schema
    #binlog-ignore-db 不同步的二进制数据库名,如果不设置可以将其注释掉
    
    log-slave-updates = true
    #log-slave-updates 将从服务器从主服务器收到的更新记入到从服务器自己的二进制日志文件中(即TRUE和FALSE)
    
    sync_binlog = 1  
    #sync_binlog 设为1就是把MySql每次发生的修改和事件的日志即时同步到硬盘上(这个参数性能消耗很大，但可减小MySQL崩溃造成的损失)
    
    binlog_format = Row  
    #binlog_format 复制模式 Statement, Row, mixed
    
    skip_slave_start = 1
    #skip_slave_start 防止从服务器在崩溃后自动开启，以给你足够的时间修复。
    
    max_binlog_size = 200M 	    
    #max_binlog_size 指定二进制日志的大小
    
    expire_logs_days = 7
    #expire_logs_days 二进制日志自动删除的天数，默认值为0,表示“没有自动删除”，启动时和二进制日志循环时可能删除  
    
    read_only = on
    ###read_only 这个配置针对从库配置，主库不需要配置，是否指定此库为只读
    
    relay-log = /home/mysql/relay_log/relay-log
    #开启中继日志，并且创建对应的目录

#### MySQL 优化配置(针对4核32G起且配置主从的主库机器配置) ####

vim /etc/my.cnf
    [client]
    port = 63306
    socket = /var/lib/mysql/mysql.sock
    default-character-set = utf8mb4
    
    [mysql]
    auto-rehash	#auto-rehash
    default-character-set = utf8mb4
    
    [mysqld]
    ####################### Basic Settings  #######################
    port = 63306
    server-id = 50
    bind-address = 0.0.0.0
    user = mysql
    datadir = /var/lib/mysql
    socket = /var/lib/mysql/mysql.sock
    log-error = /var/log/mysqld.log
    pid-file = /var/run/mysqld/mysqld.pid
    skip-locking
    skip_ssl
    max_user_connections= 0
    
    
    ##################### Modify parameter item  ##################
    back_log = 512
    key_buffer_size = 2048M
    thread_stack = 512K
    max_allowed_packet = 16M
    sort_buffer_size = 16M
    join_buffer_size = 8M
    table_open_cache = 1024 
    net_buffer_length = 16K
    query_cache_size = 128M 
    tmp_table_size = 256M
    
    
    ####################### MyISAM parameters ########################
    myisam_sort_buffer_size = 128M  
    key_buffer_size = 32M
    query_cache = 32M
    read_rnd_buffer_size = 16M
    thread_cache_size = 256
    bulk_insert_buffer_size = 64M
    
    
    ####################### Innodb parameters  #######################
    innodb_thread_concurrency = 8
    innodb_flush_log_at_trx_commit = 2
    innodb_log_buffer_size = 8M
    innodb_log_file_size = 4G
    innodb_buffer_pool_size = 20G
    innodb_lock_wait_timeout = 100 
    innodb_file_per_table = ON
    
    
    ########################## log settings ###########################
    开启慢查询日志(需要创建对应的文件路径)
    long_query_time = 2
    slow_query_log=1
    slow-query-log-file = /home/mysql/slow_log/slow.log
    
    开启二进制日志(在启用主从同步时才开启；还得创建对应的文件路径)
    log_bin=ON
    log_bin_basename=/home/mysql/bin_log/mysql-bin
    log_bin_index=/var/lib/mysql/mysql-bin.index
    
    
    ####################### AB Replication  #######################
    开启主配置从同步配置(不配置主从同步的时候不需要配置)
    binlog-do-db = db
    binlog-ignore-db = information_schema
    log-slave-updates = true
    sync_binlog = 1  
    binlog_format = Row  
    skip_slave_start = 1
    max_binlog_size = 200M 	    
    expire_logs_days = 7
    read_only = on
    relay-log = /home/mysql/relay_log/relay-log