mysql dba系统学习（17）mysql的备份和恢复的完整实践 _MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方技术论坛-国内领先的Weblogic、Tuxedo中间件技术论坛
联动北方-国内领先的云技术服务提供商
»  游客             当前位置：  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 2111 | 回复: 0   主题： mysql dba系统学习（17）mysql的备份和恢复的完整实践         下一篇 
gang
注册用户
等级：上等兵
经验：142
发帖：79
精华：0
注册：2011-12-30
状态：离线
发送短消息息给gang 加好友    发送短消息息给gang 发消息
发表于： IP：您无权察看 2015-1-8 16:07:11 | [全部帖] [楼主帖] 楼主

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

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

1，创建数据库test1，创建表tt插入如下数据

mysql> create  database test1;
Query OK, 1 row affected (0.04 sec)
mysql> use test1
Database changed
mysql> create table tt(id int,name varchar(100),msg varchar(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

[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;
[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` varchar(100) DEFAULT NULL,
`msg` varchar(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的位置就可以恢复误删除的表

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)


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

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

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

[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)


--转自 北京联动北方科技有限公司


赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码： 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论

Weblogic中间件技术论坛

 |

Tuxedo中间件技术论坛

 |

数据库论坛

 |

Java论坛

 |

Linux/unix论坛

 |

网站地图


Powered by LandingBJ     @ 2008-2018     联动北方   
京ICP证号090597    京ICP备案09017624号    京公网安备110112000093号    
400-810-2327     Email：service@landingbj.com   

Powered by landingbj @ 2008-2015 联动北方
京ICP证号090597 京ICP备案09017624号 京公网安备110112000093号
86-27-87056338 Email：service@landingbj.com