{"id":3788,"date":"2025-11-03T19:51:50","date_gmt":"2025-11-03T17:51:50","guid":{"rendered":"https:\/\/ru.davidka.net\/ru\/?p=3788"},"modified":"2025-11-03T20:07:08","modified_gmt":"2025-11-03T18:07:08","slug":"%d0%b3%d0%bb%d0%b0%d0%b2%d0%b0-11-%d1%80%d0%b5%d0%b7%d0%b5%d1%80%d0%b2%d0%bd%d0%be%d0%b5-%d0%ba%d0%be%d0%bf%d0%b8%d1%80%d0%be%d0%b2%d0%b0%d0%bd%d0%b8%d0%b5-%d0%b8-%d0%b2%d0%be%d1%81%d1%81%d1%82%d0%b0","status":"publish","type":"post","link":"https:\/\/ru.davidka.net\/ru\/%D0%93%D0%9B%D0%90%D0%92%D0%90-11-%D0%A0%D0%B5%D0%B7%D0%B5%D1%80%D0%B2%D0%BD%D0%BE%D0%B5-%D0%BA%D0%BE%D0%BF%D0%B8%D1%80%D0%BE%D0%B2%D0%B0%D0%BD%D0%B8%D0%B5-%D0%B8-%D0%B2%D0%BE%D1%81%D1%81%D1%82%D0%B0%D0%BD%D0%BE%D0%B2%D0%BB%D0%B5%D0%BD%D0%B8%D0%B5\/","title":{"rendered":"\u0413\u041b\u0410\u0412\u0410 11: \u0420\u0435\u0437\u0435\u0440\u0432\u043d\u043e\u0435 \u043a\u043e\u043f\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435 \u0438 \u0432\u043e\u0441\u0441\u0442\u0430\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u0435"},"content":{"rendered":"\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\"><strong>\u0426\u0435\u043b\u044c \u0433\u043b\u0430\u0432\u044b:<\/strong> \u041d\u0430\u0441\u0442\u0440\u043e\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u0443\u044e \u0441\u0438\u0441\u0442\u0435\u043c\u0443 \u0440\u0435\u0437\u0435\u0440\u0432\u043d\u043e\u0433\u043e \u043a\u043e\u043f\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f<\/p>\n<\/blockquote>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\".\/10-replication-clustering.md\">\u2190 \u041f\u0440\u0435\u0434\u044b\u0434\u0443\u0449\u0430\u044f \u0433\u043b\u0430\u0432\u0430<\/a> | <a href=\".\/00-INDEX.md\">\u041d\u0430\u0437\u0430\u0434 \u043a \u043e\u0433\u043b\u0430\u0432\u043b\u0435\u043d\u0438\u044e<\/a> | <a href=\".\/12-monitoring-maintenance.md\">\u0421\u043b\u0435\u0434\u0443\u044e\u0449\u0430\u044f \u0433\u043b\u0430\u0432\u0430 \u2192<\/a><\/p>\n\n\n\n<div id=\"rtoc-mokuji-wrapper\" class=\"rtoc-mokuji-content frame2 preset1 animation-fade rtoc_open default\" data-id=\"3788\" data-theme=\"Neve - Davidka\">\n\t\t\t<div id=\"rtoc-mokuji-title\" class=\"rtoc_btn_none rtoc_left\">\n\t\t\t\n\t\t\t<span>In Questo Articolo<\/span>\n\t\t\t<\/div><ol class=\"rtoc-mokuji decimal_ol level-1\"><li class=\"rtoc-item\"><a href=\"#rtoc-1\">1. \u0421\u0442\u0440\u0430\u0442\u0435\u0433\u0438\u044f \u0440\u0435\u0437\u0435\u0440\u0432\u043d\u043e\u0433\u043e \u043a\u043e\u043f\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f<\/a><ul class=\"rtoc-mokuji mokuji_ul level-2\"><li class=\"rtoc-item\"><a href=\"#rtoc-2\">1.1 \u041f\u0440\u0430\u0432\u0438\u043b\u043e 3-2-1<\/a><\/li><li class=\"rtoc-item\"><a href=\"#rtoc-3\">1.2 \u0422\u0438\u043f\u044b \u0431\u044d\u043a\u0430\u043f\u043e\u0432<\/a><\/li><\/ul><\/li><li class=\"rtoc-item\"><a href=\"#rtoc-4\">2. \u0410\u0432\u0442\u043e\u043c\u0430\u0442\u0438\u0437\u0430\u0446\u0438\u044f \u0441 mariabackup<\/a><ul class=\"rtoc-mokuji mokuji_ul level-2\"><li class=\"rtoc-item\"><a href=\"#rtoc-5\">2.1 \u0421\u043e\u0437\u0434\u0430\u043d\u0438\u0435 backup \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u044f<\/a><\/li><li class=\"rtoc-item\"><a href=\"#rtoc-6\">2.2 \u0421\u043a\u0440\u0438\u043f\u0442 \u043f\u043e\u043b\u043d\u043e\u0433\u043e \u0431\u044d\u043a\u0430\u043f\u0430<\/a><\/li><\/ul><\/li><li class=\"rtoc-item\"><a href=\"#rtoc-7\">3. \u0418\u043d\u043a\u0440\u0435\u043c\u0435\u043d\u0442\u0430\u043b\u044c\u043d\u044b\u0435 \u0431\u044d\u043a\u0430\u043f\u044b<\/a><ul class=\"rtoc-mokuji mokuji_ul level-2\"><li class=\"rtoc-item\"><a href=\"#rtoc-8\">3.1 \u0421\u043a\u0440\u0438\u043f\u0442 \u0438\u043d\u043a\u0440\u0435\u043c\u0435\u043d\u0442\u0430\u043b\u044c\u043d\u043e\u0433\u043e \u0431\u044d\u043a\u0430\u043f\u0430<\/a><\/li><li class=\"rtoc-item\"><a href=\"#rtoc-9\">3.2 \u0412\u043e\u0441\u0441\u0442\u0430\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u0435 \u0438\u0437 \u0438\u043d\u043a\u0440\u0435\u043c\u0435\u043d\u0442\u0430\u043b\u044c\u043d\u044b\u0445 \u0431\u044d\u043a\u0430\u043f\u043e\u0432<\/a><\/li><\/ul><\/li><li class=\"rtoc-item\"><a href=\"#rtoc-10\">4. Point-in-time recovery<\/a><ul class=\"rtoc-mokuji mokuji_ul level-2\"><li class=\"rtoc-item\"><a href=\"#rtoc-11\">4.1 \u0421\u043e\u0445\u0440\u0430\u043d\u0435\u043d\u0438\u0435 binary logs<\/a><\/li><li class=\"rtoc-item\"><a href=\"#rtoc-12\">4.2 \u0412\u043e\u0441\u0441\u0442\u0430\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u0435 \u043d\u0430 \u043e\u043f\u0440\u0435\u0434\u0435\u043b\u0435\u043d\u043d\u043e\u0435 \u0432\u0440\u0435\u043c\u044f<\/a><\/li><\/ul><\/li><li class=\"rtoc-item\"><a href=\"#rtoc-13\">5. \u0422\u0435\u0441\u0442\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435 \u0432\u043e\u0441\u0441\u0442\u0430\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u044f<\/a><ul class=\"rtoc-mokuji mokuji_ul level-2\"><li class=\"rtoc-item\"><a href=\"#rtoc-14\">5.1 \u0415\u0436\u0435\u043c\u0435\u0441\u044f\u0447\u043d\u044b\u0439 \u0442\u0435\u0441\u0442 \u0432\u043e\u0441\u0441\u0442\u0430\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u044f<\/a><\/li><\/ul><\/li><li class=\"rtoc-item\"><a href=\"#rtoc-15\">6. Disaster recovery<\/a><ul class=\"rtoc-mokuji mokuji_ul level-2\"><li class=\"rtoc-item\"><a href=\"#rtoc-16\">6.1 Disaster Recovery Plan<\/a><\/li><li class=\"rtoc-item\"><a href=\"#rtoc-17\">6.2 \u0421\u043a\u0440\u0438\u043f\u0442 \u0430\u0432\u0442\u043e\u043c\u0430\u0442\u0438\u0447\u0435\u0441\u043a\u043e\u0433\u043e DR<\/a><\/li><\/ul><\/li><li class=\"rtoc-item\"><a href=\"#rtoc-18\">\u0410\u0432\u0442\u043e\u043c\u0430\u0442\u0438\u0437\u0430\u0446\u0438\u044f (Cron)<\/a><\/li><li class=\"rtoc-item\"><a href=\"#rtoc-19\">\u0427\u0435\u043a-\u043b\u0438\u0441\u0442<\/a><ul class=\"rtoc-mokuji mokuji_ul level-2\"><li class=\"rtoc-item\"><a href=\"#rtoc-20\">\u2705 \u041e\u0431\u044f\u0437\u0430\u0442\u0435\u043b\u044c\u043d\u044b\u0435 \u0434\u0435\u0439\u0441\u0442\u0432\u0438\u044f<\/a><\/li><li class=\"rtoc-item\"><a href=\"#rtoc-21\">\ud83d\udd04 \u041f\u0440\u043e\u0432\u0435\u0440\u043a\u0438<\/a><\/li><\/ul><\/li><li class=\"rtoc-item\"><a href=\"#rtoc-22\">\u0421\u043b\u0435\u0434\u0443\u044e\u0449\u0438\u0439 \u0448\u0430\u0433<\/a><\/li><\/ol><\/div><h2 id=\"rtoc-1\"  class=\"wp-block-heading\">1. \u0421\u0442\u0440\u0430\u0442\u0435\u0433\u0438\u044f \u0440\u0435\u0437\u0435\u0440\u0432\u043d\u043e\u0433\u043e \u043a\u043e\u043f\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f<\/h2>\n\n\n\n<h3 id=\"rtoc-2\"  class=\"wp-block-heading\">1.1 \u041f\u0440\u0430\u0432\u0438\u043b\u043e 3-2-1<\/h3>\n\n\n\n<pre class=\"wp-block-code line-numbers\"><code class=\" language-python\" data-line=\"\">3 \u043a\u043e\u043f\u0438\u0438 \u0434\u0430\u043d\u043d\u044b\u0445\n2 \u0440\u0430\u0437\u043b\u0438\u0447\u043d\u044b\u0445 \u0442\u0438\u043f\u0430 \u043d\u043e\u0441\u0438\u0442\u0435\u043b\u0435\u0439\n1 \u043a\u043e\u043f\u0438\u044f off-site (\u0443\u0434\u0430\u043b\u0435\u043d\u043d\u0430\u044f)<\/code><\/pre>\n\n\n\n<h3 id=\"rtoc-3\"  class=\"wp-block-heading\">1.2 \u0422\u0438\u043f\u044b \u0431\u044d\u043a\u0430\u043f\u043e\u0432<\/h3>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>\u0422\u0438\u043f<\/th><th>\u0427\u0430\u0441\u0442\u043e\u0442\u0430<\/th><th>Retention<\/th><th>\u041c\u0435\u0442\u043e\u0434<\/th><\/tr><\/thead><tbody><tr><td><strong>\u041f\u043e\u043b\u043d\u044b\u0439<\/strong><\/td><td>\u0412\u043e\u0441\u043a\u0440\u0435\u0441\u0435\u043d\u044c\u0435 02:00<\/td><td>4 \u043d\u0435\u0434\u0435\u043b\u0438<\/td><td>mariabackup<\/td><\/tr><tr><td><strong>\u0418\u043d\u043a\u0440\u0435\u043c\u0435\u043d\u0442\u0430\u043b\u044c\u043d\u044b\u0439<\/strong><\/td><td>\u0415\u0436\u0435\u0434\u043d\u0435\u0432\u043d\u043e 02:00<\/td><td>7 \u0434\u043d\u0435\u0439<\/td><td>mariabackup<\/td><\/tr><tr><td><strong>\u041b\u043e\u0433\u0438\u0447\u0435\u0441\u043a\u0438\u0439<\/strong><\/td><td>\u0415\u0436\u0435\u0434\u043d\u0435\u0432\u043d\u043e 03:00<\/td><td>7 \u0434\u043d\u0435\u0439<\/td><td>mariadb-dump<\/td><\/tr><tr><td><strong>Binary logs<\/strong><\/td><td>\u041f\u043e\u0441\u0442\u043e\u044f\u043d\u043d\u043e<\/td><td>7 \u0434\u043d\u0435\u0439<\/td><td>\u041a\u043e\u043f\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 id=\"rtoc-4\"  class=\"wp-block-heading\">2. \u0410\u0432\u0442\u043e\u043c\u0430\u0442\u0438\u0437\u0430\u0446\u0438\u044f \u0441 mariabackup<\/h2>\n\n\n\n<h3 id=\"rtoc-5\"  class=\"wp-block-heading\">2.1 \u0421\u043e\u0437\u0434\u0430\u043d\u0438\u0435 backup \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u044f<\/h3>\n\n\n\n<pre class=\"wp-block-code line-numbers\"><code class=\" language-python\" data-line=\"\">CREATE USER &#039;backup&#039;@&#039;localhost&#039; IDENTIFIED BY &#039;BackupPass123!&#039;;\nGRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO &#039;backup&#039;@&#039;localhost&#039;;\nGRANT SELECT ON mysql.* TO &#039;backup&#039;@&#039;localhost&#039;;\nFLUSH PRIVILEGES;<\/code><\/pre>\n\n\n\n<h3 id=\"rtoc-6\"  class=\"wp-block-heading\">2.2 \u0421\u043a\u0440\u0438\u043f\u0442 \u043f\u043e\u043b\u043d\u043e\u0433\u043e \u0431\u044d\u043a\u0430\u043f\u0430<\/h3>\n\n\n\n<pre class=\"wp-block-code line-numbers\"><code class=\" language-python\" data-line=\"\">#!\/bin\/bash\n# mariabackup_full.sh\n\nBACKUP_DIR=&quot;\/backup\/mariabackup&quot;\nDATE=$(date +%Y%m%d_%H%M%S)\nFULL_BACKUP=&quot;$BACKUP_DIR\/full_$DATE&quot;\nLOG_FILE=&quot;$BACKUP_DIR\/backup.log&quot;\n\nmkdir -p &quot;$FULL_BACKUP&quot;\n\necho &quot;&#091;$(date)] Starting full backup...&quot; | tee -a &quot;$LOG_FILE&quot;\n\nmariabackup \\\n    --backup \\\n    --target-dir=&quot;$FULL_BACKUP&quot; \\\n    --user=backup \\\n    --password=BackupPass123! \\\n    --parallel=4 \\\n    --compress \\\n    --compress-threads=4 \\\n    2&gt;&amp;1 | tee -a &quot;$LOG_FILE&quot;\n\nif &#091; ${PIPESTATUS&#091;0]} -eq 0 ]; then\n    echo &quot;&#091;$(date)] Backup completed: $FULL_BACKUP&quot; | tee -a &quot;$LOG_FILE&quot;\n\n    # \u0421\u043e\u0437\u0434\u0430\u0442\u044c \u043c\u0430\u0440\u043a\u0435\u0440 \u0443\u0441\u043f\u0435\u0448\u043d\u043e\u0433\u043e \u0431\u044d\u043a\u0430\u043f\u0430\n    echo &quot;$DATE&quot; &gt; &quot;$BACKUP_DIR\/last_full_backup.txt&quot;\n\n    # \u0410\u0440\u0445\u0438\u0432\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435 (\u043e\u043f\u0446\u0438\u043e\u043d\u0430\u043b\u044c\u043d\u043e)\n    # tar -czf &quot;${FULL_BACKUP}.tar.gz&quot; -C &quot;$BACKUP_DIR&quot; &quot;full_$DATE&quot;\n    # rm -rf &quot;$FULL_BACKUP&quot;\nelse\n    echo &quot;&#091;$(date)] Backup FAILED!&quot; | tee -a &quot;$LOG_FILE&quot;\n    exit 1\nfi<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 id=\"rtoc-7\"  class=\"wp-block-heading\">3. \u0418\u043d\u043a\u0440\u0435\u043c\u0435\u043d\u0442\u0430\u043b\u044c\u043d\u044b\u0435 \u0431\u044d\u043a\u0430\u043f\u044b<\/h2>\n\n\n\n<h3 id=\"rtoc-8\"  class=\"wp-block-heading\">3.1 \u0421\u043a\u0440\u0438\u043f\u0442 \u0438\u043d\u043a\u0440\u0435\u043c\u0435\u043d\u0442\u0430\u043b\u044c\u043d\u043e\u0433\u043e \u0431\u044d\u043a\u0430\u043f\u0430<\/h3>\n\n\n\n<pre class=\"wp-block-code line-numbers\"><code class=\" language-python\" data-line=\"\">#!\/bin\/bash\n# mariabackup_incremental.sh\n\nBACKUP_DIR=&quot;\/backup\/mariabackup&quot;\nDATE=$(date +%Y%m%d_%H%M%S)\n\n# \u041d\u0430\u0439\u0442\u0438 \u043f\u043e\u0441\u043b\u0435\u0434\u043d\u0438\u0439 \u043f\u043e\u043b\u043d\u044b\u0439 \u0431\u044d\u043a\u0430\u043f\nLAST_FULL=$(cat &quot;$BACKUP_DIR\/last_full_backup.txt&quot;)\nBASE_DIR=&quot;$BACKUP_DIR\/full_$LAST_FULL&quot;\n\nif &#091; ! -d &quot;$BASE_DIR&quot; ]; then\n    echo &quot;\u274c No full backup found! Run full backup first.&quot;\n    exit 1\nfi\n\nINCR_DIR=&quot;$BACKUP_DIR\/incr_$DATE&quot;\n\necho &quot;&#091;$(date)] Starting incremental backup based on: $BASE_DIR&quot;\n\nmariabackup \\\n    --backup \\\n    --target-dir=&quot;$INCR_DIR&quot; \\\n    --incremental-basedir=&quot;$BASE_DIR&quot; \\\n    --user=backup \\\n    --password=BackupPass123! \\\n    --parallel=4 \\\n    --compress\n\nif &#091; $? -eq 0 ]; then\n    echo &quot;&#091;$(date)] Incremental backup completed: $INCR_DIR&quot;\n    echo &quot;$DATE&quot; &gt; &quot;$BACKUP_DIR\/last_incr_backup.txt&quot;\nelse\n    echo &quot;&#091;$(date)] Incremental backup FAILED!&quot;\n    exit 1\nfi<\/code><\/pre>\n\n\n\n<h3 id=\"rtoc-9\"  class=\"wp-block-heading\">3.2 \u0412\u043e\u0441\u0441\u0442\u0430\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u0435 \u0438\u0437 \u0438\u043d\u043a\u0440\u0435\u043c\u0435\u043d\u0442\u0430\u043b\u044c\u043d\u044b\u0445 \u0431\u044d\u043a\u0430\u043f\u043e\u0432<\/h3>\n\n\n\n<pre class=\"wp-block-code line-numbers\"><code class=\" language-python\" data-line=\"\">#!\/bin\/bash\n# restore_incremental.sh\n\nBACKUP_DIR=&quot;\/backup\/mariabackup&quot;\nFULL_BACKUP=&quot;$BACKUP_DIR\/full_20251101_020000&quot;\nINCR_BACKUP=&quot;$BACKUP_DIR\/incr_20251102_020000&quot;\n\necho &quot;&#091;$(date)] Preparing full backup...&quot;\nmariabackup --prepare --apply-log-only --target-dir=&quot;$FULL_BACKUP&quot;\n\necho &quot;&#091;$(date)] Applying incremental backup...&quot;\nmariabackup \\\n    --prepare \\\n    --apply-log-only \\\n    --target-dir=&quot;$FULL_BACKUP&quot; \\\n    --incremental-dir=&quot;$INCR_BACKUP&quot;\n\necho &quot;&#091;$(date)] Final prepare...&quot;\nmariabackup --prepare --target-dir=&quot;$FULL_BACKUP&quot;\n\necho &quot;&#091;$(date)] Stopping MariaDB...&quot;\nsudo systemctl stop mariadb\n\necho &quot;&#091;$(date)] Backing up current data...&quot;\nsudo mv \/var\/lib\/mysql \/var\/lib\/mysql.old\n\necho &quot;&#091;$(date)] Restoring backup...&quot;\nmariabackup --copy-back --target-dir=&quot;$FULL_BACKUP&quot;\n\nsudo chown -R mysql:mysql \/var\/lib\/mysql\n\necho &quot;&#091;$(date)] Starting MariaDB...&quot;\nsudo systemctl start mariadb\n\necho &quot;\u2705 Restore completed!&quot;<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 id=\"rtoc-10\"  class=\"wp-block-heading\">4. Point-in-time recovery<\/h2>\n\n\n\n<h3 id=\"rtoc-11\"  class=\"wp-block-heading\">4.1 \u0421\u043e\u0445\u0440\u0430\u043d\u0435\u043d\u0438\u0435 binary logs<\/h3>\n\n\n\n<pre class=\"wp-block-code line-numbers\"><code class=\" language-python\" data-line=\"\">#!\/bin\/bash\n# backup_binlogs.sh\n\nBINLOG_DIR=&quot;\/var\/log\/mysql&quot;\nBACKUP_DIR=&quot;\/backup\/binlogs&quot;\nDATE=$(date +%Y%m%d)\n\n# Flush \u0442\u0435\u043a\u0443\u0449\u0438\u0445 binlogs\nmariadb -u root -p -e &quot;FLUSH BINARY LOGS;&quot;\n\n# \u041a\u043e\u043f\u0438\u0440\u043e\u0432\u0430\u0442\u044c \u0441\u0442\u0430\u0440\u044b\u0435 binlogs\nmkdir -p &quot;$BACKUP_DIR\/$DATE&quot;\nfind &quot;$BINLOG_DIR&quot; -name &quot;mariadb-bin.&#091;0-9]*&quot; -type f -mtime +1 \\\n    -exec cp {} &quot;$BACKUP_DIR\/$DATE\/&quot; \\;\n\necho &quot;Binary logs backed up to: $BACKUP_DIR\/$DATE&quot;<\/code><\/pre>\n\n\n\n<h3 id=\"rtoc-12\"  class=\"wp-block-heading\">4.2 \u0412\u043e\u0441\u0441\u0442\u0430\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u0435 \u043d\u0430 \u043e\u043f\u0440\u0435\u0434\u0435\u043b\u0435\u043d\u043d\u043e\u0435 \u0432\u0440\u0435\u043c\u044f<\/h3>\n\n\n\n<pre class=\"wp-block-code line-numbers\"><code class=\" language-python\" data-line=\"\">#!\/bin\/bash\n# point_in_time_recovery.sh\n\nBINLOG_DIR=&quot;\/backup\/binlogs\/20251101&quot;\nSTOP_DATETIME=&quot;2025-11-01 14:30:00&quot;\n\n# 1. \u0412\u043e\u0441\u0441\u0442\u0430\u043d\u043e\u0432\u0438\u0442\u044c \u0438\u0437 \u043f\u043e\u043b\u043d\u043e\u0433\u043e \u0431\u044d\u043a\u0430\u043f\u0430\n# (\u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0439\u0442\u0435 restore_incremental.sh)\n\n# 2. \u041f\u0440\u0438\u043c\u0435\u043d\u0438\u0442\u044c binlogs \u0434\u043e \u043d\u0443\u0436\u043d\u043e\u0433\u043e \u0432\u0440\u0435\u043c\u0435\u043d\u0438\nfor binlog in &quot;$BINLOG_DIR&quot;\/mariadb-bin.*; do\n    echo &quot;Applying: $binlog&quot;\n    mariadb-binlog \\\n        --stop-datetime=&quot;$STOP_DATETIME&quot; \\\n        &quot;$binlog&quot; | mariadb -u root -p\ndone\n\necho &quot;\u2705 Point-in-time recovery to $STOP_DATETIME completed&quot;<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 id=\"rtoc-13\"  class=\"wp-block-heading\">5. \u0422\u0435\u0441\u0442\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435 \u0432\u043e\u0441\u0441\u0442\u0430\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u044f<\/h2>\n\n\n\n<h3 id=\"rtoc-14\"  class=\"wp-block-heading\">5.1 \u0415\u0436\u0435\u043c\u0435\u0441\u044f\u0447\u043d\u044b\u0439 \u0442\u0435\u0441\u0442 \u0432\u043e\u0441\u0441\u0442\u0430\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u044f<\/h3>\n\n\n\n<pre class=\"wp-block-code line-numbers\"><code class=\" language-python\" data-line=\"\">#!\/bin\/bash\n# test_restore_monthly.sh\n\nTEST_DATE=$(date +%Y%m%d)\nLATEST_BACKUP=$(ls -t \/backup\/mariabackup\/full_* | head -1)\n\necho &quot;=== Monthly Restore Test: $TEST_DATE ===&quot;\necho &quot;Using backup: $LATEST_BACKUP&quot;\n\n# 1. \u0421\u043e\u0437\u0434\u0430\u0442\u044c \u0442\u0435\u0441\u0442\u043e\u0432\u044b\u0439 \u043a\u043e\u043d\u0442\u0435\u0439\u043d\u0435\u0440\ndocker run -d --name mariadb-restore-test \\\n    -e MYSQL_ROOT_PASSWORD=testpass \\\n    -v &quot;$LATEST_BACKUP&quot;:\/backup \\\n    mariadb:10.11\n\n# 2. \u0412\u043e\u0441\u0441\u0442\u0430\u043d\u043e\u0432\u0438\u0442\u044c \u0431\u044d\u043a\u0430\u043f\ndocker exec mariadb-restore-test bash -c &quot;\n    systemctl stop mariadb\n    rm -rf \/var\/lib\/mysql\/*\n    mariabackup --copy-back --target-dir=\/backup\n    chown -R mysql:mysql \/var\/lib\/mysql\n    systemctl start mariadb\n&quot;\n\n# 3. \u041f\u0440\u043e\u0432\u0435\u0440\u0438\u0442\u044c \u0434\u0430\u043d\u043d\u044b\u0435\nRECORD_COUNT=$(docker exec mariadb-restore-test \\\n    mariadb -u root -ptestpass -N -e \\\n    &#039;SELECT COUNT(*) FROM production_db.users;&#039;)\n\necho &quot;Restored record count: $RECORD_COUNT&quot;\n\n# 4. \u041e\u0447\u0438\u0441\u0442\u043a\u0430\ndocker stop mariadb-restore-test\ndocker rm mariadb-restore-test\n\n# 5. \u041e\u0442\u0447\u0435\u0442\ncat &gt; &quot;\/backup\/test_reports\/restore_test_$TEST_DATE.txt&quot; &lt;&lt; EOF\nRestore Test Report\nDate: $TEST_DATE\nBackup: $LATEST_BACKUP\nRecord Count: $RECORD_COUNT\nStatus: SUCCESS\nEOF\n\necho &quot;\u2705 Test completed&quot;<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 id=\"rtoc-15\"  class=\"wp-block-heading\">6. Disaster recovery<\/h2>\n\n\n\n<h3 id=\"rtoc-16\"  class=\"wp-block-heading\">6.1 Disaster Recovery Plan<\/h3>\n\n\n\n<pre class=\"wp-block-code line-numbers\"><code class=\" language-python\" data-line=\"\">=== MariaDB Disaster Recovery Plan ===\n\nRTO (Recovery Time Objective): 4 \u0447\u0430\u0441\u0430\nRPO (Recovery Point Objective): 1 \u0447\u0430\u0441\n\n\u0428\u0430\u0433\u0438 \u0432\u043e\u0441\u0441\u0442\u0430\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u044f:\n\n1. \u041e\u0426\u0415\u041d\u041a\u0410 (15 \u043c\u0438\u043d\u0443\u0442)\n   - \u041e\u043f\u0440\u0435\u0434\u0435\u043b\u0438\u0442\u044c \u043c\u0430\u0441\u0448\u0442\u0430\u0431 \u043f\u0440\u043e\u0431\u043b\u0435\u043c\u044b\n   - \u041d\u0430\u0439\u0442\u0438 \u043f\u043e\u0441\u043b\u0435\u0434\u043d\u0438\u0439 \u0432\u0430\u043b\u0438\u0434\u043d\u044b\u0439 \u0431\u044d\u043a\u0430\u043f\n   - \u0423\u0432\u0435\u0434\u043e\u043c\u0438\u0442\u044c \u043a\u043e\u043c\u0430\u043d\u0434\u0443\n\n2. \u041f\u041e\u0414\u0413\u041e\u0422\u041e\u0412\u041a\u0410 (30 \u043c\u0438\u043d\u0443\u0442)\n   - \u041f\u043e\u0434\u0433\u043e\u0442\u043e\u0432\u0438\u0442\u044c \u043d\u043e\u0432\u044b\u0439 \u0441\u0435\u0440\u0432\u0435\u0440 (\u0435\u0441\u043b\u0438 \u043d\u0443\u0436\u043d\u043e)\n   - \u0417\u0430\u0433\u0440\u0443\u0437\u0438\u0442\u044c \u0431\u044d\u043a\u0430\u043f\u044b\n   - \u041f\u0440\u043e\u0432\u0435\u0440\u0438\u0442\u044c \u0446\u0435\u043b\u043e\u0441\u0442\u043d\u043e\u0441\u0442\u044c\n\n3. \u0412\u041e\u0421\u0421\u0422\u0410\u041d\u041e\u0412\u041b\u0415\u041d\u0418\u0415 (2 \u0447\u0430\u0441\u0430)\n   - \u0412\u043e\u0441\u0441\u0442\u0430\u043d\u043e\u0432\u0438\u0442\u044c \u0438\u0437 \u043f\u043e\u043b\u043d\u043e\u0433\u043e \u0431\u044d\u043a\u0430\u043f\u0430\n   - \u041f\u0440\u0438\u043c\u0435\u043d\u0438\u0442\u044c \u0438\u043d\u043a\u0440\u0435\u043c\u0435\u043d\u0442\u0430\u043b\u044c\u043d\u044b\u0435 \u0431\u044d\u043a\u0430\u043f\u044b\n   - \u041f\u0440\u0438\u043c\u0435\u043d\u0438\u0442\u044c binlogs (point-in-time)\n\n4. \u041f\u0420\u041e\u0412\u0415\u0420\u041a\u0410 (1 \u0447\u0430\u0441)\n   - \u041f\u0440\u043e\u0432\u0435\u0440\u0438\u0442\u044c \u0434\u0430\u043d\u043d\u044b\u0435\n   - \u0422\u0435\u0441\u0442 \u043f\u043e\u0434\u043a\u043b\u044e\u0447\u0435\u043d\u0438\u0439\n   - Smoke tests \u043f\u0440\u0438\u043b\u043e\u0436\u0435\u043d\u0438\u0439\n\n5. \u041f\u0415\u0420\u0415\u041a\u041b\u042e\u0427\u0415\u041d\u0418\u0415 (30 \u043c\u0438\u043d\u0443\u0442)\n   - \u041e\u0431\u043d\u043e\u0432\u0438\u0442\u044c DNS\/\u043a\u043e\u043d\u0444\u0438\u0433\u0438\n   - \u041f\u0435\u0440\u0435\u043a\u043b\u044e\u0447\u0438\u0442\u044c \u0442\u0440\u0430\u0444\u0438\u043a\n   - \u041c\u043e\u043d\u0438\u0442\u043e\u0440\u0438\u043d\u0433<\/code><\/pre>\n\n\n\n<h3 id=\"rtoc-17\"  class=\"wp-block-heading\">6.2 \u0421\u043a\u0440\u0438\u043f\u0442 \u0430\u0432\u0442\u043e\u043c\u0430\u0442\u0438\u0447\u0435\u0441\u043a\u043e\u0433\u043e DR<\/h3>\n\n\n\n<pre class=\"wp-block-code line-numbers\"><code class=\" language-python\" data-line=\"\">#!\/bin\/bash\n# disaster_recovery.sh\n\nBACKUP_SERVER=&quot;backup.company.com&quot;\nBACKUP_USER=&quot;backup&quot;\nLATEST_BACKUP_DIR=&quot;\/backups\/mariadb\/latest&quot;\n\necho &quot;=== DISASTER RECOVERY INITIATED ===&quot;\necho &quot;Timestamp: $(date)&quot;\n\n# 1. \u041e\u0441\u0442\u0430\u043d\u043e\u0432\u0438\u0442\u044c \u043f\u0440\u043e\u0431\u043b\u0435\u043c\u043d\u044b\u0439 \u0441\u0435\u0440\u0432\u0435\u0440 (\u0435\u0441\u043b\u0438 \u0440\u0430\u0431\u043e\u0442\u0430\u0435\u0442)\nsudo systemctl stop mariadb\n\n# 2. \u0417\u0430\u0433\u0440\u0443\u0437\u0438\u0442\u044c \u043f\u043e\u0441\u043b\u0435\u0434\u043d\u0438\u0439 \u0431\u044d\u043a\u0430\u043f \u0441 backup \u0441\u0435\u0440\u0432\u0435\u0440\u0430\necho &quot;&#091;$(date)] Downloading latest backup...&quot;\nrsync -avz --progress \\\n    &quot;$BACKUP_USER@$BACKUP_SERVER:$LATEST_BACKUP_DIR\/&quot; \\\n    \/tmp\/restore_backup\/\n\n# 3. \u041f\u043e\u0434\u0433\u043e\u0442\u043e\u0432\u0438\u0442\u044c \u0431\u044d\u043a\u0430\u043f\necho &quot;&#091;$(date)] Preparing backup...&quot;\nmariabackup --prepare --target-dir=\/tmp\/restore_backup\/\n\n# 4. \u0412\u043e\u0441\u0441\u0442\u0430\u043d\u043e\u0432\u0438\u0442\u044c\necho &quot;&#091;$(date)] Restoring data...&quot;\nsudo rm -rf \/var\/lib\/mysql\/*\nmariabackup --copy-back --target-dir=\/tmp\/restore_backup\/\nsudo chown -R mysql:mysql \/var\/lib\/mysql\n\n# 5. \u0417\u0430\u043f\u0443\u0441\u0442\u0438\u0442\u044c MariaDB\necho &quot;&#091;$(date)] Starting MariaDB...&quot;\nsudo systemctl start mariadb\n\n# 6. \u041f\u0440\u043e\u0432\u0435\u0440\u043a\u0430\necho &quot;&#091;$(date)] Verification...&quot;\nmariadb -u root -p -e &quot;SELECT COUNT(*) FROM production_db.users;&quot;\n\necho &quot;\u2705 DISASTER RECOVERY COMPLETED&quot;\necho &quot;Please verify all data and applications&quot;<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 id=\"rtoc-18\"  class=\"wp-block-heading\">\u0410\u0432\u0442\u043e\u043c\u0430\u0442\u0438\u0437\u0430\u0446\u0438\u044f (Cron)<\/h2>\n\n\n\n<pre class=\"wp-block-code line-numbers\"><code class=\" language-python\" data-line=\"\"># \u0414\u043e\u0431\u0430\u0432\u0438\u0442\u044c \u0432 crontab\n\n# \u041f\u043e\u043b\u043d\u044b\u0439 \u0431\u044d\u043a\u0430\u043f \u043a\u0430\u0436\u0434\u043e\u0435 \u0432\u043e\u0441\u043a\u0440\u0435\u0441\u0435\u043d\u044c\u0435 \u0432 2:00\n0 2 * * 0 \/usr\/local\/bin\/mariabackup_full.sh\n\n# \u0418\u043d\u043a\u0440\u0435\u043c\u0435\u043d\u0442\u0430\u043b\u044c\u043d\u044b\u0439 \u0435\u0436\u0435\u0434\u043d\u0435\u0432\u043d\u043e \u0432 2:00 (\u043a\u0440\u043e\u043c\u0435 \u0432\u043e\u0441\u043a\u0440\u0435\u0441\u0435\u043d\u044c\u044f)\n0 2 * * 1-6 \/usr\/local\/bin\/mariabackup_incremental.sh\n\n# Binary logs \u043a\u0430\u0436\u0434\u044b\u0439 \u0447\u0430\u0441\n0 * * * * \/usr\/local\/bin\/backup_binlogs.sh\n\n# \u0420\u043e\u0442\u0430\u0446\u0438\u044f \u0441\u0442\u0430\u0440\u044b\u0445 \u0431\u044d\u043a\u0430\u043f\u043e\u0432\n0 3 * * * find \/backup\/mariabackup -type d -mtime +30 -exec rm -rf {} \\;\n\n# \u0415\u0436\u0435\u043c\u0435\u0441\u044f\u0447\u043d\u044b\u0439 \u0442\u0435\u0441\u0442 \u0432\u043e\u0441\u0441\u0442\u0430\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u044f\n0 4 1 * * \/usr\/local\/bin\/test_restore_monthly.sh<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 id=\"rtoc-19\"  class=\"wp-block-heading\">\u0427\u0435\u043a-\u043b\u0438\u0441\u0442<\/h2>\n\n\n\n<h3 id=\"rtoc-20\"  class=\"wp-block-heading\">\u2705 \u041e\u0431\u044f\u0437\u0430\u0442\u0435\u043b\u044c\u043d\u044b\u0435 \u0434\u0435\u0439\u0441\u0442\u0432\u0438\u044f<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>[ ] \u0421\u0442\u0440\u0430\u0442\u0435\u0433\u0438\u044f \u0431\u044d\u043a\u0430\u043f\u043e\u0432 \u043e\u043f\u0440\u0435\u0434\u0435\u043b\u0435\u043d\u0430<\/li>\n\n\n\n<li>[ ] mariabackup \u043d\u0430\u0441\u0442\u0440\u043e\u0435\u043d<\/li>\n\n\n\n<li>[ ] \u041f\u043e\u043b\u043d\u044b\u0435 \u0431\u044d\u043a\u0430\u043f\u044b \u0430\u0432\u0442\u043e\u043c\u0430\u0442\u0438\u0437\u0438\u0440\u043e\u0432\u0430\u043d\u044b<\/li>\n\n\n\n<li>[ ] \u0418\u043d\u043a\u0440\u0435\u043c\u0435\u043d\u0442\u0430\u043b\u044c\u043d\u044b\u0435 \u0431\u044d\u043a\u0430\u043f\u044b \u043d\u0430\u0441\u0442\u0440\u043e\u0435\u043d\u044b<\/li>\n\n\n\n<li>[ ] Binary logs \u0441\u043e\u0445\u0440\u0430\u043d\u044f\u044e\u0442\u0441\u044f<\/li>\n\n\n\n<li>[ ] \u0422\u0435\u0441\u0442\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435 \u0432\u043e\u0441\u0441\u0442\u0430\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u044f \u0440\u0435\u0433\u0443\u043b\u044f\u0440\u043d\u043e\u0435<\/li>\n\n\n\n<li>[ ] Disaster Recovery Plan \u0434\u043e\u043a\u0443\u043c\u0435\u043d\u0442\u0438\u0440\u043e\u0432\u0430\u043d<\/li>\n\n\n\n<li>[ ] Off-site \u043a\u043e\u043f\u0438\u0438 \u043d\u0430\u0441\u0442\u0440\u043e\u0435\u043d\u044b<\/li>\n<\/ul>\n\n\n\n<h3 id=\"rtoc-21\"  class=\"wp-block-heading\">\ud83d\udd04 \u041f\u0440\u043e\u0432\u0435\u0440\u043a\u0438<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>[ ] \u041f\u043e\u0441\u043b\u0435\u0434\u043d\u0438\u0439 \u0431\u044d\u043a\u0430\u043f \u0443\u0441\u043f\u0435\u0448\u0435\u043d<\/li>\n\n\n\n<li>[ ] \u0422\u0435\u0441\u0442 \u0432\u043e\u0441\u0441\u0442\u0430\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u044f \u043f\u0440\u043e\u0439\u0434\u0435\u043d<\/li>\n\n\n\n<li>[ ] \u0414\u043e\u0441\u0442\u0430\u0442\u043e\u0447\u043d\u043e \u043c\u0435\u0441\u0442\u0430 \u0434\u043b\u044f \u0431\u044d\u043a\u0430\u043f\u043e\u0432<\/li>\n\n\n\n<li>[ ] RTO\/RPO \u0441\u043e\u0431\u043b\u044e\u0434\u0430\u044e\u0442\u0441\u044f<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 id=\"rtoc-22\"  class=\"wp-block-heading\">\u0421\u043b\u0435\u0434\u0443\u044e\u0449\u0438\u0439 \u0448\u0430\u0433<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong><a href=\".\/12-monitoring-maintenance.md\">\u2192 \u0413\u041b\u0410\u0412\u0410 12: \u041c\u043e\u043d\u0438\u0442\u043e\u0440\u0438\u043d\u0433 \u0438 \u043e\u0431\u0441\u043b\u0443\u0436\u0438\u0432\u0430\u043d\u0438\u0435<\/a><\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u0412 \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0435\u0439 \u0433\u043b\u0430\u0432\u0435:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Prometheus + Grafana<\/li>\n\n\n\n<li>Alerting<\/li>\n\n\n\n<li>\u0420\u0435\u0433\u0443\u043b\u044f\u0440\u043d\u043e\u0435 \u043e\u0431\u0441\u043b\u0443\u0436\u0438\u0432\u0430\u043d\u0438\u0435<\/li>\n\n\n\n<li>Performance monitoring<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>\u0420\u0443\u043a\u043e\u0432\u043e\u0434\u0441\u0442\u0432\u043e \u043f\u043e \u043c\u0438\u0433\u0440\u0430\u0446\u0438\u0438 MySQL \u2192 MariaDB. \u0413\u041b\u0410\u0412\u0410 11: \u0420\u0435\u0437\u0435\u0440\u0432\u043d\u043e\u0435 \u043a\u043e\u043f\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435 \u0438 \u0432\u043e\u0441\u0441\u0442\u0430\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u0435. \u0426\u0435\u043b\u044c \u0433\u043b\u0430\u0432\u044b: \u041d\u0430\u0441\u0442\u0440\u043e\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u0443\u044e \u0441\u0438\u0441\u0442\u0435\u043c\u0443 \u0440\u0435\u0437\u0435\u0440\u0432\u043d\u043e\u0433\u043e \u043a\u043e\u043f\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f<\/p>\n","protected":false},"author":1,"featured_media":3794,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","_themeisle_gutenberg_block_has_review":false,"footnotes":""},"categories":[967],"tags":[975,976,973,972,974],"class_list":["post-3788","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category---mysql--mariadb","tag-db","tag-dba","tag-mariadb","tag-mysql","tag-974"],"acf":[],"_links":{"self":[{"href":"https:\/\/ru.davidka.net\/ru\/wp-json\/wp\/v2\/posts\/3788","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ru.davidka.net\/ru\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ru.davidka.net\/ru\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ru.davidka.net\/ru\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ru.davidka.net\/ru\/wp-json\/wp\/v2\/comments?post=3788"}],"version-history":[{"count":0,"href":"https:\/\/ru.davidka.net\/ru\/wp-json\/wp\/v2\/posts\/3788\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ru.davidka.net\/ru\/wp-json\/wp\/v2\/media\/3794"}],"wp:attachment":[{"href":"https:\/\/ru.davidka.net\/ru\/wp-json\/wp\/v2\/media?parent=3788"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ru.davidka.net\/ru\/wp-json\/wp\/v2\/categories?post=3788"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ru.davidka.net\/ru\/wp-json\/wp\/v2\/tags?post=3788"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}