MySQL二进制日志等

MySQL 日志管理

一、事务日志

1、Innodb事务日志相关配置：

show variables like '%innodb_log%';

innodb_log_file_size 50331648		#每个日志文件大小
innodb_log_files_in_group 2			#日志组成员个数
innodb_log_group_home_dir ./		#事务文件路径

2、事务日志性能优化

innodb_flush_log_at_trx_commit=0|1|2

1 此为默认值，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。 这是完全遵守ACID特性

0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。这样可提供更好的性能，但服务器崩溃可能丢失最后一秒的事务

2 每次提交后都会写入OS的缓冲区，但每秒才会进行一次刷新到磁盘文件中。 性能比0略差一些，但操作系统或停电可能导致最后一秒的交易丢失

设置为1，同时sync_binlog = 1表示最高级别的容错
高并发业务行业最佳实践，是使用第三种折衷配置（=2）：

1.配置为2和配置为0，性能差异并不大，因为将数据从Log Buffer拷贝到OS cache，虽然跨越用户态与内核态，但毕竟只是内存的数据拷贝，速度很快
2.配置为2和配置为0，安全性差异巨大，操作系统崩溃的概率相比MySQL应用程序崩溃的概率，小很多，设置为2，只要操作系统不奔溃，也绝对不会丢数据

二、错误日志

1、错误文件路径

mysql> show global variables like 'log_error';
+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| log_error     | /var/log/mysql/mysqld.log |
+---------------+---------------------------+

三、通用日志

通用日志：记录对数据库的通用操作，包括:错误的SQL语句
通用日志可以保存在：file（默认值）或 table（mysql.general_log表）
1、通用日志相关设置

general_log=ON|OFF					#通用日志 0关闭 1打开
general_log_file=HOSTNAME.log		#通用日志存放的文件路径
log_output=TABLE|FILE|NONE			#默认通用日志存放在文件中

2、查看是否启用通用日志

mysql> select @@general_log;
+---------------+
| @@general_log |
+---------------+
|             0 |
+---------------+

3、启用通用日志

mysql> set global general_log=1;

4、查看通用日志存放方式

mysql> show global variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+

5、查看通用日志存放的文件路径

mysql> select @@general_log_file;
+--------------------------------+
| @@general_log_file             |
+--------------------------------+
| /var/lib/mysql/rocky86-100.log |
+--------------------------------+

6、修改通用日志，记录通用日志至mysql.general_log表中

mysql> set global log_output="table";
mysql> show global variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+

7、general_log表是CSV格式的存储引擎

mysql> use mysql;
mysql> show table status like 'general_log'\G
*************************** 1. row ***************************
           Name: general_log
         Engine: CSV
        Version: 10
     Row_format: Dynamic
           Rows: 2
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2022-09-01 11:22:44
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: General log

四、慢查询日志

慢查询日志：记录执行查询时长超出指定时长的操作
1、慢查询相关变量

slow_query_log=ON|OFF #开启或关闭慢查询，支持全局和会话，只有全局设置才会生成慢查询文件
long_query_time=N #慢查询的阀值，单位秒,默认为10s
slow_query_log_file=HOSTNAME-slow.log #慢查询日志文件
#上述查询类型且查询时长超过long_query_time，则记录日志

log_queries_not_using_indexes=ON #不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语句是否记录日志，默认OFF，即不记录
log_slow_rate_limit = 1 #多少次查询才记录，mariadb特有

2、慢查询分析工具mysqldumpslow

[root@rocky86-100 ~]# mysqldumpslow --help

五、二进制日志(备份)

1、二进制日志相关的服务器变量

sql_log_bin=ON|OFF					#是否记录二进制日志，默认ON，支持动态修改，系统变量，而非服务器选项
log_bin=/PATH/BIN_LOG_FILE			#指定文件位置；默认ON，表示不启用二进制日志功能，上述两项都开启才可以
binlog_format=STATEMENT|ROW|MIXED	#二进制日志记录的格式，mariadb5.5默认STATEMENT
max_binlog_size=1073741824：		#单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G
									#说明：文件达到上限时的大小未必为指定的精确值
binlog_cache_size=4m			#此变量确定在每次事务中保存二进制日志更改记录的缓存的大小（每次连接）
max_binlog_cache_size=512m		#限制用于缓存多事务查询的字节大小。
sync_binlog=1|0					#设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘
expire_logs_days=N				#二进制日志可以自动删除的天数。 默认为0，即不自动删除

2、查看二进制日志的格式

# 二进制日志记录三种格式
# 基于"语句"记录：statement，记录语句，默认模式（ MariaDB 10.2.3 版本以下 ），日志量较少
# 基于"行"记录：row，记录数据，日志量较大，更加安全，建议使用的格式,MySQL8.0默认格式
# 混合模式：mixed, 让系统自行判定该基于哪种方式进行，默认模式（ MariaDB 10.2.4及版本以上）
mysql> select @@binlog_format;
mysql> show variables like 'binlog_format';

3、二进制日志文件的构成

1.日志文件：mysql|mariadb-bin.文件名后缀，二进制格式,如： on.000001,mariadb-bin.000002
2.索引文件：mysql|mariadb-bin.index，文本格式,记录当前已有的二进制日志文件列表

[root@rocky86-100 ~]# ll /data/logbin/
-rw-r----- 1 mysql mysql 156 Sep  1 17:47 mysql-bin100.000001
-rw-r----- 1 mysql mysql  33 Sep  1 17:47 mysql-bin100.index

4、查看二进制日志文件列表，及大小

SHOW {BINARY | MASTER} LOGS

6、查看使用中的二进制日志文件

SHOW MASTER STATUS

7、mysqlbinlog：二进制日志的客户端命令工具，支持离线查看二进制日志
命令格式：

mysqlbinlog [OPTIONS] log_file…
--start-position=# 指定开始位置
--stop-position=#
--start-datetime= #时间格式：YYYY-MM-DD hh:mm:ss
--stop-datetime=
--base64-output[=name]
-v -vvv

范例：

mysqlbinlog --start-position=678 --stop-position=752 /var/lib/mysql/mariadbbin.000003 -v

8、同步远程主机的二进制日志

mysqlbinlog -R --host=10.0.0.100 --user=repluser --password=123456 --raw --stop-never mysql-bin100.000001