mysql> use zjzc;
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> select * from abcdefg;
+------+
| id |
+------+
| 10 |
+------+
1 row in set (0.04 sec)
mysql> quit
Bye
[root@master ~]# set -o vi
[root@master ~]# mysqldump -uroot -p'1234567' -R --single-transaction --master-data=2 zjzc >zjzc.sql
Warning: Using a password on the command line interface can be insecure.
[root@master ~]# date
2015年 07月 30日 星期四 17:02:18 CST
2.插入数据:
mysql> use zjzc;
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> select * from abcdefg;
+------+
| id |
+------+
| 10 |
+------+
1 row in set (0.00 sec)
mysql> insert into abcdefg values(20);
Query OK, 1 row affected (0.08 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from abcdefg;
+------+
| id |
+------+
| 10 |
| 20 |
+------+
2 rows in set (0.00 sec)
[root@master ~]# date
2015年 07月 30日 星期四 17:09:18 CST
3.再次插入数据:
mysql> use zjzc;
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> select * from abcdefg;
+------+
| id |
+------+
| 10 |
| 20 |
+------+
2 rows in set (0.00 sec)
mysql> update abcdefg set id=100 where id=20;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from abcdefg;
+------+
| id |
+------+
| 10 |
| 100 |
+------+
2 rows in set (0.00 sec)
mysql> quit
Bye
[root@master ~]# date
2015年 07月 30日 星期四 17:14:58 CST
//////////////////////////////////////////////////////////////////
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 260861478 |
| mysql-bin.000002 | 143 |
| mysql-bin.000003 | 1717 |
| mysql-bin.000004 | 430 |
| mysql-bin.000005 | 537677804 |
| mysql-bin.000006 | 505764890 |
+------------------+-----------+
6 rows in set (0.02 sec)
////////////////////////////////////////////////////////////////////
show binlog events in '';
4.drop table
mysql> drop table abcdefg;
Query OK, 0 rows affected (0.28 sec)
mysql> quit
dBye
[root@master ~]# date
2015年 07月 30日 星期四 17:22:24 CST
5. 恢复数据库:
mysql> use zjzc;
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> select * from abcdefg;
+------+
| id |
+------+
| 10 |
+------+
1 row in set (0.00 sec)
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=505764455;
[root@master binlog]# mysqlbinlog mysql-bin.000006 | grep -n abcdefg
5200:insert into abcdefg values(30)
5213:update abcdefg set id=100 where id=30
10963:DROP TABLE IF EXISTS `abcdefg` /* generated by server */
10968:CREATE TABLE `abcdefg` (
10975:/*!40000 ALTER TABLE `abcdefg` DISABLE KEYS */
10985:INSERT INTO `abcdefg` VALUES (10)
10993:/*!40000 ALTER TABLE `abcdefg` ENABLE KEYS */
11043:insert into abcdefg values(20)
11056:update abcdefg set id=100 where id=20
11064:DROP TABLE `abcdefg` /* generated by server */
[root@master binlog]# mysqlbinlog mysql-bin.000006 | sed -n '11043,11064p'
insert into abcdefg values(20)
/*!*/;
# at 505764638
#150730 17:08:55 server id 135 end_log_pos 505764669 CRC32 0xd190762a Xid = 9210
COMMIT/*!*/;
# at 505764669
#150730 17:14:51 server id 135 end_log_pos 505764748 CRC32 0xf71eb1fc Query thread_id=32 exec_time=0 error_code=0
SET TIMESTAMP=1438247691/*!*/;
BEGIN
/*!*/;
# at 505764748
#150730 17:14:51 server id 135 end_log_pos 505764859 CRC32 0xed65ed4c Query thread_id=32 exec_time=0 error_code=0
SET TIMESTAMP=1438247691/*!*/;
update abcdefg set id=100 where id=20
/*!*/;
# at 505764859
#150730 17:14:51 server id 135 end_log_pos 505764890 CRC32 0x50a8a197 Xid = 9282
COMMIT/*!*/;
# at 505764890
#150730 17:22:21 server id 135 end_log_pos 505765010 CRC32 0x3ba200e0 Query thread_id=36 exec_time=0 error_code=0
SET TIMESTAMP=1438248141/*!*/;
DROP TABLE `abcdefg` /* generated by server */
[root@master binlog]# mysqlbinlog --start-position=505764455 --stop-position=505764859 /data01/mysqllog/binlog/mysql-bin.000006 >aa.sql
[root@master binlog]# mysql -uroot -p1234567 -D zjzc <aa.sql
数据已回到那个时间点:
mysql> select * from abcdefg;
+------+
| id |
+------+
| 10 |
| 20 |
+------+
2 rows in set (0.02 sec)
[root@master binlog]# mysqlbinlog --start-position=505764455 /data01/mysqllog/binlog/mysql-bin.000006 >aa.sql
[root@master binlog]# set -o vi
[root@master binlog]# mysql -uroot -p1234567 -D zjzc <aa.sql
Warning: Using a password on the command line interface can be insecure.
mysql> select * from abcdefg;
ERROR 1146 (42S02): Table 'zjzc.abcdefg' doesn't exist
从给定的 starting position到binary log的结尾