江苏龙网

  1. 主页 > 生活百科 > >

mysql日志介绍( 二 )

mysql日志

 

通过执行flush logs命令之后，再次查询binary log信息，发现已经使用了一个新的bin log文件了 。
flush logs命令的作用就是关闭当前使用的binary log，然后打开一个新的binary log文件，binlog文件的序号递增加1
查看mysql正在使用的日志文件 
Master [(none)]>show master status;+------------------+----------+--------------+------------------+-------------------+| File| Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000003 |154 ||||+------------------+----------+--------------+------------------+-------------------+Master [(none)]> 
file：当前MySQL正在使用的文件名
Position：最后一个事件的结束位置号
日志内容查看event查看 
Master [binlog]>show binlog events in 'mysql-bin.000003';+------------------+-----+----------------+-----------+-------------+----------------------------------------+| Log_name| Pos | Event_type| Server_id | End_log_pos | Info|+------------------+-----+----------------+-----------+-------------+----------------------------------------+| mysql-bin.000003 |4 | Format_desc|6 |123 | Server ver: 5.7.20-log, Binlog ver: 4|| mysql-bin.000003 | 123 | Previous_gtids |6 |154 ||| mysql-bin.000003 | 154 | Anonymous_Gtid |6 |219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'|| mysql-bin.000003 | 219 | Query|6 |319 | create database binlog|| mysql-bin.000003 | 319 | Anonymous_Gtid |6 |384 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'|| mysql-bin.000003 | 384 | Query|6 |486 | use `binlog`; create table t1 (id int) |+------------------+-----+----------------+-----------+-------------+----------------------------------------+Log_name：binlog文件名Pos：开始的position*****Event_type：事件类型Format_desc：格式描述，每一个日志文件的第一个事件，多用户没有意义，MySQL识别binlog必要信息Server_id：mysql服务号标识End_log_pos：事件的结束位置号 *****Info：事件内容*****补充:SHOW BINLOG EVENTS[IN 'log_name'][FROM pos][LIMIT [offset,] row_count][root@db01 binlog]# mysql -e "show binlog events in 'mysql-bin.000004'" |grep drop 
binlog文件内容详细查看mysqlbinlog /data/mysql/mysql-bin.000006mysqlbinlog --base64-output=decode-rows -vvv /data/binlog/mysql-bin.000003mysqlbinlog-d binlog /data/binlog/mysql-bin.000003[root@db01 binlog]# mysqlbinlog --start-datetime='2019-05-06 17:00:00' --stop-datetime='2019-05-06 17:01:00'/data/binlog/mysql-bin.000004 基于Position号进行日志截取 
核心就是找截取的起点和终点--start-position=321--stop-position=513 mysqlbinlog --start-position=219 --stop-position=1347 /data/binlog/mysql-bin.000003 >/tmp/bin.sql案例: 使用binlog日志进行数据恢复模拟:1. [(none)]>create database binlog charset utf8;2. [(none)]>use binlog;[binlog]>create table t1(id int);3. [binlog]>insert into t1 values(1);[binlog]>commit;[binlog]>insert into t1 values(2);[binlog]>commit;[binlog]>insert into t1 values(3);[binlog]>commit;4. [binlog]>drop database binlog;恢复:[(none)]>show master status ;[(none)]>show binlog events in 'mysql-bin.000004';[root@db01 binlog]# mysqlbinlog --start-position=1227 --stop-position=2342 /data/binlog/mysql-bin.000004 >/tmp/bin.sql[(none)]>set sql_Log_bin=0;#设为0后，在Master数据库上执行的语句临时都不记录binlog，退出窗口可以恢复[(none)]>source /tmp/bin.sql 
-----上述通过pos位置恢复 需要关闭gitd----经过测试发现 开起来gitd，通过pos位置无法恢复数据
mysql日志介绍

文章插图
 

mysql日志介绍

文章插图
 
mysqlbinlog --start-position=219 --stop-position=1012 /data/binlog/3306/mysql-bin.000002 >/tmp/bin.sqlbinlog日志的GTID新特性GTID 介绍
5.6 版本新加的特性,5.7中做了加强
5.6 中不开启,没有这个功能.
5.7 中的GTID,即使不开也会有自动生成
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
GTID(Global Transaction ID)
是对于一个已提交事务的编号，并且是一个全局唯一的编号 。
它的官方定义如下：
GTID = source_id ：transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29
重要参数介绍：
【mysql日志介绍】 
vim /etc/my.cnfgtid-mode=onenforce-gtid-consistency=truesystemctl restart mysqldMaster [(none)]>create database gtid charset utf8;Query OK, 1 row affected (0.01 sec)Master [(none)]>show master status ;+------------------+----------+--------------+------------------+----------------------------------------+| File| Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set|+------------------+----------+--------------+------------------+----------------------------------------+| mysql-bin.000004 |326 ||| dff98809-55c3-11e9-a58b-000c2928f5dd:1 |+------------------+----------+--------------+------------------+----------------------------------------+1 row in set (0.00 sec)Master [(none)]>use gtidDatabase changedMaster [gtid]>create table t1 (id int);Query OK, 0 rows affected (0.01 sec)Master [gtid]>show master status ;+------------------+----------+--------------+------------------+------------------------------------------+| File| Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set|+------------------+----------+--------------+------------------+------------------------------------------+| mysql-bin.000004 |489 ||| dff98809-55c3-11e9-a58b-000c2928f5dd:1-2 |+------------------+----------+--------------+------------------+------------------------------------------+1 row in set (0.00 sec)Master [gtid]>create table t2 (id int);Query OK, 0 rows affected (0.01 sec)Master [gtid]>create table t3 (id int);Query OK, 0 rows affected (0.02 sec)Master [gtid]>show master status ;+------------------+----------+--------------+------------------+------------------------------------------+| File| Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set|+------------------+----------+--------------+------------------+------------------------------------------+| mysql-bin.000004 |815 ||| dff98809-55c3-11e9-a58b-000c2928f5dd:1-4 |+------------------+----------+--------------+------------------+------------------------------------------+1 row in set (0.00 sec)Master [gtid]>begin;Query OK, 0 rows affected (0.00 sec)Master [gtid]>insert into t1 values(1);Query OK, 1 row affected (0.00 sec)Master [gtid]>commit;Query OK, 0 rows affected (0.00 sec)Master [gtid]>show master status ;+------------------+----------+--------------+------------------+------------------------------------------+| File| Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set|+------------------+----------+--------------+------------------+------------------------------------------+| mysql-bin.000004 |1068 ||| dff98809-55c3-11e9-a58b-000c2928f5dd:1-5 |+------------------+----------+--------------+------------------+------------------------------------------+1 row in set (0.00 sec)Master [gtid]>begin;Query OK, 0 rows affected (0.00 sec)Master [gtid]>insert into t2 values(1);Query OK, 1 row affected (0.00 sec)Master [gtid]>commit;Query OK, 0 rows affected (0.01 sec)Master [gtid]>show master status ;+------------------+----------+--------------+------------------+------------------------------------------+| File| Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set|+------------------+----------+--------------+------------------+------------------------------------------+| mysql-bin.000004 |1321 ||| dff98809-55c3-11e9-a58b-000c2928f5dd:1-6 |+------------------+----------+--------------+------------------+------------------------------------------+1 row in set (0.00 sec)


推荐阅读


上一篇：element乘风破浪

下一篇：如何使用 Web3.js 库构建 Web3 登录功能