Tutorial
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.
About the author(s)
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!
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.
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.
This is excellent, Followed it by example, then set up my master-master-slave from there. Thanks D.O.
@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?
@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???
@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.
@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 ?
@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!!!
@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?
@hi.suketu82: It should work in all editions of MySQL.
@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
@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?
@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?
@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.
@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?
@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.
@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?
@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
@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?
@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.
@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?
@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.
@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
@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?
@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?
@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>.
@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.
@dbase92000: As far as I know, they will act is if they were separate servers and will sync once they’re reconnected.
@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):
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
I found the solution, it works great :)
http://forums.mysql.com/read.php?26,213270,216664#msg-216664
@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.
@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.
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,
@minkoaung3: I believe you can do that by commenting the
binlog_do_dbline.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
For Server 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 slaveandstart slaveinstead ofslave stopandslave 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.
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
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:
“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.
Yes, the error you encountered (
Can't create database 'example'; database exists) is likely due to a replication conflict where theCREATE DATABASEstatement is being executed on the replication slave (Server D) even though the database already exists.Cause:
The issue happens when the same
CREATE DATABASEquery 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:
CREATE DATABASE IF NOT EXISTSinstead of justCREATE DATABASE. This ensures that the database is only created if it doesn’t already exist, avoiding errors during replication.Ignore Duplicate Database Creation Errors: You can instruct MySQL replication to skip certain types of errors. Since error code
1007is for “database exists”, you can configure MySQL to ignore this specific error during replication by adding this to yourmy.cnfon Server D: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
exampledatabase on all servers. This way, theCREATE DATABASEstatement 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: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:
I had to replace them with:
Also helpful for me were this line if you want to check if the replication works:
Or this command to reset the master parameter:
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:
slave stopandslave start) might not be recognized or supported anymore, which is why you’re required to use the full SQL commands (STOP SLAVE;andSTART SLAVE;).Checking for Replication:
SHOW SLAVE STATUS \Gworks universally for checking replication in both MySQL and MariaDB. However, note that in MySQL 8.0, some commands are deprecated, andSHOW 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 useRESET 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.
For a MySQL master-master-master setup with 3 servers, here are the key differences from a standard 2-server master-master setup:
Replication Configuration:
Unique Server IDs:
server_idin the MySQL configuration (my.cnf), as follows:server_id=1server_id=2server_id=3Binary Log Configuration:
log_bin) with different names for easier identification (e.g.,log_bin=A-bin,log_bin=B-bin,log_bin=C-bin).Change Master Setup:
CHANGE MASTER TOconfiguration pointing to two other servers. On Server A:Avoid Write Conflicts:
binlog_format=ROW) to minimize conflicts.Auto-Increment Offset:
auto_increment_incrementandauto_increment_offsetvariables to prevent auto-increment collisions:auto_increment_offset=1,auto_increment_increment=3auto_increment_offset=2,auto_increment_increment=3auto_increment_offset=3,auto_increment_increment=3These 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.