{"id":634,"date":"2021-10-27T20:28:20","date_gmt":"2021-10-27T19:28:20","guid":{"rendered":"https:\/\/www.flat-planet.net\/?p=634"},"modified":"2021-10-27T20:30:53","modified_gmt":"2021-10-27T19:30:53","slug":"scripted-mysql-db-backups","status":"publish","type":"post","link":"https:\/\/flat-planet.net\/?p=634","title":{"rendered":"Scripted MySQL DB Backups"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Background<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Recently my homeassistant local DB (RPI-based), which is an SQLite DB, got corrupted during an upgrade, so this was an incentive to migrate to holding the data in the MySQL server I have running anyways as;<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>This is more reliable<\/li><li>Less writes to the SDcard<\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">But it made me realise that my MySQL server backups weren&#8217;t exactly regular, and I have a number of things using the DB (kodi, now homeassistant, website, etc.) that I&#8217;d be quite annoyed to lose.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Running backups using mysqldump is a normal way of doing things, but has the problem of recording the password in some way, shape or form. Fortunately to do a backup, only read-type permissions are required.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Backup Account<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">We create an account on the server, BACKUP_USER, that can only connect from the localhost, that has read-only permissions for all databases and tables<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nGRANT LOCK TABLES, SELECT, SHOW VIEW ON *.* TO &#039;BACKUP_USER&#039;@&#039;localhost&#039; IDENTIFIED BY &#039;secretReadonlyPassword&#039;;\nFLUSH PRIVILEGES;\n<\/pre><\/div>\n\n\n<h2 class=\"wp-block-heading\">Script and Cron Job<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Then we have a simple bash script, executed daily\/weekly, to trigger the backups, and only keep X number around.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n#!\/bin\/bash\n\n# Set the backup date\nBACKUP_DATE=`date +%Y-%m-%d`\nBACKUP_DIR=\/backup_location\/sql_database\nBACKUP_HISTORY=20\n\n# Dump the database\nmysqldump -u BACKUP_USER -p &#039;secretReadonlyPassword&#039; --all-databases --add-drop-database | gzip -c &gt; $BACKUP_DIR\/dump_$BACKUP_DATE.sql.gz\n\n# Remove all but the latest backups\ncd $BACKUP_DIR\nls -t | tail -n +$BACKUP_HISTORY | xargs rm --\n<\/pre><\/div>","protected":false},"excerpt":{"rendered":"<p>Background Recently my homeassistant local DB (RPI-based), which is an SQLite DB, got corrupted during an upgrade, so this was an incentive to migrate to holding the data in the MySQL server I have running anyways as; This is more reliable Less writes to the SDcard But it made me realise that my MySQL server [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[38],"tags":[42,122,411],"series":[],"class_list":["post-634","post","type-post","status-publish","format-standard","hentry","category-linux","tag-linux-2","tag-mysql","tag-backups"],"_links":{"self":[{"href":"https:\/\/flat-planet.net\/index.php?rest_route=\/wp\/v2\/posts\/634","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/flat-planet.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/flat-planet.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/flat-planet.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/flat-planet.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=634"}],"version-history":[{"count":2,"href":"https:\/\/flat-planet.net\/index.php?rest_route=\/wp\/v2\/posts\/634\/revisions"}],"predecessor-version":[{"id":637,"href":"https:\/\/flat-planet.net\/index.php?rest_route=\/wp\/v2\/posts\/634\/revisions\/637"}],"wp:attachment":[{"href":"https:\/\/flat-planet.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=634"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/flat-planet.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=634"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/flat-planet.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=634"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/flat-planet.net\/index.php?rest_route=%2Fwp%2Fv2%2Fseries&post=634"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}