mysql备份恢复实例

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) 删除成功。
mysql> select * from chanel_following;
+------------+------------+
| id
| uid
|
+------------+------------+
|
3 | 69686869 |
|
102 | 100000 |
| 2132723743 | 2147483647 |
1571 |
+------------------+-----------+
21 rows in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000021';
+------------------+------+-------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| official_channel_widget |
| personal_channel
|
| personal_channel_widget |
| tags
|
+-------------------------+
6 rows in set (0.00 sec)
mysql> show columns from chanel_following;
1.5 通过备份文档 还原数据库
在 1.2 步，我们把表备份在了 chanelfollowing.sql。这里要注意路径。 [root@channelme ~]# mysql -uroot -p channel </root/chanelfollowing.sql Enter password: [root@channelme ~]#
[root@channelme ~]# mysqldump -uroot -p -B channel --table chanel_following >
chanelfollowing.sql
Enter password: [root@channelme ~]# 备份成功。 注意： mysqldump、mysqladmin、mysqlbinlog 等工具是在 linux 命令行下输入。 如果没有特别写明备份路径，则默认在当前路径下，而不是 mysql 数据目录下。 查看备份内容：
`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 */;
| personal_channel
|
| personal_channel_widget |
| tags
|
+-------------------------c)
成功。
也可以在 mysql 下用 source 命令：
mysql> source \root\chanelfollowing.sql
mysql> show binary logs;
+------------------+-----------+
| Log_name
| File_size |
+------------------+-----------+
| mysql-bin.000001 | ……
29692 |
| mysql-bin.000021 |
| 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);
Database changed
mysql> show tables;
+-------------------------+
| Tables_in_channel
|
+-------------------------+
| official_channel
|
| official_channel_widget |
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL |
[root@channelme ~]# cat chanelfollowing.sql
-- MySQL dump 10.11
--
-- Host: localhost Database: channel
-- ------------------------------------------------------
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 查看插入是否正确：
Query OK, 0 rows affected (1.18 sec)
mysql> show tables;
+-------------------------+
| Tables_in_channel
|
+-------------------------+
| chanel_following
|
| official_channel
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL |
|
| uid | int(11) | NO | | NULL |
Query OK, 0 rows affected (0.00sec) ……
Query OK, 0 rows affected (0.00sec)
mysql> show columns from chanel_following;
+-------+---------+------+-----+---------+-------+
| Log_name
| Pos | Event_type | Server_id | End_log_pos | Info
| +------------------+------+-------------+-----------+-------------+----------------------------------------------------------
实验文档
1 还原、恢复实验
创建数据库，并用 drop 语句模拟数据库宕机，通过 mysqldump 和 musqlbinlog 来还原和恢 复。过程：建表——备份——模拟宕机（删表）——还原备份——恢复到当前数据。
1.1 在 channel 下创建 table chanelfollowing
mysql> use channel;