mysql的数据增量恢复

2019-02-28

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)

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

点击查看评论

mysql的数据增量恢复

mysql的日志机制

开启mysql的binlog机制

数据库恢复

所有文章