本文主要是针对以前LAMP，以及默认用apt安装的mysql。数据文件、日志文件都在一起；innodb_buffer_pool默认用128M。如果你排查问题，最后发现是因为mysql的安装配置不对，是否一口老血要喷出来。同时给MySQL数据库安装做参考。
关于MySQL数据库优化，我这里分成5部分：1.服务器配置优化，2.MySQL安装优化，3.MySQL配置优化，4.数据库设计优化，5.针对MySQL的SQL优化。其中123都是针对数据库运行环境的优化，45是在业务运行过程中，根据业务运行情况进行的适配调整。一般这些工作都是DBA的活，但是对于开发来说，多了解点无大碍，毕竟技多不压身。而且如果在安装前不把这些设置好，也会影响数据库性能，最终导致应用慢，并且这个也比较隐蔽，一般开发是很难发现问题。
1.服务器配置优化
数据库作为应用中一个重要的组件，业务数据都存放在数据库，业务操作都需要访问数据库，为了保证数据库的性能和可靠性，选择合适的硬件设备非常重要。
CPU选型
MySQL 5.1可以使用4核，MySQL 5.5可以使用24核，MySQL 5.6可以使用64核，忘记5.6还是5.7的那个版本已经支持128核（槽点：PG没有限制，有多少核心，它都能用上）。mysql的每个连接mysqld进程都会创建一个线程去处理，也就是每个客户端的sql命令只能用一个核心，要提高SQL执行效率，高频多核的CPU是个不错的选择。所以选CPU就看核心数、频率、缓存。
- 核心数量：CPU的核心数量越多，能够同时处理的请求就越多。建议选择具有至少8个核心的CPU。
- 频率：CPU的频率越高，处理速度越快。建议选择主频在3GHz以上的CPU。
- 缓存：CPU的缓存越大，能够缓存的数据就越多，可以提高查询性能。建议选择具有至少30MB缓存的CPU。
MySQL运行服务器的CPU硬件优化总结如下：
1、选择 Performance Per Watt Optimized（DAPC）作为系统配置，以最大化功耗性能，而不是节能模式（禁用高计算节点）。节能模式在低频和高频性能转换期间容易出现错误。
2、CPU优先选择高主频以提高计算能力；其次，可以选择多个内核进行多线程并发处理和多实例部署。
3、关闭C1E（增强型空闲电源管理状态转换）和C状态。DB服务器不需要节能和省电操作。默认情况下，它处于启用状态。建议关闭DB服务器以提高CPU效率。
4、数据库服务器选择具有高主频和多核计数的CPU类型，并同时打开最大性能和关闭CPU CIE和C状态。高频加速SQL执行，多核解决并发问题。
内存选型
行内古话“要想性能好、内存少不了”。如果内存能装下你所有的业务数据，那么你想慢都难。当然这也是一句屁话。一般估算三五年的业务量，能将20%的业务热数据装载到内存，就已经很好了。一般数据库服务器，我们会将50%-90%的内存分给mysql，操作系统留个512MB基本够用。所以选内存，够大即可，有钱的土豪客户豪横任性。
- 容量：内存的容量越大，能够存储的数据就越多。建议根据实际需求选择内存容量，一般来说，每个GB的数据需要至少8GB的内存。对于1000个用户的场景，建议选择至少16GB的内存容量。
- 速度：内存的速度越快，访问数据的速度就越快。建议选择DDR4内存，其主频至少为2133MHz。
- 通道数：内存的通道数越多，数据传输的效率就越高。建议选择具有至少2个内存通道的主板。
MySQL的内存优化建议总结如下：
1、选择内存频率的最大性能。
2、大内存。renice mysql-pid防止mysql在OOM期间被强制终止。Linux的一种粗暴自我保护机制，当Linux内核发现物理内存不足时，首先会回收可回收的内存，如读写文件时的页缓存、延迟释放的空闲slab内存页等。如果Linux内核完成内存回收，还是不能解决内存不足，这个时候臭名昭著的杀手就来了“OOM Killer”，它会拿起它42米的大砍刀，率先砍掉占用内存最大的进程。大哥，这是数据库服务器，占内存最多的进程毫无疑问是mysqld。可怜的小海豚，就这样惨死了，更可怕的是你的业务数据丢失了。
$ echo -1000 > /proc/mysqld的进程id/oom_score_adj
在内核计算进程最坏分数值时，会加上进程的 oom_score_adj（OOM建议值）值。如果将此值设置为 -1000 时，那么系统将会禁止 OOM killer 杀死此进程。
如果数据库有重启，则每次都需要来设置一下。
panic_on_oom：用来控制当内存不足时该如何做。
cat /proc/sys/vm/panic_on_oom
值为0：内存不足时，启动 OOM killer。
值为1：内存不足时，有可能会触发 kernel panic(系统重启)，也有可能启动 OOM killer。
值为2：内存不足时，表示强制触发 kernel panic，内核崩溃GG(系统重启)。
这个参数建议不修改,保持0
mysql内存使用计算
内存占用=共享内存+客户端链接线程内存+PS占用内存+数据字典占用内存+内存引擎占用内存
SELECT ( @@key_buffer_size
+ @@innodb_buffer_pool_size
+ @@innodb_log_buffer_size
+ @@max_connections * (
@@read_buffer_size
+ @@read_rnd_buffer_size
+ @@sort_buffer_size
+ @@join_buffer_size
+ @@binlog_cache_size
+ @@thread_stack
+ @@tmp_table_size
+ @@max_heap_table_size))/1024/1024 as All_memory_mb
1. 使用下述语句查询各个模块的内存占用(查看当前哪个模块内存占用多)
SELECT
SUBSTRING_INDEX(event_name,'/',2) AS code_area, sys.format_bytes(SUM(current_alloc)) AS current_alloc
FROM sys.x$memory_global_by_current_bytes
GROUP BY SUBSTRING_INDEX(event_name,'/',2)
ORDER BY SUM(current_alloc) DESC;
select *
from sys.x$memory_global_by_current_bytes
where event_name like "memory/sql/%"
order by current_alloc desc;
2. 查看具体哪个连接占用内存多
select thread_id, event_name, SUM_NUMBER_OF_BYTES_ALLOC
from performance_schema.memory_summary_by_thread_by_event_name
order by SUM_NUMBER_OF_BYTES_ALLOC desc limit 20;
3. 查看占用内存最多的连接的详细信息
select *
from performance_schema.threads
where THREAD_ID = xxx;
3、对于数据库服务器，首选大内存，同时启用最大性能，禁用NUMA。numa本来是给cpu并发用的，让每个核心都有自己独有的内存块。但是问题是一个32GB的内存，被你4核心cpu干成8GB。结果业务要放16GB的内存数据，在32GB内存的服务器上却放不下。
一、检查OS是否开启NUMA
# numactl --hardware
二、Linux OS层面禁用NUMA
1、修改 grub.conf
# vi /boot/grub/grub.conf
default=0
timeout=5
hiddenmenu
foreground=000000
background=ffffff
splashimage=(hd0,0)/boot/grub/oracle.xpm.gz
title Trying_C0D0_as_HD0
root (hd0,0)
kernel /boot/vmlinuz-2.6.18-128.1.16.0.1.el5 root=LABEL=DBSYS ro bootarea=dbsys rhgb quiet console=ttyS0,115200n8 console=tty1 crashkernel=128M@16M numa=off
initrd /boot/initrd-2.6.18-128.1.16.0.1.el5.img
2、重启Linux操作系统
# /sbin/reboot
3、确认OS层面禁用NUMA是否成功
# cat /proc/cmdline
root=LABEL=DBSYS ro bootarea=dbsys rhgb quiet console=ttyS0,115200n8 console=tty1 crashkernel=128M@16M numa=off
# numactl --hardware4、选择内存分配器
LINUX内核也有内存分配器,只是它处于系统内核层. 这里用户空间内存我们要替换其它分配器,因为malloc函数效率不高也易造成内存泄漏。
tcmalloc的算法优化了glibc的malloc等内存分配函数，通过预分配大内存然后分割来避免内存碎片，同时提高内存分配效率，使用场景为多线程，大内存操作
在my.cnf添加参数
[mysqld_safe]
malloc-lib=tcmalloc
启动mysql 查看是否已加载
lsof -n | grep -i tcmalloc
jemalloc
jemalloc-3.6.0-1.el7.x86_64.rpm
https://repo.percona.com/yum/release/7/RPMS/x86_64/jemalloc-3.6.0-1.el7.x86_64.rpm
rpm -ivh jemalloc-3.6.0-1.el7.x86_64.rpm
my.cnf中添加配置，并重启mysql（mysql 5.5+）
[mysqld_safe]
malloc-lib=/usr/local/lib/libjemalloc.so
[root@localhost home]# lsof -n | grep jemalloc
mysqld 8114 root mem REG 253,2 2264568 71 /home/Percona8.0/lib/mysql/libjemalloc.so.1
mysqld 8114 8117 root mem REG 253,2 2264568 71 /home/Percona8.0/lib/mysql/libjemalloc.so.1
mysqld 8114 8118 root mem REG 253,2 2264568 71 /home/Percona8.0/lib/mysql/libjemalloc.so.1
mysqld 8114 8119 root mem REG 253,2 2264568 71 /home/Percona8.0/lib/mysql/libjemalloc.so.15、使用大页内存
LINUX 系统对大页内存不计算在OOM得分里,如何给MYSQL分配并使用上
大页内存请看探索MYSQL开启大页内存 MYSQL 大页内存+锁定内存。
为了提高性能，尤其是在处理大量内存和高并发时，MySQL配置大页内存是个不错的选择。以下是配置MySQL使用大页内存的基本步骤：
-
确保系统支持大页内存（Transparent HugePages，THP）。
-
禁用THP。
-
配置MySQL的innodb_buffer_pool_size到适当的大小。
以下是在Linux系统上禁用THP的命令：
# 临时禁用（不会在重启后持续）
echo 'never' > /sys/kernel/mm/transparent_hugepage/enabled
echo 'never' > /sys/kernel/mm/transparent_hugepage/defrag
1. 安装大内存页支持的操作系统
在开始之前，你需要确保你的操作系统支持大内存页。大内存页一般在Linux系统中得到支持，因此你需要确保你的操作系统是Linux，并且已经安装了相应的内核。
2. 配置操作系统
一旦你的操作系统支持大内存页，你需要对操作系统进行一些配置。具体来说，你需要修改sysctl.conf文件，以便启用大内存页。
首先，打开sysctl.conf文件：
sudo vi /etc/sysctl.conf
然后，添加以下内容到文件末尾：
vm.nr_hugepages=128
保存并关闭文件。
3. 重新加载系统配置
为了使配置生效，你需要重新加载系统配置。运行以下命令：
sudo sysctl -p
4. 配置MySQL
现在，你需要对MySQL进行一些配置，以便启用大内存页。
首先，打开MySQL配置文件my.cnf：
sudo vi /etc/my.cnf
然后，找到[mysqld]部分，并添加以下内容：
[mysqld]
innodb_buffer_pool_size = 10G # 假设你有10GB物理内存可以分配给InnoDB缓冲池
# 启用大内存页
large-pages
保存并关闭文件。
5. 重启MySQL
为了使MySQL的配置生效，你需要重启MySQL服务。运行以下命令：
sudo systemctl restart mysql
# 要想永久禁用，可以将上述命令添加到启动脚本中
然后，在MySQL配置文件（my.cnf或my.ini）中设置InnoDB缓冲池的大小：
确保重启MySQL服务使配置生效。
请注意，在某些系统上，禁用THP可能会对系统性能产生轻微影响，特别是在不支持大页内存的系统上，这些命令可能不需要执行。在配置MySQL之前，应当充分了解系统的内存管理策略。磁盘选型
硬盘是MySQL服务器的存储设备，用于存储数据库的数据文件和日志文件等。对于磁盘的硬件要求，数据库是 IO型负载的软件。首选 SSD 和 PCIe SSD，并强制使用高速机械硬盘。建议在运行数据库时放弃使用机械硬盘。
IO类型方面，MySQL Undo Log为顺序写入和随机读取，Redo Log 和 Binlog 为顺序写入与顺序读取，OLTP 场景以随机IO为主，OLAP 场景以顺序IO为主，其存储结构 MyISAM 为堆表，InnoDB为索引组织表。
MySQL磁盘硬件优化原则：
1、RAID策略选择：Sata SSD和PCIe SSD不需要RAID。机械硬盘首选RAID 10，其次是RAID 5。
2、RAID CACHE&BBU选择：购买的阵列卡同时配备CACHE和BBU模块，可以提高机械磁盘IOPS，定期检查或监控CACHE和BBBU模块的运行状况，并确保在发生事故时数据不会丢失。 磁盘类型选择：首选SSD或PCIe SSD，机械磁盘采用高速硬盘。
3、读/写策略选择：当有阵列卡时，将阵列写策略设置为WB或强制WB（无电池），并禁止WT。同时，关闭仅用作写缓存的显示预读策略。
在选择硬盘时，需要考虑以下因素：
- 类型：硬盘的类型有HDD和SSD两种，SSD的速度比HDD快得多，但价格也更高。建议选择SSD作为数据库的存储设备。
- 容量：硬盘的容量越大，能够存储的数据就越多。建议根据实际需求选择硬盘容量，一般来说，每个GB的数据需要至少10GB的硬盘空间。对于1000个用户的场景，建议选择至少500GB的硬盘容量。
- IOPS：IOPS是每秒输入输出操作的次数，它决定了硬盘的读写速度。建议选择具有高IOPS的SSD硬盘。
网卡选型
网络接口卡是MySQL服务器与客户端进行通信的关键组件之一。在选择NIC时，需要考虑以下因素：
- 速度：NIC的速度越快，数据传输的速度就越快。建议选择具有1Gbps或更高速度的NIC。
- 端口数：NIC的端口数越多，能够连接的设备就越多。建议选择具有至少4个端口的NIC。
- 支持的技术：NIC支持的技术包括TCP/IP、RDMA等，需要根据实际需求选择合适的技术。
2.服务器配置优化之内核调整
1.选择合适的IO调度
对于mysql的系统，如果是SSD，那么应该使用NOOP调度算法，如果是磁盘，就应该使用Deadline调度算法。默认是CFQ
echo dealine > /sys/block/{DEV-NAME}/queue/scheduler
2.选择良好的文件系统
文件系统强烈推荐: XFS （使用df -Th查看）
格式化命令：mkfs.xfs -f /dev/sdb1
3.选择更快的存储设备对于数据库可以选择SSD硬盘
4.优化挂载文件系统参数挂载XFS参数：(rw, noatime,nodiratime,nobarrier)挂载ext4参数：ext4 (rw,noatime,nodiratime,nobarrier,data=ordered)
方法一：修改/etc/fastb文件，将default修改为上面
方法二：热挂载：mount -o remount,rw,noatime /dev/sda3 /var/
5.硬件层相关优化CPU
一、选择Performance Per Watt Optimized(DAPC)模式，发挥CPU最大性能，跑DB这种通常需要高运算量的服务就不要考虑节电了；
二、关闭C1E和C States等选项，目的也是为了提升CPU效率；
三、Memory Frequency（内存频率）选择Maximum Performance（最佳性能）；
四、内存设置菜单中，启用Node Interleaving，避免NUMA问题；
6.选择合适的版本这里推荐percona版本的mysql，与原生mysql最相近，且优化过提升性能
7.优化内核参数
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
net.ipv4.tcp_max_tw_buckets = 20000
net.ipv4.tcp_sack = 1
net.ipv4.tcp_window_scaling = 1
net.ipv4.tcp_rmem = 4096 87380 4194304
net.ipv4.tcp_wmem = 4096 16384 4194304
net.core.wmem_default = 8388608
net.core.rmem_default = 8388608
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.core.netdev_max_backlog = 262144
net.core.somaxconn = 262144
net.ipv4.tcp_max_orphans = 3276800
net.ipv4.tcp_max_syn_backlog = 262144
net.ipv4.tcp_synack_retries = 1
net.ipv4.tcp_syn_retries = 1
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_timestamps = 1
net.ipv4.tcp_mem = 94500000 915000000 927000000
net.ipv4.tcp_fin_timeout = 1
net.ipv4.tcp_keepalive_time = 30
net.ipv4.ip_local_port_range = 1024 65000
vm.overcommit_memory = 1
#防火墙参数优化，建议内网运行不开启防火墙，或者云上使用安全组
net.nf_conntrack_max = 524288
net.netfilter.nf_conntrack_max = 524288
net.netfilter.nf_conntrack_tcp_timeout_established = 180
net.netfilter.nf_conntrack_tcp_timeout_close_wait = 60
net.netfilter.nf_conntrack_tcp_timeout_fin_wait = 120
net.netfilter.nf_conntrack_tcp_timeout_time_wait = 120
3.MySQL安装优化和MySQL配置优化
mysql安装最好是选择二进制文件安装，操作系统最好是linux，mysql版本最好是选择mysql8.0，mysql的参数配置参考下面。
## 注意： #
## (1)本配置假设物理服务器内存为 16G，总表数量在300之内，中小型企业业务 #
## (2)请根据实际情况作调整部分参数 #
## (3)本人不对这些建议结果负相应责任
##服务端参数配置
[Mysqld]
port= 3307
basedir = /usr/local/mysql
datadir= /data/datafile/
Socket= /var/lib/mysql/mysqld.sock
log-error = /data/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
#只能用IP地址检查客户端的登录，不用主机名
skip_name_resolve = 1
#若你的MySQL数据库主要运行在境外，请务必根据实际情况调整本参数
default_time_zone = "+8:00"
#数据库默认字符集, 主流字符集支持一些特殊表情符号（特殊表情符占用4个字节）
character-set-server = utf8mb4
#数据库字符集对应一些排序等规则，注意要和character-set-server对应
collation-server = utf8mb4_general_ci
#设置client连接mysql时的字符集,防止乱码
init_connect='SET NAMES utf8mb4'
#是否对sql语句大小写敏感，1表示不敏感
lower_case_table_names = 1
# 执行sql的模式，规定了sql的安全等级, 暂时屏蔽，my.cnf文件中配置报错
#sql_mode = STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#事务隔离级别，默认为可重复读，mysql默认可重复读级别（此级别下可能参数很多间隙锁，影响性能）
transaction_isolation = READ-COMMITTED
#TIMESTAMP如果没有显示声明NOT NULL，允许NULL值
explicit_defaults_for_timestamp = true
#它控制着mysqld进程能使用的最大文件描述(FD)符数量。
#需要注意的是这个变量的值并不一定是你设定的值，mysqld会在系统允许的情况下尽量获取更多的FD数量
open_files_limit = 65535
#最大连接数
max_connections = 10000
#最大错误连接数
max_connect_errors = 3000
#在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中
#官方建议 back_log = 50 + (max_connections / 5),封顶数为65535,默认值= max_connections
back_log = 110
# 所有线程的公开表的数量
# 例如,对于200个并发运行的连接,指定一个至少200 * N的表缓存大小,
# 其中N是您执行的任何查询中每个连接的最大数量.
table_open_cache = 600
# 可以存储在定义缓存中的表定义的数量
# MIN(400 + table_open_cache / 2, 2000)
table_definition_cache = 700
# 为了减少会话之间的争用，可以将opentables缓存划分为table_open_cache/table_open_cache_instances个小缓存
table_open_cache_instances = 64
# 每个线程的堆栈大小 如果线程堆栈太小，则会限制执行复杂SQL语句
thread_stack = 512K
# 禁止外部系统锁
external-locking = FALSE
#SQL数据包发送的大小，如果有BLOB对象建议修改成1G
max_allowed_packet = 128M
#order by 或group by 时用到
#建议先调整为4M，后期观察调整
sort_buffer_size = 4M
#inner left right join时用到
#建议先调整为4M，后期观察调整
join_buffer_size = 4M
# 服务器应缓存多少线程以供重用.
# 如果您的服务器每秒达到数百个连接，则通常应将thread_cache_size设置得足够高，以便大多数新连接使用缓存线程
# default value = 8 + ( max_connections / 100) 上限为100
thread_cache_size = 20
#MySQL连接闲置超过一定时间后(单位：秒)将会被强行关闭
#MySQL默认的wait_timeout 值为8个小时, interactive_timeout参数需要同时配置才能生效
interactive_timeout = 1800
wait_timeout = 1800
#Metadata Lock最大时长（秒）， 一般用于控制 alter操作的最大时长sine mysql5.6
#执行 DML操作时除了增加innodb事务锁外还增加Metadata Lock，其他alter（DDL）session将阻塞
lock_wait_timeout = 3600
#内部内存临时表的最大值。
#比如大数据量的group by ,order by时可能用到临时表，
#超过了这个值将写入磁盘，系统IO压力增大
tmp_table_size = 64M
max_heap_table_size = 64M
#--###############-- 慢SQL日志记录 开始 --#################
#是否启用慢查询日志，1为启用，0为禁用
slow_query_log = 1
#记录系统时区
log_timestamps = SYSTEM
#指定慢查询日志文件的路径和名字
slow_query_log_file = /var/lib/mysql/slow.log
#慢查询执行的秒数，必须达到此值可被记录
long_query_time = 2
#将没有使用索引的语句记录到慢查询日志
log_queries_not_using_indexes = 0
#设定每分钟记录到日志的未使用索引的语句数目，超过这个数目后只记录语句数量和花费的总时间
log_throttle_queries_not_using_indexes = 60
#对于查询扫描行数小于此参数的SQL，将不会记录到慢查询日志中
min_examined_row_limit = 5000
#记录执行缓慢的管理SQL，如alter table,analyze table, check table, create index, drop index, optimize table, repair table等。
log_slow_admin_statements = 0
#作为从库时生效, 从库复制中如何有慢sql也将被记录
#对于ROW格式binlog，不管执行时间有没有超过阈值，都不会写入到从库的慢查询日志
log_slow_slave_statements = 1
#--##################-- 慢SQL日志记录 结束 --##################
#--##############-- Bin-Log设置 开始 --######################
server-id = 1
#开启bin log 功能
log-bin=mysql-bin
#binlog 记录内容的方式，记录被操作的每一行
binlog_fORMat = ROW
#对于binlog_format = ROW模式时，FULL模式可以用于误操作后的flashBack。
#如果设置为MINIMAL，则会减少记录日志的内容，只记录受影响的列，但对于部分update无法flashBack
binlog_row_image = FULL
#bin log日志保存的天数
#如果 binlog_expire_logs_seconds 选项也存在则 expire_logs_days 选项无效
#expire_logs_days 已经被标注为过期参数
#expire_logs_days = 7
binlog_expire_logs_seconds = 1209600
#master 状态和连接信息输出到表mysql.slave_master_info中
master_info_repository = TABLE
#the slave's position in the relay logs输出到表mysql.slave_relay_log_info中
relay_log_info_repository = TABLE
#作为从库时生效, 想进行级联复制，则需要此参数
log_slave_updates
#作为从库时生效, 中继日志relay-log可以自我修复
relay_log_recovery = 1
#作为从库时生效, 主从复制时忽略的错误
#如果在备份过程中执行ddl操作，从机需要从主机的备份恢复时可能会异常，从而导致从机同步数据失败
#如果对数据完整性要求不是很严格，那么这个选项确实可以减轻维护的成本
slave_skip_errors = ddl_exist_errors
#####RedoLog日志 和 binlog日志的写磁盘频率设置 BEGIN #############
# RedoLog日志（用于增删改事务操作） + binlog日志（用于归档，主从复制）
# 为什么会有两份日志呢？
# 因为最开始MySQL没有 InnoDB 引擎,自带MyISAM引擎没有 crash-safe能力，binlog日志只用于归档
# InnoDB 引擎是另一个公司以插件形式引入MySQL的，采用RedoLog日志来实现 crash-safe 能力
# redo log 的写入（即事务操作）拆成两阶段提交（2PC）：prepare阶段 和 commit阶段
#(事务步骤1) 执行commit命令，InnoDB redo log 写盘，然后告知Mysql执行器:[你可以写binlog了，且一并提交事务]，事务进入 prepare 状态
#(事务步骤2) 如果前面 prepare 成功，Mysql执行器生成 binlog 并且将binlog日志写盘
#(事务步骤3) 如果binlog写盘成功，Mysql执行器一并调用InnoDB引擎的提交事务接口，事务进入 commit 状态，操作完成，事务结束
#参数设置成 1，每次事务都直接持久化到磁盘
#参数设置成 0，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
#参数设置成 2，只有在操作系统崩溃或者系统掉电的情况下，上一秒钟所有事务数据才可能丢失。
#即便都设置为1，服务崩溃或者服务器主机crash，Mysql也可能丢失但最多一个事务
#控制 redolog 写磁盘频率 默认为1
innodb_flush_log_at_trx_commit = 1
#控制 binlog 写磁盘频率
sync_binlog = 1
#####RedoLog日志 和 binlog日志的写磁盘频率设置 END ##############
#一般数据库中没什么大的事务，设成1~2M，默认32kb
binlog_cache_size = 4M
#binlog 能够使用的最大cache 内存大小
max_binlog_cache_size = 2G
#单个binlog 文件大小 默认值是1GB
max_binlog_size = 1G
#开启GTID复制模式
gtid_mode = on
#强制gtid一致性，开启后对于create table ... select ...或 CREATE TEMPORARY TABLE 将不被支持
enforce_gtid_consistency = 1
#解决部分无主键表导致的从库复制延迟问题
#其基本思路是对于在一个ROWS EVENT中的所有前镜像收集起来，
#然后在一次扫描全表时，判断HASH中的每一条记录进行更新
#该参数已经被标注为过期参数
#slave-rows-search-alGorithms = 'INDEX_SCAN,HASH_SCAN'
# default value is CRC32
#binlog_checksum = 1
# default value is ON
#relay-log-purge = 1
#--################-- Bin-Log设置 结束 --###############
#--############-- 可能用到的MyISAM性能设置 开始 --##############
#对MyISAM表起作用，但是内部的临时磁盘表是MyISAM表，也要使用该值。
#可以使用检查状态值 created_tmp_disk_tables 得知详情
key_buffer_size = 15M
#对MyISAM表起作用，但是内部的临时磁盘表是MyISAM表，也要使用该值，
#例如大表order by、缓存嵌套查询、大容量插入分区。
read_buffer_size = 8M
#对MyISAM表起作用 读取优化
read_rnd_buffer_size = 4M
#对MyISAM表起作用 插入优化
bulk_insert_buffer_size = 64M
#--############-- 可能用到的MyISAM性能设置 开始 --####################
#--################-- innodb性能设置 开始 --######################
# 定义InnoDB内部允许的最大线程数.
# 值0（默认值）被解释为无限并发（无限制）
innodb_thread_concurrency = 0
#一般设置物理存储的 60% ~ 70%
innodb_buffer_pool_size = 8G
#当缓冲池大小大于1GB时，将innodb_buffer_pool_instances设置为大于1的值，可以提高繁忙服务器的可伸缩性
innodb_buffer_pool_instances = 4
#默认启用。指定在MySQL服务器启动时，InnoDB缓冲池通过加载之前保存的相同页面自动预热。 通常与innodb_buffer_pool_dump_at_shutdown结合使用
innodb_buffer_pool_load_at_startup = 1
#默认启用。指定在MySQL服务器关闭时是否记录在InnoDB缓冲池中缓存的页面，以便在下次重新启动时缩短预热过程
innodb_buffer_pool_dump_at_shutdown = 1
# Defines the name, size, and attributes of InnoDB system tablespace data files
#innodb_data_home_dir = /data/datafile/
#innodb_data_file_path = ibdata1:1G:autoextend(打开后若启动不了，大多数发生在后设置，将1G更改为10M或12M即可)
#InnoDB用于写入磁盘日志文件的缓冲区大小（以字节为单位）。默认值为16MB
innodb_log_buffer_size = 32M
#InnoDB日志文件组数量
innodb_log_files_in_group = 3
#InnoDB日志文件组中每一个文件的大小
innodb_log_file_size = 2G
#是否开启在线回收（收缩）undo log日志文件，支持动态设置，默认开启
innodb_undo_log_truncate = 1
#当超过这个阀值（默认是1G），会触发truncate回收（收缩）动作，truncate后空间缩小到10M
innodb_max_undo_log_size = 4G
#The path where InnoDB creates undo tablespaces
#没有配置则在数据文件目录下 和数据文件在不同的磁盘上，可以减少io热点
#innodb_undo_directory = /undologdata/undolog
#用于设定创建的undo表空间的个数
#已经弃用了，只能手动添加undo表空间
#innodb_undo_tablespaces变量已弃用，并且自那时起不再可配置 MySQL 8.0.14
#innodb_undo_tablespaces = 95
#提高刷新脏页数量和合并插入数量，改善磁盘I/O处理能力
#根据您的服务器IOPS能力适当调整
#一般配普通SSD盘的话，可以调整到 10000 - 20000
#配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
#如果打开参数innodb_flush_sync, checkpoint时，flush操作将由page cleaner线程来完成，此时page cleaner会忽略io capacity的限制，进入激烈刷脏
innodb_flush_sync = 0
innodb_flush_neighbors = 0
#CPU多核处理能力设置，假设CPU是4颗8核的，设置如下
#读多，写少可以设成 2:6的比例
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
#该参数针对unix、linux，window上直接注释该参数.默认值为 NULL
#O_DIRECT减少操作系统级别VFS的缓存和Innodb本身的buffer缓存之间的冲突
#innodb_flush_method = O_DIRECT（本人测试时,打开后启动失败，暂未查明原因）
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
#为了获取被锁定的资源最大等待时间，默认50秒，超过该时间会报如下错误:
# ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
innodb_lock_wait_timeout = 20
#默认OFF，如果事务因为加锁超时，会回滚上一条语句执行的操作。如果设置ON，则整个事务都会回滚
innodb_rollback_on_timeout = 1
#强所有发生的死锁错误信息记录到 error.log中，之前通过命令行只能查看最近一次死锁信息
innodb_print_all_deadlocks = 1
#在创建InnoDB索引时用于指定对数据排序的排序缓冲区的大小
innodb_sort_buffer_size = 67108864
#控制着在向有auto_increment 列的表插入数据时，相关锁的行为，默认为2
#0：traditonal （每次都会产生表锁）
#1：consecutive （mysql的默认模式，会产生一个轻量锁，simple insert会获得批量的锁，保证连续插入）
#2：interleaved （不会锁表，来一个处理一个，并发最高）
innodb_autoinc_lock_mode = 1
#表示每个表都有自已独立的表空间
innodb_file_per_table = 1
#指定Online DDL执行期间产生临时日志文件的最大大小，单位字节，默认大小为128MB。
#日志文件记录的是表在DDL期间的数据插入、更新和删除信息(DML操作)，一旦日志文件超过该参数指定值时，
#DDL执行就会失败并回滚所有未提交的当前DML操作，所以，当执行DDL期间有大量DML操作时可以提高该参数值，
#但同时也会增加DDL执行完成时应用日志时锁定表的时间
innodb_online_alter_log_max_size = 4G
#--##########-- innodb性能设置 结束 --##################
[mysqldump]
quick
max_allowed_packet = 128M
##客户端参数配置
[client]
port= 3307
socket=/var/lib/mysql/mysqld.sock
[mysql]
#prompt="\u@mysqldb \R:\m:\s [\d]> "
#关闭自动补全sql命令功能
no-auto-rehash
## 本配置参考 https://imysql.com/my-cnf-wizard.html #
