关于数据库:mysql的一些配置操作

30次阅读

共计 5018 个字符,预计需要花费 13 分钟才能阅读完成。

一、背景

记录应用 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";

正文完
 0