{"id":1058,"date":"2021-04-05T12:19:16","date_gmt":"2021-04-05T10:19:16","guid":{"rendered":"https:\/\/devpath.pro\/?p=1058"},"modified":"2021-04-05T12:19:16","modified_gmt":"2021-04-05T10:19:16","slug":"save-yourself-from-a-disaster-3-redundancy-of-database","status":"publish","type":"post","link":"https:\/\/fabiocicerchia.it\/web\/save-yourself-from-a-disaster-3-redundancy-of-database","title":{"rendered":"Save yourself from a disaster #3: Redundancy of Database"},"content":{"rendered":"<p style=\"text-align: center;\">This is the third 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><a href=\"https:\/\/devpath.pro\/web\/save-yourself-from-a-disaster-1-secure-the-database\/\">Backups<\/a><\/li>\n<li>Redundancy<\/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>Redundancy<\/h2>\n<p>Create a cluster to have at least a structure like <del>master\/slave<\/del> primary\/secondary, <a href=\"https:\/\/www.scalecomputing.com\/blog\/best-practice-always-3-nodes-minimum-in-a-cluster\">3 nodes<\/a> will be recommended so we&#8217;ll have the flexibility to do planned maintenance without suffering and\/or affecting the performance of the whole cluster.<\/p>\n<p><strong>Spin up a secondary node<\/strong><\/p>\n<p>Create another VM somewhere else (better if in another availability zone\/region\/provider), then configure a MySQL\/MariaDB\/Percona\/&#8230; instance and <a href=\"https:\/\/www.digitalocean.com\/community\/tutorials\/how-to-set-up-master-slave-replication-in-mysql\">plug it in as a secondary node<\/a>.<br \/>\nWe can set it up even with fewer resources and make it the write-only node (in case we have less writing activity, otherwise the read-only one).<\/p>\n<p><strong>Balancing requests<\/strong><\/p>\n<p>I prefer to use something like HAProxy as a TCP load balancer, or (even better) using ProxySQL (which has a nice query caching capability). I&#8217;d go with <a href=\"https:\/\/www.digitalocean.com\/community\/tutorials\/how-to-use-proxysql-as-a-load-balancer-for-mysql-on-ubuntu-16-04\">ProxySQL load balancing<\/a> the 2 nodes created, then just change the database connection string in the application and the setup is done (we could even partition the queries and define to which node they should be sent).<\/p>\n<p>In my case, a primary\/secondary topology could more than enough, but I went for a primary\/primary configuration (you can follow a\u00a0<a href=\"https:\/\/www.ryadel.com\/en\/mysql-master-master-replication-setup-in-5-easy-steps\/\">simple tutorial<\/a> or a <a href=\"https:\/\/www.digitalocean.com\/community\/tutorials\/how-to-configure-mysql-group-replication-on-ubuntu-16-04\">more structured configuration<\/a>)\u00a0without balancing (because each web node will access their local DB instance).<\/p>\n<p><strong>Security<\/strong><\/p>\n<p>The replica must be done over a secure connection, so you need to <a href=\"http:\/\/llawlor.github.io\/2016\/03\/14\/mysql-ssl-replication.html\">generate a certificate and use it<\/a>.<\/p>\n<hr \/>\n<p>The next post will be about Redundancy of\u00a0Storage, 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 third 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 April 5, 2021","modified":"Updated on April 5, 2021"},"absolute_dates_time":{"created":"Posted on April 5, 2021 12:19 pm","modified":"Updated on April 5, 2021 12:19 pm"},"featured_img_caption":"","series_order":"","_links":{"self":[{"href":"https:\/\/fabiocicerchia.it\/wp-json\/wp\/v2\/posts\/1058"}],"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=1058"}],"version-history":[{"count":0,"href":"https:\/\/fabiocicerchia.it\/wp-json\/wp\/v2\/posts\/1058\/revisions"}],"wp:attachment":[{"href":"https:\/\/fabiocicerchia.it\/wp-json\/wp\/v2\/media?parent=1058"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/fabiocicerchia.it\/wp-json\/wp\/v2\/categories?post=1058"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/fabiocicerchia.it\/wp-json\/wp\/v2\/tags?post=1058"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}