mysql的一些配置操作数据库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、慢查询日志解释 【mysql的一些配置操作】慢查询日志示例

# 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 JOINt_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";