mysql数据恢复日志（一篇文章让你学会mysql恢复数据---Mysql根据binlog日志恢复数据）

OS:CentOS6 mysql版本：5.1

1.开启binlog

#编辑my.cnf,添加 [mysqld] log-bin #默认binlog日志存储在/var/lib/mysql下 #重启服务即可

2.建立测试使用的数据库

create database my; #把mysql库的user表导入进去做测试使用 mysqldump -uroot -p mysql user |mysql my -uroot -p

3.开始测试

#以mysql库里的user表为例，查看原有数据 mysql> select User,Host from user; ------ ----------------------- | User | Host | ------ ----------------------- | root | 127.0.0.1 | | | localhost | | root | localhost | | | localhost.localdomain | | root | localhost.localdomain | ------ ----------------------- 5 rows in set (0.00 sec) #更新几条数据 mysql> update user set Host=192.168.0.62 where Host=localhost; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> update user set Host=192.168.0.63 where Host=localhost.localdomain; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> update user set Host=192.168.0.64 where Host=127.0.0.1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 #查看更新后的数据 mysql> select User,Host from user; ------ -------------- | User | Host | ------ -------------- | | 192.168.0.62 | | root | 192.168.0.62 | | | 192.168.0.63 | | root | 192.168.0.63 | | root | 192.168.0.64 | ------ -------------- 5 rows in set (0.00 sec) #查看日志事件 mysql> show binlog events in mysqld-bin.000001G; *************************** 1. row *************************** Log_name: mysqld-bin.000001 Pos: 4 Event_type: Format_desc Server_id: 1 End_log_pos: 106 Info: Server ver: 5.1.73-log, Binlog ver: 4 *************************** 2. row *************************** Log_name: mysqld-bin.000001 Pos: 106 Event_type: Query Server_id: 1 End_log_pos: 194 Info: use `my`; DROP TABLE IF EXISTS `user` *************************** 3. row *************************** Log_name: mysqld-bin.000001 Pos: 194 Event_type: Query Server_id: 1 End_log_pos: 3049 Info: use `my`; CREATE TABLE `user` ( `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT , `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT , `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT , `Select_priv` enum(N,Y) CHARACTER SET utf8 NOT NULL DEFAULT N, `Insert_priv` enum(N,Y) CHARACTER SET utf8 NOT NULL DEFAULT N, `Update_priv` enum(N,Y) CHARACTER SET utf8 NOT NULL DEFAULT N, `Delete_priv` enum(N,Y) CHARACTER SET utf8 NOT NULL DEFAULT N, `Create_priv` enum(N,Y) CHARACTER SET utf8 NOT NULL DEFAULT N, `Drop_priv` enum(N,Y) CHARACTER SET utf8 NOT NULL DEFAULT N, `Reload_priv` enum(N,Y) CHARACTER SET utf8 NOT NULL DEFAULT N, `Shutdown_priv` enum(N,Y) CHARACTER SET utf8 NOT NULL DEFAULT N, `Process_priv` enum(N,Y) CHARACTER SET utf8 NOT NULL DEFAULT N, `File_priv` enum(N,Y) CHARACTER SET utf8 NOT NULL DEFAULT N, `Grant_priv` enum(N,Y) CHARACTER SET utf8 NOT NULL DEFAULT N, `References_priv` enum(N,Y) CHARACTER SET utf8 NOT NULL DEFAULT N, `Index_priv` enum(N,Y) CHARACTER SET utf8 NOT NULL DEFAULT N, `Alter_priv` enum(N,Y) CHARACTER SET utf8 NOT NULL DEFAULT N, `Show_db_priv` enum(N,Y) CHARACTER SET utf8 NOT NULL DEFAULT N, `Super_priv` enum(N,Y) CHARACTER SET utf8 NOT NULL DEFAULT N, `Create_tmp_table_priv` enum(N,Y) CHARACTER SET utf8 NOT NULL DEFAULT N, `Lock_tables_priv` enum(N,Y) CHARACTER SET utf8 NOT NULL DEFAULT N, `Execute_priv` enum(N,Y) CHARACTER SET utf8 NOT NULL DEFAULT N, `Repl_slave_priv` enum(N,Y) CHARACTER SET utf8 NOT NULL DEFAULT N, `Repl_client_priv` enum(N,Y) CHARACTER SET utf8 NOT NULL DEFAULT N, `Create_view_priv` enum(N,Y) CHARACTER SET utf8 NOT NULL DEFAULT N, `Show_view_priv` enum(N,Y) CHARACTER SET utf8 NOT NULL DEFAULT N, `Create_routine_priv` enum(N,Y) CHARACTER SET utf8 NOT NULL DEFAULT N, `Alter_routine_priv` enum(N,Y) CHARACTER SET utf8 NOT NULL DEFAULT N, `Create_user_priv` enum(N,Y) CHARACTER SET utf8 NOT NULL DEFAULT N, `Event_priv` enum(N,Y) CHARACTER SET utf8 NOT NULL DEFAULT N, `Trigger_priv` enum(N,Y) CHARACTER SET utf8 NOT NULL DEFAULT N, `ssl_type` enum(,ANY,X509,SPECIFIED) CHARACTER SET utf8 NOT NULL DEFAU LT , `ssl_cipher` blob NOT NULL, `x509_issuer` blob NOT NULL, `x509_subject` blob NOT NULL, `max_questions` int(11) unsigned NOT NULL DEFAULT 0, `max_updates` int(11) unsigned NOT NULL DEFAULT 0, `max_connections` int(11) unsigned NOT NULL DEFAULT 0, `max_user_connections` int(11) unsigned NOT NULL DEFAULT 0, PRIMARY KEY (`Host`,`User`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT=Users and global privileges *************************** 4. row *************************** Log_name: mysqld-bin.000001 Pos: 3049 Event_type: Query Server_id: 1 End_log_pos: 3153 Info: use `my`; /*!40000 ALTER TABLE `user` DISABLE KEYS */ *************************** 5. row *************************** Log_name: mysqld-bin.000001 Pos: 3153 Event_type: Query Server_id: 1 End_log_pos: 4076 Info: use `my`; INSERT INTO `user` VALUES (localhost,root,*6BB4837EB 74329105EE4568DDA7DC67ED2CA2AD9,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y ,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,,,,,0,0 ,0,0),(localhost.localdomain,root,,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y ,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,,, ,,0,0,0,0),(127.0.0.1,root,,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y ,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,,,, ,0,0,0,0),(localhost,,,N,N,N,N,N,N,N,N,N,N,N,N,N ,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,,,,,0,0,0,0 ),(localhost.localdomain,,,N,N,N,N,N,N,N,N,N,N,N,N ,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,,,,,0,0 ,0,0) *************************** 6. row *************************** Log_name: mysqld-bin.000001 Pos: 4076 Event_type: Query Server_id: 1 End_log_pos: 4179 Info: use `my`; /*!40000 ALTER TABLE `user` ENABLE KEYS */ *************************** 7. row *************************** Log_name: mysqld-bin.000001 Pos: 4179 Event_type: Query Server_id: 1 End_log_pos: 4298 Info: use `my`; update user set Host=192.168.0.62 where Host=localhost *************************** 8. row *************************** Log_name: mysqld-bin.000001 Pos: 4298 Event_type: Query Server_id: 1 End_log_pos: 4429 Info: use `my`; update user set Host=192.168.0.63 where Host=localhost .localdomain *************************** 9. row *************************** Log_name: mysqld-bin.000001 Pos: 4429 Event_type: Query Server_id: 1 End_log_pos: 4548 Info: use `my`; update user set Host=192.168.0.64 where Host=127.0.0.1 9 rows in set (0.00 sec) #最后一条更新有误，需要还原 #查看最后一条更新记录的开始Pos是4429，End_log_pos是4548，即需要还原到4429之前的数据即可。 [root@localhost mysql]# mysqlbinlog --stop-position=4429 --database=my /var/lib/mysql/mysqld-bin.000001 |mysql -uroot -p123456 -v my #查看是否恢复 mysql> select User,Host from user; ------ -------------- | User | Host | ------ -------------- | root | 127.0.0.1 | | | 192.168.0.62 | | root | 192.168.0.62 | | | 192.168.0.63 | | root | 192.168.0.63 | ------ -------------- 5 rows in set (0.00 sec) #若想取消3条更新，End_log_pos选择4179，--stop-position=4179