mysqldump全备份及结合binlog日志恢复的全过程 (转)

来源：互联网发布：风水织梦网站源码编辑：程序博客网时间：2024/06/05 11:06

数据库的使用情况：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
5 rows in set (0.00 sec)

mysql> use testdb;
Database changed
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| student_t |
+------------------+
1 row in set (0.00 sec)

mysql> select * from testdb.student_t;
+--------+----------+------------+
| id | name | time |
+--------+----------+------------+
| 12 | anzhen | 2017-07-25 |
| 1 | dsanzhen | 2017-07-25 |
| 18 | sfsdf | 2017-07-25 |
| 18 | s899 | 2017-07-25 |
| 134 | s899 | 2017-07-25 |
| 111 | 111 | 2017-07-25 |
| 444223 | 111 | 2017-07-25 |
| 3333 | 333 | 2017-07-25 |
+--------+----------+------------+
8 rows in set (0.00 sec)

#全备份时间为2017 Jul 25 20:46
[root@localhost104 mysql]#/usr/local/mysql/bin/mysqldump -uroot --dump-date --port=3306 --lock-all-tables --events --routines --triggers --master-data=2 --flush-logs --all-databases -p >/tmp/full.sql
[root@localhost104 mysql]# ls -all /tmp/mysqlfull_20170725.sql
-rw-r--r-- 1 root root 778018 Jul 25 20:46 /tmp/mysqlfull_20170725.sql

#全备份之后，新增的数据并且刷新了日志文件
mysql> create table testdb.add_data1(id int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into testdb.add_data1 values(1),(2),(3);
Query OK, 3 rows affected (0.09 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from testdb.add_data1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql_bin.000402 | 1124 |
| mysql_bin.000403 | 1132 |
| mysql_bin.000404 | 1628 |
| mysql_bin.000405 | 486 |
| mysql_bin.000406 | 769 |
| mysql_bin.000407 | 1241 |
| mysql_bin.000408 | 201 |
| mysql_bin.000409 | 655 |
| mysql_bin.000410 | 154 |
+------------------+-----------+
41 rows in set (0.00 sec)

继续插入数据：
mysql> insert into testdb.add_data1 values(8585),(22463),(242);
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql_bin.000402 | 1124 |
| mysql_bin.000403 | 1132 |
| mysql_bin.000404 | 1628 |
| mysql_bin.000405 | 486 |
| mysql_bin.000406 | 769 |
| mysql_bin.000407 | 1241 |
| mysql_bin.000408 | 201 |
| mysql_bin.000409 | 655 |
| mysql_bin.000410 | 428 |
+------------------+-----------+
41 rows in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.04 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql_bin.000402 | 1124 |
| mysql_bin.000403 | 1132 |
| mysql_bin.000404 | 1628 |
| mysql_bin.000405 | 486 |
| mysql_bin.000406 | 769 |
| mysql_bin.000407 | 1241 |
| mysql_bin.000408 | 201 |
| mysql_bin.000409 | 655 |
| mysql_bin.000410 | 475 |
| mysql_bin.000411 | 154 |
+------------------+-----------+
42 rows in set (0.00 sec)
日志文件增加一个。

误操作：update忘记带where条件了。误操作的时间大概是:2017-07-25 21:02:00
mysql> select * from testdb.student_t;
+--------+----------+------------+
| id | name | time |
+--------+----------+------------+
| 12 | anzhen | 2017-07-25 |
| 1 | dsanzhen | 2017-07-25 |
| 18 | sfsdf | 2017-07-25 |
| 18 | s899 | 2017-07-25 |
| 134 | s899 | 2017-07-25 |
| 111 | 111 | 2017-07-25 |
| 444223 | 111 | 2017-07-25 |
| 3333 | 333 | 2017-07-25 |
+--------+----------+------------+
8 rows in set (0.00 sec)

mysql>
mysql>
mysql> update testdb.student_t set name='m1';
Query OK, 8 rows affected (0.01 sec)
Rows matched: 8 Changed: 8 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from testdb.student_t;
+--------+------+------------+
| id | name | time |
+--------+------+------------+
| 12 | m1 | 2017-07-25 |
| 1 | m1 | 2017-07-25 |
| 18 | m1 | 2017-07-25 |
| 18 | m1 | 2017-07-25 |
| 134 | m1 | 2017-07-25 |
| 111 | m1 | 2017-07-25 |
| 444223 | m1 | 2017-07-25 |
| 3333 | m1 | 2017-07-25 |
+--------+------+------------+
8 rows in set (0.00 sec)

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2017-07-25 21:02:00 |
+---------------------+
1 row in set (0.00 sec)

其他同事新增正常的数据。
mysql> create table testdb.add_data2(id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into testdb.add_data2 values(234),(345),(1231),(235),(241);
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from testdb.add_data2;
+------+
| id |
+------+
| 234 |
| 345 |
| 1231 |
| 235 |
| 241 |
+------+

5 rows in set (0.00 sec)

此时需要关闭session级别的二进制日志使得，恢复内容不记录日志
mysql> SET SESSION sql_log_bin='OFF';

选择数据出问题时间之前最近的备份集恢复在一个新实例（restorebak.pl恢复工具支持，操作方便）
将备份集POS与数据出问题POS之间的二进制日志拷贝到新实例上（拷贝二进制日志操作繁琐）
恢复的过程：采用全备份+binlog的增量日志文件恢复到最新的的数据。
（注意：如果是生产环境不能直接就覆盖生产环境的数据。）

（1）利用最近全备份恢复数据库,最近的备份集恢复在一个新实例。新建一个MySQL实列。（注意：如果是生产环境不能直接就覆盖生产环境的数据。）
查看全备份的生成时间：也就是说全备份可以恢复大概到2017 Jul 25 20:46的数据
[root@localhost104 mysql]# ls -all /tmp/mysqlfull_20170725.sql
-rw-r--r-- 1 root root 778018 Jul 25 20:46 /tmp/mysqlfull_20170725.sql

#恢复最近的全备份（一定要最近的，如果用很旧的全备份，这样binlog的日志可以不全，达不到恢复最新的恢复）
[root@localhost104 mysql]# /usr/local/mysql/bin/mysql -uroot -p < /tmp/mysqlfull_20170725.sql
Enter password:

（2）查看全备份文件的恢复情况，是否把以前2017 Jul 25 20:46的数据的数据恢复好了。

此时需要session级别的二进制日志使得，恢复记录日志

mysql> SET SESSION sql_log_bin='ON';

mysql> show databases;

+--------------------+

| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
5 rows in set (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| student_t |
+------------------+
1 row in set (0.00 sec)
只有恢复全备份的数据

mysql> select * from testdb.student_t;
+--------+----------+------------+
| id | name | time |
+--------+----------+------------+
| 12 | anzhen | 2017-07-25 |
| 1 | dsanzhen | 2017-07-25 |
| 18 | sfsdf | 2017-07-25 |
| 18 | s899 | 2017-07-25 |
| 134 | s899 | 2017-07-25 |
| 111 | 111 | 2017-07-25 |
| 444223 | 111 | 2017-07-25 |
| 3333 | 333 | 2017-07-25 |
+--------+----------+------------+
8 rows in set (0.01 sec)

（3）现在要恢复全备份之后到误操作之前的数据。怎么样确定开始恢复的位置和停止的正确位置呢？
这个是恢复关键的所在。

[root@localhost104 mysql]# vi /tmp/mysqlfull_20170725.sql
全备份文件前面几行有以下的信息。因为全备份的时候用了这个参数--master-data，告诉我们从这个二进制文件开始恢复全备份之后的
的数据，位置为154
- CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin.000409', MASTER_LOG_POS=154;
因此开始位置为154。

怎么样确定误操作的命令的开始和结束位置号呢。依据大概误操作的时间2017-07-25 21:02:00，可以大概判断二进制文件。
在二进制目录参看：
-rw-r----- 1 mysql mysql 769 Jul 25 20:27 mysql_bin.000406
-rw-r----- 1 mysql mysql 1241 Jul 25 20:45 mysql_bin.000407
-rw-r----- 1 mysql mysql 201 Jul 25 20:46 mysql_bin.000408
-rw-r----- 1 mysql mysql 655 Jul 25 20:52 mysql_bin.000409
-rw-r----- 1 mysql mysql 475 Jul 25 20:53 mysql_bin.000410
-rw-r----- 1 mysql mysql 2288112 Jul 25 21:53 mysql_bin.000411
-rw-r----- 1 mysql mysql 154 Jul 25 21:53 mysql_bin.000412
-rw-r----- 1 mysql mysql 1677 Jul 25 21:53 mysql_bin.index
大概判断误操作是在mysql_bin.000410或者mysql_bin.000410这个二进制文件里面。只看错误大概前后5分钟左右的操作，看有没有误操作的记录。找到为止
[root@localhost104 mysql]# bin/mysqlbinlog -uroot --start-datetime='2017-07-25 20:58:00' --stop-datetime='2017-07-25 21:07:00' /usr/local/mysql/data/mysql_bin.000411 -p > /tmp/411binlog.sql
Enter password:
[root@localhost104 mysql]#
[root@localhost104 mysql]#
[root@localhost104 mysql]# vi /tmp/411binlog.sql
# at 293
#170725 21:01:25 server id 10 end_log_pos 351 CRC32 0x73dd9604 Table_map: `testdb`.`student_t` mapped to number 340
# at 351
#170725 21:01:25 server id 10 end_log_pos 583 CRC32 0x30b8f6c2 Update_rows: table id 340 flags: STMT_END_F

BINLOG '
JUF3WRMKAAAAOgAAAF8BAAAAAFQBAAAAAAEABnRlc3RkYgAJc3R1ZGVudF90AAMDDwoCCgAHBJbd
cw==
JUF3WR8KAAAA6AAAAEcCAAAAAFQBAAAAAAEAAgAD///4DAAAAAZhbnpoZW75wg/4DAAAAAJtMfnC
D/gBAAAACGRzYW56aGVu+cIP+AEAAAACbTH5wg/4EgAAAAVzZnNkZvnCD/gSAAAAAm0x+cIP+BIA
AAAEczg5OfnCD/gSAAAAAm0x+cIP+IYAAAAEczg5OfnCD/iGAAAAAm0x+cIP+G8AAAADMTEx+cIP
+G8AAAACbTH5wg/4P8cGAAMxMTH5wg/4P8cGAAJtMfnCD/gFDQAAAzMzM/nCD/gFDQAAAm0x+cIP
wva4MA==
'/*!*/;
# at 583
#170725 21:01:25 server id 10 end_log_pos 614 CRC32 0x4002b392 Xid = 1789
COMMIT/*!*/;
# at 614
可以错误命令执行之前的位置为# at 293，误操作执行完成之后的位置为583.因此要跳过位置293-583的范围。

#start-position为全备份文件里面的位置和mysql_bin.000409也是全备份里面的二进制文件开始恢复，mysql_bin.000411为包含有误操作的文件，--stop-position为误操作命
令之前的的位置。
[root@localhost104 mysql]# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql_bin.000409 /usr/local/mysql/data/mysql_bin.000410 /usr/local/mysql/data/mysql_bin.000411 --start-position=154 --stop-position=293 | /usr/local/mysql/bin/mysql -uroot -p
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| add_data1 |
| student_t |
+------------------+
2 rows in set (0.00 sec)

#表testdb.add_data1为全备份到误操作之间的数据，已经恢复。该误操作的mysql> update testdb.student_t set name='m1';数据也恢
复了。如下表testdb.student_t 的数据name不全部m1的错误数据了。
mysql> select * from testdb.add_data1;
+-------+
| id |
+-------+
| 1 |
| 2 |
| 3 |
| 8585 |
| 22463 |
| 242 |
+-------+
6 rows in set (0.00 sec)
mysql> select * from student_t;
+--------+----------+------------+
| id | name | time |
+--------+----------+------------+
| 12 | anzhen | 2017-07-25 |
| 1 | dsanzhen | 2017-07-25 |
| 18 | sfsdf | 2017-07-25 |
| 18 | s899 | 2017-07-25 |
| 134 | s899 | 2017-07-25 |
| 111 | 111 | 2017-07-25 |
| 444223 | 111 | 2017-07-25 |
| 3333 | 333 | 2017-07-25 |
+--------+----------+------------+
8 rows in set (0.00 sec)

（4）需要跳过误操作的错误数据，恢复误操作之后的正确数据。只需要mysql_bin.000411和以后的二进制文件。

查看当前的二进制文件
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000412 | 2289326 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

刷新二进制文件，把最新的数据刷新到mysql_bin.000412文件里面，然后我们一起把mysql_bin.000412最新的数据恢复出来。
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000413 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

[root@localhost104 mysql]# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql_bin.000411 /usr/local/mysql/data/mysql_bin.000412 --start-position=583 --stop-position=1071 | /usr/local/mysql/bin/mysql -uroot -p
Enter password:

mysql> use testdb;
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
误操作之后新增的正确数据add_data2也恢复完成
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| add_data1 |
| add_data2 |
| student_t |
+------------------+
3 rows in set (0.01 sec)
mysql> select * from testdb.add_data1;
+-------+
| id |
+-------+
| 1 |
| 2 |
| 3 |
| 8585 |
| 22463 |
| 242 |
+-------+
6 rows in set (0.01 sec)

mysql> select * from testdb.add_data2;
+------+
| id |
+------+
| 234 |
| 345 |
| 1231 |
| 235 |
| 241 |
+------+
5 rows in set (0.00 sec)

mysql>
mysql>
mysql>
mysql> select * from testdb.student_t;
+--------+----------+------------+
| id | name | time |
+--------+----------+------------+
| 12 | anzhen | 2017-07-25 |
| 1 | dsanzhen | 2017-07-25 |
| 18 | sfsdf | 2017-07-25 |
| 18 | s899 | 2017-07-25 |
| 134 | s899 | 2017-07-25 |
| 111 | 111 | 2017-07-25 |
| 444223 | 111 | 2017-07-25 |
| 3333 | 333 | 2017-07-25 |
+--------+----------+------------+
8 rows in set (0.00 sec)

到现在为止数据已经恢复到最后一次flush logs时候的最新的状态了。

阅读全文

0 0