江苏龙网

  1. 主页 > 生活百科 > >

mysql日志介绍( 三 )

mysql日志

 
基于GTID进行查看binlog 
具备GTID后,截取查看某些事务日志:--include-gtids--exclude-gtidsmysqlbinlog --include-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:1-6' --exclude-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:4'/data/binlog/mysql-bin.000004跳过多个事务idmysqlbinlog --include-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:1-6' --exclude-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:4,dff98809-55c3-11e9-a58b-000c2928f5dd:5'/data/binlog/mysql-bin.000004 
GTID的幂等性 
开启GTID后,MySQL恢复Binlog时,重复GTID的事务不会再执行了就想恢复?怎么办?--skip-gtidsmysqlbinlog --skip-gtids --include-gtids='3ca79ab5-3e4d-11e9-a709-000c293b577e:4' /data/binlog/mysql-bin.000004 > /tmp/binlog.sqlset sql_log_bin=0;source /tmp/binlog.sqlset sql_log_bin=1; 
使用二进制日志恢复数据案例故障环境介绍 
创建了一个库db, 导入了表t1 ,t1表中录入了很多数据一个开发人员,drop database db;没有备份,日志都在.怎么恢复?思路:找到建库语句到删库之前所有的日志,进行恢复.(开启了GTID模式)故障案例模拟:(0) drop database if exists db ;(1) create database db charset utf8;(2) use db;(3) create table t1 (id int);(4) insert into t1 values(1),(2),(3);(5) insert into t1 values(4),(5),(6);(6) commit(7) update t1 set id=30 where id=3;(8) commit;(9) delete from t1 where id=4;(10)commit;(11)insert into t1 values(7),(8),(9);(12)commit;(13)drop database db;========================drop database if exists db ;create database db charset utf8; use db;create table t1 (id int);insert into t1 values(1),(2),(3);insert into t1 values(4),(5),(6);commit;update t1 set id=30 where id=3;commit;delete from t1 where id=4;commit;insert into t1 values(7),(8),(9);commit;drop database db;=======运行以上语句，模拟故障场景需求：将数据库恢复到以下状态（提示第9步和第13步是误操作，其他都是正常操作） 
恢复过程(无GTID时的恢复)1，查看当前使用的 binlog文件
 
oldguo [db]>show master status ;+------------------+----------+--------------+------------------+-------------------+| File| Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000006 |1873 ||||+------------------+----------+--------------+------------------+-------------------+2.查看事件：第一段：| mysql-bin.000006 |813 | Query|1 |907 | use `db`; create table t1 (id int)|| mysql-bin.000006 |907 | Query|1 |977 | BEGIN|| mysql-bin.000006 |977 | Table_map|1 |1020 | table_id: 77 (db.t1)|| mysql-bin.000006 | 1020 | Write_rows|1 |1070 | table_id: 77 flags: STMT_END_F|| mysql-bin.000006 | 1070 | Table_map|1 |1113 | table_id: 77 (db.t1)|| mysql-bin.000006 | 1113 | Write_rows|1 |1163 | table_id: 77 flags: STMT_END_F|| mysql-bin.000006 | 1163 | Xid|1 |1194 | COMMIT /* xid=74 */|| mysql-bin.000006 | 1194 | Query|1 |1264 | BEGIN|| mysql-bin.000006 | 1264 | Table_map|1 |1307 | table_id: 77 (db.t1)|| mysql-bin.000006 | 1307 | Update_rows |1 |1353 | table_id: 77 flags: STMT_END_F|| mysql-bin.000006 | 1353 | Xid|1 |1384 | COMMIT /* xid=77 */mysqlbinlog --start-position=813 --stop-position=1384 /data/mysql/mysql-bin.000006 >/tmp/bin1.sql  
第二段：
 
| mysql-bin.000006 | 1568 | Query|1 |1638 | BEGIN|| mysql-bin.000006 | 1638 | Table_map|1 |1681 | table_id: 77 (db.t1)|| mysql-bin.000006 | 1681 | Write_rows|1 |1731 | table_id: 77 flags: STMT_END_F|| mysql-bin.000006 | 1731 | Xid|1 |1762 | COMMIT /* xid=81 */ mysqlbinlog --start-position=1568 --stop-position=1762 /data/mysql/mysql-bin.000006 >/tmp/bin2.sql 
3，恢复
 
set sql_log_bin=0;source /tmp/bin1.sqlsource /tmp/bin2.sqlset sql_log_bin=1;oldguo [db]>select * from t1;+------+| id|+------+|1 ||2 ||30 ||4 ||5 ||6 ||7 ||8 ||9 | 
有GTID的恢复:mysqlbinlog --skip-gtids --include-gtids='3ca79ab5-3e4d-11e9-a709-000c293b577e:7-12' mysql-bin.000004> /tmp/bin.sql恢复
set sql_log_bin=0;source /tmp/bin.sql二进制日志其他操作自动清理日志 
show variables like '%expire%';expire_logs_days0自动清理时间,是要按照全备周期+1set global expire_logs_days=8;永久生效:修改配置文件vim my.cnfexpire_logs_days=15;企业建议,至少保留两个全备周期+1的binlog 
手工清理（危险） 
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;PURGE BINARY LOGS TO 'mysql-bin.000010';注意:不要手工 rm binlog文件1. my.cnf binlog关闭掉,启动数据库2.把数据库关闭,开启binlog,启动数据库删除所有binlog,并从000001开始重新记录日志 
reset master; 主从关系中，主库执行此操作，主从环境必崩


推荐阅读


上一篇：element乘风破浪

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