时间：2021-07-01 10:21:17 帮助过：31人阅读
用mysqldump和mysqlbinlog的MySQL数据恢复实验
实验文档：过程：建表——备份——模拟宕机（删表）——还原备份——恢复到当前数据。
1 还原、恢复实验创建数据库，并用drop语句模拟数据库宕机，通过mysqldump和musqlbinlog来还原和恢复。
1.1 在channel下创建table chanelfollowingmysql> 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
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选项启动服务器，指定一个其它物理设备上的与数据目录不同的位置。这样，即使包含该目录的设备丢失，日志也不会丢失）。
自己写的，防止丢失，便于查阅。。。欢迎指正。上传后，有空格被丢掉，模拟的童鞋注意别错了。
