15-MySQL日志管理
第9章 MySQL日志管理
9.0. 什么是日志?
计算机遇到错误\不能使用以及需要管理员知道的信息等等这类问题,
也要和管理员汇报.汇报的方式就是写到文件里,这文件往往就叫日志文件
9.1 MySQL常用日志种类：
错误日志（Error log）
#当数据库启动、运行、停止时产生该日志 ==>error.log
普通查询日志（General query log） #==>nginx access.log
客户端连接数据库执行语句时产生该日志
*****二进制日志（Binary log）
当数据库内容[发生改变时产生该日志]，也被用来实现主从复制功能
*****慢查询日志（Slow query log）
SQL语句在数据库查询超过指定时间产生该日志
9.2错误日志
9.2.1 介绍
记录数据库从启动以来,状态\报错\警告等.
9.2.2 查询和配置
mysql> show variables like '%log_error';
+---------------+-------------+
| Variable_name | Value |
+---------------+-------------+
| log_error | ./mysql.err |
+---------------+-------------+
1 row in set (0.00 sec)
[root@mysql ~]# ll /data/3306/data/mysql.err
-rw-r-----. 1 mysql mysql 15331 7月 29 12:07 /data/3306/data/mysql.err
配置:my.cnf
[root@mysql ~]# cat /etc/my.cnf
[mysqld_safe]
# 配置错误日志存放
log-error = /data/3306/data/mysql.err
[root@mysql ~]#/etc/init.d/mysqld restart
默认路径:/data/3306/data/mysql.err
mysql> show variables like '%log_error';
+---------------+-----------------------------+
| Variable_name | Value |
+---------------+-----------------------------+
| log_error | /data/3306/data/oldboy.err |
+---------------+-----------------------------+
1 row in set (0.01 sec)
9.2.3 错误日志怎么用
每天定时巡检,主要关注[ERROR]和[WARNING]。
9.2.4 错误日志切割
[root@mysql data]# cd /data/3306/data/
[root@mysql data]# mv mysql-.err mysql-$(date +%F).err
# 刷新错误日志。
[root@mysql data]# mysqladmin -uroot -p123 flush-logs
##切割结果
[root@mysql data]# ls /data/3306/data/|grep 2023
mysql-2023-07-30.err
##按天分析,报警.也可以直接收集到ELK里.
9.2.4 排查错误
新手安装数据库时，遇到数据库无法启动时的排查方法为：
1)先把错误日志文件清空，然后重新启动MySQL服务，
再查看日志文件报有什么错误，并根据错误日志进行处理。
2)如果无法解决，则删除数据文件，重新初始化数据库。
3)遇到错误，关键字谷歌其次百度。
9.3. 普通日志(access.log) ##了解
9.3.1 介绍
默认是关闭的,因为会大量消耗磁盘IO.
可以记录MySQL中发生过的所有操作日志.一般用来调试.
mysql> show variables like '%general%';
+------------------+---------------------------+
| Variable_name | Value |
+------------------+---------------------------+
| general_log | OFF |
| general_log_file | /data/3306/data/mysql.log |
+------------------+---------------------------+
2 rows in set (0.00 sec)
[root@mysql data]# ll /data/3306/data/mysql.log
ls: 无法访问/data/3306/data/mysql.log: 没有那个文件或目录
9.3.2 如何配置
[root@mysql data]# vim /etc/my.cnf
[mysqld]
general_log = ON
general_log_file = /data/3306/data/mysql.log
# 修改配置文件要重启mysql服务
[root@mysql data]# systemctl restart mysqld
#
[root@oldboy ~]# cat /data/3306/data/oldboy.log
/usr/local/mysql/bin/mysqld, Version: 8.0.26 (MySQL Community Server - GPL). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
2021-12-12T03:15:48.572994Z 8 Connect root@localhost on using Socket
2021-12-12T03:15:48.573597Z 8 Query select @@version_comment limit 1
2021-12-12T03:15:49.916995Z 8 Query show variables like '%general_log%'
2021-12-12T03:15:54.772789Z 8 Query show variables like '%general_log%'
2021-12-12T03:16:02.494293Z 8 Query show databases
2021-12-12T03:16:03.967361Z 8 Quit
9.3.3 普通查询日志作用
审计
调试
真正要审计,在客户端用堡垒机,跳板机jumpserver审计.
实际生产工作中极少使用普通查询日志,最多临时开一下..
9.4 二进制日志 (binlog)
9.4.1 介绍
二进制日志（Binary Log）是一种用于记录数据库中所有更改的日志文件。二进制日志对于数据恢复、数据库复制和高可用性非常重要。
以events形式,记录MySQL数据库中【变更类】的SQL操作日志(DDL\DCL\DML).
举例：
不会记录select,show
会记录crate,drop,alter,insert,update
9.4.2 作用
1.数据增量备份与恢复
2.主从复制功能nfs+sersync
9.4.3 参数及配置
mysql> show variables like '%log_bin%';
+---------------------------------+------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------+
# 表示二进制日志功能是否已启用
| log_bin | ON |
# 表示二进制日志文件的基本名称
| log_bin_basename | /data/3306/data/binlog |
# 表示二进制日志索引文件的路径
| log_bin_index | /data/3306/data/binlog.index |
# 表示是否信任二进制日志中的函数创建者
| log_bin_trust_function_creators | OFF |
# 表示是否使用V1格式的行事件来记录二进制日志
| log_bin_use_v1_row_events | OFF |
# 表示当前会话是否记录到二进制日志
| sql_log_bin | ON |
+---------------------------------+------------------------------+
6 rows in set (0.00 sec)
#4个价参
log_bin=1 #开关,是否开启binlog日志记录。
log_bin_basename=/data/3306/data/binlog #路径和名字。
log_bin_index =/data/3306/data/binlog.index #binlog文件名列表。
sql_log_bin = 1 #是否记录binlog,临时使用.
mysql> show variables like '%binlog%';
+------------------------------------------------+----------------------+
| Variable_name | Value |
+------------------------------------------------+----------------------+
# 这个变量表示二进制日志缓存的大小
| binlog_cache_size | 32768 |
# 这个变量表示二进制日志的格式
| binlog_format | ROW |
# 这个变量表示二进制日志缓存的最大大小
| max_binlog_cache_size | 18446744073709547520 |
# 这个变量表示单个二进制日志文件的最大大小
| max_binlog_size | 1073741824 |
# 这个变量表示二进制日志语句缓存的最大大小
| max_binlog_stmt_cache_size | 18446744073709547520 |
# 这个变量用于控制写入二进制日志时是否采用同步写入方式
| sync_binlog | 1 |
+------------------------------------------------+----------------------+
其他binlog参数：
1)sync_binlog=1 #数据安全参数,数据落盘。性能差.
重点面试题:
mysql的双一是什么?
重点|面试1: sync_binlog=1
#【双一】的第一个1
1.保证事务提交前刷新binlog到磁盘。
#【双一】的第2个1
事务redo日志落盘，见后文。
2)一堆
max_binlog_size #最大binlog文件最大1G,如果超过则自动切割.
max_binlog_cache_size #最大binlog缓存大小
binlog_cache_size #当前binlog缓存大小.
原文链接：https://blog.csdn.net/lianggx3/article/details/116355284
3)binlog日志格式
binlog_format=row/statement/mixed
#binlog日志记录格式,默认ROW格式,早期statement格式.
重点|面试2: binlog_format=row/statement/mixed 格式有几种,区别.
格式区别:
1.行模式row(RBR)(5.6以后开始默认的格式,5.6是语句模式)
a.即使执行的SQL语句是一条,但是更新了很多行,binlog日志会记录每一行数据的真实变化，所以日志量会比较大.占磁盘空间大,消耗IO大，但是记录足够准确,数据不丢失.
b.只影响DML语句(按行记录),DDL和DCL仍然是Statement模式(按语句)记录.
2.statement(SBR)(5.7以前默认):
a.记录发生的语句本身,不会按更改的行记录.日志量相对少.
b.记录有可能不准确.例如特殊功能\函数\触发器\视图\存储过程
c,有100万行记录,delete全删,记录一行delete语句日志.
3.mixed混合模式
智能记录
企业倾向选择:row格式,mysql官方也推荐行格式
因为数据库数据安全更重要.
默认是row:
mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
row模式测试:
#注释:
[root@db01 /data/3306/data]# vim /etc/my.cnf
[client]
socket=/tmp/mysql.sock
#default-character-set=utf8mb4
mysql> update oldboy.stu set sname="test" where age>20;
[root@oldboy data]# mysqlbinlog --base64-output=decode-rows -vvv binlog.000030
一个更新语句在binlog日志里记录很多行,改变了4行记录,使用50行日志
### UPDATE `oldboy`.`stu`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='oldboy' /* VARSTRING(256) meta=256 nullable=0 is_null=0 */
### @3=28 /* TINYINT meta=0 nullable=0 is_null=0 */
### @4=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @5='111' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @6=1 /* TINYINT meta=0 nullable=0 is_null=0 */
statement模式测试:
mysql> set global binlog_format=statement;
退出再登陆
mysql> show variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
mysql> update oldboy.stu set sname="oldboy" where age>20;
[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv binlog.000019|grep update
update oldboy.stu set sname="oldboy" where age>20
9.5 binlog应用
9.5.1 查看和分析binlog
1）查看二进制日志文件和大小.
# 用于查看当前MySQL服务器上所有可用的二进制日志文件列表及其相关信息。执行这个命令后，MySQL会返回一个结果集，其中包含了所有二进制日志文件的名称和大小等信息。
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 179 | No |
| binlog.000002 | 179 | No |
| binlog.000003 | 179 | No |
| binlog.000004 | 179 | No |
2）查看当前库记录二进制的文件及位置点.
# 用于查看当前主服务器（Master）的二进制日志（Binary Log）状态。这个命令会返回一些关于主服务器二进制日志的重要信息，包括当前二进制日志文件名、当前二进制日志的位置（日志偏移量）、正在使用的日志格式
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000017 | 893 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
作用:
a.是备份文件和备份后更新的binlog的临界点
b.主从复制同步数据的临界点,也是起始位置
3）查看某个日志具体信息
# 令用于查看指定二进制日志文件的事件（events）内容。
mysql> show binlog events in 'binlog.000017';
+---------------+-----+----------------+-----------+-------------+---------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+---------------------------------------------------+
| binlog.000017 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.30, Binlog ver: 4 |
| binlog.000017 | 126 | Previous_gtids | 1 | 157 | |
| binlog.000017 | 157 | Anonymous_Gtid | 1 | 236 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000017 | 236 | Query | 1 | 322 | BEGIN |
| binlog.000017 | 322 | Table_map | 1 | 388 | table_id: 93 (oldboy.stu) |
| binlog.000017 | 388 | Update_rows | 1 | 530 | table_id: 93 flags: STMT_END_F |
| binlog.000017 | 530 | Xid | 1 | 561 | COMMIT /* xid=17 */ |
| binlog.000017 | 561 | Anonymous_Gtid | 1 | 640 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000017 | 640 | Query | 1 | 729 | BEGIN |
| binlog.000017 | 729 | Query | 1 | 862 | update oldboy.stu set sname="oldboy" where age>20 |
| binlog.000017 | 862 | Xid | 1 | 893 | COMMIT /* xid=25 */ |
+---------------+-----+----------------+-----------+-------------+---------------------------------------------------+
11 rows in set (0.00 sec)
4）查看分析binlog
[root@mysql data]# mysqlbinlog #解析binlog命令
--base64-output=decode-rows #行模式查看,真正恢复数据的时候,不要加此参数.
-vvv # 输出,真正恢复的时候,不要加此参数.
binlog.000018
例子：
mysqlbinlog --base64-output=decode-rows -vvv binlog.000018
#按时间截取日志参数
开始时间
start-datetime (No default value)
结束时间
stop-datetime (No default value)
#按位置截取日志参数（推荐）
--start-position=156
--stop-position=1706
#gtid参数
--skip-gtids ##忽略GTID
--include-gtids ##包含GTID
--exclude-gtids ##排除GTID
截取部分binlog数据:
a.文件大
b.个数多
c.不好查看
填充测试数据:
例子:
create database m;
use m;
create table m(id int);
insert into m values(2);
insert into m values(3);
create database n;
use n;
create table n(id int);
insert into n values(2);
insert into n values(3);
截取binlog日志,命令mysqlbinlog
日志记录形式:
1.时间
2.位置
#按时间截取日志参数
--start-datetime="2023-07-30 11:26:00"
--stop-datetime="2023-07-30 11:26:00"
例子:linux命令行
cd /data/3306/data
mysqlbinlog --base64-output=decode-rows --start-datetime="2023-07-30 11:26:00" --stop-datetime="2023-07-30 11:26:00" binlog.000017 #给定开始时间到给定结束时间
mysqlbinlog --base64-output=decode-rows --start-datetime="2023-07-30 11:26:00" binlog.000024 #到文件结尾
mysqlbinlog --base64-output=decode-rows --stop-datetime="2023-07-30 11:26:00" binlog.000024 #从开头到指定的时间
注意:
1.截取时间不一定是写着的时间,大于,小于几秒.
2.时间截取不精确的,生产不用.
3.https://dev.mysql.com/doc/refman/8.0/en/point-in-time-recovery-positions.html
4.官方建议:
仅使用 --start-datetime和 --stop-datetime 选项来帮助您找到感兴趣的实际事件位置。不建议使用这两个选项来指定要应用的二进制日志段的范围：使用这些选项时丢失二进制日志事件的风险更高。使用 --start-positionand --stop-position 代替。
如何找位置点?
方法1:
mysql> show binlog events in 'binlog.000017';
+---------------+-------+------------+-----------------------------------------------+
| Log_name | Pos |End_log_pos | Info |
+---------------+-------+------------+-----------------------------------------------+
| binlog.000024 | 18181 | 18276 | drop database n /* xid=722 */ |
| binlog.000024 | 18276 | 18353 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000024 | 18353 | 18448 | drop database m /* xid=724 */ |
| binlog.000024 | 18448 | 18525 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000024 | 18525 | 18624 | create database m /* xid=726 */ |----
| binlog.000024 | 18624 | 18701 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000024 | 18701 | 18805 | use `m`; create table m(id int) /* xid=731 */ |
| binlog.000024 | 18805 | 18884 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000024 | 18884 | 18956 | BEGIN |
| binlog.000024 | 18956 | 19000 | table_id: 112 (m.m) |
| binlog.000024 | 19000 | 19040 | table_id: 112 flags: STMT_END_F |
| binlog.000024 | 19040 | 19071 | COMMIT /* xid=732 */ |
| binlog.000024 | 19071 | 19150 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000024 | 19150 | 19222 | BEGIN |
| binlog.000024 | 19222 | 19266 | table_id: 112 (m.m) |
| binlog.000024 | 19266 | 19306 | table_id: 112 flags: STMT_END_F |
| binlog.000024 | 19306 | 19337 | COMMIT /* xid=733 */ |
| binlog.000024 | 19337 | 19414 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000024 | 19414 | 19513 | create database n /* xid=734 */ |
| binlog.000024 | 19513 | 19590 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000024 | 19590 | 19694 | use `n`; create table n(id int) /* xid=739 */ |
| binlog.000024 | 19694 | 19773 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000024 | 19773 | 19845 | BEGIN |
| binlog.000024 | 19845 | 19889 | table_id: 113 (n.n) |
| binlog.000024 | 19889 | 19929 | table_id: 113 flags: STMT_END_F |
| binlog.000024 | 19929 | 19960 | COMMIT /* xid=740 */ |
| binlog.000024 | 19960 | 20039 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000024 | 20039 | 20111 | BEGIN |
| binlog.000024 | 20111 | 20155 | table_id: 113 (n.n) |
| binlog.000024 | 20155 | 20195 | table_id: 113 flags: STMT_END_F |
| binlog.000024 | 20195 | 20226 | COMMIT /* xid=741 */ |
| binlog.000024 | 20226 | 20303 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000024 | 20303 | 20398 | drop database m /* xid=742 */ |
| binlog.000024 | 20398 | 20475 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000024 | 20475 | 20570 | drop database n /* xid=744 */ |
+---------------+-------+------------+-----------------------------------------------+
237 rows in set (0.00 sec)
方法2:
cd /data/3306/data
mysqlbinlog binlog.000017
例子:
mysqlbinlog --skip-gtids --start-position=18525 --stop-position=20226 binlog.000017 >/tmp/bin.sql
######
mysqlbinlog --skip-gtids --stop-position=1588 binlog.000017 >/tmp/bin.sql
mysqlbinlog --skip-gtids --start-position=1489 binlog.000017 >/tmp/bin.sql
错误坑:如果解析不加--skip-gtids,会报错. #--skip-gtids忽略GTID
ERROR 1790 (HY000) at line 198: @@SESSION.GTID_NEXT cannot be changed by a client that owns a GTID. The client owns ANONYMOUS. Ownership is released on COMMIT or ROLLBACK.
注意:
1.位置点没有引号.
2.推荐此方式查看binlog:show binlog events in ' binlog.000017';
3.mysqlbinlog binlog.000017 ###不推荐
位置点记录binlog问题:
1.时间记录
2.数字位置
3.GTID格式记录,5.6新功能.
#gtid参数
--skip-gtids ##忽略GTID
--include-gtids ##包含GTID
--exclude-gtids ##排除GTID
#-d 参数,恢复指定库的binlog
需求:只恢复一个库(drop database oldboy;)
解答:
mysqlbinlog -d oldboy binlog.000xxxx 恢复指定库的binlog
例子:只恢复m库的binlog
mysqlbinlog -d m --skip-gtids --start-position=18525 --stop-position=20226 binlog.000024 >/tmp/bin.sql
如何从binlog中截取一个表的日志:
同一个表的所有更新语句必然包含表名
1.grep,awk
2.binlog2sql工具.
一张表10亿条记录,删了10行,恢复.
9.5.2 数据损坏模拟和恢复--binlog应用增量恢复
故障模拟:
mysql> flush logs; ##从新文件,从156位置开始.
mysql> show master status; ##当前记录的文件名以及位置
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000018 | 157 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'binlog.000018';
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| binlog.000018 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.30, Binlog ver: 4 |
| binlog.000018 | 126 | Previous_gtids | 1 | 157 | |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
2 rows in set (0.00 sec)
测试数据:
mysql> drop database oldboy;
mysql> create database oldboy;
mysql> use oldboy;
mysql> create table t1 (id int);
mysql> insert into t1 values(1);
mysql> insert into t1 values(2);
mysql> insert into t1 values(3);
mysql> select * from oldboy.t1;
mysql> drop table t1; #破坏语句
恢复思路：
恢复方法1:根据位置点精确恢复
指定位置点精确恢复,恢复到drop命令位置点以前的数据,drop语句不恢复.
1.确认对应的binlog日志
mysql> show master status; ###确认日志文件命名为binlog.000038
2.确定日志位置
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000018 | 1838 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'binlog.000018'; ##分析要恢复的日志确定起始和结束位置.
mysql> show binlog events in 'binlog.000018';
+---------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
| binlog.000018 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.30, Binlog ver: 4 |
| binlog.000018 | 126 | Previous_gtids | 1 | 157 | |
| binlog.000018 | 157 | Anonymous_Gtid | 1 | 234 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000018 | 234 | Query | 1 | 344 | drop database oldboy /* xid=99 */ |
| binlog.000018 | 344 | Anonymous_Gtid | 1 | 421 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000018 | 421 | Query | 1 | 535 | create database oldboy /* xid=101 */ |
| binlog.000018 | 535 | Anonymous_Gtid | 1 | 612 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000018 | 612 | Query | 1 | 727 | use `oldboy`; create table t1(id int) /* xid=106 */ |
| binlog.000018 | 727 | Anonymous_Gtid | 1 | 806 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000018 | 806 | Query | 1 | 892 | BEGIN |
| binlog.000018 | 892 | Query | 1 | 997 | use `oldboy`; insert into t1 values(1) |
| binlog.000018 | 997 | Xid | 1 | 1028 | COMMIT /* xid=108 */ |
| binlog.000018 | 1028 | Anonymous_Gtid | 1 | 1107 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000018 | 1107 | Query | 1 | 1193 | BEGIN |
| binlog.000018 | 1193 | Query | 1 | 1298 | use `oldboy`; insert into t1 values(2) |
| binlog.000018 | 1298 | Xid | 1 | 1329 | COMMIT /* xid=109 */ |
| binlog.000018 | 1329 | Anonymous_Gtid | 1 | 1408 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000018 | 1408 | Query | 1 | 1494 | BEGIN |
| binlog.000018 | 1494 | Query | 1 | 1599 | use `oldboy`; insert into t1 values(3) |
| binlog.000018 | 1599 | Xid | 1 | 1630 | COMMIT /* xid=110 */ |
| binlog.000018 | 1630 | Anonymous_Gtid | 1 | 1707 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000018 | 1707 | Query | 1 | 1838 | use `oldboy`; DROP TABLE `t1` /* generated by server */ /* xid=112 */ |
+---------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
22 rows in set (0.00 sec)
确定起始535,结束1599.
3.将记录的binlog文件解析成sql文件,并观察解析结果
cd /data/3306/data
[root@mysql data]# mysqlbinlog --skip-gtids --base64-output=decode-rows --start-position=535 --stop-position=1599 -vvv binlog.000018
4.生成文件,要去掉观察的参数(--base64-output=decode-rows -vvv)
[root@mysql data]# mysqlbinlog --skip-gtids --start-position=535 --stop-position=1599 binlog.000018 >/tmp/bin.sql
查看生成的文件
[root@mysql data]# cat /tmp/bin.sql
5.恢复到数据库
恢复方法1:
[root@mysql data]# mysql -uroot -p123 oldboy </tmp/bin.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
恢复方法2
[root@mysql data]# mysql -uroot -p123 oldboy -e "source /tmp/bin.sql"
mysql: [Warning] Using a password on the command line interface can be insecure.
6.检查结果
[root@mysql data]# mysql -uroot -p123 -e "select * from oldboy.t1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
证明binlog可以实现数据恢复
恢复思路2:删库恢复
适合drop database oldboy;
1.先确定找出什么命令导致的破坏,drop database oldboy;
2.恢复的时候,包含删除破坏的drop命令,通过vim,sed去掉drop命令,然后在恢复。
恢复:
重新flush logs\建库\建表去测试;
1.确认了执行了drop database oldboy
cd /data/3306/data
mysqlbinlog --skip-gtids --start-position=535 binlog.000019 >/tmp/bin1.sql
2.清除SQL文件中的drop database oldboy语句
[root@db01 /data/3306/data]# sed -i '/drop database oldboy/d' /tmp/bin1.sql
[root@db01 /data/3306/data]# grep drop /tmp/bin1.sql
3.导入，报错没有找到oldboy这个库，因为binlog没有记录创建oldboy库的语句，也可以修改bin1.sql文件
[root@mysql data]# mysql -uroot -p123 </tmp/bin1.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1049 (42000) at line 18: Unknown database 'oldboy'
# 我们选择自己进去创建一个
mysql> create database oldboy;
Query OK, 1 row affected (0.01 sec)
# 然后再执行一次
[root@mysql data]# mysql -uroot -p123 </tmp/bin1.sql
4.检查
[root@mysql data]# mysql -uroot -p123 -e "select * from oldboy.t1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
binlog日志企业应用一些问题:
1.一个库的日志,在多个日志文件里,如何恢复?
时间 位置
binlog.000037 起始,文件结尾
binlog.000038 整个文件
binlog.000039 结束时间
命令:
mysqlbinlog --start-datetime="2022/07/11 9:17:34" binlog.000037 #给定开始时间到给定结束时间
mysqlbinlog binlog.000038 #给定开始时间到给定结束时间
mysqlbinlog --stop-datetime="2022/07/11 11:17:34" binlog.000039 #结束时间
3个文件合并
100个binlog文件截取:
mysqlbinlog --start-datetime="2022/07/11 9:17:34" binlog.000037 #给定开始时间到给定结束时间
mysqlbinlog binlog.000001 binlog.000002 binlog.000003 .... >/bin/c.sql
mysqlbinlog --stop-datetime="2022/07/11 11:17:34" binlog.000039 #结束时间
100个文件截取:例子:
mysqlbinlog binlog.000001 binlog.000002 binlog.000003 binlog.000004 binlog.000005 binlog.000006 binlog.000007 binlog.000008 binlog.000009 binlog.000010 binlog.000011 binlog.000012 binlog.000013 binlog.000014 binlog.000015 binlog.000016 binlog.000017 binlog.000018 binlog.000019 binlog.000020 binlog.000021 binlog.000022 binlog.000023 binlog.000024 binlog.000025 binlog.000026 binlog.000027 binlog.000028 binlog.000029 binlog.000030 binlog.000031 binlog.000032 binlog.000033 binlog.000034 binlog.000035 binlog.000036 binlog.000037 binlog.000038 binlog.000039 >/opt/a.sql
或
mysqlbinlog binlog.00* >/opt/b.sql
实际中使用位置截取
2.binlog是记录所有库表的日志,需要截取一个库的日志,怎么办?
mysqlbinlog -d 库名
3.binlog是记录所有库表的日志,需要截取一个表的日志,怎么办?
1)关键字：表名
grep 表名 /tmp/bin.sql
2)方法2
binlog2sql(Python工具)
3)方法3:借助临时库恢复
a.导出所有表结构恢复到测试库
b.然后把所有binlog数据恢复到临时数据库.
c.然后导出要恢复的某个表数据，就相当于过滤了一个表的binlog。
4.从建库开始,binlog不会一直保留,每天全备后,全备时刻以前的binlog就可以删掉了.
binlog保存多久?
公司每日全备:binlog保留3-7天
公司每周全备:binlog保留7-15天
恢复完整数据条件:
某一个时刻全备+这一时刻以后到下一次全备之前的所有binlog,就可以恢复完整数据.
5.如何恢复binlog sql文件时候不记录binlog
恢复binlog解析后的sql文件时候,可以临时不记录binlog
mysql> set sql_log_bin=0; ##临时不记录binlog,是session级别.
mysql> source /tmp/bin.sql
mysql> set sql_log_bin=1; ##开启binlog.
6.位置点记录binlog问题:
位置是相对的,在每个文件里的位置是唯一的,如果换了新的binlog文件,
将来恢复可能是多个binlog文件,
或者多台mysql实例的binlog文件,每个文件当中的位置点都会重复.
位置点恢复不能跨越binlog文件.
有么有所有binlog文件都是唯一的,所有服务器的binlog文件每个位置都是唯一的?
答:5.6加的功能,GTID功能
9.6 GTID
9.6.1 介绍
GTID全称Global Transaction ID，全局事务ID，5.6开始起用的功能。
9.6.2 作用
1)在整个binlog中(所有主机的binlog文件)每行日志记录都是唯一ID值.
2)不管有多少个binlog文件,ID都是连续生成的.
3)具备幂等性(运行过的就不会在运行了).
9.6.3 特点:
1)长啥样
1ced0886-23d4-11eb-9768-000c29f4772b:1
server_uuid :NO.
2)server_uuid,唯一的标识一个实例的ID号
server_uuid是自动生成的
#查看
[root@mysql data]# cat /data/3306/data/auto.cnf
[auto]
server-uuid=6d233645-278c-11ee-a94b-000c29937eff
#如需改变它,删除/data/3306/data/auto.cnf后重启
9.6.4 参数配置
mysql> show variables like "%gtid_%";
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
# 该变量表示是否启用了简单的GTID恢复模式
| binlog_gtid_simple_recovery | ON |
# 该变量表示是否强制执行GTID一致性
| enforce_gtid_consistency | OFF |
# 已执行的GTID列表。在这里为空，表示没有已执行的GTID。
| gtid_executed | |
# GTID执行列表的压缩间隔，单位为秒。在这里值为0，表示没有启用压缩。
| gtid_executed_compression_period | 0 |
# GTID模式。在这里值为OFF，表示未启用GTID。
| gtid_mode | OFF |
# 下一个要执行的GTID。在这里值为AUTOMATIC，表示GTID由MySQL自动管理。
| gtid_next | AUTOMATIC |
# 拥有的GTID列表。在这里为空，表示没有拥有的GTID。
| gtid_owned | |
# 已清除的GTID列表。在这里为空，表示没有清除的GTID。
| gtid_purged | |
# 会话中是否跟踪GTID。在这里值为OFF，表示会话中未启用GTID跟踪。
| session_track_gtids | OFF |
+----------------------------------+-----------+
9 rows in set (0.00 sec)
set global gtid_mode=ON; #开启GTID记录日志格式开关
set global enforce_gtid_consistency=ON; #强制一致性。。
报错解决:
mysql> set global gtid_mode=ON;
ERROR 1788 (HY000): The value of @@GLOBAL.GTID_MODE can only be changed one step at a time: OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON. Also note that this value must be stepped up or down simultaneously on all servers. See the Manual for instructions.
mysql>
mysql> set global gtid_mode=OFF_PERMISSIVE;
mysql> set global gtid_mode=ON_PERMISSIVE;
mysql> set global enforce_gtid_consistency=ON;
mysql> set global gtid_mode=ON;
永久开:vim /etc/my.cnf
gtid_mode=ON
enforce_gtid_consistency=ON
9.6.5 练习
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000022 | 157 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> drop database m;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+---------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+----------------------------------------+
| binlog.000022 | 329 | | | 6d233645-278c-11ee-a94b-000c29937eff:1 |
+---------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)
测试:
create database oldboy2;
use oldboy2;
create table t1 (id int);
insert into t1 values(1);
insert into t1 values(2);
insert into t1 values(3);
drop table t1;
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000022 | 1843 | | | 6d233645-278c-11ee-a94b-000c29937eff:1-7 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> show binlog events in "binlog.000022";
+---------------+------+----------------+-----------+-------------+------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+------+----------------+-----------+-------------+------------------------------------------------------------------------+
| binlog.000022 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.30, Binlog ver: 4 |
| binlog.000022 | 126 | Previous_gtids | 1 | 157 | |
| binlog.000022 | 157 | Gtid | 1 | 234 | SET @@SESSION.GTID_NEXT= '6d233645-278c-11ee-a94b-000c29937eff:1' |
| binlog.000022 | 234 | Query | 1 | 329 | drop database m /* xid=352 */ |
| binlog.000022 | 329 | Gtid | 1 | 406 | SET @@SESSION.GTID_NEXT= '6d233645-278c-11ee-a94b-000c29937eff:2' |
| binlog.000022 | 406 | Query | 1 | 523 | create database oldboy2 /* xid=355 */ |
| binlog.000022 | 523 | Gtid | 1 | 600 | SET @@SESSION.GTID_NEXT= '6d233645-278c-11ee-a94b-000c29937eff:3' |
| binlog.000022 | 600 | Query | 1 | 718 | use `oldboy2`; create table t1 (id int) /* xid=360 */ |
| binlog.000022 | 718 | Gtid | 1 | 797 | SET @@SESSION.GTID_NEXT= '6d233645-278c-11ee-a94b-000c29937eff:4' |
| binlog.000022 | 797 | Query | 1 | 885 | BEGIN |
| binlog.000022 | 885 | Query | 1 | 992 | use `oldboy2`; insert into t1 values(1) |
| binlog.000022 | 992 | Xid | 1 | 1023 | COMMIT /* xid=361 */ |
| binlog.000022 | 1023 | Gtid | 1 | 1102 | SET @@SESSION.GTID_NEXT= '6d233645-278c-11ee-a94b-000c29937eff:5' |
| binlog.000022 | 1102 | Query | 1 | 1190 | BEGIN |
| binlog.000022 | 1190 | Query | 1 | 1297 | use `oldboy2`; insert into t1 values(2) |
| binlog.000022 | 1297 | Xid | 1 | 1328 | COMMIT /* xid=362 */ |
| binlog.000022 | 1328 | Gtid | 1 | 1407 | SET @@SESSION.GTID_NEXT= '6d233645-278c-11ee-a94b-000c29937eff:6' |
| binlog.000022 | 1407 | Query | 1 | 1495 | BEGIN |
| binlog.000022 | 1495 | Query | 1 | 1602 | use `oldboy2`; insert into t1 values(3) |
| binlog.000022 | 1602 | Xid | 1 | 1633 | COMMIT /* xid=363 */ |
| binlog.000022 | 1633 | Gtid | 1 | 1710 | SET @@SESSION.GTID_NEXT= '6d233645-278c-11ee-a94b-000c29937eff:7' |
| binlog.000022 | 1710 | Query | 1 | 1843 | use `oldboy2`; DROP TABLE `t1` /* generated by server */ /* xid=364 */ |
+---------------+------+----------------+-----------+-------------+------------------------------------------------------------------------+
22 rows in set (0.00 sec)
9.6.6 基于GTID模式截取binlog日志
截取1-6:6个事务
--skip-gtids ##忽略GTID
--include-gtids ##包含GTID
--exclude-gtids ##排除GTID
#取2-6,然后对比上面结果看.
[root@mysql data]# mysqlbinlog --skip-gtids --include-gtids="6d233645-278c-11ee-a94b-000c29937eff:2-6" binlog.000022|egrep -v "/\*|#"
BINLOG'
Sl3HZA8BAAAAegAAAH4AAAABAAQAOC4wLjMwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYgAEGggAAAAICAgCAAAACgoKKioAEjQA
CigAAcqXPc0=
create database oldboy2
create table t1 (id int)
BEGIN
insert into t1 values(1)
BEGIN
insert into t1 values(2)
BEGIN
insert into t1 values(3)
DELIMITER ;'
#恢复1-6,排除1
mysqlbinlog --skip-gtids --include-gtids="6d233645-278c-11ee-a94b-000c29937eff:1-6" --exclude-gtids="6d233645-278c-11ee-a94b-000c29937eff:1" binlog.000022
注意:如果需要截取的日志需要在原库恢复,过滤时需要加--skip-gtids参数,跳过导出文件gtid的记录.
GTID:全局唯一事务ID:GTID幂等性
1.binlog日志的SQL语句运行过了,通过binlog恢复,会有GTID标识,在执行就不让执行了.
2.原库恢复,过滤时需要加--skip-gtids参数,去SQL语句GTID标识,这样过滤的BINLOG对应的SQL语句就可以执行了.
测试对比例子:
[root@mysql data]# mysqlbinlog --include-gtids="6d233645-278c-11ee-a94b-000c29937eff:2-6" binlog.000022 > /opt/a.sql
[root@mysql data]# mysqlbinlog --skip-gtids --include-gtids="6d233645-278c-11ee-a94b-000c29937eff:2-6" binlog.000022 > /opt/b.sql
vimdiff /opt/b.sql /opt/a.sql #发现b少了GTID标识语句.
9.6.5 binlog日志切割：
1.为什么需要日志切割？
1.太大。
2.分析不方便
3.传输到从库，备份。
2.日志切换文件的触发机制:4种. 面试题：
1)mysql内部命令刷新,启动新binlog记录日志
mysql> flush logs;
2)命令行mysqladmin刷新
[root@oldboy data]# mysqladmin -uroot -poldboy123 flush-logs
3）文件大小达到1G,自动切割
mysql> select @@max_binlog_size;
+-------------------+
| @@max_binlog_size |
+-------------------+
| 1073741824 |
+-------------------+
1 row in set (0.00 sec)
4）重启数据库
9.6.5 binlog日志删除
1.为什么要删除binlog日志?
答:太大,占空间,而且一旦有每日全备份,备份时刻前的binlog就无用了.
2.什么删除策略？
每天/每周/每月,保留3天/7天??
生产binlog删除策略:
1)取决于数据库的备份频率:
每天全备一次，理论只留一天binlog日志就够了,此时留建议3-7天。
每周备份一次，理论只留7天binlog日志就够了,建议7-15天。
GB每日备份,TB级别每周备份,PB,EB数据甚至一个月一次全备,binlog也要备份。
中小企业按天全备,30-60GB级别,binlog日志留3-7天.
2.删除方法
a.方法1:数据库自动删除,查看设置
mysql> show variables like '%_logs_%';
+-------------------------------+---------+
| Variable_name | Value |
+-------------------------------+---------+
| binlog_expire_logs_auto_purge | ON |
| binlog_expire_logs_seconds | 2592000 | ##默认30天，8.0新参数
| expire_logs_days | 0 | ##8.0以前老的参数8.0,当下可能不生效
+-------------------------------+---------+
3 rows in set (0.00 sec)
#5.6
mysql> show variables like '%_logs_%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 0 |
+------------------+-------+
1 row in set (0.00 sec)
binlog_expire_logs_seconds=2592000 ###30天过期，默认值是一个月.
binlog_expire_logs_seconds=3600*24*7 ###7天过期
mysql> select 3600*24*7;
+-----------+
| 3600*24*7 |
+-----------+
| 604800 |
+-----------+
b.改为7天,配置方法
临时:
mysql> set global binlog_expire_logs_seconds=604800;
永久:
[root@db01 ~]# vim /etc/my.cnf
#by oldboy weixin:oldboy0102
[mysqld]
binlog_expire_logs_seconds=604801
b.方法2:手工清理
#查询binlog
show BINARY LOGS;
##基于文件清理,删除05以前,不含05.
purge binary logs to 'binlog.000005';
##按时间清理
PURGE BINARY LOGS BEFORE '2022-09-21 00:00:00'; ###实际删除的是21日以前,不含21日
c.全部重置
reset master;
[root@db01 /data/3306/data]# ls binlog* -l
-rw-r----- 1 mysql mysql 156 7月 11 16:30 binlog.000001
-rw-r----- 1 mysql mysql 16 7月 11 16:30 binlog.index
问题:能用rm -f在binlog日志目录下删除么? 尽量不要..
如果非要删,最后进DB里去reset master,重新生成;
9.6.6 binlog如何备份?
1)单机如何备份binlog：
文件系统上层:
1.rsync定时每分钟(丢1分钟binlog)
2.rsync+sersync实时，基于文件系统。
3.mysqlbinlog --read-from-remote-server --raw --host=10.0.0.51 --port=3306 --user=repl --password=oldboy123 --stop-never binlog.000001 --result-file=/opt/
解释如下：
--read-from-remote-server：用于备份远程服务器的binlog。如果不指定该选项，则会查找本地的binlog。
--raw：binlog日志会以二进制格式存储在磁盘中，如果不指定该选项，则会以文本形式保存。
--user：复制的MySQL用户，只需要授予REPLICATION SLAVE权限。
--stop-never：mysqlbinlog可以只从远程服务器获取指定的几个binlog，也可将不断生成的binlog保存到本地。指定此选项，代表只要远程服务器不关闭或者连接未断开，mysqlbinlog就会不断的复制远程服务器上的binlog。
binlog.000001 ：代表从哪个binlog开始复制。
--result-file=/opt/ :输出到哪个路径下.
实践远程备份(--read-from-remote-server)：
A51数据库服务器用户和授权:
mysql> create user repl@'10.0.0.%' identified by 'oldboy123';
mysql> grant replication slave on *.* to repl@'10.0.0.%';
mysql> flush privileges;
在B机器上执行；
mysqlbinlog --read-from-remote-server --raw --host=10.0.0.51 --port=3306 --user=repl --password=oldboy123 --stop-never binlog.000001 --result-file=/opt/ &
A机器上测试写入：
drop database oldboy3;
create database oldboy3;
use oldboy3;
create table t1 (id int);
insert into t1 values(1);
insert into t1 values(2);
insert into t1 values(3);
drop table t1;
B机器上查看结果；
[root@db01 ~]# cd /opt/
[root@db01 opt]# ll
总用量 893380
-rw-r----- 1 root root 4142 4月 22 10:37 binlog.000001
[root@db01 opt]# mysqlbinlog binlog.000001|tail -5
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*/;
注意:当数据库重启的时候,下面的日志获取进程会被停止,需要重新指定从哪个文件开始备份
mysqlbinlog --read-from-remote-server --raw --host=10.0.0.51 --port=3306 --user=repl --password=oldboy123 --stop-never /data/3306/data/binlog.000002 --result-file=/opt/ &
2)主从复制
M---S
binlog日志自动复制到从库S,然后在从库可以记录BINLOG,同时把binlog解析出SQL,执行将数据放到数据库里.
运维4大职责；
1.数据不丢不泄露
2.服务7*24服务能力
3.用户体验，速度。
4.效能效率。
9.6.7 binlog备份实践
| 服务器 | ip | 角色 |
|---|---|---|
| mysql | 192.168.255.80 | mysql服务器 |
| mysql2 | 192.168.255.100 | 备份服务器 |
#部署mysql服务
rpm -qa | grep mariadb
yum remove mariadb-libs -y
yum install ncurses ncurses-devel libaio-devel openssl openssl-devel -y
useradd mysql -s /sbin/nologin -M
cd /opt
tar xf mysql-8.0.30-linux-glibc2.12-x86_64.tar.xz
mv /opt/mysql-8.0.30-linux-glibc2.12-x86_64 /usr/local/mysql
cat>/etc/my.cnf<<'EOF'
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
port=3306
socket=/tmp/mysql.sock
[client]
socket=/tmp/mysql.sock
EOF
chown mysql.mysql /etc/my.cnf
mkdir -p /data/3306/data
chown -R mysql.mysql /data
echo 'export PATH=/usr/local/mysql/bin:$PATH' >>/etc/profile
. /etc/profile
echo $PATH
/usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
cd /usr/local/mysql/support-files/
cp mysql.server /etc/init.d/mysqld
systemctl enable mysqld
systemctl start mysqld
netstat -lntup|grep 330
# 在mysql服务器创建远程用户
mysql> create user repl@'192.168.255.%' identified by '123'
mysql> grant replication slave on *.* to repl@'192.168.255.%';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
# 在mysql2上执行，之前一直连接不上是因为远端mysql服务器没有关闭iptables
[root@mysql2 ~]# ssh-keygen
[root@mysql2 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.255.80
[root@mysql2 ~]# ssh root@192.168.255.80
[root@mysql2 ~]# mysql -urepl -p -h192.168.255.80
[root@mysql2 opt]# mysqlbinlog --read-from-remote-server --raw --host=192.168.255.80 --port=3306 --user=repl --password=123 --stop-never binlog.000022 --result-file=/opt/ &
[root@mysql2 opt]# ll
总用量 585380
-rw-r-----. 1 root root 180 7月 31 17:13 1.000001
-rw-r-----. 1 root root 180 7月 31 17:13 1.000002
-rw-r-----. 1 root root 3041 7月 31 17:19 binlog.000022
-rw-r-----. 1 root root 220 7月 31 17:19 binlog.000023
-rw-r-----. 1 root root 944 7月 31 17:19 binlog.000024
-rw-r-----. 1 root root 220 7月 31 17:19 binlog.000025
-rw-r-----. 1 root root 197 7月 31 17:19 binlog.000026
# 在mysql进行写入数据
drop database oldboy3;
create database oldboy3;
use oldboy3;
create table t1 (id int);
insert into t1 values(1);
insert into t1 values(2);
insert into t1 values(3);
drop table t1;
# 在mysql2上进行查看
[root@mysql2 opt]# mysqlbinlog binlog.000026 | grep oldboy3
create database oldboy3
use `oldboy3`/*!*/;
#230731 17:22:10 server id 1 end_log_pos 794 CRC32 0xfe8ec5dd Table_map: `oldboy3`.`t1` mapped to number 90
#230731 17:22:10 server id 1 end_log_pos 1073 CRC32 0xa7da78e9 Table_map: `oldboy3`.`t1` mapped to number 90
#230731 17:22:10 server id 1 end_log_pos 1352 CRC32 0xb2cb7ab4 Table_map: `oldboy3`.`t1` mapped to number 90
# 把内容写入到/tmp/bin1.sql里
[root@mysql2 opt]# mysqlbinlog --skip-gtids --start-position=391 binlog.000026 >/tmp/bin1.sql
[root@mysql2 opt]# sed -i '/DROP TABLE `t1`/d' /tmp/bin1.sql
[root@mysql2 opt]# grep 'drop' /tmp/bin1.sq
[root@mysql2 opt]# mysql -urepl -p123 -h192.168.255.80 </tmp/bin1.sql
# mysql服务器上查看恢复的数据
mysql> show tables;
+-------------------+
| Tables_in_oldboy3 |
+-------------------+
| t1 |
+-------------------+
1 row in set (0.00 sec)
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.255.80:3306' (113)
是因为没有关闭iptables
systemctl stop iptables
ERROR: Got error reading packet from server: Cannot replicate anonymous transaction when @@GLOBAL.GTID_MODE = ON, at file ./binlog.000001, position 157.; the first event 'binlog.000001' at 4, the last event read from './binlog.000001' at 236, the last byte read from './binlog.000001' at 236.
[root@mysql2 opt]# cat /etc/my.cnf
[mysqld]
# 两边都把gtid给打开就行了，配置要一致
gtid_mode=ON
enforce_gtid_consistency=ON
9.8.10 MySQL日志管理篇面试题
1.5 MySQL日志管理篇面试题
1.5.1 错误日志功能，如何配置错误日志，如何查看?
1.5.2 如何配置二进制日志？
1.5.3 二进制日志如何查看事件？
1.5.4 二进制日志有哪些格式？有什么区别？你们公司用什么格式？
1.5.5 什么是双一标准？
1.5.6 什么是2PC？
1.5.7 你是如何截取二进制日志的？传统日志、GTID日志有什么不同点？
1.5.8 如何配置慢日志？
1.5.9 慢日志如何处理？慢日志在什么时候使用，如何用？
字节换算公式
1 字节（Byte） = 8 位（Bit）
1 千字节（KB） = 1024 字节（B）
1 兆字节（MB） = 1024 KB
1 吉字节（GB） = 1024 MB
1 太字节（TB） = 1024 GB
1 拍字节（PB） = 1024 TB
1 艾字节（EB） = 1024 PB
1 泽字节（ZB） = 1024 EB
1 尧字节（YB） = 1024 ZB
浙公网安备 33010602011771号