?
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?MySQL備份方案-->(利用mysqldump以及binlog二進(jìn)制日志)
?
隨著數(shù)據(jù)不斷增加,而且為了兼容以后的innodb存儲(chǔ)引擎, 所以考慮采用mysqldump全備+日志增量備份的策略。使用mysqldump對(duì)于mysql大部分mysql存儲(chǔ)引擎比如myisam、innodb都有很好的支持。
?
方案一:mysqldump全備份+日志增量備份
1, mysqldump備份方案:
周一凌晨3點(diǎn)全備
周二到周日凌晨3點(diǎn)增量備份
?
2, 備份步驟
(1)???? 創(chuàng)建備份目錄,備份腳本存放目錄
Shell > mkdir /usr/mysqlbackup;
Shell > chmod 755 /usr/mysqlbackup;
Shell > mkdir /usr/mysqlbackup/daily;
Shell > chmod 755 /usr/mysqlbackup/daily;
Shell > mkdir /usr/script;
Shell > chmod 777 /usr/script/*.sh
?
(2)???? 啟用二進(jìn)制日志
如果日志沒有啟開,必須啟用binlog,要重啟mysqld,首先,關(guān)閉mysqld,打開/etc/my.cnf,加入以下幾行:
[mysqld]
log-bin
然后重新啟動(dòng)mysqld,會(huì)產(chǎn)生hostname-bin.000001以及hostname-bin.index,前面的日志文件是記錄所有對(duì)數(shù)據(jù)的更新操作,后面的文件是存儲(chǔ)所有二進(jìn)制文件的索引,不能輕易被刪除。
?
(3)???? 全備份,增量備份。
詳細(xì)見mysqlFullBackup.sh、mysqlDailyBackup.sh腳本( 請(qǐng)注意腳本里面的備份目錄、 mysql 軟件安裝目錄、壓縮文件名以及用戶名密碼,如有不符,請(qǐng)修改 )。
下面是部分shell上單個(gè)手動(dòng)執(zhí)行的測(cè)試命令。
Shell> /usr/local/mysql/bin/mysqldump -uroot-pnYuIman25040slave201012301124 --no-create-info=FALSE --order-by-primary=FALSE--force=FALSE --no-data=FALSE --tz-utc=TRUE --flush-privileg
es=FALSE --compress=FALSE --replace=FALSE--insert-ignore=FALSE --extended-insert=TRUE --quote-names=TRUE --hex-blob=TRUE--complete-insert=FALSE --add-locks=TRUE --port=3306 --d
isable-keys=TRUE --delayed-insert=FALSE--create-options=TRUE --delete-master-logs=FALSE --comments=TRUE--default-character-set=utf8 --max_allowed_packet=1G --flush-logs=FALSE -
-dump-date=TRUE --lock-tables=TRUE--allow-keywords=FALSE --events=FALSE --single-transaction=TRUE --routines--all-databases > /backup/mysql/full/mysql_20110104_195546.sql
?
(4)????設(shè)置crontab任務(wù),每天執(zhí)行備份腳本
shell> crontab –e
#每個(gè)星期日凌晨3:00執(zhí)行完全備份腳本
0 3 * * 0 /usr/scrpit/mysqlFullBackup.sh>/dev/null 2>&1
#周一到周六凌晨3:00做增量備份
0 3 * * 1-6/root/MySQLBackup/mysqlDailyBackup.sh >/dev/null 2>&1
(5)????清除舊的備份文件。
每天去看查看下備份磁盤空間,刪除舊的備份壓縮文件。
3, 數(shù)據(jù)庫(kù)恢復(fù)
?
【注釋】 如果增量恢復(fù)操作中報(bào)“ ERROR 1051 (42S02) at line 32: Unknown table 'fin_cell_attr_his' ”類似的錯(cuò)誤,則需要現(xiàn)create一張空表,然后再執(zhí)行增量恢復(fù)。
Mysql> createtable tb1 ……;
?
(1)????全恢復(fù):
用 mysqldump 備份出來(lái)的文件是一個(gè)可以直接倒入的 SQL 腳本,直接用 mysql命令恢復(fù)最后一次全備份:
先從備份文件服務(wù)器上copy出來(lái),解壓縮,再恢復(fù),例如:
Shell>/root/s3cmd/s3cmd get s3://csf-backup/mysqldb/ mysql_20101216_231900.sql.tar.gz?/usr/mysqlrestore/
Shell>/root/s3cmd/s3cmd get s3://csf-backup/mysqldb/ mysql_binlog_20101217_030100.tar.gz?/usr/mysqlrestore/
Shell>cd /usr/mysqlrestore/
Shell>tar -xvf mysql_20101216_231900.sql.tar.gz
Shell>tar -xvf mysql_binlog_20101217_030100.tar.gz
?
刷新日志:
Mysql>flush logs;
恢復(fù)最后一次全備份命令:
Shell>/usr/local/mysql/bin/mysql -uroot -p123456 </tmp/mysqlbackup/backup_20101215_adb.sql ?
恢復(fù)最后一次全備份之后的所有增量備份命令:
Shell>/usr/local/mysql/bin/mysqlbinlog mysql-bin.00007 mysql-bin.00008mysql-bin.00009 mysql-bin.00010 mysql-bin.00011 | mysql
檢查:查看數(shù)據(jù)庫(kù)結(jié)構(gòu)以及數(shù)據(jù)。
?
(2)????基于時(shí)間點(diǎn)恢復(fù)一,當(dāng)天全備份之后的insert點(diǎn)數(shù)據(jù)故障誤操作:
如果上午全備份之后的insert數(shù)據(jù)被誤刪除, 在11點(diǎn)發(fā)生了誤操作.可以直接用bin-log日志將數(shù)據(jù)庫(kù)恢復(fù)到11點(diǎn)之前的那一刻,然后跳過故障時(shí)間點(diǎn)11點(diǎn),再進(jìn)行恢復(fù)下面所有的操作,命令如下:
刷新日志:
Mysql>flush logs;
先從備份文件服務(wù)器上把binlog日志copy出來(lái),解壓縮。
Shell>/root/s3cmd/s3cmd get s3://csf-backup/mysqldb/ mysql_binlog_20101221_030100.tar.gz? /usr/mysqlrestore/
Shell>cd /usr/mysqlrestore/
Shell>tar -xvf mysql_binlog_20101221_030100.tar.gz
日志增量恢復(fù)到故障點(diǎn)之前
Shell>/usr/local/myql/bin/mysqlbinlog --stop-date="2010-12-21 10:59:59" mysql-bin.00011|mysql -u root -p 123456
跳過故障的時(shí)間點(diǎn),繼續(xù)執(zhí)行后面的binlog,完成恢復(fù)
Shell> /usr/local/myql/bin/mysqlbinlog--start-date="2010-12-21 10:59:59" mysql-bin.00011| mysql -u root -p 123456
檢查:
用select * from tb等sql命令查看表以及數(shù)據(jù)是否恢復(fù)。
(3)????基于時(shí)間點(diǎn)恢復(fù)二,當(dāng)天11點(diǎn)數(shù)據(jù)故障誤操作:
如果上午全備份之后的insert數(shù)據(jù)被誤刪除, 在11點(diǎn)發(fā)生了誤操作.丟失的數(shù)據(jù)是最后一次備份之前錄入的數(shù)據(jù),則必須先用全備份文件進(jìn)行全恢復(fù),然后可以用bin-log日志將數(shù)據(jù)庫(kù)恢復(fù)到11點(diǎn)之前的那一刻,然后跳過故障時(shí)間點(diǎn)11點(diǎn),再進(jìn)行恢復(fù)下面所有的操作,步驟過程如下:
刷新日志:
Mysql>flush logs;
先從備份文件服務(wù)器上copy出來(lái),解壓縮,再恢復(fù)
Shell>/root/s3cmd/s3cmd get s3://csf-backup/mysqldb/mysql_20101216_231900.sql.tar.gz?/usr/mysqlrestore/
Shell>/root/s3cmd/s3cmd get s3://csf-backup/mysqldb/ mysql_binlog_20101217_030100.tar.gz? /usr/mysqlrestore/
Shell>cd /usr/mysqlrestore/
Shell>tar -xvf mysql_20101216_231900.sql.tar.gz
Shell>tar -xvf mysql_binlog_20101217_030100.tar.gz
恢復(fù)最后一次全備份命令:
Shell>/usr/local/mysql/bin/mysql -uroot -p123456 < /tmp/mysqlbackup/mysql_20101216_231900.sql
日志增量恢復(fù)到故障點(diǎn)之前
Shell>/usr/local/myql/bin/mysqlbinlog --stop-date="2010-12-21 10:59:59" mysql-bin.00011|mysql -u root -p 123456
跳過故障的時(shí)間點(diǎn),繼續(xù)執(zhí)行后面的binlog,完成恢復(fù)
Shell> /usr/local/myql/bin/mysqlbinlog--start-date="2010-12-21 10:59:59" mysql-bin.00011| mysql -u root -p 123456
檢查:
用select * from tb等sql命令查看表以及數(shù)據(jù)是否恢復(fù)。
?
(4)????基于時(shí)間點(diǎn)恢復(fù)三,昨天11點(diǎn)數(shù)據(jù)故障誤操作:
恢復(fù)流程,先全恢復(fù),然后日志增量恢復(fù)跳過11點(diǎn)后繼續(xù)日志增量恢復(fù),如下:
先從備份文件服務(wù)器上copy出來(lái),解壓縮,再恢復(fù)
Shell>/root/s3cmd/s3cmd get s3://csf-backup/mysqldb/mysql_20101216_231900.sql.tar.gz?/usr/mysqlrestore/
Shell>/root/s3cmd/s3cmd get s3://csf-backup/mysqldb/ mysql_binlog_20101217_030100.tar.gz? /usr/mysqlrestore/
Shell>cd /usr/mysqlrestore/
Shell>tar -xvf mysql_20101216_231900.sql.tar.gz
Shell>tar -xvf mysql_binlog_20101217_030100.tar.gz
刷新日志:
Mysql>flush logs;
全恢復(fù):
Shell>/usr/local/mysql/bin/mysql -uroot -p123456 < /tmp/mysqlbackup/ mysql_20101216_231900.sql
日志增量恢復(fù)到故障點(diǎn):
Shell>/usr/local/myql/bin/mysqlbinlog --stop-date="2010-12-20 10:59:59" mysql-bin.00010|mysql -u root -p 123456
跳過故障點(diǎn)繼續(xù)恢復(fù):
Shell>/usr/local/myql/bin/mysqlbinlog --start-date="2010-12-20 10:59:59" mysql-bin.00010mysql-bin.00011| mysql -u root -p 123456
?
(5)????基于時(shí)間點(diǎn)恢復(fù)四,多故障點(diǎn)恢復(fù)比如2個(gè)故障點(diǎn)數(shù)據(jù)故障誤操作:
第一個(gè)故障點(diǎn):2010-12-2113:41:41 –> 2010-12-21 13:42:36
第二個(gè)故障點(diǎn):2010-12-2113:43:16 –> 2010-12-21 13:46:05
恢復(fù)流程,先全恢復(fù),然后日志增量恢復(fù)跳過第一個(gè)故障點(diǎn)后繼續(xù)日志增量恢復(fù)到第二個(gè)故障點(diǎn),然后跳過第二個(gè)故障點(diǎn)繼續(xù)恢復(fù)到終點(diǎn),如下:
先從備份文件服務(wù)器上copy出來(lái),解壓縮,再恢復(fù)
Shell>/root/s3cmd/s3cmd get s3://csf-backup/mysqldb/mysql_20101216_231900.sql.tar.gz?/usr/mysqlrestore/
Shell>/root/s3cmd/s3cmd get s3://csf-backup/mysqldb/ mysql_binlog_20101217_030100.tar.gz? /usr/mysqlrestore/
Shell>cd /usr/mysqlrestore/
Shell>tar -xvf mysql_20101216_231900.sql.tar.gz
Shell>tar -xvf mysql_binlog_20101217_030100.tar.gz
刷新日志:
Mysql>flush logs;
全恢復(fù):
Shell>/usr/local/mysql/bin/mysql -uroot -p123456 < /tmp/mysqlbackup/ mysql_20101216_231900.sql
日志增量恢復(fù)到第一個(gè)故障點(diǎn)之前:
Shell>/usr/local/mysql/bin/mysqlbinlog --stop-date="2010-12-21 13:41:41"mysql-bin.000012| mysql -u root -p123456;
跳過故障點(diǎn)繼續(xù)恢復(fù)到第二個(gè)故障點(diǎn)之前:
Shell> /usr/local/mysql/bin/mysqlbinlog--start-date="2010-12-21 13:42:36" --stop-date="2010-12-2113:43:16" ?mysql-bin.000012 mysql-bin.000013|mysql -u root -p123456;
?????? 跳過第二個(gè)故障點(diǎn)恢復(fù)到flushlogs之時(shí)
????? Shell>/usr/local/mysql/bin/mysqlbinlog --start-date="2010-12-21 13:46:05" --stop-date="2010-12-2113:46:33" mysql-bin.000012 mysql-bin.000013| mysql -u root -p123456;
檢查:運(yùn)用sql語(yǔ)句查詢誤操作的表以及數(shù)據(jù)是否已經(jīng)恢復(fù)。
(6)????恢復(fù)單一庫(kù):
Shell>/usr/local/mysql/bin/mysql -uroot -pmysql --database=csf </tmp/mysqlbackup/backup_20101215_adb_003.sql
?
(7)????基于單庫(kù)中的各種時(shí)間點(diǎn)情況恢復(fù):
請(qǐng)參考 以上步驟 (1) 到 (5) , 過程基本一樣,只要在恢復(fù)命令后面加上一個(gè)空格和庫(kù)名就可以了。
(8)????恢復(fù)單庫(kù)中的單張表:
Mysql所提供的工具目前無(wú)法做到這一步,有個(gè)方案是:先恢復(fù)整個(gè)庫(kù)到一個(gè)測(cè)試數(shù)據(jù)服務(wù)器上,然后在此服務(wù)器上導(dǎo)出單張表的sql,然后把這個(gè)sql導(dǎo)入進(jìn)生產(chǎn)庫(kù)上。
在測(cè)試服務(wù)器上導(dǎo)入庫(kù):
Shell>/usr/local/mysql/bin/mysql -uroot -pmysql --database=csf </tmp/mysqlbackup/backup_20101215_adb_003.sql
?
從測(cè)試服務(wù)器導(dǎo)出單個(gè)表:
Shell>/usr/local/mysql/bin/mysqldump --user=root -pmysql --port=3306--default-character-set=utf8??--single-transaction=TRUE?"csf" "tmp_excel_data_000728" > /tmp/mysqlbackup/backup_20101215_single_table_013.sql
?
在生產(chǎn)庫(kù)上導(dǎo)入此表記錄
Shell>/usr/local/mysql/bin/mysql -uroot -pmysql csf < /tmp/mysqlbackup/backup_20101215_single_table_013.sql
檢查數(shù)據(jù)
Select* from csf. tmp_excel_data_000728;
?
(9)?????電源故障恢復(fù)
重啟服務(wù)器之后,重啟數(shù)據(jù)庫(kù),mysql自動(dòng)恢復(fù),斷電時(shí)候正在運(yùn)行的數(shù)據(jù)庫(kù)任務(wù)和操作需要重新執(zhí)行一遍。
(10) ?操作系統(tǒng)崩潰恢復(fù)
? 操作系統(tǒng)恢復(fù)之后,重啟數(shù)據(jù)庫(kù),mysql自動(dòng)恢復(fù),系統(tǒng)崩潰時(shí)正在運(yùn)行的數(shù)據(jù)庫(kù)任務(wù)以及操作需要重新執(zhí)行一遍
(11) ?文件系統(tǒng)崩潰恢復(fù)
文件系統(tǒng)恢復(fù)之后,重啟數(shù)據(jù)庫(kù)即可,系統(tǒng)崩潰時(shí)正在運(yùn)行的數(shù)據(jù)庫(kù)任務(wù)以及操作需要重新執(zhí)行一遍。
(12) ?硬件壞塊恢復(fù)
重格磁盤,然后恢復(fù)數(shù)據(jù)庫(kù)。
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號(hào)聯(lián)系: 360901061
您的支持是博主寫作最大的動(dòng)力,如果您喜歡我的文章,感覺我的文章對(duì)您有幫助,請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點(diǎn)擊下面給點(diǎn)支持吧,站長(zhǎng)非常感激您!手機(jī)微信長(zhǎng)按不能支付解決辦法:請(qǐng)將微信支付二維碼保存到相冊(cè),切換到微信,然后點(diǎn)擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
