mysql常见增量恢复方式_mysql的数据增量恢复

mysql的日志机制

mysql具有日志机制，以保证数据库的数据安全。常见的主要有两种，redo log和binlog。binlog是mysql本身具备的备份机制，为逻辑日志，会记录每一次操作的数据行前后变化，或者是每一次操作的sql。而redo log为物理日志，会记录每一次操作的具体描述；

redo log是InnoDB引擎的插件，并且日志是循环记录的，如果采用其他的引擎，就不会有这个日志。redo log日志能够保证事务的完成。也就是当mysql当掉的时候，保证提交事务在下一次重启之后依然会正确完成；

binlog是归档机制，会依次记录数据库的所有操作，因此当误删数据的时候，能够依靠binlog实现数据的恢复。备份频率高的话，消耗资源大，但是当出现数据丢失的时候，恢复速度快。

开启mysql的binlog机制

binlog比较占用磁盘空间，所有binlog不一定默认打开了，我使用的MySQL容器里面，binlog是打开的，可能是制作容器的前辈已经配置好了。在我mac上的MySQL默认是没有打开的。在打开MySQL之后，在命令行可以查看binlog是否开启。

显示log_bin_ 开头的变量值。

mysql> show variables like ‘log_bin%’;

+———————————+————————————–+

| Variable_name | Value |

+———————————+————————————–+

| log_bin | ON |

| log_bin_basename | /usr/local/var/mysql/mysql-bin |

| log_bin_index | /usr/local/var/mysql/mysql-bin.index |

| log_bin_trust_function_creators | OFF |

| log_bin_use_v1_row_events | OFF |

+———————————+————————————–+

5 rows in set (0.01 sec)

在这里是ON表示binlog已经开启了，如果是OFF的话，需要修改配置文件，并重启mysql服务。

显示MySQL配置文件的位置，

➜ ~ mysql –help –verbose | grep my.cnf

order of preference, my.cnf, $MYSQL_TCP_PORT,

/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnfsudo /usr/local/MySQL/support-files/mysql.server restart

/etc/my.cnf默认可能没有，可以自己创建一下。

#vim /etc/my.cnf

[mysqld]

log-bin = mysql-bin #开启binlog

binlog-format = ROW #选择row模式

server_id = 1 #配置mysql replication需要定义，不能喝canal的slaveId重复

重启MySQL

sudo /usr/local/MySQL/support-files/mysql.server restart

进入MySQL后，查看binlog信息：

mysql> show master logs;

+——————+———–+

| Log_name | File_size |

+——————+———–+

| mysql-bin.000001 | 1600 |

| mysql-bin.000002 | 1411 |

| mysql-bin.000003 | 6827 |

| mysql-bin.000004 | 201 |

| mysql-bin.000005 | 601 |

| mysql-bin.000006 | 1548 |

| mysql-bin.000007 | 201 |

| mysql-bin.000008 | 4045 |

| mysql-bin.000009 | 2186 |

+——————+———–+

9 rows in set (0.00 sec)

查找mysql-bin. 000001的存放位置

➜ ~ sudo find / -name mysql-bin.000001

/usr/local/var/mysql/mysql-bin.000001

/usr/local/mysql-5.7.18-macos10.12-x86_64/data/mysql-bin.000001

我的存放地址其实就是' /usr/local/var/mysql/mysql-bin.000001 第二个地址暂时不太清楚，之后在查一查。两个目录详细信息如下：

➜ ~ sudo ls -lsh /usr/local/mysql-5.7.18-macos10.12-x86_64/data/

total 221248

16 -rw-r—– 1 _mysql _mysql 6.9K May 18 2017 MacdeMacBook-Pro.local.err

8 -rw-r—– 1 _mysql _mysql 56B Apr 26 2017 auto.cnf

16 -rw-r—– 1 _mysql _mysql 6.9K Feb 28 16:13 bogon.err

8 -rw-r—– 1 _mysql _mysql 300B Feb 28 16:13 ib_buffer_pool

98304 -rw-r—– 1 _mysql _mysql 48M Feb 28 16:13 ib_logfile0

98304 -rw-r—– 1 _mysql _mysql 48M Apr 26 2017 ib_logfile1

24576 -rw-r—– 1 _mysql _mysql 12M Feb 28 16:13 ibdata1

0 drwxr-x— 77 _mysql _mysql 2.4K Apr 26 2017 mysql

8 -rw-r—– 1 _mysql _mysql 177B Feb 28 16:13 mysql-bin.000001

8 -rw-r—– 1 _mysql _mysql 19B Feb 28 16:13 mysql-bin.index

0 drwxr-x— 90 _mysql _mysql 2.8K Apr 26 2017 performance_schema

0 drwxr-x— 108 _mysql _mysql 3.4K Apr 26 2017 sys

➜ ~ sudo ls -lh /usr/local/var/mysql

total 385352

-rw-r—– 1 liu admin 13K Oct 31 15:39 JevadedeMBP.lan.err

-rw-r—– 1 liu admin 4B Oct 29 19:39 JevadedeMBP.lan.pid

-rw-r—– 1 liu admin 358K Feb 25 14:48 JevadedeMacBook-Pro.local.err

-rw-r—– 1 liu admin 1.0M Dec 21 2017 MacdeMacBook-Pro-2.local.err

-rw-r—– 1 liu admin 4B Dec 16 2017 MacdeMacBook-Pro-2.local.pid

-rw-r—– 1 liu admin 728K Jul 18 2017 MacdeMacBook-Pro.local.err’’ mysqldump -uroot -p123456 -lF –log-error=/Users/liu/myDump.err -B T > ~/BAK.T.sql

。。。。。。。

数据库恢复

这里简单操作一下，看看怎么样进行数据的恢复操作。备份数据库

可以设置数据库的自动备份，并且每次备份之后重新在一个新的binlog文件中写日志，此处手动备份一下。

mysqldump -uroot -p123456 -lF –log-error=/Users/liu/myDump.err -B T > ~/BAK.T.sql

进入mysql中，进行数据库的相关操作，增删改，然后模拟误删数据库。

mysql> select * from T;

+—-+——+

| ID | c |

+—-+——+

| 0 | 1 |

| 1 | 2 |

| 2 | 3 |

| 4 | 1 |

| 6 | 3 |

| 7 | 1 |

| 8 | 202 |

| 9 | 203 |

| 10 | 3 |

| 11 | 82 |

| 12 | 1 |

+—-+——+

11 rows in set (0.00 sec)

mysql> insert into T values(3,1),(5,2),(13,3);

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

查看当前数据库状态：

mysql> select * from T.T;

+—-+——+

| ID | c |

+—-+——+

| 0 | 1 |

| 1 | 2 |

| 2 | 3 |

| 3 | 1 |

| 4 | 1 |

| 5 | 2 |

| 6 | 3 |

| 7 | 1 |

| 8 | 202 |

| 9 | 203 |

| 10 | 3 |

| 11 | 82 |

| 12 | 1 |

| 13 | 3 |

+—-+——+

14 rows in set (0.00 sec)

mysql> drop database T;

Query OK, 1 row affected (0.01 sec)

mysql> show databases;

+——————–+

| Database |

+——————–+

| information_schema |

| awesome |

| comacserver |

| db_apiserver |

| django |

| goadmin |

| mxonline |

| mysql |

| performance_schema |

| sys |

| vps_information |

| xixi |

+——————–+

12 rows in set (0.01 sec)

在命令行备份binlog文件：

mkdir ~/temp

cp /usr/local/var/mysql/mysql-bin.00000* ~/temp/

恢复数据库到备份时刻的状态

mysql -uroot -p123456 -v < BAK.T.sql;

mysql> show databases;

+——————–+

| Database |

+——————–+

| information_schema |

| T |

| awesome |

| comacserver |

| db_apiserver |

| django |

| goadmin |

| mxonline |

| mysql |

| performance_schema |

| sys |

| vps_information |

| xixi |

+——————–+

13 rows in set (0.00 sec)

mysql> select * from T;

+—-+——+

| ID | c |

+—-+——+

| 0 | 1 |

| 1 | 2 |

| 2 | 3 |

| 4 | 1 |

| 6 | 3 |

| 7 | 1 |

| 8 | 202 |

| 9 | 203 |

| 10 | 3 |

| 11 | 82 |

| 12 | 1 |

+—-+——+

11 rows in set (0.00 sec)将binlog文件转化成sql

mysqlbinlog –no-defaults ~/temp/mysql-bin.000009 > . ~/temp/09.sql

打开sql文件删除drop database T 行以及之后所有行的内容。

vim .~/temp/09.sql

恢复增量数据

mysql -u root -p < ~/temp 09.sql

mysql> select * from T.T;

+—-+——+

| ID | c |

+—-+——+

| 0 | 1 |

| 1 | 2 |

| 2 | 3 |

| 3 | 1 |

| 4 | 1 |

| 5 | 2 |

| 6 | 3 |

| 7 | 1 |

| 8 | 202 |

| 9 | 203 |

| 10 | 3 |

| 11 | 82 |

| 12 | 1 |

| 13 | 3 |

+—-+——+

14 rows in set (0.00 sec)

至此数据库就恢复完成了。但是只能恢复到误删数据的时刻，误删之后丢失的数据是无法恢复，这就需要在相应的软件中有相应的机制，以应对数据库误删情况的数据的持久化问题。