{"id":364,"date":"2021-06-19T00:14:51","date_gmt":"2021-06-18T16:14:51","guid":{"rendered":"https:\/\/blog.zhouhonghe.com\/?p=364"},"modified":"2022-07-01T23:03:59","modified_gmt":"2022-07-01T15:03:59","slug":"centos7%e8%ae%be%e7%bd%aemysqldump%e8%87%aa%e5%8a%a8%e5%a4%87%e4%bb%bd","status":"publish","type":"post","link":"https:\/\/blog.zhouhonghe.com\/?p=364","title":{"rendered":"CentOS7\u8bbe\u7f6emysqldump\u81ea\u52a8\u5907\u4efd"},"content":{"rendered":"<p>1\u3001\u521b\u5efa\u5907\u4efd\u7528\u6237<br \/>\n<code>CREATE USER backup@localhost IDENTIFIED BY '<strong><em>PASSWORD<\/em><\/strong>';<br \/>\nGRANT SELECT,PROCESS,REPLICATION CLIENT,FLUSH_TABLES,EVENT ON *.* TO backup@localhost;<br \/>\nFLUSH PRIVILEGES;<br \/>\n<\/code><\/p>\n<p>2\u3001\u914d\u7f6e\u6587\u4ef6\/etc\/my.cnf\u91cc\u6dfb\u52a0\u5907\u4efd\u7528\u6237\u4fe1\u606f<br \/>\n<code>[mysqldump]<br \/>\nuser=backup<br \/>\npassword=<strong><em>PASSWORD<\/em><\/strong><br \/>\n<\/code><\/p>\n<p>3\u3001\u7f16\u8f91\u5907\u4efd\u811a\u672c<br \/>\n<code>#!\/bin\/sh<br \/>\n# File: \/path\/to\/scripts\/mysql.sh<\/p>\n<p># \u5907\u4efd\u76ee\u5f55<br \/>\nDST_DIR=\"\/path\/to\/backup\/dir\"<\/p>\n<p># \u5907\u4efd\u4fdd\u7559\u5929\u6570<br \/>\nRESERVE_DAYS=7<\/p>\n<p># \u65f6\u95f4\u683c\u5f0f<br \/>\nTIME=`date +%Y-%m-%d`<\/p>\n<p># \u5907\u4efd\u6587\u4ef6\u540d<br \/>\nFILE=\"db.$TIME.sql\"<br \/>\nZIP_FILE=\"db.$TIME.tar.gz\"<\/p>\n<p># \u5907\u4efd\u5e76\u538b\u7f29<br \/>\n\/usr\/bin\/mysqldump --all-databases --triggers --routines --events --single-transaction > $DST_DIR\/$FILE<br \/>\ncd $DST_DIR<br \/>\ntar zcf $ZIP_FILE $FILE<br \/>\nrm -f $FILE<\/p>\n<p># \u4fee\u6539\u6743\u9650<br \/>\nchmod 400 $DST_DIR\/$ZIP_FILE<\/p>\n<p># \u5220\u9664\u65e7\u5907\u4efd<br \/>\nfind $DST_DIR -name \"db.*.gz\" -type f -mtime +$RESERVE_DAYS -exec rm {} \\; &gt; \/dev\/null 2&gt;&amp;1<br \/>\n<\/code><\/p>\n<p>\u8bbe\u7f6e\u5907\u4efd\u811a\u672c\u6743\u9650<br \/>\n<code>&gt; chmod 100 \/path\/to\/scripts\/mysql.sh<br \/>\n<\/code><\/p>\n<p>4\u3001\u8bbe\u7f6e\u6bcf\u5929\u51cc\u66685\u70b9\u81ea\u52a8\u5907\u4efd<br \/>\n<code>&gt; crontab -e<\/p>\n<p>0 5 * * * \/path\/to\/scripts\/mysql.sh &amp;&gt; \/dev\/null<\/p>\n<p>&gt; systemctl reload crond<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"<p>1\u3001\u521b\u5efa\u5907\u4efd\u7528\u6237 CREATE USER backup@localhost IDENTIFIED BY 'PA [&hellip;]<\/p>... ","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-364","post","type-post","status-publish","format-standard","hentry","category-others"],"_links":{"self":[{"href":"https:\/\/blog.zhouhonghe.com\/index.php?rest_route=\/wp\/v2\/posts\/364","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.zhouhonghe.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.zhouhonghe.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.zhouhonghe.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.zhouhonghe.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=364"}],"version-history":[{"count":19,"href":"https:\/\/blog.zhouhonghe.com\/index.php?rest_route=\/wp\/v2\/posts\/364\/revisions"}],"predecessor-version":[{"id":430,"href":"https:\/\/blog.zhouhonghe.com\/index.php?rest_route=\/wp\/v2\/posts\/364\/revisions\/430"}],"wp:attachment":[{"href":"https:\/\/blog.zhouhonghe.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=364"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.zhouhonghe.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=364"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.zhouhonghe.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=364"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}