当前位置:Gxlcms > mysql > 用mysqldump和mysqlbinlog的MySQL数据恢复实验

用mysqldump和mysqlbinlog的MySQL数据恢复实验

时间:2021-07-01 10:21:17 帮助过:31人阅读

用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

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选项启动服务器,指定一个其它物理设备上的与数据目录不同的位置。这样,即使包含该目录的设备丢失,日志也不会丢失)。


自己写的,防止丢失,便于查阅。。。欢迎指正。上传后,有空格被丢掉,模拟的童鞋注意别错了。

linux

人气教程排行