事务日志
redo log
mysql的innodb引擎在开启事务后，中间的操作都会先在内存中进行。然后将这些数据先写入到redo log中--“日志先行”（Write-Ahead Logging），因为写入到rero log是磁盘某块区域的顺序写入，所以效率更高。事务提交后也不一定就立刻将redo日志里的数据写入磁盘，一般是慢慢刷新分批写入到磁盘，减轻数据库压力。如果数据库崩溃或者机器宕机，系统重启或恢复的时候，可以根据redo log中的日志信息，将已完成的事务操作写入到数据库中，而对未完成的事务则进行回滚。
undo log
uodo log只有负责数据的回滚，一般保存的是事务执行操作的前的状态。
相关变量
mysql> show variables like '%innodb_log%';
+------------------------------------+----------+
| Variable_name | Value |
+------------------------------------+----------+
| innodb_log_buffer_size | 16777216 |
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 | #单个日志文件的大小
| innodb_log_files_in_group | 2 | #日志个数
| innodb_log_group_home_dir | ./ | #事务日志路径
| innodb_log_spin_cpu_abs_lwm | 80 |
| innodb_log_spin_cpu_pct_hwm | 50 |
| innodb_log_wait_for_flush_spin_hwm | 400 |
| innodb_log_write_ahead_size | 8192 |
+------------------------------------+----------+
10 rows in set (0.00 sec)
mysql> show variables like '%innodb_flush_log%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 | #事务提交策略，建议修改为2
+--------------------------------+-------+
2 rows in set (0.00 sec)
错误日志
记录mysqld启动，运行和结束中输出的错误或警告信息
相关变量
mysql> show variables like 'log_error%';
+----------------------------+----------------------------------------+
| Variable_name | Value |
+----------------------------+----------------------------------------+
| log_error | /var/log/mysqld.log | #错误日志存放路径
| log_error_services | log_filter_internal; log_sink_internal |
| log_error_suppression_list | |
| log_error_verbosity | 2 | #mysql5.7.2后出现的系统变量，代替原来的log_warnings,错误日志的详细等级，数值越高越详细，注意：mysql 8.0,3之后版本已经移除了log_warnings变量。
+----------------------------+----------------------------------------+
4 rows in set (0.00 sec)
通用日志
记录客户端对数据库的各种操作，包括sql语句执行记录等。
相关变量
mysql> show variables like 'general_log%';
+------------------+----------------------------+
| Variable_name | Value |
+------------------+----------------------------+
| general_log | OFF | #默认未启用状态
| general_log_file | /var/lib/mysql/centos7.log | #日志存放路径
+------------------+----------------------------+
2 rows in set (0.00 sec)
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE | #日志存放类型，有none，file，table三种类型。
+---------------+-------+
1 row in set (0.00 sec)
慢查询日志
记录执行时间超过long_query_time变量设置时间的查询，也可以记录一些没有使用索引的sql。
相关变量
mysql> show variables like 'slow_query%';
+---------------------+---------------------------------+
| Variable_name | Value |
+---------------------+---------------------------------+
| slow_query_log | OFF | #默认未开启
| slow_query_log_file | /var/lib/mysql/centos7-slow.log | #日志存放路径
+---------------------+---------------------------------+
2 rows in set (0.01 sec)
mysql> show variables like 'long_query%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 | #超过这个时长的查询将会被记录
+-----------------+-----------+
1 row in set (0.00 sec)
mysql> show variables like 'log_queries%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF | #开启此项后，没有使用索引的sql也将被记录
+-------------------------------+-------+
1 row in set (0.00 sec)
慢查询相关分析工具
- mysqldumpslow
- profile
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set, 1 warning (0.01 sec)
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show tables;
ERROR 1046 (3D000): No database selected
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
mysql> create tables t1(id int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tables t1(id int)' at line 1
mysql> show profiles;
+----------+------------+--------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------+
| 1 | 0.00010625 | show tables |
| 2 | 0.01226175 | show databases |
| 3 | 0.00008175 | create tables t1(id int) |
+----------+------------+--------------------------+
3 rows in set, 1 warning (0.00 sec)
mysql> show profile;
+---------------+----------+
| Status | Duration |
+---------------+----------+
| starting | 0.000062 |
| freeing items | 0.000014 |
| cleaning up | 0.000006 |
+---------------+----------+
3 rows in set, 1 warning (0.01 sec)
mysql> show profile for query 2;
+----------------------------+----------+
| Status | Duration |
+----------------------------+----------+
| starting | 0.008047 |
| checking permissions | 0.000024 |
| Opening tables | 0.001117 |
| init | 0.000585 |
| System lock | 0.000035 |
| optimizing | 0.000205 |
| statistics | 0.000504 |
| preparing | 0.000191 |
| Creating tmp table | 0.000337 |
| executing | 0.000841 |
| end | 0.000027 |
| query end | 0.000006 |
| waiting for handler commit | 0.000021 |
| removing tmp table | 0.000006 |
| waiting for handler commit | 0.000006 |
| closing tables | 0.000016 |
| freeing items | 0.000277 |
| cleaning up | 0.000020 |
+----------------------------+----------+
18 rows in set, 1 warning (0.00 sec)
mysql> show profile cpu for query 2;
+----------------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------------+----------+----------+------------+
| starting | 0.008047 | 0.008023 | 0.000000 |
| checking permissions | 0.000024 | 0.000015 | 0.000000 |
| Opening tables | 0.001117 | 0.001128 | 0.000000 |
| init | 0.000585 | 0.000582 | 0.000000 |
| System lock | 0.000035 | 0.000027 | 0.000000 |
| optimizing | 0.000205 | 0.000208 | 0.000000 |
| statistics | 0.000504 | 0.000503 | 0.000000 |
| preparing | 0.000191 | 0.000191 | 0.000000 |
| Creating tmp table | 0.000337 | 0.000338 | 0.000000 |
| executing | 0.000841 | 0.000847 | 0.000000 |
| end | 0.000027 | 0.000016 | 0.000000 |
| query end | 0.000006 | 0.000006 | 0.000000 |
| waiting for handler commit | 0.000021 | 0.000020 | 0.000000 |
| removing tmp table | 0.000006 | 0.000007 | 0.000000 |
| waiting for handler commit | 0.000006 | 0.000006 | 0.000000 |
| closing tables | 0.000016 | 0.000016 | 0.000000 |
| freeing items | 0.000277 | 0.000279 | 0.000000 |
| cleaning up | 0.000020 | 0.000018 | 0.000000 |
+----------------------------+----------+----------+------------+
18 rows in set, 1 warning (0.00 sec)
二进制日志
记录会引起数据改变的SQL。多用于数据还原和主从复制
相关变量
sql_log_bin #是否开启二进制日志
log_bin #指定文件位置
binlog_format #默认格式statement，建议根据需求改为row和mixed
max_binlog_size #单个二进制日志最大体积，默认1G
expire_logs_days #二进制日志自动删除的天数，默认为0
二进制日志相关命令
# 查看二进制日志文件列表和大小
mysql> show master logs;
+----------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+----------------+-----------+-----------+
| bin-log.000001 | 32388 | No |
| bin-log.000002 | 156 | No |
+----------------+-----------+-----------+
2 rows in set (0.00 sec)
mysql> show binary logs;
+----------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+----------------+-----------+-----------+
| bin-log.000001 | 32388 | No |
| bin-log.000002 | 156 | No |
+----------------+-----------+-----------+
2 rows in set (0.00 sec)
# 查看正在使用的二进制日志文件
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.000002 | 156 | | | |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 查看二进制文件内容
mysql> show binlog events;
+----------------+-------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info
|
+----------------+-------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| bin-log.000001 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| bin-log.000001 | 125 | Previous_gtids | 1 | 156 | |
| bin-log.000001 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin-log.000001 | 235 | Query | 1 | 418 | CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARACTER SET utf8 */ /* xid=16 */ |
| bin-log.000001 | 418 | Anonymous_Gtid | 1 | 495 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin-log.000001 | 495 | Query | 1 | 634 | use `hellodb`; DROP TABLE IF EXISTS `classes` /* generated by server */ |
| bin-log.000001 | 634 | Anonymous_Gtid | 1 | 713 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
# 删除二进制文件
PURGE BINARY LOGS TO 'mysql-bin.00001';
PURGE BINARY LOGS BEFORE '2020-10-10 10:10:10';
RESET MASTER TO 1234;
SHOW BINARY LOGS;
+-------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+-------------------+-----------+-----------+
| master-bin.001234 | 154 | No |
+-------------------+-----------+-----------+
#切换日志文件
flush logs；
二进制日志客户端查看工具
- mysqlbinlog
[root@centos7 mysql]#mysqlbinlog bin-log.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#201013 22:29:02 server id 1 end_log_pos 125 CRC32 0x8f94c3aa Start: binlog v 4, server v 8.0.21 created 201013 22:29:02 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
rrmFXw8BAAAAeQAAAH0AAAABAAQAOC4wLjIxAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACuuYVfEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQA
CigBqsOUjw==
'/*!*/;
# at 125
#201013 22:29:02 server id 1 end_log_pos 156 CRC32 0x5873f06a 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*/;