{"id":93,"date":"2011-11-08T13:44:04","date_gmt":"2011-11-08T16:44:04","guid":{"rendered":"http:\/\/wagnerbianchi.com\/blog\/?p=93"},"modified":"2011-11-08T14:01:13","modified_gmt":"2011-11-08T17:01:13","slug":"mysql-replication-topologies","status":"publish","type":"post","link":"http:\/\/wagnerbianchi.com\/blog\/?p=93","title":{"rendered":"MySQL Replication Topologies"},"content":{"rendered":"<p>You should know that MySQL team has been doing a good job and that product naturally is being a great option when the chat is <em>horizontal scale<\/em> or<em> Scale-Out too<\/em>. I mentioned the last &#8220;too&#8221; cause MySQL has been doing a so\u00a0confident\u00a0job in vertical scaling on its availability with hands on InnoDB Storage Engine. But, treating about MySQL Replication and Scale-Out points, MySQL has its good features as that three supported kinds of data replication:<\/p>\n<ul>\n<li>Synchronous\u00a0data replication, used just when you&#8217;re using MySQL Cluster (data replication between Data Nodes);<\/li>\n<li>Asynchronous\u00a0and\u00a0Semi-synchronous\u00a0replication, to replicate data using servers available as a MASTER and SLAVE, having MASTER a lot of SLAVEs and a SLAVE a unique MASTER.<\/li>\n<\/ul>\n<div>This post are being write just to highlight the kinds of topology\u00a0mentioned\u00a0by Oracle and some other that we can create to solve a specific problem inside a company. To mention the existent kinds, it will need to explain more about the &#8220;map of availability&#8221;, created by MySQL AB.<\/div>\n<div>As say a friend from USA, it is &#8220;<em>easy peasy<\/em>&#8221; \u00a0to understand this graph and work with on your organization strategy. Starting from Small Business where normally a little and small amount of availability is required to maintain the business continuity, you can set up just only a instance of MySQL to get it working well, with small management applied to this environment. We can realizing, looking graph that this small business could count with 35 days of downtime on worst cases.\u00a0As we will rising the graph, we will seeing new situations and the number of nines will growing (high availability nines).<\/div>\n<div>.<\/div>\n<div><strong>Topology 1: Single<\/strong><\/div>\n<div>.<\/div>\n<div>The fist one I will comment is the &#8220;Simple&#8221; topology, normally used when\u00a0organization\u00a0is looking for data redundancy and backup process improvements. Simply, it will operates with two servers actuating as a MASTER and SLAVE. The good touch here is to adapt application to write data on MASTER and just read data from SLAVE. It will provide good\u00a0improvements\u00a0and will alleviate workload if you were operating with a single server to\u00a0respond all <em>app<\/em> requests\u00a0.<\/div>\n<div>\n<div id=\"attachment_268\" style=\"width: 269px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/wbianchi.files.wordpress.com\/2011\/07\/rpl_single.jpg\"><img aria-describedby=\"caption-attachment-268\" decoding=\"async\" loading=\"lazy\" class=\"size-full wp-image-268\" title=\"rpl_single\" src=\"http:\/\/wbianchi.files.wordpress.com\/2011\/07\/rpl_single.jpg\" alt=\"master-slave\" width=\"259\" height=\"156\" \/><\/a><p id=\"caption-attachment-268\" class=\"wp-caption-text\">The main server (rounded with red) acts as a MASTER and the other, as a SLAVE - that last must be configured with read-only=1<\/p><\/div>\n<\/div>\n<div>In this case your normally will configure MySQL running into SLAVE server with read-only=1, as showed below:<\/div>\n<div>.<\/div>\n<div><span class=\"Apple-style-span\" style=\"font-family: Consolas, Monaco, monospace; font-size: 12px; line-height: 18px; white-space: pre;\">mysql&gt; SET GLOBAL read_only=1;<\/span><\/div>\n<div>.<\/div>\n<div><strong>Topology 2: Multiple<\/strong><\/div>\n<div>.<\/div>\n<div>As the name says, on this topology we can have many servers looking for a unique MASTER, building what we know as a multiple topology. It will be pretty suitable when the environment has the necessity to advance to a\u00a0multiple\u00a0divided workload, which on you can let you app write data on MASTER, read from one of the SLAVEs servers (you can apply a kind of load-balancing as <a href=\"http:\/\/comments.gmane.org\/gmane.comp.db.mysql.cluster\/1709\" target=\"_blank\">mysql-proxy<\/a> or <a href=\"http:\/\/comments.gmane.org\/gmane.comp.db.mysql.cluster\/1709\" target=\"_blank\">F5 LTM<\/a>) and let the other to extract security copies to avoid\u00a0interfere on those production servers. It is a common situation where we have high workload and must backup databases at least two times a day &#8211; in this case it is good to use the snapshot backup supported by <a href=\"http:\/\/dev.mysql.com\/doc\/mysql-enterprise-backup\/3.5\/en\/intro.html\" target=\"_blank\">MySQL Enterprise\u00a0Backup<\/a>, <a href=\"http:\/\/www.scribd.com\/doc\/14683222\/Xtrabackup-online-backup-for-InnoDBXTraDB\" target=\"_blank\">Xtrabackup<\/a>\u00a0or <a href=\"http:\/\/www.zmanda.com\/zrm-mysql-enterprise.html\" target=\"_blank\">Zmanda<\/a>.<\/div>\n<div>\n<div id=\"attachment_269\" style=\"width: 255px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/wbianchi.files.wordpress.com\/2011\/07\/rpl_multiple.jpg\"><img aria-describedby=\"caption-attachment-269\" decoding=\"async\" loading=\"lazy\" class=\"size-full wp-image-269\" title=\"rpl_multiple\" src=\"http:\/\/wbianchi.files.wordpress.com\/2011\/07\/rpl_multiple.jpg\" alt=\"\" width=\"245\" height=\"238\" \/><\/a><p id=\"caption-attachment-269\" class=\"wp-caption-text\">You could set up much more servers than two depicted above!<\/p><\/div>\n<\/div>\n<div><strong>Topology 3: Chain<\/strong><\/div>\n<div><\/div>\n<div>This topology will simply provide that story of replicate data on Master(1) &lt;- Master\/Slave(2) &lt;- Slave(3) architecture. This is good when you have a\u00a0departmental servers available separately inside your organization to attend many areas with as less time as possible. With this topology replication model, you will be able to adjust applications to write data on server A and B (INSERT and DELETE), scaling writes using both mentioned servers. The third one could be used to serve reports and backup as a read only server (just SELECT). What we cannot forget is to set the log-slave-updates on server&#8217;s B my.cnf due to this server will be MASTER and SLAVE at the same time (MySQL Manual Page: http:\/\/bit.ly\/nGTQO1).<\/div>\n<div>\n<div id=\"attachment_272\" style=\"width: 383px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/wbianchi.files.wordpress.com\/2011\/07\/rpl_chain.jpg\"><img aria-describedby=\"caption-attachment-272\" decoding=\"async\" loading=\"lazy\" class=\"size-full wp-image-272\" title=\"rpl_chain\" src=\"http:\/\/wbianchi.files.wordpress.com\/2011\/07\/rpl_chain.jpg\" alt=\"rpl_chain\" width=\"373\" height=\"152\" \/><\/a><p id=\"caption-attachment-272\" class=\"wp-caption-text\">MASTER(A) &lt;-&gt; MASTER(B) -&gt; SLAVE(C) - Attention to configure out -log-slave-updates on server (B)<\/p><\/div>\n<\/div>\n<div>\n<blockquote><p>Normally, a slave does not log to its own binary log any updates that are received from a master server. This option tells the slave to log the updates performed by its SQL thread to its own binary log. For this option to have any effect, the slave must also be started with the\u00a0<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/replication-options-binary-log.html#option_mysqld_log-bin\"><code>--log-bin<\/code><\/a>\u00a0option to enable binary logging.\u00a0<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/replication-options-slave.html#option_mysqld_log-slave-updates\"><code>--log-slave-updates<\/code><\/a>\u00a0is used when you want to chain replication servers. For example, you might want to set up replication servers using this arrangement:<\/p>\n<pre>A -> B -> C<\/pre>\n<p>Here,\u00a0<code>A<\/code>\u00a0serves as the master for the slave\u00a0<code>B<\/code>, and\u00a0<code>B<\/code>\u00a0serves as the master for the slave\u00a0<code>C<\/code>. For this to work,\u00a0<code>B<\/code>\u00a0must be both a master\u00a0<em>and<\/em>\u00a0a slave. You must start both\u00a0<code>A<\/code>\u00a0and\u00a0<code>B<\/code>\u00a0with\u00a0<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/replication-options-binary-log.html#option_mysqld_log-bin\"><code>--log-bin<\/code><\/a>\u00a0to enable binary logging, and\u00a0<code>B<\/code>\u00a0with the\u00a0<a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/replication-options-slave.html#option_mysqld_log-slave-updates\"><code>--log-slave-updates<\/code><\/a>\u00a0option so that updates received from\u00a0<code>A<\/code>\u00a0are logged by\u00a0<code>B<\/code>\u00a0to its binary log.<\/p><\/blockquote>\n<\/div>\n<div><strong>Topology 4: Circular<\/strong><\/div>\n<div>\n<p>This kind of replication\u00a0topology\u00a0has been generating many discussion around MySQL environments due to the set up with MySQL 5.0, version that not count with the terminator applied on MySQL 5.1. On broad terms, MySQL servers is set up on a circular way where every server is MASTER and SLAVE at the same time. The log-slave-updates replication system variables must be configured on all servers in order to ignore servers that just have executed that current updates.<\/p>\n<div id=\"attachment_273\" style=\"width: 275px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/wbianchi.files.wordpress.com\/2011\/07\/rpl_circular.jpg\"><img aria-describedby=\"caption-attachment-273\" decoding=\"async\" loading=\"lazy\" class=\"size-full wp-image-273\" title=\"rpl_circular\" src=\"http:\/\/wbianchi.files.wordpress.com\/2011\/07\/rpl_circular.jpg\" alt=\"rpl_circular\" width=\"265\" height=\"150\" \/><\/a><p id=\"caption-attachment-273\" class=\"wp-caption-text\">You can set up MySQL Servers 5.1 ++ in circular replication as A &lt;-&gt; B &lt;-&gt; C<\/p><\/div>\n<p>In circular replication, it was sometimes possible for an event to propagate such that it would be replicated on all servers. This could occur when the originating server was removed from the replication circle and so could no longer act as the terminator of its own events, as normally happens in circular replication.<\/p>\n<p>To prevent this from occurring, a new\u00a0<code><strong>IGNORE_SERVER_IDS<\/strong><\/code>\u00a0option is introduced for the\u00a0<code><strong>CHANGE MASTER TO<\/strong><\/code>\u00a0statement. This option takes a list of replication server IDs; events having a server ID which appears in this list are ignored and not applied.<\/p>\n<p>In conjunction with the introduction of\u00a0<code><strong>IGNORE_SERVER_IDS<\/strong><\/code>, SHOW SLAVE STATUS\u00a0has two new fields.\u00a0<code><strong>Replicate_Ignore_Server_Ids<\/strong><\/code>\u00a0displays information about ignored servers.\u00a0<code><strong>Master_Server_Id<\/strong><\/code>\u00a0displays the server_id\u00a0value from the master. (Bug #47037)<\/p>\n<p>See also Bug #25998, Bug #27808.<\/p>\n<\/div>\n<div>\n<h2>Additional Resources<\/h2>\n<h3>White Papers<\/h3>\n<ul>\n<li><a href=\"http:\/\/www.mysql.com\/why-mysql\/white-papers\/mysql-wp-replication.php\">MySQL Replication &#8211; Enhancing Scalability and Availability with MySQL 5.5<\/a><\/li>\n<li><a href=\"http:\/\/www.mysql.com\/why-mysql\/white-papers\/mysql-wp-windows-replication.php\">Getting Started with MySQL Replication on Windows<\/a><\/li>\n<\/ul>\n<h3>On Demand Webinars<\/h3>\n<ul>\n<li><a href=\"http:\/\/www.mysql.com\/news-and-events\/on-demand-webinars\/display-od-572.html\">MySQL Replication &#8211; Latest Enhancements in MySQL 5.5<\/a><\/li>\n<li><a href=\"http:\/\/www.mysql.com\/news-and-events\/on-demand-webinars\/display-od-505.html\">Getting Started with MySQL Replication on Windows<\/a><\/li>\n<li><a href=\"http:\/\/www.mysql.com\/news-and-events\/on-demand-webinars\/display-od-415.html\">Geographic Replication with MySQL Cluster<\/a><\/li>\n<\/ul>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>You should know that MySQL team has been doing a good job and that product naturally is being a great option when the chat is horizontal scale or Scale-Out too. I mentioned the last &#8220;too&#8221; cause MySQL has been doing a so\u00a0confident\u00a0job in vertical scaling on its availability with hands on InnoDB Storage Engine. But, [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[16],"tags":[],"_links":{"self":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/93"}],"collection":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=93"}],"version-history":[{"count":18,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/93\/revisions"}],"predecessor-version":[{"id":116,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/93\/revisions\/116"}],"wp:attachment":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=93"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=93"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=93"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}