用mysqldump和mysqlbinlog的MySQL資料恢複實驗

實驗文檔：過程：建表——備份——類比宕機（刪表）——還原備份——恢複到當前資料。

1      還原、恢複實驗

建立資料庫，並用drop語句類比資料庫宕機，通過mysqldump和musqlbinlog來還原和恢複。

1.1    在channel下建立table  chanelfollowing

mysql> usechannel;

Database changed

mysql> showtables;

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

| Tables_in_channel       |

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

| official_channel        |

| official_channel_widget |

| personal_channel        |

| personal_channel_widget |

| tags                    |

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

5 rows in set (0.00 sec)

mysql> createtable chanel_following (id int primary key ,uid int not null);

Query OK, 0 rows affected (1.18 sec)

mysql> showtables;

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

| Tables_in_channel       |

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

| chanel_following        |

| official_channel        |

| official_channel_widget |

| personal_channel        |

| personal_channel_widget |

| tags                    |

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

6 rows in set (0.00 sec)

mysql> showcolumns from chanel_following;

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

| Field | Type    | Null | Key | Default | Extra |

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

| id   | int(11) | NO   | PRI | NULL    |      |

| uid  | int(11) | NO   |     | NULL   |       |

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

2 rows in set (0.01 sec)

1.2    通過mysqldump備份

（為還原實驗做準備）：為輸入方便，另開一視窗。

[root@channelme~]# mysqldump -uroot -p -B channel --table chanel_following >chanelfollowing.sql

Enter password:

[root@channelme ~]#   備份成功。

注意：

mysqldump、mysqladmin、mysqlbinlog 等工具是在linux命令列下輸入。

如果沒有特別寫明備份路徑，則預設在當前路徑下，而不是mysql資料目錄下。

查看備份內容：

[root@channelme~]# cat chanelfollowing.sql

-- MySQL dump 10.11

--

-- Host: localhost    Database: channel

--------------------------------------------------------

-- Server version       5.5.13-log

 

/*!40101 SET@OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

……

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

--

-- Table structure for table`chanel_following`

--

DROP TABLE IF EXISTS `chanel_following`;

SET @saved_cs_client     = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `chanel_following` (

 `id` int(11) NOT NULL,

 `uid` int(11) NOT NULL,

 PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client =@saved_cs_client;

--

-- Dumping data for table`chanel_following`

--

LOCK TABLES `chanel_following` WRITE;

/*!40000 ALTER TABLE `chanel_following`DISABLE KEYS */;

/*!40000 ALTER TABLE `chanel_following`ENABLE KEYS */;

UNLOCK TABLES;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

……

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2011-10-10  3:07:39

[root@channelme ~]#

通過備份文檔只針對錶chanel_following，說明備份正確。

1.3    在chanel_following 表下插入資料

mysql> insertinto chanel_following values(102,100000),(3,69686869),(2132723743,21327237432);

Query OK, 3 rows affected, 1 warning (0.01sec)

Records: 3 Duplicates: 0  Warnings: 1

查看插入是否正確：

mysql> select *from chanel_following;

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

| id        | uid        |

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

|         3 |   69686869 |

|       102 |     100000 |

| 2132723743 | 2147483647 |

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

rows in set (0.00 sec)

此處我順便做了個關於int的實驗。如果細心，就會發現，我插入的第三個資料與顯示的不一樣。這是因為int最大能顯示為2147483647，我存的超出了它的最大值，就截斷了，也並不是少一位為2132723743。因為uid屬性我並沒設定為非負unsigned，所以不是4294967295。

1.4    drop 表，類比宕機

mysql> droptable chanel_following;

Query OK, 0 rows affected (0.02 sec)

mysql> select *from chanel_following;

Empty set (0.00 sec)

刪除成功。

1.5    通過備份文檔還原資料庫

在1.2步，我們把表備份在了chanelfollowing.sql。這裡要注意路徑。

[root@channelme ~]# mysql -uroot -p channel</root/chanelfollowing.sql

Enter password:

[root@channelme ~]#

查看恢複結果：

mysql> show tables;

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

| Tables_in_channel       |

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

| chanel_following        |

| official_channel        |

| official_channel_widget |

| personal_channel        |

| personal_channel_widget |

| tags                    |

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

6 rows in set (0.00 sec) 

成功。

也可以在mysql下用source命令：

mysql> source \root\chanelfollowing.sql

Query OK, 0 rows affected (0.00sec)

……

Query OK, 0 rows affected (0.00sec)

mysql> showcolumns from chanel_following;

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

| Field | Type    | Null | Key | Default | Extra |

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

| id   | int(11) | NO   | PRI | NULL    |      |

| uid  | int(11) | NO   |     | NULL   |       |

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

2 rows in set (0.01 sec)

還原成功。

注意，mysqldump是用來做備份，不能夠恢複。恢複用的是mysql命令。

 

1.6    mysqlbinlog恢複

用mysqldump還原到表chanel_following建立，還有資料還沒恢複，用mysqlbinlog恢複。

mysql> showbinary logs;

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

| Log_name         | File_size |

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

| mysql-bin.000001 |     29692 |

……

| mysql-bin.000021 |      1571 |

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

21 rows in set (0.00 sec)

 

mysql> showbinlog events in 'mysql-bin.000021';

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

| Log_name         | Pos | Event_type  | Server_id |End_log_pos | Info                                                                                                                                                         |

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

| mysql-bin.000021 |    4 | Format_desc |         1 |         107 | Server ver: 5.5.13-log, Binlogver: 4                                                                                                                        |

……

| mysql-bin.000021 |  487 | Query       |        1 |         621 | use `channel`;create table chanel_following (id int primary key ,uid int not null)                                                                          |

| mysql-bin.000021 |  621 | Query       |        1 |         692 | BEGIN                                                                                                                                                        |

| mysql-bin.000021 |  692 | Query       |        1 |         843 | use `channel`;insert into chanel_following values(102,100000),(3,69686869),(2132723743,21327237432)                                                         |

| mysql-bin.000021 |  843 | Xid         |        1 |         870 | COMMIT /* xid=1296 */                                                                                                                                        |

……

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

14 rows in set (0.00 sec)

可以看出，整個資料插入在692到870之間。下面做恢複：

[root@channelme~]# mysqlbinlog -uroot -p --start-position=692 mysqlbin.000021

Enter password:

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

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

DELIMITER /*!*/;

mysqlbinlog: File 'mysqlbin.000021' notfound (Errcode: 2)

DELIMITER ;

# End of log file

 [root@channelme ~]# mysqlbinlogmysql-bin.000021 --start-position=692 --stop-position=870 | mysql -uroot -p

Enter password: mysqlbinlog: File'mysql-bin.000021' not found (Errcode: 2)

註：這裡只想著是不是我binlog 的名稱是否輸錯了，根本忘了我在/root下，而記錄檔在mysql資料目錄下！！！！！

進入data目錄：

 [root@channelme data]# ls

channel                     mysql-bin.000001  mysql-bin.000016

……

mysql-bin.000006  mysql-bin.000021   mysql-bin.index

[root@channelmedata]#  mysqlbinlog mysql-bin.000021--start-position=692 --stop-position=870 | mysql -uroot -p

Enter password:

[root@channelme data]#

恢複好了。終於沒報錯，檢查一下：

mysql> select *from chanel_following;

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

| id        | uid        |

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

|         3 |   69686869 |

|       102 |     100000 |

| 2132723743 | 2147483647 |

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

3 rows in set (0.00 sec)

ok，實驗完成。

另外注意，如果是盤壞掉，記錄檔也會丟失。所以，要想恢複，我們需要MySQL伺服器將MySQL二進位日誌儲存到安全的位置(RAID disks, SAN, ...)，應為與資料檔案的儲存位置不同的地方，保證這些日誌不在毀壞的硬碟上。(也就是，我們可以用--log-bin選項啟動伺服器，指定一個其它物理裝置上的與資料目錄不同的位置。這樣，即使包含該目錄的裝置丟失，日誌也不會丟失）。

自己寫的，防止丟失，便於查閱。。。歡迎指正。上傳後，有空格被丟掉，類比的童鞋注意別錯了。