1. 错误日志
定位MySQL工作过程中的故障
log_error=/data/binlog/mysql-bin
[ERROR] 上下文
2. 二进制日志
作用:数据恢复,主从复制
配置:
server_id
log_bin
sync_binlog=1/0
binlog_format :SBR RBR MBR
expire_logs_days
"双一标准"说明:
innodb_flush_log_at_trx_commit=1 每次事务提交,必然log buffer中redo落到磁盘
sync_binlog=1 每次事务提交,必然保证binlog cache中的日志落到磁盘
设计的重点命令:
show master status;
show binlog events in 'mysql-bin.000003' limit 100;
mysql -uroot -pmysql -e "show binlog events in 'mysql-bin.000002'" | grep DROP
无gtid
mysqlbinlog --start-position=219 --stop-position=1356 /data/binlog/mysql-bin.000003 >/tmp/bin.sql;
mysqlbinlog -d world --start-position=219 --stop-position=1356 /data/binlog/mysql-bin.000003 >/tmp/bin.sql;
mysqlbinlog --start-datetime= --stop-datetime=1356 /data/binlog/mysql-bin.000001 /data/binlog/mysql-bin.000002 >/tmp/bin.sql;
有gtid
mysqlbinlog --skip-gtids --include-gtids='xxxxxxxxxxx:1-14' mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 >/tmp/gtdb.sql
mysqlbinlog --skip-gtids --include-gtids='xxxxxxxxxxx:1-5','xxxxxxxxxxx:7-14' mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 >/tmp/gtdb.sql
mysqlbinlog --skip-gtids --include-gtids='xxxxxxxxxxx:1-14' --exclude-gtids='xxxxxxxxxxx:6' mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 >/tmp/gtdb.sql
恢复日志:
mysql> set sql_log_bin=0;
mysql> source /tmp/gtdb1.sql;
mysql> set sql_log_bin=1;
滚动日志;
flush logs;
......
删除日志:
purge binary logs to ...
reset master;
3. 慢日志
配置:
slow_query_log
slow_query_log_file
long_query_time
log_queries_not_using_indexes
分析:
mysqldumpslow -s c -t xxxxxxxxxxx
额外扩展:
pt-query-digest