mysql數據庫的備份恢復

mysqldump備份數據庫

?-B, --databases ? ? Dump several databases. Note the difference in usage; in

? ? ? ? ? ? ? ? ? ? ?this case no tables are given. All name arguments are

? ? ? ? ? ? ? ? ? ? ?regarded as database names. 'USE db_name;' will be

? ? ? ? ? ? ? ? ? ? ?included in the output.

?-e, --extended-insert

? ? ? ? ? ? ? ? ? ? ?Use multiple-row INSERT syntax that include several

? ? ? ? ? ? ? ? ? ? ?VALUES lists. ?多行插入數據

? ? 為了保證數據的一致性，我們要把表鎖起來在dump

-F, --flush-logs ? ?Flush logs file in server before starting dump. Note that

? ? ? ? ? ? ? ? ? ? ?if you dump many databases at once (using the option

? ? ? ? ? ? ? ? ? ? ?--databases= or --all-databases), the logs will be

? ? ? ? ? ? ? ? ? ? ?flushed for each database dumped. The exception is when

? ? ? ? ? ? ? ? ? ? ?using --lock-all-tables or --master-data: in this case

? ? ? ? ? ? ? ? ? ? ?the logs will be flushed only once, corresponding to the

? ? ? ? ? ? ? ? ? ? ?moment all tables are locked. So if you want your dump

? ? ? ? ? ? ? ? ? ? ?and the log flush to happen at the same exact moment you

? ? ? ? ? ? ? ? ? ? ?should use --lock-all-tables or --master-data with

? ? ? ? ? ? ? ? ? ? ?--flush-logs.

? ? ? ? ? ? ? ?

-x, --lock-all-tables

? ? ? ? ? ? ? ? ? ? ?Locks all tables across all databases. This is achieved

? ? ? ? ? ? ? ? ? ? ?by taking a global read lock for the duration of the

? ? ? ? ? ? ? ? ? ? ?whole dump. Automatically turns --single-transaction and

? ? ? ? ? ? ? ? ? ? ?--lock-tables off.

?-l, --lock-tables ? Lock all tables for read.

--master-data[=#] ? This causes the binary log position and filename to be

? ? ? ? ? ? ? ? ? ? ?appended to the output. If equal to 1, will print it as a

? ? ? ? ? ? ? ? ? ? ?CHANGE MASTER command; if equal to 2, that command will

? ? ? ? ? ? ? ? ? ? ?be prefixed with a comment symbol. This option will turn

? ? ? ? ? ? ? ? ? ? ?--lock-all-tables on, unless --single-transaction is

? ? ? ? ? ? ? ? ? ? ?specified too (in which case a global read lock is only

? ? ? ? ? ? ? ? ? ? ?taken a short time at the beginning of the dump; don't

? ? ? ? ? ? ? ? ? ? ?forget to read about --single-transaction below). In all

? ? ? ? ? ? ? ? ? ? ?cases, any action on logs will happen at the exact moment

? ? ? ? ? ? ? ? ? ? ?of the dump. Option automatically turns --lock-tables

? ? ? ? ? ? ? ? ? ? ?off.

-t, --no-create-info

? ? ? ? ? ? ? ? ? ? ?Don't write table creation info.

?-d, --no-data ? ? ? No row information.

?-N, --no-set-names ?Suppress the SET NAMES statement

--opt ? ? ? ? ? ? ? Same as --add-drop-table, --add-locks, --create-options,

? ? ? ? ? ? ? ? ? ? ?--quick, --extended-insert, --lock-tables, --set-charset,

? ? ? ? ? ? ? ? ? ? ?and --disable-keys. Enabled by default, disable with

? ? ? ? ? ? ? ? ? ? ?--skip-opt.

?-q, --quick ? ? ? ? Don't buffer query, dump directly to stdout. ? 不緩存

?-R, --routines ? ? ?Dump stored routines (functions and procedures).

?--single-transaction

? ? ? ? ? ? ? ? ? ? ?Creates a consistent snapshot by dumping all tables in a

? ? ? ? ? ? ? ? ? ? ?single transaction. Works ONLY for tables stored in

? ? ? ? ? ? ? ? ? ? ?storage engines which support multiversioning (currently

? ? ? ? ? ? ? ? ? ? ?only InnoDB does); the dump is NOT guaranteed to be

? ? ? ? ? ? ? ? ? ? ?consistent for other storage engines. While a

? ? ? ? ? ? ? ? ? ? ?--single-transaction dump is in process, to ensure a

? ? ? ? ? ? ? ? ? ? ?valid dump file (correct table contents and binary log

? ? ? ? ? ? ? ? ? ? ?position), no other connection should use the following

? ? ? ? ? ? ? ? ? ? ?statements: ALTER TABLE, DROP TABLE, RENAME TABLE,

? ? ? ? ? ? ? ? ? ? ?TRUNCATE TABLE, as consistent snapshot is not isolated

? ? ? ? ? ? ? ? ? ? ?from them. Option automatically turns off --lock-tables.

?--dump-date ? ? ? ? Put a dump date to the end of the output.

--skip-opt ? ? ? ? ?Disable --opt. Disables --add-drop-table, --add-locks,

? ? ? ? ? ? ? ? ? ? ?--create-options, --quick, --extended-insert,

? ? ? ? ? ? ? ? ? ? ?--lock-tables, --set-charset, and --disable-keys.

實踐之非事務性一直備份(備份期間數據庫不可寫)

mysql> use ?test

mysql> create table tt(id int,name varchar(12));

Query OK, 0 rows affected (0.11 sec)

mysql> insert into tt values(1,'zz');

Query OK, 1 row affected (0.01 sec)

mysql> insert into tt values(2,'yy');

Query OK, 1 row affected (0.00 sec)

[root@test4 Desktop]# mysqldump ?--databases test ?--skip-opt --quick --extended-insert=false --lock-all-tables --master-data=2 ?-u root -p123456 > ?/tmp/test.sql

? 這就是dump的結果

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

[root@test4 Desktop]# cat /tmp/test.sql -- MySQL dump?10.13??Distrib?5.1.70,?for?unknown-linux-gnu (x86_64) -- -- Host: localhost??? Database: test -- ------------------------------------------------------ -- 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.000166', MASTER_LOG_POS=798; -- -- Current Database: `test` -- CREATE DATABASE?/*!32312 IF NOT EXISTS*/?`test`?/*!40100 DEFAULT CHARACTER SET utf8 */; USE `test`; -- -- 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(12) DEFAULT NULL ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data?for?table `tt` -- INSERT INTO `tt` VALUES (1,'zz'); INSERT INTO `tt` VALUES (2,'yy'); /*!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?20:15:38

? ? ?由于我們dump的時候用了master-data參數，這個時候記錄了日志位置和日志文件名

-- CHANGE MASTER TO MASTER_LOG_FILE='mysqlbin.000166', MASTER_LOG_POS=798;

實踐之事務性一直備份（備份期間數據庫可寫）

mysql> use ?test

mysql> create table tt(id int,name varchar(12)) engine=innodb ;

Query OK, 0 rows affected (0.11 sec)

mysql> insert into tt values(1,'zz');

Query OK, 1 row affected (0.01 sec)

mysql> insert into tt values(2,'yy');

Query OK, 1 row affected (0.00 sec)

[root@test4 Desktop]# mysqldump ?--databases test ?--skip-opt --quick --extended-insert=false ?--single-transaction ??--master-data=2 ?-u root -p123456 > ?/tmp/test.sql

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

一,備份數據庫之間的環境設置

1，創建數據庫test1，創建表tt插入如下數據

1 2 3 4 5 6 7 8 9 10 11 12

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

[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

1 2 3 4 5 6 7 8 9 10 11 12

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)

六，恢復到誤操作之前恢復其他的兩條數據

這是時候恢復就要從開始備份的位置到刪除表位置。因為這個位置是插入另外兩條數據的位置

我們可以很清楚的看到這兩條數據

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 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

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

from:?http://wolfword.blog.51cto.com/4892126/1289596

http://wolfword.blog.51cto.com/4892126/1289659

總結

以上是生活随笔為你收集整理的mysql dba系统学习（16）mysql的mysqldump备份 mysql dba系统学习（17）mysql的备份和恢复的完整实践的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。