一、备份数据库
----清空一下日志
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)
----查看一下echo表的存储引擎
mysql> show table status like 'echo' \G;
*************************** 1. row ***************************
Name: echo
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 3
Avg_row_length: 5461
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 94371840
Auto_increment: NULL
Create_time: 2013-09-23 16:45:31
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
ERROR:
No query specified
----开始备份
[root@rhel5 dump]# mysqldump -u root -p --skip-opt --quick --extended-insert=false --single-transaction --master-data=2 --databases jack > /mysql/mysql5.5/dump/echo.sql
Enter password:
[root@rhel5 dump]# ll
总计 4
-rw-r--r-- 1 root root 1558 09-23 21:47 echo.sql
---查看备份出来的sql
[root@rhel5 dump]# more echo.sql
-- MySQL dump 10.13 Distrib 5.5.22, for Linux (i686)
--
-- Host: localhost Database: jack
-- ------------------------------------------------------
-- Server version 5.5.22-log
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
--
-- Current Database: `jack`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `jack` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `jack`;
--
-- Table structure for table `echo`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `echo` (
`id` int(11) DEFAULT NULL,
`msg` varchar(100) DEFAULT NULL
);
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `echo`
--
INSERT INTO `echo` VALUES (1,'aaa');
INSERT INTO `echo` VALUES (1,'bbb');
INSERT INTO `echo` VALUES (1,'人');
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2013-09-23 21:47:55
二、对表进行修改，然后删除
----插入一下内容
mysql> insert into echo values(4,'ccc');
Query OK, 1 row affected (0.02 sec)
mysql> insert into echo values(5,'ddd');
Query OK, 1 row affected (0.01 sec)
mysql> select * from echo;
+------+------+
| id | msg |
+------+------+
| 1 | aaa |
| 1 | bbb |
| 1 | 人 |
| 4 | ccc |
| 5 | ddd |
+------+------+
5 rows in set (0.00 sec)
mysql> drop table echo;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from echo;
ERROR 1146 (42S02): Table 'jack.echo' doesn't exist
三、查看表删除的位置
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 593 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> show binlog events;
+------------------+-----+-------------+-----------+-------------+---------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+---------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.22-log, Binlog ver: 4 |
| mysql-bin.000001 | 107 | Query | 1 | 175 | BEGIN |
| mysql-bin.000001 | 175 | Query | 1 | 270 | use `jack`; insert into echo values(4,'ccc') |
| mysql-bin.000001 | 270 | Xid | 1 | 297 | COMMIT /* xid=176 */ |
| mysql-bin.000001 | 297 | Query | 1 | 365 | BEGIN |
| mysql-bin.000001 | 365 | Query | 1 | 460 | use `jack`; insert into echo values(5,'ddd') |
| mysql-bin.000001 | 460 | Xid | 1 | 487 | COMMIT /* xid=177 */ |
| mysql-bin.000001 | 487 | Query | 1 | 593 | use `jack`; DROP TABLE `echo` /* generated by server */ |
| mysql-bin.000001 | 593 | Rotate | 1 | 636 | mysql-bin.000002;pos=4 |
+------------------+-----+-------------+-----------+-------------+---------------------------------------------------------+
9 rows in set (0.00 sec)
----从上面可以看出drop table 是在487----
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 636 |
| mysql-bin.000002 | 107 |
+------------------+-----------+
2 rows in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000001' \G;
*************************** 1. row ***************************
Log_name: mysql-bin.000001
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 107
Info: Server ver: 5.5.22-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql-bin.000001
Pos: 107
Event_type: Query
Server_id: 1
End_log_pos: 175
Info: BEGIN
*************************** 3. row ***************************
Log_name: mysql-bin.000001
Pos: 175
Event_type: Query
Server_id: 1
End_log_pos: 270
Info: use `jack`; insert into echo values(4,'ccc')
*************************** 4. row ***************************
Log_name: mysql-bin.000001
Pos: 270
Event_type: Xid
Server_id: 1
End_log_pos: 297
Info: COMMIT /* xid=176 */
*************************** 5. row ***************************
Log_name: mysql-bin.000001
Pos: 297
Event_type: Query
Server_id: 1
End_log_pos: 365
Info: BEGIN
*************************** 6. row ***************************
Log_name: mysql-bin.000001
Pos: 365
Event_type: Query
Server_id: 1
End_log_pos: 460
Info: use `jack`; insert into echo values(5,'ddd')
*************************** 7. row ***************************
Log_name: mysql-bin.000001
Pos: 460
Event_type: Xid
Server_id: 1
End_log_pos: 487
Info: COMMIT /* xid=177 */
*************************** 8. row ***************************
Log_name: mysql-bin.000001
Pos: 487
Event_type: Query
Server_id: 1
End_log_pos: 593
Info: use `jack`; DROP TABLE `echo` /* generated by server */
*************************** 9. row ***************************
Log_name: mysql-bin.000001
Pos: 593
Event_type: Rotate
Server_id: 1
End_log_pos: 636
Info: mysql-bin.000002;pos=4
9 rows in set (0.00 sec)
ERROR:
No query specified
四、查看备份的位置
[root@rhel5 dump]# more echo.sql
-- MySQL dump 10.13 Distrib 5.5.22, for Linux (i686)
--
-- Host: localhost Database: jack
-- ------------------------------------------------------
-- Server version 5.5.22-log
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
--
-- Current Database: `jack`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `jack` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `jack`;
--
-- Table structure for table `echo`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `echo` (
`id` int(11) DEFAULT NULL,
`msg` varchar(100) DEFAULT NULL
);
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `echo`
--
INSERT INTO `echo` VALUES (1,'aaa');
INSERT INTO `echo` VALUES (1,'bbb');
INSERT INTO `echo` VALUES (1,'人');
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2013-09-23 21:47:55
[root@rhel5 dump]# grep "CHANGE MASTER" ./echo.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
五、还原数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| jack |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.01 sec)
----因为没有测试库，所以只能把jack数据库给删除掉
mysql> drop database jack;
Query OK, 0 rows affected (0.02 sec)
----还原数据库
[root@rhel5 dump]# mysql -u root -p < ./echo.sql
Enter password:
mysql> use jack
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_jack |
+----------------+
| echo |
+----------------+
1 row in set (0.00 sec)
---已经还原到表被修改之前
mysql> select * from echo;
+------+------+
| id | msg |
+------+------+
| 1 | aaa |
| 1 | bbb |
| 1 | 人 |
+------+------+
3 rows in set (0.01 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 636 |
| mysql-bin.000002 | 1065 |
+------------------+-----------+
2 rows in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000002';
+------------------+------+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.22-log, Binlog ver: 4 |
| mysql-bin.000002 | 107 | Query | 1 | 188 | drop database jack |
| mysql-bin.000002 | 188 | Query | 1 | 337 | CREATE DATABASE /*!32312 IF NOT EXISTS*/ `jack` /*!40100 DEFAULT CHARACTER SET utf8 */ |
| mysql-bin.000002 | 337 | Query | 1 | 486 | use `jack`; CREATE TABLE `echo` (
`id` int(11) DEFAULT NULL,
`msg` varchar(100) DEFAULT NULL
) |
| mysql-bin.000002 | 486 | Query | 1 | 554 | BEGIN |
| mysql-bin.000002 | 554 | Query | 1 | 652 | use `jack`; INSERT INTO `echo` VALUES (1,'aaa') |
| mysql-bin.000002 | 652 | Xid | 1 | 679 | COMMIT /* xid=206 */ |
| mysql-bin.000002 | 679 | Query | 1 | 747 | BEGIN |
| mysql-bin.000002 | 747 | Query | 1 | 845 | use `jack`; INSERT INTO `echo` VALUES (1,'bbb') |
| mysql-bin.000002 | 845 | Xid | 1 | 872 | COMMIT /* xid=207 */ |
| mysql-bin.000002 | 872 | Query | 1 | 940 | BEGIN |
| mysql-bin.000002 | 940 | Query | 1 | 1038 | use `jack`; INSERT INTO `echo` VALUES (1,'人') |
| mysql-bin.000002 | 1038 | Xid | 1 | 1065 | COMMIT /* xid=208 */ |
+------------------+------+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------+
13 rows in set (0.00 sec)
六、恢复数据库
[root@rhel5 data]# mysqlbinlog -u root -p --start-position=107 --stop-position=487 -vv ./mysql-bin.000001 | mysql -u root -p
Enter password: Enter password:
123456
mysql> select * from echo;
+------+------+
| id | msg |
+------+------+
| 1 | aaa |
| 1 | bbb |
| 1 | 人 |
| 4 | ccc |
| 5 | ddd |
+------+------+
5 rows in set (0.00 sec)