MySQL: 增量备份-binlog

22 Nov 2015

0、修改配置

vim /etc/my.cnf
log_bin=/data/mysql/bin_log
binlog-do-db=replication
binlog-ignore-db=replication2,replication3
## 开启binlog记录

1、完整备份

mysqldump -u root -p --all-databases --master-data > lastback.sql 
# --master-data的目地是保存binlog信息到sql备份文件中

2、查看最后一次完整备份

head -50 lastback.sql | grep 'CHANGE MASTER'
CHANGE MASTER TO MASTER_LOG_FILE='bin_log.000007', MASTER_LOG_POS=1236;
# 我们可以看到binlog文件，position位置
``` 

### 3、进去replication，删掉唯一的表replitab
``` bash
mysql> use replication
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> show tables;
+-----------------------+
| Tables_in_replication |
+-----------------------+
| replitab              |
+-----------------------+
1 row in set (0.00 sec)
 
mysql> create table replitab2 (
    -> id int(5),
    -> name varchar(10));
Query OK, 0 rows affected (0.01 sec)
 
mysql> drop table replitab;
Query OK, 0 rows affected (0.01 sec)
 
mysql> show tables;
+-----------------------+
| Tables_in_replication |
+-----------------------+
| replitab2             |
+-----------------------+
1 row in set (0.00 sec)

4、恢复最后一次完整备份

mysql -u root -p < lastback.sql
``` 

### 5、查看目前的binlog位置

mysql> show binary logs; +—————-+———–+ | Log_name | File_size | +—————-+———–+ | bin_log.000001 | 143 | | bin_log.000002 | 959 | | bin_log.000003 | 653 | | bin_log.000004 | 392 | | bin_log.000005 | 259 | | bin_log.000006 | 120 | | bin_log.000007 | 3878 | +—————-+———–+ 7 rows in set (0.00 sec)

mysql> show master status\G *************************** 1. row ***************************

         File: bin_log.000007
     Position: 3878
 Binlog_Do_DB: replication

Binlog_Ignore_DB: replication2,replication3 Executed_Gtid_Set: 1 row in set (0.00 sec)

 
### 6、根据lastback.sql备份中的position和上面的postion恢复

下面命令可以查看binlog文件的详情，找到需要恢复的时间、位置

mysqlbinlog /data/mysql/bin_log.000007 | grep -C 2 ‘DROP’ use replication/!/; SET TIMESTAMP=1448201487/!/; DROP TABLE IF EXISTS replitab /* generated by server / /!*/;

at 448

– SET @@session.foreign_key_checks=1, @@session.unique_checks=1/!/; SET @@session.sql_mode=1075838976/!/; DROP TABLE replitab /* generated by server / /!*/;

at 1371

– use replication/!/; SET TIMESTAMP=1448206056/!/; DROP TABLE IF EXISTS replitab /* generated by server / /!*/;

at 1699

– #151122 23:28:58 server id 1 end_log_pos 2762 CRC32 0xfe743fd2 Query thread_id=11 exec_time=0 error_code=0 SET TIMESTAMP=1448206138/!/; DROP TABLE replitab /* generated by server / /!*/;

at 2762

– use replication/!/; SET TIMESTAMP=1448206454/!/; DROP TABLE IF EXISTS replitab /* generated by server / /!*/;

at 3090

上面发现最后一次DROP掉replitab是在position 2762，那我们就恢复到2761

mysqlbinlog –stop-position=2761 /data/mysql/bin_log.000007 | mysql -u root -p

Enter password: ERROR 1050 (42S01) at line 120: Table ‘replitab2’ already exists

报错是因为MySQL根据binlog执行的时候根据binlog去创建replitab2，发现它已经存在，这个错误是因为我之前为了尝试备份命令多次操作的原因

### 7、检查效果

### 8、总结
问题：
- 在实验过程中，尝试了删掉表然后接着就开始了恢复进程，误操作是在2762位置，但是如果我们没有及时发现这个错误，当我们过了一个小时发现这个错误的时候，可能当前的position已经是4000多了。

操作：
- 利用上面的过程，先恢复到postion 2761，然后`mysqlbinlog --start-position=2763 /data/mysql/bin_log.000007 | mysql -u root -p`相当于从2763开始继续恢复，用先stop后start指定position的方式跳过故障点。

扩展：
- 同理我们也可以使用--start-date和--stop-date来跳过故障时间
> 官方文档：
> https://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html

定时备份binlog

既然我们需要做incremental备份，那最好是有规划的去处理binlog而不是让系统自动去分割binlog文件，这时我们就需要利用一个命令”flush”

ll /data/mysql/bin_log.0* -rw-rw—-. 1 mysql mysql 143 Nov 19 08:17 /data/mysql/bin_log.000001 -rw-rw—-. 1 mysql mysql 959 Nov 19 08:53 /data/mysql/bin_log.000002 -rw-rw—-. 1 mysql mysql 653 Nov 19 17:38 /data/mysql/bin_log.000003 -rw-rw—-. 1 mysql mysql 392 Nov 19 17:46 /data/mysql/bin_log.000004 -rw-rw—-. 1 mysql mysql 259 Nov 20 22:02 /data/mysql/bin_log.000005 -rw-rw—- 1 mysql mysql 120 Nov 22 21:02 /data/mysql/bin_log.000006 -rw-rw—- 1 mysql mysql 6290 Nov 23 01:14 /data/mysql/bin_log.000007

mysql -u root -p -e “flush logs” Enter password:

执行flush logs之后，系统自动创建新的binlog

”`

最新文章

文章分类