MySQL 備份和恢複

標籤：des   http   io   ar   os   使用   sp   strong   on   

方案：Full backups are necessary, but it is not always convenient to create them. They produce large backup files and take time to generate. They are not optimal in the sense that each successive full backup includes all data, even that part that has not changed since the previous full backup. It is more efficient to make an initial full backup, and then to make incremental backups. The incremental backups are smaller and take less time to produce. The tradeoff is that, at recovery time, you cannot restore your data just by reloading the full backup. You must also process the incremental backups to recover the incremental changes.  步驟：http://dev.mysql.com/doc/refman/5.6/en/backup-policy.htmlhttp://dev.mysql.com/doc/refman/5.6/en/recovery-from-backups.html1，啟用bin-log關閉資料庫，編輯/usr/my.cnf,my.ini，增加配置:

[mysqld]log-bin=mysql-bin# server-id=1 # replication時用到

innodb_flush_log_at_trx_commit=1

sync_binlog=1

重啟mysql 2，給當前資料庫做一個full backup首先查看mysql的data目錄，看看有沒有以mysql-bin.000* 開頭的檔案，這些都是bin-log，記下當前最大的number號，如：mysql-bin.000005 執行full backup

shell> mysqldump --single-transaction --flush-logs --master-data=2 \         --all-databases > backup_sunday_1_PM.sql

執行完之後，目錄下多了一個bin-log檔案：mysql-bin.000006，因為--flush-logs會讓mysql flush，--master-data=2會在輸出的sql檔案中添加下面2行注釋，（在replication中，你可以拿上面的.sql去初始化slave資料庫，然後告訴slave從master上mysql-bin.000006檔案的位置4開始replication；當然，如果你配置好了master，你也可以執行SHOW MASTER STATUS擷取這些資訊）

-- Position to start replication or point-in-time recovery from-- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000006‘,MASTER_LOG_POS=4; 

• 輸出的.sql檔案包含mysql-bin.000006之前所有的資料庫改變。

• 在這次full backup之後，所有的資料庫改變都將會記錄到mysql-bin.000006以及後續number的bin-log中。

3，定期增量備份儲存好上面的.sql檔案，以後每天使用以下命令做一個增量備份，

shell> mysqladmin flush-logs

就會產生mysql-bin.000007檔案，當天所有的log都在mysql-bin.000006中，把mysql-bin.000006檔案儲存好。明天再執行上面的命令產生08檔案，明天所有的log都07中，把07檔案儲存好。 4，Recovery當有一天，你的資料庫伺服器down掉了或者磁碟壞了，你需要restore資料庫時，先執行上面的.sql檔案，然後從mysql-bin.000006檔案開始，依次按照序號restore資料即可。

shell> mysql < backup_sunday_1_PM.sql

shell> mysqlbinlog mysql-bin.000006 mysql-bin.000007 | mysql

 5，查看某個bin-log檔案中的sql語句，sql執行時間，postion等資訊

shell> mysqlbinlog mysql-bin.000006 | more

shell> mysqlbinlog mysql-bin.000006 > temp.sql

結果片斷：# at 199
#141213  3:12:15 server id 1
use `test`/*!*/;
SET TIMESTAMP=1418469135/*!*/;
insert into user_list (name, age) values (‘haha‘, 50) 6，按時間點恢複http://dev.mysql.com/doc/refman/5.6/en/point-in-time-recovery-times.html

shell> mysqlbinlog --stop-datetime="2012-12-12 12:12:12" mysql-bin.000006 | mysql

shell> mysqlbinlog --start-datetime="2012-12-12 12:12:12" mysql-bin.000006 | mysql

 7，按postion恢複http://dev.mysql.com/doc/refman/5.6/en/point-in-time-recovery-positions.html

shell> mysqlbinlog --stop-position=120 mysql-bin.000006 | mysql

shell> mysqlbinlog --start-position=100 mysql-bin.000006 | mysql

  8，  

MySQL 備份和恢複