MySQL数据库管理员系统学习(17)的MySQL的备份和恢复的完整实践

Mysql 路人甲 194℃ 0评论

MySQL的的备份和恢复的完整实践

一,备份数据库之间的环境设置

1,创建数据库为test1,创建表TT插入如下数据

1
2
3
4
6
7
8
9
10
11
12
MySQL的> CREATE DATABASE test1的;
查询行,1 行受影响(0.04 秒)
MySQL的> 使用 TEST1
数据库中更改
MySQL的“创建表TT(ID INT ,名VAR CHAR(100 ),味精VAR CHAR(200 ))引擎= MyISAM数据;
查询OK,0 受影响的行(0.18 秒)
MySQL的“插入TT值(1 'chenzhongyang' '你好' );
查询行,1 行受影响(0.00 秒)
MySQL的“插入TT值(2 'tianhongyan' '宝马' );
查询行,1 行受影响(0.00 秒)
MySQL的“插入TT值(3 'jisuanji” ,“为什么” );
查询行,1 行受影响(0.00 秒)

2,由于我设置的二进制日志文件的记录格式是row,所以每一行的数据改变就会记录一次日志

MySQL的> showvariableslike“%格式%”

– >;

+ ——————— + ——————- +

| VARIABLE_NAME |值|

+ ——————— + ——————- +

| binlog_format | ROW |

3,此时只有一个二进制日志文件

MySQL的> showbinarylogs;

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

| Log_name | FILE_SIZE |

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

| mysqlbin.000161 | 1133 |

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

1rowinset(0.00sec)

4,查看二进制日志文件的内容

二进制日志文件end_log_pos 1133

1
2
3
4
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
三十
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
[根@ TEST4〜]#mysqlbinlog可以'/tmp/mysqlbin.000161“
...................
#在588
130905 22 26 42 服务器ID 1   end_log_pos 658    查询的thread_id = 7 exec_time = 0 ERROR_CODE = 0
SET TIMESTAMP = 1378391202 / * * /!;
承诺
/ * * /!;
#在658
130905 22 27 15 服务器ID 1   end_log_pos 727    查询的thread_id = 7 exec_time = 0 ERROR_CODE = 0
SET TIMESTAMP = 1378391235 / * * /!;
开始
/ * * /!;
#在727
#在775
130905 22 27 15 服务器ID 1   end_log_pos 775    Table_map:`test1`.`tt`映射到人数21
130905 22 27 15 服务器ID 1   end_log_pos 827    Write_rows:表编号21 的标志:STMT_END_F
BINLOG“
w5QoUhMBAAAAMAAAAAcDAAAAABUAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIH
w5QoUhcBAAAANAAAADsDAAAAABUAAAAAAAEAA // 4AgAAAAsAdGlhbmhvbmd5YW4DAEJNVw ==
/ * * /!;
#在827
130905 22 27 15 服务器ID 1   end_log_pos 897    查询的thread_id = 7 exec_time = 0 ERROR_CODE = 0
SET TIMESTAMP = 1378391235 / * * /!;
承诺
/ * * /!;
#在897
130905 22 27 56 服务器ID 1   end_log_pos 966    查询的thread_id = 7 exec_time = 0 ERROR_CODE = 0
SET TIMESTAMP = 1378391276 / * * /!;
开始
/ * * /!;
#在966
#在1014
130905 22 27 56 服务器ID 1   end_log_pos 1014   Table_map:`test1`.`tt`映射到人数21
130905 22 27 56 服务器ID 1   end_log_pos 1063   Write_rows:表编号21 的标志:STMT_END_F
BINLOG“
7JQoUhMBAAAAMAAAAPYDAAAAABUAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIH
7JQoUhcBAAAAMQAAACcEAAAAABUAAAAAAAEAA // 4AwAAAAgAamlzdWFuamkDAHdoeQ ==
/ * * /!;
#在1063
130905 22 27 56 服务器ID 1   end_log_pos 1133   查询的thread_id = 7 exec_time = 0 ERROR_CODE = 0
SET TIMESTAMP = 1378391276 / * * /!;
承诺
/ * * /!;
DELIMITER;
日志文件#结束
ROLLBACK / *通过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
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
三十
31
32
33
34
35
36
37
38
39
40
41
42
43
44
[根@ TEST4〜]#猫/tmp/test1.sql
- MySQL的转储10.13   DISTRIB 5.1 70 未知Linux的GNU下(x86_64)
-
- 主机:本地主机数据库:TEST1
- ------------------------------------------------ ------
-服务器版本   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 * / ;
-
-位置,开始复制或点- 距离-时间恢复
-
- CHANGE MASTER TO MASTER_LOG_FILE = 'mysqlbin.000161' ,MASTER_LOG_POS = 1133 ;
-
- 当前数据库:`test1`
-
CREATE DATABASE / * 32312 IF NOT EXISTS * /! `test1` / * 40100默认字符集UTF8 * /!;
使用`test1`;
-
-表结构 表`tt`
-
/ * 40101 SET @saved_cs_client = @@ character_set_client字符* / ;
/ * 40101等价:character_set_connection = UTF8 * / ;
CREATE TABLE`tt`(
`id` INT 11 )DEFAULT NULL,
`name` VAR CHAR(100 )DEFAULT NULL,
`msg` VAR CHAR(200 )DEFAULT NULL
);
/ * 40101等价:character_set_connection = @saved_cs_client * / ;
-
-转储数据 表`tt`
-
INSERT INTO`tt` VALUES(1 'chenzhongyang' '你好' );
INSERT INTO`tt` VALUES(2 'tianhongyan' '宝马' );
INSERT INTO`tt` VALUES(3 'jisuanji“ ,”为什么“ );
/ * 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 * / ;
-卸载完成2013 - 09 - 05 22 48 50

三,对表进行修改插入数据然后误删表

由于我们不小心把表tt给删除了,那么我们就要把表tt通过二进制日志给恢复过来

MySQL的> insertintottvalues(4’shuijunyi’,’老板’);

QueryOK,1rowaffected(0.01秒)

MySQL的> insertintottvalues(5,“朱骏’,’mayIknowyourname’);

QueryOK,1rowaffected(0.00sec)

MySQL的> SELECT * fromtt;

+ —— + ————— + ———————- +

| ID |名称|味精|

+ —— + ————— + ———————- +

| 1 | chenzhongyang | howareyou |

| 2 | tianhongyan |宝马|

| 3 | jisuanji |为什么|

| 4 | shuijunyi |老板|

| 5 |朱骏| mayIknowyourname |

+ —— + ————— + ———————- +

5rowsinset(0.01秒)

MySQL的> droptablett;

QueryOK,0rowsaffected(0.00sec)

四,查看执行误操作的位置

通过showmasterstatus;可以查看当前的二进制日志文件的位置

MySQL的> showmasterstatus;

+ —————– + ———- + ————– + —– ————- +

|文件|位置| Binlog_Do_DB | Binlog_Ignore_DB |

+ —————– + ———- + ————– + —– ————- +

| mysqlbin.000161 | 1622 |||

+ —————– + ———- + ————– + —– ————- +

1rowinset(0.00sec)

我们可以看到droptablett的开始位置是1622所以只需要恢复到1622的位置就可以恢复误删除的表tt

1
2
3
4
6
7
8
9
10
11
12
MySQL的>显示二进制日志事件  “mysqlbin.000161”
- >;
| 。mysqlbin 000161 | 1250 | Write_rows |         1 |        1301 | 表标识符:22 标志:STMT_END_F |
| 。mysqlbin 000161 | 1301 | 查询|         1 |        1371 | COMMIT |
| 。mysqlbin 000161 | 1371 | 查询|         1 |        1440 | BEGIN |
| 。mysqlbin 000161 | 1440 | Table_map |         1 |        1488 | 表标识符:22 (test1.tt)|
| 。mysqlbin 000161 | 1488 | Write_rows |         1 |        1552 | 表标识符:22 标志:STMT_END_F |
| 。mysqlbin 000161 | 1552 | 查询|         1 |        1622 | COMMIT |
| 。mysqlbin 000161 | 1622 | 查询|         1 |        1699 | 使用 `test1`; 删除表TT |
| 。mysqlbin 000161 | 1699 | 旋转|         1 |        1741 | 。mysqlbin 000162 ; POS = 4                                                                  |
+-----------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------+
26 0.00 秒)

五,还原数据库

这个时候我们发现只恢复了三条数据,但是我们一共有五条数据,这个时候就要通过二进制日志文件来恢复了。

注意的是当我们在恢复数据库的时候也会产生二进制日志文件,所以一定要分清楚备份前的二进制日志文件和恢复之后的二进制日志文件

MySQL的> dropdatabasetest1;

QueryOK,0rowsaffected(0.00sec)

MySQL的> showdatabases;

+ ——————– +

|数据库|

+ ——————– +

| INFORMATION_SCHEMA |

| MySQL的|

|考试|

+ ——————– +

3rowsinset(0.00sec)

[根@ TEST4〜]#的mysql-uroot-p123456 </tmp/test1.sql

MySQL的> showdatabases;

+ ——————– +

|数据库|

+ ——————– +

| INFORMATION_SCHEMA |

| MySQL的|

|考试|

| TEST1 |

+ ——————– +

4rowsinset(0.00sec)

MySQL的> usetest1

Databasechanged

MySQL的> SELECT * fromtt;

+ —— + ————— + ————- +

| ID |名称|味精|

+ —— + ————— + ————- +

| 1 | chenzhongyang | howareyou |

| 2 | tianhongyan |宝马|

| 3 | jisuanji |为什么|

+ —— + ————— + ————- +

3rowsinset(0.00sec)

六,恢复到误操作之前恢复其他的两条数据

这是时候恢复就要从开始备份的位置到删除表位置。因为这个位置是插入另外两条数据的位置

我们可以很清楚的看到这两条数据

1
2
3
4
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
三十
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
[根@ TEST4〜]#mysqlbinlog可以-p123456 --start位置= 1133 --stop位置= 1622   -vv的/ tmp / mysqlbin。000161
/ * 40019 SET @@ session.max_insert_delayed_threads = 0 * / ;
/ * 50003 SET @OLD_COMPLETION_TYPE = @@ COMPLETION_TYPE,COMPLETION_TYPE = 0 * / ;
DELIMITER / * * /!;
在#4
130905 21 02 49 服务器ID 1   end_log_pos 106    开始:二进制日志v 4 ,v服务器5.1 70 -log创建130905 21 02 49 启动
ROLLBACK / * * /!;
BINLOG“
+ YAoUg8BAAAAZgAAAGoAAAAAAAQANS4xLjcwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAD5gChSEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
/ * * /!;
#在1133
130905 23 06 50 服务器ID 1   end_log_pos 1202   查询的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 * / / * * /!;
组@@session.character_set_client= 33 ,@@session.collation_connection= 33 ,@@session.collation_server= 33 /*!*/ ;
SET @@ session.lc_time_names = 0 / * * /!;
SET @@ session.collation_database = DEFAULT / * * /!;
开始
/ * * /!;
#在1202
#在1250
130905 23 06 50 服务器ID 1   end_log_pos 1250   Table_map:`test1`.`tt`映射到人数22
130905 23 06 50 服务器ID 1   end_log_pos 1301   Write_rows:表编号22 的标志:STMT_END_F
BINLOG“
Cp4oUhMBAAAAMAAAAOIEAAAAABYAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIH
Cp4oUhcBAAAAMwAAABUFAAAAABYAAAAAAAEAA // 4BAAAAAkAc2h1aWp1bnlpBABib3Nz
/ * * /!;
### INSERT INTO`test1`.`tt`
### SET
### @ 1 = 4 / * INT元= 0可空= 1 is_null = 0 * /
### @ 2 = 'shuijunyi' / * VARSTRING(300)元= 300可空= 1 is_null = 0 * /
### @ 3 = '老板' / * VARSTRING(600)元= 600可空= 1 is_null = 0 * /
#在1301
130905 23 06 50 服务器ID 1   end_log_pos 1371   查询的thread_id = 7 exec_time = 0 ERROR_CODE = 0
SET TIMESTAMP = 1378393610 / * * /!;
承诺
/ * * /!;
#在1371
130905 23 07 39 服务器ID 1   end_log_pos 1440   查询的thread_id = 7 exec_time = 0 ERROR_CODE = 0
SET TIMESTAMP = 1378393659 / * * /!;
开始
/ * * /!;
#在1440
#在1488
130905 23 07 39 服务器ID 1   end_log_pos 1488   Table_map:`test1`.`tt`映射到人数22
130905 23 07 39 服务器ID 1   end_log_pos 1552   Write_rows:表编号22 的标志:STMT_END_F
BINLOG“
O54oUhMBAAAAMAAAANAFAAAAABYAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIH
O54oUhcBAAAAQAAAABAGAAAAABYAAAAAAAEAA // 4BQAAAAYAemh1anVuFABtYXkgSSBrbm93IHlv
dXIgbmFtZQ ==
/ * * /!;
### INSERT INTO`test1`.`tt`
### SET
### @ 1 = 5 / * INT元= 0可空= 1 is_null = 0 * /
### @ 2 = '朱骏“ / * VARSTRING(300)元= 300可空= 1 is_null = 0 * /
### @ 3 = '我可以知道你的名字' / * VARSTRING(600)元= 600可空= 1 is_null = 0 * /
#在1552
130905 23 07 39 服务器ID 1   end_log_pos 1622   查询的thread_id = 7 exec_time = 0 ERROR_CODE = 0
SET TIMESTAMP = 1378393659 / * * /!;
承诺
/ * * /!;
DELIMITER;
日志文件#结束
ROLLBACK / *通过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 |名称|味精|

+ —— + ————— + ———————- +

| 1 | chenzhongyang | howareyou |

| 2 | tianhongyan |宝马|

| 3 | jisuanji |为什么|

| 4 | shuijunyi |老板|

| 5 |朱骏| mayIknowyourname |

+ —— + ————— + ———————- +

5rowsinset(0.00sec)

本文出自“ 好好活着 ”博客,请manufacture生产profession务必出处此http://wolfword.blog.51cto.com/4892126/1289659

分享是种美德:运维博客 » MySQL数据库管理员系统学习(17)的MySQL的备份和恢复的完整实践

喜欢 (0)or分享 (0)
发表我的评论
取消评论
表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址