日志类型
- 错误日志（error log）
- 一般查询日志（general query log）
- 二进制日志（binary query log）
- 中继日志（relay query log）
- 慢查询日志（slow query log）。
- 事务日志（DDL log）
新建日志存放目录
[root@server ~]# mkdir /data
[root@server ~]# chown -R mysql.mysql /data
错误日志
- 作用：
记录mysql数据库的一般状态信息及报错信息，是我们对于数据库常规报错处理的常用日志。
- 参数
| 变量 | 值 | 说明 |
|---|---|---|
| log-error | /path/hostname_err.log | 错误日志文件 |
- 配置：
配置
[root@server ~]# cat /etc/my.cnf.d/mariadb-server.cnf | grep -A 4 "\[mysqld\]"
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/data/mariadb.log
pid-file=/run/mariadb/mariadb.pid
[root@server ~]# systemctl restart mysqld
查看
[root@server ~]# mysql -e "show variables like '%log%error'"
+---------------+------------------+
| Variable_name | Value |
+---------------+------------------+
| log_error | /data/mariadb.log |
+---------------+------------------+
一般查询日志
作用
记录mysql所有执行成功的SQL语句信息，可以做审计用，一般情况下这个日志不会开，除非有特殊要求 例如：ELK。
参数
| 变量 | 值 | 说明 |
|---|---|---|
| general_log | on | off | 一般查询日志开关 |
| general_log_file | /path/hostname_general.log | 一般查询日志文件 |
配置
配置
[root@server mysql]# cat /etc/my.cnf.d/mariadb-server.cnf | grep -A 6 "\[mysqld\]"
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/data/mariadb.log
pid-file=/run/mariadb/mariadb.pid
general_log=on
general_log_file=/data/mariadb_general.log
[root@server ~]# systemctl restart mysqld
查看
[root@server mysql]# mysql -e "show variables like '%general%'"
+------------------+--------------------------+
| Variable_name | Value |
+------------------+--------------------------+
| general_log | ON |
| general_log_file | /data/mariadb_general.log |
+------------------+--------------------------+
慢查询日志
作用
是将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件。
通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的。
变量
| 变量 | 值 | 说明 |
|---|---|---|
| long_query_time | n | 慢查询超时时长n |
| slow_query_log | 1 | on | 0 | off | 慢查询日志开关 |
| slow_query_log_file | /path/hostname_slow.log | 慢查询日志文件 |
| log_queries_not_using_indexes | on | off | 认定未使用索引的查询属于慢查询开关 |
配置
配置
[root@server mysql]# cat /etc/my.cnf.d/mariadb-server.cnf | grep -A 9 "\[mysqld\]"
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/data/mariadb.log
pid-file=/run/mariadb/mariadb.pid
general_log=off
general_log_file=/data/mariadb_general.log
slow_query_log=on
slow_query_log_file=/data/mariadb_slow_query.log
long_query_time=3
[root@server ~]# systemctl restart mysqld
查看
[root@server mysql]# mysql -e "show variables like '%slow_query_log%'"
+---------------------+-----------------------------+
| Variable_name | Value |
+---------------------+-----------------------------+
| slow_query_log | ON |
| slow_query_log_file | /data/mariadb_slow_query.log |
+---------------------+-----------------------------+
[root@server mysql]# mysql -e "show variables like '%long%query%'"
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 3.000000 |
+-----------------+----------+
测试
[root@server ~]# mysql -e "select benchmark(1000000000,2*3)"
+---------------------------+
| benchmark(1000000000,2*3) |
+---------------------------+
| 0 |
+---------------------------+
[root@server ~]# tail -n 2 /data/mariadb_slow_query.log
SET timestamp=1581160244;
select benchmark(1000000000,2*3);
二进制日志
作用
任何引起或可能引起数据库变化的操作，主要用于回放和时间点恢复。
记录格式
- 行模式（row）
表中每行数据的变化，记录数据详细精确，但io高。
- 语句模式（statement）
按sql语句进行记录，记录信息简洁，但如果sql语句中出现函数，有可能数据不准确。
- 混合模式（mixed）
默认模式，行+语句的混合模式。
日志事件
# at 372
#200208 7:42:22 server id 254 end_log_pos 459 CRC32 0x7afd5c20 Query thread_id=9 exec_time=error_code=0
SET TIMESTAMP=1581165742/*!*/;
SET @@session.pseudo_thread_id=9/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;
SET @@session.sql_mode=1411383296/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database user
/*!*/;
# at 459
#200208 7:43:51 server id 254 end_log_pos 501 CRC32 0x91ef6bbd GTID 0-254-2 ddl
/*!100001 SET @@session.gtid_seq_no=2*//*!*/;
# at 501
#200208 7:43:51 server id 254 end_log_pos 593 CRC32 0xb4f210b6 Query thread_id=9 exec_time=error_code=0
use `user`/*!*/;
SET TIMESTAMP=1581165831/*!*/;
create table name(id int)
/*!*/;
# at 593
#200208 7:44:38 server id 254 end_log_pos 635 CRC32 0xe1427440 GTID 0-254-3 trans
/*!100001 SET @@session.gtid_seq_no=3*//*!*/;
BEGIN
/*!*/;
# at 635
#200208 7:44:38 server id 254 end_log_pos 734 CRC32 0xa8657f43 Query thread_id=9 exec_time=error_code=0
SET TIMESTAMP=1581165878/*!*/;
insert into name (id) value (1)
/*!*/;
- 日志产生时间
| at | statement |
|---|---|
| 200208 7:42:22 | create database user |
| 200208 7:43:51 | |
| 200208 7:43:51 | create table name(id int) |
| 200208 7:44:38 | |
| 200208 7:44:38 | insert into name (id) value (1) |
- 日志相对位置
| at | end_log_pos | statement |
|---|---|---|
| 372 | 459 | create database user |
| 459 | 501 | |
| 501 | 593 | create table name(id int) |
| 593 | 635 | |
| 635 | 734 | insert into name (id) value (1) |
- 说明
从上表中可以看出相同时间可能有多个不同操作，此时我们最好以相对位置来回放。
日志文件
默认日志文件和索引文件
[root@server ~]# ls /var/lib/mysql/mariadb-bin*
/var/lib/mysql/mariadb-bin.000001 /var/lib/mysql/mariadb-bin.index
- 二进制日志文件
二进制日志包含了引起或可能引起数据库改变(如delete语句但没有匹配行)的事件信息，但绝不会包括select和show这样的查询语句。语句以"事件"的形式保存，所以包含了时间、事件开始和结束位置等信息。
- 日志索引文件
当二进制日志文件滚动的时候会向该文件中写入对应的信息，所以该文件包含所有使用的二进制日志文件的文件名。
变量
| 变量 | 值 | 说明 |
|---|---|---|
| sql_log_bin | on | 对当前会话有效 在会话中进行修改 |
| log_bin | /path/hostname_bin | 二进制日志文件 |
| binlog_do_db | "database " | 记录指定数据库 |
| binlog_ignore_db | “database” | 不记录指定数据库 |
| binlog_format | mixed | row | statement | 二进制日志格式 |
配置
配置
[root@server ~]# cat /etc/my.cnf.d/mariadb-server.cnf | grep -A 11 "\[mysqld\]"
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/data/mariadb.log
pid-file=/run/mariadb/mariadb.pid
general_log=off
general_log_file=/data/mariadb_general.log
slow_query_log=on
slow_query_log_file=/data/mariadb_slow_query.log
long_query_time=3
server_id=254
log-bin=/data/mariadb_bin
[root@server ~]# systemctl restart mysqld
查看
[root@server ~]# mysql -e "show variables like '%log%bin%'"
+---------------------------------+-------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------+
| log_bin | ON |
| log_bin_basename | /data/mariadb_bin |
| log_bin_index | /data/mariadb_bin.index |
+---------------------------------+-------------------------+
添加一些数据，使二进制日志文件有内容
日志查看
- mysqlbinlog
Usage: mysqlbinlog [options] log-files
-d, --database=name 只查看指定数据库
-r /path/filename 保存二进制日志文件内容到文件
-s： 查看简要信息会省略掉一些额外的信息如位置信息和时间信息以及基于行的日志
--start-datetime=date 开始时间，date='yyyy-mm-dd hh:mm:ss‘
--stop-datetime=date 结束时间，date='yyyy-mm-dd hh:mm:ss‘
--start-position=# 开始位置
--stop-position=# 结束位置
- show master status
[root@server ~]# mysql -e "show master status"
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb_bin.000004 | 375 | | |
+--------------------+----------+--------------+------------------+
- show binary logs
MariaDB [cr]> show binary logs;
+---------------------+-----------+
| Log_name | File_size |
+---------------------+-----------+
| master_1_bin.000001 | 354 |
| master_1_bin.000002 | 1224 |
+---------------------+-----------+
- show binlog events
[root@server ~]# mysql -e "show binlog events in 'mariadb_bin.000001'"
+--------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+--------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
| mariadb_bin.000001 | 4 | Format_desc | 254 | 256 | Server ver: 10.3.11-MariaDB-log, Binlog ver: 4 |
| mariadb_bin.000001 | 256 | Gtid_list | 254 | 285 | [] |
| mariadb_bin.000001 | 285 | Binlog_checkpoint | 254 | 330 | mariadb_bin.000001 |
| mariadb_bin.000001 | 330 | Gtid | 254 | 372 | GTID 0-254-1 |
| mariadb_bin.000001 | 372 | Query | 254 | 459 | create database user |
| mariadb_bin.000001 | 459 | Gtid | 254 | 501 | GTID 0-254-2 |
| mariadb_bin.000001 | 501 | Query | 254 | 593 | use `user`; create table name(id int) |
| mariadb_bin.000001 | 593 | Gtid | 254 | 635 | BEGIN GTID 0-254-3 |
| mariadb_bin.000001 | 635 | Query | 254 | 734 | use `user`; insert into name (id) value (1) |
| mariadb_bin.000001 | 734 | Xid | 254 | 765 | COMMIT /* xid=14 */ |
+--------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
[root@server ~]# mysql -e "show binlog events in 'mariadb_bin.000001' from 372"
+--------------------+-----+------------+-----------+-------------+----------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+--------------------+-----+------------+-----------+-------------+----------------------------------------------+
| mariadb_bin.000001 | 372 | Query | 254 | 459 | create database user |
| mariadb_bin.000001 | 459 | Gtid | 254 | 501 | GTID 0-254-2 |
| mariadb_bin.000001 | 501 | Query | 254 | 593 | use `user`; create table name(id int) |
| mariadb_bin.000001 | 593 | Gtid | 254 | 635 | BEGIN GTID 0-254-3 |
| mariadb_bin.000001 | 635 | Query | 254 | 734 | use `user`; insert into name (id) value (1) |
| mariadb_bin.000001 | 734 | Xid | 254 | 765 | COMMIT /* xid=14 */ |
+--------------------+-----+------------+-----------+-------------+----------------------------------------------+
日志暂停写入
sql_log_bin仅对当前会话有效，会话关闭，该命令失败。
[root@server encrypt]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 10.3.11-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> set sql_log_bin=off;
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> show variables like 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | OFF |
+---------------+-------+
1 row in set (0.001 sec)
MariaDB [(none)]> quit;
Bye
#会话退出后sql_log_bin恢复打开
[root@server encrypt]# mysql -e "show variables like 'sql_log_bin'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON |
+---------------+-------+
日志截断
- 重启mysqld
- flush logs
[root@server ~]# rm -fr /data/*
[root@server ~]# systemctl start mariadb.service
[root@server ~]# ls /data/
mariadb_bin.000001 mariadb_bin.index
[root@server ~]# systemctl restart mariadb.service
[root@server ~]# ls /data/
mariadb_bin.000001 mariadb_bin.000002 mariadb_bin.index #截断生成了000002日志
[root@server ~]# mysql -e "flush logs"
[root@server ~]# ls /data/
mariadb_bin.000001 mariadb_bin.000002 mariadb_bin.000003 mariadb_bin.index #截断生成了000003日志
日志删除
- reset master
#清空所有日志
[root@server ~]# mysql -e "reset master"
- purge
#删除指定日志文件
[root@server ~]# mysql -e "purge binary logs to 'mariadb_bin.000001'"
#删除“日期”之前的日志
[root@server ~]# mysql -e "purge binary logs before '2020-02-08 00:00:00'"
- expire_logs_days
清理原理：当产生新的binlog时即binlog rotate，才会判断前面binlog的stop datetime是否超过expire_logs_days指定的天数，如果超过则会被清理。
产生新的binlog：
- 重启mysqld
- binlog大小超过max_binlog_size
- flush log | flush binary logs
#指定超过多少天的日志自动过期清空。
[root@server ~]# cat /etc/my.cnf.d/mariadb-server.cnf | grep expire
expire_logs_days=3
[root@server ~]# mysql -e "show variables like '%expire%'"
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 3 |
+------------------+-------+
日志回放
1、可按照相对位置和时间点进行恢复
mysqlbinlog --stop-datetime="2020-02-08 00:00:00" /data/mariadb-bin.000001 | mysql -u user -p password
2、也可以先写成sql文件再恢复
mysqlbinlog /data/mariadb-bin.000001 > /tmp/a.sql
mysqlbinlog /data/mariadb-bin.000002 >> /tmp/a.sql
mysql -u root -p password -e "source /tmp/a.sql"
事务日志
-
作用
通过事务日志的redo log 和 undo log实现事务的原子性和持久性。 -
参数
| 变量 | 值 | 说明 |
|---|---|---|
| innodb_log_group_home_dir | ./ | redo log 保存目录 |
| innodb_log_file_size | 100663296 byte | 默认redo log 96M，超过后日志滚动 |
| innodb_log_files_in_group | 1 | redo log 文件数 |
| innodb_undo_directory | ./ | undo log 保存目录 |
| innodb_undo_logs | 128 | 回滚段个数 |
| innodb_undo_tablespaces | 0 | 设置undo log 文件数；0时写入ibdata1；n时创建undo00*这样n个文件供其写入。要使用该参数必须重新执行mysql_install_db --defaults-file=/etc/my.cnf --datadir=…，其中my.cnf配置了该参数 |
[root@localhost mariadb]# ls -lh data/ib*
-rw-rw---- 1 mysql mysql 12M Feb 23 11:20 ibdata1 #系统表空间
-rw-rw---- 1 mysql mysql 48M Feb 23 11:21 ib_logfile0 #redo log
-rw-rw---- 1 mysql mysql 48M Feb 23 11:20 ib_logfile1 #redo log
-rw-rw---- 1 mysql mysql 10M Feb 23 11:20 undo001 #undo log
-rw-rw---- 1 mysql mysql 10M Feb 23 11:20 undo002 #undo log
-rw-rw---- 1 mysql mysql 10M Feb 23 11:20 undo003 #undo log
-rw-rw---- 1 mysql mysql 10M Feb 23 11:20 undo004 #undo log
undo log
undo log实现事务原子性的关键，事务对数据库所有修改，InnoDB都会生成对应的undo log，如果事务执行失败或调用了rollback，便可根据undo log回滚数据。
redo log
redo log实现事务持久性的关键，redo log采用的是WAL（Write-ahead logging，预写式日志），事务提交对数据库的所有修改，InnoDB都会先将修改写入日志，如果数据写入磁盘时宕机，便可根据redo log重做修改。
redo log 和 binlog
-
作用不同：redo log是用于crash recovery的，保证MySQL宕机也不会影响持久性；binlog是用于point-in-time recovery的，保证服务器可以基于时间点恢复数据，此外binlog还用于主从复制。
-
层次不同：redo log是InnoDB存储引擎实现的，而binlog是MySQL的服务器层实现的。
-
内容不同：redo log是物理日志，内容基于磁盘的Page；binlog的内容是二进制的，根据binlog_format参数的不同，可能基于sql语句、基于数据本身或者二者的混合。
-
写入时机不同：binlog在事务提交时写入；redo log的写入时机相对多元：
670
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
