mysql的备份和恢复的完整实践
一,备份数据库之间的环境设置
1，创建数据库test1，创建表tt插入如下数据
|
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql> create database test1;
Query OK,
1
row affected (
0.04
sec)
mysql>
use
test1
Database changed
mysql> create table tt(id
int
,name
var
char(
100
),msg
var
char(
200
)) engine=myisam;
Query OK,
0
rows affected (
0.18
sec)
mysql> insert into tt values(
1
,
'chenzhongyang'
,
'how are you'
);
Query OK,
1
row affected (
0.00
sec)
mysql> insert into tt values(
2
,
'tianhongyan'
,
'BMW'
);
Query OK,
1
row affected (
0.00
sec)
mysql> insert into tt values(
3
,
'jisuanji'
,
'why'
);
Query OK,
1
row affected (
0.00
sec)
|
2，由于我设置的二进制日志文件的记录格式是row，所以每一行的数据改变就会记录一次日志
mysql>showvariableslike"%format%"
->;
+---------------------+-------------------+
|Variable_name|Value|
+---------------------+-------------------+
|binlog_format|ROW|
3，此时只有一个二进制日志文件
mysql>showbinarylogs;
+-----------------+-----------+
|Log_name|File_size|
+-----------------+-----------+
|mysqlbin.000161|1133|
+-----------------+-----------+
1rowinset(0.00sec)
4，查看二进制日志文件的内容
二进制日志文件end_log_pos1133
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
|
[root@test4 ~]# mysqlbinlog
'/tmp/mysqlbin.000161'
。。。。。。。。。。。。。。。。。。。
# at
588
#
130905
22
:
26
:
42
server id
1
end_log_pos
658
Query thread_id=
7
exec_time=
0
error_code=
0
SET TIMESTAMP=
1378391202
/*!*/
;
COMMIT
/*!*/
;
# at
658
#
130905
22
:
27
:
15
server id
1
end_log_pos
727
Query thread_id=
7
exec_time=
0
error_code=
0
SET TIMESTAMP=
1378391235
/*!*/
;
BEGIN
/*!*/
;
# at
727
# at
775
#
130905
22
:
27
:
15
server id
1
end_log_pos
775
Table_map: `test1`.`tt` mapped to number
21
#
130905
22
:
27
:
15
server id
1
end_log_pos
827
Write_rows: table id
21
flags: STMT_END_F
BINLOG '
w5QoUhMBAAAAMAAAAAcDAAAAABUAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIH
w5QoUhcBAAAANAAAADsDAAAAABUAAAAAAAEAA
//4AgAAAAsAdGlhbmhvbmd5YW4DAEJNVw==
'
/*!*/
;
# at
827
#
130905
22
:
27
:
15
server id
1
end_log_pos
897
Query thread_id=
7
exec_time=
0
error_code=
0
SET TIMESTAMP=
1378391235
/*!*/
;
COMMIT
/*!*/
;
# at
897
#
130905
22
:
27
:
56
server id
1
end_log_pos
966
Query thread_id=
7
exec_time=
0
error_code=
0
SET TIMESTAMP=
1378391276
/*!*/
;
BEGIN
/*!*/
;
# at
966
# at
1014
#
130905
22
:
27
:
56
server id
1
end_log_pos
1014
Table_map: `test1`.`tt` mapped to number
21
#
130905
22
:
27
:
56
server id
1
end_log_pos
1063
Write_rows: table id
21
flags: STMT_END_F
BINLOG '
7JQoUhMBAAAAMAAAAPYDAAAAABUAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIH
7JQoUhcBAAAAMQAAACcEAAAAABUAAAAAAAEAA
//4AwAAAAgAamlzdWFuamkDAHdoeQ==
'
/*!*/
;
# at
1063
#
130905
22
:
27
:
56
server id
1
end_log_pos
1133
Query thread_id=
7
exec_time=
0
error_code=
0
SET TIMESTAMP=
1378391276
/*!*/
;
COMMIT
/*!*/
;
DELIMITER ;
# End of log file
ROLLBACK
/* added by mysqlbinlog */
;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/
;
|
二，备份数据库test1
1，mysqldump备份数据库
[root@test4~]#mysqldump--databasestest1--skip-opt--quick--extended-insert=false--lock-all-tables--master-data=2-uroot-p123456>/tmp/test1.sql
2，查看备份文件
我们发现这个时候记录的开始位置正好是1133，如下就是证明
CHANGEMASTERTOMASTER_LOG_FILE='mysqlbin.000161',MASTER_LOG_POS=1133;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
|
[root@test4 ~]# cat /tmp/test1.sql
-- MySQL dump
10.13
Distrib
5.1
.
70
,
for
unknown-linux-gnu (x86_64)
--
-- Host: localhost Database: test1
-- ------------------------------------------------------
-- Server version
5.1
.
70
-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=
'mysqlbin.000161'
, MASTER_LOG_POS=
1133
;
--
-- Current Database: `test1`
--
CREATE DATABASE
/*!32312 IF NOT EXISTS*/
`test1`
/*!40100 DEFAULT CHARACTER SET utf8 */
;
USE `test1`;
--
-- Table structure
for
table `tt`
--
/*!40101 SET @saved_cs_client = @@character_set_client */
;
/*!40101 SET character_set_client = utf8 */
;
CREATE TABLE `tt` (
`id`
int
(
11
) DEFAULT NULL,
`name`
var
char(
100
) DEFAULT NULL,
`msg`
var
char(
200
) DEFAULT NULL
);
/*!40101 SET character_set_client = @saved_cs_client */
;
--
-- Dumping data
for
table `tt`
--
INSERT INTO `tt` VALUES (
1
,
'chenzhongyang'
,
'how are you'
);
INSERT INTO `tt` VALUES (
2
,
'tianhongyan'
,
'BMW'
);
INSERT INTO `tt` VALUES (
3
,
'jisuanji'
,
'why'
);
/*!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
2013
-
09
-
05
22
:
48
:
50
|
三，对表进行修改插入数据然后误删表
由于我们不小心把表tt给删除了，那么我们就要把表tt通过二进制日志给恢复过来
mysql>insertintottvalues(4,'shuijunyi','boss');
QueryOK,1rowaffected(0.01sec)
mysql>insertintottvalues(5,'zhujun','mayIknowyourname');
QueryOK,1rowaffected(0.00sec)
mysql>select*fromtt;
+------+---------------+----------------------+
|id|name|msg|
+------+---------------+----------------------+
|1|chenzhongyang|howareyou|
|2|tianhongyan|BMW|
|3|jisuanji|why|
|4|shuijunyi|boss|
|5|zhujun|mayIknowyourname|
+------+---------------+----------------------+
5rowsinset(0.01sec)
mysql>droptablett;
QueryOK,0rowsaffected(0.00sec)
四，查看执行误操作的位置
通过showmasterstatus;可以查看当前的二进制日志文件的位置
mysql>showmasterstatus;
+-----------------+----------+--------------+------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
+-----------------+----------+--------------+------------------+
|mysqlbin.000161|1622|||
+-----------------+----------+--------------+------------------+
1rowinset(0.00sec)
我们可以看到droptablett的开始位置是1622所以只需要恢复到1622的位置就可以恢复误删除的表tt
|
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql> show binlog events
in
'mysqlbin.000161'
-> ;
| mysqlbin.
000161
|
1250
| Write_rows |
1
|
1301
| table_id:
22
flags: STMT_END_F |
| mysqlbin.
000161
|
1301
| Query |
1
|
1371
| COMMIT |
| mysqlbin.
000161
|
1371
| Query |
1
|
1440
| BEGIN |
| mysqlbin.
000161
|
1440
| Table_map |
1
|
1488
| table_id:
22
(test1.tt) |
| mysqlbin.
000161
|
1488
| Write_rows |
1
|
1552
| table_id:
22
flags: STMT_END_F |
| mysqlbin.
000161
|
1552
| Query |
1
|
1622
| COMMIT |
| mysqlbin.
000161
|
1622
| Query |
1
|
1699
|
use
`test1`; drop table tt |
| mysqlbin.
000161
|
1699
| Rotate |
1
|
1741
| mysqlbin.
000162
;pos=
4
|
+-----------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------+
26
rows
in
set
(
0.00
sec)
|
五，还原数据库
这个时候我们发现只恢复了三条数据，但是我们一共有五条数据，这个时候就要通过二进制日志文件来恢复了。
注意的是当我们在恢复数据库的时候也会产生二进制日志文件，所以一定要分清楚备份前的二进制日志文件和恢复之后的二进制日志文件
mysql>dropdatabasetest1;
QueryOK,0rowsaffected(0.00sec)
mysql>showdatabases;
+--------------------+
|Database|
+--------------------+
|information_schema|
|mysql|
|test|
+--------------------+
3rowsinset(0.00sec)
[root@test4~]#mysql-uroot-p123456</tmp/test1.sql
mysql>showdatabases;
+--------------------+
|Database|
+--------------------+
|information_schema|
|mysql|
|test|
|test1|
+--------------------+
4rowsinset(0.00sec)
mysql>usetest1
Databasechanged
mysql>select*fromtt;
+------+---------------+-------------+
|id|name|msg|
+------+---------------+-------------+
|1|chenzhongyang|howareyou|
|2|tianhongyan|BMW|
|3|jisuanji|why|
+------+---------------+-------------+
3rowsinset(0.00sec)
六，恢复到误操作之前恢复其他的两条数据
这是时候恢复就要从开始备份的位置到删除表位置。因为这个位置是插入另外两条数据的位置
我们可以很清楚的看到这两条数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
|
[root@test4 ~]# mysqlbinlog -p123456 --start-position=
1133
--stop-position=
1622
-vv /tmp/mysqlbin.
000161
/*!40019 SET @@session.max_insert_delayed_threads=0*/
;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/
;
DELIMITER
/*!*/
;
# at
4
#
130905
21
:
02
:
49
server id
1
end_log_pos
106
Start: binlog v
4
, server v
5.1
.
70
-log created
130905
21
:
02
:
49
at startup
ROLLBACK
/*!*/
;
BINLOG '
+YAoUg8BAAAAZgAAAGoAAAAAAAQANS4xLjcwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAD5gChSEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'
/*!*/
;
# at
1133
#
130905
23
:
06
:
50
server id
1
end_log_pos
1202
Query thread_id=
7
exec_time=
0
error_code=
0
SET TIMESTAMP=
1378393610
/*!*/
;
SET @@session.pseudo_thread_id=
7
/*!*/
;
SET @@session.foreign_key_checks=
1
, @@session.sql_auto_is_null=
1
, @@session.unique_checks=
1
, @@session.autocommit=
1
/*!*/
;
SET @@session.sql_mode=
0
/*!*/
;
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=
33
/*!*/
;
SET @@session.lc_time_names=
0
/*!*/
;
SET @@session.collation_database=DEFAULT
/*!*/
;
BEGIN
/*!*/
;
# at
1202
# at
1250
#
130905
23
:
06
:
50
server id
1
end_log_pos
1250
Table_map: `test1`.`tt` mapped to number
22
#
130905
23
:
06
:
50
server id
1
end_log_pos
1301
Write_rows: table id
22
flags: STMT_END_F
BINLOG '
Cp4oUhMBAAAAMAAAAOIEAAAAABYAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIH
Cp4oUhcBAAAAMwAAABUFAAAAABYAAAAAAAEAA
//4BAAAAAkAc2h1aWp1bnlpBABib3Nz
'
/*!*/
;
### INSERT INTO `test1`.`tt`
### SET
### @
1
=
4
/* INT meta=0 nullable=1 is_null=0 */
### @
2
=
'shuijunyi'
/* VARSTRING(300) meta=300 nullable=1 is_null=0 */
### @
3
=
'boss'
/* VARSTRING(600) meta=600 nullable=1 is_null=0 */
# at
1301
#
130905
23
:
06
:
50
server id
1
end_log_pos
1371
Query thread_id=
7
exec_time=
0
error_code=
0
SET TIMESTAMP=
1378393610
/*!*/
;
COMMIT
/*!*/
;
# at
1371
#
130905
23
:
07
:
39
server id
1
end_log_pos
1440
Query thread_id=
7
exec_time=
0
error_code=
0
SET TIMESTAMP=
1378393659
/*!*/
;
BEGIN
/*!*/
;
# at
1440
# at
1488
#
130905
23
:
07
:
39
server id
1
end_log_pos
1488
Table_map: `test1`.`tt` mapped to number
22
#
130905
23
:
07
:
39
server id
1
end_log_pos
1552
Write_rows: table id
22
flags: STMT_END_F
BINLOG '
O54oUhMBAAAAMAAAANAFAAAAABYAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIH
O54oUhcBAAAAQAAAABAGAAAAABYAAAAAAAEAA
//4BQAAAAYAemh1anVuFABtYXkgSSBrbm93IHlv
dXIgbmFtZQ==
'
/*!*/
;
### INSERT INTO `test1`.`tt`
### SET
### @
1
=
5
/* INT meta=0 nullable=1 is_null=0 */
### @
2
=
'zhujun'
/* VARSTRING(300) meta=300 nullable=1 is_null=0 */
### @
3
=
'may I know your name'
/* VARSTRING(600) meta=600 nullable=1 is_null=0 */
# at
1552
#
130905
23
:
07
:
39
server id
1
end_log_pos
1622
Query thread_id=
7
exec_time=
0
error_code=
0
SET TIMESTAMP=
1378393659
/*!*/
;
COMMIT
/*!*/
;
DELIMITER ;
# End of log file
ROLLBACK
/* added by mysqlbinlog */
;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/
;
|
正式开始恢复数据
[root@test4~]#mysqlbinlog--start-position=1133--stop-position=1622-vv/tmp/mysqlbin.000161|mysql-uroot-p123456
这个时候数据就回来了
mysql>select*fromtt;
+------+---------------+----------------------+
|id|name|msg|
+------+---------------+----------------------+
|1|chenzhongyang|howareyou|
|2|tianhongyan|BMW|
|3|jisuanji|why|
|4|shuijunyi|boss|
|5|zhujun|mayIknowyourname|
+------+---------------+----------------------+
5rowsinset(0.00sec)
