MYSQL日志管理
2019-08-29 本文已影响0人
你好_请关照
Mysql日志管理：
介绍：错误日志、二进制日志、慢日志
1、错误日志
配置方式：在配置文件中（my.cnf）中添加log_error=path 即可
错误日志默认就是开启的，默认存放在数据目录下，日志名称 主机名.err 的文件
**注意事项：此日志的存放位置需要存放在启动进程用户有权限管理的目录下，并且日志文件
本身属主、属组是服务进程管理用户也就是配置文件中user等于后面的用户
查看错误日志时只需要查看文件中[ERROR]的行即可
[root@db01 /data/mysql/data]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
log_error=/data/mysql/data/error.log
[mysql]
socket=/tmp/mysql.sock
[root@db01 /data/mysql/data]# ll error.log
-rw-r-----. 1 mysql mysql 10866 Aug 28 21:52 error.log
[root@db01 /data/mysql/data]#
2、二进制日志（binlog）
2.1 二进制日志作用：数据恢复、主从复制
2.2 二进制日志记录了什么：记录数据库所有变化的操作，包括DDL、DCL、DML，其实就是SQL语句
2.3 二进制日志的配置：
server_id=6 #主从复制需要用到
log_bin=/data/binlog/mysql-binlog/mysql-bin #指定存放位置mysql-bin名称前缀
binlog_format=row #日志格式
[root@db01 /data/binlog]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
log_error=/data/mysql/data/error.log
binlog_format=row
log_bin=/data/binlog/mysql-bin
[mysql]
socket=/tmp/mysql.sock
[root@db01 /data/binlog]#
2.4 二进制日志格式
DDL和DCL，以statement（语句）方式直接记录SQL
DML（insert、update、delete），记录的是已经提交的事物
SBR ：statement，记录具体语句（5.6以下版本常用）
RBR ：Row ,记录数据行的变化（常用）
MBR ：mixed,混合模式（一般不用）
2.5 日志内容
以事件(event) 作为记录的最小单元
以下（截取）为一个事件，以一个at开始，到下一个at结束
[root@db01 /data/binlog]# mysqlbinlog mysql-bin.000001
# at 534
#190829 15:02:50 server id 6 end_log_pos 625 CRC32 0xaf1341d1 Query thread_id=3 exec_time=0 error_code=0
create database bbb
2.6 mysql客户端下二进制日志的基本查看
查看二进制日志有多少个
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 625 |
+------------------+-----------+
1 row in set (0.00 sec)
flush logs 刷新二进制日志
mysql> flush logs; #刷新二进制日志
Query OK, 0 rows affected (0.15 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 672 |
| mysql-bin.000002 | 154 |
+------------------+-----------+
2 rows in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
mysql>
查看正在使用哪个二进制日志
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
查看某二进制日志的事件信息
说明：
对于DDL、DCL操作，一个操作（info列）就是一个事件（ Pos 892 ---End_log_pos 999）。
对于DML语句 在info列，以BEGIN 开始一个事物，以COMMIT结束一个事物（ Pos 390---End_log_pos 712） 。
mysql> show binlog events in 'mysql-bin.000005';
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------+
| mysql-bin.000005 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000005 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000005 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000005 | 219 | Query | 6 | 325 | use `aaa`; create table aa(
id int not null ) |
| mysql-bin.000005 | 325 | Anonymous_Gtid | 6 | 390 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000005 | 390 | Query | 6 | 461 | BEGIN |
| mysql-bin.000005 | 461 | Table_map | 6 | 505 | table_id: 108 (aaa.aa) |
| mysql-bin.000005 | 505 | Write_rows | 6 | 545 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000005 | 545 | Xid | 6 | 576 | COMMIT /* xid=45 */ |
| mysql-bin.000005 | 576 | Anonymous_Gtid | 6 | 641 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000005 | 641 | Query | 6 | 712 | BEGIN |
| mysql-bin.000005 | 712 | Table_map | 6 | 756 | table_id: 108 (aaa.aa) |
| mysql-bin.000005 | 756 | Write_rows | 6 | 796 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000005 | 796 | Xid | 6 | 827 | COMMIT /* xid=48 */ |
| mysql-bin.000005 | 827 | Anonymous_Gtid | 6 | 892 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000005 | 892 | Query | 6 | 999 | create database ccc charset utf8mb4 |
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------+
16 rows in set (0.00 sec)
mysql>
2.7 二进制日志的内容查看和截取
(1)查看
查看 mysqlbinlog mysql-bin.000005
mysql日志详细显示mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000005
(2)截取二进制日志恢复数据
例子:
mysql> create database bak charset utf8mb4;
mysql> use bak
mysql> create table t1 (id int) charset utf8mb4;
mysql> insert into t1 values(1),(2),(3);
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
mysql> drop database bak;
第二步 恢复过程:
mysql> show master status ;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 1764 | | | |
+------------------+----------+--------------+------------------+-------------------+
mysql> show binlog events in 'mysql-bin.000004'
| mysql-bin.000004 | 1065 | Query | 6 | 1172 | create database bak charset utf8mb4
| mysql-bin.000004 | 1675 | Query | 6 | 1764 | drop database bak
mysqlbinlog --start-position=1065 --stop-position=1675 mysql-bin.000004 >/tmp/bin.sql
[root@db01 /data/binlog]# mysql -uroot -p123456</tmp/bin.sql
mysqlbinlog --start-position=1065 --stop-position=1675 mysql-bin.000004 >/tmp/bin.sql
-b database :mysqlbinlog 中-b参数后面加库名，指明过滤某个库的二进制日志
2.8 日志滚动
第一种 ：/etc/init.d/mysqld restart
第二种：mysql> flush logs;
mysql> show binary logs;
mysql> select @@max_binlog_size/1024/1024;
2.9删除二进制日志
mysql>help purge;
mysql> PURGE BINARY LOGS TO 'mysql-bin.000004'; 删除000004号之前的所有文件
mysql>PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26'; #按日期删除
可以在配置文件中定制二进制文件的过期时间（一般最少设定为数据库全备期间）
mysql> select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
| 0 |
+--------------------+
1 row in set (0.00 sec)
mysql>
3、slow log（慢日志）
3.1 作用：记录MYSQL运行期间执行较慢的语句
3.2 查看慢日志是否打开
slow_query_log 状态为ON 表示打开
mysql> show variables like '%slow%';
+---------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------+--------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /data/mysql/data/db01-slow.log |
+---------------------------+--------------------------------+
5 rows in set (0.01 sec)
mysql>
3.3 查看慢日志记录时间
当查询语句大于等于long_query_time这个值得时间(单位S)就会被定义为查询慢的语句，就会被慢日志记录起来
mysql> show variables like '%long%';
+----------------------------------------------------------+-----------+
| Variable_name | Value |
+----------------------------------------------------------+-----------+
| long_query_time | 10.000000 |
| performance_schema_events_stages_history_long_size | 10000 |
| performance_schema_events_statements_history_long_size | 10000 |
| performance_schema_events_transactions_history_long_size | 10000 |
| performance_schema_events_waits_history_long_size | 10000 |
+----------------------------------------------------------+-----------+
5 rows in set (0.00 sec)
3.4查看是否开启未使用索引记录功能
当log_queries_not_using_indexes 这个功能被开启，会把未使用索引查询的语句记录到慢日志中
mysql> show variables like '%indexes%';
+----------------------------------------+-------+
| Variable_name | Value |
+----------------------------------------+-------+
| log_queries_not_using_indexes | OFF |
| log_throttle_queries_not_using_indexes | 0 |
+----------------------------------------+-------+
2 rows in set (0.00 sec)
mysql>
3.5 配置
slow_query_log=ON #打开慢日志
slow_query_log_file=/data/mysql/data/db01-slow.log #默认路径可手动指定
long_query_time=5 #超过多长时间被定义为慢语句
log_queries_not_using_indexes=ON #开启此功能，会将未走索引的语句记录到慢日志中
3.5慢日志分析工具
[root@db01 /data/mysql/data]# mysqldumpslow -s c -t 3 db01-slow.log
