?

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?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ù)。

?

MySQL備份方案-->（利用mysqldump以及binlog二進(jìn)制日志）