{"id":11474,"date":"2019-03-14T08:31:35","date_gmt":"2019-03-14T08:31:35","guid":{"rendered":"http:\/\/www.appservgrid.com\/paw92\/?p=11474"},"modified":"2019-03-14T08:31:35","modified_gmt":"2019-03-14T08:31:35","slug":"how-to-setup-mysql-master-slave-replication-in-rhel-centos-fedora","status":"publish","type":"post","link":"https:\/\/www.appservgrid.com\/paw92\/index.php\/2019\/03\/14\/how-to-setup-mysql-master-slave-replication-in-rhel-centos-fedora\/","title":{"rendered":"How to Setup MySQL (Master-Slave) Replication in RHEL, CentOS, Fedora"},"content":{"rendered":"<p>The following tutorial aims to provide you a simple step-by-step guide for setting up\u00a0<strong>MySQL<\/strong>\u00a0(<strong>Master-Slave<\/strong>)\u00a0<strong>Replication<\/strong>\u00a0in\u00a0<strong>RHEL 6.3\/6.2\/6.1\/6\/5.8<\/strong>,\u00a0<strong>CentOS 6.3\/6.2\/6.1\/6\/5.8<\/strong>\u00a0and\u00a0<strong>Fedora 17,16,15,14,13,12<\/strong>\u00a0using latest\u00a0<strong>MySQL\u00a0<\/strong>version. This guide is specially written for\u00a0<strong>CentOS 6.3<\/strong>\u00a0Operating System, but also work with older version of\u00a0<strong>Linux<\/strong>\u00a0distributions with\u00a0<strong>MySQL 5.x<\/strong>.<\/p>\n<p><strong>UPDATE:<\/strong>\u00a0If you\u2019re looking for MariaDB Master-Slave Replication under CentOS\/RHEL 7 and Debian 8 and it\u2019s derivatives such as Ubuntu, follow this guide\u00a0<a href=\"https:\/\/www.tecmint.com\/mariadb-master-slave-replication-on-centos-rhel-debian\/\" target=\"_blank\" rel=\"noopener\">Setup MariaDB Master-Slave Replication<\/a>.<\/p>\n<div id=\"attachment_1345\" class=\"wp-caption aligncenter\">\n<p><a href=\"https:\/\/www.tecmint.com\/wp-content\/uploads\/2012\/10\/mysql-replication.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-1345\" title=\"mysql replication in Linux\" src=\"https:\/\/www.tecmint.com\/wp-content\/uploads\/2012\/10\/mysql-replication-300x182.jpg\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" srcset=\"https:\/\/www.tecmint.com\/wp-content\/uploads\/2012\/10\/mysql-replication-300x182.jpg 300w, https:\/\/www.tecmint.com\/wp-content\/uploads\/2012\/10\/mysql-replication.jpg 630w\" alt=\"mysql replication in Linux\" width=\"300\" height=\"182\" data-lazy-loaded=\"true\" \/><\/a><\/p>\n<p class=\"wp-caption-text\">MySQL Master-Slave Replication in RedHat \/ CentOS \/ Fedora<\/p>\n<\/div>\n<p>The\u00a0<strong>MySQL Replication<\/strong>\u00a0is very useful in terms of\u00a0<strong>Data Security<\/strong>,\u00a0<strong>Fail-over Solution<\/strong>,\u00a0<strong>Database Backup from Slave<\/strong>,\u00a0<strong>Analytics<\/strong>\u00a0etc. We use the following things to carry the replication process. In your scenario it would be different.<\/p>\n<ol>\n<li>Working\u00a0<strong>Linux OS<\/strong>\u00a0like\u00a0<a href=\"https:\/\/www.tecmint.com\/centos-6-3-step-by-step-installation-guide-with-screenshots\/\" target=\"_blank\" rel=\"noopener\">CentOS 6.3<\/a>,\u00a0<a href=\"https:\/\/www.tecmint.com\/red-hat-enterprise-linux-rhel-6-installation-guide-with-screenshots\/\" target=\"_blank\" rel=\"noopener\">RedHat 6.3<\/a>\u00a0or\u00a0<a href=\"https:\/\/www.tecmint.com\/fedora-17-step-by-step-installation-guide-with-screenshots\/\" target=\"_blank\" rel=\"noopener\">Fedora 17<\/a><\/li>\n<li>Master and Slave are\u00a0<strong>CentOS 6.3<\/strong>\u00a0Linux Servers.<\/li>\n<li>Master IP Address is:\u00a0<strong>192.168.1.1<\/strong>.<\/li>\n<li>Slave IP Address is:\u00a0<strong>192.168.1.2<\/strong>.<\/li>\n<li>Master and Slave are on the same\u00a0<strong>LAN<\/strong>\u00a0network.<\/li>\n<li>Master and Slave has\u00a0<strong>MySQL\u00a0<\/strong>version installed.<\/li>\n<li>Master allow remote\u00a0<strong>MySQL connections<\/strong>\u00a0on port\u00a0<strong>3306<\/strong>.<\/li>\n<\/ol>\n<p>We have two servers, one is\u00a0<strong>Master<\/strong>\u00a0with\u00a0<strong>IP<\/strong>\u00a0(<strong>192.168.1.1<\/strong>) and other is\u00a0<strong>Slave<\/strong>\u00a0as (<strong>192.168.1.2<\/strong>). We have divided the setup process in two phases to make things easier for you, In\u00a0<strong>Phase I<\/strong>\u00a0we will configure\u00a0<strong>Master<\/strong>\u00a0server and in\u00a0<strong>Phase II<\/strong>\u00a0with\u00a0<strong>Slave<\/strong>\u00a0server. Let\u2019s start the replication setup process.<\/p>\n<h3>Phase I: Configure Master Server (192.168.1.1) for Replication<\/h3>\n<p>In\u00a0<strong>Phase I<\/strong>, we will see the installation of\u00a0<strong>MySQL<\/strong>, setting up\u00a0<strong>Replication<\/strong>\u00a0and then verifying replication.<\/p>\n<h5>Install a MySQL in Master Server<\/h5>\n<p><center><ins class=\"adsbygoogle\" data-ad-client=\"ca-pub-2601749019656699\" data-ad-slot=\"5590002574\" data-ad-format=\"auto\" data-adsbygoogle-status=\"done\"><ins id=\"aswift_4_expand\"><ins id=\"aswift_4_anchor\"><iframe id=\"aswift_4\" name=\"aswift_4\" width=\"780\" height=\"90\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\" allowfullscreen=\"allowfullscreen\" data-mce-fragment=\"1\"><\/iframe><\/ins><\/ins><\/ins><\/center>First, proceed with\u00a0<strong>MySQL<\/strong>\u00a0installation using\u00a0<a href=\"https:\/\/www.tecmint.com\/20-linux-yum-yellowdog-updater-modified-commands-for-package-mangement\/\" target=\"_blank\" rel=\"noopener\">YUM command<\/a>. If you already have MySQL installation, you can skip this step.<\/p>\n<pre># yum install mysql-server mysql<\/pre>\n<h5>Configure a MySQL in Master Server<\/h5>\n<p>Open\u00a0<strong>my.cnf<\/strong>\u00a0configuration file with\u00a0<strong>VI<\/strong>\u00a0editor.<\/p>\n<pre># vi \/etc\/my.cnf<\/pre>\n<p>Add the following entries under\u00a0<strong>[mysqld]<\/strong>\u00a0section and don\u2019t forget to replace\u00a0<strong>tecmint<\/strong>\u00a0with database name that you would like to replicate on\u00a0<strong>Slave<\/strong>.<\/p>\n<pre>server-id = 1\r\nbinlog-do-db=<strong>tecmint<\/strong>\r\nrelay-log = \/var\/lib\/mysql\/mysql-relay-bin\r\nrelay-log-index = \/var\/lib\/mysql\/mysql-relay-bin.index\r\nlog-error = \/var\/lib\/mysql\/mysql.err\r\nmaster-info-file = \/var\/lib\/mysql\/mysql-master.info\r\nrelay-log-info-file = \/var\/lib\/mysql\/mysql-relay-log.info\r\nlog-bin = \/var\/lib\/mysql\/mysql-bin<\/pre>\n<p>Restart the\u00a0<strong>MySQL<\/strong>\u00a0service.<\/p>\n<pre># \/etc\/init.d\/mysqld restart<\/pre>\n<p>Login into\u00a0<strong>MySQL<\/strong>\u00a0as\u00a0<strong>root<\/strong>\u00a0user and create the\u00a0<strong>slave user<\/strong>\u00a0and grant privileges for replication. Replace\u00a0<strong>slave_user<\/strong>with user and\u00a0<strong>your_password<\/strong>\u00a0with password.<\/p>\n<pre># mysql -u root -p<\/pre>\n<pre>mysql&gt; GRANT REPLICATION SLAVE ON *.* TO '<strong>slave_user<\/strong>'@'%' IDENTIFIED BY '<strong>your_password<\/strong>';\r\nmysql&gt; FLUSH PRIVILEGES;\r\nmysql&gt; FLUSH TABLES WITH READ LOCK;\r\nmysql&gt; SHOW MASTER STATUS;\r\n\r\n+------------------+----------+--------------+------------------+\r\n| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |\r\n+------------------+----------+--------------+------------------+\r\n| <strong>mysql-bin.000003<\/strong> | <strong>11128001<\/strong> | tecmint\t\t |                  |\r\n+------------------+----------+--------------+------------------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql&gt; quit;<\/pre>\n<p>Please write down the File (<strong>mysql-bin.000003<\/strong>) and Position (<strong>11128001<\/strong>) numbers, we required these numbers later on\u00a0<strong>Slave<\/strong>\u00a0server. Next apply\u00a0<strong>READ LOCK<\/strong>\u00a0to databases to export all the database and master database information with\u00a0<strong>mysqldump<\/strong>\u00a0command.<\/p>\n<pre>#  mysqldump -u root -p --all-databases --master-data &gt; \/root\/dbdump.db<\/pre>\n<p>Once you\u2019ve dump all the databases, now again connect to mysql as root user and unlcok tables.<\/p>\n<pre>mysql&gt; UNLOCK TABLES;\r\nmysql&gt; quit;<\/pre>\n<p>Upload the database dump file on\u00a0<strong>Slave<\/strong>\u00a0Server (<strong>192.168.1.2<\/strong>) using SCP command.<\/p>\n<pre>scp \/root\/dbdump.db root@192.168.1.2:\/root\/<\/pre>\n<p>That\u2019s it we have successfully configured\u00a0<strong>Master<\/strong>\u00a0server, let\u2019s proceed to\u00a0<strong>Phase II<\/strong>\u00a0section.<\/p>\n<h3>Phase II: Configure Slave Server (192.168.1.2) for Replication<\/h3>\n<p>In\u00a0<strong>Phase II<\/strong>, we do the installation of\u00a0<strong>MySQL<\/strong>, setting up\u00a0<strong>Replication<\/strong>\u00a0and then verifying replication.<\/p>\n<h5>Install a MySQL in Slave Server<\/h5>\n<p>If you don\u2019t have\u00a0<strong>MySQL<\/strong>\u00a0installed, then install it using YUM command.<\/p>\n<pre># yum install mysql-server mysql<\/pre>\n<h5>Configure a MySQL in Slave Server<\/h5>\n<p>Open\u00a0<strong>my.cnf<\/strong>\u00a0configuration file with\u00a0<strong>VI<\/strong>\u00a0editor.<\/p>\n<pre># vi \/etc\/my.cnf<\/pre>\n<p>Add the following entries under\u00a0<strong>[mysqld]<\/strong>\u00a0section and don\u2019t forget to replace\u00a0<strong>IP<\/strong>\u00a0address of\u00a0<strong>Master<\/strong>\u00a0server,\u00a0<strong>tecmint<\/strong>\u00a0with database name etc, that you would like to replicate with\u00a0<strong>Master<\/strong>.<\/p>\n<pre>server-id = 2\r\nmaster-host=<strong>192.168.1.1<\/strong>\r\nmaster-connect-retry=60\r\nmaster-user=<strong>slave_user<\/strong>\r\nmaster-password=<strong>yourpassword<\/strong>\r\nreplicate-do-db=<strong>tecmint<\/strong>\r\nrelay-log = \/var\/lib\/mysql\/mysql-relay-bin\r\nrelay-log-index = \/var\/lib\/mysql\/mysql-relay-bin.index\r\nlog-error = \/var\/lib\/mysql\/mysql.err\r\nmaster-info-file = \/var\/lib\/mysql\/mysql-master.info\r\nrelay-log-info-file = \/var\/lib\/mysql\/mysql-relay-log.info\r\nlog-bin = \/var\/lib\/mysql\/mysql-bin<\/pre>\n<p>Now import the dump file that we exported in earlier command and restart the MySQL service.<\/p>\n<pre># mysql -u root -p &lt; \/root\/dbdump.db\r\n# \/etc\/init.d\/mysqld restart\r\n<\/pre>\n<p>Login into\u00a0<strong>MySQL<\/strong>\u00a0as root user and stop the\u00a0<strong>slave<\/strong>. Then tell the\u00a0<strong>slave<\/strong>\u00a0to where to look for\u00a0<strong>Master log file<\/strong>, that we have write down on master with\u00a0<strong>SHOW MASTER STATUS<\/strong>; command as File (<strong>mysql-bin.000003<\/strong>) and Position (<strong>11128001<\/strong>) numbers. You must change\u00a0<strong>192.168.1.1<\/strong>\u00a0to the\u00a0<strong>IP<\/strong>\u00a0address of the\u00a0<strong>Master Server<\/strong>, and change the\u00a0<strong>user<\/strong>\u00a0and\u00a0<strong>password<\/strong>\u00a0accordingly.<\/p>\n<pre># mysql -u root -p<\/pre>\n<pre>mysql&gt; slave stop;\r\nmysql&gt; CHANGE MASTER TO MASTER_HOST='<b>192.168.1.1<\/b>', MASTER_USER='<b>slave_user<\/b>', MASTER_PASSWORD='<b>yourpassword<\/b>', MASTER_LOG_FILE='<strong>mysql-bin.000003<\/strong>', MASTER_LOG_POS=<strong>11128001<\/strong>;\r\nmysql&gt; slave start;\r\nmysql&gt; show slave status\\G<\/pre>\n<pre>*************************** 1. row ***************************\r\n               Slave_IO_State: Waiting for master to send event\r\n                  Master_Host: <strong>192.168.1.1<\/strong>\r\n                  Master_User: slave_user\r\n                  Master_Port: 3306\r\n                Connect_Retry: 60\r\n              Master_Log_File: mysql-bin.000003\r\n          Read_Master_Log_Pos: 12345100\r\n               Relay_Log_File: mysql-relay-bin.000002\r\n                Relay_Log_Pos: 11381900\r\n        Relay_Master_Log_File: mysql-bin.000003\r\n             Slave_IO_Running: Yes\r\n            Slave_SQL_Running: Yes\r\n              Replicate_Do_DB: <strong>tecmint<\/strong>\r\n          Replicate_Ignore_DB:\r\n           Replicate_Do_Table:\r\n       Replicate_Ignore_Table:\r\n      Replicate_Wild_Do_Table:\r\n  Replicate_Wild_Ignore_Table:\r\n                   Last_Errno: 0\r\n                   Last_Error:\r\n                 Skip_Counter: 0\r\n          Exec_Master_Log_Pos: 12345100\r\n              Relay_Log_Space: 11382055\r\n              Until_Condition: None\r\n               Until_Log_File:\r\n                Until_Log_Pos: 0\r\n           Master_SSL_Allowed: No\r\n           Master_SSL_CA_File:\r\n           Master_SSL_CA_Path:\r\n              Master_SSL_Cert:\r\n            Master_SSL_Cipher:\r\n               Master_SSL_Key:\r\n        Seconds_Behind_Master: 0\r\nMaster_SSL_Verify_Server_Cert: No\r\n                Last_IO_Errno: 0\r\n                Last_IO_Error:\r\n               Last_SQL_Errno: 0\r\n               Last_SQL_Error:\r\n1 row in set (0.00 sec)<\/pre>\n<h3>Verifying MySQL Replication on Master and Slave Server<\/h3>\n<p>It\u2019s really very important to know that the replication is working perfectly. On\u00a0<strong>Master<\/strong>\u00a0server create table and insert some values in it.<\/p>\n<h5>On Master Server<\/h5>\n<pre>mysql&gt; create database tecmint;\r\nmysql&gt; use tecmint;\r\nmysql&gt; CREATE TABLE employee (c int);\r\nmysql&gt; INSERT INTO employee (c) VALUES (1);\r\nmysql&gt; SELECT * FROM employee;<\/pre>\n<pre>+------+\r\n|  c  |\r\n+------+\r\n|  1  |\r\n+------+\r\n1 row in set (0.00 sec)<\/pre>\n<h5>On Slave Server<\/h5>\n<p>Verifying the\u00a0<strong>SLAVE<\/strong>, by running the same command, it will return the same values in the slave too.<\/p>\n<pre>mysql&gt; use tecmint;\r\nmysql&gt; SELECT * FROM employee;<\/pre>\n<pre>+------+\r\n|  c  |\r\n+------+\r\n|  1  |\r\n+------+\r\n1 row in set (0.00 sec)<\/pre>\n<p>That\u2019s it, finally you\u2019ve configured\u00a0<strong>MySQL Replication<\/strong>\u00a0in a few simple steps. More information can be found at\u00a0<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/replication-howto-masterbaseconfig.html\" target=\"_blank\" rel=\"noopener\">MySQL Replication Guide<\/a>.<\/p>\n<p><a href=\"https:\/\/www.tecmint.com\/how-to-setup-mysql-master-slave-replication-in-rhel-centos-fedora\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>The following tutorial aims to provide you a simple step-by-step guide for setting up\u00a0MySQL\u00a0(Master-Slave)\u00a0Replication\u00a0in\u00a0RHEL 6.3\/6.2\/6.1\/6\/5.8,\u00a0CentOS 6.3\/6.2\/6.1\/6\/5.8\u00a0and\u00a0Fedora 17,16,15,14,13,12\u00a0using latest\u00a0MySQL\u00a0version. This guide is specially written for\u00a0CentOS 6.3\u00a0Operating System, but also work with older version of\u00a0Linux\u00a0distributions with\u00a0MySQL 5.x. UPDATE:\u00a0If you\u2019re looking for MariaDB Master-Slave Replication under CentOS\/RHEL 7 and Debian 8 and it\u2019s derivatives such as Ubuntu, follow &hellip; <\/p>\n<p class=\"link-more\"><a href=\"https:\/\/www.appservgrid.com\/paw92\/index.php\/2019\/03\/14\/how-to-setup-mysql-master-slave-replication-in-rhel-centos-fedora\/\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;How to Setup MySQL (Master-Slave) Replication in RHEL, CentOS, Fedora&#8221;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-11474","post","type-post","status-publish","format-standard","hentry","category-linux"],"_links":{"self":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/11474","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/comments?post=11474"}],"version-history":[{"count":1,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/11474\/revisions"}],"predecessor-version":[{"id":11475,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/11474\/revisions\/11475"}],"wp:attachment":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/media?parent=11474"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/categories?post=11474"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/tags?post=11474"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}