mysql binlog
vi my.cnf
log-bin = mysql-bin
binlog_format = row
expire_logs_days=7
max_binlog_size=500m
binlog_cache_size=4m
max_binlog_cache_size=256m
全备：
基础数据
create database mydb;
create table t1 (id int primary key auto_increment,name varchar(32));
insert into t1 (name) values ('mysql'),('oracle'),('postgres'),('sql server');
全备：
mysqldump --master-data=2 --single-transaction mydb >mydb.sql
后续数据DML 1:
delete from t1 where id=4;
insert into t1 (name) values ('redis');
mysql> select * from t1;
+----+----------+
| id | name |
+----+----------+
| 1 | mysql |
| 2 | oracle |
| 3 | postgres |
| 5 | redis |
+----+----------+
误操作：
truncate table t1;
后续数据DML 2:
insert into t1 (name) values ('mongodb');
mysql> select * from t1;
+----+---------+
| id | name |
+----+---------+
| 1 | mongodb |
+----+---------+
mongodb数据己在原表，占用id=1，是从1开始的，恢复好数据，要进行整合
恢复到truncate之前的数据
恢复过程：
1.查看全备的position位置
more mydb.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=595;
从595位置开始查找binlog
2.根据全备的位置查看binlog
mysqlbinlog -v -v mysql-bin.000002 >/tmp/1.sql
vi /tmp/1.sql
:/595
:/truncate
# at 994
#151229 5:41:15 server id 1 end_log_pos 1078 CRC32 0x60d11463 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1451338875/*!*/;
truncate table t1
mysqlbinlog -v -v --start-position=595 --stop-position=994 mysql-bin.000002 >/tmp/2.sql
3.恢复数据
mysql mydb
alter table t1 rename to t1_bak;
create table t1 like t1_bak;
mysqlbinlog --start-position=1150 mysql-bin.000002 |mysql mydb 跳过truncate后续的恢复
4.新恢复出来的数据与线上整合
update t1 set id=6 where id=1;
insert into t1_bak select * from t1;
alter table t1 rename to t1_2;
alter table t1_bak rename to t1;
select * from t1;