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

　　,

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!