{"id":561,"date":"2022-05-26T06:31:51","date_gmt":"2022-05-26T06:31:51","guid":{"rendered":"https:\/\/www.checkmateq.com\/blog\/?p=561"},"modified":"2023-08-03T07:30:47","modified_gmt":"2023-08-03T07:30:47","slug":"master-master-replication-of-mysql","status":"publish","type":"post","link":"https:\/\/www.checkmateq.com\/blog\/master-master-replication-of-mysql","title":{"rendered":"How to Configure Master Master Replication of MySQL"},"content":{"rendered":"

What is MySQL?<\/strong><\/p>\n

MySQL<\/a> is one of the most used relational database management system(dbms)\u00a0 based on SQL, developed and distributed by Oracle corporation. it’s a relational database stores data in tables in which data in tables may be related to each other these relations between tables helps to structure the data and preform operations like Create , Read, Update and Delete famously known as CRUD operations.<\/p>\n

What is database replication?<\/strong><\/p>\n

database replication is way of copying or storing data\u00a0 at more than\u00a0 one server\/node, it helps to increase\u00a0 availability of data, replication is a simple process of copying data from one server to other so that all the users are enabled to access same data at a particular point of time without any inconsistency, by default all the MySQL replications are asynchronous which means data is written to primary storage first and then copied on to the secondary in contrast data is written to both the servers simultaneously in synchronous replication.<\/p>\n

Master Master replication:<\/strong><\/p>\n

To understand what is master master you have to know what is master node and what is a slave node,\u00a0 \u00a0 \u00a0a users can both read and write data to a master node where as all the data written to master is copied to slave node which acts as read only server to users, in master master replication there will be more than one master node\/server ,here\u00a0 a two way communication is established between all the master nodes where data written to any of the master node is copied to\u00a0 all other master nodes and slave nodes, each master node can have slave nodes separately, this increases the availability\u00a0 of\u00a0 database for users to\u00a0 both read and write data .<\/p>\n

Steps to setup Master Master replication<\/strong><\/p>\n

I am using two aws\u00a0 ec2 instances with RHEL platform and assume these are Private IPv4 addresses\u00a0 of the instances.<\/p>\n

Instance-1:\u00a0 172.31.43.199\r\nInstance-2: 172.31.33.123\r\n<\/pre>\n
Note: if you are also using instances hosted on any cloud provider please allow traffic on port number 3306 (mysql mostly uses this port number)<\/p>\n
Step-1:\u00a0 Install MySQL on both the machines<\/strong><\/h4>\n
follow all the below process for both the machines, let’s start with installing mysql-server on both the machines<\/p>\n
execute this command on both your terminals to install mysql-server.<\/p>\n
sudo yum install mysql-server -y<\/pre>\n
\"\"<\/p>\n
after the installation start the database server<\/p>\n
systemctl start mysqld<\/pre>\n
enable the service, enabling\u00a0 makes it to automatically start on boot.<\/p>\n
systemctl enable mysqld<\/pre>\n
\"\"<\/p>\n
execute the given command to check the the status of MySQL server and confirm that it’s running<\/strong><\/p>\n
systemctl status mysqld<\/pre>\n
\"\"<\/p>\n
In step-2 we will configure server-1(machine-1) as master and server-2 (machine-2) as a slave for it.<\/p>\n
Step-2: <\/strong>Configuring sever-1 as master node and server-2 as slave node.<\/strong><\/h4>\n
edit the mysql-server.cnf file on your machine-1<\/p>\n
sudo vi \/etc\/my.cnf.d\/mysql-server.cnf<\/pre>\n
add these under [mysqld] in your configuration file , save it and close.<\/p>\n
bind-address = 172.31.43.199(use your machine-1 private ip here)\r\nserver-id = 1\r\nlog_bin = mysql-bin\r\n<\/pre>\n
\"\"<\/p>\n
bind_address<\/strong>: tells MySQL on which network it can listen for connections, if you give 0.0.0.0 that means you are allowing access from any ip.<\/p>\n
server-id<\/strong>:\u00a0 server id’s should be unique for every node\/server.<\/p>\n
log_bin<\/strong>: binary log is a sequence of files with a base name and numeric extension, server logs all statements that change data to the binary log, which is used for backup and replication.<\/p>\n
Now restart your mysql-server using below command.<\/p>\n
sudo systemctl restart mysqld<\/pre>\n
Now use below command and login to your mysql-shell<\/p>\n
sudo mysql -u root -p<\/pre>\n
execute the below given commands in you MySQL shell , these commands will create a user and will grant the permission “REPLICATION SLAVE” to the created user on all the tables in all the databases.<\/p>\n
mysql> CREATE USER 'aks'@'172.31.33.123' IDENTIFIED BY '@Aksshaay1'; \r\nmysql> GRANT REPLICATION SLAVE ON *.*TO 'aks'@'172.31.33.123';<\/pre>\n
Here “aks” is a user name , “172.31.33.123” is private-ip of my server-2 and “@Aksshaay1” is password for the user, “REPLICATION SLAVE” is a permission type given to user “aks” on all the databases and all tables(*.*)<\/p>\n
then run below command\u00a0 to load the changes and exit.<\/p>\n
mysql> FLUSH PRIVILEGES;<\/pre>\n
FLUSH PRIVILEGES will reload grant tables in mysql database , which makes changes to take effect immediately without\u00a0 restarting or reloading MySQL.<\/p>\n
mysql> EXIT;<\/pre>\n
\"\"<\/p>\n
now use below command to verify the status of master , copy file name and position.<\/p>\n
SHOW MASTER STATUS\\G<\/pre>\n
\"\"<\/p>\n
Configure server-2 as a slave to server-1: <\/strong><\/p>\n
edit the configuration file “mysql-server.cnf”.<\/p>\n
sudo vi \/etc\/my.cnf.d\/mysql-server.cnf<\/pre>\n
add these under [mysqld] inyour configuration file.<\/p>\n
bind-address=172.31.33.123(give your 2nd machine private ip here)\r\nserver-id=2\r\nlog_bin=mysql-bin\r\n<\/pre>\n
save the file and restart mysql-server<\/p>\n
systemctl restart mysqld<\/pre>\n
login to your mysql shell to configure it as a slave for server-1<\/p>\n
sudo mysql -u root -p<\/pre>\n
use below command to stop replication threads.<\/p>\n
mysql> STOP SLAVE;<\/pre>\n
\"\"<\/p>\n
now run below command to set your server-2 to replicate data from master(server-1)<\/p>\n
mysql> CHANGE MASTER TO\r\n-> MASTER_HOST='172.31.43.199',\r\n-> MASTER_USER='aks',\r\n-> MASTER_PASSWORD='@Aksshaay1',\r\n-> MASTER_LOG_FILE='musql-bin.000005',\r\n-> MASTER_LOG_POS=1481;\r\n<\/pre>\n
\"\"<\/p>\n
then execute below command to start replication threads.<\/p>\n
mysql> START SLAVE;<\/pre>\n
\"\"<\/p>\n
as of now we have created a one way communication from server-1(as master ) to server 2(as slave), now lets check our configuration.<\/p>\n
on your server-1 use below command to create a database,<\/p>\n
mysql>CREATE DATABASE dtabase_aks;<\/pre>\n
database_aks is the name of database iam creating, and use below command to check weather database is created or not.<\/p>\n
mysql>SHOW DATABASES;<\/pre>\n
and now logon to your server-2 and use command SHOW DATABASES to check for replication, in the snapshot below left side is my server-1(master) , I created a database ‘databses_aksshaay’ and checking for replication of same in server-2 on the right side.<\/p>\n
\"\"<\/p>\n
our configuration of server-1 as master ans server-2 as slave is done now let’s configure server-2 as master and server-1 as slave so that it will be a two way communication.<\/p>\n
STEP-3: Configure Server-2 as Master\u00a0 and Server-1 as slave.\u00a0<\/strong><\/h4>\n
login to mysql shell of server-2<\/p>\n
sudo mysql -u root -p<\/p>\n
execute below commands to create user and give replication permission.<\/p>\n
mysql> CREATE USER 'ajay'@'172.31.43.199' IDENTIFIED BY '@Password1';\r\nGRANT REPLICATION SLAVE ON *.*TO 'ajay'@'172.31.43.199';<\/pre>\n
here ‘ajay’ is a user ‘172.31.43.199’ is private-ip of my server-1 and \u00a0use below given command\u00a0 to reload the grant table<\/p>\n
mysql> FLUSH PRIVILEGES;<\/pre>\n
execute below command and copy file name and position number which we will use later.<\/p>\n
mysql> SHOW MASTER STATUS\\G<\/pre>\n
\"\"<\/p>\n
copy file name and position number.<\/p>\n
mysql> EXIT;<\/pre>\n
exit from your shell .<\/p>\n
Configure server-1 as a slave to server-2:<\/strong><\/p>\n
login to your to mysql shell of server-1 and configure master details.<\/p>\n
sudo mysql -u root -p<\/pre>\n
use below command to stop replication threads.<\/p>\n
mysql> STOP SLAVE;<\/pre>\n
\"\"<\/p>\n
then execute below command to set master ,please use your server-2 private ip here, for user and password use from user created on server-2 and for file name and position number use the detail you copied from SHOW MASTER STATUS command on server-2.<\/p>\n
mysql> CHANGE MASTER TO\r\n-> MASTER_HOST='172.31.33.123',\r\n-> MASTER_USER='ajay',\r\n-> MASTER_PASSWORD='@Password1',\r\n-> MASTER_LOG_FILE='mysql-bin.000005',\r\n-> MASTER_LOG_POS=323;\r\n<\/pre>\n
\"\"<\/p>\n
use below command to start replication threads.<\/p>\n
mysql>START SLAVE;<\/pre>\n
\"\"<\/p>\n
Yes that’s it you have configured a master master replication ,Lets test it now I am going to create\u00a0 a database named database_s1_s2 on server-1 which will be replicated on server-1\u00a0 and a database named database_s2_s1 on server-2 which will be replicated on to server-1.<\/p>\n
use below commands<\/p>\n
To create data base , give your database name in the place of database-name.<\/p>\n
CREATE DATABASE\u00a0 database-name;<\/pre>\n
To see databases<\/p>\n
SHOW DATABASES;<\/pre>\n
My server-1 is on the left side and server-2 on the right ,first I am creating a database on server-1 and checking it on server-2\u00a0 then creating a database on server-2 and checking it on server-1.<\/p>\n
\"\"\u00a0 \u00a0 \u00a0 \"\"<\/p>\n
 <\/p>\n
If you need an offshore cloud computing solution or a dedicated DevOps engineer to assist your production environment, please contact us<\/a>. Our skilled offshore engineers are ready to take on any issues that arise in order to keep production running smoothly.<\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
What is MySQL? MySQL is one of the most used relational database management system(dbms)\u00a0 based on SQL, developed and distributed by Oracle corporation. it’s a relational database stores data in tables in which data in tables may be related to each other these relations between tables helps to structure the data and preform operations like … Continue reading “How to Configure Master Master Replication of MySQL”<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":655,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[41,39,40,38],"_links":{"self":[{"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/posts\/561"}],"collection":[{"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/comments?post=561"}],"version-history":[{"count":59,"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/posts\/561\/revisions"}],"predecessor-version":[{"id":4191,"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/posts\/561\/revisions\/4191"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/media\/655"}],"wp:attachment":[{"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/media?parent=561"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/categories?post=561"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/tags?post=561"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}