日志类型
- 事务日志 transaction log
- 错误日志 error log
- 通用日志 general log
- 慢查询日志 slow query log
- 二进制日志 bin log
- 中继日志 relay log
事务日志
由存储引擎管理，事务日志记录了对数据的各种操作，记录了一个个已经完成或未完成的事务，依靠事务日志可以重放或回滚事务操作，应和数据文件分开存储，数据损坏时，根据事务日志可以进行补救
- redo log
- undo log
相关变量
MariaDB [(none)]> show variables like '%innodb_log%';
+-------------------------------+------------+
| Variable_name | Value |
+-------------------------------+------------+
| innodb_log_arch_dir | |
| innodb_log_arch_expire_sec | 0 |
| innodb_log_archive | OFF |
| innodb_log_block_size | 0 |
| innodb_log_buffer_size | 16777216 |
| innodb_log_checksum_algorithm | DEPRECATED |
| 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_optimize_ddl | ON |
| innodb_log_write_ahead_size | 8192 |
# 事务日志有自己的内存buffer区，日志的写盘策略，由参数innodb_flush_log_at_trx_commit决定；
# 0：每秒将buffer区内容刷一次磁盘，刷盘频率不高，性能较好，但崩溃会丢失一秒的事务日志（此处不考虑磁盘崩溃）
# 1，每提交一个事务就写缓存，立刻刷盘一次，大量并发的短事务场景下，刷盘频率较高，不容易丢失事务日志
# 2，每提交一个事务，就写缓存，刷盘是每s刷一次
# 默认是1，容错级别最大
MariaDB [(none)]> select @@global.innodb_flush_log_at_trx_commit;
+-----------------------------------------+
| @@global.innodb_flush_log_at_trx_commit |
+-----------------------------------------+
| 1 |
+-----------------------------------------+
错误日志
- mysqld进程启动、运行、停止时一些信息
- event scheduler 运行event产生的日志信息
- 主从复制中，从服务器启动从服务器线程时产生的信息
相关变量
日志路径：
MariaDB [(none)]> show global variables like "%log_error%";
+---------------+-----------------------------+
| Variable_name | Value |
+---------------+-----------------------------+
| log_error | /data/mysql/host2.b.com.err |
+---------------+-----------------------------+
默认在数据目录下的主机名结尾加.err后缀的文件；
log_warnings
MariaDB [(none)]> show global variables like "%log_warnings%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_warnings | 2 |
+---------------+-------+
是否记录警告级别日志
通用日志
记录了对数据库的通用操作，一般是sql语句，有存储为文件格式和mysql数据库中表的格式；2种方式；
相关变量
通用日志文件路径
MariaDB [(none)]> show global variables like "%general_log%";
+------------------+-----------+
| Variable_name | Value |
+------------------+-----------+
| general_log | OFF |
| general_log_file | host2.log |
+------------------+-----------+
默认关闭，默认文件名主机名短格式.log
MariaDB [(none)]> set @@global.general_log=on;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> show global variables like "%general_log%";
+------------------+-----------+
| Variable_name | Value |
+------------------+-----------+
| general_log | ON |
| general_log_file | host2.log |
+------------------+-----------+
开启通用日志，随即生成通用日志文件；
[root@host2 ~]# ll /data/mysql/host2.log
-rw-rw---- 1 mysql mysql 250 Sep 24 11:26 /data/mysql/host2.log
[root@host2 ~]# tail !$
tail /data/mysql/host2.log
/usr/local/mysql/bin/mysqld, Version: 10.2.25-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
200924 11:26:51 11 Query show global variables like "%general_log%"
日志格式
MariaDB [(none)]> show global variables like "%log_output%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
默认文件，有file，table，none三种选项；
慢查询日志
相关变量
MariaDB [(none)]> show variables like '%query_log%';
+---------------------+----------------+
| Variable_name | Value |
+---------------------+----------------+
| slow_query_log | OFF |
| slow_query_log_file | host2-slow.log |
+---------------------+----------------+
默认没有开启，不支持在线修改，需要修改配置文件，重启；
MariaDB [(none)]> show variables like '%query%';
+------------------------------+----------------+
| Variable_name | Value |
+------------------------------+----------------+
| expensive_subquery_limit | 100 |
| ft_query_expansion_limit | 20 |
| have_query_cache | YES |
| long_query_time | 10.000000 |
| query_alloc_block_size | 16384 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_strip_comments | OFF |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 24576 |
| slow_query_log | ON |
| slow_query_log_file | host2-slow.log |
+------------------------------+----------------+
二进制日志
记录了导致数据改变，或可能导致数据改变的sql语句；不依赖存储引擎，
作用
通过重放，二进制日志文件中的事件，生成数据库数据的一份副本；二进制日志要和数据文件分开存储，以保证数据丢失时，二进制日志可以帮助恢复数据；
中继日志
主从复制结构中，从服务器中从主节点的二进制日志中读取事件，并存储到本地，就成了中继日志，本质也属于二进制文件
日志记录格式
- 语句，基于statements，记录了改变数据的sql，默认模式；
- 行，基于row，记录了改变后的数据，此种模式记录的日志量较大
- 混合模式，行和语句，系统综合使用语句和行模式
MariaDB [(none)]> show variables like '%binlog%';
+-----------------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------+
| binlog_format | MIXED |
| binlog_optimize_thread_scheduling | ON |
| max_binlog_cache_size | 18446744073709547520 |
缓存空间大小定义
| max_binlog_size | 1073741824 |
每个binlog文件的最大值，达到该值会滚动文件
| max_binlog_stmt_cache_size | 18446744073709547520 |
| read_binlog_speed_limit | 0 |
| sync_binlog | 0 |
是否立即刷盘，默认0，表示有os进行定期刷盘
| wsrep_forced_binlog_format | NONE |
+-----------------------------------------+----------------------+
默认是mixed模式
MariaDB [(none)]> show variables like '%sql_log_bin%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
# on表示开启binlog
MariaDB [(none)]> show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| log_bin | ON |
| log_bin_basename | /data/mysql/mysql-bin |
| log_bin_compress | OFF |
| log_bin_compress_min_len | 256 |
| log_bin_index | /data/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| sql_log_bin | ON |
+---------------------------------+-----------------------------+
binlog的路径，索引文件路径，不开启压缩；
日志文件路径
-rw-rw---- 1 mysql mysql 29277 Sep 18 14:42 mysql-bin.000001
...
-rw-rw---- 1 mysql mysql 342 Sep 24 15:35 mysql-bin.000017
-rw-rw---- 1 mysql mysql 323 Sep 24 15:35 mysql-bin.index
[root@host2 ~]# ll /data/mysql/
# index是索引文件，数字结尾的是二进制文件，会不断滚动；
查看binlog
MariaDB [(none)]> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 29277 |
| mysql-bin.000002 | 365 |
| mysql-bin.000003 | 365 |
| mysql-bin.000004 | 365 |
| mysql-bin.000005 | 1685 |
| mysql-bin.000006 | 365 |
| mysql-bin.000007 | 365 |
| mysql-bin.000008 | 2592 |
| mysql-bin.000009 | 745 |
| mysql-bin.000010 | 365 |
| mysql-bin.000011 | 365 |
| mysql-bin.000012 | 669 |
| mysql-bin.000013 | 1038 |
| mysql-bin.000014 | 365 |
| mysql-bin.000015 | 365 |
| mysql-bin.000016 | 365 |
| mysql-bin.000017 | 342 |
+------------------+-----------+
查看现有binlog文件，
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000017 | 342 | | |
+------------------+----------+--------------+------------------+
查看现在binlog的文件及当前位置，主从复制是会用到；
mysqlbinlog
[root@host2 ~]# mysqlbinlog --start-position=245 --stop-position=300 /data/mysql/mysql-bin.000017 -v
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200924 15:35:23 server id 1 end_log_pos 256 CRC32 0x4f6078b5 Start: binlog v 4, server v 10.2.25-MariaDB-log created 200924 15:35:23 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
O0xsXw8BAAAA/AAAAAABAAABAAQAMTAuMi4yNS1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAA7TGxfEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQADQgICAoKCgG1eGBP
'/*!*/;
mysqlbinlog: Out of memory (Needed 1278955752 bytes)
ERROR: Error in Log_event::read_log_event(): 'Out of memory', data_len: 1278955360, event_type: 10
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
常用选项：
--start-position --stop-position，
--start-datetime --stop-datetime
后跟具体的binlog文件；
日志滚动
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000017 | 342 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> flush logs;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000018 | 385 | | |
+------------------+----------+--------------+------------------+
# 主从复制或备份前，一般滚动一下日志文件，使得之后记录的binlog事件，在一个新的文件内；
删除日志：可以删除指定日志，或位置之前的日志，
MariaDB [(none)]> help purge
Name: 'PURGE BINARY LOGS'
Description:
Syntax:
PURGE { BINARY | MASTER } LOGS
{ TO 'log_name' | BEFORE datetime_expr }
...
Examples:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';
# 删除所有binlog，
MariaDB [(none)]> reset master;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 328 | | |
+------------------+----------+--------------+------------------+