硬件的优化
1、磁盘寻道能力（磁盘I/O）,
MySQL每一秒钟都在进行大量,复杂的查询操作,磁盘的读写量很高,所以磁盘I/O是约制MySQL性能的最大因素之一
SAS 15000转的硬盘,用6快这样的硬盘做RAID 1+0(raid5不建议使用),可以选择固态硬盘SSD来替代SAS硬盘做RAID 1+0
2、CPU对于MySQL的影响也至关重要,选择运算能力强悍的CPU,推荐使用DELL R720(双4核)
3、内存不要小于2GB，推荐使用4GB以上的物理内存
配置文件优化 https://mirrors.linyaohong.com/conf/mysql/my.cnf
参考文档 ： https://www.cnblogs.com/angryprogrammer/p/6667741.html https://www.cnblogs.com/kevingrace/p/6133818.html
https://blog.csdn.net/tuzhen007/article/details/9792577
[client]
port = 3306
socket = /data/3306/mysql.sock
default-character-set = utf8
[mysql]
prompt="MySQL [\d]> "
# 登陆提示符,多实例登陆需要指定配置文件
no-auto-rehash
# 不自动重新进行哈希运算,该选项使mysql启动得更快,每次登录与切换db时,不会刷新信息,减轻DB负担
[mysqld]
#skip-grant-tables
# 忘记密码时可以跳过密码验证
init-connect = 'SET NAMES utf8'
# 非super privilege用户连接数据库时隐式执行的SQL语句,如果语句错误,则用户执行任何语句都会报错
character-set-server = utf8
# 数据库和数据库表的默认字符集
#default-storage-engine= InnoDB #设置MySQL的默认存储引擎
bind-address = 0.0.0.0
# MySQL服务监听的IP
user = mysql
# 编译时--user=mysql指定
port = 3306
socket = /data/3306/mysql.sock
# 用户在Linux/Unix环境下客户端连接可以不通过TCP/IP网络而直接使用unix socket连接MySQL
basedir = /application/mysql
# mysql程序所存放路径
datadir = /data/3306/data
# mysql数据存放目录,重要 ******
pid-file = /data/3306/mysql.pid
skip-external-locking
# MySQL选项可以避免外部锁定
skip-name-resolve
# 禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。
# 但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求
# | 2007 | unauthenticated user | 192.168.4.29:58519 | | Connect | | login | |
# 如果不加就可能会报上面这样的错误,在show processllist; 里存在大量这样的线程
open_files_limit = 10000
# 不能大于 ulimit -n MySQL 5.7中为5000 MySQL能打开文件的最大个数,
# 如果出现too mant open files之类的就需要调整该值了。
back_log = 600
# 默认50 back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。
# 也就是说，如果MySql的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，
# 该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。
# 将会报：unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时.
# back_log值不能超过TCP/IP连接的侦听队列的大小。若超过则无效，
# 查看当前系统的TCP/IP连接的侦听队列的大小命令：cat /proc/sys/net/ipv4/tcp_max_syn_backlog
# 对于Linux系统推荐设置为小于512的整数。
max_connections = 800
# 同时进行的客户端连接的最大允许数量
max_connect_errors = 3000
#设置每个主机的连接请求异常中断的最大次数,当超过该次数，MySQL服务器将禁止host的连接请求，
# 直到MySQL服务器重启或通过flush hosts命令清空此host的相关信息
table_open_cache = 614
# table_cache指定表高速缓存的大小。
# 每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。
# 通过检查Open_tables和Opened_tables，可以决定是否需要增加table_cache的值(SHOW STATUS LIKE 'Open%tables';)
# 如果open_tables等于table_cache，并且opened_tables在不断增长，那么需要增加table_cache的值
# 注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败
# 最大2048 8G>1024 16>2048 4>614
max_allowed_packet = 16M
# 服务器一次能处理最大的查询包的值，也是服务器程序能够处理的最大查询
# 有时候大的插入和更新会被max_allowed_packet 参数限制掉，导致失败
sort_buffer_size = 4M
# 默认2M 设置查询排序时所能使用的缓冲区大小,对于内存在4GB左右的服务器来说，推荐将其设置为2MB~8MB
# show global status like 'sort%'; 来决定,Sort_merge_passes | 0 | # 若值过大,增加sort_buffer_siz
join_buffer_size = 8M
# 联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享
thread_cache_size = 100
# #设置Thread Cache池中可以缓存的连接线程最大数量，可设置为0~16384，默认为0.
# 这个值表示可以重新利用保存在缓存中线程的数量，当断开连接时如果缓存中还有空间，那么客户端的线程将被放到缓存中;
# 如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建，
# 如果有很多线程，增加这个值可以改善系统性能。
# 通过比较Connections和Threads_created状态的变量，可以看到这个变量的作用。
# 根据物理内存设置规则如下:1GB内存我们配置为8,2GB内存我们配置为16,3GB我们配置为32,4GB或4GB以上我们给此值为64或更大的值。
# thread_concurrency = 2
# CPU核数的2倍,这个参数在mysql 5.6.1中已经被标记为过时，在5.7.2版本的mysql中被移除,该参数取值为服务器逻辑CPU数量 x 2
query_cache_type = 1
# 开启查询缓存，0为关闭
query_cache_size = 256M
# 查询缓存,不开启请设为0
#指定MySQL查询缓冲区的大小。show global status like 'qcache%';具体来决定设置多少，
# 如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况;
# 如果Qcache_hits的值非常大，则表明查询缓冲使用得非常频繁。
# 如果Qcache_hits的值非常小反而会影响效率，那么可以考虑不用查询缓冲。
# 对于Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多。
query_cache_limit = 2M
# 只有小于此设置值的结果才会被缓存
query_cache_min_res_unit = 2k
# 设置查询缓存分配内存的最小单位，要适当第设置此参数，可以做到为减少内存快的申请和分配次数，
# 但是设置过大可能导致内存碎片数值上升。默认值为4K，建议设置为1K~16K
thread_stack = 256K
# 设置MySQL每个线程的堆栈大小，默认值足够大，可满足普通操作。可设置范围为128KB至4GB，默认为192KB
tmp_table_size = 2048M
# 线程独占，太大可能内存不够I/O堵塞
# 设置内存临时表最大值。如果超过该值，则会将临时表写入磁盘，其范围1KB到4GB
# show global status like 'created_tmp%'; # 查看临时表使用的情况
# 如果是在磁盘上创建临时表,Created_tmp_disk_tables增加
# max_heap_table_size = 2048M
# 和tmp_table_size大小一样也可以,
# 独立的内存表所允许的最大容量,这个变量和tmp_table_size一起限制了内部内存表的大小
slow_query_log = 1
# 开启慢查询
long_query_time = 3
# 慢查询记录时间为3秒
#log_queries_not_using_indexes=on #记录未使用索引的
slow-query-log-file = /data/3306/3306_slow.log
#慢查询日志存放位置
relay-log = /data/3306/relay-bin
# relay-log日志记录的是从服务器I/O线程将主服务器的二进制日志/读取过来/记录到从服务器本地文件,
# 然后SQL线程会读取relay-log日志的内容并应用到从服务器
relay-log-info-file = /data/3306/relay-log.info
# 从服务器用于记录中继日志相关信息的文件
ft_min_word_len = 4 #默认是该值。fulltext只会对4个字或者4个字以上的关键字进行建立索引操作
server-id = 1 # 表示本机的序列号为1,如果做主从,或者多实例,server-id一定不能相同
log-bin = /data/3306/mysql-bin # 表示开启binlog
binlog_format = mixed # binlog日志格式，mysql默认采用statement
expire_logs_days = 10 # binlog过期清理时间,默认值为0,表示“没有自动删除
max_binlog_size = 512M # 二进制日志文件的最大长度,默认1.1G
binlog_cache_size = 1M # binlog缓存大小 show status like 'binlog_%'; 只要 Binlog_cache_disk_use为 0 即可
max_binlog_cache_size = 2M # 最大的二进制Cache日志缓冲尺寸
# read-only # 主从复制时配置从库只读
# log-slave-updates # 主从复制时,从库也写binlog
# slave-skip-errors = 1032,1062 # 主从复制时,从库可以跳过的错误数字值,
# https://mirrors.linyaohong.com/conf/mysql/MySQL_master_slave_error.txt
# replicate-ignore-db = mysql # 忽略 mysql记录二进制日志
# replicate_wild_do_table = mysql.table01 # 主从同步的时,从库忽略同步的库
key_buffer_size = 384M
# 是用于索引块的缓冲区大小，增加它可得到更好处理的索引,
# 是对MyISAM表性能影响最大的一个参数
# 严格说是它决定了数据库索引处理的速度，尤其是索引读的速度。
# 4GB- 384M 8GB- 512M 16G 1024M
# show global status like 'key_read%'; Key_buffer_size使用情况
# 索引命中率 = (1 - (Key_reads / Key_read_requests)) * 100%
read_buffer_size = 4M
# 读查询操作所能使用的缓冲区大小,和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享
read_rnd_buffer_size = 8M
# 设置进行随机读的时候所使用的缓冲区。此参数和read_buffer_size所设置的Buffer相反，
# 一个是顺序读的时候使用，一个是随机读的时候使用。但是两者都是针对与线程的设置，
# 每个线程都可以产生两种Buffer中的任何一个。默认值256KB，最大值4GB
bulk_insert_buffer_size = 8M
# 如果经常性的需要使用批量插入的特殊语句来插入数据,可以适当调整参数至16MB~32MB,建议8MB。
# lower_case_table_names = 1
# 实现MySQL不区分大小
# lower_case_table_names=0 表名存储为给定的大小和比较是区分大小写的
# lower_case_table_names = 1 表名存储在磁盘是小写的，但是比较的时候是不区分大小写
# lower_case_table_names=2 表名存储为给定的大小写但是比较的时候是小写的
# unix,linux下lower_case_table_names默认值为 0 .Windows下默认值是 1 .Mac OS X下默认值是 2
#myisam_sort_buffer_size = 64M # MyISAM表发生变化时重新排序所需的缓冲。一般64M足矣
#myisam_max_sort_file_size = 10G # 当重新建索引（REPAIR，ALTER，TABLE，或者LOAD，DATA，TNFILE）时，MySQL被允许使用临时文件的最大值
#myisam_repair_threads = 1 # 如果一个表拥有超过一个索引, MyISAM 可以通过并行排序使用超过一个线程去修复他们
#myisam_recover # 自动检查和修复没有适当关闭的 MyISAM 表
innodb_additional_mem_pool_size = 16M
# MySQL 5.7.4 中移除
# 是 InnoDB 用来保存数据字典信息和其他内部数据结构的内存池的大小，单位是 byte，参数默认值为8M。
# 数据库中的表数量越多，参数值应该越大，
# 如果 InnoDB 用完了内存池中的内存，就会从操作系统中分配内存，同时在 error log 中打入报警信息
innodb_buffer_pool_size = 512M
# Innodb优化首要参数。专用的数据库服务器下需要设置成物理内存的80%大小。不要设置太大，以免因此与操作系统进行分页竞争。
# 注意，在32位系统中，每个进程被限制在2-3.5G，因此不要设置太高。
# MySQL默认配置文件my-innodb-heavy-4G（中设置的是innodb_buffer_pool_size = 2G）
# 4G-512M 8G-1028M 16G-4096M
# show status like 'Innodb_buffer_pool_read%';
# | Innodb_buffer_pool_read_requests | 335568 |
# | Innodb_buffer_pool_reads | 1354 |
# 命中率 = (335568-1354)/335568 * 100% = 99% 已经很不错了
innodb_data_file_path = ibdata1:256M:autoextend
#设置配置一个可扩展大小的尺寸为128MB的单独文件,名为ibdata1.没有给出文件的位置，所以默认的是在MySQL的数据目录内
# innodb_file_io_threads = 4 # innodb_file_io_threads参数默认是4，该参数在Linux系统上是不可更改的，
# 但Windows系统上可以调整。这个参数的作用是：InnoDB使用后台线程处理数据页上读写I/O（输入输出）请求的数量。
# 在MySQL5.5.X版本中，或者说是在InnoDB Plugin1.0.4以后，就用两个新的参数，
# 即innodb_read_io_threads和innodb_write_io_threads，取代了innodb_file_io_threads
# 如此调整后，在Linux平台上就可以根据CPU核数来更改相应的参数值了，默认是4。
innodb_read_io_threads = 4
innodb_write_io_threads = 4
# 假如CPU是2颗8核的，那么可以设置：
# innodb_read_io_threads = 8
# innodb_write_io_threads = 8
# 如果数据库的读操作比写操作多，那么可以设置：
# innodb_read_io_threads = 10
# innodb_write_io_threads = 6
innodb_thread_concurrency = 0
# 限制线程的数量 0 为 不限制
# 服务器有几个CPU就设置为几，建议用默认设置，一般设为8
# https://www.cnblogs.com/mydriverc/p/8301644.html
innodb_flush_log_at_trx_commit = 2
# 当设置为0 该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
# 当设置为1 该模式是最安全的，但也是最慢的一种方式。在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。
# 当设置为2 该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。
# https://www.linyaohong.com/plus/view.php?aid=259
innodb_log_buffer_size = 16M
# 默认为1MB，通常设置为8~16MB就足够了
innodb_log_file_size = 256M
# 如果对 Innodb 数据表有大量的写入操作，那么选择合适的 innodb_log_file_size 值对提升MySQL性能很重要。
# 然而设置太大了，就会增加恢复的时间，因此在MySQL崩溃或者突然断电等情况会令MySQL服务器花很长时间来恢复
# https://blog.51cto.com/kaifly/2114068?source=dra
# 通过如果命令,可以计算出没分写入量
# a=$(mysql -uroot -p'b3b65e7568a0ed28' -e "show engine innodb status\G"| grep "Log sequence number" | awk '{print $4}'); sleep 60; b=$(mysql -uroot -p'b3b65e7568a0ed28' -e "show engine innodb status\G" | grep "Log sequence number" | awk '{print $4}'); let "res=($b-$a)*60/1024/1024";echo $res
innodb_log_files_in_group = 3
# 该变量控制日志文件数。默认值为2。日志是以顺序的方式写入
# 为提高性能,MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3
innodb_file_per_table = 0
# InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间。0关闭，1开启
# 独立表空间优点：
# 1、每个表都有自己独立的表空间。
# 2、每个表的数据和索引都会存在自己的表空间中。
# 3、可以实现单表在不同的数据库中移动。
# 4、空间可以回收（除drop table操作处，表空不能自己回收。）
innodb_open_files = 500
# 限制Innodb能打开的表的数据,默认为300,数据库里的表特别多的情况,可以适当增大为1000,
# innodb_open_files的大小对InnoDB效率的影响比较小。
# 但是在InnoDBcrash(崩溃)的情况下,innodb_open_files设置过小会影响recovery(修复)的效率。
# 所以用InnoDB的时候还是把innodb_open_files放大一些比较合适。
# MySQL默认配置文件my-innodb-heavy-4G（无相关设置）
# MySQL默认配置文件my-innodb-heavy-4G（无相关设置）
innodb_purge_threads = 1
#>= 5.6.5)的版本中该值默认为1，最大值为32。默认值1表示innodb的purge操作被分离到purge线程中，master thread不再做purge操作
innodb_max_dirty_pages_pct = 90
# InnoDB主线程刷新缓存池中的数据 show engine innodb status\G; 有空整理
innodb_lock_wait_timeout = 120
# #InnoDB事务被回滚之前可以等待一个锁定的超时秒数。InnoDB在它自己的锁定表中自动检测事务死锁并且回滚事务。
# InnoDB用locak tables 语句注意到锁定设置。默认值是50秒
interactive_timeout = 7200
# 服务器关闭交互式连接前等待活动的秒数
wait_timeout = 7200
# 服务器关闭非交互连接之前等待活动的秒数
[mysqldump]
quick
max_allowed_packet = 64M
# 设定在网络传输中一次消息传输量的最大值。系统默认值为1MB，
# 最大值是1GB，必须设置为1024的倍数。单位为字节
[mysqld_safe]
pid-file = /data/3306/mysql.pid
log-error = /data/3306/3306_error.log
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
[mysqlhotcopy]
interactive-timeout
