How To Set Up Replication in MySQL

A few suggestions I would like to make in regards to this article:

1. I think it should be mentioned that MySQL has the capabilities to bind to multiple IP addresses and that to take advantage of this feature, an additional “bind-address” line is required.

2. In the following section (regarding server-ids) you mentioned that this line has to be “uncommented” but I’m not sure all users will know that means that they have to remove the “#” preceding it if there is one (which there will be in the installed version of my.cnf.

3. In the section regarding the databases that they want to replicate, I believe it should be mentioned that leaving this line commented will result in all databases being replicated. In some, though admittedly rather rare situations, this is the desired behavior. This, too, will allow new databases to be replicated automatically without any additionally editing of the configuration files.

4. Some of the wording in the explanations is a bit awkward, specifically: “This is the position from which the slave database will start replicating. Record these numbers, they will come in useful later.” This might be more grammatically correct if stated as “This is the file name and position that the master server is using for logging purposes. Make a note of these, as you will need to enter them later”.

5. After the mysqldump step, there should be an additional step explaining how to send the newly created .sql file to the remote host. SCP would be the easiest way, in my opinion, to accomplish this.

Well, i just followed the steps as mentioned and everything went fine. But the changes in Master DB did not reflect in slave DB In my case, Master DB & Slave DB resides on two different droplets. Where could i have gone wrong ?

Nice post, works great. It would be nice if you could extend this article to include how one would add additional slave servers to the setup, since most people following this would want to scale someday.

This is a good link to add yet another slave and from my experience it works great. http://www.redips.net/mysql/add-new-slave/

As a backup I would not recommended this approach. If data is corrupted, lost or deleted in the master that will be synced to the slave. Restoring the slave to the master does not get you your data back.

What I miss is the explanation how to use the slave for failover. If somebody could add that that would be helpful (at least for me)

Ruter

@Ruter

Good point. What do you suggest?

Thanks, Mike

Great article !! Just one comment from my experience : Use short short passwords to the replica user :) password like bKpGpJIQEm1KHhbEuf6zueTBvfW84mI6XYCcxaS2 WON’T work !! half of it works well, took me some hours checking everything before find it.

Good starter, but I wonder: why are you running bin-logs on the slave?

How often slave updated from master? Is it configurable?

@Sahaya: I believe it’s instant by default. It is configurable: http://alexalexander.blogspot.com/2013/03/mysql-slave-delay-how-to.html