{"id":73,"date":"2007-04-01T00:00:00","date_gmt":"2007-04-01T00:00:00","guid":{"rendered":"http:\/\/netexpertise\/en\/?p=73"},"modified":"2021-10-10T22:35:28","modified_gmt":"2021-10-10T20:35:28","slug":"mysql-replication","status":"publish","type":"post","link":"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-replication.html","title":{"rendered":"Mysql Replication"},"content":{"rendered":"<div style=\"float:left; margin-right:15px; margin-top:0px;\">\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script><br \/>\n<!-- 200x200, Netexpertise --><br \/>\n<ins class=\"adsbygoogle\" style=\"display:inline-block;width:200px;height:200px\" data-ad-client=\"ca-pub-6495750100906580\" data-ad-slot=\"1946825373\"><\/ins><br \/>\n<script><br \/>\n(adsbygoogle = window.adsbygoogle || []).push({});<br \/>\n<\/script><\/div>\n<p>Having a few databases running on Mysql with tables growing to several millions of rows, backups become pretty heavy. If the server fails, it can mean several hours lost, working on setting up a new server and reimporting the data from the backup. I&#8217;m not even talking about the potential financial loss due to the downtime. A better reliability can be achieved with Mysql either with a cluster or the replication feature. We will focus on replication in this article. However, this is a quick tutorial on how to setup replication; Check Mysql <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/replication.html\">documentation<\/a> to get more information about it.<br \/>\n&nbsp;<\/p>\n<div style=\"text-align:center\"><img decoding=\"async\" src=\"\/images\/Replication.png\" alt=\"Replication\"><\/div>\n<p>&nbsp;<br \/>\nThe master can replicate to many slaves, to which different web clients can send their requests. Writes always have to be sent to the master. If they were sent to a slave, they wouldn&#8217;t be replicated on other servers.<br \/>\n&nbsp;<br \/>\n<i>Note<\/i> Replication is asynchrone as the slave needs a small delay to be up to date. This is particularly suited to applications such as datawarehouses.<br \/>\n&nbsp;<\/p>\n<h3>Fail-over<\/h3>\n<p>Replication doesn&#8217;t provide auto fail-over as it requires a manual intervention. Another article has been written about implementing <a href=\"\/en\/mysql\/mysql-failover.html\">failover<\/a> with Heartbeat. It provides the ability to switch automatically to the slave server within seconds.<br \/>\n&nbsp;<\/p>\n<h3>Getting started<\/h3>\n<p>We&#8217;ll assume a standalone server is already running with the precious data, and redundancy needs to be implemented. Proceed to the following steps to get your server ready fr replication:<\/p>\n<ul>\n<li>Activate binary log on the master server. This is absolutely needed, the slave reads the binary logs to synchronize. <i>my.cnf<\/i> contains <i>log-bin=mysql-bin<\/i> in the <b>[mysqld]<\/b> section in my case<\/li>\n<li>Add <i>server-id=1<\/i> in <i>my.cnf<\/i>.<br \/>\nThis id should be unique for each server (The slave will be 2). Restart the server if those options were not activated.<\/li>\n<li>Create an account for the slave on the master that&#8217;s only allowed to replicate:\n<pre>GRANT REPLICATION SLAVE ON *.*\nTO 'slave'@'192.168.0.3' IDENTIFIED BY 'mypassword';<\/pre>\n<p>where &#8216;slave&#8217; is the username the slave server is going to connect with, 192.168.0.3 the IP address of the slave.<\/li>\n<li>Setup a new machine for the slave server and install Mysql. my.cnf should contain the following:<br \/>\n<i>[mysqld]<br \/>\nserver-id=2<\/i>. Don&#8217;t start the service yet!<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<h3>Transfer data accross to the slave<\/h3>\n<ul>\n<li>First block write operations on the master and record the last values from the binary log:\n<pre>mysql&gt; FLUSH TABLES WITH READ LOCK;\nmysql&gt; SHOW MASTER STATUS;<\/pre>\n<p>&nbsp;<br \/>\nYou should get a result similar to the following:<\/p>\n<pre style=\"face:courier; size:3\">+-------------------+----------+--------------+------------------+\n| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |\n+-------------------+----------+--------------+------------------+\n| my-db1-bin.000002 |      239 |              |                  |\n+-------------------+----------+--------------+------------------+<\/pre>\n<p>&nbsp;<br \/>\nMake sure to record those values, they will be needed after starting the slave.<\/li>\n<li>Shutdown Mysql process on the master and make a copy of the data directory with tar for example. After the tar was done, restart the process. This is the only time the master will be turned off.<\/li>\n<li>Copy the tar file accross to the slave into the temp folder, uncompress it and copy the files to the data directory. Don&#8217;t copy the log files.<br \/>\nMake sure the rights are correct (They should be identical to the original files). You now have the master data on the slave.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<h3>Activate Replication on the Slave<\/h3>\n<p>Start the slave with the following option:<\/p>\n<pre>--skip-slave-start<\/pre>\n<p>&nbsp;<br \/>\nYou can also log warnings in the error log to have a better idea of what is going on<\/p>\n<pre>--log-warnings<\/pre>\n<p>&nbsp;<br \/>\nNow that the server is started, connect on to it with Mysql client, and add the master&#8217;s details:<\/p>\n<pre>mysql&gt; CHANGE MASTER TO\nmysql&gt; MASTER_HOST='192.168.0.2',\nmysql&gt; MASTER_USER='slave',\nmysql&gt; MASTER_PASSWORD='mypassword',\nmysql&gt; MASTER_LOG_FILE='my-db1-bin.000002',\nmysql&gt; MASTER_LOG_POS=239;\n<\/pre>\n<p>&nbsp;<br \/>\nThe last 2 lines contain of course the values that were collected on the master.<\/p>\n<pre>mysql&gt; START SLAVE;\n<\/pre>\n<p>&nbsp;<br \/>\nReplication can start!<br \/>\nThe slave status can be checked via the following command:<\/p>\n<pre>mysql&gt; show slave status;\n<\/pre>\n<pre style=\"face:courier; size:3\">+----------------------------+-------------+-------------+-\/\/\/-+---------------+-------------------+\n| Slave_IO_State             | Master_Host | Master_User |     | Connect_Retry | Master_Log_File   |\n+----------------------------+-------------+-------------+-\/\/\/-+---------------+-------------------+\n| Waiting for master to s... | 192.168.0.2 | slave       |     |            60 | my-db1-bin.000006 |\n+----------------------------+-------------+-------------+-\/\/\/-+---------------+-------------------+\n\n+---------------------+----------------------+---------------+-----------------------+------------------+\n| Read_Master_Log_Pos | Relay_Log_File       | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running |\n+---------------------+----------------------+---------------+-----------------------+------------------+\n|           514457737 | bak-relay-bin.000007 |      26082745 | my-db1-bin.000006     | Yes              |\n+---------------------+----------------------+---------------+-----------------------+------------------+\n\n+-------------------+-\/\/\/-+---------------------+-----------------+-\/\/\/-+-----------------------+\n| Slave_SQL_Running |     | Exec_Master_Log_Pos | Relay_Log_Space |     | Seconds_Behind_Master |\n+-------------------+-\/\/\/-+---------------------+-----------------+-\/\/\/-+-----------------------+\n| Yes               |     |           514457737 |        26082745 |     |                     0 |\n+-------------------+-\/\/\/-+---------------------+-----------------+-\/\/\/-+-----------------------+\n<\/pre>\n<p>&nbsp;<\/p>\n<div id=\"imgright\">\n<a href=\"http:\/\/www.amazon.com\/gp\/product\/0672328127?ie=UTF8&amp;tag=netexpertise-20&amp;linkCode=as2&amp;camp=1789&amp;creative=9325&amp;creativeASIN=0672328127\"><img decoding=\"async\" border=\"0\" src=\"\/images\/Mysql_SL110_.jpg\"><\/a><img decoding=\"async\" loading=\"lazy\" src=\"http:\/\/www.assoc-amazon.com\/e\/ir?t=netexpertise-20&amp;l=as2&amp;o=1&amp;a=0672328127\" width=\"1\" height=\"1\" border=\"0\" alt=\"\" style=\"border:none !important; margin:0px !important;\"><\/div>\n<p>If replication stops working for some reasons, the last error will be displayed here. This can also find them in mysql error log file.<br \/>\nDisable the <i>&#8211;skip-slave-start<\/i> option from the startup script so replication is activated after a server reboot.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Having a few databases running on Mysql with tables growing to several millions of rows, backups become pretty heavy. If the server fails, it can mean several hours lost, working on setting up a new server and reimporting the data from the backup. I&#8217;m not even talking about the potential financial loss due to the [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[4],"tags":[51,5,385],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.8.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Netexpertise - Mysql Replication<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-replication.html\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Netexpertise - Mysql Replication\" \/>\n<meta property=\"og:description\" content=\"Having a few databases running on Mysql with tables growing to several millions of rows, backups become pretty heavy. If the server fails, it can mean several hours lost, working on setting up a new server and reimporting the data from the backup. I&#8217;m not even talking about the potential financial loss due to the [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-replication.html\" \/>\n<meta property=\"og:site_name\" content=\"Netexpertise\" \/>\n<meta property=\"article:published_time\" content=\"2007-04-01T00:00:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-10-10T20:35:28+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.assoc-amazon.com\/e\/ir?t=netexpertise-20&amp;l=as2&amp;o=1&amp;a=0672328127\" \/>\n<meta name=\"author\" content=\"dave\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@netexpertise\" \/>\n<meta name=\"twitter:site\" content=\"@netexpertise\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-replication.html\",\"url\":\"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-replication.html\",\"name\":\"Netexpertise - Mysql Replication\",\"isPartOf\":{\"@id\":\"http:\/\/www.netexpertise.eu\/en\/#website\"},\"datePublished\":\"2007-04-01T00:00:00+00:00\",\"dateModified\":\"2021-10-10T20:35:28+00:00\",\"author\":{\"@id\":\"http:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/cb4cd666549d22e9070ec1cfc1a496fa\"},\"breadcrumb\":{\"@id\":\"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-replication.html#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-replication.html\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-replication.html#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\/\/www.netexpertise.eu\/en\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Mysql Replication\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/www.netexpertise.eu\/en\/#website\",\"url\":\"http:\/\/www.netexpertise.eu\/en\/\",\"name\":\"Netexpertise\",\"description\":\"Systems \/ Networks \/ DevOps\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/www.netexpertise.eu\/en\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"http:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/cb4cd666549d22e9070ec1cfc1a496fa\",\"name\":\"dave\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/image\/\",\"url\":\"http:\/\/1.gravatar.com\/avatar\/1129916e1f4955bd632f27f836f64e55?s=96&d=mm&r=g\",\"contentUrl\":\"http:\/\/1.gravatar.com\/avatar\/1129916e1f4955bd632f27f836f64e55?s=96&d=mm&r=g\",\"caption\":\"dave\"},\"sameAs\":[\"http:\/\/www.netexpertise.eu\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Netexpertise - Mysql Replication","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-replication.html","og_locale":"en_US","og_type":"article","og_title":"Netexpertise - Mysql Replication","og_description":"Having a few databases running on Mysql with tables growing to several millions of rows, backups become pretty heavy. If the server fails, it can mean several hours lost, working on setting up a new server and reimporting the data from the backup. I&#8217;m not even talking about the potential financial loss due to the [&hellip;]","og_url":"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-replication.html","og_site_name":"Netexpertise","article_published_time":"2007-04-01T00:00:00+00:00","article_modified_time":"2021-10-10T20:35:28+00:00","og_image":[{"url":"http:\/\/www.assoc-amazon.com\/e\/ir?t=netexpertise-20&amp;l=as2&amp;o=1&amp;a=0672328127"}],"author":"dave","twitter_card":"summary_large_image","twitter_creator":"@netexpertise","twitter_site":"@netexpertise","schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-replication.html","url":"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-replication.html","name":"Netexpertise - Mysql Replication","isPartOf":{"@id":"http:\/\/www.netexpertise.eu\/en\/#website"},"datePublished":"2007-04-01T00:00:00+00:00","dateModified":"2021-10-10T20:35:28+00:00","author":{"@id":"http:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/cb4cd666549d22e9070ec1cfc1a496fa"},"breadcrumb":{"@id":"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-replication.html#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-replication.html"]}]},{"@type":"BreadcrumbList","@id":"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-replication.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/www.netexpertise.eu\/en"},{"@type":"ListItem","position":2,"name":"Mysql Replication"}]},{"@type":"WebSite","@id":"http:\/\/www.netexpertise.eu\/en\/#website","url":"http:\/\/www.netexpertise.eu\/en\/","name":"Netexpertise","description":"Systems \/ Networks \/ DevOps","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/www.netexpertise.eu\/en\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"http:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/cb4cd666549d22e9070ec1cfc1a496fa","name":"dave","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/image\/","url":"http:\/\/1.gravatar.com\/avatar\/1129916e1f4955bd632f27f836f64e55?s=96&d=mm&r=g","contentUrl":"http:\/\/1.gravatar.com\/avatar\/1129916e1f4955bd632f27f836f64e55?s=96&d=mm&r=g","caption":"dave"},"sameAs":["http:\/\/www.netexpertise.eu"]}]}},"_links":{"self":[{"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/posts\/73"}],"collection":[{"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/comments?post=73"}],"version-history":[{"count":0,"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/posts\/73\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/media?parent=73"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/categories?post=73"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/tags?post=73"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}