(一)错误日志配置
vim /etc/my.cnf
log-error=/var/log/mysql.log
查找错误日志位置
show variables like '%log_error%'
一般查询日志一般都不开启,这样会导致日志文件异常的大,审计的时候会开启
(二)二进制日志配置
mkdir /data/binlog -p
chown -R mysql.mysql /data/binlog
set sql_log_bin=0 //做数据备份或恢复时,临时关闭二进制日志
vim /etc/my.cnf //控制开启二进制日志
[mysqld]
log-bin=/data/binlog/my-bin //my-bin是二进制日志文件名的前缀,这也是永久开启二进制日志的方式
binlog_format=row //statement格式直接记录操作的语句,量级小,但函数类语句不准确;row行模式更加准确,量级大,建议用row模式
sync_binlog=1 //1表示每次提交commit时,就会将数据写入磁盘。一般设置成1
#粗略查看二进制日志
show binary logs;
show binlog events in 'my-bin.000002' //查看某个二进制文件具体的内容,事件信息
show master status;
#截取二进制日志
mysqlbinlog --base64-output=decode-rows -v my-bin.000002 //** 命令行处,通过该命令可以看到二进制日志里面的具体语句。
mysqlbinlog --start-position=120 --stop-position=721 my-bin.000002 //可以通过截取部分二进制日志,对误删除等操作进行数据库的恢复
mysqbinlog --start-position=340 --stop-position=721 my-bin.000002 >/tmp/binlog.sql
#刷新日志
flush logs;
#设置二进制日志保留时间
vim /etc/my.cnf
expire_logs_days=90
#删除二进制:
默认情况下,不会删除旧的日志文件,二进制日志太重要了!
根据存在时间删除日志:
SET GLOBAL expire_logs_days = 90;
…或者…
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day; //手工删除二进制日志
根据文件名删除日志:
PURGE BINARY LOGS TO 'mysql-bin.000010'; //手工删除二进制日志
reset master; //清空所有二进制日志,然后从新开始记录二进制日志
(三)MySQL的慢查询日志
功能:
slow-log,记录所有条件内的慢的SQL语句
优化的一种工具日志。帮我们定位问题。
是将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件
通过对这些特殊的SQL语句分析,改进以达到提高数据库性能的目的。
慢日志设置:
long_query_time : 设定慢查询的阀值,超出次设定值的SQL即被记录到慢查询日志,缺省值为10s
slow_query_log : 指定是否开启慢查询日志
slow_query_log_file : 指定慢日志文件存放位置,可以为空,系统会给一个缺省的文件host_name-slow.log
min_examined_row_limit:查询检查返回少于该参数指定行的SQL不被记录到慢查询日志
log_queries_not_using_indexes: 不使用索引的慢查询日志是否记录到索引
配置例子: //一般只开启下列4项就可以了
slow_query_log=1 //开启慢日志
slow_query_log_file=/data/slow/slow.log //data/slow目录提前创建好,权限等 mkdir -p /data/slow, chown -R mysql:mysql /data/slow,否则没法生成文件
long_query_time=0.5 //0.5秒
log_queries_not_using_indexes //记录慢查询里面,不使用索引的语句记录下来
处理慢日志:
mysqldumpslow命令
mysqldumpslow -s c -t 10 /data/slow/slow.log //按次数排序
mysqldumpslow -s at -t 10 /data/slow/slow.log //按时间排序,倒序
这会输出记录次数最多的10条SQL语句,
其中:-s是表示按照何种方式排序c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序。
ac、at、al、ar,表示相应的倒叙。
-t是top n的意思,即为返回前面多少条的数据;
慢日志演示实例:
插入数据
mysql> create table city_bak select from city; //创建新表,并且把city表输入导入
mysql> insert into city_bak select from city_bak;
mysql> select sleep(3);
mysql慢日志常用工具:(已经帮你排序好了,只需要逐条优化即可)
pt-query-diagest (是percona-toolkit工具箱里面的工具)
mysqlsla