个人学习使用
cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core)
----------------------------------------------------------------------
getenforce
Enforcing
----------------------------------------------------------------------
systemctl status firewalld.service
Active: active (running)
----------------------------------------------------------------------
systemctl status NetworkManager
Active: active (running)
----------------------------------------------------------------------
MySQL
Server version: 5.7.26
一）日志管理
1.1）错误日志
存放在datadir/hostname.err
select @@log_error;
+-------------+
| @@log_error |
+-------------+
| ./db01.err |
+-------------+
- 修改错误日志存在位置
vim /etc /my.cnf
---------------------
#添加此行、重启生效
log_error=/data/mysql/data/mysql.log
1.2）二进制日志
以event形式记录MySQL中发生过变更的语句
可以提供数据恢复、主从复制
- 配置参数
#创建二进制日志目录
mkdir -p /data/mysql/binlog
chown -R mysql.mysql /data/mysql/*
----------------------------------------------------------------------
#修改配置文件
vim /etc/my.cnf
---------------------
log_bin=/data/mysql/binlog/mysql-bin
binlog_format=row
sync_binlog=1
expire_logs_days=30
server_id=6
- 参数说明
(1)指定文件名与位置
log_bin=
(2)日志格式
binlog_format=row/statement/mixed
DDL、DCL：记录语句本身（statement模式）
insert、update、delete：受binlog_format影响
row(RBR)：记录数据行的变化
statement(SBR)：记录语句本身
mixed(MBR)：混合
(3)日志过期时间
expire_logs_days=
设置依据：2*全备周期+1
(4)binlog刷盘策略
sync_binlog=
每次提交事务，binlog立即写入磁盘文件
- 使用binlog
(1)查看日志文件
show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 154 |
+------------------+-----------+
(2)滚动日志文件
flush logs;
查看文件自动滚动值
select @@max_binlog_size;
+-------------------+
| @@max_binlog_size |
+-------------------+
| 1073741824 |
+-------------------+
重启数据库会自动滚动
备份时加了自动滚动参数
(3)正在使用的日志文件
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
(4)查看binlog文件内容
show binlog events in 'mysql-bin.000004';
------------------------------
mysqlbinlog --base64-output=decode-rows -v mysql-bin.000004 |grep -v 'SET'
(5)截取日志
mysqlbinlog --start-position=xxxx --stop-position=xxxx mysql-bin.000004>/data/bin.sql
(6)手动清理日志
PURGE BINARY LOGS TO 'mysql-bin.000010';
---------------------
reset master;
切记不要rm日志文件
1.3）慢日志
记录MySQL工作过程中，运行较慢的语句
- 配置文件
vim /etc/my.cnf
---------------------
slow_query_log=1
slow_query_log_file=/data/mysql/log/slow-mysql.log
log_queries_not_using_indexes
long_query_time=0.1
- 配置参数
(1)开关
1：开启、0：关闭
select @@slow_query_log;
+------------------+
| @@slow_query_log |
+------------------+
| 0 |
+------------------+
(2)日志文件位置
select @@slow_query_log_file;
+--------------------------------+
| @@slow_query_log_file |
+--------------------------------+
| /data/mysql/data/db01-slow.log |
+--------------------------------+
(3)时间阈值
select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
| 10.000000 |
+-------------------+
(4)未走索引的语句
1：开启、0：关闭
select @@log_queries_not_using_indexes;
+---------------------------------+
| @@log_queries_not_using_indexes |
+---------------------------------+
| 0 |
+---------------------------------+
- 慢日志工具
mysqldumpslow -s c -t 5 /data/mysql/log/slow.log
