mysql的一些配置操作

一、背景

记录使用mysql过程中遇到的问题,及一些配置

二、mysql配置

[client]
#服务端口号 默认3306
port = 3306
socket = /tmp/mysql.sock

[mysqld]
# 设置socke文件所在目录
socket = /tmp/mysql.sock
# mysql的安装根路径
basedir = /usr/local/mysql-5.7.25-macos10.14-x86_64/
# 数据目录
datadir = /usr/local/mysql/data/
# 临时目录
tmpdir = /var/tmp
# MySQL打开的文件描述符限制,默认最小1024;当open_files_limit没有被配置的时候,比较max_connections*5和ulimit -n的值,哪个大用哪个,
# 当open_file_limit被配置的时候,比较open_files_limit和max_connections*5的值,哪个大用哪个。
open_files_limit = 65535
# MySQL每打开一个表,都会读入一些数据到table_open_cache缓存中,当MySQL在这个缓存中找不到相应信息时,才会去磁盘上读取。默认值64
# 假定系统有200个并发连接,则需将此参数设置为200*N(N为每个连接所需的文件描述符数目);
# 当把table_open_cache设置为很大时,如果系统处理不了那么多文件描述符,那么就会出现客户端失效,连接不上
table_open_cache = 2000

#事务隔离级别,默认为可重复读,mysql默认可重复读级别
transaction_isolation = REPEATABLE-READ
#SQL数据包发送的大小,如果有BLOB对象建议修改成1G
max_allowed_packet = 128M
#MySQL连接闲置超过一定时间后(单位:秒)将会被强行关闭
#MySQL默认的wait_timeout  值为8个小时, interactive_timeout参数需要同时配置才能生效
interactive_timeout = 28800
wait_timeout = 28800

# 数据库默认字符集,主流字符集支持一些特殊表情符号(特殊表情符占用4个字节)
character-set-server = utf8mb4
# 数据库字符集对应一些排序等规则,注意要和character-set-server对应
collation-server = utf8mb4_general_ci
# 设置client连接mysql时的字符集,防止乱码
init_connect='SET NAMES utf8mb4'
# 是否对sql语句大小写敏感,1表示不敏感,8.0需要在初始化时候设置
lower_case_table_names = 1
# 最大连接数,上限为16384
max_connections = 1000
# 存放执行请求的堆栈大小,默认值是50,设置为最大链接数的20%或30%
back_log=300
#最大错误连接数
max_connect_errors = 1200
# 代表并发线程数,默认是0,即不限制。推荐设置为CPU核心数的2倍。
innodb_thread_concurrency=64
# 设置InnoDB的缓存容量,默认是128M,推荐为主机内存的70%
innodb_buffer_pool_size=1024M

# 开启慢查询日志
slow_query_log = ON
# 慢查询日志位置
slow_query_log_file = /usr/local/mysql/data/huandeMacBook-Pro-slow.log
# 慢查询sql语句执行时间
long_query_time = 3
#检索的行数必须达到此值才可被记为慢查询
min_examined_row_limit = 0

# 设置中继日志的路径
relay_log=/usr/local/var/mysql/relaylog/mysql-relay

# 开启binlog
#开启binlog日志
#binlog日志的基本文件名,需要注意的是启动mysql的用户需要对这个目录(/usr/local/var/mysql/binlog)有写入的权限
log_bin_basename=/usr/local/var/mysql/binlog/mysql-bin
#配置serverid
server-id=1
# 配置binlog日志的格式
binlog_format = ROW
# 设置中继日志的路径
relay_log=/usr/local/var/mysql/relaylog/mysql-relay
# 如果当前服务器是从服务器,但是又需要作为别的服务器的主服务器,那么需要打开此参数
log_slave_updates = OFF
# 如果此服务器做为一个从服务器,那么建议设置为on,让其只读
read_only = on
# 记录日志的大小,也可以理解为详细程度 FULL、minimal、
binlog_row_image = FULL
#每次事务提交时MySQL都会把log buffer的数据写入log file,并且flush(刷到磁盘)中去,该模式为系统默认
innodb_flush_log_at_trx_commit=1
#默认:0,表示MySQL不控制binlog的刷新,由文件系统自己控制它的缓存的刷新,如果这期间服务器crash,则会丢失部分数据,性能最高,
#如果配置为1,则表示每次事务提交都会刷新到磁盘,最安全,性能损耗较高
sync_binlog=1
# 配置binlog保存多久时间,看实际情况填写此值
expire_logs_days=3
#binlog每个日志文件大小
max_binlog_size = 1G
#binlog缓存大小
binlog_cache_size = 4m
#最大binlog缓存大小,建议就使用默认值(18446744073709547520),不要修改
# max_binlog_cache_size = 18446744073709547520

# 发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on,表示开启这个逻辑。
innodb_deadlock_detect = on

# 在 InnoDB 中,innodb_lock_wait_timeout 的默认值是 50s,意味着如果采用第一个策略,当出现死锁以后,第一个被锁住的线程要过 50s 才会超时退出,然后其他线程才有可能继续执行。对于在线服务来说,这个等待时间往往是无法接受的。
innodb_lock_wait_timeout=50s

# 磁盘的io能力,根据实际情况设置,可以使用 fio 这个工具测试得出
innodb_io_capacity=1000

三、慢查询日志

1、命令行临时生效

重启mysql服务器之后,命令行配置不生效。

# 1、检查是否开启慢查询日志
show variables like '%slow_query_log%';
# 检查 slow_query_log 的值是否是 ON,如果是,则说明是打开的。

# 2、启用慢查询日志
set global slow_query_log=on;

# 3、查看慢查询日志的路径
show variables like '%slow_query_log_file%';

# 4、查看慢查询日志的时间,默认是10s。
show variables like '%long_query_time%';

# 5、修改慢查询日志的时间为5s
# 执行完以下命令后,需要在另外的一个终端上查看,即执行 show variables like '%long_query_time%' 这个命令才会看到修改后的值。
set global long_query_time=5;

# 6、查看慢查询的条数
show global status like '%slow_queries%';

2、配置文件修改永久生效

修改 my.cnf

mac 上获取my.cnf文件的位置: mysql --verbose --help | grep my.cnf

/etc/mysql/conf.d/mysql.cnf
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/instance-1-slow.log
# sql语句超过这个时间为慢查询sql,单位为秒
long_query_time = 2

3、慢查询日志解释

慢查询日志示例

# Time: 2021-05-25T13:43:05.359523Z(sql语句执行时间)
# User@Host: root[root] @ localhost []  Id:    32
# Query_time: 12.001026(sql执行总耗时)  Lock_time: 0.000000(等待表锁的时间) Rows_sent: 1  (查询返回的行数)Rows_examined: 0(查询检查的行数,值越大越耗时)
SET timestamp=1621950185;
select sleep(12);(具体执行的sql语句)

4、mysqldumpdlow查看慢查询日志

# 查看慢查询日志
sudo mysqldumpslow -v /usr/local/mysql/data/huandeMacBook-Pro-slow.log
# 按照总耗时排序
sudo mysqldumpslow -v t /usr/local/mysql/data/huandeMacBook-Pro-slow.log
# 按照平均耗时排序
sudo mysqldumpslow -v at /usr/local/mysql/data/huandeMacBook-Pro-slow.log
# 查看平均时间最耗时的5条
sudo mysqldumpslow -v at -t 5 /usr/local/mysql/data/huandeMacBook-Pro-slow.log
# 得到返回记录集最多的10个sql
sudo mysqldumpslow -s r -t 10 /usr/local/mysql/data/huandeMacBook-Pro-slow.log
# 得到访问次数最多的10个sql
sudo mysqldumpslow -s c -t 10 /usr/local/mysql/data/huandeMacBook-Pro-slow.log
# 得到按照时间排序的前10条里面含有左连接的查询语句
sudo mysqldumpslow -s t -t 10 -g "left join"  /usr/local/mysql/data/huandeMacBook-Pro-slow.log
# 一般执行上面的语句都要和 | more 一起使用,否则日志太多
sudo mysqldumpslow -s r -t 10 /usr/local/mysql/data/huandeMacBook-Pro-slow.log | more
# 慢查询日志分析统计
pt-query-digest /usr/local/mysql/data/huandeMacBook-Pro-slow.log

四、查看索引为何不生效

1、explain分析

-- 1、查看执行计划
explain EXTENDED
select * from t_department a LEFT JOIN  t_employees b on a.de_no = b.de_no where a.id = 4019;

-- 2、使用 show warnings
show warnings;
注意: explain 后面跟的关键字 EXTENDED(扩展信息) 在MySQL5.7及之后的版本中废弃了,但是该语法仍被识别为向后兼容,所以在5.7版本及后续版本中,可以不用在 explain 后面添加 EXTENDED 了;

2、optimizer_trace分析

show variables like '%optimizer_trace%';
set optimizer_trace = "enabled=on";
SET optimizer_trace_offset=-30, optimizer_trace_limit=30;
set optimizer_trace_max_mem_size = 163840;

执行查询语句,增加语句等

SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
set optimizer_trace = "enabled=off";

你可能感兴趣的