mysql dba系統學習（17）mysql的備份和恢複的完整實踐

mysql的備份和恢複的完整實踐

一,備份資料庫之間的環境設定

1，建立資料庫test1，建立表tt插入如下資料

mysql> create  database test1;Query OK, 1 row affected (0.04 sec)mysql> use test1Database changedmysql> 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=0SET 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=0SET 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_FBINLOG 'w5QoUhMBAAAAMAAAAAcDAAAAABUAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIHw5QoUhcBAAAANAAAADsDAAAAABUAAAAAAAEAA//4AgAAAAsAdGlhbmhvbmd5YW4DAEJNVw=='/*!*/;# at 827#130905 22:27:15 server id 1  end_log_pos 897   Query   thread_id=7 exec_time=0 error_code=0SET 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=0SET 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_FBINLOG '7JQoUhMBAAAAMAAAAPYDAAAAABUAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIH7JQoUhcBAAAAMQAAACcEAAAAABUAAAAAAAEAA//4AwAAAAgAamlzdWFuamkDAHdoeQ=='/*!*/;# at 1063#130905 22:27:56 server id 1  end_log_pos 1133  Query   thread_id=7 exec_time=0 error_code=0SET TIMESTAMP=1378391276/*!*/;COMMIT/*!*/;DELIMITER ;# End of log fileROLLBACK /* 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-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 startupROLLBACK/*!*/;BINLOG '+YAoUg8BAAAAZgAAAGoAAAAAAAQANS4xLjcwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAD5gChSEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC'/*!*/;# at 1133#130905 23:06:50 server id 1  end_log_pos 1202  Query   thread_id=7 exec_time=0 error_code=0SET 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_FBINLOG 'Cp4oUhMBAAAAMAAAAOIEAAAAABYAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIHCp4oUhcBAAAAMwAAABUFAAAAABYAAAAAAAEAA//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=0SET 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=0SET 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_FBINLOG 'O54oUhMBAAAAMAAAANAFAAAAABYAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIHO54oUhcBAAAAQAAAABAGAAAAABYAAAAAAAEAA//4BQAAAAYAemh1anVuFABtYXkgSSBrbm93IHlvdXIgbmFtZQ=='/*!*/;### 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=0SET TIMESTAMP=1378393659/*!*/;COMMIT/*!*/;DELIMITER ;# End of log fileROLLBACK /* 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)

本文出自 “好好活著” 部落格，請務必保留此出處http://wolfword.blog.51cto.com/4892126/1289659