{"id":953,"date":"2021-03-22T15:32:37","date_gmt":"2021-03-22T14:32:37","guid":{"rendered":"https:\/\/devpath.pro\/?p=953"},"modified":"2021-03-22T15:32:37","modified_gmt":"2021-03-22T14:32:37","slug":"save-yourself-from-a-disaster-1-secure-the-database","status":"publish","type":"post","link":"https:\/\/fabiocicerchia.it\/web\/save-yourself-from-a-disaster-1-secure-the-database","title":{"rendered":"Save yourself from a disaster #1: Secure the Database"},"content":{"rendered":"<p style=\"text-align: center;\">This is the first part of the series <a href=\"https:\/\/devpath.pro\/web\/save-yourself-from-a-disaster-redundancy-on-a-budget\/\">Save yourself from a disaster: Redundancy on a budget<\/a>.<\/p>\n<p>How can we make sure our most important asset (which is the DB) is safely secured in case of a disaster?<\/p>\n<p>We could do mainly 2 things (and you better do both of them):<\/p>\n<ul>\n<li>Backups<\/li>\n<li><a href=\"https:\/\/devpath.pro\/web\/save-yourself-from-a-disaster-3-redundancy-of-database\/\">Redundancy<\/a><\/li>\n<\/ul>\n<h3><!--more--><\/h3>\n<div style=\"border: 1px solid darkyellow; background: lightyellow; padding: 0.5em; line-height: 0.9em;\"><small><strong>Disclaimer<\/strong><br \/>\nThis guide won&#8217;t cover everything, it won&#8217;t be a comprehensive guide, and the steps that are shown need to be carefully reviewed and tested in your development\/pre-production environment. I don&#8217;t take any responsibility for any damage, interruption of service nor leak\/loss of data for the use of the instructions in the ebook (nor from any external website I&#8217;ve mentioned).<\/small><\/div>\n<h2>Backups<\/h2>\n<p>Start doing the DB backups (with <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/mysqldump.html\">mysqldump<\/a> or <a href=\"https:\/\/www.percona.com\/doc\/percona-xtrabackup\/2.4\/index.html\">xtrabackup<\/a>) and define a policy for <a href=\"https:\/\/www.ibm.com\/services\/business-continuity\/rto\">RTO<\/a> and <a href=\"https:\/\/www.ibm.com\/services\/business-continuity\/rpo\">RPO<\/a>, so you&#8217;ll know what is the accepted loss (there&#8217;s always loss &#8211; even if very minimal). RTO defines how long can the infrastructure can be down, and RPO defines how much data can you afford to lose (ie. how old the latest backup is).<\/p>\n<p><strong>Rotation<\/strong><\/p>\n<p>To rotate the DB backups we could simply use <a href=\"https:\/\/linux.die.net\/man\/8\/logrotate\">logrotate<\/a>.<br \/>\nWe could simply start with a basic daily backup rotation (or any interval you have defined as RPO):<\/p>\n<pre>\/var\/backups\/daily\/alldb.sql.gz {\n  notifempty\n  daily\n  rotate 7\n  nocompress\n  create 640 root adm\n  dateext\n  dateformat -%Y%m%d-%s\n\n  postrotate\n    mysqldump -u$USER -p$PASSWD --single-transaction --all-databases | gzip -9f &gt; \/var\/backups\/daily\/alldb.sql.gz\n  endscript\n}\n<\/pre>\n<p>This will create the rotated DB backups on the <span style=\"text-decoration: underline;\">same<\/span> server where logrotate is running (most likely the same DB instance). We have seen that this is very wrong, so you must always store the backups somewhere else (and also offline).<\/p>\n<p><strong>Remote Storage<\/strong><br \/>\nWith a simple change, we can upload to an <a href=\"https:\/\/docs.aws.amazon.com\/AmazonS3\/latest\/userguide\/create-bucket-overview.html\">AWS S3 bucket<\/a> (with cold storage access set to <a href=\"https:\/\/docs.aws.amazon.com\/AmazonS3\/latest\/userguide\/storage-class-intro.html#sc-glacier\">rarely-used<\/a>):<\/p>\n<pre>  lastaction\n    BUCKET=\"...\"\n    REGION=\"eu-west-1\"\n    aws s3 sync \/var\/backups\/hourly \"s3:\/\/$BUCKET\/daily\/\" --region $REGION --exclude \"*\" --include \"*.gz-$FORMAT*\" --storage-class GLACIER\n  endscript\n<\/pre>\n<p><strong>Local Storage<\/strong><\/p>\n<p>Just do a rsync (better if scheduled) to download it locally to an external hard-drive:<\/p>\n<pre>rsync -e \"ssh -i $HOME\/.ssh\/id_rsa\" --progress -auv &lt;USER&gt;@&lt;IP&gt;:\/var\/backups .\/path\/to\/backups<\/pre>\n<p>There you go, you have now backups on-site (for faster restore), remote on another provider (for more reliability), offline (for more peace of mind).<\/p>\n<p><strong>Security<\/strong><\/p>\n<p>Remember the good practices, and do not forget about <a href=\"https:\/\/www.backup-systems.co.uk\/blog\/6-gdpr-implications-on-data-backup-and-disaster-recovery\">GDPR<\/a>, the backups must be stored <a href=\"https:\/\/severalnines.com\/database-blog\/how-encrypt-your-mysql-mariadb-backups\">encrypted at-rest<\/a> (and use a <a href=\"https:\/\/www.czeskis.com\/random\/openssl-encrypt-file.html\">key instead of a plain password<\/a>).<\/p>\n<h2>Restore<\/h2>\n<p>Once everything is backed up, you need to think about how to restore the dump properly, or at least switch the connection to the other node. I&#8217;ll cover this in the Disaster Recovery Plan post.<\/p>\n<hr \/>\n<p>The next post will be about Secure the Storage, Stay Tuned.<\/p>\n<p style=\"text-align: center; text-decoration: underline; color: darkblue; font-weight: bold;\">Check out the whole version of this post in the <a style=\"color: #000080; text-decoration: underline;\" href=\"https:\/\/leanpub.com\/savefromdisaster\">ebook.<br \/>\n<img decoding=\"async\" style=\"height: 100px;\" src=\"https:\/\/d2sofvawe08yqg.cloudfront.net\/savefromdisaster\/small\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>This is the first part of the series Save yourself from a disaster: Redundancy on a budget. How can we make sure our most important asset (which is the DB) is safely secured in case of a disaster? We could do mainly 2 things (and you better do both of them): Backups Redundancy<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advgb_blocks_editor_width":"","advgb_blocks_columns_visual_guide":"","footnotes":""},"categories":[14],"tags":[122,123,124,125],"aioseo_notices":[],"author_meta":{"display_name":"fabio","author_link":"https:\/\/fabiocicerchia.it\/author\/fabio"},"featured_img":null,"coauthors":[],"tax_additional":{"categories":{"linked":["<a href=\"https:\/\/fabiocicerchia.it\/category\/web\" class=\"advgb-post-tax-term\">Web<\/a>"],"unlinked":["<span class=\"advgb-post-tax-term\">Web<\/span>"]},"tags":{"linked":["<a href=\"https:\/\/fabiocicerchia.it\/category\/web\" class=\"advgb-post-tax-term\">disaster recovery<\/a>","<a href=\"https:\/\/fabiocicerchia.it\/category\/web\" class=\"advgb-post-tax-term\">ovh<\/a>","<a href=\"https:\/\/fabiocicerchia.it\/category\/web\" class=\"advgb-post-tax-term\">redundancy<\/a>","<a href=\"https:\/\/fabiocicerchia.it\/category\/web\" class=\"advgb-post-tax-term\">sbg2<\/a>"],"unlinked":["<span class=\"advgb-post-tax-term\">disaster recovery<\/span>","<span class=\"advgb-post-tax-term\">ovh<\/span>","<span class=\"advgb-post-tax-term\">redundancy<\/span>","<span class=\"advgb-post-tax-term\">sbg2<\/span>"]}},"comment_count":"0","relative_dates":{"created":"Posted 3 years ago","modified":"Updated 3 years ago"},"absolute_dates":{"created":"Posted on March 22, 2021","modified":"Updated on March 22, 2021"},"absolute_dates_time":{"created":"Posted on March 22, 2021 3:32 pm","modified":"Updated on March 22, 2021 3:32 pm"},"featured_img_caption":"","series_order":"","_links":{"self":[{"href":"https:\/\/fabiocicerchia.it\/wp-json\/wp\/v2\/posts\/953"}],"collection":[{"href":"https:\/\/fabiocicerchia.it\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/fabiocicerchia.it\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/fabiocicerchia.it\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/fabiocicerchia.it\/wp-json\/wp\/v2\/comments?post=953"}],"version-history":[{"count":0,"href":"https:\/\/fabiocicerchia.it\/wp-json\/wp\/v2\/posts\/953\/revisions"}],"wp:attachment":[{"href":"https:\/\/fabiocicerchia.it\/wp-json\/wp\/v2\/media?parent=953"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/fabiocicerchia.it\/wp-json\/wp\/v2\/categories?post=953"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/fabiocicerchia.it\/wp-json\/wp\/v2\/tags?post=953"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}