mysql备份恢复实例


实验文档 1 还原、恢复实验 创建数据库,并用 drop 语句模拟数据库宕机,通过 mysqldump 和 musqlbinlog 来还原和恢 复。过程:建表——备份——模拟宕机(删表)——还原备份——恢复到当前数据。 1.1 在 channel 下创建 table chanelfollowing mysql> use channel; Database changed mysql> show tables; +-------------------------+ | Tables_in_channel | +-------------------------+ | official_channel | | official_channel_widget | | personal_channel | | personal_channel_widget | | tags | +-------------------------+ 5 rows in set (0.00 sec) mysql> create table chanel_following (id int primary key ,uid int not null); Query OK, 0 rows affected (1.18 sec) 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> show columns 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> insert into chanel_following values(102,100000),(3,69686869),(2132723743,21327237432); Query OK, 3 rows affected, 1 warning (0.01 sec) 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> drop table 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 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> show columns 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> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 29692 | …… | mysql-bin.000021 | 1571 | +------------------+-----------+ 21 rows in set (0.00 sec) mysql> show binlog 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, Binlog ver: 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' not found (Errcode: 2) DELIMITER ; # End of log file [root@channelme ~]# mysqlbinlog mysql-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@channelme data]# 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 选项启动服务器, 指定一个其它物理设备上的与数据目录不同的位置。这样,即使包含该目录的设备丢失,日 志也不会丢失)。
还剩5页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 10 金币 [ 分享pdf获得金币 ] 0 人已下载

下载pdf

pdf贡献者

xljs

贡献于2016-09-13

下载需要 10 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf