MySQL利用binlog來恢複資料庫

標籤：

1.根據binlog解析出所有ring資料庫的所有sql[[email protected] ]$ mysqlbinlog --no-defaults --database=ring --start-datetime="2005-04-20 9:55:00" --stop-datetim="2009-04-08 08:05:00" /u01/mysql/log/mysql-bin.000005 > /u01/mysql/log/mysql_restore5.sql [[email protected] ]$ ls -l /u01/mysql/log/mysql_restore5.sql-rw-r--r--    1 mysql    dba           407 Apr  8 15:33 /u01/mysql/log/mysql_restore5.sql --start-datetime=datetime 從哪個點開始解析--stop-datetim=datetime   從哪個點停止解析--database=ring               指定需要解析哪個資料庫，只提取某個資料庫的sql語句--如果有多個binlog的話需要解析多次，需要解析所有的binlog 2.重新在資料庫中執行[[email protected] ]$ mysql -u root  < /u01/mysql/log/mysql_restore5.sqlERROR 1062 (23000) at line 2559580: Duplicate entry 175754263-140 for key 1 --恢複過程中出現主鍵衝突，匯入失敗 3.刪除已經恢複的資料，重新恢複一遍[[email protected] ]$ mysql -u rootWelcome to the MySQL monitor.  Commands end with ; or g.Your MySQL connection id is 7647Server version: 5.0.67-log Source distributionType help; or h for help. Type c to clear the buffer. [email protected](none) 02:55:41>drop database ring;Query OK, 8 rows affected (0.04 sec) [[email protected] ]$ mysql -u root -f  < /u01/mysql/log/mysql_restore5.sql   -f參數：忽略錯誤繼續ERROR 1062 (23000) at line 2559580: Duplicate entry 175754263-140 for key 1ERROR 1062 (23000) at line 2564671: Duplicate entry 138 for key 1ERROR 1062 (23000) at line 2566216: Duplicate entry 139 for key 1ERROR 1062 (23000) at line 2566224: Duplicate entry 140 for key 1ERROR 1062 (23000) at line 2566232: Duplicate entry 141 for key 1ERROR 1062 (23000) at line 2566240: Duplicate entry 142 for key 1ERROR 1062 (23000) at line 2648410: Duplicate entry 143 for key 1ERROR 1062 (23000) at line 2648418: Duplicate entry 144 for key 1ERROR 1062 (23000) at line 2648581: Duplicate entry 145 for key 1ERROR 1062 (23000) at line 2648589: Duplicate entry 146 for key 1ERROR 1062 (23000) at line 2648597: Duplicate entry 147 for key 1ERROR 1062 (23000) at line 2648605: Duplicate entry 148 for key 1ERROR 1062 (23000) at line 2649279: Duplicate entry 149 for key 1ERROR 1062 (23000) at line 2649287: Duplicate entry 150 for key 1ERROR 1062 (23000) at line 2649295: Duplicate entry 151 for key 1ERROR 1062 (23000) at line 2649303: Duplicate entry 152 for key 1ERROR 1062 (23000) at line 2649311: Duplicate entry 153 for key 1ERROR 1062 (23000) at line 2649319: Duplicate entry 154 for key 1ERROR 1062 (23000) at line 2649327: Duplicate entry 155 for key 1ERROR 1062 (23000) at line 2649335: Duplicate entry 156 for key 1  恢複成功，有部分資料衝突需要開發自己去處理了。

MySQL利用binlog來恢複資料庫