{"id":571,"date":"2015-06-24T16:12:11","date_gmt":"2015-06-24T08:12:11","guid":{"rendered":"http:\/\/www.zyuns.com\/?p=571"},"modified":"2015-06-24T16:12:11","modified_gmt":"2015-06-24T08:12:11","slug":"mysql%e5%bc%80%e6%ba%90%e5%a4%87%e4%bb%bd%e5%b7%a5%e5%85%b7xtrabackup%e5%a4%87%e4%bb%bd%e9%83%a8%e7%bd%b2","status":"publish","type":"post","link":"https:\/\/www.siediyer.cn\/?p=571","title":{"rendered":"Mysql\u5f00\u6e90\u5907\u4efd\u5de5\u5177Xtrabackup\u5907\u4efd\u90e8\u7f72"},"content":{"rendered":"
Mysql\u5f00\u6e90\u5907\u4efd\u5de5\u5177Xtrabackup\u5907\u4efd\u90e8\u7f72<\/div>\n
<\/div>\n
\u00a0 Xtrabackup\u662f\u4e00\u4e2a\u5bf9InnoDB\u505a\u6570\u636e\u5907\u4efd\u7684\u5de5\u5177\uff0c\u652f\u6301\u5728\u7ebf\u70ed\u5907\u4efd(\u5907\u4efd\u65f6\u4e0d\u5f71\u54cd\u6570\u636e\u8bfb\u5199)\uff0c\u662f\u5546\u4e1a\u5907\u4efd\u5de5\u5177InnoDB Hotbackup\u7684\u4e00\u4e2a\u5f88\u597d\u7684\u66ff\u4ee3\u54c1\u3002<\/div>\n
\u00a0 Xtrabackup\u6709\u4e24\u4e2a\u4e3b\u8981\u7684\u5de5\u5177\uff1axtrabackup\u3001innobackupex<\/div>\n
\u3000 1\u3001xtrabackup\u53ea\u80fd\u5907\u4efdInnoDB\u548cXtraDB\u4e24\u79cd\u6570\u636e\u8868\uff0c\u800c\u4e0d\u80fd\u5907\u4efdMyISAM\u6570\u636e\u8868<\/div>\n
\u3000 2\u3001innobackupex\u662f\u53c2\u8003\u4e86InnoDB Hotbackup\u7684innoback\u811a\u672c\u4fee\u6539\u800c\u6765\u7684.innobackupex\u662f\u4e00\u4e2aperl\u811a\u672c\u5c01\u88c5\uff0c\u5c01\u88c5\u4e86xtrabackup\u3002\u4e3b\u8981\u662f\u4e3a\u4e86\u65b9\u4fbf\u7684 \u540c\u65f6\u5907\u4efdInnoDB\u548cMyISAM\u5f15\u64ce\u7684\u8868\uff0c\u4f46\u5728\u5904\u7406myisam\u65f6\u9700\u8981\u52a0\u4e00\u4e2a\u8bfb\u9501\u3002\u5e76\u4e14\u52a0\u5165\u4e86\u4e00\u4e9b\u4f7f\u7528\u7684\u9009\u9879\u3002\u5982slave-info\u53ef\u4ee5\u8bb0\u5f55\u5907\u4efd\u6062 \u590d\u540e\uff0c\u4f5c\u4e3aslave\u9700\u8981\u7684\u4e00\u4e9b\u4fe1\u606f\uff0c\u6839\u636e\u8fd9\u4e9b\u4fe1\u606f\uff0c\u53ef\u4ee5\u5f88\u65b9\u4fbf\u7684\u5229\u7528\u5907\u4efd\u6765\u91cd\u505aslave\u3002<\/div>\n
<\/div>\n
\u00a0 Xtrabackup\u4e0b\u8f7d<\/a>\u5730\u5740\uff1a<\/div>\n
\u00a0 \u00a0http:\/\/www.percona.com\/downloads\/XtraBackup\/LATEST\/binary\/<\/div>\n
<\/div>\n
\u00a0 \u9009\u62e9\u5408\u9002\u7684\u7248\u672c\uff0c\u6211\u8fd9\u91cc\u9009\u62e9binary\u3002<\/div>\n
<\/div>\n
\u00a0 \u89e3\u538b\u5230\/usr\/src\/\u76ee\u5f55\uff0c\u89e3\u538b\u540e\u76ee\u5f55\u540dpercona-xtrabackup-2.1.3\uff0c\u8fdb\u5165\u5230bin\u76ee\u5f55\uff0c\u5373\u53ef\u76f4\u63a5\u4f7f\u7528innobackupex\u547d\u4ee4<\/div>\n
\u00a0 # pwd<\/div>\n
\u00a0 \/usr\/src\/percona-xtrabackup-2.1.3\/bin<\/div>\n
\u00a0 # ls -l<\/div>\n
\u00a0 total 112396<\/div>\n
\u00a0 -rwxr-xr-x 1 root root \u00a0 110738 Jun \u00a07 11:43 innobackupex<\/div>\n
\u00a0 -rwxr-xr-x 1 root root \u00a0 110738 Jun \u00a07 11:43 innobackupex-1.5.1<\/div>\n
\u00a0 -rwxr-xr-x 1 root root \u00a02211237 Jun \u00a07 11:43 xbcrypt<\/div>\n
\u00a0 -rwxr-xr-x 1 root root \u00a02285672 Jun \u00a07 11:43 xbstream<\/div>\n
\u00a0 -rwxr-xr-x 1 root root 13033745 Jun \u00a07 11:43 xtrabackup<\/div>\n
\u00a0 -rwxr-xr-x 1 root root 16333506 Jun \u00a07 11:43 xtrabackup_55<\/div>\n
\u00a0 -rwxr-xr-x 1 root root 80988093 Jun \u00a07 11:43 xtrabackup_56<\/div>\n
<\/div>\n
\u00a0 \u5728\u5f00\u59cb\u4f7f\u7528\u7684\u65f6\u5019\u53ef\u80fd\u4f1a\u62a5\u5982\u4e0b\u9519\u8bef\uff1a<\/div>\n
\u00a0 sh: xtrabackup_55: command not found<\/div>\n
\u00a0 innobackupex: fatal error: no ‘mysql<\/a>d’ group in MySQL options<\/div>\n
<\/div>\n
<\/div>\n
\u00a0 \u89e3\u51b3\u529e\u6cd5xtrabackup_55\u590d\u5236\u5230\/usr\/bin\u4e0b\u5373\u53ef\u3002<\/div>\n
\u00a0 # cp \/usr\/src\/percona-xtrabackup-2.1.3\/bin\/xtrabackup_55 \/usr\/bin\/<\/div>\n
<\/div>\n
<\/div>\n
\u00a0 \u5efa\u7acbmysql\u5907\u4efd\u7528\u6237\uff1a<\/div>\n
\u00a0 mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ‘backupuser’@’localhost’ identified by ‘skEBfef5E2’;<\/div>\n
\u00a0 mysql> FLUSH PRIVILEGES;<\/div>\n
<\/div>\n
\u00a0 \u5907\u4efd\u811a\u672c\u5185\u5bb9\uff08\u6bcf\u5468\u4e00\u6b21\u5168\u5907\uff0c\u516d\u6b21\u589e\u91cf\u5907\u4efd\uff0c\u4fdd\u75592\u5468\u7684\u5907\u4efd\uff0c\u5e76\u538b\u7f29\uff09\uff1a<\/div>\n
<\/div>\n
[plain]<\/div>\n
#!\/bin\/bash<\/div>\n
BEGINTIME=`date +”%Y-%m-%d %H:%M:%S”`<\/div>\n
format_time=`date +”%Y-%m-%d_%H:%M:%S”`<\/div>\n
week=`date +%w`<\/div>\n
backupbin=\/usr\/src\/percona-xtrabackup-2.1.3\/bin<\/div>\n
backdir=\/data\/databasebak\/bak<\/div>\n
file_cnf=\/etc\/my.cnf<\/div>\n
user_name=backupuser<\/div>\n
password=”skEBfef5E2″<\/div>\n
db=”db1 db2 db3 db4″<\/div>\n
out_log=$backdir\/xtrabackup_log_$format_time<\/div>\n
time_cost=$backdir\/xtrabackup_time.txt<\/div>\n
<\/div>\n
if [ -f “$backdir.lastlastlastweek.gz” ];then<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 rm -rf $backdir.lastlastweek.gz<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 mv $backdir.lastweek.gz $backdir.lastlastweek.gz<\/div>\n
fi<\/div>\n
<\/div>\n
if [ -d “$backdir\/rec5” ];then<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 gzip -cr $backdir >$backdir.lastweek.gz<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 rm -rf $backdir<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 mkdir $backdir<\/div>\n
fi<\/div>\n
<\/div>\n
#full<\/div>\n
if [ ! -d “$backdir\/full” ];then<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 echo “#####start full backup at $BEGINTIME to directory full” >>$time_cost<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0$backupbin\/innobackupex –defaults-file=$file_cnf –no-timestamp –user=$user_name –password=$password –slave-info –databases=”$db” $backdir\/full 1> $out_log 2>&1<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0break;<\/div>\n
elif [ ! -d “$backdir\/rec0” ];then<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 echo “#####start 0 incremental backup at $BEGINTIME to directory rec0” >>$time_cost<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 $backupbin\/innobackupex –defaults-file=$file_cnf \u00a0–no-timestamp –user=$user_name –password=$password –slave-info –databases=”$db” –incremental –incremental-basedir=$backdir\/full $backdir\/rec0 1> $out_log 2>&1<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 break;<\/div>\n
elif [ ! -d “$backdir\/rec1” ];then<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 echo “#####start 1 incremental backup at $BEGINTIME to directory rec1” >>$time_cost<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 $backupbin\/innobackupex –defaults-file=$file_cnf \u00a0–no-timestamp –user=$user_name –password=$password –slave-info –databases=”$db” –incremental –incremental-basedir=$backdir\/rec0 $backdir\/rec1 1> $out_log 2>&1<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0break;<\/div>\n
elif [ ! -d “$backdir\/rec2” ];then<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 echo “#####start 2 incremental backup at $BEGINTIME to directory rec2” >>$time_cost<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 $backupbin\/innobackupex –defaults-file=$file_cnf \u00a0–no-timestamp –user=$user_name –password=$password –slave-info –databases=”$db” –incremental –incremental-basedir=$backdir\/rec1 $backdir\/rec2 1> $out_log 2>&1<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0break;<\/div>\n
elif [ ! -d “$backdir\/rec3” ];then<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 echo “#####start 3 incremental backup at $BEGINTIME to directory rec3” >>$time_cost<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 $backupbin\/innobackupex –defaults-file=$file_cnf \u00a0–no-timestamp –user=$user_name –password=$password –slave-info –databases=”$db” –incremental –incremental-basedir=$backdir\/rec2 $backdir\/rec3 1> $out_log 2>&1<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0break;<\/div>\n
elif [ ! -d “$backdir\/rec4” ];then<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 echo “#####start 4 incremental backup at $BEGINTIME to directory rec4” >>$time_cost<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 $backupbin\/innobackupex –defaults-file=$file_cnf \u00a0–no-timestamp –user=$user_name –password=$password –slave-info –databases=”$db” –incremental –incremental-basedir=$backdir\/rec3 $backdir\/rec4 1> $out_log 2>&1<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0break;<\/div>\n
elif [ ! -d “$backdir\/rec5” ];then<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 echo “#####start 5 incremental backup at $BEGINTIME to directory rec5” >>$time_cost<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 $backupbin\/innobackupex –defaults-file=$file_cnf \u00a0–no-timestamp –user=$user_name –password=$password –slave-info –databases=”$db” –incremental –incremental-basedir=$backdir\/rec4 $backdir\/rec5 1> $out_log 2>&1<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0break;<\/div>\n
\u00a0fi<\/div>\n
\u00a0ENDTIME=`date +”%Y-%m-%d %H:%M:%S”`<\/div>\n
\u00a0begin_data=`date -d \u00a0“$BEGINTIME” +%s`<\/div>\n
\u00a0end_data=`date -d \u00a0“$ENDTIME” +%s`<\/div>\n
\u00a0spendtime=`expr $end_data – $begin_data`<\/div>\n
\u00a0echo “it takes $spendtime sec for packing the data directory” >>$time_cost<\/div>\n
<\/div>\n
\u00a0 \u00a0\u90e8\u7f72\u5907\u4efd\u811a\u672c\uff1b<\/div>\n
\u00a0 # crontab -l<\/div>\n
\u00a0 10 2 * * * \u00a0\/opt\/cron\/mysqldbbakup.sh<\/div>\n
<\/div>\n
<\/div>\n
\u00a0 \u6062\u590d\uff1a<\/div>\n
\u00a0 #\u5168\u5907\u6062\u590d\uff1a<\/div>\n
\u00a0 innobackupex –apply-log –redo-only \u00a0–defaults-file=$file_cnf –user=$user_name –password=$password \u00a0 \u00a0$backdir\/full<\/div>\n
<\/div>\n
\u00a0 #\u589e\u5907\u6062\u590d\uff0c\u4fee\u6539\u589e\u91cf\u5907\u4efd\u76ee\u5f55\uff0c\u9010\u4e2aapply-log\uff0c\u5230\u7b2c\u4e94\u5929\u7684\u589e\u91cfrec4\u90fd\u662f\u8f93\u51fa\u90fd\u663e\u793ainnobackupex: completed OK!<\/div>\n
\u00a0 innobackupex –apply-log –redo-only –defaults-file=$file_cnf –user=$user_name –password=$password \u00a0 \u00a0$backdir\/full –incremental-dir=$backdir\/rec5<\/div>\n","protected":false},"excerpt":{"rendered":"

Mysql\u5f00\u6e90\u5907\u4efd\u5de5\u5177Xtrabackup\u5907\u4efd\u90e8\u7f72 \u00a0 Xtrabackup\u662f\u4e00\u4e2a\u5bf9InnoDB\u505a\u6570\u636e\u5907\u4efd\u7684\u5de5 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[22,19],"class_list":["post-571","post","type-post","status-publish","format-standard","hentry","category-linux","tag-mysql","tag-xtrabackup"],"_links":{"self":[{"href":"https:\/\/www.siediyer.cn\/index.php?rest_route=\/wp\/v2\/posts\/571","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.siediyer.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.siediyer.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.siediyer.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.siediyer.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=571"}],"version-history":[{"count":1,"href":"https:\/\/www.siediyer.cn\/index.php?rest_route=\/wp\/v2\/posts\/571\/revisions"}],"predecessor-version":[{"id":572,"href":"https:\/\/www.siediyer.cn\/index.php?rest_route=\/wp\/v2\/posts\/571\/revisions\/572"}],"wp:attachment":[{"href":"https:\/\/www.siediyer.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=571"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.siediyer.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=571"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.siediyer.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=571"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}