April 26, 2013

Beginner

How To Set Up MySQL Master-Master Replication

Tagged In: Backups, Ubuntu, My Sql

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.

Share this Tutorial

Vote on Hacker News

Try this tutorial on an SSD cloud server.

Includes 512MB RAM, 20GB SSD Disk, and 1TB Transfer for $5/mo! Learn more

Create an account or login:

78 Comments

Write Tutorial
  • Gravatar Marlon 10 months

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

  • Gravatar Kamal Nasser 10 months

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

  • Gravatar Marlon 10 months

    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.

  • Gravatar rmang 10 months

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

  • Gravatar Kamal Nasser 10 months

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

  • Gravatar Dave Ganley 10 months

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

  • Gravatar vguhesan+do 9 months

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

  • Gravatar Kamal Nasser 9 months

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

  • Gravatar masud 8 months

    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.

  • Gravatar Kamal Nasser 8 months

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

  • Gravatar Sarayut Utsakoo 7 months

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

  • Gravatar m.shoaib.mir 7 months

    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 ?

  • Gravatar Kamal Nasser 7 months

    @m.shoaib.mir: Check mysql's binary logs: http://egloo.wordpress.com/2008/11/19/how-to-read-mysql-binary-logs/ - see if you can find any errors/pointers there.

  • Gravatar m.shoaib.mir 7 months

    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!!!

  • Gravatar Kamal Nasser 7 months

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

  • Gravatar m.shoaib.mir 7 months

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

  • Gravatar hi.suketu82 7 months

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

  • Gravatar Kamal Nasser 7 months

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

  • Gravatar Kamal Nasser 7 months

    @m.shoaib.mir: Are the droplets firewalled? What's the output of

    sudo iptables -L -n -v
    on each droplet?

  • Gravatar m.shoaib.mir 7 months

    ////////////////////////////////////// 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\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

  • Gravatar m.shoaib.mir 7 months

    @ Kamal Nasser Any help :) Thank you

  • Gravatar Kamal Nasser 7 months

    @m.shoaib.mir: What's the output of

    grep bind-address /etc/mysql/my.cnf
    on each droplet? Also try restarting mysql on both droplets and see if that fixes it:
    sudo service mysql restart

  • Gravatar hedberg 6 months

    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?

  • Gravatar Kamal Nasser 6 months

    @hedberg: 2 webservers 2 sql-servers 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.

  • Gravatar hedberg 6 months

    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?

  • Gravatar Kamal Nasser 6 months

    @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.

  • Gravatar mikster22 6 months

    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.

  • Gravatar Kamal Nasser 6 months

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

    mysql --pager=`which less` -u root -p -e 'show slave status'

  • Gravatar lionel 5 months

    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?

  • Gravatar Kamal Nasser 5 months

    @lionel: You should edit the binlog_do_db = example directive and replace "example" with the databases you want to replicate.

  • Gravatar amos.kanakam 5 months

    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.

  • Gravatar Kamal Nasser 5 months

    @amos.kanakam: You usually don't need 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 http://dev.mysql.com/doc/refman/5.0/en/replication-options-master.html for more info on what they do.

  • Gravatar martin 5 months

    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?

  • Gravatar Kamal Nasser 5 months

    @martin: I believe it does that automatically.

  • Gravatar el3raf6 4 months

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

  • Gravatar piotr.pawlaczek 4 months

    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?

  • Gravatar ruoju.liu 4 months

    same question as piotr.pawlaczek

  • Gravatar Kamal Nasser 3 months

    @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.

  • Gravatar Krishna 3 months

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

  • Gravatar arthurzheng 3 months

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

  • Gravatar suhail 3 months

    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?

  • Gravatar Kamal Nasser 3 months

    @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 Delayed Replication.

  • Gravatar casbot 3 months

    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.

  • Gravatar Kamal Nasser 3 months

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

  • Gravatar shnx88 2 months

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

  • Gravatar shnx88 2 months

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

  • Gravatar Kamal Nasser 2 months

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

  • Gravatar shnx88 2 months

    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

  • Gravatar casbot 2 months

    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

  • Gravatar casbot 2 months

    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.

  • Gravatar casbot 2 months

    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.

  • Gravatar casbot 2 months

    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.

  • Gravatar kevin.waterson about 1 month

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

  • Gravatar Kamal Nasser about 1 month

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

  • Gravatar gopi.te about 1 month

    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

  • Gravatar gopi.te about 1 month

    I looked at the slave status on the Droplet 1. This is the message i get. error connecting to master '[email protected]: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

  • Gravatar Kamal Nasser about 1 month

    @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.

  • Gravatar wea.along about 1 month

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

  • Gravatar Kamal Nasser about 1 month

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

  • Gravatar info about 1 month

    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

  • Gravatar ken about 1 month

    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!

  • Gravatar dbase92000 about 1 month

    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.

  • Gravatar amos.kanakam about 1 month

    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

  • Gravatar Justin L. about 1 month

    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!

  • Gravatar dbase92000 about 1 month

    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?

  • Gravatar Kamal Nasser about 1 month

    @ken: Simply install MySQL on a fresh new droplet (https://www.digitalocean.com/community/articles/how-to-secure-mysql-and-mariadb-databases-in-a-linux-vps) 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: https://www.digitalocean.com/community/articles/how-to-setup-a-firewall-with-ufw-on-an-ubuntu-and-debian-cloud-server.

  • Gravatar Kamal Nasser about 1 month

    @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.

  • Gravatar Kamal Nasser about 1 month

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

  • Gravatar Kamal Nasser about 1 month

    @amos.kanakam: Check MySQL's error log, are there any errors in there? www.cyberciti.biz/faq/debian-ubuntu-linux-mysql-error-log/.

  • Gravatar kiran.gkundapur about 1 month

    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 ??

  • Gravatar jrdubstep 23 days

    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

  • Gravatar anomalieh 22 days

    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

  • Gravatar suraj.pune22 22 days

    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

  • Gravatar wbhuana 21 days

    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.

  • Gravatar wbhuana 21 days

    @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).

  • Gravatar wbhuana 21 days

    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...

  • Gravatar cbluth 16 days

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

  • Gravatar parsaniyaparth 11 days

    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.

Leave a Comment

Create an account or login:
Ajax-loader