{"id":65,"date":"2007-04-18T00:00:00","date_gmt":"2007-04-18T00:00:00","guid":{"rendered":"http:\/\/netexpertise\/en\/?p=65"},"modified":"2021-10-10T22:34:50","modified_gmt":"2021-10-10T20:34:50","slug":"mysql-failover","status":"publish","type":"post","link":"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-failover.html","title":{"rendered":"Mysql Failover"},"content":{"rendered":"<div style=\"float: left; margin-right: 15px; margin-top: 0px;\"><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>Important note:<br \/>\n<a href=\"http:\/\/www.linux-ha.org\/wiki\/Site_news\">Heartbeat<\/a> is now obsolete and has moved to a new stack available on <a href=\"https:\/\/clusterlabs.org\">Clusterlabs<\/a>. For simple high availability project using a virtual IP, try out keepalived that does monitoring and failover with just a simple configuration file.<\/p>\n<p>When a Mysql server contains critical data, it&#8217;s always a good idea to rely on more than one server.<br \/>\nImplementing a cluster would be ideal in this situation and could offer a high availability setup.<br \/>\nIt gives the option to replace a node if it fails with no disruption of service or even data loss.<br \/>\nThe fact is it becomes much less interesting financially considering a minimum of 4 machines is required, and a lot of time as well.<br \/>\nI&#8217;d rather have a replication setup but a manual fix needs to be done to switch the traffic from the master to the slave. This article gives the ability to switch the traffic <b>automatically<\/b>.<\/p>\n<h3>Initial Setup<\/h3>\n<p>Let&#8217;s assume we have a standalone server server running on 192.168.0.2<br \/>\nAfter modification, the live server will take IP 192.168.0.4<br \/>\nA slave will be added with IP 192.168.0.5<br \/>\n192.168.0.2 becomes the virtual IP of the &#8220;cluster&#8221;. Changes are transparent to the programs connecting to the database. They will still use IP 192.168.0.2 as originally written.<\/p>\n<div style=\"text-align: center;\"><img decoding=\"async\" src=\"\/images\/Failover.png\" alt=\"Failover\"><\/div>\n<ul>\n<li>Mysql replication needs to be implemented with the following settings:<br \/>\nMaster 192.168.0.2<br \/>\nSlave 192.168.0.5<br \/>\nThe master keeps the original IP &#8211; for now &#8211; so all applications still connect to the same machine.<br \/>\nCheck this other tutorial about replication <a href=\"\/en\/mysql\/mysql-replication.html\">here<\/a>.<\/li>\n<li>Firewalls updates<br \/>\nApply the master&#8217;s rules on the slave ie 192.168.0.5 identical to 192.168.0.2 so each single program can connect to the slave if requested.<br \/>\nThis method could be used to implement load-balancing in a quick and easy way.<br \/>\nAllow all traffic between master and slave. This is important to get Heartbeat and Mon to check services, as well as the Mysql replication protocol.<\/li>\n<li>Disable Selinux on the 2 machines<br \/>\nIt creates problems with heartbeat<br \/>\nIn \/etc\/selinux\/config<br \/>\nset &#8220;SELINUX=disabled&#8221;<br \/>\nChanges need unfortunately a server reboot to be applied<\/li>\n<\/ul>\n<h3>Heartbeat<\/h3>\n<p>Download and install heartbeat from the Linux high availability project website at <a href=\"http:\/\/www.linux-ha.org\/Heartbeat\">http:\/\/www.linux-ha.org\/Heartbeat<\/a>.<br \/>\nRPMs are available for Fedora Core.<br \/>\nsudo yum install heartbeat<\/p>\n<p>Configuration files are located in <i>\/etc\/ha.d\/<\/i>. You need to create those 3 files on the two servers:<\/p>\n<pre>cat &gt; \/etc\/ha.d\/authkeys\nauth 1\n1 crc\n<\/pre>\n<p>Set appropriate rights to the file<\/p>\n<pre>chmod 600 \/etc\/ha.d\/authkeys<\/pre>\n<pre>cat &gt; \/etc\/ha.d\/haresources\nmaster 192.168.0.2 mysqld mon\n<\/pre>\n<p><i>haresources<\/i> must contain the virtual IP.<\/p>\n<pre>cat &gt; \/etc\/ha.d\/ha.cf\nlogfile \/var\/log\/ha-log\nkeepalive 2\n<em style=\"color: #ff0000;\"># the time to wait before declaring a node to be dead<\/em>\ndeadtime 10\n<em style=\"color: #ff0000;\"># this is the time before heartbeat will start the resources the first time it starts ie mon,mysql...<\/em>\ninitdead 20\nbcast eth0\nnode master.mydomain.com\nnode slave.mydomain.com\n<em style=\"color: #ff0000;\"># Set this to \"on\" only if you have a multi-master setup<\/em>\nauto_failback off\n<em style=\"color: #ff0000;\"># will ping the default gateway to check the network connectivity<\/em>\nping 192.168.0.1\nrespawn hacluster \/usr\/lib64\/heartbeat\/ipfail\n<\/pre>\n<p><i>Note<\/i> Nodes should be literally taken from `uname -n`. This is fairly important or heartbeat won&#8217;t work.<\/p>\n<p><i>Note<\/i> \/etc\/hosts file on BOTH machines should contain entries for the 2 hosts<br \/>\n192.168.0.4 master.mydomain.com<br \/>\n192.168.0.5 slave.mydomain.com<\/p>\n<p><i>Note<\/i> \/usr\/lib64\/heartbeat\/ipfail is for a 64 bit-architecture. Remove &#8217;64&#8217; if you&#8217;re on a 32.<\/p>\n<h3>Mon<\/h3>\n<p>Download MON<\/p>\n<pre>cd \/usr\/local\/src\nwget ftp:\/\/ftp.kernel.org\/pub\/software\/admin\/mon\/mon-0.99.2.tar.gz\n<\/pre>\n<p>Check if a new version is available on <a href=\"http:\/\/www.kernel.org\/software\/mon\/\">http:\/\/www.kernel.org\/software\/mon\/<\/a>. 0.99.2 was the most recent stable version when I last checked.<br \/>\nUncompress the tarball and move it to \/usr\/local or your usual favourite location<\/p>\n<pre>tar xvfz mon-0.99.2.tar.gz\nmv mon-0.99.2 \/usr\/local\/mon\n<\/pre>\n<p>Copy the configuration directory to \/etc<\/p>\n<pre>mv \/usr\/local\/mon\/etc \/etc\/mon\n<\/pre>\n<p>Install Perl and the modules that Mon requires<\/p>\n<pre>- DBI\n- DBD::mysql\n- Time::Period\n- Time::HiRes\n- Convert::BER\n- Mon::Client\n<\/pre>\n<p>Modules can be found on the CPAN website at <a href=\"http:\/\/www.perl.com\/CPAN\/modules\/index.html\">http:\/\/www.perl.com\/CPAN\/modules\/index.html<\/a>.<br \/>\nDownload and install the appropriate packages following instructions given in INSTALL. Usually:<\/p>\n<pre>gunzip  &lt;module&gt;.tar.gz\ntar -xvf  &lt;module&gt;.tar\ncd &lt;module-dir&gt;\nperl Makefile.pl\nmake\nmake test\nmake install\n<\/pre>\n<p>or via<\/p>\n<pre>perl -MCPAN -e shell<\/pre>\n<p>and run<\/p>\n<pre>install &lt;module&gt;<\/pre>\n<p><i>Note<\/i> Make sure Mysql libraries are in the path before installing DBD::mysql.<\/p>\n<p>Create the Mon configuration file<\/p>\n<pre>cat &gt; mon.cf\nalertdir   = \/usr\/local\/mon\/alert.d\nmondir     = \/usr\/local\/mon\/mon.d\nstatedir   = \/usr\/local\/mon\/state.d\nmaxprocs    = 20\nhistlength = 100\nrandstart = 60s\n<em style=\"color: #ff0000;\"># Virtual IP<\/em>\nhostgroup mysql_servers 192.168.0.2\nwatch mysql_servers\n  mysql\n    <em style=\"color: #ff0000;\">## Mon will do a test on Mysql port every 60 seconds<\/em>\n    interval 1m\n    monitor mysql.monitor\n    period wd {Mon-Sun}\n      <em style=\"color: #ff0000;\">alert bring-ha-down.alert<\/em>\n      alert mail.alert -S \"Host1 MYSQL is down\" admin@example.com\n      upalert mail.alert -S \"Host1 MYSQL server is back up\" admin@example.com\n      alertevery 600s\n      ## Sends an alert after 3 failures\n      ## ie 2mn\n      alertafter 3\n<\/pre>\n<p>Create a script to bring heartbeat down if Mysql service becomes unavailable<\/p>\n<pre>cat &gt; \/usr\/local\/mon\/alert.d\/bring-ha-down.alert\n\/etc\/rc.d\/init.d\/heartbeat stop\n<\/pre>\n<p>Change the script&#8217;s name to activate mysql mode by default<\/p>\n<pre>cd mon.d\nmv msql-mysql.monitor mysql.monitor\n<\/pre>\n<p>Create a user authorized to access the test database<\/p>\n<pre>mysql&gt; GRANT ALL PRIVILEGES ON test.* TO alive@'%' IDENTIFIED BY 'mypassword';<\/pre>\n<p>Edit mysql.server accordingly and add a line to connect to the &#8216;test&#8217; database<\/p>\n<pre>$options{database} ||= \"test\";<\/pre>\n<p>This option was missing in my default configuration file.<\/p>\n<p>Create a startup script for mon. Here&#8217;s a sample:<\/p>\n<pre>cat &gt; \/etc\/rc.d\/init.d\/mon\n#!\/bin\/bash\nMON_HOME=\/usr\/local\/mon\ncase \"$1\" in\n    start)\n        if [ -f $MON_HOME\/mon.pid ]; then\n                echo \"mon already started\"\n                exit\n        fi\n        echo \"Starting Mon\"\n    $MON_HOME\/mon -c $MON_HOME\/mon.cf -L $MON_HOME -P $MON_HOME\/mon.pid &amp;\n        ;;\n    stop)\n    if [ -f $MON_HOME\/mon.pid ]; then\n    echo \"Stopping Mon\"\n        kill -9 `cat $MON_HOME\/mon.pid`\n        rm  -f $MON_HOME\/mon.pid\n    else\n        echo \"no server pid, server doesn't seem to run\"\n    fi\n    ;;\n    *)\n    echo \"Usage: $0 {start|stop|status|reload|restart}\"\n    exit 1\nesac\nexit 0\n<\/pre>\n<p>Make it executable<br \/>\n<i>chmod 755 \/etc\/rc.d\/init.d\/mon<\/i><\/p>\n<h3>Run Applications<\/h3>\n<p>Make sure Mysql is running on the 2 boxes and replication is up-to-date before doing anything else.<br \/>\nChange the Master&#8217;s IP to 192.168.0.4 in the config files and restart the 2 servers (master first).<br \/>\nFire up Mysql and Heartbeat on the master<br \/>\n\/etc\/rc.d\/init\/mysqld start<br \/>\n\/etc\/rc.d\/init.d\/heartbeat start<\/p>\n<p># You can check hearbeat logs in \/var\/log\/ha-log<br \/>\nThe virtual IP will be assigned to eth0:0 after a few seconds<br \/>\nThis can be checked with `ifconfig`<\/p>\n<pre>eth0:0    Link encap:Ethernet  HWaddr 00:13:72:5D:1D:1F\n          inet addr:192.168.0.2  Bcast:192.168.0.255  Mask:255.255.255.0\n          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1\n          Base address:0xdcc0 Memory:fe6e0000-fe700000\n<\/pre>\n<p>If you experience problems, check messages for Mon in \/var\/log\/messages as well<br \/>\nDo the same on the slave<\/p>\n<p>You can then try to turn the machine off, disconnect the cable or shutdown Mysql;<br \/>\nThe virtual IP will migrate to the second server after 2mn (this time can be reduced in mon.cf 1mn and 3 checks.<br \/>\nIt is better to give some time in case Mysql becomes unresponsive for a short period of time while there are traffic spikes)<\/p>\n<div id=\"imgright\"><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\" src=\"\/images\/Mysql_SL110_.jpg\" border=\"0\"><\/a><img decoding=\"async\" loading=\"lazy\" style=\"border: none !important; margin: 0px !important;\" src=\"http:\/\/www.assoc-amazon.com\/e\/ir?t=netexpertise-20&amp;l=as2&amp;o=1&amp;a=0672328127\" alt=\"\" width=\"1\" height=\"1\" border=\"0\"><\/div>\n<p>Some basic load-balancing is now possible redirecting all read commands to the slave (192.168.0.5), leaving the writes to the master.<br \/>\nYou need to monitor that the slave is always live in this case though because the failover won&#8217;t apply to the reads.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Important note: Heartbeat is now obsolete and has moved to a new stack available on Clusterlabs. For simple high availability project using a virtual IP, try out keepalived that does monitoring and failover with just a simple configuration file. When a Mysql server contains critical data, it&#8217;s always a good idea to rely on more [&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 Failover<\/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-failover.html\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Netexpertise - Mysql Failover\" \/>\n<meta property=\"og:description\" content=\"Important note: Heartbeat is now obsolete and has moved to a new stack available on Clusterlabs. For simple high availability project using a virtual IP, try out keepalived that does monitoring and failover with just a simple configuration file. When a Mysql server contains critical data, it&#8217;s always a good idea to rely on more [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-failover.html\" \/>\n<meta property=\"og:site_name\" content=\"Netexpertise\" \/>\n<meta property=\"article:published_time\" content=\"2007-04-18T00:00:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-10-10T20:34:50+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-failover.html\",\"url\":\"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-failover.html\",\"name\":\"Netexpertise - Mysql Failover\",\"isPartOf\":{\"@id\":\"http:\/\/www.netexpertise.eu\/en\/#website\"},\"datePublished\":\"2007-04-18T00:00:00+00:00\",\"dateModified\":\"2021-10-10T20:34:50+00:00\",\"author\":{\"@id\":\"http:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/cb4cd666549d22e9070ec1cfc1a496fa\"},\"breadcrumb\":{\"@id\":\"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-failover.html#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-failover.html\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-failover.html#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\/\/www.netexpertise.eu\/en\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Mysql Failover\"}]},{\"@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 Failover","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-failover.html","og_locale":"en_US","og_type":"article","og_title":"Netexpertise - Mysql Failover","og_description":"Important note: Heartbeat is now obsolete and has moved to a new stack available on Clusterlabs. For simple high availability project using a virtual IP, try out keepalived that does monitoring and failover with just a simple configuration file. When a Mysql server contains critical data, it&#8217;s always a good idea to rely on more [&hellip;]","og_url":"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-failover.html","og_site_name":"Netexpertise","article_published_time":"2007-04-18T00:00:00+00:00","article_modified_time":"2021-10-10T20:34:50+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-failover.html","url":"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-failover.html","name":"Netexpertise - Mysql Failover","isPartOf":{"@id":"http:\/\/www.netexpertise.eu\/en\/#website"},"datePublished":"2007-04-18T00:00:00+00:00","dateModified":"2021-10-10T20:34:50+00:00","author":{"@id":"http:\/\/www.netexpertise.eu\/en\/#\/schema\/person\/cb4cd666549d22e9070ec1cfc1a496fa"},"breadcrumb":{"@id":"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-failover.html#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-failover.html"]}]},{"@type":"BreadcrumbList","@id":"http:\/\/www.netexpertise.eu\/en\/database\/mysql\/mysql-failover.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/www.netexpertise.eu\/en"},{"@type":"ListItem","position":2,"name":"Mysql Failover"}]},{"@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\/65"}],"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=65"}],"version-history":[{"count":0,"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/posts\/65\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/media?parent=65"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/categories?post=65"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.netexpertise.eu\/en\/wp-json\/wp\/v2\/tags?post=65"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}