{"id":4502,"date":"2015-12-20T02:49:13","date_gmt":"2015-12-20T07:49:13","guid":{"rendered":"http:\/\/cdnhost.net\/?p=4502"},"modified":"2022-09-18T01:47:08","modified_gmt":"2022-09-18T06:47:08","slug":"master-slave-mysql-replication-tutorial-for-newbie","status":"publish","type":"post","link":"https:\/\/cdnhost.net\/content\/?p=4502","title":{"rendered":"MASTER \u2013 SLAVE MYSQL REPLICATION TUTORIAL FOR NEWBIE"},"content":{"rendered":"<p>In this guide I will show you practical steps (with pictures) how to setup MySQL Replication in Master \u2013 Slave mode on either CentOS or Ubuntu server. I believe MySQL replication technique is not expert-only stuff but even newbies can learn and use this technique. In case if you didn\u2019t know yet, let\u2019s start with a short introduction about what is it.<\/p>\n<h2>MEET MYSQL REPLICATION<\/h2>\n<p>MySQL Replication is a method to create synced backup of your MySQL database. You can call it hot backup or a backup that\u2019s always up-to-date and ready-to-use in case the main one crashes or down. Master \u2013 Slave replication is just one of available mode that is commonly used in many production system. So this tutorial is the simplest one (<em>one master will send information to a single slave<\/em>) which newbies can follow and use it at their production servers. Better take backup than not!<\/p>\n<p><strong>What\u2019s the main purpose of MySQL Replication?<\/strong> It is to create a redundant MySQL server setup which is also very useful in terms of Data Security, Fail-over Solution, Database Backup from Slave, Analytics (<em>to analyze it without using the main database<\/em>), etc.<\/p>\n<h3>INGREDIENTS:<\/h3>\n<p>What you\u2019ll need:<\/p>\n<ul>\n<li>Two VPS servers: this can be in single node, same providers, or at separate \/ different nodes (different providers \/ locations). Need recommendation? See my <a href=\"http:\/\/www.servermom.org\/recommended-vps\">list of recommended providers<\/a> or the <a href=\"http:\/\/www.servermom.org\/low-end-cloud-server-providers\/1577\/\">20 low end cloud servers<\/a>.<\/li>\n<li>Either CentOS or Ubuntu running on top of it<\/li>\n<li>Note down Master and Slave IPs.<\/li>\n<li>About 30 minutes of your time<\/li>\n<li>A cup of coffee or tea<\/li>\n<\/ul>\n<p>What I use in this tutorial:<\/p>\n<ul>\n<li>A 512MB RAM cloud server from <a href=\"http:\/\/www.servermom.org\/unboxing-atlantic-net-cloud-server\/2274\/\">Atlantic.net<\/a> as Master server.<\/li>\n<li>A 512MB RAM droplet from <a href=\"http:\/\/www.servermom.org\/node\/digitalocean\">Digital Ocean<\/a> as Slave server.<\/li>\n<li>Both running CentOS 6.x 32-bit<\/li>\n<li>MySQL version I use is v5.1.73 at both Master and Slave.<\/li>\n<li>A cup of Torabika coffee.<\/li>\n<\/ul>\n<h3>PREPARATION<\/h3>\n<p>First thing first, you have to install MySQL server on both Master and Slave server. In case if you haven\u2019t installed it yet, you can follow my previous articles:<\/p>\n<ul>\n<li><strong><a href=\"http:\/\/www.servermom.org\/basic-guide-install-mysql-server-on-centos\/445\/\">How to install MySQL server on CentOS<\/a><\/strong> (<a href=\"http:\/\/www.servermom.org\/complete-newbie-guide-to-build-centos-server-to-host-websites\/\">Index<\/a>)<\/li>\n<li><strong><a href=\"http:\/\/www.servermom.org\/basic-guide-install-mysql-server-on-ubuntu\/117\/\">How to install MySQL server on Ubuntu<\/a><\/strong> (<a href=\"http:\/\/www.servermom.org\/build-ubuntu-server-a-complete-guide\/\">Index<\/a>)<\/li>\n<\/ul>\n<p>Do not forget to note down the password of MySQL root user.<\/p>\n<p>FYI, you can also have MySQL server installed via Control Panel software like Vesta CP at Master server while install ONLY MySQL server manually on Slave.<\/p>\n<p>Before we step up, I use these imaginary IPs which you can replace with your own IPs:<\/p>\n<p><strong>Master server:<\/strong> 111.111.111.111<\/p>\n<p><strong>Slave server:<\/strong> 222.222.222.222<\/p>\n<h2>SETUP MYSQL MASTER NODE<\/h2>\n<p>Do these steps on Master Node:<\/p>\n<p><strong>Step 1<\/strong> \u2013 Edit MySQL configuration file using your favorite text editor like vi or Nano. Command below uses Nano editor:<\/p>\n<div>\n<div id=\"highlighter_633466\" class=\"syntaxhighlighter  shell\">\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td class=\"gutter\">\n<div class=\"line number1 index0 alt2\">1<\/div>\n<div class=\"line number2 index1 alt1\">2<\/div>\n<div class=\"line number3 index2 alt2\">3<\/div>\n<div class=\"line number4 index3 alt1\">4<\/div>\n<div class=\"line number5 index4 alt2\">5<\/div>\n<\/td>\n<td class=\"code\">\n<div class=\"container\">\n<div class=\"line number1 index0 alt2\"><code class=\"shell comments\">## On Ubuntu<\/code><\/div>\n<div class=\"line number2 index1 alt1\"><code class=\"shell plain\">nano <\/code><code class=\"shell plain\">\/etc\/mysql\/my<\/code><code class=\"shell plain\">.cnf<\/code><\/div>\n<div class=\"line number3 index2 alt2\"><\/div>\n<div class=\"line number4 index3 alt1\"><code class=\"shell comments\">## On CentOS<\/code><\/div>\n<div class=\"line number5 index4 alt2\"><code class=\"shell plain\">nano <\/code><code class=\"shell plain\">\/etc\/my<\/code><code class=\"shell plain\">.cnf<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p>You may firstly need to edit IP binding configuration (especially in Ubuntu). Change 127.0.0.1 at bind-address with the IP of your Master server<\/p>\n<div>\n<div id=\"highlighter_434816\" class=\"syntaxhighlighter  shell\">\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td class=\"gutter\">\n<div class=\"line number1 index0 alt2\">1<\/div>\n<div class=\"line number2 index1 alt1\">2<\/div>\n<div class=\"line number3 index2 alt2\">3<\/div>\n<div class=\"line number4 index3 alt1\">4<\/div>\n<\/td>\n<td class=\"code\">\n<div class=\"container\">\n<div class=\"line number1 index0 alt2\"><code class=\"shell plain\">bind-address\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = 127.0.0.1<\/code><\/div>\n<div class=\"line number2 index1 alt1\"><\/div>\n<div class=\"line number3 index2 alt2\"><code class=\"shell comments\">## change 127.0.0.1 with IP address of your Master node<\/code><\/div>\n<div class=\"line number4 index3 alt1\"><code class=\"shell plain\">bind-address\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = 111.111.111.111<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p><strong>Step 2<\/strong> \u2013 Now put these lines right after the [mysqld] section:<\/p>\n<div>\n<div id=\"highlighter_878875\" class=\"syntaxhighlighter  shell\">\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td class=\"gutter\">\n<div class=\"line number1 index0 alt2\">1<\/div>\n<div class=\"line number2 index1 alt1\">2<\/div>\n<div class=\"line number3 index2 alt2\">3<\/div>\n<div class=\"line number4 index3 alt1\">4<\/div>\n<div class=\"line number5 index4 alt2\">5<\/div>\n<div class=\"line number6 index5 alt1\">6<\/div>\n<div class=\"line number7 index6 alt2\">7<\/div>\n<div class=\"line number8 index7 alt1\">8<\/div>\n<\/td>\n<td class=\"code\">\n<div class=\"container\">\n<div class=\"line number1 index0 alt2\"><code class=\"shell plain\">server-<\/code><code class=\"shell functions\">id<\/code><code class=\"shell plain\">=1<\/code><\/div>\n<div class=\"line number2 index1 alt1\"><code class=\"shell plain\">binlog-<\/code><code class=\"shell keyword\">do<\/code><code class=\"shell plain\">-db=databasename<\/code><\/div>\n<div class=\"line number3 index2 alt2\"><code class=\"shell plain\">relay-log=<\/code><code class=\"shell plain\">\/var\/lib\/mysql\/mysql-relay-bin<\/code><\/div>\n<div class=\"line number4 index3 alt1\"><code class=\"shell plain\">relay-log-index=<\/code><code class=\"shell plain\">\/var\/lib\/mysql\/mysql-relay-bin<\/code><code class=\"shell plain\">.index<\/code><\/div>\n<div class=\"line number5 index4 alt2\"><code class=\"shell plain\">log-error=<\/code><code class=\"shell plain\">\/var\/lib\/mysql\/mysql<\/code><code class=\"shell plain\">.err<\/code><\/div>\n<div class=\"line number6 index5 alt1\"><code class=\"shell plain\">master-info-<\/code><code class=\"shell functions\">file<\/code><code class=\"shell plain\">=<\/code><code class=\"shell plain\">\/var\/lib\/mysql\/mysql-master<\/code><code class=\"shell plain\">.info<\/code><\/div>\n<div class=\"line number7 index6 alt2\"><code class=\"shell plain\">relay-log-info-<\/code><code class=\"shell functions\">file<\/code><code class=\"shell plain\">=<\/code><code class=\"shell plain\">\/var\/lib\/mysql\/mysql-relay-log<\/code><code class=\"shell plain\">.info<\/code><\/div>\n<div class=\"line number8 index7 alt1\"><code class=\"shell plain\">log-bin=<\/code><code class=\"shell plain\">\/var\/lib\/mysql\/mysql-bin<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p>Change <strong>databasename<\/strong> with the database name you want to replicate \/ copy it to the Slave server.\u00a0You can include more than one database by repeating this line for all of the databases you will need. I use <strong>servermomdb<\/strong> as my database name for this example. Screenshot pic:<\/p>\n<p><a href=\"http:\/\/www.servermom.org\/master-slave-mysql-replication-tutorial\/2356\/2015-06-15_211112\/\" rel=\"attachment wp-att-2368\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-2368\" src=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_211112.png\" srcset=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_211112-300x130.png 300w, http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_211112.png 615w\" alt=\"2015-06-15_211112\" width=\"615\" height=\"267\" \/><\/a><\/p>\n<p>Once done, save changes and exit the editor. In Nano it is Control+O then Control+X.<\/p>\n<p><strong>Step 3<\/strong> \u2013 You can now restart mysql service:<\/p>\n<div>\n<div id=\"highlighter_973963\" class=\"syntaxhighlighter  shell\">\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td class=\"gutter\">\n<div class=\"line number1 index0 alt2\">1<\/div>\n<\/td>\n<td class=\"code\">\n<div class=\"container\">\n<div class=\"line number1 index0 alt2\"><code class=\"shell plain\">service mysql restart<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p><a href=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-14_233853.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-2360\" src=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-14_233853.png\" srcset=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-14_233853-300x42.png 300w, http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-14_233853.png 541w\" alt=\"2015-06-14_233853\" width=\"541\" height=\"75\" \/><\/a><\/p>\n<p><strong>Step 4<\/strong> \u2013 Login to MySQL shell as root using password you defined :<\/p>\n<div>\n<div id=\"highlighter_618827\" class=\"syntaxhighlighter  shell\">\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td class=\"gutter\">\n<div class=\"line number1 index0 alt2\">1<\/div>\n<\/td>\n<td class=\"code\">\n<div class=\"container\">\n<div class=\"line number1 index0 alt2\"><code class=\"shell plain\">mysql -u root -p<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p><a href=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-14_234240.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-2361\" src=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-14_234240-620x251.png\" srcset=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-14_234240-300x121.png 300w, http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-14_234240.png 620w\" alt=\"2015-06-14_234240\" width=\"620\" height=\"251\" \/><\/a><\/p>\n<p><strong>Step 5<\/strong> \u2013 Here\u2019s a little bit tricky part. Pay attention closely to each command is issued inside MySQL shell. What you are going to do is: to create the slave user and grant privileges for replication task, flushing privileges and lock the database.<\/p>\n<div>\n<div id=\"highlighter_595982\" class=\"syntaxhighlighter  shell\">\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td class=\"gutter\">\n<div class=\"line number1 index0 alt2\">1<\/div>\n<div class=\"line number2 index1 alt1\">2<\/div>\n<div class=\"line number3 index2 alt2\">3<\/div>\n<\/td>\n<td class=\"code\">\n<div class=\"container\">\n<div class=\"line number1 index0 alt2\"><code class=\"shell plain\">GRANT REPLICATION SLAVE ON *.* TO <\/code><code class=\"shell string\">'replicauser'<\/code><code class=\"shell plain\">@<\/code><code class=\"shell string\">'222.222.222.222'<\/code> <code class=\"shell plain\">IDENTIFIED BY <\/code><code class=\"shell string\">'replicapass'<\/code><code class=\"shell plain\">;<\/code><\/div>\n<div class=\"line number2 index1 alt1\"><code class=\"shell plain\">FLUSH PRIVILEGES;<\/code><\/div>\n<div class=\"line number3 index2 alt2\"><code class=\"shell plain\">FLUSH TABLES WITH READ LOCK;<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p>Do not forget to replace <strong>replicauser<\/strong>, <strong>222.222.222.222<\/strong> and <strong>replicapass<\/strong> accordingly.<\/p>\n<ul>\n<li>replicauser = is new MySQL user for replicating purpose.<\/li>\n<li>222.222.222.222 = is Slave IP<\/li>\n<li>replicapass = is password for the new MySQL user.<\/li>\n<\/ul>\n<p>Here\u2019s an illustration of mine. I use servermom as user and a1b2c3d4 as password:<\/p>\n<p><a href=\"http:\/\/www.servermom.org\/master-slave-mysql-replication-tutorial\/2356\/2015-06-15_205601\/\" rel=\"attachment wp-att-2365\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-2365\" src=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_205601-620x139.png\" srcset=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_205601-300x67.png 300w, http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_205601-620x139.png 620w, http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_205601.png 780w\" alt=\"2015-06-15_205601\" width=\"620\" height=\"139\" \/><\/a><\/p>\n<p><strong>Step 6<\/strong> \u2013 You\u2019ll need to find out the mysql-bin name and its Position then note it down (I simply open up Notepad and note it there). Still in MySQL shell, use command below:<\/p>\n<div>\n<div id=\"highlighter_649676\" class=\"syntaxhighlighter  shell\">\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td class=\"gutter\">\n<div class=\"line number1 index0 alt2\">1<\/div>\n<\/td>\n<td class=\"code\">\n<div class=\"container\">\n<div class=\"line number1 index0 alt2\"><code class=\"shell plain\">SHOW MASTER STATUS;<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p>it should give you output similar to this:<\/p>\n<p><a href=\"http:\/\/www.servermom.org\/master-slave-mysql-replication-tutorial\/2356\/2015-06-15_210705\/\" rel=\"attachment wp-att-2367\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-2367\" src=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_210705.png\" srcset=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_210705-300x85.png 300w, http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_210705.png 530w\" alt=\"2015-06-15_210705\" width=\"530\" height=\"150\" \/><\/a><\/p>\n<p>In my case I have to note the <strong>mysql-bin.000002<\/strong> and <strong>106<\/strong>, yours will be different.<\/p>\n<p><strong>Step 7<\/strong> \u2013 You can now exit MySQL shell:<\/p>\n<div>\n<div id=\"highlighter_776478\" class=\"syntaxhighlighter  shell\">\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td class=\"gutter\">\n<div class=\"line number1 index0 alt2\">1<\/div>\n<\/td>\n<td class=\"code\">\n<div class=\"container\">\n<div class=\"line number1 index0 alt2\"><code class=\"shell plain\">quit;<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p>and continue by exporting the database file you wish to copy it to the Slave:<\/p>\n<div>\n<div id=\"highlighter_4930\" class=\"syntaxhighlighter  shell\">\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td class=\"gutter\">\n<div class=\"line number1 index0 alt2\">1<\/div>\n<\/td>\n<td class=\"code\">\n<div class=\"container\">\n<div class=\"line number1 index0 alt2\"><code class=\"shell plain\">mysqldump -u root -p databasename &gt; databasename.sql<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p>change databasename with yours.<\/p>\n<p><a href=\"http:\/\/www.servermom.org\/master-slave-mysql-replication-tutorial\/2356\/2015-06-15_211417\/\" rel=\"attachment wp-att-2369\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-2369\" src=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_211417.png\" srcset=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_211417-300x45.png 300w, http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_211417.png 544w\" alt=\"2015-06-15_211417\" width=\"544\" height=\"81\" \/><\/a><\/p>\n<h2>TRANSFER THE DATABASE TO SLAVE<\/h2>\n<p>It\u2019s time to transfer \/ copy your database to the Slave server. You can do this using <strong>SCP<\/strong>.<\/p>\n<p>This is the easiest method available. The command is:<\/p>\n<div>\n<div id=\"highlighter_76091\" class=\"syntaxhighlighter  shell\">\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td class=\"gutter\">\n<div class=\"line number1 index0 alt2\">1<\/div>\n<\/td>\n<td class=\"code\">\n<div class=\"container\">\n<div class=\"line number1 index0 alt2\"><code class=\"shell functions\">scp<\/code> <code class=\"shell plain\">-P 22 <\/code><code class=\"shell plain\">\/root\/databasename<\/code><code class=\"shell plain\">.sql root@222.222.222.222:<\/code><code class=\"shell plain\">\/root\/<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p>Change <strong>22<\/strong> with specific SSH port (<em>if you changed it<\/em>) of your Slave server., also change<strong>databasename.sql<\/strong> and <strong>222.222.222.222<\/strong> with yours. Example of mine:<\/p>\n<p><a href=\"http:\/\/www.servermom.org\/master-slave-mysql-replication-tutorial\/2356\/2015-06-15_212438\/\" rel=\"attachment wp-att-2370\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-2370\" src=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_212438-620x105.png\" srcset=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_212438-300x51.png 300w, http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_212438-620x105.png 620w, http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_212438.png 789w\" alt=\"2015-06-15_212438\" width=\"620\" height=\"105\" \/><\/a><\/p>\n<h2>SETUP MYSQL SLAVE NODE<\/h2>\n<p>Leave the first server (Master node) for a while and now login to your second server which is the Slave node as root. Issue all command in this section only on Slave node.<\/p>\n<p><strong>Step 1<\/strong> \u2013 Edit MySQL configuration file using your favorite text editor like vi or Nano. Command below uses Nano editor:<\/p>\n<div>\n<div id=\"highlighter_729570\" class=\"syntaxhighlighter  shell\">\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td class=\"gutter\">\n<div class=\"line number1 index0 alt2\">1<\/div>\n<div class=\"line number2 index1 alt1\">2<\/div>\n<div class=\"line number3 index2 alt2\">3<\/div>\n<div class=\"line number4 index3 alt1\">4<\/div>\n<div class=\"line number5 index4 alt2\">5<\/div>\n<\/td>\n<td class=\"code\">\n<div class=\"container\">\n<div class=\"line number1 index0 alt2\"><code class=\"shell comments\">## On Ubuntu<\/code><\/div>\n<div class=\"line number2 index1 alt1\"><code class=\"shell plain\">nano <\/code><code class=\"shell plain\">\/etc\/mysql\/my<\/code><code class=\"shell plain\">.cnf<\/code><\/div>\n<div class=\"line number3 index2 alt2\"><\/div>\n<div class=\"line number4 index3 alt1\"><code class=\"shell comments\">## On CentOS<\/code><\/div>\n<div class=\"line number5 index4 alt2\"><code class=\"shell plain\">nano <\/code><code class=\"shell plain\">\/etc\/my<\/code><code class=\"shell plain\">.cnf<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p><strong>Step 2<\/strong> \u2013 Now let\u2019s configure MySQL configuration file in your Slave server. Copy paste these configuration right after the [mysqld] section:<\/p>\n<div>\n<div id=\"highlighter_140647\" class=\"syntaxhighlighter  shell\">\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td class=\"gutter\">\n<div class=\"line number1 index0 alt2\">1<\/div>\n<div class=\"line number2 index1 alt1\">2<\/div>\n<div class=\"line number3 index2 alt2\">3<\/div>\n<div class=\"line number4 index3 alt1\">4<\/div>\n<div class=\"line number5 index4 alt2\">5<\/div>\n<div class=\"line number6 index5 alt1\">6<\/div>\n<div class=\"line number7 index6 alt2\">7<\/div>\n<div class=\"line number8 index7 alt1\">8<\/div>\n<div class=\"line number9 index8 alt2\">9<\/div>\n<div class=\"line number10 index9 alt1\">10<\/div>\n<div class=\"line number11 index10 alt2\">11<\/div>\n<div class=\"line number12 index11 alt1\">12<\/div>\n<\/td>\n<td class=\"code\">\n<div class=\"container\">\n<div class=\"line number1 index0 alt2\"><code class=\"shell plain\">server-<\/code><code class=\"shell functions\">id<\/code><code class=\"shell plain\">=2<\/code><\/div>\n<div class=\"line number2 index1 alt1\"><code class=\"shell plain\">master-host=111.111.111.111<\/code><\/div>\n<div class=\"line number3 index2 alt2\"><code class=\"shell plain\">master-connect-retry=60<\/code><\/div>\n<div class=\"line number4 index3 alt1\"><code class=\"shell plain\">master-user=replicauser<\/code><\/div>\n<div class=\"line number5 index4 alt2\"><code class=\"shell plain\">master-password=replicapass<\/code><\/div>\n<div class=\"line number6 index5 alt1\"><code class=\"shell plain\">replicate-<\/code><code class=\"shell keyword\">do<\/code><code class=\"shell plain\">-db=databasename<\/code><\/div>\n<div class=\"line number7 index6 alt2\"><code class=\"shell plain\">relay-log = <\/code><code class=\"shell plain\">\/var\/lib\/mysql\/mysql-relay-bin<\/code><\/div>\n<div class=\"line number8 index7 alt1\"><code class=\"shell plain\">relay-log-index = <\/code><code class=\"shell plain\">\/var\/lib\/mysql\/mysql-relay-bin<\/code><code class=\"shell plain\">.index<\/code><\/div>\n<div class=\"line number9 index8 alt2\"><code class=\"shell plain\">log-error = <\/code><code class=\"shell plain\">\/var\/lib\/mysql\/mysql<\/code><code class=\"shell plain\">.err<\/code><\/div>\n<div class=\"line number10 index9 alt1\"><code class=\"shell plain\">master-info-<\/code><code class=\"shell functions\">file<\/code> <code class=\"shell plain\">= <\/code><code class=\"shell plain\">\/var\/lib\/mysql\/mysql-master<\/code><code class=\"shell plain\">.info<\/code><\/div>\n<div class=\"line number11 index10 alt2\"><code class=\"shell plain\">relay-log-info-<\/code><code class=\"shell functions\">file<\/code> <code class=\"shell plain\">= <\/code><code class=\"shell plain\">\/var\/lib\/mysql\/mysql-relay-log<\/code><code class=\"shell plain\">.info<\/code><\/div>\n<div class=\"line number12 index11 alt1\"><code class=\"shell plain\">log-bin = <\/code><code class=\"shell plain\">\/var\/lib\/mysql\/mysql-bin<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p>Change 111.111.111.111 with IP address of Master server. Change replicauser, replicapass and databasename with yours. Example:<\/p>\n<p><a href=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_214511.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-2373\" src=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_214511.png\" srcset=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_214511-300x182.png 300w, http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_214511.png 537w\" alt=\"2015-06-15_214511\" width=\"537\" height=\"326\" \/><\/a><\/p>\n<p>Save changes and exit text editor which in Nano it is Control+O then Control+X.<\/p>\n<p><strong>Step 3<\/strong> \u2013 Login to MySQL shell as root:<\/p>\n<div>\n<div id=\"highlighter_944588\" class=\"syntaxhighlighter  shell\">\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td class=\"gutter\">\n<div class=\"line number1 index0 alt2\">1<\/div>\n<\/td>\n<td class=\"code\">\n<div class=\"container\">\n<div class=\"line number1 index0 alt2\"><code class=\"shell plain\">mysql -u root -p<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p>pic:<\/p>\n<p><a href=\"http:\/\/www.servermom.org\/master-slave-mysql-replication-tutorial\/2356\/2015-06-15_213456\/\" rel=\"attachment wp-att-2371\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-2371\" src=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_213456-620x313.png\" srcset=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_213456-300x151.png 300w, http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_213456.png 620w\" alt=\"2015-06-15_213456\" width=\"620\" height=\"313\" \/><\/a><\/p>\n<p><strong>Step 4<\/strong> \u2013 Create new database with same name then exit MySQL shell:<\/p>\n<div>\n<div id=\"highlighter_817493\" class=\"syntaxhighlighter  shell\">\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td class=\"gutter\">\n<div class=\"line number1 index0 alt2\">1<\/div>\n<div class=\"line number2 index1 alt1\">2<\/div>\n<\/td>\n<td class=\"code\">\n<div class=\"container\">\n<div class=\"line number1 index0 alt2\"><code class=\"shell plain\">CREATE DATABASE databasename;<\/code><\/div>\n<div class=\"line number2 index1 alt1\"><code class=\"shell plain\">quit;<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p>replace databasename with your own real database name. Example:<\/p>\n<p><a href=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_213659.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-2372\" src=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_213659.png\" srcset=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_213659-300x95.png 300w, http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_213659.png 367w\" alt=\"2015-06-15_213659\" width=\"367\" height=\"116\" \/><\/a><\/p>\n<p><strong>Step 5<\/strong> \u2013 Restore database you have transferred previously using this command:<\/p>\n<div>\n<div id=\"highlighter_597788\" class=\"syntaxhighlighter  shell\">\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td class=\"gutter\">\n<div class=\"line number1 index0 alt2\">1<\/div>\n<\/td>\n<td class=\"code\">\n<div class=\"container\">\n<div class=\"line number1 index0 alt2\"><code class=\"shell plain\">mysql -u root -p databasename &lt; databasename.sql<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p><strong>Step 6<\/strong> \u2013 Restart MySQL server to apply \/ load changes:<\/p>\n<div>\n<div id=\"highlighter_168023\" class=\"syntaxhighlighter  shell\">\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td class=\"gutter\">\n<div class=\"line number1 index0 alt2\">1<\/div>\n<\/td>\n<td class=\"code\">\n<div class=\"container\">\n<div class=\"line number1 index0 alt2\"><code class=\"shell plain\">service mysqld restart<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p>example:<\/p>\n<p><a href=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_214956.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-2374\" src=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_214956.png\" srcset=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_214956-300x58.png 300w, http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_214956.png 550w\" alt=\"2015-06-15_214956\" width=\"550\" height=\"106\" \/><\/a><\/p>\n<p><strong>Step 7<\/strong> \u2013 Login back to MySQL shell as root to do some important tasks:<\/p>\n<div>\n<div id=\"highlighter_466371\" class=\"syntaxhighlighter  shell\">\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td class=\"gutter\">\n<div class=\"line number1 index0 alt2\">1<\/div>\n<\/td>\n<td class=\"code\">\n<div class=\"container\">\n<div class=\"line number1 index0 alt2\"><code class=\"shell plain\">mysql -u root -p<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p>First task is to stop the Slave so you can define where to look for Master log file.<\/p>\n<div>\n<div id=\"highlighter_690632\" class=\"syntaxhighlighter  shell\">\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td class=\"gutter\">\n<div class=\"line number1 index0 alt2\">1<\/div>\n<div class=\"line number2 index1 alt1\">2<\/div>\n<\/td>\n<td class=\"code\">\n<div class=\"container\">\n<div class=\"line number1 index0 alt2\"><code class=\"shell plain\">slave stop;<\/code><\/div>\n<div class=\"line number2 index1 alt1\"><code class=\"shell plain\">CHANGE MASTER TO MASTER_HOST=<\/code><code class=\"shell string\">'111.111.111.111'<\/code><code class=\"shell plain\">, MASTER_USER=<\/code><code class=\"shell string\">'replicauser'<\/code><code class=\"shell plain\">, MASTER_PASSWORD=<\/code><code class=\"shell string\">'replicapass'<\/code><code class=\"shell plain\">, MASTER_LOG_FILE=<\/code><code class=\"shell string\">'mysql-bin.000001'<\/code><code class=\"shell plain\">, MASTER_LOG_POS=12345;<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p>Change 111.111.111.111, replicauser, replicapass, <strong>mysql-bin.000001<\/strong> and <strong>12345<\/strong> with yours. Example of mine:<\/p>\n<p><a href=\"http:\/\/www.servermom.org\/master-slave-mysql-replication-tutorial\/2356\/2015-06-15_215900\/\" rel=\"attachment wp-att-2375\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-2375\" src=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_215900-620x193.png\" srcset=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_215900-300x93.png 300w, http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_215900-620x193.png 620w, http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_215900.png 1149w\" alt=\"2015-06-15_215900\" width=\"620\" height=\"193\" \/><\/a><\/p>\n<p>Once done, now you can start Slave and show its status with this command:<\/p>\n<div>\n<div id=\"highlighter_152083\" class=\"syntaxhighlighter  shell\">\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td class=\"gutter\">\n<div class=\"line number1 index0 alt2\">1<\/div>\n<div class=\"line number2 index1 alt1\">2<\/div>\n<\/td>\n<td class=\"code\">\n<div class=\"container\">\n<div class=\"line number1 index0 alt2\"><code class=\"shell plain\">slave start;<\/code><\/div>\n<div class=\"line number2 index1 alt1\"><code class=\"shell plain\">show slave status\\G<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p>It will give you an output similar to this:<\/p>\n<p><a href=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_220403.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-2376\" src=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_220403.png\" srcset=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_220403-208x300.png 208w, http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_220403.png 519w\" alt=\"2015-06-15_220403\" width=\"519\" height=\"748\" \/><\/a><\/p>\n<p>If you see something similar to above pic then you are success. There are few lines to pay more attention with: <strong><em>Last_Error<\/em><\/strong> will be blank, and <strong><em>Slave_IO_State<\/em><\/strong> will report \u201c<em>Waiting for master to send event<\/em>\u201c, also the <strong><em>Seconds_Behind_Master<\/em><\/strong> will indicate how far behind it is. If you see those then congratulation you have configured MySQL slave replication.<\/p>\n<p>You can now exit MySQL shell:<\/p>\n<div>\n<div id=\"highlighter_995742\" class=\"syntaxhighlighter  shell\">\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td class=\"gutter\">\n<div class=\"line number1 index0 alt2\">1<\/div>\n<\/td>\n<td class=\"code\">\n<div class=\"container\">\n<div class=\"line number1 index0 alt2\"><code class=\"shell plain\">quit;<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<h3>FINISHING UP<\/h3>\n<p>Leave Slave server for a while and go back to Master server. Login back to MySQL shell at Master server:<\/p>\n<div>\n<div id=\"highlighter_9357\" class=\"syntaxhighlighter  shell\">\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td class=\"gutter\">\n<div class=\"line number1 index0 alt2\">1<\/div>\n<\/td>\n<td class=\"code\">\n<div class=\"container\">\n<div class=\"line number1 index0 alt2\"><code class=\"shell plain\">mysql -u root -p<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p>this is important, as you locked the tables of MySQL database in Master server, you can now safely unlock it:<\/p>\n<div>\n<div id=\"highlighter_920029\" class=\"syntaxhighlighter  shell\">\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td class=\"gutter\">\n<div class=\"line number1 index0 alt2\">1<\/div>\n<div class=\"line number2 index1 alt1\">2<\/div>\n<\/td>\n<td class=\"code\">\n<div class=\"container\">\n<div class=\"line number1 index0 alt2\"><code class=\"shell plain\">UNLOCK TABLES;<\/code><\/div>\n<div class=\"line number2 index1 alt1\"><code class=\"shell plain\">quit;<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p>pic:<\/p>\n<p><a href=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_221916.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-2377\" src=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_221916-620x322.png\" srcset=\"http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_221916-300x156.png 300w, http:\/\/www.servermom.org\/wp-content\/uploads\/2015\/06\/2015-06-15_221916.png 620w\" alt=\"2015-06-15_221916\" width=\"620\" height=\"322\" \/><\/a><\/p>\n<p>In production environment this is very important to make sure database between Master and Slave can be synced easily, but visitors \/ users of your websites will not able to make changes (<em>post comment, add article, etc<\/em>) during your time setting up MySQL replication above. But however you\u2019ll not suffer any downtime during the process. That\u2019s it.<\/p>\n<p>Additional Note:<\/p>\n<p>Once Master \u2013 Slave mode is enabled and succeed, the MySQL root password of Slave server will change following its Master.<\/p>\n<p>If you accidentally change a row of data on your slave or if there is an issue in connecting, try issuing this command inside MySQL shell:<\/p>\n<div>\n<div id=\"highlighter_119784\" class=\"syntaxhighlighter  shell\">\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td class=\"gutter\">\n<div class=\"line number1 index0 alt2\">1<\/div>\n<div class=\"line number2 index1 alt1\">2<\/div>\n<div class=\"line number3 index2 alt2\">3<\/div>\n<\/td>\n<td class=\"code\">\n<div class=\"container\">\n<div class=\"line number1 index0 alt2\"><code class=\"shell plain\">STOP SLAVE;<\/code><\/div>\n<div class=\"line number2 index1 alt1\"><code class=\"shell plain\">SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;<\/code><\/div>\n<div class=\"line number3 index2 alt2\"><code class=\"shell plain\">START SLAVE;<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p>Do not forget to follow me on <a class=\"ext-link ext-icon-1\" title=\"external link\" href=\"http:\/\/twitter.com\/servermomdotcom\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">twitter<\/a> to get notified quicker when a new article is posted. Have fun!<\/p>\n<p>&nbsp;<\/p>\n<div id=\"wp-subscribe-content-bottom\"><\/div>\n<div class=\"sharedaddy sd-sharing-enabled\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>In this guide I will show you practical steps (with pictures) how to setup MySQL Replication in Master \u2013 Slave mode on either CentOS or Ubuntu server. I believe MySQL replication technique is not expert-only stuff but even newbies can learn and use this technique. In case if you didn\u2019t know yet, let\u2019s start with [&hellip;]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-4502","post","type-post","status-publish","format-standard","hentry","category-uncategorized","has-post-title","has-post-date","has-post-category","has-post-tag","has-post-comment","has-post-author",""],"builder_content":"","_links":{"self":[{"href":"https:\/\/cdnhost.net\/content\/index.php?rest_route=\/wp\/v2\/posts\/4502","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/cdnhost.net\/content\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/cdnhost.net\/content\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/cdnhost.net\/content\/index.php?rest_route=\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/cdnhost.net\/content\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4502"}],"version-history":[{"count":0,"href":"https:\/\/cdnhost.net\/content\/index.php?rest_route=\/wp\/v2\/posts\/4502\/revisions"}],"wp:attachment":[{"href":"https:\/\/cdnhost.net\/content\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4502"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/cdnhost.net\/content\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4502"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/cdnhost.net\/content\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4502"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}