提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
mysql调优记录
前言
mysql是个非常好用的免费数据库,初始话安装后,有很多参数需要调优,否则高并发的情况下经常出现故障,本人在此记录mysql5.7.21相关的调优和操作命令以备不时之需。
提示:以下是本篇文章正文内容,下面案例可供参考
一、centeros系统mysql语句
1.centeros系统命令行登录mysql数据库
登录mysql数据库服务器后,登录mysql数据库语句
mysql -u root -p
其中 root是mysql数据库的用户名,不是操作系统的用户名,命令输入后,输入密码即可进入登录mysql数据库
2.centeros系统命令行查看mysql当前正在执行的进程
在centeros系统命令行登录mysql数据库后,执行以下语句,查看当前正在执行的语句列表
show processlist;
这个语句查询的其实就是当前mysql的连接池情况,行数就是连接池数,不能只看id列的数,id列可能数字很大,因为连接池超时后关闭重新建立的,id自增的,只看总行数就行了。
3.mysql停服、启动、重启命令
service mysql stop
service mysql start
service mysql restart
注意:重启数据库之前,要确定my.cnf文件有没有修改过,经常遇到一种情况,那就是某个傻傻的程序员,某个时间修改了my.cnf,但是他当时不懂,以为修改就生效了,也不重启mysql服务,他修改的my.cnf配置有语法错误,这就给你埋了一个巨大的隐患,你过段时间也不知道当时这情况,一重启,哦吼,起不来了(真想捶死这家伙)
二、centeros系统mysql调优相关
先讲个故事(事故):前段时间业务系统因为业务原因,忽然大量的用户使用,让一个mysql5.7.21本来就是裸奔,什么配置用的都是默认的僵尸系统诈尸了!系统一到上班时间用户量上来了就卡死了,一看应用服务器,大量的线程阻塞,再看数据库服务器,cpu100%,内存32G只用了16G~~show processlist一看连接池连接数几千!遇到1000人左右的并发就扛不住了,临时的解决办法就是重启数据库让所有连接断开,可重启后没一会就又炸,哎,自力更生吧,首先用druid德鲁伊自带的监控看了下,确实烂sql太多了,sql执行太慢了,但是一下子要大量优化业务sql不现实,首先因为服务器在政务云上,立马申请翻倍增加核心数从8V升级到16V,但是业务启动后,高峰期还是挂了,依然cpu100%,总不能又申请增加cpu核心数吧。就这么边找问题边宕机了一个星期。。。经过折腾,终于想到看mysql错误日志,还好当时配置了mysql输出错误日志,在my.cnf文件中找到错误文件的位置,打开错误文件,发现了很多的error,逐个百度解决呗。
先贴出我的最终my.cnf配置,我们业务是主从复制,不用纠结一些配置跟你的不一样
[mysqld]
skip-name-resolve
lower_case_table_names=1
bind-address=0.0.0.0
port=3306
user=mysql
basedir=/usr/local/mysql
datadir=/newdisk/data/mysql
socket=/newdisk/tmp/mysql.sock
log-error=/newdisk/data/mysql/mysql.err
pid-file=/newdisk/data/mysql/mysql.pid
character_set_server=utf8mb4
symbolic-links=0
explicit_defaults_for_timestamp=true
sql-mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
max_connections=5000
table_open_cache=10000
wait_timeout=3600
interactive_timeout=36000
log_bin=/newdisk/data/mysql/binlog
server-id=1
binlog_do_db=DB1
binlog_do_db=DB2
binlog_cache_size=4M
max_binlog_cache_size=512m
max_binlog_size =500M
binlog_format="MIXED"
innodb_buffer_pool_instances=8
innodb_buffer_pool_size=8589934592
innodb_sort_buffer_size=104857600
max_heap_table_size=209715200
tmp_table_size=209715200
sort_buffer_size=104857600
performance_schema = off
[client]
socket=/newdisk/tmp/mysql.sock
1.my.cnf配置及修改
文件位置在:/etc/my.cnf
修改my.cnf配置需要重启数据库才能生效,也可以上网学学怎么临时修改这些参数,当然有的参数是只读的,可能没法临时修改,只能修改这个文件后重启数据库才能修改和生效。
注意:这个文件不要写错语法,有一次我多写一个=号,数据库就起不来 还报pid丢了,后来删除多余的=号就启动成功了,吓坏了。
2.mysql错误日志查看
my.cnf里面配置了错误日志位置:
log-error=/newdisk/data/mysql/mysql.err
--根据my.cnf实际配置的路径修改下面的命令
cd /newdisk/data/mysql/
tailf -2000 mysql.err
查看最后的2000条,一次也别看太多,文件大,容易卡!
3.缓冲池设置
innodb_buffer_pool_instances=8
innodb_buffer_pool_size=8589934592
使用show variables like ‘%pool%’;可以看默认多少,单位是b,8589934592也就是8G
innodb_buffer_pool_instances在mysql安装后默认的是1
innodb_buffer_pool_size在mysql安装后默认的是128M,先透露下就这两个参数设置的太小了,坑了我,白费了32G内存的服务器,这块网上资料也挺多的,可以多搜索,设置多大也是有讲究的,一昧的的设置很大也不行,大小设置到系统内存的50%也就基本差不多了,innodb_buffer_pool_instances根据核心数设置1/2差不多了
总结
路漫漫其修远兮,吾将上下而求索,慢慢总结吧,想到一点补充一点。