一、错误日志
1.介绍
错误日志主要用于记录MySQL生命周期内的错误、状态和警告等信息；
2.配置
修改配置文件，后重启MySQL服务；
|
log_error=目录/文件名 错误日志默认开启，存放于数据路径下，默认文件名称为主机名.err |
|
错误日志必须生成于已有路径下； 错误日志的生成路径的属主必须是Mysql用户(Mysql用户对该路径有操作权限) |
[root@db01 /data/mysql/3306]# vim /data/mysql/3306/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/3306/data
socket=/tmp/mysql.sock
server_id=6
port=3306
log_error=/data/mysql/3306/log/error.log
[mysql]
socket=/tmp/mysql.sock
[root@db01 /]# ll /data/mysql/3306/log/ -d
drwxr-xr-x 2 mysql mysql 23 Aug 26 13:33 /data/mysql/3306/log/
[root@db01 /]# ll /data/mysql/3306/log/
total 4
-rw-r----- 1 mysql mysql 3248 Aug 26 13:33 error.log3.查看日志
|
less 错误日志 grep 'ERROR' 错误日志 |
[root@db01 /]# grep '\[Warning\]' /data/mysql/3306/log/error.log
2019-08-26T05:33:23.503771Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-08-26T05:33:24.520895Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key#模拟错误
[root@db01 /]# vim /data/mysql/3306/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/3306/data
socket=/tmp/mysql.sock
server_id=6
port=3306
log_error=/data/mysql/3306/log/error.log
fghjkllllllll
[mysql]
socket=/tmp/mysql.sock
[root@db01 /]# systemctl restart mysqld
[root@db01 /]# grep '\[ERROR\]' /data/mysql/3306/log/error.log
2019-08-26T05:39:31.085947Z 0 [ERROR] unknown option '--fghjkllllllll'
2019-08-26T05:39:31.085964Z 0 [ERROR] Aborting二、二进制日志
| 二进制日志是配合数据库备份恢复数据的一个手段，二进制日志文件需要备份 |
1.概述
- 功能：数据恢复，主从复制
- 内容：以二进制格式，记录数据库所有变化的操作(DDL,DCL,DML)
2.配置
|
配置文件必要参数 server_id=数字 #指定服务ID(0 < ID < 65535) log_bin=文件前缀 #指定二进制日志文件路径 binlog_format=row #指定二进制日志文件格式为行格式 |
[root@db01 /]# vim /data/mysql/3306/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/3306/data
socket=/tmp/mysql.sock
server_id=6
port=3306
log_error=/data/mysql/3306/log/error.log
log_bin=/data/mysql/3306/log/bin-log
binlog_format=row
[mysql]
socket=/tmp/mysql.sock|
创建二进制日志文件目录 mkdir 目录 #创建二进制日志文件目录 chown -R 属主.属组 目录 #修改目录所属权限 |
[root@db01 /]# ll -d /data/mysql/3306/log/
drwxr-xr-x 2 mysql mysql 23 Aug 26 13:33 /data/mysql/3306/log/|
重启MySQL服务 systemctl restart mysqld #重启MySQL服务 |
[root@db01 /]# ll -d /data/mysql/3306/log/
drwxr-xr-x 2 mysql mysql 23 Aug 26 13:33 /data/mysql/3306/log/
[root@db01 /]# ll /data/mysql/3306/log/
total 20
-rw-r----- 1 mysql mysql 17342 Aug 26 13:40 error.log
[root@db01 /]# systemctl restart mysqld
[root@db01 /]# ll /data/mysql/3306/log/
total 36
-rw-r----- 1 mysql mysql 154 Aug 26 13:47 bin-log.000001
-rw-r----- 1 mysql mysql 36 Aug 26 13:47 bin-log.index
-rw-r----- 1 mysql mysql 24883 Aug 26 13:47 error.log3.二进制日志格式
|
对于DDL和DCL的操作，以statement(语句)方式直接记录SQL 对于DML(insert,update,delete)操作，记录的是已经提交的事务 |
- RBR(row)：记录数据行的变化(推荐使用：逻辑严谨，数据高度一致)
- SBR(statement):记录SQL语句
- MBR(mixed)：混合模式
4.二进制日志的基本查看
| 以事件(Event)作为记录的最小单元(两个at之间记录的是一个事件) |
系统模式
- mysqlbinlog 二进制日志 #查看二进制文件
[root@db01 /]# mysqlbinlog /data/mysql/3306/log/bin-log.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190826 13:47:41 server id 6 end_log_pos 123 CRC32 0x09bd862c Start: binlog v 4, server v 5.7.26-log created 190826 13:47:41 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
fXJjXQ8GAAAAdwAAAHsAAAABAAQANS43LjI2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAB9cmNdEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ASyGvQk=
'/*!*/;
# at 123
#190826 13:47:41 server id 6 end_log_pos 154 CRC32 0x31b5d32b Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;|
参数 --base64-output=decode-rows #解码日志信息 -vvv #示日志详细信息 -d 数据库名 #取指定数据库的日志信息 --start-position=起始序号 #志截取起始位置 --stop-position=结束序号 #截取结束位置 --help #看mysqlbinlog参数帮助 |
[root@db01 /]# mysqlbinlog --base64-output=decode-rows -vvv /data/mysql/3306/log/bin-log.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190826 13:47:41 server id 6 end_log_pos 123 CRC32 0x09bd862c Start: binlog v 4, server v 5.7.26-log created 190826 13:47:41 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#190826 13:47:41 server id 6 end_log_pos 154 CRC32 0x31b5d32b Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;数据库模式
- show binary logs; #查看二进制文件名称
mysql> show binary logs;
+----------------+-----------+
| Log_name | File_size |
+----------------+-----------+
| bin-log.000001 | 154 |
+----------------+-----------+
1 row in set (0.00 sec)- flush logs; #刷新(切割)二进制文件
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
mysql> show binary logs;
+----------------+-----------+
| Log_name | File_size |
+----------------+-----------+
| bin-log.000001 | 199 |
| bin-log.000002 | 154 |
+----------------+-----------+
2 rows in set (0.00 sec)- show master status #查看目前使用的二进制文件
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.000002 | 154 | | | |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)- show binlog events in '日志文件名' #查看二进制日志事件信息
mysql> show binlog events in 'bin-log.000002';
+----------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------+-----+----------------+-----------+-------------+---------------------------------------+
| bin-log.000002 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| bin-log.000002 | 123 | Previous_gtids | 6 | 154 | |
+----------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)5.日志截取与数据恢复
step 0 模拟数据丢失
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| Linux |
| binlog |
| mysql |
| performance_schema |
| sys |
| test |
| world |
+--------------------+
8 rows in set (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_binlog |
+------------------+
| stu |
| test |
| time |
+------------------+
3 rows in set (0.00 sec)
mysql> select * from time;
+------+---------------------+
| id | time |
+------+---------------------+
| 1 | 2019-08-26 14:46:21 |
| 2 | 2019-08-26 14:46:21 |
| 3 | 2019-08-26 14:46:21 |
| 4 | 2019-08-26 14:46:21 |
| 5 | 2019-08-26 14:46:38 |
| 6 | 2019-08-26 14:46:38 |
+------+---------------------+
6 rows in set (0.00 sec)
mysql> drop database binlog;
Query OK, 3 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| Linux |
| mysql |
| performance_schema |
| sys |
| test |
| world |
+--------------------+
7 rows in set (0.00 sec)step 1 查看当前使用的二进制文件
| show master status; |
mysql> show master logs;
+----------------+-----------+
| Log_name | File_size |
+----------------+-----------+
| bin-log.000001 | 199 |
| bin-log.000002 | 1711 |
+----------------+-----------+
2 rows in set (0.00 sec)step 2 查看日志
| show binlog events in '日志文件' |
mysql> show binlog events in 'bin-log.000002';
+----------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| bin-log.000002 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| bin-log.000002 | 123 | Previous_gtids | 6 | 154 | |
| bin-log.000002 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin-log.000002 | 219 | Query | 6 | 319 | create database binlog |
| bin-log.000002 | 319 | Anonymous_Gtid | 6 | 384 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin-log.000002 | 384 | Query | 6 | 596 | use `binlog`; create table stu (id int primary key not null auto_increment,name char(10) not null,age tinyint not null default '18',telnum char(13)) |
| bin-log.000002 | 596 | Anonymous_Gtid | 6 | 661 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin-log.000002 | 661 | Query | 6 | 764 | use `binlog`; create table test(id int) |
| bin-log.000002 | 764 | Anonymous_Gtid | 6 | 829 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin-log.000002 | 829 | Query | 6 | 960 | use `binlog`; create table time(id int,time datetime default now()) |
| bin-log.000002 | 960 | Anonymous_Gtid | 6 | 1025 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin-log.000002 | 1025 | Query | 6 | 1107 | BEGIN |
| bin-log.000002 | 1107 | Table_map | 6 | 1158 | table_id: 108 (binlog.time) |
| bin-log.000002 | 1158 | Write_rows | 6 | 1233 | table_id: 108 flags: STMT_END_F |
| bin-log.000002 | 1233 | Xid | 6 | 1264 | COMMIT /* xid=26 */ |
| bin-log.000002 | 1264 | Anonymous_Gtid | 6 | 1329 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin-log.000002 | 1329 | Query | 6 | 1411 | BEGIN |
| bin-log.000002 | 1411 | Table_map | 6 | 1462 | table_id: 108 (binlog.time) |
| bin-log.000002 | 1462 | Write_rows | 6 | 1517 | table_id: 108 flags: STMT_END_F |
| bin-log.000002 | 1517 | Xid | 6 | 1548 | COMMIT /* xid=27 */ |
| bin-log.000002 | 1548 | Anonymous_Gtid | 6 | 1613 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin-log.000002 | 1613 | Query | 6 | 1711 | drop database binlog |
+----------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
22 rows in set (0.00 sec)step 3 截取日志
| mysqlbinlog --start-position=起始序号 --stop-position=结束序号 '日志文件' >备份文件.sql |
[root@db01 ~]# mysqlbinlog --start-position=219 --stop-position=1613 '/data/mysql/3306/log/bin-log.000002' >/tmp/backup.sql
[root@db01 ~]# ll /tmp/
total 4767
-rw-r--r-- 1 root root 4761 Aug 26 15:05 backup.sql
srwxrwxrwx 1 mysql mysql 0 Aug 26 13:47 mysql.sock
-rw------- 1 mysql mysql 6 Aug 26 13:47 mysql.sock.lockstep 4 数据恢复
| mysql -u用户名 -p密码 < 备份文件.sql |
[root@db01 ~]# mysql -uroot -p123 </tmp/backup.sql
mysql: [Warning] Using a password on the command line interface can be insecure.mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| Linux |
| binlog |
| mysql |
| performance_schema |
| sys |
| test |
| world |
+--------------------+
8 rows in set (0.00 sec)
mysql> use binlog;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from time;
+------+---------------------+
| id | time |
+------+---------------------+
| 1 | 2019-08-26 14:46:21 |
| 2 | 2019-08-26 14:46:21 |
| 3 | 2019-08-26 14:46:21 |
| 4 | 2019-08-26 14:46:21 |
| 5 | 2019-08-26 14:46:38 |
| 6 | 2019-08-26 14:46:38 |
+------+---------------------+
6 rows in set (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_binlog |
+------------------+
| stu |
| test |
| time |
+------------------+
3 rows in set (0.00 sec)6.日志滚动
- flush logs #手动进行日志滚动
[root@db01 ~]# mysql -uroot -p123 -e 'show master status;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.000002 | 3089 | | | |
+----------------+----------+--------------+------------------+-------------------+
[root@db01 ~]# mysql -uroot -p123 -e 'flush logs;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@db01 ~]# mysql -uroot -p123 -e 'show master status;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.000003 | 154 | | | |
+----------------+----------+--------------+------------------+-------------------+- 重启数据库，可以触发数据库二进制日志文件滚动；
[root@db01 ~]# mysql -uroot -p123 -e 'show master status;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.000003 | 154 | | | |
+----------------+----------+--------------+------------------+-------------------+
[root@db01 ~]# systemctl restart mysqld
[root@db01 ~]# mysql -uroot -p123 -e 'show master status;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.000004 | 154 | | | |
+----------------+----------+--------------+------------------+-------------------+- 超过日志设定的最大尺寸(默认1GB)
| select @@max_binlog_size; #查看日志最大尺寸 |
[root@db01 ~]# mysql -uroot -p123 -e 'select @@max_binlog_size/1024/1024/1024;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------------------------+
| @@max_binlog_size/1024/1024/1024 |
+----------------------------------+
| 1.000000000000 |
+----------------------------------+- 某些备份参数
| 每次全备之前，可以进行手动刷新二进制日志文件 |
7.清除日志
- 手动删除
|
reset master #清除所有二进制日志，并重置二进制文件编号为1; purge binary logs to '日志文件' #删除起始日志至该日志之间的日志文件(不包含指定日志) purge binary logs before '时间' #删除指定时间之前的日志文件 |
mysql> help purge;
Name: 'PURGE BINARY LOGS'
......
URL: http://dev.mysql.com/doc/refman/5.7/en/purge-binary-logs.html
Examples:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';- 自动删除
| select @@expire_logs_days; #查询日志的过期时间(0为永久不过期) |
| 一般最少设置为全备周期+1天； |
mysql> select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
| 0 |
+--------------------+
1 row in set (0.00 sec)三、慢日志(Slow Log)
1.概述
慢日志：记录MySQL运行期间执行较慢的语句
2. 配置(编辑配置文件)
慢日志默认没有开启
[root@db01 /data/mysql/3306]# vim my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/3306/data
socket=/tmp/mysql.sock
server_id=6
port=3306
log_error=/data/mysql/3306/log/error.log
log_bin=/data/mysql/3306/log/bin-log
binlog_format=row
slow_query_log=ON
slow_query_log_file=/data/mysql/3306/log/slow.log
long_query_time=0.1
log_queries_not_using_indexes=ON
[mysql]
socket=/tmp/mysql.sock|
slow_query_log=ON #开启MySQL慢日志 slow_query_log_file=文件位置 #指定日志存放位置(默认存放在MySQL数据路径下) long_query_time=时间(单位：秒；默认时间为10s) log_queries_not_using_indexes=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 | OFF |
| slow_query_log_file | /data/mysql/data/db03-slow.log |
+---------------------------+--------------------------------+
5 rows in set (0.16 sec)
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)
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)3. 分析日志文件
mysqldumpslow 慢日志分析工具
[root@db01 /data/mysql/3306/log]# mysqldumpslow slow.log
Reading mysql slow query log from slow.log
Count: 1 Time=1.32s (1s) Lock=0.00s (0s) Rows=1000000.0 (1000000), root[root]@localhost
select * from t100w
Count: 2 Time=0.34s (0s) Lock=0.00s (0s) Rows=515.0 (1030), root[root]@localhost
select * from t100w where k1='S'
Count: 4 Time=0.30s (1s) Lock=0.00s (0s) Rows=525.0 (2100), root[root]@localhost
select id,num from t100w where k1='S' order by num limit N
Count: 8 Time=0.29s (2s) Lock=0.00s (0s) Rows=91.0 (728), root[root]@localhost
select k1,k2 from t100w where k1='S' and k2 like 'S'
Count: 1 Time=0.28s (0s) Lock=0.00s (0s) Rows=1030.0 (1030), root[root]@localhost
select k1,k2 from t100w where k1='S-s 排序规则 排序(自动合并重复执行语句)
| c #按SQL语句执行次数排序 |
[root@db01 /data/mysql/3306/log]# mysqldumpslow -s c slow.log
Reading mysql slow query log from slow.log
Count: 8 Time=0.29s (2s) Lock=0.00s (0s) Rows=91.0 (728), root[root]@localhost
select k1,k2 from t100w where k1='S' and k2 like 'S'
Count: 4 Time=0.30s (1s) Lock=0.00s (0s) Rows=525.0 (2100), root[root]@localhost
select id,num from t100w where k1='S' order by num limit N
Count: 2 Time=0.34s (0s) Lock=0.00s (0s) Rows=515.0 (1030), root[root]@localhost
select * from t100w where k1='S'
Count: 1 Time=0.28s (0s) Lock=0.00s (0s) Rows=1030.0 (1030), root[root]@localhost
select k1,k2 from t100w where k1='S'
Count: 1 Time=1.32s (1s) Lock=0.00s (0s) Rows=1000000.0 (1000000), root[root]@localhost
select * from t100w-t 数字 显示前指定数目的日志记录
[root@db01 /data/mysql/3306/log]# mysqldumpslow -s c -t 3 slow.log
Reading mysql slow query log from slow.log
Count: 8 Time=0.29s (2s) Lock=0.00s (0s) Rows=91.0 (728), root[root]@localhost
select k1,k2 from t100w where k1='S' and k2 like 'S'
Count: 4 Time=0.30s (1s) Lock=0.00s (0s) Rows=525.0 (2100), root[root]@localhost
select id,num from t100w where k1='S' order by num limit N
Count: 2 Time=0.34s (0s) Lock=0.00s (0s) Rows=515.0 (1030), root[root]@localhost
select * from t100w where k1='S'附：思维导图
