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.000001'\G;
*************************** 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到了演示最后了，不知道是否帮助到各位？
如果扥扥们有什么问题，可以文章留言。
有事留言，无事点赞，有用转发！