{"id":3089,"date":"2016-10-07T15:54:05","date_gmt":"2016-10-07T19:54:05","guid":{"rendered":"https:\/\/www.globo.tech\/learning-center\/?p=3089"},"modified":"2017-12-12T15:46:13","modified_gmt":"2017-12-12T20:46:13","slug":"setup-mysql-replication-centos-7","status":"publish","type":"post","link":"https:\/\/www.globo.tech\/learning-center\/setup-mysql-replication-centos-7\/","title":{"rendered":"How to setup a MySQL Master-Slave Replication on CentOS 7"},"content":{"rendered":"

How to setup a MySQL Master-Slave Replication on CentOS 7<\/h1>\n

MySQL replication is a technique by which a master database will be automatically copied to one or more slave databases, making data backup, recovery, and analysis a much easier prospect. The following guide will walk you through a basic MySQL replication setup using just two servers, one master and one slave. We’ll also be using MariaDB, a popular drop-in replacement for MySQL.<\/p>\n

Getting Started<\/h2>\n

Before you begin, make sure you have the following:
\n\u2022 2 servers (Cloud Server<\/a> or Dedicated Server<\/a>), both running a fresh installation of CentOS 7
\n\u2022 Root access to both machines<\/p>\n

Tutorial<\/h2>\n

Note: these are the local IP addresses our servers will be using for the purposes of this guide.
\n\u2022\tServer Master IP: 10.0.0.179
\n\u2022\tServer Slave IP: 10.0.0.180<\/p>\n

Before starting, we need to make sure that Selinux is disabled on each of our servers.<\/p>\n

setenforce 0
\nsed -i 's\/enforcing\/disabled\/' \/etc\/sysconfig\/selinux
\nsed -i 's\/enforcing\/disabled\/' \/etc\/selinux\/config<\/code><\/p>\n

Let’s also install MariaDB on each server, making sure to secure the installation.<\/p>\n

yum install mariadb-server mariadb -y
\nsystemctl start mariadb.service
\nmysql_secure_installation<\/code><\/p>\n

Now we can begin the setup, focusing first on the master server. Go to my.cnf on the master server and add these 3 lines:<\/p>\n

nano \/etc\/my.cnf<\/code><\/p>\n

[mysqld]
\n...
\nserver_id=1
\nlog-bin
\nreplicate-do-db=globotech<\/code><\/p>\n

Replace globotech with the name of the database that you want to replicate.<\/p>\n

To save the changes, you will have to restart MariaDB.<\/p>\n

systemctl restart mariadb.service<\/code><\/p>\n

Now let’s load up MariaDB itself so that we can better configure the replication.<\/p>\n

mysql -u root -p<\/code><\/p>\n

MariaDB [(none)]> grant replication slave on *.* to 'slave_user'@'%' identified by 'globopassword';
\nMariaDB [(none)]> flush privileges;<\/code><\/p>\n

Replace slave_user with the slave username and globopassword with its password.
\nMariaDB [(none)]> show master status;
\n+--------------------+----------+--------------+------------------+
\n| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
\n+--------------------+----------+--------------+------------------+
\n| mariadb-bin.000001 | 475 | | |
\n+--------------------+----------+--------------+------------------+
\n1 row in set (0.00 sec)<\/code><\/p>\n

Make a note of the log file “mariadb-bin.000001” and the log position “475” for later on in the guide.
\nMariaDB [(none)]> exit<\/code><\/p>\n

The next step is to configure with the slave server. Like before, open up its my.cnf and add these 2 lines:
\n[mysqld]
\n...
\nserver_id=2
\nreplicate-do-db=globotech<\/code><\/p>\n

Of course, replace globotech with the name of your own database.<\/p>\n

Next, restart MariaDB so the changes will be recognized.<\/p>\n

systemctl restart mariadb.service<\/code><\/p>\n

We will now configure the slave to connect to the master.<\/p>\n

mysql -u root -p<\/code><\/p>\n

MariaDB [(none)]> stop slave;
\nMariaDB [(none)]> change master to master_host='10.0.0.179' , master_user='slave_user' , master_password='globopassword' , master_log_file='mariadb-bin.000001' , master_log_pos=475;
\nMariaDB [(none)]> start slave;<\/code><\/p>\n

Replace 10.0.0.179 with the IP address for your master server, and add in the log file and log position that you noted earlier.<\/p>\n

We’re almost done. Let’s check the replication status.<\/p>\n

MariaDB [(none)]> SHOW SLAVE STATUS\\G;
\n*************************** 1. row ***************************
\n Slave_IO_State: Waiting for master to send event
\n Master_Host: 10.0.0.179
\n Master_User: slave_user
\n Master_Port: 3306
\n Connect_Retry: 60
\n Master_Log_File: mariadb-bin.000001
\n Read_Master_Log_Pos: 475
\n Relay_Log_File: mariadb-relay-bin.000002
\n Relay_Log_Pos: 531
\n Relay_Master_Log_File: mariadb-bin.000001
\n Slave_IO_Running: Yes
\n Slave_SQL_Running: Yes
\n Replicate_Do_DB: globotech
\n Replicate_Ignore_DB:
\n Replicate_Do_Table:
\n Replicate_Ignore_Table:
\n Replicate_Wild_Do_Table:
\n Replicate_Wild_Ignore_Table:
\n Last_Errno: 0
\n Last_Error:
\n Skip_Counter: 0
\n Exec_Master_Log_Pos: 475
\n Relay_Log_Space: 827
\n Until_Condition: None
\n Until_Log_File:
\n Until_Log_Pos: 0
\n Master_SSL_Allowed: No
\n Master_SSL_CA_File:
\n Master_SSL_CA_Path:
\n Master_SSL_Cert:
\n Master_SSL_Cipher:
\n Master_SSL_Key:
\n Seconds_Behind_Master: 0
\nMaster_SSL_Verify_Server_Cert: No
\n Last_IO_Errno: 0
\n Last_IO_Error:
\n Last_SQL_Errno: 0
\n Last_SQL_Error:
\n Replicate_Ignore_Server_Ids:
\n Master_Server_Id: 1
\n1 row in set (0.00 sec)<\/code><\/p>\n

Finally, let’s test that replication is actually occurring. Log in to the master server and enter MariaDB.<\/p>\n

mysql -u root -p<\/code><\/p>\n

Create the database globotech and insert a table.
\nMariaDB [(none)]> create database globotech;
\nMariaDB [(none)]> use globotech;
\nMariaDB [globotech]> create table staff (c int);
\nMariaDB [globotech]> insert into staff (c) values (3);
\nMariaDB [globotech]> select * from staff;
\n+------+
\n| c |
\n+------+
\n| 3 |
\n+------+
\n1 row in set (0.00 sec)<\/code><\/p>\n

Once logging into the slave server, you should see the same database and table.
\nmysql -u root -p<\/code><\/p>\n

MariaDB [(none)]> use globotech;
\nMariaDB [globotech]> select * from staff;
\n+------+
\n| c |
\n+------+
\n| 3 |
\n+------+
\n1 row in set (0.00 sec)<\/code><\/p>\n

Conclusion<\/h2>\n

With MySQL (or in this case, MariaDB) replication, you’ll be able to better protect your databases in the case of failure, and safely analyse data without worrying about corrupting your production copy. If you found this article helpful, feel free to share it with your friends and let us know in the comments below! <\/p>\n","protected":false},"excerpt":{"rendered":"

How to setup a MySQL Master-Slave Replication on CentOS 7 MySQL replication is a technique by which a master database will be automatically copied to one or more slave databases, making data backup, recovery, and analysis a much easier prospect. The following guide will walk you through a basic MySQL replication setup using just two<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[61],"tags":[],"class_list":["post-3089","post","type-post","status-publish","format-standard","hentry","category-database","operating_system-centos-7"],"yoast_head":"\nHow to setup a MySQL Master-Slave Replication on CentOS 7 - Globo.Tech<\/title>\n<meta name=\"description\" content=\"This tutorial will show you how to setup a MySQL Master-Slave Replication on your CentOS 7 server. Read now & Replicate your database!\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.globo.tech\/learning-center\/setup-mysql-replication-centos-7\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to setup a MySQL Master-Slave Replication on CentOS 7 - Globo.Tech\" \/>\n<meta property=\"og:description\" content=\"This tutorial will show you how to setup a MySQL Master-Slave Replication on your CentOS 7 server. Read now & Replicate your database!\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.globo.tech\/learning-center\/setup-mysql-replication-centos-7\/\" \/>\n<meta property=\"og:site_name\" content=\"Globo.Tech\" \/>\n<meta property=\"article:published_time\" content=\"2016-10-07T19:54:05+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-12-12T20:46:13+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.globo.tech\/learning-center\/wp-content\/uploads\/2016\/09\/GloboTech-Logo.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1920\" \/>\n\t<meta property=\"og:image:height\" content=\"963\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"GloboTech Communications\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"GloboTech Communications\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.globo.tech\\\/learning-center\\\/setup-mysql-replication-centos-7\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.globo.tech\\\/learning-center\\\/setup-mysql-replication-centos-7\\\/\"},\"author\":{\"name\":\"GloboTech Communications\",\"@id\":\"https:\\\/\\\/www.globo.tech\\\/learning-center\\\/#\\\/schema\\\/person\\\/e17784b37f4a4f49b7bc611847912e87\"},\"headline\":\"How to setup a MySQL Master-Slave Replication on CentOS 7\",\"datePublished\":\"2016-10-07T19:54:05+00:00\",\"dateModified\":\"2017-12-12T20:46:13+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.globo.tech\\\/learning-center\\\/setup-mysql-replication-centos-7\\\/\"},\"wordCount\":437,\"commentCount\":0,\"articleSection\":[\"Database\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.globo.tech\\\/learning-center\\\/setup-mysql-replication-centos-7\\\/\",\"url\":\"https:\\\/\\\/www.globo.tech\\\/learning-center\\\/setup-mysql-replication-centos-7\\\/\",\"name\":\"How to setup a MySQL Master-Slave Replication on CentOS 7 - Globo.Tech\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.globo.tech\\\/learning-center\\\/#website\"},\"datePublished\":\"2016-10-07T19:54:05+00:00\",\"dateModified\":\"2017-12-12T20:46:13+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.globo.tech\\\/learning-center\\\/#\\\/schema\\\/person\\\/e17784b37f4a4f49b7bc611847912e87\"},\"description\":\"This tutorial will show you how to setup a MySQL Master-Slave Replication on your CentOS 7 server. Read now & Replicate your database!\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.globo.tech\\\/learning-center\\\/setup-mysql-replication-centos-7\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.globo.tech\\\/learning-center\\\/setup-mysql-replication-centos-7\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.globo.tech\\\/learning-center\\\/setup-mysql-replication-centos-7\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.globo.tech\\\/learning-center\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to setup a MySQL Master-Slave Replication on CentOS 7\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.globo.tech\\\/learning-center\\\/#website\",\"url\":\"https:\\\/\\\/www.globo.tech\\\/learning-center\\\/\",\"name\":\"Globo.Tech\",\"description\":\"Welcome to the Official Globo.Tech Learning Center\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.globo.tech\\\/learning-center\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.globo.tech\\\/learning-center\\\/#\\\/schema\\\/person\\\/e17784b37f4a4f49b7bc611847912e87\",\"name\":\"GloboTech Communications\",\"sameAs\":[\"http:\\\/\\\/www.gtcomm.net\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to setup a MySQL Master-Slave Replication on CentOS 7 - Globo.Tech","description":"This tutorial will show you how to setup a MySQL Master-Slave Replication on your CentOS 7 server. Read now & Replicate your database!","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":"https:\/\/www.globo.tech\/learning-center\/setup-mysql-replication-centos-7\/","og_locale":"en_US","og_type":"article","og_title":"How to setup a MySQL Master-Slave Replication on CentOS 7 - Globo.Tech","og_description":"This tutorial will show you how to setup a MySQL Master-Slave Replication on your CentOS 7 server. Read now & Replicate your database!","og_url":"https:\/\/www.globo.tech\/learning-center\/setup-mysql-replication-centos-7\/","og_site_name":"Globo.Tech","article_published_time":"2016-10-07T19:54:05+00:00","article_modified_time":"2017-12-12T20:46:13+00:00","og_image":[{"width":1920,"height":963,"url":"https:\/\/www.globo.tech\/learning-center\/wp-content\/uploads\/2016\/09\/GloboTech-Logo.png","type":"image\/png"}],"author":"GloboTech Communications","twitter_misc":{"Written by":"GloboTech Communications","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.globo.tech\/learning-center\/setup-mysql-replication-centos-7\/#article","isPartOf":{"@id":"https:\/\/www.globo.tech\/learning-center\/setup-mysql-replication-centos-7\/"},"author":{"name":"GloboTech Communications","@id":"https:\/\/www.globo.tech\/learning-center\/#\/schema\/person\/e17784b37f4a4f49b7bc611847912e87"},"headline":"How to setup a MySQL Master-Slave Replication on CentOS 7","datePublished":"2016-10-07T19:54:05+00:00","dateModified":"2017-12-12T20:46:13+00:00","mainEntityOfPage":{"@id":"https:\/\/www.globo.tech\/learning-center\/setup-mysql-replication-centos-7\/"},"wordCount":437,"commentCount":0,"articleSection":["Database"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.globo.tech\/learning-center\/setup-mysql-replication-centos-7\/","url":"https:\/\/www.globo.tech\/learning-center\/setup-mysql-replication-centos-7\/","name":"How to setup a MySQL Master-Slave Replication on CentOS 7 - Globo.Tech","isPartOf":{"@id":"https:\/\/www.globo.tech\/learning-center\/#website"},"datePublished":"2016-10-07T19:54:05+00:00","dateModified":"2017-12-12T20:46:13+00:00","author":{"@id":"https:\/\/www.globo.tech\/learning-center\/#\/schema\/person\/e17784b37f4a4f49b7bc611847912e87"},"description":"This tutorial will show you how to setup a MySQL Master-Slave Replication on your CentOS 7 server. Read now & Replicate your database!","breadcrumb":{"@id":"https:\/\/www.globo.tech\/learning-center\/setup-mysql-replication-centos-7\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.globo.tech\/learning-center\/setup-mysql-replication-centos-7\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.globo.tech\/learning-center\/setup-mysql-replication-centos-7\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.globo.tech\/learning-center\/"},{"@type":"ListItem","position":2,"name":"How to setup a MySQL Master-Slave Replication on CentOS 7"}]},{"@type":"WebSite","@id":"https:\/\/www.globo.tech\/learning-center\/#website","url":"https:\/\/www.globo.tech\/learning-center\/","name":"Globo.Tech","description":"Welcome to the Official Globo.Tech Learning Center","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.globo.tech\/learning-center\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.globo.tech\/learning-center\/#\/schema\/person\/e17784b37f4a4f49b7bc611847912e87","name":"GloboTech Communications","sameAs":["http:\/\/www.gtcomm.net"]}]}},"_links":{"self":[{"href":"https:\/\/www.globo.tech\/learning-center\/wp-json\/wp\/v2\/posts\/3089","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.globo.tech\/learning-center\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.globo.tech\/learning-center\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.globo.tech\/learning-center\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.globo.tech\/learning-center\/wp-json\/wp\/v2\/comments?post=3089"}],"version-history":[{"count":2,"href":"https:\/\/www.globo.tech\/learning-center\/wp-json\/wp\/v2\/posts\/3089\/revisions"}],"predecessor-version":[{"id":3987,"href":"https:\/\/www.globo.tech\/learning-center\/wp-json\/wp\/v2\/posts\/3089\/revisions\/3987"}],"wp:attachment":[{"href":"https:\/\/www.globo.tech\/learning-center\/wp-json\/wp\/v2\/media?parent=3089"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.globo.tech\/learning-center\/wp-json\/wp\/v2\/categories?post=3089"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.globo.tech\/learning-center\/wp-json\/wp\/v2\/tags?post=3089"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}