{"id":1830,"date":"2020-07-01T06:05:05","date_gmt":"2020-06-30T22:05:05","guid":{"rendered":"http:\/\/39.97.231.10\/?p=1830"},"modified":"2020-07-01T06:05:05","modified_gmt":"2020-06-30T22:05:05","slug":"xtrabackup%e5%a4%87%e4%bb%bd%e5%b7%a5%e5%85%b7%e4%bd%bf%e7%94%a8","status":"publish","type":"post","link":"http:\/\/www.weijing.co\/?p=1830","title":{"rendered":"Xtrabackup\u5907\u4efd\u5de5\u5177\u4f7f\u7528"},"content":{"rendered":"
\nXtrabackup\u662f\u7531percona\u63d0\u4f9b\u7684mysql\u6570\u636e\u5e93\u5907\u4efd\u5de5\u5177\uff0c\u652f\u6301\u5728\u7ebf\u70ed\u5907\u4efd(\u5907\u4efd\u65f6\u4e0d\u5f71\u54cd\u6570\u636e\u8bfb\u5199).
\nXtrabackup\u6709\u4e24\u4e2a\u4e3b\u8981\u7684\u5de5\u5177\uff1axtrabackup\u3001innobackupex
\n1\u3001xtrabackup\u53ea\u80fd\u5907\u4efdInnoDB\u548cXtraDB\u4e24\u79cd\u6570\u636e\u8868\uff0c\u800c\u4e0d\u80fd\u5907\u4efdMyISAM\u6570\u636e\u8868
\n2\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
\n\u5b89\u88c5\uff1a<\/p>\n
\n
yum install http:\/\/www.percona.com\/downloads\/percona-release\/redhat\/0.1-4\/percona-release-0.1-4.noarch.rpm\nyum install percona-xtrabackup-24\n# \u53ef\u4f7f\u7528innobackupex -verion\u68c0\u67e5\u7248\u672c \u82e5\u51fa\u73b0\u7248\u672c\u53f7\u5219\u5b89\u88c5\u6210\u529f\n# innobackupex --help \u53ef\u67e5\u770b\u53c2\u6570\u5e2e\u52a9\n<\/pre>\n<\/div>\n
\u5907\u4efd\u524d\u51c6\u5907\uff1a
\n\u4fee\u6539InnoDB\u4e3a\u72ec\u7acb\u8868\u7a7a\u95f4\u6a21\u5f0f\uff0c\u5373\u5728my.cnf\u4e2d[mysqld]\u4e0b\u8bbe\u7f6einnodb_file_per_table=1
\n\u5efa\u8bae\u521b\u5efa\u4e00\u4e2a\u5355\u72ec\u7684\u5907\u4efd\u76ee\u5f55 \u4f8b\uff1a\/xbackup
\n\u5168\u5907\uff1a<\/p>\n
\n
innobackupex --defaults-file=\/etc\/my.cnf  --user=root --password=\\'yourpassword\\'  \/xbackup\n# \u51fa\u73b0completed OK!\u5219\u4ee3\u8868\u5907\u4efd\u6210\u529f\uff0c\u6587\u4ef6\u4f1a\u4fdd\u5b58\u81f3\u4e00\u4e2a\u4ee5\u65f6\u95f4\u6233\u547d\u540d\u7684\u76ee\u5f55\u5185\u3002\n<\/pre>\n<\/div>\n
\u6062\u590d\uff1a
\n\u82e5\u5168\u90e8\u6062\u590d\uff0c\u5219\u9700\u8981\u5148\u505c\u6b62mysql\u670d\u52a1\uff0c\u8fd8\u9700\u786e\u4fddmysqldata\u76ee\u5f55\u4e0b\u65e0\u6587\u4ef6<\/p>\n
\n
service mysqld stop\ninnobackupex  --apply-log  \/xbackup\/2017-09-07_09-50-11\/\n# apply-log\u79f0\u4f5c\u51c6\u5907\u9636\u6bb5\uff0c\u662f\u4e3a\u4e86\u4fdd\u6301\u6570\u636e\u4e00\u81f4\u6027\uff0c\u56de\u6eda\u5907\u4efd\u8fc7\u7a0b\u4e2d\u672a\u63d0\u4ea4\u7684\u4e8b\u52a1\uff0c\u63d0\u4ea4\u5df2\u63d0\u4ea4\u7684\u4e8b\u52a1\ninnobackupex --defaults-file=\/etc\/my.cnf  --copy-back \/xbackup\/2017-09-07_09-50-11\/\nchown -R mysql:mysql \/mysqldata\nservice mysqld start\n<\/pre>\n<\/div>\n
\u5355\u8868\u6062\u590d<\/p>\n
\n
innobackupex  --apply-log --export \/xbackup\/2017-09-07_15-53-53\/\n# \u82e5t1\u8868\u6570\u636e\u8bef\u5220 \u786e\u4fdd\u8868\u7ed3\u6784\u5b58\u5728\nALTER TABLE t1 DISCARD TABLESPACE;\ncp \/xbackup\/2017-09-07_15-53-53\/test\/t1.{ibd,exp,cfg}  \/mysqldata\/test\/\nchown -R mysql:mysql \/mysqldata\nALTER TABLE t1 IMPORT TABLESPACE;\n<\/pre>\n<\/div>\n
\u589e\u91cf\u5907\u4efd\u4e0e\u6062\u590d\uff1a<\/p>\n
\n
innobackupex --defaults-file=\/etc\/my.cnf  --user=root --password=\\'xxxxxx\\'  --no-timestamp  --incremental  \/xbackup\/inc1 --incremental-basedir=\/xbackup\/2017-09-07_09-50-11\n# \u6062\u590d\nservice mysqld stop\ninnobackupex --apply-log \/xbackup\/2017-09-07_09-50-11\/  --incremental-dir=\/xbackup\/inc1\/\ninnobackupex  --copy-back \/xbackup\/2017-09-07_09-50-11\/\nchown -R mysql:mysql \/mysqldata\nservice mysqld start<\/pre>\n<\/div>\n<\/div>\n
Xtrabackup\u5907\u4efd\u53ca\u6062\u590d\u811a\u672c<\/h1>\n
\n
\u6b64\u5907\u4efd\u811a\u672c\u7684\u7b56\u7565\u662f\u6bcf\u5468\u65e5\u548c\u5468\u4e09\u8fdb\u53bb\u5168\u5907 \u5176\u4f59\u6bcf\u5929\u589e\u91cf\u5907\u4efd\u3002<\/span><\/div>\n<\/div>\n
\n
\n
# \u5907\u4efd\u811a\u672cXtraBackup.sh\uff1a\n#!\/bin\/bash\n# filename      : XtraBackup.sh\n# Author        : wang\nday=`date +%w`\ndt=`date +%Y%m%d`\nlastday=`date -d \\'1 days ago\\' +%Y%m%d`\nuser=root\npwd=\\'xxxxx\\'\nlog=backuplog.`date +%Y%m%d`\ncase $day in\n    0)\n        # Sunday Full backup\n        find \/backup\/ -name \\\"xtra_*\\\" -mtime +6 -exec rm -rf {} \\\\;\n        innobackupex --defaults-file=\/etc\/my.cnf  --user=$user --password=$pwd --no-timestamp \/backup\/xtra_base_$dt > \/tmp\/$log 2>&1\n        ;;\n    1)\n        # Monday Relatively Sunday\\'s incremental backup\n        innobackupex --defaults-file=\/etc\/my.cnf  --user=$user --password=$pwd  --no-timestamp  --incremental  \/backup\/xtra_inc_$dt --incremental-basedir=\/backup\/xtra_base_$lastday > \/tmp\/$log 2>&1\n        ;;\n    2)\n        # Tuesday Compared with Monday\\'s incremental backup\n        innobackupex --defaults-file=\/etc\/my.cnf  --user=$user --password=$pwd  --no-timestamp  --incremental  \/backup\/xtra_inc_$dt --incremental-basedir=\/backup\/xtra_inc_$lastday > \/tmp\/$log 2>&1\n        ;;\n    3)\n        # Wednesday Full backup\n        find \/backup\/ -name \\\"xtra_*\\\" -mtime +6 -exec rm -rf {} \\\\;\n        innobackupex --defaults-file=\/etc\/my.cnf  --user=$user --password=$pwd --no-timestamp \/backup\/xtra_base_$dt > \/tmp\/$log 2>&1\n        ;;\n    4)\n        # Thursday  Relatively Wednesday\\'s incremental backup\n        innobackupex --defaults-file=\/etc\/my.cnf  --user=$user --password=$pwd  --no-timestamp  --incremental  \/backup\/xtra_inc_$dt --incremental-basedir=\/backup\/xtra_base_$lastday > \/tmp\/$log 2>&1\n        ;;\n    5)\n        # Friday Compared with Thursday\\'s incremental backup\n        innobackupex --defaults-file=\/etc\/my.cnf  --user=$user --password=$pwd  --no-timestamp  --incremental  \/backup\/xtra_inc_$dt --incremental-basedir=\/backup\/xtra_inc_$lastday > \/tmp\/$log 2>&1\n        ;;\n    6)\n        # Saturday Compared with Friday\\'s incremental backup\n        innobackupex --defaults-file=\/etc\/my.cnf  --user=$user --password=$pwd  --no-timestamp  --incremental  \/backup\/xtra_inc_$dt --incremental-basedir=\/backup\/xtra_inc_$lastday > \/tmp\/$log 2>&1\n        ;;\nesac\nfind \/tmp -mtime +6 -type f -name \\'backuplog.*\\' -exec rm -rf {} \\\\;<\/pre>\n<\/div>\n
\n
# \u5168\u5e93\u6062\u590d\u811a\u672c xtrabackup_recover.sh:\n#!\/bin\/bash\n# filename      : xtrabackup_recover.sh\n# Author        : wang\nday=`date +%w`\ndt=`date +%Y%m%d`\nlastday=`date -d \\'1 days ago\\' +%Y%m%d`\nlasttwoday=`date -d \\'2 days ago\\' +%Y%m%d`\nlastthreeday=`date -d \\'3 days ago\\' +%Y%m%d`\nuser=root\npwd=\\'xxxxxxx\\'\nlog=recoverlog.`date +%Y%m%d`\ndatefile=\/mysqldata\ncase $day in\n    0)\n        # Sunday Recover Database\n        innobackupex --apply-log  \/backup\/xtra_base_$dt > \/tmp\/$log 2>&1\n        service mysqld stop\n        rm -rf $datefile\/*\n        innobackupex --defaults-file=\/etc\/my.cnf --copy-back \/backup\/xtra_base_$dt >> \/tmp\/$log 2>&1\n        chown -R mysql:mysql $datefile\n        service mysqld start\n        binlog=`cat \/backup\/xtra_base_$dt\/xtrabackup_binlog_info|awk \\'{print $1}\\'`\n        pos=`cat \/backup\/xtra_base_$dt\/xtrabackup_binlog_info|awk \\'{print $2}\\'`\n        mysqlbinlog --no-defaults --start-position=$pos \/mysqllog\/$binlog | mysql -u$user -p$pwd\n        ;;\n    1)\n        # Monday Recover Database\n        innobackupex --apply-log --redo-only \/backup\/xtra_base_$lastday > \/tmp\/$log 2>&1\n        innobackupex --apply-log  \/backup\/xtra_base_$lastday\/ --incremental-dir=\/backup\/xtra_inc_$dt\/ >> \/tmp\/$log 2>&1\n        innobackupex --apply-log  \/backup\/xtra_base_$lastday >> \/tmp\/$log 2>&1\n        service mysqld stop\n        rm -rf $datefile\/*\n        innobackupex --defaults-file=\/etc\/my.cnf --copy-back \/backup\/xtra_base_$lastday >> \/tmp\/$log 2>&1\n        chown -R mysql:mysql $datefile\n        service mysqld start\n        binlog=`cat \/backup\/xtra_base_$lastday\/xtrabackup_binlog_info|awk \\'{print $1}\\'`\n        pos=`cat \/backup\/xtra_base_$lastday\/xtrabackup_binlog_info|awk \\'{print $2}\\'`\n        mysqlbinlog --no-defaults --start-position=$pos \/mysqllog\/$binlog | mysql -u$user -p$pwd\n        ;;\n    2)\n        # Tuesday Recover Database\n        innobackupex --apply-log --redo-only \/backup\/xtra_base_$lasttwoday > \/tmp\/$log 2>&1\n        innobackupex --apply-log --redo-only \/backup\/xtra_base_$lasttwoday\/ --incremental-dir=\/backup\/xtra_inc_$lastday\/ >> \/tmp\/$log 2>&innobackupex --apply-log  \/backup\/xtra_base_$lasttwoday\/ --incremental-dir=\/backup\/xtra_inc_$dt\/ >> \/tmp\/$log 2>&1\n        innobackupex --apply-log  \/backup\/xtra_base_$lasttwoday >> \/tmp\/$log 2>&1\n        service mysqld stop\n        rm -rf $datefile\/*\n        innobackupex --defaults-file=\/etc\/my.cnf --copy-back \/backup\/xtra_base_$lasttwoday >> \/tmp\/$log 2>&1\n        chown -R mysql:mysql $datefile\n        service mysqld start\n        binlog=`cat \/backup\/xtra_base_$lasttwoday\/xtrabackup_binlog_info|awk \\'{print $1}\\'`\n        pos=`cat \/backup\/xtra_base_$lasttwoday\/xtrabackup_binlog_info|awk \\'{print $2}\\'`\n        mysqlbinlog --no-defaults --start-position=$pos \/mysqllog\/$binlog | mysql -u$user -p$pwd\n        ;;\n    3)\n        # Wednesday Recover Database\n        innobackupex --apply-log  \/backup\/xtra_base_$dt > \/tmp\/$log 2>&1\n        service mysqld stop\n        rm -rf $datefile\/*\n        innobackupex --defaults-file=\/etc\/my.cnf --copy-back \/backup\/xtra_base_$dt >> \/tmp\/$log 2>&1\n        chown -R mysql:mysql $datefile\n        service mysqld start\n        binlog=`cat \/backup\/xtra_base_$dt\/xtrabackup_binlog_info|awk \\'{print $1}\\'`\n        pos=`cat \/backup\/xtra_base_$dt\/xtrabackup_binlog_info|awk \\'{print $2}\\'`\n        mysqlbinlog --no-defaults --start-position=$pos \/mysqllog\/$binlog | mysql -u$user -p$pwd\n        ;;\n    4)\n        # Thursday  Recover Database\n        innobackupex --apply-log --redo-only \/backup\/xtra_base_$lastday > \/tmp\/$log 2>&1\n        innobackupex --apply-log  \/backup\/xtra_base_$lastday\/ --incremental-dir=\/backup\/xtra_inc_$dt\/ >> \/tmp\/$log 2>&1\n        innobackupex --apply-log  \/backup\/xtra_base_$lastday >> \/tmp\/$log 2>&1\n        service mysqld stop\n        rm -rf $datefile\/*\n        innobackupex --defaults-file=\/etc\/my.cnf --copy-back \/backup\/xtra_base_$lastday >> \/tmp\/$log 2>&1\n        chown -R mysql:mysql $datefile\n        service mysqld start\n        binlog=`cat \/backup\/xtra_base_$lastday\/xtrabackup_binlog_info|awk \\'{print $1}\\'`\n        pos=`cat \/backup\/xtra_base_$lastday\/xtrabackup_binlog_info|awk \\'{print $2}\\'`\n        mysqlbinlog --no-defaults --start-position=$pos \/mysqllog\/$binlog | mysql -u$user -p$pwd\n        ;;\n    5)\n        # Friday Recover Database\n        innobackupex --apply-log --redo-only \/backup\/xtra_base_$lasttwoday > \/tmp\/$log 2>&1\n        innobackupex --apply-log --redo-only \/backup\/xtra_base_$lasttwoday\/ --incremental-dir=\/backup\/xtra_inc_$lastday\/ >> \/tmp\/$log 2>&innobackupex --apply-log  \/backup\/xtra_base_$lasttwoday\/ --incremental-dir=\/backup\/xtra_inc_$dt\/ >> \/tmp\/$log 2>&1\n        innobackupex --apply-log  \/backup\/xtra_base_$lasttwoday >> \/tmp\/$log 2>&1\n        service mysqld stop\n        rm -rf $datefile\/*\n        innobackupex --defaults-file=\/etc\/my.cnf --copy-back \/backup\/xtra_base_$lasttwoday >> \/tmp\/$log 2>&1\n        chown -R mysql:mysql $datefile\n        service mysqld start\n        binlog=`cat \/backup\/xtra_base_$lasttwoday\/xtrabackup_binlog_info|awk \\'{print $1}\\'`\n        pos=`cat \/backup\/xtra_base_$lasttwoday\/xtrabackup_binlog_info|awk \\'{print $2}\\'`\n        mysqlbinlog --no-defaults --start-position=$pos \/mysqllog\/$binlog | mysql -u$user -p$pwd\n        ;;\n    6)\n        # Saturday Recover Database\n        innobackupex --apply-log --redo-only \/backup\/xtra_base_$lastthreeday > \/tmp\/$log 2>&1\n        innobackupex --apply-log --redo-only \/backup\/xtra_base_$lastthreeday\/ --incremental-dir=\/backup\/xtra_inc_$lasttwoday\/ >> \/tmp\/$log 2>&1\n        innobackupex --apply-log --redo-only \/backup\/xtra_base_$lastthreeday\/ --incremental-dir=\/backup\/xtra_inc_$lastday\/ >> \/tmp\/$log 2>&1\n        innobackupex --apply-log  \/backup\/xtra_base_$lastthreeday\/ --incremental-dir=\/backup\/xtra_inc_$dt\/ >> \/tmp\/$log 2>&1\n        innobackupex --apply-log  \/backup\/xtra_base_$lastthreeday >> \/tmp\/$log 2>&1\n        service mysqld stop\n        rm -rf $datefile\/*\n        innobackupex --defaults-file=\/etc\/my.cnf --copy-back \/backup\/xtra_base_$lastthreeday >> \/tmp\/$log 2>&1\n        chown -R mysql:mysql $datefile\n        service mysqld start\n        binlog=`cat \/backup\/xtra_base_$lastthreeday\/xtrabackup_binlog_info|awk \\'{print $1}\\'`\n        pos=`cat \/backup\/xtra_base_$lastthreeday\/xtrabackup_binlog_info|awk \\'{print $2}\\'`\n        mysqlbinlog --no-defaults --start-position=$pos \/mysqllog\/$binlog | mysql -u$user -p$pwd\n        ;;\nesac\nfind \/tmp -mtime +6 -type f -name \\'recoverlog.*\\' -exec rm -rf {} \\\\;<\/pre>\n<\/div>\n<\/article>\n","protected":false},"excerpt":{"rendered":"
Xtrabackup\u662f\u7531percona\u63d0\u4f9b\u7684mysql\u6570\u636e\u5e93\u5907\u4efd\u5de5\u5177\uff0c\u652f\u6301\u5728\u7ebf\u70ed\u5907\u4efd(\u5907\u4efd\u65f6\u4e0d\u5f71\u54cd\u6570\u636e\u8bfb\u5199). …<\/p>\n
  Xtrabackup\u5907\u4efd\u5de5\u5177\u4f7f\u7528<\/span> \u67e5\u770b\u5168\u6587 »<\/a><\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":""},"categories":[90,88],"tags":[],"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false,"mailpoet_newsletter_max":false,"woocommerce_thumbnail":false,"woocommerce_single":false,"woocommerce_gallery_thumbnail":false,"shop_catalog":false,"shop_single":false,"shop_thumbnail":false},"uagb_author_info":{"display_name":"weijing","author_link":"http:\/\/www.weijing.co\/?author=4"},"uagb_comment_info":0,"uagb_excerpt":"Xtrabackup\u662f\u7531percona\u63d0\u4f9b\u7684mysql\u6570\u636e\u5e93\u5907\u4efd\u5de5\u5177\uff0c\u652f\u6301\u5728\u7ebf\u70ed\u5907\u4efd(\u5907\u4efd\u65f6\u4e0d\u5f71\u54cd\u6570\u636e\u8bfb\u5199).…","_links":{"self":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts\/1830"}],"collection":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1830"}],"version-history":[{"count":0,"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts\/1830\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1830"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1830"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1830"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}