Tutorial

How To Set Up MySQL Master-Master Replication

Published on April 26, 2013
author

Jason Kurtz

How To Set Up MySQL Master-Master Replication

Status: Deprecated

This tutorial has been deprecated and is no longer maintained.

Reason: This tutorial covers an outdated method for building a multi-master topology. At the time this article was originally published, group replication had not yet been implemented in MySQL.

See Instead: You can read the newer How To Configure MySQL Group Replication on Ubuntu 16.04 tutorial to set up a multi-primary replication group.

Intro

This second installment of “Scaling Web Applications” will list out the steps necessary for scaling a mysql deployment over two VPS.

The first article in this series laid out the steps needed to load-balance nginx over two VPS, and it is recommended that you read that article first.

MySQL replication is the process by which a single data set, stored in a MySQL database, will be live-copied to a second server. This configuration, called “master-slave” replication, is a typical setup. Our setup will be better than that, because master-master replication allows data to be copied from either server to the other one. This subtle but important difference allows us to perform mysql read or writes from either server. This configuration adds redundancy and increases efficiency when dealing with accessing the data.

The examples in this article will be based on two VPS, named Server C and Server D.

Server C: 3.3.3.3

Server D: 4.4.4.4

Step 1 - Install and Configure MySQL on Server C

The first thing we need to do is to install the mysql-server and mysql-client packages on our server. We can do that by typing the following:

sudo apt-get install mysql-server mysql-client

By default, the mysql process will only accept connections on localhost (127.0.0.1). To change this default behavior and change a few other settings necessary for replication to work properly, we need to edit /etc/mysql/my.cnf on Server C. There are four lines that we need to change, which are currently set to the following:

#server-id              = 1
#log_bin                = /var/log/mysql/mysql-bin.log
#binlog_do_db           = include_database_name
bind-address            = 127.0.0.1

The first of those lines is to uniquely identify our particular server, in our replication configuration. We need to uncomment that line, by removing the “#” before it. The second line indicates the file in which changes to any mysql database or table will be logged.

The third line indicates which databases we want to replicate between our servers. You can add as many databases to this line as you’d like. The article will use a single database named “example” for the purposes of simplicity. And the last line tells our server to accept connections from the internet (by not listening on 127.0.0.1).

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = example
# bind-address            = 127.0.0.1

Now we need to restart mysql:

sudo service mysql restart

We next need to change some command-line settings within our mysql instance. Back at our shell, we can get to our root mysql user by typing the following:

mysql -u root -p 

Please note that the password this command will prompt you for is that of the root mysql user, not the root user on our droplet. To confirm that you are logged in to the mysql shell, the prompt should look like the following.

mysql> 

Once we are logged in, we need to run a few commands.

We need to create a pseudo-user that will be used for replicating data between our two VPS. The examples in this article will assume that you name this user “replicator”. Replace “password” with the password you wish to use for replication.

create user 'replicator'@'%' identified by 'password'; 

Next, we need to give this user permissions to replicate our mysql data:

grant replication slave on *.* to 'replicator'@'%'; 

Permissions for replication cannot, unfortunately, be given on a per-database basis. Our user will only replicate the database(s) that we instruct it to in our config file.

For the final step of the initial Server C configuration, we need to get some information about the current MySQL instance which we will later provide to Server D.

The following command will output a few pieces of important information, which we will need to make note of:

show master status; 

The output will looking similiar to the following, and will have two pieces of critical information:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 | example      |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

We need to make a note of the file and position which will be used in the next step.

Step 2 - Install and Configure MySQL on Server D

We need to repeat the same steps that we followed on Server C. First we need to install it, which we can do with the following command:

sudo apt-get install mysql-server mysql-client

Once the two packages are properly installed, we need to configure it in much the same way as we configured Server C. We will start by editing the /etc/mysql/my.cnf file.

sudo nano /etc/mysql/my.cnf

We need to change the same four lines in the configuration file as we changed earlier.

The defaults are listed below, followed by the changes we need to make.

#server-id              = 1
#log_bin                = /var/log/mysql/mysql-bin.log
#binlog_do_db           = include_database_name
bind-address            = 127.0.0.1

We need to change these four lines to match the lines below. Please note, that unlike Server C, the server-id for Server D cannot be set to 1.

server-id              = 2
log_bin                = /var/log/mysql/mysql-bin.log
binlog_do_db           = example
# bind-address            = 127.0.0.1

After you save and quit that file, you need to restart mysql:

sudo service mysql restart

It is time to go into the mysql shell and set some more configuration options.

mysql -u root -p 

First, just as on Server C, we are going to create the pseudo-user which will be responsible for the replication. Replace “password” with the password you wish to use.

create user 'replicator'@'%' identified by 'password'; 

Next, we need to create the database that we are going to replicate across our VPS.

create database example; 

And we need to give our newly created ‘replication’ user permissions to replicate it.

grant replication slave on *.* to 'replicator'@'%'; 

The next step involves taking the information that we took a note of earlier and applying it to our mysql instance. This will allow replication to begin. The following should be typed at the mysql shell:

slave stop; 
CHANGE MASTER TO MASTER_HOST = '3.3.3.3', MASTER_USER = 'replicator', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 107; 
slave start; 

You need to replace ‘password’ with the password that you have chosen for replication. Your values for MASTER_LOG_FILE and MASTER_LOG_POS may differ than those above. You should copy the values that “SHOW MASTER STATUS” returns on Server C.

The last thing we have to do before we complete the mysql master-master replication is to make note of the master log file and position to use to replicate in the other direction (from Server D to Server C).

We can do that by typing the following:

SHOW MASTER STATUS; 

The output will look similiar to the following:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      107 | example      |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Take note of the file and position, as we will have to enter those on server C, to complete the two-way replication.

The next step will explain how to do that.

Step 3 - Completing Replication on Server C

Back on Server C, we need to finish configuring replication on the command line. Running this command will replicate all data from Server D.

slave stop; 
CHANGE MASTER TO MASTER_HOST = '4.4.4.4', MASTER_USER = 'replicator', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000004', MASTER_LOG_POS = 107; 
slave start; 

Keep in mind that your values may differ from those above. Please also replace the value of MASTER_PASSWORD with the password you created when setting up the replication user.

The output will look similiar to the following:

Query OK, 0 rows affected (0.01 sec)

The last thing to do is to test that replication is working on both VPS. The last step will explain an easy way to test this configuration.

Step 4 - Testing Master-Master Replication

Now that have all the configuration set up, we are going to test it now. To do this, we are going to create a table in our example database on Server C and check on Server D to see if it shows up. Then, we are going to delete it from Server D and make sure it’s no longer showing up on Server C.

We now need to create the database that will be replicated between the servers. We can do that by typing the following at the mysql shell:

create database example; 

Once that’s done, let’s create a dummy table on Server C:

create table example.dummy (`id` varchar(10)); 

We now are going to check Server D to see if our table exists.

show tables in example; 

We should see output similiar to the following:

+-------------------+
| Tables_in_example |
+-------------------+
| dummy             |
+-------------------+
1 row in set (0.00 sec)

The last test to do is to delete our dummy table from Server D. It should also be deleted from Server C.

We can do this by entering the following on Server D:

DROP TABLE dummy; 

To confirm this, running the “show tables” command on Server C will show no tables:

Empty set (0.00 sec)

And there you have it! Working mysql master-master replication. As always, any feedback is more than welcome.

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about our products

About the author(s)

Category:
Tutorial

Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 
144 Comments
Leave a comment...

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

This is excellent, Followed it by example, then set up my master-master-slave from there. Thanks D.O.

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
June 20, 2013

@Marlon: Thanks for the awesome feedback! I’m really glad to hear that this article was helpful to you :]

Karmal, cool!

I hate posting again, but I forgot something. I could be way off, but I think adding the auto_increment and auto_increment_offset directives might be a good idea for master-master, as this could save folks from potential PK conflicts, or users could investigate this for themselves: http://dev.mysql.com/doc/refman/5.7/en/replication-options-master.html#sysvar_auto_increment_increment So on Server C: 2/1 and for server D: 2/2 or something like that.

Correct this doc is useless and I find most of digital ocean documentations are not tested before posting. Just some useless fellow copying from another website and changing few words and posting it here.

How about if you have an existing database and want to set up master-master? What needs to be changed for this scenario?

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
July 3, 2013

@rmang: This article should still work even if you have an already existing mysql setup.

These guys have a really good replication configurator http://www.severalnines.com/

what is the minimal MySQL version that supports this model? 5.0, 5.1, 5.5, 5.6???

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
July 29, 2013

@vguhesan+do: Replication capabilities enabling the databases on one MySQL server to be duplicated on another were introduced in MySQL 3.23.15.

Kamal, great article. Congratulations.

Now I have one scenario on hand. We have 2 servers under one physical load balancer. Its configured using round robin method. Now we know the content / file replication or synchronization wont be an issue but can we have the same database on both dedicated servers ( under same network! ) and do real-time replication using your method or you suggest something else?

We did consider rsync for mysql as well but it can be tricky as the db has to be stopped while replicating and all.

Can you please advice?

Thanks in advance mate.

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
August 28, 2013

@masud: I recommend using one separate database server and having it accessible by both web servers so that they access the exact same data.

This is a really good article what I’m look for. Thanks for share.

Is this article work for two droplets ? I have been trying to configure master master on two droplets with different ips and db servers. But it doesn’t work. All is set and no data is replicated. Any help ?

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
September 17, 2013

@m.shoaib.mir: Check mysql’s binary logs:

<a href=“http://egloo.wordpress.com/2008/11/19/how-to-read-mysql-binary-logs/”>http://egloo.wordpress.com/2008/11/19/how-to-read-mysql-binary-logs/</a> - see if you can find any errors/pointers there.

Hi Kamal,

I am getting the following error in Master Master Replication between two droplets. I have found the error through the command “mysql> show slave status\G;”

Error - error connecting to master ‘replicator@Droplet IP:3306’

It means that Master-Master or Master Slave are not connecting with each other.

Help!!!

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
September 23, 2013

@m.shoaib.mir: Did you follow steps 1 and 2?

@Kamal Naseer Yes, I had followed steps 1 and 2.

Which edition of MYSQL is required for this setup. Does it works in Standard edition?

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
September 25, 2013

@hi.suketu82: It should work in all editions of MySQL.

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
September 25, 2013

@m.shoaib.mir: Are the droplets firewalled? What’s the output of <pre>sudo iptables -L -n -v</pre> on each droplet?

////////////////////////////////////// Droplet 1 \\\\\\\\\\\\\\\\\ Chain INPUT (policy ACCEPT 679 packets, 46247 bytes) pkts bytes target prot opt in out source destination

Chain FORWARD (policy ACCEPT 0 packets, 0 bytes) pkts bytes target prot opt in out source destination

Chain OUTPUT (policy ACCEPT 732 packets, 59559 bytes) pkts bytes target prot opt in out source destination

/////////////////////////////////////////////////// Droplet 1 Ends\\\\\\\\\\\\\\\

//////////////////////////Droplet 2 \\\\\\\\\\\\\\
Chain INPUT (policy ACCEPT 0 packets, 0 bytes) pkts bytes target prot opt in out source destination

Chain FORWARD (policy ACCEPT 0 packets, 0 bytes) pkts bytes target prot opt in out source destination

Chain OUTPUT (policy ACCEPT 0 packets, 0 bytes) pkts bytes target prot opt in out source destination

//////////////////Droplet 2 Ends\\\\\\\\\\\\\\\\\\\

hi, i was setting up this replication, but why on my databse this action was doing replication using method slave-master ?

Can you describe or give some explanation from any logs that can be clue ?

Note: i was already doing all the steps

@ Kamal Nasser Any help :)

Thank you

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
September 27, 2013

@m.shoaib.mir: What’s the output of <pre>grep bind-address /etc/mysql/my.cnf</pre> on each droplet? Also try restarting mysql on both droplets and see if that fixes it: <pre>sudo service mysql restart</pre>

I have the same scenarios as Masud had. But i didnt understand your answer Kamal. 2 webservers 2 sql-servers useing your method and in front 1 loadbalancer with roundroubin for the SQL. Would this be a good soulution to use then to reach a good HA solution for mysql?

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
October 7, 2013

@hedberg: <strong>2 webservers 2 sql-servers</strong>

I’m pretty sure you don’t really need 2 database servers however if you want to go that route you can have webserver 1 connect to database server 1 and webserver 2 connect to database server 2.

Then set up master-master replication between both database servers so that they have the same data.

Why I want that is because of the failover, if one of the hardware crashes i still want the site up and running. But yes that is a smart way of doing it. But what happens if when the one node goes down, then i make the loadbalancer chooses the sql and web that is up ofcourse.

But why isnt it a good ide to make webserver1 write to random sql server?

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
October 9, 2013

@hedberg: It’s not a bad idea but it needs more work, we do not support floating IPs so you cannot use programs such as Heartbeat to use only the database server that is up.

You can also select a random database server in your app’s logic so that it connects to a random database server on e.g. each page load.

i have tried this about 3 times on virtual machines, (clean installs) the “show slave status” appears to have no errors, but it goes off the screen so i can’t be sure. new databases that i create and give the replicator user permission to and add to the /etc/mysql/my.cnf do not get replicated, and in the original database the test table from your guide does not get created. so i don’t think replication is working, i thought it could be a connection issue, but i can connect to both servers using mysql workbench from my computer.

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
November 3, 2013

@mikster22: You can scroll through “show slave status”'s output by running the following command:

<pre>mysql --pager=which less -u root -p -e ‘show slave status’</pre>

Used your tutorial on two VM’s but I wasn’t getting replication until I ran ‘unlock tables;’ dropped the ‘example’ db and recreated it.

Also, I seem to unable to replicate anything other than a DB called ‘example’. Does this have something to do with the bin_log name?

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
November 5, 2013

@lionel: You should edit the <strong>binlog_do_db = example</strong> directive and replace “example” with the databases you want to replicate.

Hi Kamal, Excellent post. I have one doubt : do I need to use auto_increment and auto_increment_offset to do master - master replication? thank you.

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
November 6, 2013

@amos.kanakam: You usually don’t <strong>need</strong> them, however you might have to configure them if you get primary key conflicts however this is usually not needed unless you have a really large amount of rows inserted every second. See <a href=“http://dev.mysql.com/doc/refman/5.0/en/replication-options-master.html”>http://dev.mysql.com/doc/refman/5.0/en/replication-options-master.html</a> for more info on what they do.

Hi Kamal, quick question. I got the replication working great on a Master-Master basis. However, how would I get the DB back in sync if one goes offline for a bit and needs to be updated when it becomes available again? Can this be forced through manually?

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
November 28, 2013

@martin: I believe it does that automatically.

اشكرك جزيل الشكر … لقد استفدت جدا من شرحك المرتب الجميل الخالي من التعقيد وقمت بالفعل بعمل replication master master وهي تعمل بكفاءة وتنقل داتا بالملايين

اشكرك جدا جدا وربنا يبارك فيك

It works well when we have 2 databases, but what should I change when there are >2 databases? For ex. there are 3 databases (A, B, C). When A is changed I expect this change will be replicated to B and C. Is that possible?

same question as piotr.pawlaczek

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
January 4, 2014

@piotr.pawlaczek, @ruoju.liu: I believe setting up master-master replication from server 1 to server 2, and then from server 2 to server 3 should work. So you will have to follow this article once on server 1 and server 2, and once on server 2 and server 3.

Hi , Auto_increment is not working & find my configuration file http://pastebin.com/6bFCksEB

I am running Mysql in Windows. Can I have your advice on how to do a master to master replicatio?

What if the data in one of the Master server gets corrupt, will the corrupt data get copied over to the other master server? If yes, how can this be avoided?

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
January 28, 2014

@suhail: Yes, the corrupt data will be copied over to the other master. You can’t avoid that however you can delay it by setting up <a href=“http://dev.mysql.com/doc/refman/5.6/en/replication-delayed.html”>Delayed Replication</a>.

This doesnt work. I have 2 ubuntu 12.10 servers. Did a lamp install on them. Then went and did all of your steps to the T and it didnt work.

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
February 2, 2014

@casbot: Which part doesn’t work? Are there any errors in the error logs?

nice article… i had been assigned with this task , and i followed ur steps and i completed my task :)

one doubt… can we have multiple masters to be connected to single slave?

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
February 10, 2014

@shnx88: It’s not possible to have multiple masters replicate to a single slave.

yeah that what i read. But if you give “SHOW SLAVE STATUS” command you can see these configuration. Replicate_Ignore_Server_Ids: Master_Server_Id: 1 which is nothing but telling slave to ignore events from configured masters. http://dev.mysql.com/doc/refman/5.6/en/show-slave-status.html

Kamal, I followed the steps exactly and when I went into one of the databases through PHPMyAdmin and changed a value in the table, I waited about 5 minutes and then went to the other database via PHPMyAdmin and checked and the value had not sync´d. I did not see any errors. What do you suggest as this point? I really need this master- master configuration to work as I have a website using the MySql database and then a duplicate website on another machine on the same subnet that I am load balancing and so i need databases to sync when someone changes something on either one so that both sites keep synd´d

By the way, I even tried it by creating a database on one server, the same database on the other server, and then I did an import of a schema.sql to one of the server´s database. The tables that got created on the first server never got replicated on the second server.

Kamal, well i setup two fresh lamp servers on Ubuntu 12.10. I also install PHPMyAdmin and tried throught the gui this time. I was able to get 50% of configured from the Server 1 --> Server 2 and the replicatioin worked fine. Then I set it up for the other way too and then all of it broke unless I manually told it to sync. Now on both servers the Slave SQL Thread is not running and I cant get it to start.

Any suggestions, or should I uninstall MySQL and PHPMyAdmin and then reinstall them and try again via the cli above per your instructions?

Thanks.

Well I sat up until 1am trying this and these instructions DONT WORK. I dont think youre instructions above are for Ubuntu 12.10. The reason I say this is because the command you stated above for the big long “change master to …”, you cant combine all those commands on the same line like that. When I type it in exactly like you have it, the system spits out syntax errors saying its not correct, so I end up having to multiple "change master to " lines.

Now doing it my way through PHPMyAdmin, at least i had replication going one way but then it broke when i configured it to go back the other way. With your instructions, and I have tried them 3 times now, DO NOT WORK at all.

Please create a step by step, and when I say step by step I mean every little detail, on how to do this via PHPMyAdmin because obviously you are not on the same platform as me. And, doing it through the gui it does all the correct cli commands in the background so there are no typos. I need this crap working ASAP. Unfortunately your article is the only one on the internet that I can find. So please revise it to match Ubuntu 12.10 and the default MySql that gets installed on that version.

Thanks.

For some reason this does not work on ubuntu as expected. I have tried with other distro’s without issue, but ubuntu fails.

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
February 24, 2014

@kevin.waterson: Which part doesn’t work for you on Ubuntu?

I have tried it on Ubuntu as well. Replication is not working.

I have 2 droplets, one in NYC and other in AMS. I have followed the tutorial. Replication is not working. One thing I noticed is when I look at the “show master status” the position and file changes quite often. I am not sure about it.

Do I have to open 3306 PORT on iptables?

Thanks

I looked at the slave status on the Droplet 1. This is the message i get.

error connecting to master ‘replicator@xx.xx.xx.xx:3306’ - retry-time: 60 retries: 86400

Is that mean, do I have to allow the access to droplet1 on Droplet2 in iptables? Do we have to do that, since we have removed “bind-address”?

Thanks

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
February 27, 2014

@gopi.te: You have to open up port 3306 on both droplets only to the other mysql server and app droplets. e.g. the first mysql server would only allow the second mysql server and all app servers to connect to port 3306 while the second mysql server would only allow the first mysql server and all app servers to connect.

If 1 server goes down what happen if that server comes up again?

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
March 3, 2014

@wea.along: As far as I know, it will reconnect to the other master and sync the data it missed.

Kamal,

Nice, simple and straight article.

I just followed the steps and I’ve gotten the replication to work.

Now I can go to sleep having replicated between two servers - different providers

Kamal

“I recommend using one separate database server and having it accessible by both web servers so that they access the exact same data.”

Is there a tutorial on this?

Cheers!

It was very helpful. Thank you very much. But why do I have to restart the mysql server every time at server D to see the changes that were done by Server C.

Hi Kamal,

I have setup the master - master replication on CentOS6.4 machine, mysql 5.1.69. Replication is working, but sometimes, the data is not replicating. During this time I never find any errors (show slave status). How to rectify this issue? how to check which queries are replicated and which are not.

Please suggest.

Thank you, Amos

With this master-master, can I also add a master-master+slave approach? How should that be configured? Should both masters sync to the slave or should only one master replicate to the slave? Awesome article BTW!

Hi kamal, I have a question. What happens when Server C and Server D are disconnected and now they are isolated and the database is updated independently at isolated state respectively. What happens when they reconnect ? Will they synchronize with each other when they reconnect?

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
March 15, 2014

@ken: Simply install MySQL on a fresh new droplet (<a href=“https://www.digitalocean.com/community/articles/how-to-secure-mysql-and-mariadb-databases-in-a-linux-vps”>https://www.digitalocean.com/community/articles/how-to-secure-mysql-and-mariadb-databases-in-a-linux-vps</a>) but make sure it’s listening on the private interface: (bind-address = 1.2.3.4 where 1.2.3.4 is your droplet’s private IP address).

Make sure you set up a firewall so that only your app servers can connect to it: <a href=“https://www.digitalocean.com/community/articles/how-to-setup-a-firewall-with-ufw-on-an-ubuntu-and-debian-cloud-server”>https://www.digitalocean.com/community/articles/how-to-setup-a-firewall-with-ufw-on-an-ubuntu-and-debian-cloud-server</a>.

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
March 15, 2014

@Justin: I think you can do that. But you can’t have two masters syncing to one slave. Pick one of the masters and have it replicate to a slave.

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
March 15, 2014

@dbase92000: As far as I know, they will act is if they were separate servers and will sync once they’re reconnected.

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
March 15, 2014

@amos.kanakam: Check MySQL’s error log, are there any errors in there? <a href=“www.cyberciti.biz/faq/debian-ubuntu-linux-mysql-error-log/”>www.cyberciti.biz/faq/debian-ubuntu-linux-mysql-error-log/</a>.

Hi. This is super code and works fine for single DB. Can you tell me how to do this for more than 1 DB binlog_do_db = example here what should i change ??

maybe i can help someone else with their headache. I was stuck on this for 5 hours with constant errors. in particular:

ERROR 1007 (HY000): Can’t create database ‘example’; database exists

and lo and behold i just removed ‘other_database’ from the server that was reporting the error and then the master-master configuration took over.

i rebooted mysql on the error prone server which then took over commands from the other master, resulting in a successful master-master config with a database.

I cant believe it took me that long, i had even created 2 brand new droplets to figure out the problem.

what it came down to is that in the steps their is:

create database example;

its listed twice so i went ahead and did them in order which resulted in the conflicting databases!

hope this helps someone

Hi Kamal, I followed the outlined steps and I’m unable to replicate my database on mysql 5.5. When I checked my slave status I noticed that the “Replicate_Do_DB” is blank.

mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.18.23.52 Master_User: jinny Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 30474 Relay_Log_File: vodjsr1-relay-bin.000003 Relay_Log_Pos: 1086 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 30474 Relay_Log_Space: 30843 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2 1 row in set (0.00 sec)

Which steps should I follow to enable replication Master-Master for a particular database?

Best Regards

Pete

Hello Kamal,

Very GOOD Article.

I setup the Master-Master replication as you described in your tutorial but when 1 server goes down then it will not connect to secondary. Means Failover is not working in my structure.

Here I have one Web application which is on 192.168.1.68 & I am using two database servers for this application

Server 1 : 192.168.1.126 Server 2 : 192.168.1.54

Primarily web application dump the data in 192.168.1.126 but in case this mysql server 1 goes down then it should automatically connected to Server 2 : 192.168.1.54 without noticing to web application user.

Please provide me the solustion.

Waiting for your reply.

Thanks, Suraj

Hi…I’m running Mysql Server 5.5. And i can’t find binlog_do_db

I have my own configuration, and it works. You try this configuration (from fresh my.ini or my.cnf):

log-bin=mysql-bin				# this conf will create mysql-bin.xxxxxx
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1

You can CREATE USER and GRANT like above example. Just make sure you create user at ‘%’. MySQL Replication is log-based replication. When we set log-bin=mysql-bin it means that we activate logging option and the log file is mysql-bin.xxxxxx (xxxxxx is number). CMIIW, everytime we restart the server, the log file are different. Log file mysql-bin.000004 will changed to mysql-bin.000005 after restart. That is why we have to set MASTER_LOG_FILE = ‘mysql-bin.000004’, MASTER_LOG_POS = 107; MASTER_LOG_POS is starting line where Slave should start reading the log file. Any operation logged AFTER 107 will be executed by SLAVE (including CREATE DATABASE). Hope this answer kiran.gkundapur question.

With that configuration we will have bin-log-do-db dan replicate-do-db empty. Actually that option only needed if we replicate specific database.

We can use this configuration both on MASTER or SLAVE to do MASTER to MASTER Replication. Just make sure the server-id on my.cnf or my.ini are different.

@suraj.pune22 both this article and my configuration above won’t work for Failover.

kamal: @wea.along: As far as I know, it will reconnect to the other master and sync the data it missed.

Are you sure? As far as I know, log-bin file will be different every time we restart (stop and then start the server).

Ah…i do replication between windows (xampp) and linux mint (ubuntu based) on VirtualBox. And it’s run well.

I have 2 video made by my student, database level and table level replication configuration, but i’m running slow internet connection now…so…

Would you be able to provide a quick set of steps to add a third master? Mostly to avoid split brain syndrome.

Will it possible that the master replication and slave replication on the same server? I want replication on which the master server id is same as the slave server id.

I am trying the same. Will it work for two different version mysql 5.5 to mysql 5.6 and vice versa? Also what is VPS in our scenario? Can’t it configure on two simple linux machine? Sorry for the question but I am new.

I tried to with 3 MySQL servers(A to B master-master) and then (B to C master-master). Now i created database in A, i could see it in B but could not see it in C. Again, I dropped the database and created the database in B. Now it showed in C. I want to create a database from A and it should be seen in C too, where should i make the change?

when we create a example db. create database example; on C. It will replicate this to slave(D) means slave side the same command run create database example; in background.

So the D as master will send this create database example; to slave C ???

and C will again run the same command create database example; ???

Love you man you ! are awesome and your tutorial is awesome

Hi,

Replication Master Master is OK thanks for Tutorial.

Just a problem with replication after import of a my database.sql. The tables never replicate to the other server. Strange ? If I Create a tables it replicate but not with import. An idea ?

For mysql 5.6 I had to add ‘replicate-same-server-id’ to my.conf. Also, as a heads up, if you add this after going through the tutorial you need to go update the MASTER_LOG_POS and MASTER_LOG_FILE values as they will change after you restart with this new config parameter.

Not sure if this is only for 5.6 and after but I had to use STOP/START SLAVE instead of SLAVE STOP/START.

Hi Marketstem, I use Mysql 5.5 and replicate-same-server-id hasn’t resolved problem. I tryed to put “replicate-same-server-id” on one server then two server but no tables imported with shema.sql replicate. I’ve updated the MASTER_LOG_POS & LOG_FILE but doesn’t work. If i create a table by hand it works. Very Strange !

Finaly it works with replicate-same-server-id. I put in my.cnf in tho server. Then drop the database then create and SRV 1 > The database automaticaly replicate on the second. Then import shema.sql : mysql -u root -p databasename < databashema.sql

Hi,

I setup the Master-Master replication as you described in your tutorial. The issue I am facing is when I do a service mysql restart log file and position are getting changed. After that replication is not working as expected. So I have to run CHANGE MASTER TO MASTER_HOST command again, then everything will work fine. Actually log file and position is getting modified.

Please tell me any solution to keep the log file and position permanently?

Thank you, Umaimath

Andrew SB
DigitalOcean Employee
DigitalOcean Employee badge
June 5, 2014

@umaimath: Thanks for sharing your answer!

This two-way replication solution cannot work because there are potential PK conflicts. You must no implement this architecture.

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
June 6, 2014

@Vicenton: You can add the following lines to my.cnf to make sure that doesn’t happen: Server 1: <pre>auto-increment-increment = 2 auto-increment-offset = 1</pre> Server 2: <pre>auto-increment-increment = 2 auto-increment-offset = 2</pre>

Great tutorial! Got it working second time around (typo I think)

Just a thought on the auto increment though, setting the values above does indeed prevent a collision of primary keys but it has the behaviour of picking up from the current highest id regardless of what server it was produced on and then using its own next available in sequence. For example, if server1 inserts PK 1, 3, 5, 7 & 9, then server2 will insert its first PK as 10, not 2.

As the number of masters increase I can see a lot of wasted PK’s that won’t get used if AUTO_INCREMENT is in place.

Hi Kamal, Well I am stuck with a weird issue. Every time the mysql service is restarted the value of mysql-bin.xxxxxx file gets incremented(eg: mysql-bin.000005 to mysql-bin.000006) and it stops the master - master replication process. How can I prevent the mysql-bin file value rotation or is there any way to deal with this increment issue so that even if the rotation happens the replication wont get affected? Thanks :)

Hi Kamal, my case is abit similar to one already asked above. I just need to be sure you are sure about this. Two isolated servers updated separately. One offline and the other online. When the offline db connects online will it fetch unique data from the online db and will the online db do the same.? Thanks.

umaimath’s comment above is important.

Adding in the three lines below onto both servers my.cnf file is necessary to ensure that the replication continues after log files fill up ( EX: going from mysql-bin.000005 to mysql-bin.000006 ), or after the mysqld process restarts. I also recommend a cronjob to clean up old log files as they are no longer needed after the next log file is created - so your disk space doesn’t fill up.

relay-log = /var/lib/mysql/relay-bin
relay-log-index = /var/lib/mysql/relay-bin.index
relay-log-info-file = /var/lib/mysql/relay-bin.info

I also added the auto-increment and auto-increment-offset lines posted above as they were necessary for my setup.

After making those two changes everything works great for me! I love D.O!

Great!!! I follow all the instructions and working fine. :) But I have one question. if I want to replicate all the databases from master to master instead of one database, how can I do for that? Thanks.

Regards,

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
July 12, 2014

@minkoaung3: I believe you can do that by commenting the binlog_do_db line.

Thanks for the article Kamal.

I think I have solved the issue of replication, but it is failing me when a failover of one of the machines occurs. In that case, the other machine changes occur and when reconnecting the damaged machine the changes are not reflected.

What I can do? What is the problem?

I follwed step by step and worked well for me.

Hello,

I followed step by step and everything went good and replication is being done but when i monitor replication health using “mysqlrpladmin”,it shows healt status message “Binary log and Relay log filters differ.” Please help me to resolve this problem.

Very good article, thank you. What happen if the communication link between Server C and D doesn’t work for a period? Can databases be written independently ? What happen when the link resumes ?

One of the quickest and the useful howto I’ve seen!

This comment has been deleted

    Hi ,

    I followed the same and master master replication is working on the database level. Where as when i use the same with application level ( Joomla ) I am not able to login with users specified in database. I am using two different instances or vm. I am running joomla website on both the vm’s. Database is integrated as master - master replication. Master master replication is working fine. I am only able to login with users created on VM like if i am creating a user on VM1. the user is able to login only on VM1. Where as same user is shown in VM2 but not able to login.

    when i check the logs on VM2 it tells me that user does not exist on database. Where as user is available in both the databases.

    Need help on the same.

    Regards, Vijay

    Hi. The article is very useful and it took me hardly few mins to be able to setup master master replication on my staging servers. I am not aware how do I do the same steps with my production servers. The mysql is running on different port out there and I am not sure what needs to be done. It will be great help if you can give some advice or update the article.

    Thanks and Regards, Makarand Maha.

    The statement “you can add as many databases to this line as you’d like” isn’t correct. See http://dev.mysql.com/doc/refman/4.1/en/replication-options-binary-log.html: "To log multiple databases, use this option multiple times, specifying the option once for each database to be logged. Because database names can contain commas, the list will be treated as the name of a single database if you supply a comma-separated list. "

    Getting this error:

    Error ‘Can’t create database ‘example’; database exists’ on query. Default database: ‘example’. Query: ‘create database example’

    Error 1007

    Tried several different versions of both CentOS and MySQL. Always get this error:

    Last_SQL_Error: Error ‘Can’t create database ‘example’; database exists’ on query. Default database: ‘example’. Query: ‘create database example’

    Please help

    Thank you for this tutorial, very helpful!

    I would definetely setup an auto increment offset for both server to prevent collision.

    For Server 1

    server-id = 1
    auto_increment_increment = 2
    auto_increment_offset = 1
    

    For Server 2

    server-id = 2
    auto_increment_increment = 2
    auto_increment_offset = 2
    

    What happens when one of the two nodes dies. Secondly, how best to auto-failover or recover and rejoin a failed node ?

    slave stop; CHANGE MASTER TO MASTER_HOST = ‘4.4.4.4’, MASTER_USER = ‘replicator’, MASTER_PASSWORD = ‘password’, MASTER_LOG_FILE = ‘mysql-bin.000004’, MASTER_LOG_POS = 107; slave start;

    this code should change to

    stop slave; CHANGE MASTER TO MASTER_HOST = ‘4.4.4.4’, MASTER_USER = ‘replicator’, MASTER_PASSWORD = ‘password’, MASTER_LOG_FILE = ‘mysql-bin.000004’, MASTER_LOG_POS = 107; start slave;

    Would this work with 3 or more servers?

    Does it work for two different instances in different regions in AWS?

    What if the Server D MASTER_LOG_POS keep changing on busy server? What will happen if I enter obsolete position?

    hey i m very new to this whole replication thing…I tried doing it the way its mentioned here but its acting like master slave not master master i.e, i can update server D from server C but the opposite is not possible… can you please help.?

    I did all the steps for 4 times! But my D server became master and my C server became slave… Any idea about fixing it? :(

    What is the difference between this setup vs. Galera cluster? It seems that there is no difference at all, considering the Galera cluster still requires it to be fronted using something like HAProxy and still has the limitation of writing to only 1 node for high write applications. Also, Galera is limited to InnoDB engine only, so in reality, the setup you described above is better. Why would anyone use a clustering platform instead of using mysql’s built-in master / slave capabilities?

    In my version of MySQL I had to run stop slave and start slave instead of slave stop and slave start.

    I am using v. 5.6.19 on Ubuntu 14.04.1.

    This comment has been deleted

      Hi, This is excellent. But I don’t know why it didn’t work for me…

      Hey everyone. I’d thought i share my automated resync mariadb/mysql script.

      It’s possible that the master-master (or master-slave) setup get out of sync. This can happen when, for example, you set back snapshot, or in some really exceptional cases, when two queries are send at the same time to different databases with the same database/table as destination. When a out-of-sync occurs, without you knowing it, it’ll cause a big problem. because from that point on, the servers won’t be synchronising data anymore. And because we have a load-balancer with multiple databases, people will keep sending and storing data in different databases. if you find out, after lets say a week, that you’re databases aren’t in sync anymore, than you’ll have to drop a week of data for one database. To overcome this problem, i wrote a script that checks if the database is in sync every 10 minutes, and if not, automatically synchronizes with the master database.

      #First we grab the master status of server 2(the other server) and the slave state (of this server)
      #----------------------------------------------------------------
      #MAKE SURE YOU EDIT THE *MASTERSERVERIP* TO THE IP OF YOUR SECOND SERVER AND *PASSWORD* TO YOUR MYSQL PASSWORD
      
      MASTER_STATUS=$(ssh root@*MASTERSERVERIP* 'mysql -uroot -p*PASSWORD* -e"SHOW MASTER STATUS;";' | awk '{$1=$1} NR==2')
       
      SLAVE_STATUS=$(mysql -uroot -p'*PASSWORD*' -e "SHOW SLAVE STATUS\G" | awk '{$1=$1} NR==2 || NR==7 || NR==8')
       
      
      #----------------------------------------------------------------
       
      
      #The variable MASTER_STATUS contains the MASTER_LOG_FILE and the MASTER_LOG_POS, if a out-of-sync occurs we want to provide our slave with these values.
      #Since MASTER_STATUS is now containing exactly for example: "mariadb-bin.000073 317858042"
      #We'll make a new variable from this variable, so we have two clean variables containing the log file and position. We'll use these later as variable in our query to update the sync status of the database.
      # the awk '{print $1;} makes sure that we only get the first word (mariadb-bin.000073) and awk '{print $2;} the second word (317858042)
       
      
      MASTER_LOG_FILE=$(echo "$MASTER_STATUS" | awk '{print $1;}')
       
      MASTER_LOG_POS=$(echo "$MASTER_STATUS" | awk '{print $2;}')
      
      
      
      #----------------------------------------------------------------
       
      
      #For our comparisation (the check if everything is running fine) we need to check the SLAVE_IO_STATE.
      #SLAVE_STATUS contains the following 3 lines at the moment:
      #
      #Slave_IO_State: Waiting for master to send event
      #Master_Log_File: mariadb-bin.000073
      #Read_Master_Log_Pos: 317858042
      #
      #We want to extract each part after the ": " part. So we can compare the string "Waiting for master to send event". This can be done with: "awk 'NR==1' | sed -n -e 's/^.*: //p'".
      #We use awk to get a certain line number and sed to print the part after a certain pattern, in this case ": "
      #If the string contains something else, it means that the database is out of sync.
      #The slave master log file and log position can be printed afterward to make sure that the master and slave are in sync now.
       
      
      SLAVE_IO_STATE=$(echo "$SLAVE_STATUS" | awk 'NR==1' | sed -n -e 's/^.*: //p')
      
      
      
      #----------------------------------------------------------------
      #We don't need these variables. Altough they could come in handy for later (for some reason, don't know what yet?) for now i'll coment these two out
      SLAVE_LOG_FILE=$(echo "$SLAVE_STATUS" | awk 'NR==2' | sed -n -e 's/^.*: //p')
      
      
      
      SLAVE_LOG_POS=$(echo "$SLAVE_STATUS" | awk 'NR==3' | sed -n -e 's/^.*: //p')
      #----------------------------------------------------------------
      
      
      
      printf "\n\n\n\n"
      
      
      
      
      #----------------------------------------------------------------
       
      
      #We now have all the information needed to check if the server is still in sync, and if not, sending a query with the right log pos and file.
       
      
      if [ "$SLAVE_IO_STATE" != "Waiting for master to send event" ]
       
      
      then
       
      
      printf "\n\nTHE SLAVE STATE IS: \n"
      echo "$SLAVE_IO_STATE"
       
      
      printf "\n\nTHE MASTER LOG FILE IS: \n"
      echo "$MASTER_LOG_FILE"
       
      
      printf "\n\nTHE SLAVE LOG FILE IS: \n"
      echo "$SLAVE_LOG_FILE"
       
      
      printf "\n\nTHE MASTER LOG POSITION IS: \n"
      echo "$MASTER_LOG_POS"
       
      
      printf "\n\nTHE SLAVE LOG POSITION IS: \n"
      echo "$SLAVE_LOG_POS"
      
      
      
      printf "\n\n\nSERVER IS OUT OF SYNC \nRESYNCING SERVER NOW!!! \nPLEASE  WAIT!!!\n\n\n"
       
      
      mysql -uroot -p'*PASSWORD*' -e "STOP SLAVE;"
      mysql -uroot -p'*PASSWORD*' -e "CHANGE MASTER TO MASTER_LOG_FILE='$MASTER_LOG_FILE', MASTER_LOG_POS=$MASTER_LOG_POS;"
      mysql -uroot -p'*PASSWORD*' -e "START SLAVE;"
      
      
      
      MASTER_STATUS=$(ssh root@*MASTERSERVERIP* 'mysql -uroot -p*PASSWORD* -e"SHOW MASTER STATUS;";' | awk '{$1=$1} NR==2')
      SLAVE_STATUS=$(mysql -uroot -p'*PASSWORD*' -e "SHOW SLAVE STATUS\G" | awk '{$1=$1} NR==2 || NR==7 || NR==8')
      MASTER_LOG_FILE=$(echo "$MASTER_STATUS" | awk '{print $1;}')
      MASTER_LOG_POS=$(echo "$MASTER_STATUS" | awk '{print $2;}')
      SLAVE_IO_STATE=$(echo "$SLAVE_STATUS" | awk 'NR==1' | sed -n -e 's/^.*: //p')
      SLAVE_LOG_FILE=$(echo "$SLAVE_STATUS" | awk 'NR==2' | sed -n -e 's/^.*: //p')
      SLAVE_LOG_POS=$(echo "$SLAVE_STATUS" | awk 'NR==3' | sed -n -e 's/^.*: //p')
       
      
      printf "\n\nTHE SLAVE STATE IS: \n"
      echo "$SLAVE_IO_STATE"
       
      
      printf "\n\nTHE MASTER LOG FILE IS: \n"
      echo "$MASTER_LOG_FILE"
       
      
      printf "\n\nTHE SLAVE LOG FILE IS: \n"
      echo "$SLAVE_LOG_FILE"
       
      
      printf "\n\nTHE MASTER LOG POSITION IS: \n"
      echo "$MASTER_LOG_POS"
       
      
      printf "\n\nTHE SLAVE LOG POSITION IS: \n"
      echo "$SLAVE_LOG_POS"
       
      
      else
       
      
      echo "SERVER IS IN SYNC"
      printf "\n\nTHE SLAVE STATE IS: \n"
      echo "$SLAVE_IO_STATE"
       
      
      printf "\n\nTHE MASTER LOG FILE IS: \n"
      echo "$MASTER_LOG_FILE"
       
      
      printf "\n\nTHE SLAVE LOG FILE IS: \n"
      echo "$SLAVE_LOG_FILE"
       
      
      printf "\n\nTHE MASTER LOG POSITION IS: \n"
      echo "$MASTER_LOG_POS"
       
      
      printf "\n\nTHE SLAVE LOG POSITION IS: \n"
      echo "$SLAVE_LOG_POS"
      fi
      printf "\n\n\n\n"
      

      nice tutorial ! I tried the steps but somehow it doesn’t work… can u tell how to check what went wrong?

      Thanks for the detailed tutorial. I’ve posted my.cnf which required some tweaks as multiple comments below stated. They’re all in one place.

      One question I do have, is the security using this method shown in this tutorial? With grant replication slave on . to ‘replicator’@‘%’; isn’t that open to sniffing? I’m using 2 droplets at different data centers.

      Would it be more prudent to set up an SSH tunnel? Something along the line of this:

      sudo ssh -N -f -L13306:<Droplet A IP>:3306 -R13306:127.0.0.1:3306 user@database-b

      Using mySQL Server 5.5. Here is my.cnf

      #  .....Skipped stuff I didn't change....
      
      # Instead of skip-networking the default is now to listen only on
      # localhost which is more compatible and is not less secure.
      #bind-address		= 127.0.0.1
      #
      
       .....Skipped stuff I didn't change....
      
      # * Logging and Replication
      #
      
       .....Skipped stuff I didn't change....
      
      #
      # The following can be used as easy to replay backup logs or for replication.
      # note: if you are setting up a replication slave, see README.Debian about
      #       other settings you may need to change.
      server-id		= 2   ###NOTE: This setting on the other server Master is 1
      log_bin			= /var/log/mysql/mysql-bin.log
      expire_logs_days	= 10
      max_binlog_size         = 100M
      # I had a few databases I wanted to replicate, put them on their own lines.
      binlog_do_db            = test5
      binlog_do_db            = accessDB
      binlog_do_db            = MyDataDB
      #binlog_ignore_db	= include_database_name
      log_slave_updates   = 1
      log_bin_index       = /var/log/mysql/mysql-bin.log.index
      relay_log           = /var/log/mysql/mysql-relay-bin
      relay_log_index     = /var/log/mysql/mysql-relay-bin.index
      auto-increment-increment = 2 
      auto-increment-offset = 2  ###NOTE: This setting on the other server Master is 1
      replicate-same-server-id = 0
      #
      # * InnoDB 
      
      ...Didn't change anything below this...
      

      This comment has been deleted

        This article is great!! Thank you very much for for sharing your knowledge in excellent way…

        Very good guide. I have followed this doc and setup master-master replication for testing: Master-Master Replication Docker I think it would be worth mentioning about the auto_increment_offset: Auto Increment Offset

        Thanks a lot for this great article… :)

        I want to replicate MySQL server as master-master. I just want them to replicate data not routines,functions…etc just replicate data between them ? Is this replication only sync data or everything routines,functions configurations…?

        Awesome! Super simple and easy. Thank you very much!

        Hi,

        I was wondering if this things works on scenario like this :

        Server A. Table A1 (id,content). it has PK, autoincrement Server B. Table A1. Now, there are 2 apps. Apps1 accessing Server A at the same time Apps2 accessing Server B. Both apps doing an insert. Apps 1 inserts (5,‘x’) and Apps2 inserts (5,‘Y’) Does this kind of edit will complement each other (ie: 5,x and 6,y… maybe) or overwrite one of them (ie: there will be only one ID 5 that has value of ‘x’ or ‘y’) ?

        thanks

        Remember that allowing remote connections is a security vulnerability, they will start brute forcing your root password… you should block external connections on 3306

        I did exactly the same (except for entering server specific values) as it says on the article but it doesn’t seem to be replicating from one server to another.

        Is there any tool or command that we can use to check to test whether the two server are connected and in master, master mode rather than creating a table at one server and waiting it to see in another server ? Thanks.

        Clear and nice, for those whom not yet deal with MySql through the shell, i recommend them to have a test-drive on it. Thank you again.

        If I set this up where server A is an existing production server with a full database, and server B is a brand new server, how can I ensure that server B doesn’t overwrite what’s on server A? I’m worried about wiping out the contents of server A’s database.

        Hello, I built this up for testing some things. However, I’d now like to remove it.

        I have done the following:

        • “STOP SLAVE” on both
        • Commented out replication lines in the config files (/etc/mysql/mysql.conf.d/mysqld.cnf) on both hosts
        • Restarted mysql on both hosts
        • Removed the “replicator” users from MySQL on both hosts

        “SHOW MASTER STATUS” shows “Empty set” on both hosts.

        Is there anything else I need to do to clean this completely?

        Just a note on this. The private IP should be used for C & D as I’m quite sure the internal network is 10GbE or 40GbE (maybe even higher). What slightly worries me is PK collisions, what happens when / if a server goes down etc

        What if i want to go beyond 2 servers only? Let’s say 3 servers cluster?

        I followed the exact steps above but I got the following error one one of my machines (D): [ERROR] Slave SQL: Error ‘Can’t create database ‘example’; database exists’ on query. Default database: ‘example’. Query: ‘create database example’, Error_code: 1007

        The replication works from server D to C but not from server C to D.

        I eventually fixed it by running these mysql tasks on server D: STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE; SHOW SLAVE STATUS \G

        I know this is not ideal so can anyone tell me how I can prevent this error?

        I think it has something to to with the order of creating the example database.

        KFSys
        Site Moderator
        Site Moderator badge
        September 23, 2024

        Yes, the error you encountered (Can't create database 'example'; database exists) is likely due to a replication conflict where the CREATE DATABASE statement is being executed on the replication slave (Server D) even though the database already exists.

        Cause:

        The issue happens when the same CREATE DATABASE query is replicated to another server that already has the database. MySQL replication tries to run the query as part of the binary log, but since the database already exists, it raises an error (Error code 1007).

        Ideal Fix:

        To prevent this from happening in the future, you can take one of the following approaches:

        1. Modify the Database Creation Query: Use CREATE DATABASE IF NOT EXISTS instead of just CREATE DATABASE. This ensures that the database is only created if it doesn’t already exist, avoiding errors during replication.
        CREATE DATABASE IF NOT EXISTS example;
        

        Ignore Duplicate Database Creation Errors: You can instruct MySQL replication to skip certain types of errors. Since error code 1007 is for “database exists”, you can configure MySQL to ignore this specific error during replication by adding this to your my.cnf on Server D:

        slave-skip-errors = 1007
        
        • This will allow replication to continue even if the database already exists.

        • Pre-create the Database on All Servers: Before starting replication, manually create the example database on all servers. This way, the CREATE DATABASE statement will not be replicated, and you’ll avoid the error.

        • Use Row-Based Replication: Switching to row-based replication (RBR) can help minimize such issues, especially when dealing with schema changes. RBR logs the changes at the row level instead of the query level, making replication more robust for certain cases like this.

          You can enable row-based replication by adding this to your my.cnf:

        binlog_format = ROW
        

        Temporary Fix:

        The method you used (STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;) is a quick fix but not ideal, as it skips one event in the replication process, which can lead to missing or incomplete data if used frequently.

        To avoid this, implementing one of the above solutions would be more sustainable in the long run.

        For me the following two lines did not work:

        slave stop; 
        slave start; 
        

        I had to replace them with:

        STOP SLAVE;
        START SLAVE;
        

        Also helpful for me were this line if you want to check if the replication works:

        SHOW SLAVE STATUS \G
        

        Or this command to reset the master parameter:

        RESET SLAVE;
        
        KFSys
        Site Moderator
        Site Moderator badge
        September 23, 2024

        the difference in the commands could indeed be due to different MySQL or MariaDB versions.

        • slave stop; / slave start;: These are shorthand versions of the full SQL commands and may have been available in older or specific MySQL versions but are not always supported or recommended in newer versions.

        • STOP SLAVE; / START SLAVE;: These are the fully qualified SQL commands, which are universally supported across all MySQL and MariaDB versions. They are the recommended syntax, especially in more recent versions.

        Version Differences:

        • In newer versions of MySQL (5.7+ and 8.0+), and in MariaDB, shorthand commands (slave stop and slave start) might not be recognized or supported anymore, which is why you’re required to use the full SQL commands (STOP SLAVE; and START SLAVE;).

        Checking for Replication:

        • SHOW SLAVE STATUS \G works universally for checking replication in both MySQL and MariaDB. However, note that in MySQL 8.0, some commands are deprecated, and SHOW REPLICA STATUS; might be preferred moving forward as “replica” is replacing “slave” in terminology.

        Resetting Replication:

        • RESET SLAVE; also works universally but be cautious: this command deletes replication settings like binary log positions. In some cases, you may want to use RESET SLAVE ALL; to reset replication entirely, especially if you’re reconfiguring the replication from scratch.

        So yes, these differences are often due to changes between MySQL versions and the evolution of replication commands.

        Kamal hello, Thank you for the article. We are currently running MySQL master-master replication in test environment and noticed that it breaks quite often. The replication can break because of multiple things like locks on selects, the “fast transactions” like insert-update.

        The failure rate can be increased when you are doing multi-hosting replication where number of masters is greater than two.

        What will be your recommendations or maybe DigitalOceans’ special treatment for multi-master replication? Thank you, Yuri.

        **Caution: **Useless document don’t use this document to setup master-master replication you will end up crashing your replication.

        hi,

        if we have 3 x nodes what should I do for the third server for this:

        slave stop; CHANGE MASTER TO MASTER_HOST = ‘4.4.4.4’, MASTER_USER = ‘replicator’, MASTER_PASSWORD = ‘password’, MASTER_LOG_FILE = ‘mysql-bin.000004’, MASTER_LOG_POS = 107; slave start;

        2 server is straight forward, but adding one more is a mix up.

        KFSys
        Site Moderator
        Site Moderator badge
        September 23, 2024

        For a MySQL master-master-master setup with 3 servers, here are the key differences from a standard 2-server master-master setup:

        1. Replication Configuration:

          • Each server (A, B, and C) will need to replicate from both of the other two servers. This requires setting up circular replication where:
            • Server A replicates from Server B.
            • Server B replicates from Server C.
            • Server C replicates from Server A.
        2. Unique Server IDs:

          • Ensure each server has a unique server_id in the MySQL configuration (my.cnf), as follows:
            • Server A: server_id=1
            • Server B: server_id=2
            • Server C: server_id=3
        3. Binary Log Configuration:

          • Enable binary logging on all servers (log_bin) with different names for easier identification (e.g., log_bin=A-bin, log_bin=B-bin, log_bin=C-bin).
        4. Change Master Setup:

          • Each server needs a CHANGE MASTER TO configuration pointing to two other servers. On Server A:
        CHANGE MASTER TO MASTER_HOST='B', MASTER_LOG_FILE='B-bin.000001', MASTER_LOG_POS=position;
        CHANGE MASTER TO MASTER_HOST='C', MASTER_LOG_FILE='C-bin.000001', MASTER_LOG_POS=position;
        
          • Apply the equivalent on Servers B and C.
        1. Avoid Write Conflicts:

          • For a 3-way setup, be cautious about potential write conflicts. Consider using row-based replication (binlog_format=ROW) to minimize conflicts.
        2. Auto-Increment Offset:

          • Configure the auto_increment_increment and auto_increment_offset variables to prevent auto-increment collisions:
            • On Server A: auto_increment_offset=1, auto_increment_increment=3
            • On Server B: auto_increment_offset=2, auto_increment_increment=3
            • On Server C: auto_increment_offset=3, auto_increment_increment=3

        These are the main adjustments needed to extend a 2-node master-master setup into a 3-node configuration. Let me know if you need further clarification on any specific part!

        thanks dude. but there is a problem. i followed you tutorial but the end result is not the same. its like master-slave configuration. i can add tables from server 2nd and it will exist in server1 but not the way around. please help thanks.

        This is good post. but dear replication failed (NOT Working) in both the server device restarted at same time. How to we handle it ? please guide on it ! Thanks.

        Try DigitalOcean for free

        Click below to sign up and get $200 of credit to try our products over 60 days!

        Sign up

        Join the Tech Talk
        Success! Thank you! Please check your email for further details.

        Please complete your information!

        Become a contributor for community

        Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

        DigitalOcean Documentation

        Full documentation for every DigitalOcean product.

        Resources for startups and SMBs

        The Wave has everything you need to know about building a business, from raising funding to marketing your product.

        Get our newsletter

        Stay up to date by signing up for DigitalOcean’s Infrastructure as a Newsletter.

        New accounts only. By submitting your email you agree to our Privacy Policy

        The developer cloud

        Scale up as you grow — whether you're running one virtual machine or ten thousand.

        Get started for free

        Sign up and get $200 in credit for your first 60 days with DigitalOcean.*

        *This promotional offer applies to new accounts only.