mysql備份恢復測試
1.建立測試表，並檢視當前日誌記錄位置：
mysql> create table t1(id int,name varchar(200)) engine=innodb;
Query OK, 0 rows affected (0.10 sec)
mysql> insert into t1 values(1,'lsq');
Query OK, 1 row affected (0.03 sec)
mysql> insert into t1 values(2,'ljk');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(3,'llj');
Query OK, 1 row affected (0.00 sec)
mysql> show variables like '%format%';
+--------------------------+-------------------+
| Variable_name | Value |
+--------------------------+-------------------+
| binlog_format | ROW |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| time_format | %H:%i:%s |
+--------------------------+-------------------+
8 rows in set (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 834 |
+------------------+-----------+
1 row in set (0.00 sec)
可以看出二進位制日誌裡最後的記錄時834，檢視日誌內容確認：
[root@mysqldb mysql]# mysqlbinlog mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#150827 6:52:29 server id 1 end_log_pos 120 CRC32 0x9407869e Start: binlog v 4, server v 5.6.23-enterprise-commercial-advanced-log created 150827 6:52:29 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
7eveVQ8BAAAAdAAAAHgAAAABAAQANS42LjIzLWVudGVycHJpc2UtY29tbWVyY2lhbC1hZHZhbmNl
ZC1sb2cAAAAAAAAAAADt695VEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAZ6G
B5Q=
'/*!*/;
# at 120
#150827 6:57:07 server id 1 end_log_pos 249 CRC32 0xc9890f80 Query thread_id=4 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1440673027/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table t1(id int,name varchar(200)) engine innodb
/*!*/;
# at 249
#150827 6:57:26 server id 1 end_log_pos 321 CRC32 0x67ff596c Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1440673046/*!*/;
BEGIN
/*!*/;
# at 321
#150827 6:57:26 server id 1 end_log_pos 369 CRC32 0x95b7a1e7 Table_map: `test`.`t1` mapped to number 81
# at 369
#150827 6:57:26 server id 1 end_log_pos 413 CRC32 0xfb52b344 Write_rows: table id 81 flags: STMT_END_F
BINLOG '
Fu3eVRMBAAAAMAAAAHEBAAAAAFEAAAAAAAEABHRlc3QAAnQxAAIDDwLIAAPnobeV
Fu3eVR4BAAAALAAAAJ0BAAAAAFEAAAAAAAEAAgAC//wBAAAAA2xzcUSzUvs=
'/*!*/;
# at 413
#150827 6:57:26 server id 1 end_log_pos 444 CRC32 0x1548e10f Xid = 110
COMMIT/*!*/;
# at 444
#150827 6:57:32 server id 1 end_log_pos 516 CRC32 0x77b8c87d Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1440673052/*!*/;
BEGIN
/*!*/;
# at 516
#150827 6:57:32 server id 1 end_log_pos 564 CRC32 0x3516423d Table_map: `test`.`t1` mapped to number 81
# at 564
#150827 6:57:32 server id 1 end_log_pos 608 CRC32 0x0d240a02 Write_rows: table id 81 flags: STMT_END_F
BINLOG '
HO3eVRMBAAAAMAAAADQCAAAAAFEAAAAAAAEABHRlc3QAAnQxAAIDDwLIAAM9QhY1
HO3eVR4BAAAALAAAAGACAAAAAFEAAAAAAAEAAgAC//wCAAAAA2xqawIKJA0=
'/*!*/;
# at 608
#150827 6:57:32 server id 1 end_log_pos 639 CRC32 0xc519509f Xid = 111
COMMIT/*!*/;
# at 639
#150827 6:57:38 server id 1 end_log_pos 711 CRC32 0x12891a2a Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1440673058/*!*/;
BEGIN
/*!*/;
# at 711
#150827 6:57:38 server id 1 end_log_pos 759 CRC32 0xcb96f571 Table_map: `test`.`t1` mapped to number 81
# at 759
#150827 6:57:38 server id 1 end_log_pos 803 CRC32 0xffab5f61 Write_rows: table id 81 flags: STMT_END_F
BINLOG '
Iu3eVRMBAAAAMAAAAPcCAAAAAFEAAAAAAAEABHRlc3QAAnQxAAIDDwLIAANx9ZbL
Iu3eVR4BAAAALAAAACMDAAAAAFEAAAAAAAEAAgAC//wDAAAAA2xsamFfq/8=
'/*!*/;
# at 803
#150827 6:57:38 server id 1 end_log_pos 834 CRC32 0x3d7ee8c0 Xid = 112
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
發現end_log_pos 為834
2. 執行備份
[root@mysqldb mysql]# mysqldump --databases test --skip-opt --quick --extended-insert=false --lock-all-tables --master-data=2 -u root -p >/tmp/test.sql
Enter password:
[root@mysqldb mysql]# cd /tmp/
[root@mysqldb tmp]# ls -l test.sql
-rw-r--r-- 1 root root 56625668 Aug 27 06:41 test.sql
[root@mysqldb tmp]# cat test.sql
-- MySQL dump 10.13 Distrib 5.6.23, for Linux (x86_64)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.6.23-enterprise-commercial-advanced-log
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=834;
--
-- Current Database: `test`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `test`;
--
-- Table structure for table `t1`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` varchar(200) DEFAULT NULL
);
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `t1`
--
INSERT INTO `t1` VALUES (1,'lsq');
INSERT INTO `t1` VALUES (2,'ljk');
INSERT INTO `t1` VALUES (3,'llj');
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2015-08-27 7:05:54
3.模擬誤操作，dorp表
mysql> insert into t1 values(4,'zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(5,'wangwu');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | lsq |
| 2 | ljk |
| 3 | llj |
| 4 | zhangsan |
| 5 | wangwu |
+------+----------+
5 rows in set (0.00 sec)
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1347 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000001';
+------------------+------+-------------+-----------+-------------+----------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+-------------+-----------+-------------+----------------------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.23-enterprise-commercial-advanced-log, Binlog ver: 4 |
| mysql-bin.000001 | 120 | Query | 1 | 249 | use `test`; create table t1(id int,name varchar(200)) engine innodb |
| mysql-bin.000001 | 249 | Query | 1 | 321 | BEGIN |
| mysql-bin.000001 | 321 | Table_map | 1 | 369 | table_id: 81 (test.t1) |
| mysql-bin.000001 | 369 | Write_rows | 1 | 413 | table_id: 81 flags: STMT_END_F |
| mysql-bin.000001 | 413 | Xid | 1 | 444 | COMMIT /* xid=110 */ |
| mysql-bin.000001 | 444 | Query | 1 | 516 | BEGIN |
| mysql-bin.000001 | 516 | Table_map | 1 | 564 | table_id: 81 (test.t1) |
| mysql-bin.000001 | 564 | Write_rows | 1 | 608 | table_id: 81 flags: STMT_END_F |
| mysql-bin.000001 | 608 | Xid | 1 | 639 | COMMIT /* xid=111 */ |
| mysql-bin.000001 | 639 | Query | 1 | 711 | BEGIN |
| mysql-bin.000001 | 711 | Table_map | 1 | 759 | table_id: 81 (test.t1) |
| mysql-bin.000001 | 759 | Write_rows | 1 | 803 | table_id: 81 flags: STMT_END_F |
| mysql-bin.000001 | 803 | Xid | 1 | 834 | COMMIT /* xid=112 */ |
| mysql-bin.000001 | 834 | Query | 1 | 906 | BEGIN |
| mysql-bin.000001 | 906 | Table_map | 1 | 954 | table_id: 82 (test.t1) |
| mysql-bin.000001 | 954 | Write_rows | 1 | 1003 | table_id: 82 flags: STMT_END_F |
| mysql-bin.000001 | 1003 | Xid | 1 | 1034 | COMMIT /* xid=140 */ |
| mysql-bin.000001 | 1034 | Query | 1 | 1106 | BEGIN |
| mysql-bin.000001 | 1106 | Table_map | 1 | 1154 | table_id: 82 (test.t1) |
| mysql-bin.000001 | 1154 | Write_rows | 1 | 1201 | table_id: 82 flags: STMT_END_F |
| mysql-bin.000001 | 1201 | Xid | 1 | 1232 | COMMIT /* xid=141 */ |
| mysql-bin.000001 | 1232 | Query | 1 | 1347 | use `test`; DROP TABLE `t1` /* generated by server */ |
+------------------+------+-------------+-----------+-------------+----------------------------------------------------------------------+
23 rows in set (0.00 sec)
可以看出drop的日誌位置為1232
4.恢復資料庫
先刪除資料庫test
mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| data1 |
| data2 |
| mbs |
| mysql |
| performance_schema |
+--------------------+
6 rows in set (0.00 sec)
執行恢復：
[root@mysqldb tmp]# mysql -u root -p </tmp/test.sql
Enter password:
[root@mysqldb tmp]#
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| data1 |
| data2 |
| mbs |
| mysql |
| performance_schema |
| test |
+--------------------+
7 rows in set (0.00 sec)
mysql> use test;
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> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | lsq |
| 2 | ljk |
| 3 | llj |
+------+------+
3 rows in set (0.00 sec)
此時發現表t1裡只有3條記錄，也就是備份時間點的記錄數。
5.恢復到drop表之前的資料
先確定需要檢視日誌的起始位置為834--1232，執行mysqlbinlog檢視這個範圍的日誌內容，發現裡面正好是最後插入的兩條記錄：
[root@mysqldb mysql]# mysqlbinlog --start-position=834 --stop-position=1232 -vv /u01/mysql/mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#150827 6:52:29 server id 1 end_log_pos 120 CRC32 0x9407869e Start: binlog v 4, server v 5.6.23-enterprise-commercial-advanced-log created 150827 6:52:29 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
7eveVQ8BAAAAdAAAAHgAAAABAAQANS42LjIzLWVudGVycHJpc2UtY29tbWVyY2lhbC1hZHZhbmNl
ZC1sb2cAAAAAAAAAAADt695VEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAZ6G
B5Q=
'/*!*/;
# at 834
#150827 7:17:32 server id 1 end_log_pos 906 CRC32 0xa1a853c6 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1440674252/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 906
#150827 7:17:32 server id 1 end_log_pos 954 CRC32 0xc06e83bc Table_map: `test`.`t1` mapped to number 82
# at 954
#150827 7:17:32 server id 1 end_log_pos 1003 CRC32 0xd1a6d22a Write_rows: table id 82 flags: STMT_END_F
BINLOG '
zPHeVRMBAAAAMAAAALoDAAAAAFIAAAAAAAEABHRlc3QAAnQxAAIDDwLIAAO8g27A
zPHeVR4BAAAAMQAAAOsDAAAAAFIAAAAAAAEAAgAC//wEAAAACHpoYW5nc2FuKtKm0Q==
'/*!*/;
### INSERT INTO `test`.`t1`
### SET
### @1=4 /* INT meta=0 nullable=1 is_null=0 */
### @2='zhangsan' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
# at 1003
#150827 7:17:32 server id 1 end_log_pos 1034 CRC32 0xb161c0d9 Xid = 140
COMMIT/*!*/;
# at 1034
#150827 7:17:42 server id 1 end_log_pos 1106 CRC32 0x7e0ef7e3 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1440674262/*!*/;
BEGIN
/*!*/;
# at 1106
#150827 7:17:42 server id 1 end_log_pos 1154 CRC32 0xfaf1c40b Table_map: `test`.`t1` mapped to number 82
# at 1154
#150827 7:17:42 server id 1 end_log_pos 1201 CRC32 0x1912c44b Write_rows: table id 82 flags: STMT_END_F
BINLOG '
1vHeVRMBAAAAMAAAAIIEAAAAAFIAAAAAAAEABHRlc3QAAnQxAAIDDwLIAAMLxPH6
1vHeVR4BAAAALwAAALEEAAAAAFIAAAAAAAEAAgAC//wFAAAABndhbmd3dUvEEhk=
'/*!*/;
### INSERT INTO `test`.`t1`
### SET
### @1=5 /* INT meta=0 nullable=1 is_null=0 */
### @2='wangwu' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
# at 1201
#150827 7:17:42 server id 1 end_log_pos 1232 CRC32 0x4a6a75fe Xid = 141
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
執行恢復操作：
[root@mysqldb mysql]# mysqlbinlog --start-position=834 --stop-position=1232 -vv /u01/mysql/mysql-bin.000001|mysql -u root -p
Enter password:
[root@mysqldb mysql]#
再次查詢表中的資料，發現資料已經恢復：
mysql> select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | lsq |
| 2 | ljk |
| 3 | llj |
| 4 | zhangsan |
| 5 | wangwu |
+------+----------+
5 rows in set (0.00 sec)
mysql> create table t1(id int,name varchar(200)) engine=innodb;
Query OK, 0 rows affected (0.10 sec)
mysql> insert into t1 values(1,'lsq');
Query OK, 1 row affected (0.03 sec)
mysql> insert into t1 values(2,'ljk');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(3,'llj');
Query OK, 1 row affected (0.00 sec)
mysql> show variables like '%format%';
+--------------------------+-------------------+
| Variable_name | Value |
+--------------------------+-------------------+
| binlog_format | ROW |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| time_format | %H:%i:%s |
+--------------------------+-------------------+
8 rows in set (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 834 |
+------------------+-----------+
1 row in set (0.00 sec)
可以看出二進位制日誌裡最後的記錄時834，檢視日誌內容確認：
[root@mysqldb mysql]# mysqlbinlog mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#150827 6:52:29 server id 1 end_log_pos 120 CRC32 0x9407869e Start: binlog v 4, server v 5.6.23-enterprise-commercial-advanced-log created 150827 6:52:29 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
7eveVQ8BAAAAdAAAAHgAAAABAAQANS42LjIzLWVudGVycHJpc2UtY29tbWVyY2lhbC1hZHZhbmNl
ZC1sb2cAAAAAAAAAAADt695VEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAZ6G
B5Q=
'/*!*/;
# at 120
#150827 6:57:07 server id 1 end_log_pos 249 CRC32 0xc9890f80 Query thread_id=4 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1440673027/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table t1(id int,name varchar(200)) engine innodb
/*!*/;
# at 249
#150827 6:57:26 server id 1 end_log_pos 321 CRC32 0x67ff596c Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1440673046/*!*/;
BEGIN
/*!*/;
# at 321
#150827 6:57:26 server id 1 end_log_pos 369 CRC32 0x95b7a1e7 Table_map: `test`.`t1` mapped to number 81
# at 369
#150827 6:57:26 server id 1 end_log_pos 413 CRC32 0xfb52b344 Write_rows: table id 81 flags: STMT_END_F
BINLOG '
Fu3eVRMBAAAAMAAAAHEBAAAAAFEAAAAAAAEABHRlc3QAAnQxAAIDDwLIAAPnobeV
Fu3eVR4BAAAALAAAAJ0BAAAAAFEAAAAAAAEAAgAC//wBAAAAA2xzcUSzUvs=
'/*!*/;
# at 413
#150827 6:57:26 server id 1 end_log_pos 444 CRC32 0x1548e10f Xid = 110
COMMIT/*!*/;
# at 444
#150827 6:57:32 server id 1 end_log_pos 516 CRC32 0x77b8c87d Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1440673052/*!*/;
BEGIN
/*!*/;
# at 516
#150827 6:57:32 server id 1 end_log_pos 564 CRC32 0x3516423d Table_map: `test`.`t1` mapped to number 81
# at 564
#150827 6:57:32 server id 1 end_log_pos 608 CRC32 0x0d240a02 Write_rows: table id 81 flags: STMT_END_F
BINLOG '
HO3eVRMBAAAAMAAAADQCAAAAAFEAAAAAAAEABHRlc3QAAnQxAAIDDwLIAAM9QhY1
HO3eVR4BAAAALAAAAGACAAAAAFEAAAAAAAEAAgAC//wCAAAAA2xqawIKJA0=
'/*!*/;
# at 608
#150827 6:57:32 server id 1 end_log_pos 639 CRC32 0xc519509f Xid = 111
COMMIT/*!*/;
# at 639
#150827 6:57:38 server id 1 end_log_pos 711 CRC32 0x12891a2a Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1440673058/*!*/;
BEGIN
/*!*/;
# at 711
#150827 6:57:38 server id 1 end_log_pos 759 CRC32 0xcb96f571 Table_map: `test`.`t1` mapped to number 81
# at 759
#150827 6:57:38 server id 1 end_log_pos 803 CRC32 0xffab5f61 Write_rows: table id 81 flags: STMT_END_F
BINLOG '
Iu3eVRMBAAAAMAAAAPcCAAAAAFEAAAAAAAEABHRlc3QAAnQxAAIDDwLIAANx9ZbL
Iu3eVR4BAAAALAAAACMDAAAAAFEAAAAAAAEAAgAC//wDAAAAA2xsamFfq/8=
'/*!*/;
# at 803
#150827 6:57:38 server id 1 end_log_pos 834 CRC32 0x3d7ee8c0 Xid = 112
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
發現end_log_pos 為834
2. 執行備份
[root@mysqldb mysql]# mysqldump --databases test --skip-opt --quick --extended-insert=false --lock-all-tables --master-data=2 -u root -p >/tmp/test.sql
Enter password:
[root@mysqldb mysql]# cd /tmp/
[root@mysqldb tmp]# ls -l test.sql
-rw-r--r-- 1 root root 56625668 Aug 27 06:41 test.sql
[root@mysqldb tmp]# cat test.sql
-- MySQL dump 10.13 Distrib 5.6.23, for Linux (x86_64)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.6.23-enterprise-commercial-advanced-log
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=834;
--
-- Current Database: `test`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `test`;
--
-- Table structure for table `t1`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` varchar(200) DEFAULT NULL
);
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `t1`
--
INSERT INTO `t1` VALUES (1,'lsq');
INSERT INTO `t1` VALUES (2,'ljk');
INSERT INTO `t1` VALUES (3,'llj');
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2015-08-27 7:05:54
3.模擬誤操作，dorp表
mysql> insert into t1 values(4,'zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(5,'wangwu');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | lsq |
| 2 | ljk |
| 3 | llj |
| 4 | zhangsan |
| 5 | wangwu |
+------+----------+
5 rows in set (0.00 sec)
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1347 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000001';
+------------------+------+-------------+-----------+-------------+----------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+-------------+-----------+-------------+----------------------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.23-enterprise-commercial-advanced-log, Binlog ver: 4 |
| mysql-bin.000001 | 120 | Query | 1 | 249 | use `test`; create table t1(id int,name varchar(200)) engine innodb |
| mysql-bin.000001 | 249 | Query | 1 | 321 | BEGIN |
| mysql-bin.000001 | 321 | Table_map | 1 | 369 | table_id: 81 (test.t1) |
| mysql-bin.000001 | 369 | Write_rows | 1 | 413 | table_id: 81 flags: STMT_END_F |
| mysql-bin.000001 | 413 | Xid | 1 | 444 | COMMIT /* xid=110 */ |
| mysql-bin.000001 | 444 | Query | 1 | 516 | BEGIN |
| mysql-bin.000001 | 516 | Table_map | 1 | 564 | table_id: 81 (test.t1) |
| mysql-bin.000001 | 564 | Write_rows | 1 | 608 | table_id: 81 flags: STMT_END_F |
| mysql-bin.000001 | 608 | Xid | 1 | 639 | COMMIT /* xid=111 */ |
| mysql-bin.000001 | 639 | Query | 1 | 711 | BEGIN |
| mysql-bin.000001 | 711 | Table_map | 1 | 759 | table_id: 81 (test.t1) |
| mysql-bin.000001 | 759 | Write_rows | 1 | 803 | table_id: 81 flags: STMT_END_F |
| mysql-bin.000001 | 803 | Xid | 1 | 834 | COMMIT /* xid=112 */ |
| mysql-bin.000001 | 834 | Query | 1 | 906 | BEGIN |
| mysql-bin.000001 | 906 | Table_map | 1 | 954 | table_id: 82 (test.t1) |
| mysql-bin.000001 | 954 | Write_rows | 1 | 1003 | table_id: 82 flags: STMT_END_F |
| mysql-bin.000001 | 1003 | Xid | 1 | 1034 | COMMIT /* xid=140 */ |
| mysql-bin.000001 | 1034 | Query | 1 | 1106 | BEGIN |
| mysql-bin.000001 | 1106 | Table_map | 1 | 1154 | table_id: 82 (test.t1) |
| mysql-bin.000001 | 1154 | Write_rows | 1 | 1201 | table_id: 82 flags: STMT_END_F |
| mysql-bin.000001 | 1201 | Xid | 1 | 1232 | COMMIT /* xid=141 */ |
| mysql-bin.000001 | 1232 | Query | 1 | 1347 | use `test`; DROP TABLE `t1` /* generated by server */ |
+------------------+------+-------------+-----------+-------------+----------------------------------------------------------------------+
23 rows in set (0.00 sec)
可以看出drop的日誌位置為1232
4.恢復資料庫
先刪除資料庫test
mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| data1 |
| data2 |
| mbs |
| mysql |
| performance_schema |
+--------------------+
6 rows in set (0.00 sec)
執行恢復：
[root@mysqldb tmp]# mysql -u root -p </tmp/test.sql
Enter password:
[root@mysqldb tmp]#
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| data1 |
| data2 |
| mbs |
| mysql |
| performance_schema |
| test |
+--------------------+
7 rows in set (0.00 sec)
mysql> use test;
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> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | lsq |
| 2 | ljk |
| 3 | llj |
+------+------+
3 rows in set (0.00 sec)
此時發現表t1裡只有3條記錄，也就是備份時間點的記錄數。
5.恢復到drop表之前的資料
先確定需要檢視日誌的起始位置為834--1232，執行mysqlbinlog檢視這個範圍的日誌內容，發現裡面正好是最後插入的兩條記錄：
[root@mysqldb mysql]# mysqlbinlog --start-position=834 --stop-position=1232 -vv /u01/mysql/mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#150827 6:52:29 server id 1 end_log_pos 120 CRC32 0x9407869e Start: binlog v 4, server v 5.6.23-enterprise-commercial-advanced-log created 150827 6:52:29 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
7eveVQ8BAAAAdAAAAHgAAAABAAQANS42LjIzLWVudGVycHJpc2UtY29tbWVyY2lhbC1hZHZhbmNl
ZC1sb2cAAAAAAAAAAADt695VEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAZ6G
B5Q=
'/*!*/;
# at 834
#150827 7:17:32 server id 1 end_log_pos 906 CRC32 0xa1a853c6 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1440674252/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 906
#150827 7:17:32 server id 1 end_log_pos 954 CRC32 0xc06e83bc Table_map: `test`.`t1` mapped to number 82
# at 954
#150827 7:17:32 server id 1 end_log_pos 1003 CRC32 0xd1a6d22a Write_rows: table id 82 flags: STMT_END_F
BINLOG '
zPHeVRMBAAAAMAAAALoDAAAAAFIAAAAAAAEABHRlc3QAAnQxAAIDDwLIAAO8g27A
zPHeVR4BAAAAMQAAAOsDAAAAAFIAAAAAAAEAAgAC//wEAAAACHpoYW5nc2FuKtKm0Q==
'/*!*/;
### INSERT INTO `test`.`t1`
### SET
### @1=4 /* INT meta=0 nullable=1 is_null=0 */
### @2='zhangsan' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
# at 1003
#150827 7:17:32 server id 1 end_log_pos 1034 CRC32 0xb161c0d9 Xid = 140
COMMIT/*!*/;
# at 1034
#150827 7:17:42 server id 1 end_log_pos 1106 CRC32 0x7e0ef7e3 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1440674262/*!*/;
BEGIN
/*!*/;
# at 1106
#150827 7:17:42 server id 1 end_log_pos 1154 CRC32 0xfaf1c40b Table_map: `test`.`t1` mapped to number 82
# at 1154
#150827 7:17:42 server id 1 end_log_pos 1201 CRC32 0x1912c44b Write_rows: table id 82 flags: STMT_END_F
BINLOG '
1vHeVRMBAAAAMAAAAIIEAAAAAFIAAAAAAAEABHRlc3QAAnQxAAIDDwLIAAMLxPH6
1vHeVR4BAAAALwAAALEEAAAAAFIAAAAAAAEAAgAC//wFAAAABndhbmd3dUvEEhk=
'/*!*/;
### INSERT INTO `test`.`t1`
### SET
### @1=5 /* INT meta=0 nullable=1 is_null=0 */
### @2='wangwu' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
# at 1201
#150827 7:17:42 server id 1 end_log_pos 1232 CRC32 0x4a6a75fe Xid = 141
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
執行恢復操作：
[root@mysqldb mysql]# mysqlbinlog --start-position=834 --stop-position=1232 -vv /u01/mysql/mysql-bin.000001|mysql -u root -p
Enter password:
[root@mysqldb mysql]#
再次查詢表中的資料，發現資料已經恢復：
mysql> select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | lsq |
| 2 | ljk |
| 3 | llj |
| 4 | zhangsan |
| 5 | wangwu |
+------+----------+
5 rows in set (0.00 sec)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10972173/viewspace-1784961/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【Mysql】xtrabackup 備份和恢復測試MySql
- mysql備份和恢復測試(一）--xtrabackupMySql
- DB2備份恢復測試DB2
- oracle備份與恢復測試(五)Oracle
- RMAN備份恢復測試指令碼指令碼
- Mysql備份恢復MySql
- mysql 備份恢復MySql
- rman備份恢復-rman恢復資料檔案測試
- 【MySQL】MySQL備份和恢復MySql
- MySQL備份與恢復——基於Xtrabackup物理備份恢復MySql
- MySQL備份與恢復MySql
- Mysql備份和恢復MySql
- MySQL 備份與恢復MySql
- 【管理篇備份恢復】rman恢復測試（二） 控制檔案恢復（三）
- 【管理篇備份恢復】rman恢復測試（二） 控制檔案恢復（二）
- 【管理篇備份恢復】rman恢復測試（二） 控制檔案恢復（一）
- Mysql備份與恢復(1)---物理備份MySql
- 【PG備份恢復】pg_dump命令測試
- Oracle DG從庫 Rman備份恢復測試Oracle
- mysql innobackupex增量備份恢復MySql
- MySql邏輯備份恢復MySql
- Mysql的備份與恢復MySql
- MySQL 備份和恢復 一MySql
- Mysql 備份與恢復 二MySql
- 阿里面試官：知道 MySQL 邏輯備份與恢復測試麼？阿里面試MySql
- 【MYSQL備庫恢復速度測試 半同步速度測試】MySql
- MySQL 非常規恢復與物理備份恢復MySql
- Mysql備份與恢復(2)---邏輯備份MySql
- MySQL備份與恢復——基於MyDumper/MyLoader 邏輯備份恢復MySql
- 入門MySQL——備份與恢復MySql
- docker 中 MySQL 備份及恢復DockerMySql
- 《入門MySQL—備份與恢復》MySql
- MySQL備份與恢復操作解析MySql
- Mysql資料備份與恢復MySql
- MySQL備份與恢復——實操MySql
- MySQL備份與恢復簡介MySql
- 【MySql】innobackupex增量備份和恢復MySql
- mysql備份恢復mysqldump面面觀MySql