MySQL 日志

为什么需要日志

    1.用来排错
    2.用来做数据分析
    3.了解程序的运行情况，是否健康--》了解MySQL的性能，运行情况

在MySQL数据库中，日志（log）是记录数据库操作和状态变化的重要组成部分。

    作用：
        1.恢复还原数据--》二进制日志
        2.排错

日志类型

    存放位置：/data/mysql
    1.错误日志 error log  （默认开启）
        名字：主机名.err
    2.通用日志  默认是关闭
        名字：主机名.log
    3.慢日志 默认是关闭
        名字：主机名-slow.log
    4.二进制日志 binary log 
        文件里存放的是二进制数据，使用cat，tail看不了，需要专门的工具查看日志
        默认关闭
        名字：主机名-bin.000001

     5. undo log
            名字：ibdata1
     6. redo log
            ib_logfile0
            ib_logfile1

错误日志

登录失败会记录到错误日志
配置文件出错也会记录
启动过程出问题也会记录

可以指定错误日志名字

# 查看错误日志名字
root@hunan 17:08  mysql>show variables like '%err%';
+---------------------+--------------+
| Variable_name       | Value        |
+---------------------+--------------+
| binlog_error_action | ABORT_SERVER |
| error_count         | 0            |
| log_error           | ./mysql.err  |
| log_error_verbosity | 3            |
| max_connect_errors  | 100          |
| max_error_count     | 64           |
| slave_skip_errors   | OFF          |
+---------------------+--------------+
7 rows in set (0.00 sec)

# 修改配置文件
[root@mysql ~]# vim /etc/my.cnf
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
log-error=sc.err
[root@mysql ~]# service mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL.Logging to '/data/mysql/sc.err'.
.. SUCCESS! 

[root@mysql mysql]# ls
auto.cnf    client-cert.pem  ib_logfile0  mysql.err           private_key.pem
ca-key.pem  client-key.pem   ib_logfile1  mysql.pid           public_key.pem   
sys             ca.pem        ibtmp1       mysql.sock          sc.err 

如果指定错误日志的路径，需要给mysql用户写的权限

[root@mysql mysql]# cat /etc/my.cnf
[mysqld_safe]

[client]
socket=/data/mysql/mysql.sock

[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
#指定错误日志的路径
log-error = /mysql_log/sc.err

[mysql]
auto-rehash
prompt=\u@\d \R:\m  mysql>

#重启服务
[root@mysql mysql]# service mysqld restart

#授权
[root@mysql mysql]# chown mysql:mysql /mysql_log/

通用日志

优点：记录所有的sql操作

缺点：消耗大量的磁盘空间

临时开启通用日志

root@(none) 18:11  mysql>select @@general_log;
+---------------+
| @@general_log |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

root@(none) 18:11  mysql>set global general_log=1;

永久开启通用日志

[root@mysql ~]# vim /etc/my.cnf
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
general_log

慢日志

作用：记录消耗时间比较长的SQL语句，为数据库性能提升提供了线索

开启慢日志

root@(none) 18:12  mysql>select @@slow_query_log;
+------------------+
| @@slow_query_log |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

#默认是10s
root@(none) 18:07  mysql>show variables like '%long_query%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)


[root@mysql ~]# vim /etc/my.cnf
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
slow_query_log = 1

二进制日志

开启二进制日志

# 查看二进制日志
root@(none) 18:16  mysql>select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

# 修改配置
[root@mysql ~]# vim /etc/my.cnf
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
log_bin
server_id = 1
[root@mysql ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!
[root@mysql mysql]# ls
ca-key.pem       ib_buffer_pool  mysql-bin.000001
ca.pem           ibdata1         mysql-bin.index
[root@mysql mysql]# cat mysql-bin.000001                                                     _þbin¢dBdw{5.7.41-log¢dBd8

**4V³ÿH¢dBd#­ft[root@mysql mysql]# 

#使用mysqlbinlog 查看二进制日志
[root@mysql mysql]# mysqlbinlog mysql-bin.000001 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#230421 18:25:38 server id 1  end_log_pos 123 CRC32 0x48ffb356     Start: binlog v 4, server v 5.7.41-log created 230421 18:25:38 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
omRCZA8BAAAAdwAAAHsAAAABAAQANS43LjQxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACiZEJkEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AVaz/0g=
'/*!*/;
# at 123
#230421 18:25:38 server id 1  end_log_pos 154 CRC32 0x7466ad89     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*/;


#查看二进制文件的大小
root@(none) 18:27  mysql>select @@max_binlog_size;
+-------------------+
| @@max_binlog_size |
+-------------------+
|        1073741824 |
+-------------------+
1 row in set (0.00 sec)


#查看当前正在使用的是哪个二进制日志文件
root@(none) 18:28  mysql>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# 刷新
root@(none) 18:29  mysql>flush logs;
Query OK, 0 rows affected (0.00 sec)


root@(none) 18:30  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)

# 删除所有的二进制日志
root@(none) 18:30  mysql>reset master;
Query OK, 0 rows affected (0.01 sec)

root@(none) 18:30  mysql>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

二进制日志的格式

row level：记录操作的每一行数据  --》表里的哪些行的数据发生了变化

root@(none) 18:13  mysql>select version();
+------------+
| version()  |
+------------+
| 5.7.37-log |
+------------+
1 row in set (0.00 sec)


root@tennis 18:33  mysql>show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

statement level：记录用户输入的SQL语句（mariadb-5.5.64）

mixed level：混合使用Row和Statement格式，对DDL记录会使用Statement，对于table里的行操作会记录Row格式

产生新的二进制日志文件

        service  mysqld restart

        flush  logs

        二进制文件大小超过1G

mysql-bin.index：二进制日志文件的索引

作用：记录一共有多少个二进制日志文件了

[root@mysql mysql]# cat mysql-bin.index 
./mysql-bin.000001

一个二进制日志文件是否记录了整个mysql进程里所有的库的操作？

        是的，对所有库进行的操作都会记录到一个二进制文件里

如果需要记录到不同的日志文件里，可以采用多实例

mysql实例： 正在运行的一个mysql进程，这个进程里有哪些库可以操作，二进制日志就记录哪些库的操作。
多实例：
    多启动几个mysqld的进程，一个mysqld的进程对应一个库
    隔离应用，避免一个库使用特别频繁，影响其他的库
    多实例任然受到整个机器整体系统资源的限制。
    经典的例子：一款游戏对应一个实例。
多实例的替代方案：使用云服务器：一款游戏使用一台云服务器。

清除所有的二进制日志

1.手动清理

root@TENNIS 15:15  scmysql>purge binary logs to 'sc-mysql-bin.000002';
Query OK, 0 rows affected (0.01 sec)

2.自动清理

#临时修改
SQL > set global expire_logs_days = 3;

#永久修改
vim /etc/my.cnf
[mysqld]

#开启binary log
log-bin=mysql-bin

#日志超过3天自动过期
expire_logs_days = 3


重启MySql服务

什么时候会产生二进制日志？

binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志。

二进制日志不是存储引擎管理的，是MySQL内部的相关线程去完成。
二进制日志保存下来有2个过程：
flush：将二进制日志写到binlog_buffer里
sync：将binlog_buffer里的内容刷盘到disk里binlog file

二进制日志是如何写到磁盘里的？

sync_binlog=0: 表示刷新binlog时间点由操作系统自身来决定，操作系统自身会每隔一段时间就会刷新缓存数据到磁盘，这个性能最好。--》容易丢失数据

sync_binlog=1: 表示每次事务提交都要调用fsync()，刷新binlog写入到磁盘。--》能快速的存储数据，不容易丢失数据

sync_binlog=N: 表示 N个事务提交，才会调用 fsync()进行一次binlog刷新，写入磁盘。

root@(none) 18:19  mysql>show variables like '%sync_binlog%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1     |
+---------------+-------+
1 row in set (0.00 sec)

redo log 和 undo log

重做日志（redo log）
作用：确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

内容：物理格式的日志，记录的是物理数据页面的修改的信息，其redo log是顺序写入redo log file的物理文件中去的。

什么时候产生：事务开始之后就产生redo log，redo log的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo log文件中。

什么时候释放：当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。

对应的物理文件：位于数据库的data目录下的ib_logfile1&ib_logfile2

回滚日志（undo log）
作用：保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC）。

内容：逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于redo log的。

什么时候产生：事务开始之前，将当前是的版本生成undo log，undo 也会产生 redo 来保证undo log的可靠性。

什么时候释放：当事务提交之后，undo log并不能立马被删除，而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。

对应的物理文件：数据目录下/data/mysql/ibdata1

介绍

redo的整体流程

 先写日志，再写数据

写入流程

innodb 存储引擎产生的日志

    redo log：记录的是脏数据的变化--》buffer pool里的
    作用：解决事务commit不成功
        MySQL意外宕机重启也不要紧。只要在重启时解析redo log中的事务而后重做一遍。将Buffer Pool中的缓存页重作成脏页。后续再在合适的时机将该脏页刷入磁盘便可。

    undo log：记录某数据被修改前的值
    作用：解决事务rollback，撤销