设为首页 收藏本站

运维网

 找回密码
 立即注册
查看: 1515|回复: 0

[经验分享] mysql dba系统学习（17）mysql的备份和恢复的完整实践

[复制链接]
285572001

尚未签到
发表于 2018-9-27 08:02:20 | 显示全部楼层 |阅读模式
　　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>　　
SET TIMESTAMP=1378391202/*!*/;
　　
COMMIT
　　
/*!*/;
　　
# at 658

　　
#130905 22:27:15 server>　　
SET TIMESTAMP=1378391235/*!*/;
　　
BEGIN
　　
/*!*/;
　　
# at 727
　　
# at 775

　　
#130905 22:27:15 server>
　　
#130905 22:27:15 server>　　
BINLOG '
　　
w5QoUhMBAAAAMAAAAAcDAAAAABUAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIH
　　
w5QoUhcBAAAANAAAADsDAAAAABUAAAAAAAEAA//4AgAAAAsAdGlhbmhvbmd5YW4DAEJNVw==
　　
'/*!*/;
　　
# at 827

　　
#130905 22:27:15 server>　　
SET TIMESTAMP=1378391235/*!*/;
　　
COMMIT
　　
/*!*/;
　　
# at 897

　　
#130905 22:27:56 server>　　
SET TIMESTAMP=1378391276/*!*/;
　　
BEGIN
　　
/*!*/;
　　
# at 966
　　
# at 1014

　　
#130905 22:27:56 server>
　　
#130905 22:27:56 server>　　
BINLOG '
　　
7JQoUhMBAAAAMAAAAPYDAAAAABUAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIH
　　
7JQoUhcBAAAAMQAAACcEAAAAABUAAAAAAAEAA//4AwAAAAgAamlzdWFuamkDAHdoeQ==
　　
'/*!*/;
　　
# at 1063

　　
#130905 22:27:56 server>　　
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的位置就可以恢复误删除的表tt
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-p123456showdatabases;
　　+--------------------+
　　|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>　　
ROLLBACK/*!*/;
　　
BINLOG '
　　
+YAoUg8BAAAAZgAAAGoAAAAAAAQANS4xLjcwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
　　
AAAAAAAAAAAAAAAAAAD5gChSEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
　　
'/*!*/;
　　
# at 1133

　　
#130905 23:06:50 server>　　
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>
　　
#130905 23:06:50 server>　　
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>　　
SET TIMESTAMP=1378393610/*!*/;
　　
COMMIT
　　
/*!*/;
　　
# at 1371

　　
#130905 23:07:39 server>　　
SET TIMESTAMP=1378393659/*!*/;
　　
BEGIN
　　
/*!*/;
　　
# at 1440
　　
# at 1488

　　
#130905 23:07:39 server>
　　
#130905 23:07:39 server>　　
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>　　
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)



运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表，该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有，请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意，请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息，一经发现立即删除。若您因此触犯法律，一切后果自负，我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集，我们仅提供一个展示、介绍、观摩学习的平台，我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责，亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏，不得用于商业或者其他用途，否则，一切后果均由您自己承担，我们对此不承担任何法律责任
7、如涉及侵犯版权等问题，请您及时通知我们，我们将立即采取措施予以解决
8、联系人Email：admin@iyunv.com 网址：www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址：https://www.yunweiku.com/thread-602541-1-1.html 上篇帖子： 记录：linux下安装mysql5 下篇帖子： Lamp 安装 MySQL 遇到的问题汇总
回复

使用道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群，最新一手资源尽在官方微信交流群！快快加入我们吧...

扫描微信二维码查看详情

客服 E-mail:iyunvcom@gmail.com

本站由青云提供云计算服务

运维网--中国最专业的运维工程师交流社区

豫ICP备20007574号-1 Copyright © 2012-2025

关于我们

发展历程

联系我们

关注我们

广告服务

广告案例

隐私条款

免责声明

用户登录

用户注册

版主守则

申请版主

手机版

金币获取

帮助中心

每日签到

Good Good

Study

Day Day

UP


客服E-mail:kefu@iyunv.com 客服QQ：1061981298


QQ群⑦：运维网交流群⑦ QQ群⑧：运维网交流群⑧ k8s群：运维网kubernetes交流群


提醒：禁止发布任何违反国家法律、法规的言论与图片等内容；本站内容均来自个人观点与网络等信息，非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来，其版权均归原作者及其网站所有，我们尊重他人的合法权益，如有内容侵犯您的合法权益，请及时与我们联系进行核实删除！



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表