{"id":3148,"date":"2026-02-06T14:24:12","date_gmt":"2026-02-06T06:24:12","guid":{"rendered":"https:\/\/linuxjk.cn\/?p=3148"},"modified":"2026-02-06T14:24:15","modified_gmt":"2026-02-06T06:24:15","slug":"mysql_postgresql%e5%a4%87%e4%bb%bd%e5%af%bc%e5%85%a5","status":"publish","type":"post","link":"https:\/\/linuxjk.cn\/?p=3148","title":{"rendered":"mysql_postgresql\u5907\u4efd+\u5bfc\u5165"},"content":{"rendered":"\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_88 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">\u76ee\u5f55<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/linuxjk.cn\/?p=3148\/#%E8%83%8C%E6%99%AF%EF%BC%9A\" >\u80cc\u666f\uff1a<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/linuxjk.cn\/?p=3148\/#%E5%A4%87%E4%BB%BD%EF%BC%9A\" >\u5907\u4efd\uff1a<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/linuxjk.cn\/?p=3148\/#%E5%A4%87%E4%BB%BD%E8%84%9A%E6%9C%AC%E5%88%9B%E5%BB%BA%EF%BC%88mysql%EF%BC%89%EF%BC%9A\" >\u5907\u4efd\u811a\u672c\u521b\u5efa\uff08mysql\uff09\uff1a<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/linuxjk.cn\/?p=3148\/#%E5%A4%87%E4%BB%BD%E8%84%9A%E6%9C%AC%E5%88%9B%E5%BB%BA%EF%BC%88postgresql%EF%BC%89%EF%BC%9A\" >\u5907\u4efd\u811a\u672c\u521b\u5efa\uff08postgresql\uff09\uff1a<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/linuxjk.cn\/?p=3148\/#%E8%BF%90%E8%A1%8C%E5%A4%87%E4%BB%BD%E8%84%9A%E6%9C%AC\" >\u8fd0\u884c\u5907\u4efd\u811a\u672c<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/linuxjk.cn\/?p=3148\/#%E8%BF%98%E5%8E%9F%EF%BC%9A\" >\u8fd8\u539f\uff1a<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/linuxjk.cn\/?p=3148\/#%E4%BD%BF%E7%94%A8docker-compose%E9%83%A8%E7%BD%B2db%E7%8E%AF%E5%A2%83\" >\u4f7f\u7528docker-compose\u90e8\u7f72db\u73af\u5883<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/linuxjk.cn\/?p=3148\/#docker-composeyml\" >docker-compose.yml<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/linuxjk.cn\/?p=3148\/#mysql%E9%85%8D%E7%BD%AE%E6%96%87%E4%BB%B6%EF%BC%9Amycnf\" >mysql\u914d\u7f6e\u6587\u4ef6\uff1amy.cnf<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/linuxjk.cn\/?p=3148\/#%E5%90%AF%E5%8A%A8mysqlpostgresql%E5%AE%B9%E5%99%A8\" >\u542f\u52a8mysql+postgresql\u5bb9\u5668<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/linuxjk.cn\/?p=3148\/#%E8%BF%98%E5%8E%9F%E6%93%8D%E4%BD%9C%EF%BC%9A\" >\u8fd8\u539f\u64cd\u4f5c\uff1a<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/linuxjk.cn\/?p=3148\/#%E8%BF%98%E5%8E%9F%E5%90%8E%E6%A3%80%E6%9F%A5%EF%BC%9A\" >\u8fd8\u539f\u540e\u68c0\u67e5\uff1a<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-13\" href=\"https:\/\/linuxjk.cn\/?p=3148\/#%E9%99%84%EF%BC%9A%E5%AF%BC%E5%85%A5%E8%BF%87%E7%A8%8B%E4%B8%AD%E6%8E%92%E6%9F%A5%E5%91%BD%E4%BB%A4%EF%BC%9A\" >\u9644\uff1a\u5bfc\u5165\u8fc7\u7a0b\u4e2d\u6392\u67e5\u547d\u4ee4\uff1a<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-14\" href=\"https:\/\/linuxjk.cn\/?p=3148\/#%E9%99%84%EF%BC%9A%E8%BF%9C%E7%A8%8B%E6%89%A7%E8%A1%8C%E8%84%9A%E6%9C%AC\" >\u9644\uff1a\u8fdc\u7a0b\u6267\u884c\u811a\u672c<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-15\" href=\"https:\/\/linuxjk.cn\/?p=3148\/#mysql_backupsh\" >mysql_backup.sh<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-16\" href=\"https:\/\/linuxjk.cn\/?p=3148\/#postgresql_backupsh\" >postgresql_backup.sh<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-17\" href=\"https:\/\/linuxjk.cn\/?p=3148\/#%E6%89%A7%E8%A1%8C%E6%95%88%E6%9E%9C%EF%BC%9A\" >\u6267\u884c\u6548\u679c\uff1a<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-18\" href=\"https:\/\/linuxjk.cn\/?p=3148\/#%E8%BF%9C%E7%A8%8B%E6%89%A7%E8%A1%8C%E5%A4%87%E4%BB%BD%E8%84%9A%E6%9C%ACV20postgresql%EF%BC%9A\" >\u8fdc\u7a0b\u6267\u884c\u5907\u4efd\u811a\u672cV2.0(postgresql)\uff1a<\/a><ul class='ez-toc-list-level-5' ><li class='ez-toc-heading-level-5'><a class=\"ez-toc-link ez-toc-heading-19\" href=\"https:\/\/linuxjk.cn\/?p=3148\/#%E6%89%A7%E8%A1%8C%E6%95%88%E6%9E%9C%EF%BC%9A-2\" >\u6267\u884c\u6548\u679c\uff1a<\/a><\/li><\/ul><\/li><\/ul><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E8%83%8C%E6%99%AF%EF%BC%9A\"><\/span>\u80cc\u666f\uff1a<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">\u751f\u4ea7\u73af\u5883\u53d1\u7248\u524d\u9700\u8981\u5907\u4efd\u6570\u636e\u5e93\u6570\u636e\uff0c\u4e66\u5199\u811a\u672c\u7528\u4e8e\u624b\u52a8\u6267\u884c\u5907\u4efd<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>\u73af\u5883\u4fe1\u606f\u5982\u4e0b\uff1a<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>\u5bb9\u5668\u540d<\/th><th>\u4f7f\u7528\u7684\u955c\u50cf<\/th><th>\u7aef\u53e3<\/th><th>\u7248\u672c<\/th><th>\u6570\u636e\u5e93\u540d<\/th><th>\u8d26\u53f7<\/th><th>\u5bc6\u7801<\/th><\/tr><\/thead><tbody><tr><td>bim-myql<\/td><td>mysql:8.0.36-debian<\/td><td>3306<\/td><td>8.0.36<\/td><td>lowcode-bi<\/td><td>root<\/td><td>bim%2018<\/td><\/tr><tr><td>bim-postgres<\/td><td>timescale\/timescaledb-postgis:latest-pg13<\/td><td>5432<\/td><td>13<\/td><td>bim3dv2<\/td><td>postgres<\/td><td>bim%2018<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E5%A4%87%E4%BB%BD%EF%BC%9A\"><\/span>\u5907\u4efd\uff1a<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<pre class=\"wp-block-preformatted\">mkdir -p \/data\/scripts<br>\u200b<br>\u200b<br>#192.168.99.56<br>#mysql\u6570\u636e\u5e93\u5907\u4efd\u811a\u672c\u4f4d\u7f6e\uff1a\/data\/scripts\/mysql_backup.sh<br>#mysql\u6570\u636e\u5e93\u5907\u4efd\u5b58\u653e\u4f4d\u7f6e\uff1a\/data\/data_bak\/mysql\/<br>#postgresql\u6570\u636e\u5e93\u5907\u4efd\u811a\u672c\u4f4d\u7f6e\uff1a\/data\/scripts\/postgresql_backup.sh<br>#postgresql\u6570\u636e\u5e93\u5907\u4efd\u5b58\u653e\u4f4d\u7f6e\uff1a\/data\/data_bak\/postgresql\/<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E5%A4%87%E4%BB%BD%E8%84%9A%E6%9C%AC%E5%88%9B%E5%BB%BA%EF%BC%88mysql%EF%BC%89%EF%BC%9A\"><\/span>\u5907\u4efd\u811a\u672c\u521b\u5efa\uff08mysql\uff09\uff1a<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<pre class=\"wp-block-preformatted\">cat &gt; \/data\/scripts\/mysql_backup.sh  &lt;&lt; 'EOF'<br>#!\/bin\/bash<br>\u200b<br># MySQL\u6570\u636e\u5e93\u5907\u4efd\u811a\u672c<br># \u81ea\u52a8\u5907\u4efdMySQL(lowcode-bi)\u6570\u636e\u5e93<br>\u200b<br># \u989c\u8272\u5b9a\u4e49<br>RED='\\033[0;31m'<br>GREEN='\\033[0;32m'<br>YELLOW='\\033[1;33m'<br>BLUE='\\033[0;34m'<br>NC='\\033[0m' # No Color<br>\u200b<br># \u914d\u7f6e\u4fe1\u606f<br>MYSQL_CONTAINER=\"bim-myql\"<br>MYSQL_BACKUP_DIR=\"\/data\/data_bak\/mysql\"<br>\u200b<br># MySQL\u914d\u7f6e<br>MYSQL_PORT=3306<br>MYSQL_DB=\"lowcode-bi\"<br>MYSQL_USERNAME=\"root\"<br>MYSQL_PASSWORD=\"bim%2018\"<br>\u200b<br># \u751f\u6210\u65f6\u95f4\u6233<br>TIMESTAMP=$(date +\"%Y%m%d_%H%M%S\")<br>\u200b<br># \u6253\u5370\u9192\u76ee\u7684\u6807\u9898<br>print_header() {<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;echo -e \"${BLUE}================================================${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE} &nbsp; &nbsp;  MySQL\u6570\u636e\u5e93\u624b\u52a8\u5907\u4efd\u811a\u672c &nbsp; &nbsp; &nbsp; &nbsp;${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE}================================================${NC}\"<br> &nbsp; &nbsp;echo<br>}<br>\u200b<br># \u6253\u5370\u6210\u529f\u6d88\u606f<br>print_success() {<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;echo -e \"${GREEN}************************************************${NC}\"<br> &nbsp; &nbsp;echo -e \"${GREEN}$1${NC}\"<br> &nbsp; &nbsp;echo -e \"${GREEN}************************************************${NC}\"<br> &nbsp; &nbsp;echo<br>}<br>\u200b<br># \u6253\u5370\u9519\u8bef\u6d88\u606f<br>print_error() {<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;echo -e \"${RED}************************************************${NC}\"<br> &nbsp; &nbsp;echo -e \"${RED}\u9519\u8bef: $1${NC}\"<br> &nbsp; &nbsp;echo -e \"${RED}************************************************${NC}\"<br> &nbsp; &nbsp;echo<br>}<br>\u200b<br># \u6253\u5370\u8b66\u544a\u6d88\u606f<br>print_warning() {<br> &nbsp; &nbsp;echo -e \"${YELLOW}\u8b66\u544a: $1${NC}\"<br>}<br>\u200b<br># \u68c0\u67e5\u5bb9\u5668\u662f\u5426\u8fd0\u884c<br>check_container() {<br> &nbsp;  local container_name=$1<br> &nbsp; &nbsp;if ! docker ps | grep -q \"$container_name\"; then<br> &nbsp; &nbsp; &nbsp;  print_error \"\u5bb9\u5668 $container_name \u672a\u8fd0\u884c\u6216\u4e0d\u5b58\u5728\uff01\"<br> &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp;fi<br> &nbsp;  return 0<br>}<br>\u200b<br># \u521b\u5efa\u5907\u4efd\u76ee\u5f55<br>create_backup_dir() {<br> &nbsp;  local dir=$1<br> &nbsp; &nbsp;if [ ! -d \"$dir\" ]; then<br> &nbsp; &nbsp; &nbsp; &nbsp;mkdir -p \"$dir\"<br> &nbsp; &nbsp; &nbsp; &nbsp;echo -e \"\u521b\u5efa\u5907\u4efd\u76ee\u5f55: ${YELLOW}$dir${NC}\"<br> &nbsp; &nbsp;fi<br>}<br>\u200b<br># MySQL\u5907\u4efd\u51fd\u6570<br>backup_mysql() {<br> &nbsp; &nbsp;echo -e \"${BLUE}\u5f00\u59cb\u5907\u4efdMySQL\u6570\u636e\u5e93...${NC}\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u68c0\u67e5MySQL\u5bb9\u5668<br> &nbsp; &nbsp;if ! check_container \"$MYSQL_CONTAINER\"; then<br> &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u521b\u5efa\u5907\u4efd\u76ee\u5f55<br> &nbsp;  create_backup_dir \"$MYSQL_BACKUP_DIR\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u751f\u6210\u5907\u4efd\u6587\u4ef6\u540d<br> &nbsp;  local backup_file=\"${MYSQL_DB}_${TIMESTAMP}.sql\"<br> &nbsp;  local backup_path=\"${MYSQL_BACKUP_DIR}\/${backup_file}\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;echo \"\u6b63\u5728\u5907\u4efdMySQL\u6570\u636e\u5e93: $MYSQL_DB\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u6267\u884cMySQL\u5907\u4efd<br> &nbsp; &nbsp;if docker exec \"$MYSQL_CONTAINER\" mysqldump -u\"$MYSQL_USERNAME\" -p\"$MYSQL_PASSWORD\" \"$MYSQL_DB\" &gt; \"$backup_path\" 2&gt;\/dev\/null; then<br> &nbsp; &nbsp; &nbsp; &nbsp;<br> &nbsp; &nbsp; &nbsp; &nbsp;# \u68c0\u67e5\u5907\u4efd\u6587\u4ef6\u662f\u5426\u751f\u6210\u4e14\u4e0d\u4e3a\u7a7a<br> &nbsp; &nbsp; &nbsp; &nbsp;if [ -s \"$backup_path\" ]; then<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  local file_size=$(ls -lh \"$backup_path\" | awk '{print $5}')<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  print_success \"\u2705 MySQL\u5907\u4efd\u6210\u529f\u5b8c\u6210\uff01<br>\ud83d\udcc1 \u5907\u4efd\u4f4d\u7f6e: $backup_path<br>\ud83d\udcc4 \u5907\u4efd\u6587\u4ef6: $backup_file<br>\ud83d\udcca \u6587\u4ef6\u5927\u5c0f: $file_size\"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  return 0<br> &nbsp; &nbsp; &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  print_error \"MySQL\u5907\u4efd\u6587\u4ef6\u4e3a\u7a7a\uff0c\u5907\u4efd\u53ef\u80fd\u5931\u8d25\uff01\"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;rm -f \"$backup_path\" 2&gt;\/dev\/null<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp; &nbsp; &nbsp;fi<br> &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp;  print_error \"MySQL\u5907\u4efd\u5931\u8d25\uff01\u8bf7\u68c0\u67e5\u6570\u636e\u5e93\u8fde\u63a5\u548c\u6743\u9650\u3002\"<br> &nbsp; &nbsp; &nbsp; &nbsp;rm -f \"$backup_path\" 2&gt;\/dev\/null<br> &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp;fi<br>}<br>\u200b<br># \u4e3b\u51fd\u6570<br>main() {<br> &nbsp;  print_header<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;echo -e \"${BLUE}\u5f00\u59cb\u5907\u4efdMySQL\u6570\u636e\u5e93...${NC}\"<br> &nbsp; &nbsp;echo \"\ud83d\udccb \u5907\u4efd\u6e05\u5355:\"<br> &nbsp; &nbsp;echo \" &nbsp; \u2022 MySQL\u6570\u636e\u5e93: $MYSQL_DB\"<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u5907\u4efdMySQL<br> &nbsp;  backup_mysql<br> &nbsp;  local mysql_result=$?<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u603b\u7ed3\u5907\u4efd\u7ed3\u679c<br> &nbsp; &nbsp;echo -e \"${BLUE}================================================${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE} &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; \u5907\u4efd\u4efb\u52a1\u5b8c\u6210\u603b\u7ed3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE}================================================${NC}\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;if [ $mysql_result -eq 0 ]; then<br> &nbsp; &nbsp; &nbsp;  print_success \"\ud83c\udf89 MySQL\u6570\u636e\u5e93\u5907\u4efd\u5b8c\u6210\uff01<br>\u2705 MySQL (lowcode-bi): \u5907\u4efd\u6210\u529f<br>\u200b<br>\ud83d\udcc1 MySQL\u5907\u4efd\u76ee\u5f55: $MYSQL_BACKUP_DIR<br>\ud83d\udcc5 \u5907\u4efd\u65f6\u95f4\u6233: $TIMESTAMP\"<br> &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp;  print_error \"\u274c MySQL\u6570\u636e\u5e93\u5907\u4efd\u5931\u8d25\uff01<br>\u8bf7\u68c0\u67e5\uff1a<br>1. \u5bb9\u5668\u662f\u5426\u6b63\u5e38\u8fd0\u884c<br>2. \u6570\u636e\u5e93\u8fde\u63a5\u4fe1\u606f\u662f\u5426\u6b63\u786e<br>3. \u5907\u4efd\u76ee\u5f55\u6743\u9650\u662f\u5426\u6b63\u786e\"<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;echo -e \"${BLUE}\u5907\u4efd\u811a\u672c\u6267\u884c\u5b8c\u6bd5\uff01${NC}\"<br> &nbsp; &nbsp;<br> &nbsp;  return $mysql_result<br>}<br>\u200b<br># \u68c0\u67e5\u662f\u5426\u4ee5root\u7528\u6237\u8fd0\u884c<br>if [ \"$EUID\" -ne 0 ]; then<br> &nbsp;  print_error \"\u8bf7\u4ee5root\u7528\u6237\u8eab\u4efd\u8fd0\u884c\u6b64\u811a\u672c\uff01\"<br> &nbsp; &nbsp;exit 1<br>fi<br>\u200b<br># \u68c0\u67e5docker\u662f\u5426\u5b89\u88c5\u5e76\u8fd0\u884c<br>if ! command -v docker &amp;&gt; \/dev\/null; then<br> &nbsp;  print_error \"Docker\u672a\u5b89\u88c5\u6216\u4e0d\u5728PATH\u4e2d\uff01\"<br> &nbsp; &nbsp;exit 1<br>fi<br>\u200b<br>if ! docker info &amp;&gt; \/dev\/null; then<br> &nbsp;  print_error \"Docker\u670d\u52a1\u672a\u8fd0\u884c\uff01\"<br> &nbsp; &nbsp;exit 1<br>fi<br>\u200b<br># \u8fd0\u884c\u4e3b\u51fd\u6570<br>main<br>EOF<br>chmod +x \/data\/scripts\/mysql_backup.sh<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E5%A4%87%E4%BB%BD%E8%84%9A%E6%9C%AC%E5%88%9B%E5%BB%BA%EF%BC%88postgresql%EF%BC%89%EF%BC%9A\"><\/span>\u5907\u4efd\u811a\u672c\u521b\u5efa\uff08postgresql\uff09\uff1a<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<pre class=\"wp-block-preformatted\">cat &gt; \/data\/scripts\/postgresql_backup.sh &lt;&lt; 'EOF'<br>#!\/bin\/bash<br>\u200b<br># PostgreSQL\u6570\u636e\u5e93\u5907\u4efd\u811a\u672c<br># \u81ea\u52a8\u5907\u4efdPostgreSQL(bim3dv2)\u6570\u636e\u5e93<br>\u200b<br># \u989c\u8272\u5b9a\u4e49<br>RED='\\033[0;31m'<br>GREEN='\\033[0;32m'<br>YELLOW='\\033[1;33m'<br>BLUE='\\033[0;34m'<br>NC='\\033[0m' # No Color<br>\u200b<br># \u914d\u7f6e\u4fe1\u606f<br>POSTGRES_CONTAINER=\"bim-postgres\"<br>POSTGRES_BACKUP_DIR=\"\/data\/data_bak\/postgresql\"<br>\u200b<br># PostgreSQL\u914d\u7f6e<br>POSTGRESQL_PORT=5432<br>POSTGRESQL_USERNAME=\"postgres\"<br>POSTGRESQL_DATABASE=\"bim3dv2\"<br>POSTGRESQL_PASSWORD=\"bim%2018\"<br>\u200b<br># \u751f\u6210\u65f6\u95f4\u6233<br>TIMESTAMP=$(date +\"%Y%m%d_%H%M%S\")<br>\u200b<br># \u6253\u5370\u9192\u76ee\u7684\u6807\u9898<br>print_header() {<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;echo -e \"${BLUE}================================================${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE} &nbsp;  PostgreSQL\u6570\u636e\u5e93\u624b\u52a8\u5907\u4efd\u811a\u672c &nbsp; &nbsp; &nbsp; &nbsp;${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE}================================================${NC}\"<br> &nbsp; &nbsp;echo<br>}<br>\u200b<br># \u6253\u5370\u6210\u529f\u6d88\u606f<br>print_success() {<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;echo -e \"${GREEN}************************************************${NC}\"<br> &nbsp; &nbsp;echo -e \"${GREEN}$1${NC}\"<br> &nbsp; &nbsp;echo -e \"${GREEN}************************************************${NC}\"<br> &nbsp; &nbsp;echo<br>}<br>\u200b<br># \u6253\u5370\u9519\u8bef\u6d88\u606f<br>print_error() {<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;echo -e \"${RED}************************************************${NC}\"<br> &nbsp; &nbsp;echo -e \"${RED}\u9519\u8bef: $1${NC}\"<br> &nbsp; &nbsp;echo -e \"${RED}************************************************${NC}\"<br> &nbsp; &nbsp;echo<br>}<br>\u200b<br># \u6253\u5370\u8b66\u544a\u6d88\u606f<br>print_warning() {<br> &nbsp; &nbsp;echo -e \"${YELLOW}\u8b66\u544a: $1${NC}\"<br>}<br>\u200b<br># \u68c0\u67e5\u5bb9\u5668\u662f\u5426\u8fd0\u884c<br>check_container() {<br> &nbsp;  local container_name=$1<br> &nbsp; &nbsp;if ! docker ps | grep -q \"$container_name\"; then<br> &nbsp; &nbsp; &nbsp;  print_error \"\u5bb9\u5668 $container_name \u672a\u8fd0\u884c\u6216\u4e0d\u5b58\u5728\uff01\"<br> &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp;fi<br> &nbsp;  return 0<br>}<br>\u200b<br># \u521b\u5efa\u5907\u4efd\u76ee\u5f55<br>create_backup_dir() {<br> &nbsp;  local dir=$1<br> &nbsp; &nbsp;if [ ! -d \"$dir\" ]; then<br> &nbsp; &nbsp; &nbsp; &nbsp;mkdir -p \"$dir\"<br> &nbsp; &nbsp; &nbsp; &nbsp;echo -e \"\u521b\u5efa\u5907\u4efd\u76ee\u5f55: ${YELLOW}$dir${NC}\"<br> &nbsp; &nbsp;fi<br>}<br>\u200b<br># PostgreSQL\u5907\u4efd\u51fd\u6570<br>backup_postgresql() {<br> &nbsp; &nbsp;echo -e \"${BLUE}\u5f00\u59cb\u5907\u4efdPostgreSQL\u6570\u636e\u5e93...${NC}\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u68c0\u67e5PostgreSQL\u5bb9\u5668<br> &nbsp; &nbsp;if ! check_container \"$POSTGRES_CONTAINER\"; then<br> &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u521b\u5efa\u5907\u4efd\u76ee\u5f55<br> &nbsp;  create_backup_dir \"$POSTGRES_BACKUP_DIR\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u751f\u6210\u5907\u4efd\u6587\u4ef6\u540d<br> &nbsp;  local backup_file=\"${POSTGRESQL_DATABASE}_${TIMESTAMP}.sql\"<br> &nbsp;  local backup_path=\"${POSTGRES_BACKUP_DIR}\/${backup_file}\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;echo \"\u6b63\u5728\u5907\u4efdPostgreSQL\u6570\u636e\u5e93: $POSTGRESQL_DATABASE\"<br> &nbsp; &nbsp;echo \"PostgreSQL\u6570\u636e\u5e93\u8f83\u5927\uff0c\u5927\u7ea630\u5206\u949f\u5de6\u53f3\u5907\u4efd\u5b8c\u6210\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u6267\u884cPostgreSQL\u5907\u4efd<br> &nbsp; &nbsp;if docker exec -e PGPASSWORD=\"$POSTGRESQL_PASSWORD\" \"$POSTGRES_CONTAINER\" pg_dump -h localhost -p \"$POSTGRESQL_PORT\" -U \"$POSTGRESQL_USERNAME\" -d \"$POSTGRESQL_DATABASE\" &gt; \"$backup_path\" 2&gt;\/dev\/null; then<br> &nbsp; &nbsp; &nbsp; &nbsp;<br> &nbsp; &nbsp; &nbsp; &nbsp;# \u68c0\u67e5\u5907\u4efd\u6587\u4ef6\u662f\u5426\u751f\u6210\u4e14\u4e0d\u4e3a\u7a7a<br> &nbsp; &nbsp; &nbsp; &nbsp;if [ -s \"$backup_path\" ]; then<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  local file_size=$(ls -lh \"$backup_path\" | awk '{print $5}')<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  print_success \"\u2705 PostgreSQL\u5907\u4efd\u6210\u529f\u5b8c\u6210\uff01<br>\ud83d\udcc1 \u5907\u4efd\u4f4d\u7f6e: $backup_path<br>\ud83d\udcc4 \u5907\u4efd\u6587\u4ef6: $backup_file<br>\ud83d\udcca \u6587\u4ef6\u5927\u5c0f: $file_size\"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  return 0<br> &nbsp; &nbsp; &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  print_error \"PostgreSQL\u5907\u4efd\u6587\u4ef6\u4e3a\u7a7a\uff0c\u5907\u4efd\u53ef\u80fd\u5931\u8d25\uff01\"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;rm -f \"$backup_path\" 2&gt;\/dev\/null<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp; &nbsp; &nbsp;fi<br> &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp;  print_error \"PostgreSQL\u5907\u4efd\u5931\u8d25\uff01\u8bf7\u68c0\u67e5\u6570\u636e\u5e93\u8fde\u63a5\u548c\u6743\u9650\u3002\"<br> &nbsp; &nbsp; &nbsp; &nbsp;rm -f \"$backup_path\" 2&gt;\/dev\/null<br> &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp;fi<br>}<br>\u200b<br># \u4e3b\u51fd\u6570<br>main() {<br> &nbsp;  print_header<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;echo -e \"${BLUE}\u5f00\u59cb\u5907\u4efdPostgreSQL\u6570\u636e\u5e93...${NC}\"<br> &nbsp; &nbsp;echo \"\ud83d\udccb \u5907\u4efd\u6e05\u5355:\"<br> &nbsp; &nbsp;echo \" &nbsp; \u2022 PostgreSQL\u6570\u636e\u5e93: $POSTGRESQL_DATABASE\"<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u5907\u4efdPostgreSQL<br> &nbsp;  backup_postgresql<br> &nbsp;  local postgres_result=$?<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u603b\u7ed3\u5907\u4efd\u7ed3\u679c<br> &nbsp; &nbsp;echo -e \"${BLUE}================================================${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE} &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; \u5907\u4efd\u4efb\u52a1\u5b8c\u6210\u603b\u7ed3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE}================================================${NC}\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;if [ $postgres_result -eq 0 ]; then<br> &nbsp; &nbsp; &nbsp;  print_success \"\ud83c\udf89 PostgreSQL\u6570\u636e\u5e93\u5907\u4efd\u5b8c\u6210\uff01<br>\u2705 PostgreSQL (bim3dv2): \u5907\u4efd\u6210\u529f<br>\u200b<br>\ud83d\udcc1 PostgreSQL\u5907\u4efd\u76ee\u5f55: $POSTGRES_BACKUP_DIR<br>\ud83d\udcc5 \u5907\u4efd\u65f6\u95f4\u6233: $TIMESTAMP\"<br> &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp;  print_error \"\u274c PostgreSQL\u6570\u636e\u5e93\u5907\u4efd\u5931\u8d25\uff01<br>\u8bf7\u68c0\u67e5\uff1a<br>1. \u5bb9\u5668\u662f\u5426\u6b63\u5e38\u8fd0\u884c<br>2. \u6570\u636e\u5e93\u8fde\u63a5\u4fe1\u606f\u662f\u5426\u6b63\u786e<br>3. \u5907\u4efd\u76ee\u5f55\u6743\u9650\u662f\u5426\u6b63\u786e\"<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;echo -e \"${BLUE}\u5907\u4efd\u811a\u672c\u6267\u884c\u5b8c\u6bd5\uff01${NC}\"<br> &nbsp; &nbsp;<br> &nbsp;  return $postgres_result<br>}<br>\u200b<br># \u68c0\u67e5\u662f\u5426\u4ee5root\u7528\u6237\u8fd0\u884c<br>if [ \"$EUID\" -ne 0 ]; then<br> &nbsp;  print_error \"\u8bf7\u4ee5root\u7528\u6237\u8eab\u4efd\u8fd0\u884c\u6b64\u811a\u672c\uff01\"<br> &nbsp; &nbsp;exit 1<br>fi<br>\u200b<br># \u68c0\u67e5docker\u662f\u5426\u5b89\u88c5\u5e76\u8fd0\u884c<br>if ! command -v docker &amp;&gt; \/dev\/null; then<br> &nbsp;  print_error \"Docker\u672a\u5b89\u88c5\u6216\u4e0d\u5728PATH\u4e2d\uff01\"<br> &nbsp; &nbsp;exit 1<br>fi<br>\u200b<br>if ! docker info &amp;&gt; \/dev\/null; then<br> &nbsp;  print_error \"Docker\u670d\u52a1\u672a\u8fd0\u884c\uff01\"<br> &nbsp; &nbsp;exit 1<br>fi<br>\u200b<br># \u8fd0\u884c\u4e3b\u51fd\u6570<br>main<br>EOF<br>chmod +x \/data\/scripts\/postgresql_backup.sh<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E8%BF%90%E8%A1%8C%E5%A4%87%E4%BB%BD%E8%84%9A%E6%9C%AC\"><\/span>\u8fd0\u884c\u5907\u4efd\u811a\u672c<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<pre class=\"wp-block-preformatted\">#\u5907\u4efdmysql<br>bash \/data\/scripts\/mysql_backup.sh<br>#\u5907\u4efdpostgresql<br>bash \/data\/scripts\/postgresql_backup.sh<\/pre>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/typora-zp.oss-cn-shanghai.aliyuncs.com\/typora20260107110318606.png\" alt=\"4369a7ee00b9b8826476202bd7cef5c4\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E8%BF%98%E5%8E%9F%EF%BC%9A\"><\/span>\u8fd8\u539f\uff1a<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>\u9700\u8981\u5728\u53e6\u4e00\u53f0\u865a\u62df\u673a\u4e2d\u521b\u5efa\u76f8\u540c\u73af\u5883\u7684docker\u90e8\u7f72mysql+postgresql\u6d4b\u8bd5\u80fd\u5426\u4f7f\u7528\u5907\u4efd\u51fa\u6765\u7684sql\u6587\u4ef6\u8fdb\u884c\u8fd8\u539f<\/strong><\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E4%BD%BF%E7%94%A8docker-compose%E9%83%A8%E7%BD%B2db%E7%8E%AF%E5%A2%83\"><\/span><strong>\u4f7f\u7528docker-compose\u90e8\u7f72db\u73af\u5883<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<pre class=\"wp-block-preformatted\">\u76ee\u5f55\u7ed3\u6784\uff1a<br>[root@localhost db_test_zp]# tree<br>.<br>\u251c\u2500\u2500 docker-compose.yml<br>\u251c\u2500\u2500 mysql<br>\u2502&nbsp;&nbsp; \u251c\u2500\u2500 conf<br>\u2502&nbsp;&nbsp; \u2502&nbsp;&nbsp; \u251c\u2500\u2500 conf.d<br>\u2502&nbsp;&nbsp; \u2502&nbsp;&nbsp; \u2502&nbsp;&nbsp; \u251c\u2500\u2500 docker.conf<br>\u2502&nbsp;&nbsp; \u2502&nbsp;&nbsp; \u2502&nbsp;&nbsp; \u251c\u2500\u2500 my.cnf<br>\u2502&nbsp;&nbsp; \u2502&nbsp;&nbsp; \u2502&nbsp;&nbsp; \u2514\u2500\u2500 mysql.conf<br>\u2502&nbsp;&nbsp; \u2502&nbsp;&nbsp; \u2514\u2500\u2500 my.cnf<br>\u2502&nbsp;&nbsp; \u251c\u2500\u2500 logs<br>\u2502&nbsp;&nbsp; \u2502&nbsp;&nbsp; \u251c\u2500\u2500 error.log<br>\u2502&nbsp;&nbsp; \u2502&nbsp;&nbsp; \u2514\u2500\u2500 slow.log<br>\u2502&nbsp;&nbsp; \u2514\u2500\u2500 my.cnf<br>\u2514\u2500\u2500 postgres<br> &nbsp;  \u2514\u2500\u2500 conf<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"docker-composeyml\"><\/span><code>docker-compose.yml<\/code><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<pre class=\"wp-block-preformatted\">version: '3.8'<br>\u200b<br>services:<br>  mysql:<br> &nbsp;  image: mysql:8.0.36-debian<br> &nbsp;  container_name: bim-myql<br> &nbsp;  restart: unless-stopped<br> &nbsp;  ports:<br> &nbsp; &nbsp;  - \"3306:3306\"<br> &nbsp;  environment:<br> &nbsp; &nbsp;  MYSQL_ROOT_PASSWORD: \"bim%2018\"<br> &nbsp; &nbsp;  MYSQL_DATABASE: \"lowcode-bi\"<br> &nbsp;  volumes:<br> &nbsp; &nbsp;  - mysql_data:\/var\/lib\/mysql<br> &nbsp; &nbsp;  - .\/mysql:\/etc\/mysql<br> &nbsp; &nbsp;  - .\/mysql\/logs:\/var\/log\/mysql &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;# \u65e5\u5fd7\u76ee\u5f55<br> &nbsp; &nbsp; &nbsp;# \u5982\u679c\u9700\u8981\u5bfc\u5165\u6587\u4ef6\uff0c\u53ef\u4ee5\u6302\u8f7d<br> &nbsp; &nbsp; &nbsp;# - \/data\/data_bak:\/data\/backup:ro<br> &nbsp;  networks:<br> &nbsp; &nbsp;  - bim-network<br> &nbsp;  healthcheck:<br> &nbsp; &nbsp;  test: [\"CMD\", \"mysqladmin\", \"ping\", \"-h\", \"localhost\", \"-u\", \"root\", \"-pbim%2018\"]<br> &nbsp; &nbsp;  interval: 30s<br> &nbsp; &nbsp;  timeout: 10s<br> &nbsp; &nbsp;  retries: 5<br> &nbsp; &nbsp;  start_period: 60s<br>\u200b<br>  postgres:<br> &nbsp;  image: timescale\/timescaledb-postgis:latest-pg13<br> &nbsp;  container_name: bim-postgres<br> &nbsp;  restart: unless-stopped<br> &nbsp;  ports:<br> &nbsp; &nbsp;  - \"5432:5432\"<br> &nbsp;  environment:<br> &nbsp; &nbsp;  POSTGRES_DB: lowcode-bi<br> &nbsp; &nbsp;  POSTGRES_USER: bim<br> &nbsp; &nbsp;  POSTGRES_PASSWORD: \"bim%2018\"<br> &nbsp; &nbsp; &nbsp;# PostgreSQL\u5185\u5b58\u4f18\u5316<br> &nbsp; &nbsp;  POSTGRES_SHARED_BUFFERS: 2GB<br> &nbsp; &nbsp;  POSTGRES_EFFECTIVE_CACHE_SIZE: 6GB<br> &nbsp; &nbsp;  POSTGRES_WORK_MEM: 64MB<br> &nbsp;  volumes:<br> &nbsp; &nbsp;  - postgres_data:\/var\/lib\/postgresql\/data<br> &nbsp; &nbsp;  - .\/postgres\/conf:\/etc\/postgresql\/conf.d<br> &nbsp;  networks:<br> &nbsp; &nbsp;  - bim-network<br> &nbsp; &nbsp;# PostgreSQL\u8d44\u6e90\u9650\u5236<br> &nbsp;  deploy:<br> &nbsp; &nbsp;  resources:<br> &nbsp; &nbsp; &nbsp;  limits:<br> &nbsp; &nbsp; &nbsp; &nbsp;  memory: 4G<br> &nbsp; &nbsp; &nbsp; &nbsp;  cpus: '2.0'<br> &nbsp; &nbsp; &nbsp;  reservations:<br> &nbsp; &nbsp; &nbsp; &nbsp;  memory: 1G<br> &nbsp; &nbsp; &nbsp; &nbsp;  cpus: '1.0'<br> &nbsp;  healthcheck:<br> &nbsp; &nbsp;  test: [\"CMD-SHELL\", \"pg_isready -U bim -d lowcode-bi\"]<br> &nbsp; &nbsp;  interval: 30s<br> &nbsp; &nbsp;  timeout: 10s<br> &nbsp; &nbsp;  retries: 5<br>\u200b<br>volumes:<br>  mysql_data:<br> &nbsp;  driver: local<br> &nbsp;  driver_opts:<br> &nbsp; &nbsp;  type: none<br> &nbsp; &nbsp;  o: bind<br> &nbsp; &nbsp;  device: \/data\/soft\/docker\/volumes\/db_test_zp_mysql_data\/_data<br>  postgres_data:<br> &nbsp;  driver: local<br> &nbsp;  driver_opts:<br> &nbsp; &nbsp;  type: none<br> &nbsp; &nbsp;  o: bind<br> &nbsp; &nbsp;  device: \/data\/soft\/docker\/volumes\/db_test_zp_postgres_data\/_data<br>\u200b<br>networks:<br>  bim-network:<br> &nbsp;  driver: bridge<br> &nbsp;  driver_opts:<br> &nbsp; &nbsp;  com.docker.network.driver.mtu: 1500<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"mysql%E9%85%8D%E7%BD%AE%E6%96%87%E4%BB%B6%EF%BC%9Amycnf\"><\/span><code>mysql\u914d\u7f6e\u6587\u4ef6\uff1amy.cnf<\/code><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<pre class=\"wp-block-preformatted\">[root@localhost db_test_zp]# cat mysql\/conf\/conf.d\/docker.conf <br>[mysqld]<br>host_cache_size=0<br>skip-name-resolve<br>[root@localhost db_test_zp]# cat mysql\/conf\/conf.d\/my.cnf <br>[mysqld]<br>bind-address = 0.0.0.0<br>[root@localhost db_test_zp]# cat mysql\/conf\/conf.d\/mysql.conf <br>\u200b<br>[mysql]<br>[root@localhost db_test_zp]# cat mysql\/conf\/my.cnf <br>\u200b<br>[mysqld]<br>pid-file &nbsp; &nbsp; &nbsp; &nbsp;= \/var\/run\/mysqld\/mysqld.pid<br>socket &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;= \/var\/run\/mysqld\/mysqld.sock<br>datadir &nbsp; &nbsp; &nbsp; &nbsp; = \/var\/lib\/mysql<br>secure-file-priv= NULL<br>\u200b<br># Custom config should go here<br>!includedir \/etc\/mysql\/conf.d\/<br>[root@localhost db_test_zp]# <\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E5%90%AF%E5%8A%A8mysqlpostgresql%E5%AE%B9%E5%99%A8\"><\/span><strong>\u542f\u52a8mysql+postgresql\u5bb9\u5668<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><code>docker compose up -d<\/code><\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E8%BF%98%E5%8E%9F%E6%93%8D%E4%BD%9C%EF%BC%9A\"><\/span>\u8fd8\u539f\u64cd\u4f5c\uff1a<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<pre class=\"wp-block-preformatted\"># 1. \u9996\u5148\u68c0\u67e5SQL\u6587\u4ef6<br>echo \"=== \u68c0\u67e5SQL\u6587\u4ef6 ===\"<br>ls -lh \/tmp\/lowcode-bi_20260106_163829.sql<br>head -20 \/tmp\/lowcode-bi_20260106_163829.sql<br>\u200b<br># 2. \u91cd\u65b0\u521b\u5efa\u6570\u636e\u5e93<br>echo \"=== \u91cd\u65b0\u521b\u5efa\u6570\u636e\u5e93 ===\"<br>mysql -u root -pbim%2018 -e \"<br>DROP DATABASE IF EXISTS \\`lowcode-bi\\`;<br>CREATE DATABASE \\`lowcode-bi\\` <br>  DEFAULT CHARACTER SET utf8mb4 <br>  DEFAULT COLLATE utf8mb4_unicode_ci;<br>SHOW DATABASES LIKE 'lowcode-bi';<br>\"<br>\u200b<br># 3. \u8bbe\u7f6e\u4f1a\u8bdd\u53d8\u91cf\uff08\u5728\u5bfc\u5165\u524d\uff09<br>echo \"=== \u8bbe\u7f6e\u4f18\u5316\u53c2\u6570 ===\"<br>mysql -u root -pbim%2018 -e \"<br>SET GLOBAL foreign_key_checks = 0;<br>SET GLOBAL unique_checks = 0;<br>SET GLOBAL autocommit = 0;<br>SET GLOBAL innodb_flush_log_at_trx_commit = 0;<br>SET GLOBAL sync_binlog = 0;<br>SET GLOBAL local_infile = 1;<br>SHOW VARIABLES WHERE Variable_name IN ('foreign_key_checks', 'autocommit', 'local_infile');<br>\"<br>\u200b<br># 4. \u4f7f\u7528\u6700\u7b80\u5355\u7684\u5bfc\u5165\u65b9\u6cd5<br>echo \"=== \u5f00\u59cb\u5bfc\u5165 ($(date)) ===\"<br>time mysql -u root -pbim%2018 lowcode-bi &lt; \/tmp\/lowcode-bi_20260106_163829.sql<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E8%BF%98%E5%8E%9F%E5%90%8E%E6%A3%80%E6%9F%A5%EF%BC%9A\"><\/span>\u8fd8\u539f\u540e\u68c0\u67e5\uff1a<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>\u67e5\u8be2\u6570\u636e\u5e93\u5927\u5c0f<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">docker exec bim-myql mysql -u root -pbim%2018 -e \"SELECT <br>table_schema as '\u6570\u636e\u5e93',<br>COUNT(*) as '\u8868\u6570\u91cf',<br>SUM(table_rows) as '\u603b\u884c\u6570',<br>ROUND(SUM(data_length)\/1024\/1024\/1024, 2) as '\u6570\u636e\u5927\u5c0fGB',<br>ROUND(SUM(index_length)\/1024\/1024\/1024, 2) as '\u7d22\u5f15\u5927\u5c0fGB',<br>ROUND(SUM(data_length + index_length)\/1024\/1024\/1024, 2) as '\u603b\u5927\u5c0fGB'<br>FROM information_schema.tables <br>WHERE table_schema = 'lowcode-bi'<br>GROUP BY table_schema;<br>\"<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>\u68c0\u67e5\u5404\u8868\u5927\u5c0f\uff1a<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">mysql -u root -pbim%2018 -e \"<br>USE lowcode-bi;<br>SELECT <br>  table_name as '\u8868\u540d',<br>  table_rows as '\u884c\u6570',<br>  ROUND(data_length\/1024\/1024, 2) as 'MB',<br>  table_comment as '\u5907\u6ce8'<br>FROM information_schema.tables <br>WHERE table_schema = 'lowcode-bi' <br>ORDER BY data_length DESC <br>LIMIT 20;<br>\"<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>\u68c0\u67e5\u5b57\u7b26\u96c6\u914d\u7f6e\uff1a<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">docker exec bim-myql mysql -u root -pbim%2018 -e \"<br>SHOW VARIABLES LIKE '%character%';<br>SHOW VARIABLES LIKE '%collation%';<br>\"<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>\u68c0\u67e5\u7d22\u5f15\u60c5\u51b5<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">docker exec bim-myql mysql -u root -pbim%2018 -e \"<br>SELECT <br>  table_name,<br>  ROUND(data_length\/1024\/1024, 2) as 'Data_MB',<br>  ROUND(index_length\/1024\/1024, 2) as 'Index_MB',<br>  ROUND((data_length + index_length)\/1024\/1024, 2) as 'Total_MB'<br>FROM information_schema.tables <br>WHERE table_schema = 'lowcode-bi' <br>AND table_name = 'b_screen_data_bak';<br>\"<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E9%99%84%EF%BC%9A%E5%AF%BC%E5%85%A5%E8%BF%87%E7%A8%8B%E4%B8%AD%E6%8E%92%E6%9F%A5%E5%91%BD%E4%BB%A4%EF%BC%9A\"><\/span>\u9644\uff1a\u5bfc\u5165\u8fc7\u7a0b\u4e2d\u6392\u67e5\u547d\u4ee4\uff1a<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<pre class=\"wp-block-preformatted\">\u68c0\u67e5\u5bfc\u5165\u8fdb\u7a0b:<br> &nbsp;  docker exec bim-myql ps aux | grep mysql<br>  \u67e5\u770b\u8fde\u63a5\u72b6\u6001:<br> &nbsp;  docker exec bim-myql mysql -u root -pbim%2018 -e \"SHOW PROCESSLIST;\"<br>  \u68c0\u67e5\u8868\u5927\u5c0f:<br> &nbsp;  docker exec bim-myql mysql -u root -pbim%2018 -e \"SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema='lowcode-bi' ORDER BY table_rows DESC LIMIT 5;\"<\/pre>\n\n\n\n<pre class=\"wp-block-preformatted\">cat &gt; \/tmp\/mysql_monitor_final.sh &lt;&lt; 'EOF'<br>#!\/bin\/bash<br>\u200b<br># \u989c\u8272\u5b9a\u4e49<br>RED='\\033[0;31m'<br>GREEN='\\033[0;32m'<br>YELLOW='\\033[1;33m'<br>BLUE='\\033[0;34m'<br>CYAN='\\033[0;36m'<br>MAGENTA='\\033[0;35m'<br>WHITE='\\033[1;37m'<br>NC='\\033[0m'<br>\u200b<br># \u83b7\u53d6\u6570\u636e\u5e93\u5b9e\u65f6\u4fe1\u606f\u51fd\u6570<br>get_db_info() {<br> &nbsp;  local timeout=3<br> &nbsp;  timeout $timeout docker exec bim-myql mysql -u root -pbim%2018 -N -e \"<br> &nbsp;  SELECT <br> &nbsp; &nbsp; &nbsp;  COALESCE(COUNT(*), 0) as table_count,<br> &nbsp; &nbsp; &nbsp;  COALESCE(SUM(table_rows), 0) as total_rows,<br> &nbsp; &nbsp; &nbsp;  COALESCE(ROUND(SUM(data_length)\/1024\/1024\/1024, 3), 0) as data_gb,<br> &nbsp; &nbsp; &nbsp;  COALESCE(ROUND(SUM(index_length)\/1024\/1024\/1024, 3), 0) as index_gb,<br> &nbsp; &nbsp; &nbsp;  COALESCE(ROUND(SUM(data_length + index_length)\/1024\/1024\/1024, 3), 0) as total_gb<br> &nbsp;  FROM information_schema.tables <br> &nbsp;  WHERE table_schema = 'lowcode-bi';<br> &nbsp; &nbsp;\" 2&gt;\/dev\/null<br>}<br>\u200b<br># \u83b7\u53d6\u5f53\u524d\u6267\u884c\u7684SQL<br>get_current_sql() {<br> &nbsp;  local timeout=2<br> &nbsp;  timeout $timeout docker exec bim-myql mysql -u root -pbim%2018 -N -e \"<br> &nbsp;  SELECT SUBSTRING(Info, 1, 100)<br> &nbsp;  FROM information_schema.PROCESSLIST <br> &nbsp;  WHERE db = 'lowcode-bi' AND Command = 'Query' AND Info IS NOT NULL <br> &nbsp;  LIMIT 1;\" 2&gt;\/dev\/null || echo \"Idle\"<br>}<br>\u200b<br># \u68c0\u6d4b\u5bfc\u5165\u8fdb\u7a0b\u72b6\u6001 - \u7b80\u5316\u7248\u672c<br>check_import_status() {<br> &nbsp; &nbsp;# \u68c0\u67e5\u662f\u5426\u6709\u6d3b\u8dc3\u7684\u5bfc\u5165\u8fde\u63a5<br> &nbsp;  local import_connections=$(timeout 2 docker exec bim-myql mysql -u root -pbim%2018 -N -e \"<br> &nbsp;  SELECT COUNT(*) FROM information_schema.PROCESSLIST <br> &nbsp;  WHERE db = 'lowcode-bi' AND Command IN ('Query', 'Execute');\" 2&gt;\/dev\/null)<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u6e05\u7406\u8f93\u51fa\uff0c\u79fb\u9664\u6362\u884c\u7b26\u548c\u7a7a\u683c<br> &nbsp; &nbsp;import_connections=$(echo \"$import_connections\" | tr -d '\\n\\r ' || echo \"0\")<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u786e\u4fdd\u662f\u6570\u5b57<br> &nbsp; &nbsp;if ! [[ \"$import_connections\" =~ ^[0-9]+$ ]]; then<br> &nbsp; &nbsp; &nbsp; &nbsp;import_connections=0<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;echo \"$import_connections\"<br>}<br>\u200b<br># \u68c0\u67e5\u5bb9\u5668\u5185\u7684\u5bfc\u5165\u8fdb\u7a0b\u72b6\u6001<br>check_container_import_processes() {<br> &nbsp; &nbsp;echo \"=== \u5bfc\u5165\u72b6\u6001\u68c0\u6d4b ===\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u83b7\u53d6\u5bfc\u5165\u72b6\u6001<br> &nbsp;  local import_connections=$(check_import_status)<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u68c0\u67e5SQL\u6587\u4ef6\u72b6\u6001<br> &nbsp;  local file_exists=$(timeout 1 docker exec bim-myql test -f \/tmp\/lowcode-bi_20260106_163829.sql &amp;&amp; echo \"\u5b58\u5728\" || echo \"\u4e0d\u5b58\u5728\")<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;echo \"  \ud83d\udcc1 SQL\u6587\u4ef6\u72b6\u6001: $file_exists (25GB)\"<br> &nbsp; &nbsp;echo \"  \ud83d\udd17 \u6d3b\u8dc3\u6570\u636e\u5e93\u8fde\u63a5: $import_connections \u4e2a\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;if [ \"$import_connections\" -gt 0 ]; then<br> &nbsp; &nbsp; &nbsp; &nbsp;echo \"  \ud83d\udcca \u72b6\u6001: IMPORTING\"<br> &nbsp; &nbsp; &nbsp;  return 0<br> &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp; &nbsp;echo \"  \ud83d\udcca \u72b6\u6001: NO_ACTIVE_CONNECTIONS\"<br> &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp;fi<br>}<br>\u200b<br># \u83b7\u53d6MySQL\u8be6\u7ec6\u72b6\u6001<br>get_mysql_detailed_status() {<br> &nbsp;  timeout 3 docker exec bim-myql mysql -u root -pbim%2018 -N -e \"<br> &nbsp;  SELECT <br> &nbsp; &nbsp; &nbsp;  Id, User, Command, Time, State, <br> &nbsp; &nbsp; &nbsp;  CASE <br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  WHEN Info IS NULL THEN 'NULL'<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  WHEN LENGTH(Info) &gt; 80 THEN CONCAT(SUBSTRING(Info, 1, 80), '...')<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  ELSE Info<br> &nbsp; &nbsp; &nbsp;  END as Query_Info<br> &nbsp;  FROM information_schema.PROCESSLIST <br> &nbsp;  WHERE (db = 'lowcode-bi' OR Command != 'Sleep') AND Command != 'Binlog Dump'<br> &nbsp;  ORDER BY Time DESC<br> &nbsp;  LIMIT 5;<br> &nbsp; &nbsp;\" 2&gt;\/dev\/null<br>}<br>\u200b<br># \u83b7\u53d6\u6700\u5927\u7684\u51e0\u4e2a\u8868\u4fe1\u606f<br>get_largest_tables() {<br> &nbsp;  timeout 3 docker exec bim-myql mysql -u root -pbim%2018 -N -e \"<br> &nbsp;  SELECT <br> &nbsp; &nbsp; &nbsp;  table_name,<br> &nbsp; &nbsp; &nbsp;  table_rows,<br> &nbsp; &nbsp; &nbsp;  ROUND((data_length + index_length)\/1024\/1024, 1) as size_mb<br> &nbsp;  FROM information_schema.tables <br> &nbsp;  WHERE table_schema = 'lowcode-bi'<br> &nbsp;  ORDER BY (data_length + index_length) DESC<br> &nbsp;  LIMIT 5;<br> &nbsp; &nbsp;\" 2&gt;\/dev\/null<br>}<br>\u200b<br># \u4e3b\u5faa\u73af\u53d8\u91cf<br>LAST_TOTAL_GB=0<br>LAST_TOTAL_ROWS=0<br>LAST_UPDATE_TIME=$(date +%s)<br>UPDATE_SPEED_GB=0<br>UPDATE_SPEED_ROWS=0<br>NO_UPDATE_COUNT=0<br>COMPLETION_CHECK_COUNT=0<br>\u200b<br>while true; do<br> &nbsp; &nbsp;clear<br> &nbsp; &nbsp;CURRENT_TIME=$(date '+%Y-%m-%d %H:%M:%S')<br> &nbsp; &nbsp;CURRENT_TIMESTAMP=$(date +%s)<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u83b7\u53d6\u6570\u636e\u5e93\u4fe1\u606f<br> &nbsp; &nbsp;DB_INFO=$(get_db_info)<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;if [ -z \"$DB_INFO\" ]; then<br> &nbsp; &nbsp; &nbsp;  printf \"${RED}\u274c MySQL \u8fde\u63a5\u5931\u8d25\uff0c\u8bf7\u68c0\u67e5\u5bb9\u5668\u72b6\u6001${NC}\\n\"<br> &nbsp; &nbsp; &nbsp; &nbsp;sleep 5<br> &nbsp; &nbsp; &nbsp;  continue<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u89e3\u6790\u6570\u636e\uff0c\u4f7f\u7528awk\u5904\u7406\uff0c\u786e\u4fdd\u6570\u503c\u6709\u6548<br> &nbsp; &nbsp;TABLE_COUNT=$(echo \"$DB_INFO\" | awk '{print int($1)}')<br> &nbsp; &nbsp;TOTAL_ROWS=$(echo \"$DB_INFO\" | awk '{print int($2)}')<br> &nbsp; &nbsp;DATA_GB=$(echo \"$DB_INFO\" | awk '{print $3}')<br> &nbsp; &nbsp;INDEX_GB=$(echo \"$DB_INFO\" | awk '{print $4}')<br> &nbsp; &nbsp;TOTAL_GB=$(echo \"$DB_INFO\" | awk '{print $5}')<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u786e\u4fdd\u6570\u503c\u6709\u6548<br> &nbsp; &nbsp;TABLE_COUNT=${TABLE_COUNT:-0}<br> &nbsp; &nbsp;TOTAL_ROWS=${TOTAL_ROWS:-0}<br> &nbsp; &nbsp;DATA_GB=${DATA_GB:-0.000}<br> &nbsp; &nbsp;INDEX_GB=${INDEX_GB:-0.000}<br> &nbsp; &nbsp;TOTAL_GB=${TOTAL_GB:-0.000}<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u8ba1\u7b97\u53d8\u5316\u901f\u5ea6<br> &nbsp; &nbsp;TIME_DIFF=$((CURRENT_TIMESTAMP - LAST_UPDATE_TIME))<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;if [ $TIME_DIFF -gt 0 ] &amp;&amp; [ $TIME_DIFF -lt 300 ]; then &nbsp;<br> &nbsp; &nbsp; &nbsp; &nbsp;SIZE_DIFF=$(awk \"BEGIN {printf \\\"%.3f\\\", $TOTAL_GB - $LAST_TOTAL_GB}\")<br> &nbsp; &nbsp; &nbsp; &nbsp;ROWS_DIFF=$((TOTAL_ROWS - LAST_TOTAL_ROWS))<br> &nbsp; &nbsp; &nbsp; &nbsp;UPDATE_SPEED_GB=$(awk \"BEGIN {printf \\\"%.3f\\\", $SIZE_DIFF \/ $TIME_DIFF * 60}\") &nbsp;<br> &nbsp; &nbsp; &nbsp; &nbsp;<br> &nbsp; &nbsp; &nbsp; &nbsp;if [ \"$ROWS_DIFF\" -gt 0 ] &amp;&amp; [ $TIME_DIFF -gt 0 ]; then<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;UPDATE_SPEED_ROWS=$(awk \"BEGIN {printf \\\"%.0f\\\", $ROWS_DIFF \/ $TIME_DIFF}\") &nbsp;<br> &nbsp; &nbsp; &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;UPDATE_SPEED_ROWS=0<br> &nbsp; &nbsp; &nbsp; &nbsp;fi<br> &nbsp; &nbsp; &nbsp; &nbsp;<br> &nbsp; &nbsp; &nbsp; &nbsp;# \u5224\u65ad\u662f\u5426\u6709\u589e\u957f<br> &nbsp; &nbsp; &nbsp; &nbsp;if (( $(awk \"BEGIN {print ($SIZE_DIFF &gt; 0.001) ? 1 : 0}\") )) || [ \"$ROWS_DIFF\" -gt 5 ]; then<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NO_UPDATE_COUNT=0<br> &nbsp; &nbsp; &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NO_UPDATE_COUNT=$((NO_UPDATE_COUNT + 1))<br> &nbsp; &nbsp; &nbsp; &nbsp;fi<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;LAST_TOTAL_GB=$TOTAL_GB<br> &nbsp; &nbsp;LAST_TOTAL_ROWS=$TOTAL_ROWS<br> &nbsp; &nbsp;LAST_UPDATE_TIME=$CURRENT_TIMESTAMP<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u68c0\u67e5\u5bfc\u5165\u72b6\u6001<br> &nbsp; &nbsp;if check_container_import_processes &gt; \/dev\/null 2&gt;&amp;1; then<br> &nbsp; &nbsp; &nbsp; &nbsp;IMPORT_ACTIVE=1<br> &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp; &nbsp;IMPORT_ACTIVE=0<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;CURRENT_SQL=$(get_current_sql)<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u667a\u80fd\u72b6\u6001\u5224\u65ad<br> &nbsp; &nbsp;if [ \"$TABLE_COUNT\" -eq 0 ]; then<br> &nbsp; &nbsp; &nbsp; &nbsp;STATUS_ICON=\"\ud83d\udfe1\"<br> &nbsp; &nbsp; &nbsp; &nbsp;STATUS_TEXT=\"\u521d\u59cb\u5316\u4e2d\"<br> &nbsp; &nbsp; &nbsp; &nbsp;STATUS_COLOR=$YELLOW<br> &nbsp; &nbsp;elif [ \"$IMPORT_ACTIVE\" -eq 1 ]; then<br> &nbsp; &nbsp; &nbsp; &nbsp;if (( $(awk \"BEGIN {print ($UPDATE_SPEED_GB &gt; 0.001) ? 1 : 0}\") )) || [ \"$UPDATE_SPEED_ROWS\" -gt 0 ]; then<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;STATUS_ICON=\"\ud83d\udfe2\"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;STATUS_TEXT=\"\u5bfc\u5165\u6d3b\u8dc3 (\u6570\u636e\u589e\u957f\u4e2d)\"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;STATUS_COLOR=$GREEN<br> &nbsp; &nbsp; &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;STATUS_ICON=\"\ud83d\udd35\"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;STATUS_TEXT=\"\u5bfc\u5165\u8fdb\u884c\u4e2d (\u53ef\u80fd\u5728\u5904\u7406\u5927\u8868\u6216\u7d22\u5f15)\"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;STATUS_COLOR=$CYAN<br> &nbsp; &nbsp; &nbsp; &nbsp;fi<br> &nbsp; &nbsp; &nbsp; &nbsp;COMPLETION_CHECK_COUNT=0<br> &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp; &nbsp;# \u6ca1\u6709\u6d3b\u8dc3\u5bfc\u5165\u8fdb\u7a0b\uff0c\u68c0\u67e5\u662f\u5426\u5df2\u5b8c\u6210<br> &nbsp; &nbsp; &nbsp; &nbsp;if [ \"$TABLE_COUNT\" -gt 0 ] &amp;&amp; [ \"$TOTAL_ROWS\" -gt 0 ]; then<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;COMPLETION_CHECK_COUNT=$((COMPLETION_CHECK_COUNT + 1))<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;if [ \"$COMPLETION_CHECK_COUNT\" -ge 2 ]; then<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;STATUS_ICON=\"\u2705\"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;STATUS_TEXT=\"\u5bfc\u5165\u5b8c\u6210\"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;STATUS_COLOR=$GREEN<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;STATUS_ICON=\"\ud83d\udd35\"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;STATUS_TEXT=\"\u786e\u8ba4\u5b8c\u6210\u4e2d...\"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;STATUS_COLOR=$CYAN<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;fi<br> &nbsp; &nbsp; &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;STATUS_ICON=\"\ud83d\udfe0\"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;STATUS_TEXT=\"\u7b49\u5f85\u4e2d\"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;STATUS_COLOR=$YELLOW<br> &nbsp; &nbsp; &nbsp; &nbsp;fi<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550<br> &nbsp; &nbsp;# \u7ed8\u5236\u9762\u677f<br> &nbsp; &nbsp;# \u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550<br> &nbsp;  printf \"${CYAN}\u2554\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2557${NC}\\n\"<br> &nbsp;  printf \"${CYAN}\u2551${NC} &nbsp; &nbsp; ${WHITE}MySQL \u5bb9\u5668\u5bfc\u5165\u76d1\u63a7\u9762\u677f v9.2 (\u6700\u7ec8\u7248)${NC}${CYAN} &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; \u2551${NC}\\n\"<br> &nbsp;  printf \"${CYAN}\u255a\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u255d${NC}\\n\\n\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u65f6\u95f4\u4fe1\u606f<br> &nbsp;  printf \"${BLUE}\u3010\u65f6\u95f4\u3011${NC}%s (\u6bcf20\u79d2\u5237\u65b0)\\n\\n\" \"$CURRENT_TIME\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u2550\u2550\u2550 \u5bfc\u5165\u72b6\u6001 \u2550\u2550\u2550<br> &nbsp;  printf \"${BLUE}\u3010\u5bfc\u5165\u72b6\u6001\u3011${NC}\\n\"<br> &nbsp;  printf \" &nbsp;${STATUS_ICON} \u72b6\u6001: ${STATUS_COLOR}%s${NC}\\n\" \"$STATUS_TEXT\"<br> &nbsp; &nbsp;if [ \"$TABLE_COUNT\" -gt 0 ]; then<br> &nbsp; &nbsp; &nbsp;  printf \"  \ud83c\udfaf \u8868\u6570\u91cf: ${GREEN}%s${NC} \u5f20 | \u6570\u636e\u884c: ${GREEN}%s${NC} \u6761\\n\" \"$TABLE_COUNT\" \"$(echo $TOTAL_ROWS | sed ':a;s\/\\B[0-9]\\{3\\}\\&gt;\/,&amp;\/;ta')\"<br> &nbsp; &nbsp; &nbsp;  printf \"  \ud83d\udcbe \u6570\u636e\u5927\u5c0f: \u6570\u636e ${YELLOW}%.3f GB${NC} + \u7d22\u5f15 ${YELLOW}%.3f GB${NC} = ${WHITE}\u603b\u8ba1 %.3f GB${NC}\\n\" \"$DATA_GB\" \"$INDEX_GB\" \"$TOTAL_GB\"<br> &nbsp; &nbsp; &nbsp;  printf \"  \ud83d\udd04 \u5f53\u524d\u64cd\u4f5c: ${MAGENTA}%s${NC}\\n\" \"$CURRENT_SQL\"<br> &nbsp; &nbsp; &nbsp; &nbsp;<br> &nbsp; &nbsp; &nbsp; &nbsp;# \u663e\u793a\u5bfc\u5165\u901f\u5ea6\uff08\u5982\u679c\u6709\uff09<br> &nbsp; &nbsp; &nbsp; &nbsp;if [ \"$UPDATE_SPEED_ROWS\" -gt 0 ] || (( $(awk \"BEGIN {print ($UPDATE_SPEED_GB &gt; 0.001) ? 1 : 0}\") )); then<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  printf \"  \ud83d\udcc8 \u5bfc\u5165\u901f\u5ea6: \"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;if [ \"$UPDATE_SPEED_ROWS\" -gt 0 ]; then<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  printf \"${GREEN}%s \u884c\/\u79d2${NC}\" \"$UPDATE_SPEED_ROWS\"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;fi<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;if (( $(awk \"BEGIN {print ($UPDATE_SPEED_GB &gt; 0.001) ? 1 : 0}\") )); then<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  [ \"$UPDATE_SPEED_ROWS\" -gt 0 ] &amp;&amp; printf \" | \"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  printf \"${GREEN}%.3f GB\/\u5206\u949f${NC}\" \"$UPDATE_SPEED_GB\"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;fi<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  printf \"\\n\"<br> &nbsp; &nbsp; &nbsp; &nbsp;fi<br> &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp;  printf \" &nbsp;${YELLOW}\u23f3 \u521d\u59cb\u5316\u4e2d\uff0c\u6b63\u5728\u521b\u5efa\u6570\u636e\u5e93\u7ed3\u6784...${NC}\\n\"<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u2550\u2550\u2550 \u5bfc\u5165\u8fdb\u7a0b\u8be6\u60c5 \u2550\u2550\u2550<br> &nbsp;  printf \"\\n${BLUE}\u3010\u5bfc\u5165\u8fdb\u7a0b\u8be6\u60c5\u3011${NC}\\n\"<br> &nbsp;  check_container_import_processes | head -n -1<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u2550\u2550\u2550 \u6700\u5927\u8868\u4fe1\u606f \u2550\u2550\u2550<br> &nbsp; &nbsp;if [ \"$TABLE_COUNT\" -gt 0 ]; then<br> &nbsp; &nbsp; &nbsp;  printf \"\\n${BLUE}\u3010\u6570\u636e\u8868\u4fe1\u606f TOP5\u3011${NC}\\n\"<br> &nbsp; &nbsp; &nbsp; &nbsp;LARGEST_TABLES=$(get_largest_tables)<br> &nbsp; &nbsp; &nbsp; &nbsp;if [ -n \"$LARGEST_TABLES\" ]; then<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;echo \"$LARGEST_TABLES\" | while IFS=$'\\t' read -r table_name table_rows size_mb; do<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  printf \" &nbsp;  \u2022 %-30s %10s \u884c %8s MB\\n\" \"$table_name\" \"$(echo $table_rows | sed ':a;s\/\\B[0-9]\\{3\\}\\&gt;\/,&amp;\/;ta')\" \"$size_mb\"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;done<br> &nbsp; &nbsp; &nbsp; &nbsp;fi<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u2550\u2550\u2550 MySQL\u8fde\u63a5\u8be6\u60c5 \u2550\u2550\u2550<br> &nbsp;  printf \"\\n${BLUE}\u3010MySQL\u8fde\u63a5\u8be6\u60c5\u3011${NC}\\n\"<br> &nbsp; &nbsp;MYSQL_PROCESSES=$(get_mysql_detailed_status)<br> &nbsp; &nbsp;if [ -n \"$MYSQL_PROCESSES\" ]; then<br> &nbsp; &nbsp; &nbsp; &nbsp;echo \"$MYSQL_PROCESSES\" | while IFS=$'\\t' read -r id user command time state query; do<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  printf \"  \ud83d\udd17 ID:%s | \u7528\u6237:%s | \u547d\u4ee4:%s | \u65f6\u95f4:%ss\\n\" \"$id\" \"$user\" \"$command\" \"$time\"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;if [ \"$state\" != \"NULL\" ] &amp;&amp; [ -n \"$state\" ]; then<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  printf \" &nbsp; &nbsp;  \u72b6\u6001: %s\\n\" \"$state\"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;fi<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;if [ \"$query\" != \"NULL\" ] &amp;&amp; [ -n \"$query\" ]; then<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  printf \" &nbsp; &nbsp;  \u67e5\u8be2: ${CYAN}%s${NC}\\n\" \"$query\"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;fi<br> &nbsp; &nbsp; &nbsp; &nbsp;done<br> &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp;  printf \" &nbsp;${YELLOW}\u65e0\u6d3b\u8dc3\u8fde\u63a5${NC}\\n\"<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u2550\u2550\u2550 \u7cfb\u7edf\u8d44\u6e90 \u2550\u2550\u2550<br> &nbsp;  printf \"\\n${BLUE}\u3010\u7cfb\u7edf\u8d44\u6e90\u3011${NC}\\n\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u8d1f\u8f7d\u548c\u5185\u5b58<br> &nbsp; &nbsp;LOAD=$(uptime | awk -F'load average:' '{print $2}' | xargs)<br> &nbsp; &nbsp;MEMORY_INFO=$(free -h | awk 'NR==2{printf \"%s \/ %s\", $3, $2}')<br> &nbsp; &nbsp;MEMORY_PERCENT=$(free -h | awk 'NR==2{printf \"%.1f\", ($3\/$2)*100}')<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;IFS=',' read -ra LOADS &lt;&lt;&lt; \"$LOAD\"<br> &nbsp; &nbsp;LOAD1=$(printf \"%.2f\" \"${LOADS[0]}\" 2&gt;\/dev\/null || echo \"0\")<br> &nbsp; &nbsp;LOAD5=$(printf \"%.2f\" \"${LOADS[1]}\" 2&gt;\/dev\/null || echo \"0\")<br> &nbsp; &nbsp;LOAD15=$(printf \"%.2f\" \"${LOADS[2]}\" 2&gt;\/dev\/null || echo \"0\")<br> &nbsp;  printf \"  \ud83d\udd25 CPU\u8d1f\u8f7d: ${CYAN}%s${NC} (1min) | ${CYAN}%s${NC} (5min) | ${CYAN}%s${NC} (15min)\\n\" \"$LOAD1\" \"$LOAD5\" \"$LOAD15\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;if (( $(awk \"BEGIN {print (${MEMORY_PERCENT} &gt; 80) ? 1 : 0}\") )); then<br> &nbsp; &nbsp; &nbsp; &nbsp;MEM_COLOR=$RED<br> &nbsp; &nbsp;elif (( $(awk \"BEGIN {print (${MEMORY_PERCENT} &gt; 60) ? 1 : 0}\") )); then<br> &nbsp; &nbsp; &nbsp; &nbsp;MEM_COLOR=$YELLOW<br> &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp; &nbsp;MEM_COLOR=$GREEN<br> &nbsp; &nbsp;fi<br> &nbsp;  printf \"  \ud83d\udcbe \u5185\u5b58\u4f7f\u7528: ${MEM_COLOR}%s${NC} (${MEM_COLOR}%.1f%%${NC})\\n\" \"$MEMORY_INFO\" \"$MEMORY_PERCENT\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u5bb9\u5668\u8d44\u6e90<br> &nbsp; &nbsp;CONTAINER_STATS=$(timeout 3 docker stats bim-myql --no-stream --format \"{{.CPUPerc}} {{.MemUsage}} {{.MemPerc}} {{.BlockIO}}\" 2&gt;\/dev\/null)<br> &nbsp; &nbsp;if [ -n \"$CONTAINER_STATS\" ]; then<br> &nbsp; &nbsp; &nbsp; &nbsp;CPU_PERC=$(echo \"$CONTAINER_STATS\" | awk '{print $1}')<br> &nbsp; &nbsp; &nbsp; &nbsp;MEM_USAGE=$(echo \"$CONTAINER_STATS\" | awk '{print $2}')<br> &nbsp; &nbsp; &nbsp; &nbsp;MEM_PERC=$(echo \"$CONTAINER_STATS\" | awk '{print $3}')<br> &nbsp; &nbsp; &nbsp; &nbsp;BLOCK_IO=$(echo \"$CONTAINER_STATS\" | awk '{print $4}')<br> &nbsp; &nbsp; &nbsp; &nbsp;<br> &nbsp; &nbsp; &nbsp;  printf \"  \ud83d\udc33 \u5bb9\u5668\u8d44\u6e90: CPU ${GREEN}%s${NC} | \u5185\u5b58 ${GREEN}%s${NC} (%s) | \u78c1\u76d8IO ${CYAN}%s${NC}\\n\" \"$CPU_PERC\" \"$MEM_USAGE\" \"$MEM_PERC\" \"$BLOCK_IO\"<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u2550\u2550\u2550 \u63d0\u793a\u4fe1\u606f \u2550\u2550\u2550<br> &nbsp;  printf \"\\n${CYAN}\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501${NC}\\n\"<br> &nbsp; &nbsp;if [ \"$STATUS_TEXT\" = \"\u5bfc\u5165\u5b8c\u6210\" ]; then<br> &nbsp; &nbsp; &nbsp;  printf \"${GREEN}\ud83c\udf89 \u5bfc\u5165\u5df2\u5b8c\u6210\uff01\u6700\u7ec8\u6570\u636e\uff1a%.3f GB\uff0c%s \u884c\u8bb0\u5f55${NC}\\n\" \"$TOTAL_GB\" \"$(echo $TOTAL_ROWS | sed ':a;s\/\\B[0-9]\\{3\\}\\&gt;\/,&amp;\/;ta')\"<br> &nbsp; &nbsp; &nbsp;  printf \"${GREEN}\u2705 \u6240\u6709 %d \u5f20\u8868\u5df2\u6210\u529f\u5bfc\u5165${NC}\\n\" \"$TABLE_COUNT\"<br> &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp;  printf \"${GREEN}\ud83d\udcca \u667a\u80fd\u76d1\u63a7\u4e2d | \u5df2\u5bfc\u5165: %.3f GB | \u6309 Ctrl+C \u505c\u6b62${NC}\\n\" \"$TOTAL_GB\"<br> &nbsp; &nbsp;fi<br> &nbsp;  printf \"${CYAN}\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501${NC}\\n\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;sleep 20<br>done<br>EOF<br>chmod +x \/tmp\/mysql_monitor_final.sh<br>bash \/tmp\/mysql_monitor_final.sh<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E9%99%84%EF%BC%9A%E8%BF%9C%E7%A8%8B%E6%89%A7%E8%A1%8C%E8%84%9A%E6%9C%AC\"><\/span>\u9644\uff1a\u8fdc\u7a0b\u6267\u884c\u811a\u672c<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">\u539f\u811a\u672c\u5728192.168.99.56\u4e0a\u6267\u884c\uff0c\u5907\u4efd\u4fdd\u5b58\u5728\u672c\u5730\uff0c\u73b0\u5728\u9700\u8981\u5c06\u811a\u672c\u653e\u5728192.168.99.57\u4e0a\u8fdc\u7a0b\u6267\u884c\uff0c\u5c06\u5907\u4efd\u6570\u636e\u4fdd\u5b58\u5728192.168.99.57\u7684\/data\/data_bak\/\u76ee\u5f55\u4e2d<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"mysql_backupsh\"><\/span><code>mysql_backup.sh<\/code><span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<pre class=\"wp-block-preformatted\">#!\/bin\/bash<br>\u200b<br># MySQL\u6570\u636e\u5e93\u5907\u4efd\u811a\u672c - \u8fdc\u7a0b\u6267\u884c\u7248\u672c<br># \u81ea\u52a8\u5728\u8fdc\u7a0b\u670d\u52a1\u5668\u4e0a\u5907\u4efdMySQL(lowcode-bi)\u6570\u636e\u5e93\uff0c\u4fdd\u5b58\u5230\u672c\u5730<br>\u200b<br># \u989c\u8272\u5b9a\u4e49<br>RED='\\033[0;31m'<br>GREEN='\\033[0;32m'<br>YELLOW='\\033[1;33m'<br>BLUE='\\033[0;34m'<br>CYAN='\\033[0;36m'<br>NC='\\033[0m' # No Color<br>\u200b<br># \u8fdc\u7a0b\u670d\u52a1\u5668\u914d\u7f6e<br>REMOTE_HOST=\"192.168.99.56\"<br>REMOTE_USER=\"root\"<br>REMOTE_PASSWORD=\"Txpt_4app\"<br>REMOTE_PORT=\"22\"<br>\u200b<br># \u914d\u7f6e\u4fe1\u606f<br>MYSQL_CONTAINER=\"bim-myql\"<br>\u200b<br># MySQL\u914d\u7f6e<br>MYSQL_PORT=3306<br>MYSQL_DB=\"lowcode-bi\"<br>MYSQL_USERNAME=\"root\"<br>MYSQL_PASSWORD=\"bim%2018\"<br>\u200b<br># \u672c\u5730\u5907\u4efd\u5b58\u50a8\u76ee\u5f55<br>LOCAL_BACKUP_DIR=\"\/data\/data_bak\/mysql\"<br>\u200b<br># \u751f\u6210\u65f6\u95f4\u6233<br>TIMESTAMP=$(date +\"%Y%m%d_%H%M%S\")<br>\u200b<br># \u6253\u5370\u9192\u76ee\u7684\u6807\u9898<br>print_header() {<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;echo -e \"${BLUE}================================================${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE} &nbsp; MySQL\u6570\u636e\u5e93\u8fdc\u7a0b\u5907\u4efd\u811a\u672c &nbsp; &nbsp; &nbsp; &nbsp;${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE} &nbsp; \u76ee\u6807\u670d\u52a1\u5668: $REMOTE_HOST${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE}================================================${NC}\"<br> &nbsp; &nbsp;echo<br>}<br>\u200b<br># \u6253\u5370\u6210\u529f\u6d88\u606f<br>print_success() {<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;echo -e \"${GREEN}************************************************${NC}\"<br> &nbsp; &nbsp;echo -e \"${GREEN}$1${NC}\"<br> &nbsp; &nbsp;echo -e \"${GREEN}************************************************${NC}\"<br> &nbsp; &nbsp;echo<br>}<br>\u200b<br># \u6253\u5370\u9519\u8bef\u6d88\u606f<br>print_error() {<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;echo -e \"${RED}************************************************${NC}\"<br> &nbsp; &nbsp;echo -e \"${RED}\u9519\u8bef: $1${NC}\"<br> &nbsp; &nbsp;echo -e \"${RED}************************************************${NC}\"<br> &nbsp; &nbsp;echo<br>}<br>\u200b<br># \u6253\u5370\u8b66\u544a\u6d88\u606f<br>print_warning() {<br> &nbsp; &nbsp;echo -e \"${YELLOW}\u26a0\ufe0f  \u8b66\u544a: $1${NC}\"<br>}<br>\u200b<br># \u6253\u5370\u4fe1\u606f\u6d88\u606f<br>print_info() {<br> &nbsp; &nbsp;echo -e \"${BLUE}\u2139\ufe0f &nbsp;$1${NC}\"<br>}<br>\u200b<br># \u68c0\u67e5sshpass\u662f\u5426\u5b89\u88c5<br>check_sshpass() {<br> &nbsp; &nbsp;if ! command -v sshpass &amp;&gt; \/dev\/null; then<br> &nbsp; &nbsp; &nbsp;  print_error \"sshpass\u672a\u5b89\u88c5\uff01<br>\u8bf7\u5148\u8fd0\u884c\u4ee5\u4e0b\u547d\u4ee4\u5b89\u88c5sshpass:<br>  \u2022 \u5728CentOS\/RHEL\u4e0a: yum install -y sshpass<br>  \u2022 \u5728Debian\/Ubuntu\u4e0a: apt-get install -y sshpass\"<br> &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp;fi<br> &nbsp;  return 0<br>}<br>\u200b<br># \u68c0\u67e5\u8fdc\u7a0bSSH\u8fde\u63a5<br>check_remote_connection() {<br> &nbsp;  print_info \"\u6b63\u5728\u68c0\u67e5\u8fdc\u7a0b\u670d\u52a1\u5668\u8fde\u63a5...\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;if sshpass -p \"$REMOTE_PASSWORD\" ssh -o ConnectTimeout=5 -o StrictHostKeyChecking=no \\<br> &nbsp; &nbsp; &nbsp; &nbsp;-p \"$REMOTE_PORT\" \"$REMOTE_USER@$REMOTE_HOST\" \"echo 'SSH\u8fde\u63a5\u6210\u529f'\" &amp;&gt;\/dev\/null; then<br> &nbsp; &nbsp; &nbsp;  print_info \"\u8fdc\u7a0b\u670d\u52a1\u5668\u8fde\u63a5\u6210\u529f \u2713\"<br> &nbsp; &nbsp; &nbsp;  return 0<br> &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp;  print_error \"\u65e0\u6cd5\u8fde\u63a5\u5230\u8fdc\u7a0b\u670d\u52a1\u5668 $REMOTE_HOST<br>\u8bf7\u68c0\u67e5\uff1a<br>1. \u8fdc\u7a0b\u670d\u52a1\u5668\u662f\u5426\u5728\u7ebf<br>2. SSH\u670d\u52a1\u662f\u5426\u542f\u52a8<br>3. \u7528\u6237\u540d\u5bc6\u7801\u662f\u5426\u6b63\u786e<br>4. \u9632\u706b\u5899\u662f\u5426\u5f00\u653e22\u7aef\u53e3\"<br> &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp;fi<br>}<br>\u200b<br># \u8fdc\u7a0b\u6267\u884c\u547d\u4ee4<br>remote_exec() {<br> &nbsp;  local command=\"$1\"<br> &nbsp;  sshpass -p \"$REMOTE_PASSWORD\" ssh -o StrictHostKeyChecking=no \\<br> &nbsp; &nbsp; &nbsp; &nbsp;-p \"$REMOTE_PORT\" \"$REMOTE_USER@$REMOTE_HOST\" \"$command\" 2&gt;\/dev\/null<br>}<br>\u200b<br># \u68c0\u67e5\u8fdc\u7a0b\u5bb9\u5668\u662f\u5426\u8fd0\u884c<br>check_remote_container() {<br> &nbsp;  local container_name=$1<br> &nbsp;  print_info \"\u6b63\u5728\u68c0\u67e5\u8fdc\u7a0b\u5bb9\u5668: $container_name\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;if remote_exec \"docker ps | grep -q '$container_name'\"; then<br> &nbsp; &nbsp; &nbsp;  print_info \"\u8fdc\u7a0b\u5bb9\u5668\u68c0\u67e5\u6210\u529f \u2713\"<br> &nbsp; &nbsp; &nbsp;  return 0<br> &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp;  print_error \"\u8fdc\u7a0b\u5bb9\u5668 $container_name \u672a\u8fd0\u884c\u6216\u4e0d\u5b58\u5728\uff01\"<br> &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp;fi<br>}<br>\u200b<br># MySQL\u8fdc\u7a0b\u5907\u4efd\u51fd\u6570<br>backup_mysql_remote() {<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;echo -e \"${BLUE}\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE}  \u5f00\u59cb\u8fdc\u7a0b\u5907\u4efdMySQL\u6570\u636e\u5e93${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE}\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550${NC}\"<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u68c0\u67e5MySQL\u5bb9\u5668<br> &nbsp; &nbsp;if ! check_remote_container \"$MYSQL_CONTAINER\"; then<br> &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u521b\u5efa\u672c\u5730\u5907\u4efd\u76ee\u5f55<br> &nbsp; &nbsp;if [ ! -d \"$LOCAL_BACKUP_DIR\" ]; then<br> &nbsp; &nbsp; &nbsp; &nbsp;mkdir -p \"$LOCAL_BACKUP_DIR\"<br> &nbsp; &nbsp; &nbsp;  print_info \"\u521b\u5efa\u672c\u5730\u5907\u4efd\u76ee\u5f55: $LOCAL_BACKUP_DIR\"<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u751f\u6210\u5907\u4efd\u6587\u4ef6\u540d<br> &nbsp;  local backup_file=\"${MYSQL_DB}_${TIMESTAMP}.sql\"<br> &nbsp;  local local_backup_path=\"${LOCAL_BACKUP_DIR}\/${backup_file}\"<br> &nbsp; &nbsp;<br> &nbsp;  print_info \"\u5f00\u59cb\u5907\u4efd\u8fdc\u7a0bMySQL\u6570\u636e\u5e93: $MYSQL_DB\"<br> &nbsp; &nbsp;echo \"  \u2022 \u8fdc\u7a0b\u670d\u52a1\u5668: $REMOTE_HOST\"<br> &nbsp; &nbsp;echo \"  \u2022 \u5907\u4efd\u6570\u636e\u5e93: $MYSQL_DB\"<br> &nbsp; &nbsp;echo \"  \u2022 \u5907\u4efd\u6587\u4ef6: $backup_file\"<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;<br> &nbsp;  print_warning \"\u6b63\u5728\u8fdc\u7a0b\u670d\u52a1\u5668\u4e0a\u6267\u884c\u5907\u4efd\uff0c\u8bf7\u52ff\u4e2d\u65ad...\"<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u76f4\u63a5\u4ece\u8fdc\u7a0b\u670d\u52a1\u5668\u5907\u4efd\u5230\u672c\u5730\uff0c\u4e0d\u5728\u8fdc\u7a0b\u670d\u52a1\u5668\u4fdd\u5b58\u6587\u4ef6<br> &nbsp; &nbsp;if sshpass -p \"$REMOTE_PASSWORD\" ssh -o StrictHostKeyChecking=no \\<br> &nbsp; &nbsp; &nbsp; &nbsp;-p \"$REMOTE_PORT\" \"$REMOTE_USER@$REMOTE_HOST\" \\<br> &nbsp; &nbsp; &nbsp; &nbsp;\"docker exec $MYSQL_CONTAINER mysqldump -u'$MYSQL_USERNAME' -p'$MYSQL_PASSWORD' '$MYSQL_DB' 2&gt;\/dev\/null\" \\<br> &nbsp; &nbsp; &nbsp;  &gt; \"$local_backup_path\"; then<br> &nbsp; &nbsp; &nbsp; &nbsp;<br> &nbsp; &nbsp; &nbsp; &nbsp;# \u68c0\u67e5\u5907\u4efd\u6587\u4ef6\u5927\u5c0f<br> &nbsp; &nbsp; &nbsp; &nbsp;if [ -s \"$local_backup_path\" ]; then<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  local file_size=$(ls -lh \"$local_backup_path\" | awk '{print $5}')<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  print_success \"\u2705 MySQL\u5907\u4efd\u6210\u529f\u5b8c\u6210\uff01<br>\ud83d\udcc1 \u672c\u5730\u5907\u4efd\u4f4d\u7f6e: $local_backup_path<br>\ud83d\udcc4 \u5907\u4efd\u6587\u4ef6: $backup_file<br>\ud83d\udcca \u6587\u4ef6\u5927\u5c0f: $file_size<br>\u23f1\ufe0f  \u5907\u4efd\u65f6\u95f4\u6233: $TIMESTAMP\"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  return 0<br> &nbsp; &nbsp; &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  print_error \"MySQL\u5907\u4efd\u6587\u4ef6\u4e3a\u7a7a\uff0c\u5907\u4efd\u53ef\u80fd\u5931\u8d25\uff01\"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;rm -f \"$local_backup_path\" 2&gt;\/dev\/null<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp; &nbsp; &nbsp;fi<br> &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp;  print_error \"\u8fdc\u7a0bMySQL\u5907\u4efd\u5931\u8d25\uff01\u8bf7\u68c0\u67e5\uff1a<br>1. \u8fdc\u7a0b\u5bb9\u5668\u662f\u5426\u6b63\u5e38\u8fd0\u884c<br>2. \u8fdc\u7a0b\u6570\u636e\u5e93\u8fde\u63a5\u4fe1\u606f\u662f\u5426\u6b63\u786e<br>3. \u7f51\u7edc\u8fde\u63a5\u662f\u5426\u7a33\u5b9a\"<br> &nbsp; &nbsp; &nbsp; &nbsp;rm -f \"$local_backup_path\" 2&gt;\/dev\/null<br> &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp;fi<br>}<br>\u200b<br># \u663e\u793a\u5907\u4efd\u7edf\u8ba1\u4fe1\u606f<br>show_backup_statistics() {<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;echo -e \"${BLUE}\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE} &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; \u5907\u4efd\u6587\u4ef6\u7edf\u8ba1\u4fe1\u606f &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE}\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550${NC}\"<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;if [ -d \"$LOCAL_BACKUP_DIR\" ]; then<br> &nbsp; &nbsp; &nbsp; &nbsp;echo -e \"${CYAN}\u672c\u5730\u5907\u4efd\u76ee\u5f55: $LOCAL_BACKUP_DIR${NC}\"<br> &nbsp; &nbsp; &nbsp; &nbsp;echo -e \"${CYAN}\u6700\u8fd1\u5907\u4efd\u6587\u4ef6:${NC}\"<br> &nbsp; &nbsp; &nbsp; &nbsp;ls -lh \"$LOCAL_BACKUP_DIR\" | tail -5 | awk '{if(NR&gt;1) printf \"  \u2022 %-40s %6s\\n\", $9, $5}'<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;echo<br>}<br>\u200b<br># \u4e3b\u51fd\u6570<br>main() {<br> &nbsp;  print_header<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;echo -e \"${BLUE}\u51c6\u5907\u6267\u884c\u8fdc\u7a0bMySQL\u5907\u4efd\u4efb\u52a1...${NC}\"<br> &nbsp; &nbsp;echo \"\ud83d\udccb \u5907\u4efd\u914d\u7f6e:\"<br> &nbsp; &nbsp;echo \" &nbsp; \u2022 \u672c\u5730\u670d\u52a1\u5668: $(hostname -I | awk '{print $1}')\"<br> &nbsp; &nbsp;echo \" &nbsp; \u2022 \u8fdc\u7a0b\u670d\u52a1\u5668: $REMOTE_HOST\"<br> &nbsp; &nbsp;echo \" &nbsp; \u2022 \u8fdc\u7a0b\u6570\u636e\u5e93: $MYSQL_DB\"<br> &nbsp; &nbsp;echo \" &nbsp; \u2022 \u672c\u5730\u5907\u4efd\u76ee\u5f55: $LOCAL_BACKUP_DIR\"<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u68c0\u67e5sshpass<br> &nbsp; &nbsp;if ! check_sshpass; then<br> &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u68c0\u67e5\u8fdc\u7a0b\u8fde\u63a5<br> &nbsp; &nbsp;if ! check_remote_connection; then<br> &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u6267\u884c\u8fdc\u7a0b\u5907\u4efd<br> &nbsp;  backup_mysql_remote<br> &nbsp;  local backup_result=$?<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u603b\u7ed3\u5907\u4efd\u7ed3\u679c<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;echo -e \"${BLUE}\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE} &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; \u5907\u4efd\u4efb\u52a1\u5b8c\u6210\u603b\u7ed3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE}\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550${NC}\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;if [ $backup_result -eq 0 ]; then<br> &nbsp; &nbsp; &nbsp;  print_success \"\ud83c\udf89 MySQL\u6570\u636e\u5e93\u8fdc\u7a0b\u5907\u4efd\u5b8c\u6210\uff01<br>\u2705 \u8fdc\u7a0b\u5907\u4efd: \u6210\u529f ($REMOTE_HOST)<br>\u2705 \u672c\u5730\u4fdd\u5b58: \u6210\u529f<br>\u200b<br>\ud83d\udcc1 \u672c\u5730\u5907\u4efd\u76ee\u5f55: $LOCAL_BACKUP_DIR<br>\ud83d\udcc5 \u5907\u4efd\u65f6\u95f4\u6233: $TIMESTAMP\"<br> &nbsp; &nbsp; &nbsp; &nbsp;<br> &nbsp; &nbsp; &nbsp; &nbsp;# \u663e\u793a\u5907\u4efd\u7edf\u8ba1<br> &nbsp; &nbsp; &nbsp;  show_backup_statistics<br> &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp;  print_error \"\u274c MySQL\u6570\u636e\u5e93\u8fdc\u7a0b\u5907\u4efd\u5931\u8d25\uff01<br>\u8bf7\u68c0\u67e5\uff1a<br>1. \u8fdc\u7a0b\u670d\u52a1\u5668\u662f\u5426\u5728\u7ebf\u548cSSH\u53ef\u8fbe<br>2. \u8fdc\u7a0b\u5bb9\u5668\u662f\u5426\u6b63\u5e38\u8fd0\u884c<br>3. \u8fdc\u7a0b\u6570\u636e\u5e93\u8fde\u63a5\u4fe1\u606f\u662f\u5426\u6b63\u786e<br>4. \u672c\u5730\u5907\u4efd\u76ee\u5f55\u6743\u9650\u662f\u5426\u6b63\u786e\"<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;echo -e \"${BLUE}\u8fdc\u7a0b\u5907\u4efd\u811a\u672c\u6267\u884c\u5b8c\u6bd5\uff01${NC}\"<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;<br> &nbsp;  return $backup_result<br>}<br>\u200b<br># \u68c0\u67e5\u662f\u5426\u4ee5root\u7528\u6237\u8fd0\u884c<br>if [ \"$EUID\" -ne 0 ]; then<br> &nbsp;  print_error \"\u8bf7\u4ee5root\u7528\u6237\u8eab\u4efd\u8fd0\u884c\u6b64\u811a\u672c\uff01\"<br> &nbsp; &nbsp;exit 1<br>fi<br>\u200b<br># \u8fd0\u884c\u4e3b\u51fd\u6570<br>main<\/pre>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"postgresql_backupsh\"><\/span><strong>postgresql_backup.sh<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<pre class=\"wp-block-preformatted\">#!\/bin\/bash<br>\u200b<br># PostgreSQL\u6570\u636e\u5e93\u5907\u4efd\u811a\u672c - \u8fdc\u7a0b\u6267\u884c\u7248\u672c<br># \u81ea\u52a8\u5728\u8fdc\u7a0b\u670d\u52a1\u5668\u4e0a\u5907\u4efdPostgreSQL(bim3dv2)\u6570\u636e\u5e93\uff0c\u4fdd\u5b58\u5230\u672c\u5730<br>\u200b<br># \u989c\u8272\u5b9a\u4e49<br>RED='\\033[0;31m'<br>GREEN='\\033[0;32m'<br>YELLOW='\\033[1;33m'<br>BLUE='\\033[0;34m'<br>CYAN='\\033[0;36m'<br>NC='\\033[0m' # No Color<br>\u200b<br># \u8fdc\u7a0b\u670d\u52a1\u5668\u914d\u7f6e<br>REMOTE_HOST=\"192.168.99.56\"<br>REMOTE_USER=\"root\"<br>REMOTE_PASSWORD=\"Txpt_4app\"<br>REMOTE_PORT=\"22\"<br>\u200b<br># \u914d\u7f6e\u4fe1\u606f<br>POSTGRES_CONTAINER=\"bim-postgres\"<br>\u200b<br># PostgreSQL\u914d\u7f6e<br>POSTGRESQL_PORT=5432<br>POSTGRESQL_USERNAME=\"postgres\"<br>POSTGRESQL_DATABASE=\"bim3dv2\"<br>POSTGRESQL_PASSWORD=\"bim%2018\"<br>\u200b<br># \u672c\u5730\u5907\u4efd\u5b58\u50a8\u76ee\u5f55<br>LOCAL_BACKUP_DIR=\"\/data\/data_bak\/postgresql\"<br>\u200b<br># \u751f\u6210\u65f6\u95f4\u6233<br>TIMESTAMP=$(date +\"%Y%m%d_%H%M%S\")<br>\u200b<br># \u6253\u5370\u9192\u76ee\u7684\u6807\u9898<br>print_header() {<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;echo -e \"${BLUE}================================================${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE} &nbsp; PostgreSQL\u6570\u636e\u5e93\u8fdc\u7a0b\u5907\u4efd\u811a\u672c &nbsp; &nbsp; &nbsp; &nbsp;${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE} &nbsp; \u76ee\u6807\u670d\u52a1\u5668: $REMOTE_HOST${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE}================================================${NC}\"<br> &nbsp; &nbsp;echo<br>}<br>\u200b<br># \u6253\u5370\u6210\u529f\u6d88\u606f<br>print_success() {<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;echo -e \"${GREEN}************************************************${NC}\"<br> &nbsp; &nbsp;echo -e \"${GREEN}$1${NC}\"<br> &nbsp; &nbsp;echo -e \"${GREEN}************************************************${NC}\"<br> &nbsp; &nbsp;echo<br>}<br>\u200b<br># \u6253\u5370\u9519\u8bef\u6d88\u606f<br>print_error() {<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;echo -e \"${RED}************************************************${NC}\"<br> &nbsp; &nbsp;echo -e \"${RED}\u9519\u8bef: $1${NC}\"<br> &nbsp; &nbsp;echo -e \"${RED}************************************************${NC}\"<br> &nbsp; &nbsp;echo<br>}<br>\u200b<br># \u6253\u5370\u8b66\u544a\u6d88\u606f<br>print_warning() {<br> &nbsp; &nbsp;echo -e \"${YELLOW}\u26a0\ufe0f  \u8b66\u544a: $1${NC}\"<br>}<br>\u200b<br># \u6253\u5370\u4fe1\u606f\u6d88\u606f<br>print_info() {<br> &nbsp; &nbsp;echo -e \"${BLUE}\u2139\ufe0f &nbsp;$1${NC}\"<br>}<br>\u200b<br># \u68c0\u67e5sshpass\u662f\u5426\u5b89\u88c5<br>check_sshpass() {<br> &nbsp; &nbsp;if ! command -v sshpass &amp;&gt; \/dev\/null; then<br> &nbsp; &nbsp; &nbsp;  print_error \"sshpass\u672a\u5b89\u88c5\uff01<br>\u8bf7\u5148\u8fd0\u884c\u4ee5\u4e0b\u547d\u4ee4\u5b89\u88c5sshpass:<br>  \u2022 \u5728CentOS\/RHEL\u4e0a: yum install -y sshpass<br>  \u2022 \u5728Debian\/Ubuntu\u4e0a: apt-get install -y sshpass\"<br> &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp;fi<br> &nbsp;  return 0<br>}<br>\u200b<br># \u68c0\u67e5\u8fdc\u7a0bSSH\u8fde\u63a5<br>check_remote_connection() {<br> &nbsp;  print_info \"\u6b63\u5728\u68c0\u67e5\u8fdc\u7a0b\u670d\u52a1\u5668\u8fde\u63a5...\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;if sshpass -p \"$REMOTE_PASSWORD\" ssh -o ConnectTimeout=5 -o StrictHostKeyChecking=no \\<br> &nbsp; &nbsp; &nbsp; &nbsp;-p \"$REMOTE_PORT\" \"$REMOTE_USER@$REMOTE_HOST\" \"echo 'SSH\u8fde\u63a5\u6210\u529f'\" &amp;&gt;\/dev\/null; then<br> &nbsp; &nbsp; &nbsp;  print_info \"\u8fdc\u7a0b\u670d\u52a1\u5668\u8fde\u63a5\u6210\u529f \u2713\"<br> &nbsp; &nbsp; &nbsp;  return 0<br> &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp;  print_error \"\u65e0\u6cd5\u8fde\u63a5\u5230\u8fdc\u7a0b\u670d\u52a1\u5668 $REMOTE_HOST<br>\u8bf7\u68c0\u67e5\uff1a<br>1. \u8fdc\u7a0b\u670d\u52a1\u5668\u662f\u5426\u5728\u7ebf<br>2. SSH\u670d\u52a1\u662f\u5426\u542f\u52a8<br>3. \u7528\u6237\u540d\u5bc6\u7801\u662f\u5426\u6b63\u786e<br>4. \u9632\u706b\u5899\u662f\u5426\u5f00\u653e22\u7aef\u53e3\"<br> &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp;fi<br>}<br>\u200b<br># \u8fdc\u7a0b\u6267\u884c\u547d\u4ee4<br>remote_exec() {<br> &nbsp;  local command=\"$1\"<br> &nbsp;  sshpass -p \"$REMOTE_PASSWORD\" ssh -o StrictHostKeyChecking=no \\<br> &nbsp; &nbsp; &nbsp; &nbsp;-p \"$REMOTE_PORT\" \"$REMOTE_USER@$REMOTE_HOST\" \"$command\" 2&gt;\/dev\/null<br>}<br>\u200b<br># \u68c0\u67e5\u8fdc\u7a0b\u5bb9\u5668\u662f\u5426\u8fd0\u884c<br>check_remote_container() {<br> &nbsp;  local container_name=$1<br> &nbsp;  print_info \"\u6b63\u5728\u68c0\u67e5\u8fdc\u7a0b\u5bb9\u5668: $container_name\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;if remote_exec \"docker ps | grep -q '$container_name'\"; then<br> &nbsp; &nbsp; &nbsp;  print_info \"\u8fdc\u7a0b\u5bb9\u5668\u68c0\u67e5\u6210\u529f \u2713\"<br> &nbsp; &nbsp; &nbsp;  return 0<br> &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp;  print_error \"\u8fdc\u7a0b\u5bb9\u5668 $container_name \u672a\u8fd0\u884c\u6216\u4e0d\u5b58\u5728\uff01\"<br> &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp;fi<br>}<br>\u200b<br># PostgreSQL\u8fdc\u7a0b\u5907\u4efd\u51fd\u6570<br>backup_postgresql_remote() {<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;echo -e \"${BLUE}\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE}  \u5f00\u59cb\u8fdc\u7a0b\u5907\u4efdPostgreSQL\u6570\u636e\u5e93${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE}\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550${NC}\"<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u68c0\u67e5PostgreSQL\u5bb9\u5668<br> &nbsp; &nbsp;if ! check_remote_container \"$POSTGRES_CONTAINER\"; then<br> &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u521b\u5efa\u672c\u5730\u5907\u4efd\u76ee\u5f55<br> &nbsp; &nbsp;if [ ! -d \"$LOCAL_BACKUP_DIR\" ]; then<br> &nbsp; &nbsp; &nbsp; &nbsp;mkdir -p \"$LOCAL_BACKUP_DIR\"<br> &nbsp; &nbsp; &nbsp;  print_info \"\u521b\u5efa\u672c\u5730\u5907\u4efd\u76ee\u5f55: $LOCAL_BACKUP_DIR\"<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u751f\u6210\u5907\u4efd\u6587\u4ef6\u540d<br> &nbsp;  local backup_file=\"${POSTGRESQL_DATABASE}_${TIMESTAMP}.sql\"<br> &nbsp;  local local_backup_path=\"${LOCAL_BACKUP_DIR}\/${backup_file}\"<br> &nbsp; &nbsp;<br> &nbsp;  print_info \"\u5f00\u59cb\u5907\u4efd\u8fdc\u7a0bPostgreSQL\u6570\u636e\u5e93: $POSTGRESQL_DATABASE\"<br> &nbsp; &nbsp;echo \"  \u2022 \u8fdc\u7a0b\u670d\u52a1\u5668: $REMOTE_HOST\"<br> &nbsp; &nbsp;echo \"  \u2022 \u5907\u4efd\u6570\u636e\u5e93: $POSTGRESQL_DATABASE\"<br> &nbsp; &nbsp;echo \"  \u2022 \u5907\u4efd\u6587\u4ef6: $backup_file\"<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;<br> &nbsp;  print_warning \"PostgreSQL\u6570\u636e\u5e93\u8f83\u5927\uff0c\u5927\u7ea630\u5206\u949f\u5de6\u53f3\u5907\u4efd\u5b8c\u6210\uff0c\u8bf7\u52ff\u4e2d\u65ad...\"<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u76f4\u63a5\u4ece\u8fdc\u7a0b\u670d\u52a1\u5668\u5907\u4efd\u5230\u672c\u5730\uff0c\u4e0d\u5728\u8fdc\u7a0b\u670d\u52a1\u5668\u4fdd\u5b58\u6587\u4ef6<br> &nbsp; &nbsp;if sshpass -p \"$REMOTE_PASSWORD\" ssh -o StrictHostKeyChecking=no \\<br> &nbsp; &nbsp; &nbsp; &nbsp;-p \"$REMOTE_PORT\" \"$REMOTE_USER@$REMOTE_HOST\" \\<br> &nbsp; &nbsp; &nbsp; &nbsp;\"docker exec -e PGPASSWORD='$POSTGRESQL_PASSWORD' '$POSTGRES_CONTAINER' pg_dump -h localhost -p '$POSTGRESQL_PORT' -U '$POSTGRESQL_USERNAME' -d '$POSTGRESQL_DATABASE' 2&gt;\/dev\/null\" \\<br> &nbsp; &nbsp; &nbsp;  &gt; \"$local_backup_path\"; then<br> &nbsp; &nbsp; &nbsp; &nbsp;<br> &nbsp; &nbsp; &nbsp; &nbsp;# \u68c0\u67e5\u5907\u4efd\u6587\u4ef6\u5927\u5c0f<br> &nbsp; &nbsp; &nbsp; &nbsp;if [ -s \"$local_backup_path\" ]; then<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  local file_size=$(ls -lh \"$local_backup_path\" | awk '{print $5}')<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  print_success \"\u2705 PostgreSQL\u5907\u4efd\u6210\u529f\u5b8c\u6210\uff01<br>\ud83d\udcc1 \u672c\u5730\u5907\u4efd\u4f4d\u7f6e: $local_backup_path<br>\ud83d\udcc4 \u5907\u4efd\u6587\u4ef6: $backup_file<br>\ud83d\udcca \u6587\u4ef6\u5927\u5c0f: $file_size<br>\u23f1\ufe0f  \u5907\u4efd\u65f6\u95f4\u6233: $TIMESTAMP\"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  return 0<br> &nbsp; &nbsp; &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  print_error \"PostgreSQL\u5907\u4efd\u6587\u4ef6\u4e3a\u7a7a\uff0c\u5907\u4efd\u53ef\u80fd\u5931\u8d25\uff01\"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;rm -f \"$local_backup_path\" 2&gt;\/dev\/null<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp; &nbsp; &nbsp;fi<br> &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp;  print_error \"\u8fdc\u7a0bPostgreSQL\u5907\u4efd\u5931\u8d25\uff01\u8bf7\u68c0\u67e5\uff1a<br>1. \u8fdc\u7a0b\u5bb9\u5668\u662f\u5426\u6b63\u5e38\u8fd0\u884c<br>2. \u8fdc\u7a0b\u6570\u636e\u5e93\u8fde\u63a5\u4fe1\u606f\u662f\u5426\u6b63\u786e<br>3. \u7f51\u7edc\u8fde\u63a5\u662f\u5426\u7a33\u5b9a\"<br> &nbsp; &nbsp; &nbsp; &nbsp;rm -f \"$local_backup_path\" 2&gt;\/dev\/null<br> &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp;fi<br>}<br>\u200b<br># \u663e\u793a\u5907\u4efd\u7edf\u8ba1\u4fe1\u606f<br>show_backup_statistics() {<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;echo -e \"${BLUE}\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE} &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; \u5907\u4efd\u6587\u4ef6\u7edf\u8ba1\u4fe1\u606f &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE}\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550${NC}\"<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;if [ -d \"$LOCAL_BACKUP_DIR\" ]; then<br> &nbsp; &nbsp; &nbsp; &nbsp;echo -e \"${CYAN}\u672c\u5730\u5907\u4efd\u76ee\u5f55: $LOCAL_BACKUP_DIR${NC}\"<br> &nbsp; &nbsp; &nbsp; &nbsp;echo -e \"${CYAN}\u6700\u8fd1\u5907\u4efd\u6587\u4ef6:${NC}\"<br> &nbsp; &nbsp; &nbsp; &nbsp;ls -lh \"$LOCAL_BACKUP_DIR\" | tail -5 | awk '{if(NR&gt;1) printf \"  \u2022 %-40s %6s\\n\", $9, $5}'<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;echo<br>}<br>\u200b<br># \u4e3b\u51fd\u6570<br>main() {<br> &nbsp;  print_header<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;echo -e \"${BLUE}\u51c6\u5907\u6267\u884c\u8fdc\u7a0bPostgreSQL\u5907\u4efd\u4efb\u52a1...${NC}\"<br> &nbsp; &nbsp;echo \"\ud83d\udccb \u5907\u4efd\u914d\u7f6e:\"<br> &nbsp; &nbsp;echo \" &nbsp; \u2022 \u672c\u5730\u670d\u52a1\u5668: $(hostname -I | awk '{print $1}')\"<br> &nbsp; &nbsp;echo \" &nbsp; \u2022 \u8fdc\u7a0b\u670d\u52a1\u5668: $REMOTE_HOST\"<br> &nbsp; &nbsp;echo \" &nbsp; \u2022 \u8fdc\u7a0b\u6570\u636e\u5e93: $POSTGRESQL_DATABASE\"<br> &nbsp; &nbsp;echo \" &nbsp; \u2022 \u672c\u5730\u5907\u4efd\u76ee\u5f55: $LOCAL_BACKUP_DIR\"<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u68c0\u67e5sshpass<br> &nbsp; &nbsp;if ! check_sshpass; then<br> &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u68c0\u67e5\u8fdc\u7a0b\u8fde\u63a5<br> &nbsp; &nbsp;if ! check_remote_connection; then<br> &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u6267\u884c\u8fdc\u7a0b\u5907\u4efd<br> &nbsp;  backup_postgresql_remote<br> &nbsp;  local backup_result=$?<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u603b\u7ed3\u5907\u4efd\u7ed3\u679c<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;echo -e \"${BLUE}\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE} &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; \u5907\u4efd\u4efb\u52a1\u5b8c\u6210\u603b\u7ed3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE}\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550${NC}\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;if [ $backup_result -eq 0 ]; then<br> &nbsp; &nbsp; &nbsp;  print_success \"\ud83c\udf89 PostgreSQL\u6570\u636e\u5e93\u8fdc\u7a0b\u5907\u4efd\u5b8c\u6210\uff01<br>\u2705 \u8fdc\u7a0b\u5907\u4efd: \u6210\u529f ($REMOTE_HOST)<br>\u2705 \u672c\u5730\u4fdd\u5b58: \u6210\u529f<br>\u200b<br>\ud83d\udcc1 \u672c\u5730\u5907\u4efd\u76ee\u5f55: $LOCAL_BACKUP_DIR<br>\ud83d\udcc5 \u5907\u4efd\u65f6\u95f4\u6233: $TIMESTAMP\"<br> &nbsp; &nbsp; &nbsp; &nbsp;<br> &nbsp; &nbsp; &nbsp; &nbsp;# \u663e\u793a\u5907\u4efd\u7edf\u8ba1<br> &nbsp; &nbsp; &nbsp;  show_backup_statistics<br> &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp;  print_error \"\u274c PostgreSQL\u6570\u636e\u5e93\u8fdc\u7a0b\u5907\u4efd\u5931\u8d25\uff01<br>\u8bf7\u68c0\u67e5\uff1a<br>1. \u8fdc\u7a0b\u670d\u52a1\u5668\u662f\u5426\u5728\u7ebf\u548cSSH\u53ef\u8fbe<br>2. \u8fdc\u7a0b\u5bb9\u5668\u662f\u5426\u6b63\u5e38\u8fd0\u884c<br>3. \u8fdc\u7a0b\u6570\u636e\u5e93\u8fde\u63a5\u4fe1\u606f\u662f\u5426\u6b63\u786e<br>4. \u672c\u5730\u5907\u4efd\u76ee\u5f55\u6743\u9650\u662f\u5426\u6b63\u786e\"<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;echo -e \"${BLUE}\u8fdc\u7a0b\u5907\u4efd\u811a\u672c\u6267\u884c\u5b8c\u6bd5\uff01${NC}\"<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;<br> &nbsp;  return $backup_result<br>}<br>\u200b<br># \u68c0\u67e5\u662f\u5426\u4ee5root\u7528\u6237\u8fd0\u884c<br>if [ \"$EUID\" -ne 0 ]; then<br> &nbsp;  print_error \"\u8bf7\u4ee5root\u7528\u6237\u8eab\u4efd\u8fd0\u884c\u6b64\u811a\u672c\uff01\"<br> &nbsp; &nbsp;exit 1<br>fi<br>\u200b<br># \u8fd0\u884c\u4e3b\u51fd\u6570<br>main<\/pre>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E6%89%A7%E8%A1%8C%E6%95%88%E6%9E%9C%EF%BC%9A\"><\/span>\u6267\u884c\u6548\u679c\uff1a<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/typora-zp.oss-cn-shanghai.aliyuncs.com\/typora20260107135619422.png\" alt=\"image-20260107135619314\"\/><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E8%BF%9C%E7%A8%8B%E6%89%A7%E8%A1%8C%E5%A4%87%E4%BB%BD%E8%84%9A%E6%9C%ACV20postgresql%EF%BC%9A\"><\/span><mark>\u8fdc\u7a0b\u6267\u884c\u5907\u4efd\u811a\u672cV2.0(postgresql)<\/mark>\uff1a<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\"><code>postgresql_remote_backup_client.sh<\/code><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">#!\/bin\/bash<br>\u200b<br># PostgreSQL\u6570\u636e\u5e93\u5907\u4efd\u811a\u672c - \u672c\u5730pg\u5ba2\u6237\u7aef\u7248\u672c<br># \u81ea\u52a8\u4e0b\u8f7dPostgreSQL\u5ba2\u6237\u7aef\u5e76\u8fde\u63a5\u8fdc\u7a0bPostgreSQL\u6570\u636e\u5e93\u8fdb\u884c\u5907\u4efd<br>\u200b<br># \u989c\u8272\u5b9a\u4e49<br>RED='\\033[0;31m'<br>GREEN='\\033[0;32m'<br>YELLOW='\\033[1;33m'<br>BLUE='\\033[0;34m'<br>CYAN='\\033[0;36m'<br>NC='\\033[0m' # No Color<br>\u200b<br># PostgreSQL\u8fdc\u7a0b\u6570\u636e\u5e93\u914d\u7f6e<br>PG_HOST=\"192.168.99.56\"<br>PG_PORT=\"5432\"<br>PG_USER=\"postgres\"<br>PG_DATABASE=\"bim3dv2\"<br>PG_PASSWORD=\"bim%2018\"<br>\u200b<br># \u672c\u5730\u5907\u4efd\u5b58\u50a8\u76ee\u5f55<br>LOCAL_BACKUP_DIR=\"\/data\/data_bak\/postgresql\"<br>\u200b<br># PostgreSQL\u5ba2\u6237\u7aef\u914d\u7f6e<br>PG_CLIENT_DIR=\"\/data\/resources\"<br>#\u4f9d\u8d56\u5e93\u914d\u7f6e<br>export LD_LIBRARY_PATH=\/data\/resources\/pgsql\/lib:$LD_LIBRARY_PATH<br>#\u6b64\u4e0b\u8f7d\u5730\u5740linux\u4e8c\u8fdb\u5236\u5305\u6700\u9ad8\u4e3a10.23,\u4f7f\u7528\u5907\u7528\u4e0b\u8f7d\u6e90<br>#PG_DOWNLOAD_URL=\"https:\/\/get.enterprisedb.com\/postgresql\/postgresql-10.23-1-linux-x64-binaries.tar.gz\"<br>PG_VERSION=\"13.3\"<br>PG_DOWNLOAD_URL=\"https:\/\/repo.linuxjk.cn\/%E8%BD%AF%E4%BB%B6%E5%8C%85\/%E6%9C%8D%E5%8A%A1%E8%BD%AF%E4%BB%B6%E5%8C%85\/postgresql\/pg13%E7%BC%96%E8%AF%91%E5%AE%89%E8%A3%85%E7%9A%84%E4%BA%8C%E8%BF%9B%E5%88%B6%E5%8C%85\/postgresql13.3_erjinzhi.tar.gz\"<br># \u751f\u6210\u65f6\u95f4\u6233<br>TIMESTAMP=$(date +\"%Y%m%d_%H%M%S\")<br>\u200b<br># \u6253\u5370\u9192\u76ee\u7684\u6807\u9898<br>print_header() {<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;echo -e \"${BLUE}================================================${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE} &nbsp; PostgreSQL\u6570\u636e\u5e93\u5907\u4efd\u811a\u672c &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE} &nbsp; \u76ee\u6807\u670d\u52a1\u5668: $PG_HOST${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE}================================================${NC}\"<br> &nbsp; &nbsp;echo<br>}<br>\u200b<br># \u6253\u5370\u6210\u529f\u6d88\u606f<br>print_success() {<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;echo -e \"${GREEN}************************************************${NC}\"<br> &nbsp; &nbsp;echo -e \"${GREEN}$1${NC}\"<br> &nbsp; &nbsp;echo -e \"${GREEN}************************************************${NC}\"<br> &nbsp; &nbsp;echo<br>}<br>\u200b<br># \u6253\u5370\u9519\u8bef\u6d88\u606f<br>print_error() {<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;echo -e \"${RED}************************************************${NC}\"<br> &nbsp; &nbsp;echo -e \"${RED}\u9519\u8bef: $1${NC}\"<br> &nbsp; &nbsp;echo -e \"${RED}************************************************${NC}\"<br> &nbsp; &nbsp;echo<br>}<br>\u200b<br># \u6253\u5370\u8b66\u544a\u6d88\u606f<br>print_warning() {<br> &nbsp; &nbsp;echo -e \"${YELLOW}\u26a0\ufe0f  \u8b66\u544a: $1${NC}\"<br>}<br>\u200b<br># \u6253\u5370\u4fe1\u606f\u6d88\u606f<br>print_info() {<br> &nbsp; &nbsp;echo -e \"${BLUE}\u2139\ufe0f &nbsp;$1${NC}\"<br>}<br>\u200b<br># \u4e0b\u8f7d\u5e76\u5b89\u88c5PostgreSQL\u5ba2\u6237\u7aef\u5de5\u5177<br>download_pg_client() {<br> &nbsp;  print_info \"\u68c0\u67e5PostgreSQL\u5ba2\u6237\u7aef\u5de5\u5177...\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u68c0\u67e5\u662f\u5426\u5df2\u7ecf\u5b89\u88c5<br> &nbsp; &nbsp;if [ -f \"$PG_CLIENT_DIR\/pgsql\/bin\/pg_dump\" ]; then<br> &nbsp; &nbsp; &nbsp;  print_info \"PostgreSQL\u5ba2\u6237\u7aef\u5df2\u5b58\u5728\uff0c\u8df3\u8fc7\u4e0b\u8f7d\"<br> &nbsp; &nbsp; &nbsp;  return 0<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp;  print_info \"\u5f00\u59cb\u4e0b\u8f7dPostgreSQL $PG_VERSION \u5ba2\u6237\u7aef\u5de5\u5177...\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u521b\u5efa\u5ba2\u6237\u7aef\u76ee\u5f55<br> &nbsp; &nbsp;mkdir -p \"$PG_CLIENT_DIR\"<br> &nbsp; &nbsp;cd \"$PG_CLIENT_DIR\" || {<br> &nbsp; &nbsp; &nbsp;  print_error \"\u65e0\u6cd5\u521b\u5efa\u76ee\u5f55: $PG_CLIENT_DIR\"<br> &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp;  }<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u4e0b\u8f7dPostgreSQL\u4e8c\u8fdb\u5236\u5305<br> &nbsp;  print_info \"\u6b63\u5728\u4e0b\u8f7d: $PG_DOWNLOAD_URL\"<br> &nbsp; &nbsp;if wget -q --show-progress \"$PG_DOWNLOAD_URL\" -O postgresql-client.tar.gz; then<br> &nbsp; &nbsp; &nbsp;  print_info \"\u4e0b\u8f7d\u5b8c\u6210 \u2713\"<br> &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp;  print_error \"\u4e0b\u8f7d\u5931\u8d25\uff01\u8bf7\u68c0\u67e5\u7f51\u7edc\u8fde\u63a5\u6216URL\u662f\u5426\u6709\u6548\"<br> &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u89e3\u538b\u6587\u4ef6<br> &nbsp;  print_info \"\u6b63\u5728\u89e3\u538bPostgreSQL\u5ba2\u6237\u7aef...\"<br> &nbsp; &nbsp;if tar -xzf postgresql-client.tar.gz; then<br> &nbsp; &nbsp; &nbsp;  print_info \"\u89e3\u538b\u5b8c\u6210 \u2713\"<br> &nbsp; &nbsp; &nbsp; &nbsp;# \u6e05\u7406\u4e0b\u8f7d\u7684\u538b\u7f29\u5305<br> &nbsp; &nbsp; &nbsp; &nbsp;rm -f postgresql-client.tar.gz<br> &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp;  print_error \"\u89e3\u538b\u5931\u8d25\uff01\"<br> &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u68c0\u67e5\u5173\u952e\u6587\u4ef6\u662f\u5426\u5b58\u5728<br> &nbsp; &nbsp;if [ -f \"$PG_CLIENT_DIR\/pgsql\/bin\/pg_dump\" ] &amp;&amp; [ -f \"$PG_CLIENT_DIR\/pgsql\/bin\/psql\" ]; then<br> &nbsp; &nbsp; &nbsp;  print_success \"PostgreSQL\u5ba2\u6237\u7aef\u5de5\u5177\u5b89\u88c5\u6210\u529f\uff01<br>\ud83d\udcc1 \u5b89\u88c5\u8def\u5f84: $PG_CLIENT_DIR\/pgsql\/bin\/<br>\ud83d\udee0\ufe0f  \u53ef\u7528\u5de5\u5177: pg_dump, psql, pg_restore \u7b49\"<br> &nbsp; &nbsp; &nbsp; &nbsp;<br> &nbsp; &nbsp; &nbsp; &nbsp;# \u663e\u793a\u7248\u672c\u4fe1\u606f<br> &nbsp; &nbsp; &nbsp;  local pg_dump_version=$(\"$PG_CLIENT_DIR\/pgsql\/bin\/pg_dump\" --version)<br> &nbsp; &nbsp; &nbsp;  print_info \"pg_dump\u7248\u672c: $pg_dump_version\"<br> &nbsp; &nbsp; &nbsp; &nbsp;<br> &nbsp; &nbsp; &nbsp;  return 0<br> &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp;  print_error \"PostgreSQL\u5ba2\u6237\u7aef\u5de5\u5177\u5b89\u88c5\u5931\u8d25\uff01\u5173\u952e\u6587\u4ef6\u4e0d\u5b58\u5728\"<br> &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp;fi<br>}<br>\u200b<br># \u8bbe\u7f6ePostgreSQL\u5ba2\u6237\u7aef\u73af\u5883<br>setup_pg_environment() {<br> &nbsp; &nbsp;# \u8bbe\u7f6ePostgreSQL\u5ba2\u6237\u7aef\u8def\u5f84<br> &nbsp; &nbsp;export PATH=\"$PG_CLIENT_DIR\/pgsql\/bin:$PATH\"<br> &nbsp; &nbsp;export LD_LIBRARY_PATH=\"$PG_CLIENT_DIR\/pgsql\/lib:$LD_LIBRARY_PATH\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u9a8c\u8bc1pg_dump\u547d\u4ee4<br> &nbsp; &nbsp;if command -v pg_dump &amp;&gt; \/dev\/null; then<br> &nbsp; &nbsp; &nbsp;  print_info \"PostgreSQL\u5ba2\u6237\u7aef\u73af\u5883\u8bbe\u7f6e\u6210\u529f \u2713\"<br> &nbsp; &nbsp; &nbsp;  return 0<br> &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp;  print_error \"PostgreSQL\u5ba2\u6237\u7aef\u73af\u5883\u8bbe\u7f6e\u5931\u8d25\"<br> &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp;fi<br>}<br>\u200b<br># \u68c0\u67e5\u8fdc\u7a0bPostgreSQL\u8fde\u63a5<br>check_pg_connection() {<br> &nbsp;  print_info \"\u6b63\u5728\u68c0\u67e5\u8fdc\u7a0bPostgreSQL\u6570\u636e\u5e93\u8fde\u63a5...\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u8bbe\u7f6e\u5bc6\u7801\u73af\u5883\u53d8\u91cf<br> &nbsp; &nbsp;export PGPASSWORD=\"$PG_PASSWORD\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u6d4b\u8bd5\u8fde\u63a5\uff08\u4f7f\u7528\u7edd\u5bf9\u8def\u5f84\u786e\u4fdd\u4f7f\u7528\u6b63\u786e\u7684psql\uff09<br> &nbsp; &nbsp;if \"$PG_CLIENT_DIR\/pgsql\/bin\/psql\" -h \"$PG_HOST\" -p \"$PG_PORT\" -U \"$PG_USER\" -d \"$PG_DATABASE\" -c \"\\q\" 2&gt;\/dev\/null; then<br> &nbsp; &nbsp; &nbsp;  print_info \"\u8fdc\u7a0bPostgreSQL\u8fde\u63a5\u6210\u529f \u2713\"<br> &nbsp; &nbsp; &nbsp;  return 0<br> &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp;  print_error \"\u65e0\u6cd5\u8fde\u63a5\u5230\u8fdc\u7a0bPostgreSQL\u6570\u636e\u5e93\uff01<br>\u8bf7\u68c0\u67e5\uff1a<br>1. \u8fdc\u7a0b\u670d\u52a1\u5668 $PG_HOST \u662f\u5426\u5728\u7ebf<br>2. PostgreSQL\u670d\u52a1\u662f\u5426\u542f\u52a8 (\u7aef\u53e3:$PG_PORT)<br>3. \u6570\u636e\u5e93\u7528\u6237\u540d\u5bc6\u7801\u662f\u5426\u6b63\u786e<br>4. \u9632\u706b\u5899\u662f\u5426\u5f00\u653e5432\u7aef\u53e3<br>5. pg_hba.conf\u662f\u5426\u5141\u8bb8\u8fdc\u7a0b\u8fde\u63a5\"<br> &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp;fi<br>}<br>\u200b<br># \u521b\u5efa\u5907\u4efd\u76ee\u5f55<br>create_backup_dir() {<br> &nbsp; &nbsp;if [ ! -d \"$LOCAL_BACKUP_DIR\" ]; then<br> &nbsp; &nbsp; &nbsp; &nbsp;mkdir -p \"$LOCAL_BACKUP_DIR\"<br> &nbsp; &nbsp; &nbsp; &nbsp;if [ $? -eq 0 ]; then<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  print_info \"\u521b\u5efa\u672c\u5730\u5907\u4efd\u76ee\u5f55: $LOCAL_BACKUP_DIR\"<br> &nbsp; &nbsp; &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  print_error \"\u65e0\u6cd5\u521b\u5efa\u5907\u4efd\u76ee\u5f55: $LOCAL_BACKUP_DIR\"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp; &nbsp; &nbsp;fi<br> &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp;  print_info \"\u5907\u4efd\u76ee\u5f55\u5df2\u5b58\u5728: $LOCAL_BACKUP_DIR\"<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u68c0\u67e5\u76ee\u5f55\u6743\u9650<br> &nbsp; &nbsp;if [ ! -w \"$LOCAL_BACKUP_DIR\" ]; then<br> &nbsp; &nbsp; &nbsp;  print_error \"\u5907\u4efd\u76ee\u5f55\u65e0\u5199\u6743\u9650: $LOCAL_BACKUP_DIR\"<br> &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp;  return 0<br>}<br>\u200b<br># PostgreSQL\u5907\u4efd\u51fd\u6570<br>backup_postgresql() {<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;echo -e \"${BLUE}\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE}  \u5f00\u59cb\u5907\u4efdPostgreSQL\u6570\u636e\u5e93${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE}\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550${NC}\"<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u751f\u6210\u5907\u4efd\u6587\u4ef6\u540d<br> &nbsp;  local backup_file=\"${PG_DATABASE}_${TIMESTAMP}.sql\"<br> &nbsp;  local backup_path=\"${LOCAL_BACKUP_DIR}\/${backup_file}\"<br> &nbsp; &nbsp;<br> &nbsp;  print_info \"\u5f00\u59cb\u5907\u4efdPostgreSQL\u6570\u636e\u5e93: $PG_DATABASE\"<br> &nbsp; &nbsp;echo \"  \u2022 \u8fdc\u7a0b\u670d\u52a1\u5668: $PG_HOST:$PG_PORT\"<br> &nbsp; &nbsp;echo \"  \u2022 \u5907\u4efd\u6570\u636e\u5e93: $PG_DATABASE\"<br> &nbsp; &nbsp;echo \"  \u2022 \u5907\u4efd\u6587\u4ef6: $backup_file\"<br> &nbsp; &nbsp;echo \"  \u2022 \u4fdd\u5b58\u8def\u5f84: $backup_path\"<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;<br> &nbsp;  print_warning \"PostgreSQL\u6570\u636e\u5e93\u8f83\u5927\uff0c\u5927\u7ea630\u5206\u949f\u5de6\u53f3\u5907\u4efd\u5b8c\u6210\uff0c\u8bf7\u52ff\u4e2d\u65ad...\"<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u8bbe\u7f6e\u5bc6\u7801\u73af\u5883\u53d8\u91cf<br> &nbsp; &nbsp;export PGPASSWORD=\"$PG_PASSWORD\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u663e\u793a\u8fdb\u5ea6<br> &nbsp;  print_info \"\u6b63\u5728\u6267\u884c\u5907\u4efd\uff0c\u8bf7\u8010\u5fc3\u7b49\u5f85...\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u6267\u884cpg_dump\u5907\u4efd\uff08\u4f7f\u7528\u7edd\u5bf9\u8def\u5f84\uff09<br> &nbsp; &nbsp;if \"$PG_CLIENT_DIR\/pgsql\/bin\/pg_dump\" -h \"$PG_HOST\" -p \"$PG_PORT\" -U \"$PG_USER\" -d \"$PG_DATABASE\" \\<br> &nbsp; &nbsp; &nbsp; &nbsp;--verbose --clean --if-exists --create --compress=0 \\<br> &nbsp; &nbsp; &nbsp; &nbsp;--format=plain --no-owner --no-privileges \\<br> &nbsp; &nbsp; &nbsp;  &gt; \"$backup_path\" 2&gt;\/dev\/null; then<br> &nbsp; &nbsp; &nbsp; &nbsp;<br> &nbsp; &nbsp; &nbsp; &nbsp;# \u68c0\u67e5\u5907\u4efd\u6587\u4ef6\u5927\u5c0f<br> &nbsp; &nbsp; &nbsp; &nbsp;if [ -s \"$backup_path\" ]; then<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  local file_size=$(du -h \"$backup_path\" | cut -f1)<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  print_success \"\u2705 PostgreSQL\u5907\u4efd\u6210\u529f\u5b8c\u6210\uff01<br>\ud83d\udcc1 \u5907\u4efd\u4f4d\u7f6e: $backup_path<br>\ud83d\udcc4 \u5907\u4efd\u6587\u4ef6: $backup_file<br>\ud83d\udcca \u6587\u4ef6\u5927\u5c0f: $file_size<br>\u23f1\ufe0f  \u5907\u4efd\u65f6\u95f4\u6233: $TIMESTAMP\"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;# \u538b\u7f29\u5907\u4efd\u6587\u4ef6\u4ee5\u8282\u7701\u7a7a\u95f4<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  print_info \"\u6b63\u5728\u538b\u7f29\u5907\u4efd\u6587\u4ef6...\"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;if gzip \"$backup_path\"; then<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  local compressed_size=$(du -h \"${backup_path}.gz\" | cut -f1)<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  print_info \"\u538b\u7f29\u5b8c\u6210\uff01\u538b\u7f29\u540e\u5927\u5c0f: $compressed_size\"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;fi<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  return 0<br> &nbsp; &nbsp; &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  print_error \"\u5907\u4efd\u6587\u4ef6\u4e3a\u7a7a\uff0c\u5907\u4efd\u53ef\u80fd\u5931\u8d25\uff01\"<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;rm -f \"$backup_path\" 2&gt;\/dev\/null<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp; &nbsp; &nbsp;fi<br> &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp;  print_error \"PostgreSQL\u5907\u4efd\u5931\u8d25\uff01\u8bf7\u68c0\u67e5\uff1a<br>1. \u8fdc\u7a0b\u6570\u636e\u5e93\u670d\u52a1\u662f\u5426\u6b63\u5e38<br>2. \u7f51\u7edc\u8fde\u63a5\u662f\u5426\u7a33\u5b9a<br>3. \u6570\u636e\u5e93\u6743\u9650\u662f\u5426\u8db3\u591f<br>4. \u78c1\u76d8\u7a7a\u95f4\u662f\u5426\u5145\u8db3\"<br> &nbsp; &nbsp; &nbsp; &nbsp;rm -f \"$backup_path\" 2&gt;\/dev\/null<br> &nbsp; &nbsp; &nbsp;  return 1<br> &nbsp; &nbsp;fi<br>}<br>\u200b<br># \u6e05\u7406\u65e7\u5907\u4efd\u6587\u4ef6\uff08\u4fdd\u7559\u6700\u8fd17\u5929\uff09<br>cleanup_old_backups() {<br> &nbsp;  print_info \"\u6e05\u74067\u5929\u524d\u7684\u65e7\u5907\u4efd\u6587\u4ef6...\"<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;if [ -d \"$LOCAL_BACKUP_DIR\" ]; then<br> &nbsp; &nbsp; &nbsp;  local deleted_files=$(find \"$LOCAL_BACKUP_DIR\" -name \"${PG_DATABASE}_*.sql.gz\" -mtime +7 -delete -print | wc -l)<br> &nbsp; &nbsp; &nbsp; &nbsp;if [ \"$deleted_files\" -gt 0 ]; then<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  print_info \"\u6e05\u7406\u4e86 $deleted_files \u4e2a\u65e7\u5907\u4efd\u6587\u4ef6\"<br> &nbsp; &nbsp; &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  print_info \"\u6ca1\u6709\u9700\u8981\u6e05\u7406\u7684\u65e7\u5907\u4efd\u6587\u4ef6\"<br> &nbsp; &nbsp; &nbsp; &nbsp;fi<br> &nbsp; &nbsp;fi<br>}<br>\u200b<br># \u663e\u793a\u5907\u4efd\u7edf\u8ba1\u4fe1\u606f<br>show_backup_statistics() {<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;echo -e \"${BLUE}\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE} &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; \u5907\u4efd\u6587\u4ef6\u7edf\u8ba1\u4fe1\u606f &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;${NC}\"<br> &nbsp; &nbsp;echo -e \"${BLUE}\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550\u2550${NC}\"<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;if [ -d \"$LOCAL_BACKUP_DIR\" ]; then<br> &nbsp; &nbsp; &nbsp; &nbsp;echo -e \"${CYAN}\u5907\u4efd\u76ee\u5f55: $LOCAL_BACKUP_DIR${NC}\"<br> &nbsp; &nbsp; &nbsp; &nbsp;echo -e \"${CYAN}\u6700\u8fd15\u4e2a\u5907\u4efd\u6587\u4ef6:${NC}\"<br> &nbsp; &nbsp; &nbsp; &nbsp;ls -lht \"$LOCAL_BACKUP_DIR\"\/${PG_DATABASE}_*.sql.gz 2&gt;\/dev\/null | head -5 | while read line; do<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  local filename=$(echo \"$line\" | awk '{print $9}')<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  local filesize=$(echo \"$line\" | awk '{print $5}')<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  local filedate=$(echo \"$line\" | awk '{print $6, $7, $8}')<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  [ -n \"$filename\" ] &amp;&amp; echo \"  \u2022 $(basename \"$filename\") - $filesize ($filedate)\"<br> &nbsp; &nbsp; &nbsp; &nbsp;done<br> &nbsp; &nbsp; &nbsp; &nbsp;<br> &nbsp; &nbsp; &nbsp; &nbsp;echo<br> &nbsp; &nbsp; &nbsp;  local total_files=$(ls -1 \"$LOCAL_BACKUP_DIR\"\/${PG_DATABASE}_*.sql.gz 2&gt;\/dev\/null | wc -l)<br> &nbsp; &nbsp; &nbsp;  local total_size=$(du -sh \"$LOCAL_BACKUP_DIR\" 2&gt;\/dev\/null | cut -f1)<br> &nbsp; &nbsp; &nbsp; &nbsp;echo -e \"${CYAN}\u603b\u8ba1: $total_files \u4e2a\u5907\u4efd\u6587\u4ef6\uff0c\u5360\u7528\u7a7a\u95f4: $total_size${NC}\"<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;echo<br>}<br>\u200b<br># \u4e3b\u51fd\u6570<br>main() {<br> &nbsp;  print_header<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;echo -e \"${BLUE}\u51c6\u5907\u6267\u884cPostgreSQL\u5907\u4efd\u4efb\u52a1...${NC}\"<br> &nbsp; &nbsp;echo \"\ud83d\udccb \u5907\u4efd\u914d\u7f6e:\"<br> &nbsp; &nbsp;echo \" &nbsp; \u2022 \u672c\u5730\u670d\u52a1\u5668: $(hostname -I | awk '{print $1}' || echo 'localhost')\"<br> &nbsp; &nbsp;echo \" &nbsp; \u2022 \u8fdc\u7a0b\u6570\u636e\u5e93: $PG_HOST:$PG_PORT\"<br> &nbsp; &nbsp;echo \" &nbsp; \u2022 \u6570\u636e\u5e93\u540d: $PG_DATABASE\"<br> &nbsp; &nbsp;echo \" &nbsp; \u2022 \u7528\u6237\u540d: $PG_USER\"<br> &nbsp; &nbsp;echo \" &nbsp; \u2022 \u5907\u4efd\u76ee\u5f55: $LOCAL_BACKUP_DIR\"<br> &nbsp; &nbsp;echo \" &nbsp; \u2022 \u5ba2\u6237\u7aef\u5b89\u88c5\u76ee\u5f55: $PG_CLIENT_DIR\"<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u4e0b\u8f7d\u5e76\u5b89\u88c5PostgreSQL\u5ba2\u6237\u7aef<br> &nbsp; &nbsp;if ! download_pg_client; then<br> &nbsp; &nbsp; &nbsp; &nbsp;exit 1<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u8bbe\u7f6ePostgreSQL\u73af\u5883<br> &nbsp; &nbsp;if ! setup_pg_environment; then<br> &nbsp; &nbsp; &nbsp; &nbsp;exit 1<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u521b\u5efa\u5907\u4efd\u76ee\u5f55<br> &nbsp; &nbsp;if ! create_backup_dir; then<br> &nbsp; &nbsp; &nbsp; &nbsp;exit 1<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u68c0\u67e5PostgreSQL\u8fde\u63a5<br> &nbsp; &nbsp;if ! check_pg_connection; then<br> &nbsp; &nbsp; &nbsp; &nbsp;exit 1<br> &nbsp; &nbsp;fi<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;echo<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;# \u6267\u884c\u5907\u4efd<br> &nbsp; &nbsp;if backup_postgresql; then<br> &nbsp; &nbsp; &nbsp; &nbsp;# \u6e05\u7406\u65e7\u5907\u4efd<br> &nbsp; &nbsp; &nbsp;  cleanup_old_backups<br> &nbsp; &nbsp; &nbsp; &nbsp;<br> &nbsp; &nbsp; &nbsp; &nbsp;# \u663e\u793a\u7edf\u8ba1\u4fe1\u606f<br> &nbsp; &nbsp; &nbsp;  show_backup_statistics<br> &nbsp; &nbsp; &nbsp; &nbsp;<br> &nbsp; &nbsp; &nbsp;  print_success \"\ud83c\udf89 PostgreSQL\u6570\u636e\u5e93\u5907\u4efd\u5b8c\u6210\uff01<br>\u2705 \u5ba2\u6237\u7aef\u5b89\u88c5: \u6210\u529f (PostgreSQL $PG_VERSION)<br>\u2705 \u8fdc\u7a0b\u8fde\u63a5: \u6210\u529f ($PG_HOST:$PG_PORT)<br>\u2705 \u6570\u636e\u5907\u4efd: \u6210\u529f<br>\u2705 \u6587\u4ef6\u538b\u7f29: \u6210\u529f<br>\u200b<br>\ud83d\udcc1 \u5907\u4efd\u76ee\u5f55: $LOCAL_BACKUP_DIR<br>\ud83d\udcc5 \u5907\u4efd\u65f6\u95f4: $TIMESTAMP\"<br> &nbsp; &nbsp; &nbsp; &nbsp;<br> &nbsp; &nbsp; &nbsp; &nbsp;echo -e \"${BLUE}\u5907\u4efd\u811a\u672c\u6267\u884c\u5b8c\u6bd5\uff01${NC}\"<br> &nbsp; &nbsp; &nbsp; &nbsp;exit 0<br> &nbsp; &nbsp;else<br> &nbsp; &nbsp; &nbsp;  print_error \"\u274c PostgreSQL\u6570\u636e\u5e93\u5907\u4efd\u5931\u8d25\uff01\"<br> &nbsp; &nbsp; &nbsp; &nbsp;exit 1<br> &nbsp; &nbsp;fi<br>}<br>\u200b<br># \u68c0\u67e5\u5fc5\u8981\u7684\u7cfb\u7edf\u547d\u4ee4<br>check_requirements() {<br> &nbsp;  local missing_commands=()<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;for cmd in wget tar gzip; do<br> &nbsp; &nbsp; &nbsp; &nbsp;if ! command -v \"$cmd\" &amp;&gt; \/dev\/null; then<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  missing_commands+=(\"$cmd\")<br> &nbsp; &nbsp; &nbsp; &nbsp;fi<br> &nbsp; &nbsp;done<br> &nbsp; &nbsp;<br> &nbsp; &nbsp;if [ ${#missing_commands[@]} -gt 0 ]; then<br> &nbsp; &nbsp; &nbsp;  print_error \"\u7f3a\u5c11\u5fc5\u8981\u7684\u7cfb\u7edf\u547d\u4ee4: ${missing_commands[*]}<br>\u8bf7\u5148\u5b89\u88c5\u8fd9\u4e9b\u5de5\u5177\uff1a<br>  \u2022 \u5728CentOS\/RHEL\u4e0a: yum install -y wget tar gzip<br>  \u2022 \u5728Debian\/Ubuntu\u4e0a: apt-get install -y wget tar gzip\"<br> &nbsp; &nbsp; &nbsp; &nbsp;exit 1<br> &nbsp; &nbsp;fi<br>}<br>\u200b<br># \u68c0\u67e5\u662f\u5426\u4ee5root\u7528\u6237\u8fd0\u884c<br>if [ \"$EUID\" -ne 0 ]; then<br> &nbsp;  print_error \"\u8bf7\u4ee5root\u7528\u6237\u8eab\u4efd\u8fd0\u884c\u6b64\u811a\u672c\uff01\"<br> &nbsp; &nbsp;exit 1<br>fi<br>\u200b<br># \u68c0\u67e5\u7cfb\u7edf\u8981\u6c42<br>check_requirements<br>\u200b<br># \u8fd0\u884c\u4e3b\u51fd\u6570<br>main<\/pre>\n\n\n\n<h5 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E6%89%A7%E8%A1%8C%E6%95%88%E6%9E%9C%EF%BC%9A-2\"><\/span>\u6267\u884c\u6548\u679c\uff1a<span class=\"ez-toc-section-end\"><\/span><\/h5>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/typora-zp.oss-cn-shanghai.aliyuncs.com\/typora20260122153314370.png\" alt=\"image-20260122153011811\"\/><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>\u80cc\u666f\uff1a \u751f\u4ea7\u73af\u5883\u53d1\u7248\u524d\u9700\u8981\u5907\u4efd\u6570\u636e\u5e93\u6570\u636e\uff0c\u4e66\u5199\u811a\u672c\u7528\u4e8e\u624b\u52a8\u6267\u884c\u5907\u4efd \u73af\u5883\u4fe1\u606f\u5982\u4e0b\uff1a \u5bb9\u5668\u540d \u4f7f\u7528\u7684\u955c\u50cf \u7aef\u53e3  [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","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":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[1],"tags":[],"class_list":["post-3148","post","type-post","status-publish","format-standard","hentry","category-vxhs888p"],"_links":{"self":[{"href":"https:\/\/linuxjk.cn\/index.php?rest_route=\/wp\/v2\/posts\/3148","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/linuxjk.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/linuxjk.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/linuxjk.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/linuxjk.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3148"}],"version-history":[{"count":1,"href":"https:\/\/linuxjk.cn\/index.php?rest_route=\/wp\/v2\/posts\/3148\/revisions"}],"predecessor-version":[{"id":3149,"href":"https:\/\/linuxjk.cn\/index.php?rest_route=\/wp\/v2\/posts\/3148\/revisions\/3149"}],"wp:attachment":[{"href":"https:\/\/linuxjk.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3148"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/linuxjk.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3148"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/linuxjk.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3148"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}