mysql dba系统学习（17）mysql的备份和恢复的完整实践

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

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

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

<code>mysql> create database test1;</code>

<code>Query OK, </code><code>1</code> <code>row affected (</code><code>0.04</code> <code>sec)</code>

<code>mysql> </code><code>use</code> <code>test1</code>

<code>Database changed</code>

<code>mysql> create table tt(id </code><code>int</code><code>,name </code><code>var</code><code>char(</code><code>100</code><code>),msg </code><code>var</code><code>char(</code><code>200</code><code>)) engine=myisam;</code>

<code>Query OK, </code><code>0</code> <code>rows affected (</code><code>0.18</code> <code>sec)</code>

<code>mysql> insert into tt values(</code><code>1</code><code>,</code><code>'chenzhongyang'</code><code>,</code><code>'how are you'</code><code>);</code>

<code>Query OK, </code><code>1</code> <code>row affected (</code><code>0.00</code> <code>sec)</code>

<code>mysql> insert into tt values(</code><code>2</code><code>,</code><code>'tianhongyan'</code><code>,</code><code>'BMW'</code><code>);</code>

<code>mysql> insert into tt values(</code><code>3</code><code>,</code><code>'jisuanji'</code><code>,</code><code>'why'</code><code>);</code>

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，查看二进制日志文件的内容

二进制日志文件<code>end_log_pos</code><code>1133</code>

<code>[root@test4 ~]# mysqlbinlog </code><code>'/tmp/mysqlbin.000161'</code>

<code>#</code><code>130905</code> <code>22</code><code>:</code><code>26</code><code>:</code><code>42</code> <code>server id </code><code>1</code> <code>end_log_pos </code><code>658</code> <code>Query thread_id=</code><code>7</code> <code>exec_time=</code><code>0</code> <code>error_code=</code><code>0</code>

<code>SET TIMESTAMP=</code><code>1378391202</code><code>/*!*/</code><code>;</code>

<code>COMMIT</code>

<code>#</code><code>130905</code> <code>22</code><code>:</code><code>27</code><code>:</code><code>15</code> <code>server id </code><code>1</code> <code>end_log_pos </code><code>727</code> <code>Query thread_id=</code><code>7</code> <code>exec_time=</code><code>0</code> <code>error_code=</code><code>0</code>

<code>SET TIMESTAMP=</code><code>1378391235</code><code>/*!*/</code><code>;</code>

<code>BEGIN</code>

<code>#</code><code>130905</code> <code>22</code><code>:</code><code>27</code><code>:</code><code>15</code> <code>server id </code><code>1</code> <code>end_log_pos </code><code>775</code> <code>Table_map: `test1`.`tt` mapped to number </code><code>21</code>

<code>#</code><code>130905</code> <code>22</code><code>:</code><code>27</code><code>:</code><code>15</code> <code>server id </code><code>1</code> <code>end_log_pos </code><code>827</code> <code>Write_rows: table id </code><code>21</code> <code>flags: STMT_END_F</code>

<code>BINLOG '</code>

<code>w5QoUhMBAAAAMAAAAAcDAAAAABUAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIH</code>

<code>w5QoUhcBAAAANAAAADsDAAAAABUAAAAAAAEAA</code><code>//4AgAAAAsAdGlhbmhvbmd5YW4DAEJNVw==</code>

<code>#</code><code>130905</code> <code>22</code><code>:</code><code>27</code><code>:</code><code>15</code> <code>server id </code><code>1</code> <code>end_log_pos </code><code>897</code> <code>Query thread_id=</code><code>7</code> <code>exec_time=</code><code>0</code> <code>error_code=</code><code>0</code>

<code>#</code><code>130905</code> <code>22</code><code>:</code><code>27</code><code>:</code><code>56</code> <code>server id </code><code>1</code> <code>end_log_pos </code><code>966</code> <code>Query thread_id=</code><code>7</code> <code>exec_time=</code><code>0</code> <code>error_code=</code><code>0</code>

<code>SET TIMESTAMP=</code><code>1378391276</code><code>/*!*/</code><code>;</code>

<code>#</code><code>130905</code> <code>22</code><code>:</code><code>27</code><code>:</code><code>56</code> <code>server id </code><code>1</code> <code>end_log_pos </code><code>1014</code> <code>Table_map: `test1`.`tt` mapped to number </code><code>21</code>

<code>#</code><code>130905</code> <code>22</code><code>:</code><code>27</code><code>:</code><code>56</code> <code>server id </code><code>1</code> <code>end_log_pos </code><code>1063</code> <code>Write_rows: table id </code><code>21</code> <code>flags: STMT_END_F</code>

<code>7JQoUhMBAAAAMAAAAPYDAAAAABUAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIH</code>

<code>7JQoUhcBAAAAMQAAACcEAAAAABUAAAAAAAEAA</code><code>//4AwAAAAgAamlzdWFuamkDAHdoeQ==</code>

<code>#</code><code>130905</code> <code>22</code><code>:</code><code>27</code><code>:</code><code>56</code> <code>server id </code><code>1</code> <code>end_log_pos </code><code>1133</code> <code>Query thread_id=</code><code>7</code> <code>exec_time=</code><code>0</code> <code>error_code=</code><code>0</code>

<code>DELIMITER ;</code>

<code>ROLLBACK </code><code>/* added by mysqlbinlog */</code><code>;</code>

<code>/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/</code><code>;</code>

二，备份数据库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，如下就是证明

<code>CHANGEMASTERTOMASTER_LOG_FILE=</code><code>'mysqlbin.000161'</code><code>,MASTER_LOG_POS=</code><code>1133</code><code>;</code>

<code>-- MySQL dump </code><code>10.13</code> <code>Distrib </code><code>5.1</code><code>.</code><code>70</code><code>, </code><code>for</code> <code>unknown-linux-gnu (x86_64)</code>

<code>-- Host: localhost Database: test1</code>

<code>-- Server version </code><code>5.1</code><code>.</code><code>70</code><code>-log</code>

<code>/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */</code><code>;</code>

<code>/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */</code><code>;</code>

<code>/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */</code><code>;</code>

<code>/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */</code><code>;</code>

<code>-- Position to start replication or point-</code><code>in</code><code>-time recovery from</code>

<code>-- CHANGE MASTER TO MASTER_LOG_FILE=</code><code>'mysqlbin.000161'</code><code>, MASTER_LOG_POS=</code><code>1133</code><code>;</code>

<code>-- Current Database: `test1`</code>

<code>CREATE DATABASE </code><code>/*!32312 IF NOT EXISTS*/</code> <code>`test1` </code><code>/*!40100 DEFAULT CHARACTER SET utf8 */</code><code>;</code>

<code>-- Table structure </code><code>for</code> <code>table `tt`</code>

<code>/*!40101 SET @saved_cs_client = @@character_set_client */</code><code>;</code>

<code>/*!40101 SET character_set_client = utf8 */</code><code>;</code>

<code>CREATE TABLE `tt` (</code>

<code>`id` </code><code>int</code><code>(</code><code>11</code><code>) DEFAULT NULL,</code>

<code>`name` </code><code>var</code><code>char(</code><code>100</code><code>) DEFAULT NULL,</code>

<code>`msg` </code><code>var</code><code>char(</code><code>200</code><code>) DEFAULT NULL</code>

<code>/*!40101 SET character_set_client = @saved_cs_client */</code><code>;</code>

<code>-- Dumping data </code><code>for</code> <code>table `tt`</code>

<code>INSERT INTO `tt` VALUES (</code><code>1</code><code>,</code><code>'chenzhongyang'</code><code>,</code><code>'how are you'</code><code>);</code>

<code>INSERT INTO `tt` VALUES (</code><code>2</code><code>,</code><code>'tianhongyan'</code><code>,</code><code>'BMW'</code><code>);</code>

<code>INSERT INTO `tt` VALUES (</code><code>3</code><code>,</code><code>'jisuanji'</code><code>,</code><code>'why'</code><code>);</code>

<code>/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */</code><code>;</code>

<code>/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */</code><code>;</code>

<code>/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */</code><code>;</code>

<code>-- Dump completed on </code><code>2013</code><code>-</code><code>09</code><code>-</code><code>05</code> <code>22</code><code>:</code><code>48</code><code>:</code><code>50</code>

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

由于我们不小心把表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;

+-----------------+----------+--------------+------------------+

|mysqlbin.000161|1622|||

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

<code>mysql> show binlog events </code><code>in</code> <code>'mysqlbin.000161'</code>

五，还原数据库

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

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

mysql>dropdatabasetest1;

mysql>showdatabases;

+--------------------+

|Database|

|information_schema|

|mysql|

|test|

3rowsinset(0.00sec)

[root@test4~]#mysql-uroot-p123456</tmp/test1.sql

|test1|

4rowsinset(0.00sec)

mysql>usetest1

Databasechanged

+------+---------------+-------------+

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

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

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

<code>[root@test4 ~]# mysqlbinlog -p123456 --start-position=</code><code>1133</code> <code>--stop-position=</code><code>1622</code> <code>-vv /tmp/mysqlbin.</code><code>000161</code>

<code>/*!40019 SET @@session.max_insert_delayed_threads=0*/</code><code>;</code>

<code>/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/</code><code>;</code>

<code>DELIMITER </code><code>/*!*/</code><code>;</code>

<code>#</code><code>130905</code> <code>21</code><code>:</code><code>02</code><code>:</code><code>49</code> <code>server id </code><code>1</code> <code>end_log_pos </code><code>106</code> <code>Start: binlog v </code><code>4</code><code>, server v </code><code>5.1</code><code>.</code><code>70</code><code>-log created </code><code>130905</code> <code>21</code><code>:</code><code>02</code><code>:</code><code>49</code> <code>at startup</code>

<code>ROLLBACK</code><code>/*!*/</code><code>;</code>

<code>+YAoUg8BAAAAZgAAAGoAAAAAAAQANS4xLjcwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</code>

<code>AAAAAAAAAAAAAAAAAAD5gChSEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC</code>

<code>#</code><code>130905</code> <code>23</code><code>:</code><code>06</code><code>:</code><code>50</code> <code>server id </code><code>1</code> <code>end_log_pos </code><code>1202</code> <code>Query thread_id=</code><code>7</code> <code>exec_time=</code><code>0</code> <code>error_code=</code><code>0</code>

<code>SET TIMESTAMP=</code><code>1378393610</code><code>/*!*/</code><code>;</code>

<code>SET @@session.pseudo_thread_id=</code><code>7</code><code>/*!*/</code><code>;</code>

<code>SET @@session.foreign_key_checks=</code><code>1</code><code>, @@session.sql_auto_is_null=</code><code>1</code><code>, @@session.unique_checks=</code><code>1</code><code>, @@session.autocommit=</code><code>1</code><code>/*!*/</code><code>;</code>

<code>SET @@session.sql_mode=</code><code>0</code><code>/*!*/</code><code>;</code>

<code>SET @@session.auto_increment_increment=</code><code>1</code><code>, @@session.auto_increment_offset=</code><code>1</code><code>/*!*/</code><code>;</code>

<code>SET @@session.character_set_client=</code><code>33</code><code>,@@session.collation_connection=</code><code>33</code><code>,@@session.collation_server=</code><code>33</code><code>/*!*/</code><code>;</code>

<code>SET @@session.lc_time_names=</code><code>0</code><code>/*!*/</code><code>;</code>

<code>SET @@session.collation_database=DEFAULT</code><code>/*!*/</code><code>;</code>

<code>#</code><code>130905</code> <code>23</code><code>:</code><code>06</code><code>:</code><code>50</code> <code>server id </code><code>1</code> <code>end_log_pos </code><code>1250</code> <code>Table_map: `test1`.`tt` mapped to number </code><code>22</code>

<code>#</code><code>130905</code> <code>23</code><code>:</code><code>06</code><code>:</code><code>50</code> <code>server id </code><code>1</code> <code>end_log_pos </code><code>1301</code> <code>Write_rows: table id </code><code>22</code> <code>flags: STMT_END_F</code>

<code>Cp4oUhMBAAAAMAAAAOIEAAAAABYAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIH</code>

<code>Cp4oUhcBAAAAMwAAABUFAAAAABYAAAAAAAEAA</code><code>//4BAAAAAkAc2h1aWp1bnlpBABib3Nz</code>

<code>### INSERT INTO `test1`.`tt`</code>

<code>### @</code><code>1</code><code>=</code><code>4</code> <code>/* INT meta=0 nullable=1 is_null=0 */</code>

<code>### @</code><code>2</code><code>=</code><code>'shuijunyi'</code> <code>/* VARSTRING(300) meta=300 nullable=1 is_null=0 */</code>

<code>### @</code><code>3</code><code>=</code><code>'boss'</code> <code>/* VARSTRING(600) meta=600 nullable=1 is_null=0 */</code>

<code>#</code><code>130905</code> <code>23</code><code>:</code><code>06</code><code>:</code><code>50</code> <code>server id </code><code>1</code> <code>end_log_pos </code><code>1371</code> <code>Query thread_id=</code><code>7</code> <code>exec_time=</code><code>0</code> <code>error_code=</code><code>0</code>

<code>#</code><code>130905</code> <code>23</code><code>:</code><code>07</code><code>:</code><code>39</code> <code>server id </code><code>1</code> <code>end_log_pos </code><code>1440</code> <code>Query thread_id=</code><code>7</code> <code>exec_time=</code><code>0</code> <code>error_code=</code><code>0</code>

<code>SET TIMESTAMP=</code><code>1378393659</code><code>/*!*/</code><code>;</code>

<code>#</code><code>130905</code> <code>23</code><code>:</code><code>07</code><code>:</code><code>39</code> <code>server id </code><code>1</code> <code>end_log_pos </code><code>1488</code> <code>Table_map: `test1`.`tt` mapped to number </code><code>22</code>

<code>#</code><code>130905</code> <code>23</code><code>:</code><code>07</code><code>:</code><code>39</code> <code>server id </code><code>1</code> <code>end_log_pos </code><code>1552</code> <code>Write_rows: table id </code><code>22</code> <code>flags: STMT_END_F</code>

<code>O54oUhMBAAAAMAAAANAFAAAAABYAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIH</code>

<code>O54oUhcBAAAAQAAAABAGAAAAABYAAAAAAAEAA</code><code>//4BQAAAAYAemh1anVuFABtYXkgSSBrbm93IHlv</code>

<code>dXIgbmFtZQ==</code>

<code>### @</code><code>1</code><code>=</code><code>5</code> <code>/* INT meta=0 nullable=1 is_null=0 */</code>

<code>### @</code><code>2</code><code>=</code><code>'zhujun'</code> <code>/* VARSTRING(300) meta=300 nullable=1 is_null=0 */</code>

<code>### @</code><code>3</code><code>=</code><code>'may I know your name'</code> <code>/* VARSTRING(600) meta=600 nullable=1 is_null=0 */</code>

<code>#</code><code>130905</code> <code>23</code><code>:</code><code>07</code><code>:</code><code>39</code> <code>server id </code><code>1</code> <code>end_log_pos </code><code>1622</code> <code>Query thread_id=</code><code>7</code> <code>exec_time=</code><code>0</code> <code>error_code=</code><code>0</code>

正式开始恢复数据

[root@test4~]#mysqlbinlog--start-position=1133--stop-position=1622-vv/tmp/mysqlbin.000161|mysql-uroot-p123456

这个时候数据就回来了

5rowsinset(0.00sec)

本文转自陈仲阳0 51CTO博客，原文链接:http://blog.51cto.com/wolfword/1289659

mysql dba系统学习（17）mysql的备份和恢复的完整实践

继续阅读

报错：'mysql' 不是内部或外部命令，也不是可运行的程序或批处理文件。

Ubuntu16.04安装Apache+MySQL+PHP1. 安装Apache2. 安装MySQL3. 安装PHP4. 安装phpMyAdmin

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

mysql使用source命令导入.sql文件

登录plsql 报错 the account is locked --用户被锁

SequoiaDB巨杉数据库C++驱动概述