mySQL Master Slave

[kylesbigdog]

We have 2 servers at a remote data center with mySQL databases and have another single server locally. So that we can maintain "real time" working copies (back-ups) of the mySQL databases, we would like to configure the remote mySQL instances as masters and have the single server, which can communicate with the remote servers via our WAN, act as a "slave" for both mySQL instances. Is this possible? I have been told that we need 2 separate servers locally to act as slaves and that we cannot have both mySQL slave instances on the same server. Is this correct?


Thanks in advance

[FBI]

Hi,

I have a tough time to understand your question. What i understood from your post is


1st part of the question
==================

"You have a two remote servers and one local servers.

Master -> Remote Servers
Slave -> Local Servers


You want to do master-slave replication.

If this is your question, then the answer is yes. Its possible to configure master-slave replication.

I hope for connecting the remote servers, you need some configurations of VPN or WAN is also a better choice. But i dont have a idea on how to access the same thru WAN.

Here i have a doubt on both mysql instance(does it mean the instances on two remote servers or what do you mean here as both mysql instances)

2nd Part of the question
==================

Please give some more precise notes on your second question

Thanks

[kylesbigdog]

FBI,
Thanks for responding. While I am not the one who configured this, I had been told that you cannot have a single server act as the slave for two mySQL masters. What we are trying to accomplish is to have replication of the mySQL databases currently running on two remote servers, to a single local server.
From a connectivity standpoint, this is already in and working over a WAN connection.

Thanks.

[FBI]

Yes, it is possible to make replication slave for two mysql masters. We can do it by running two mysql instances with different port.

Here

Slave -> single server
Master -> 2 server(remote)

Here is the way to configure the same

Let me explain with example

Remote server1 -> A (master 1)
Remote server2 -> B (master 2)
Local -> C (slave)


Master 1 running in port 3306 and Master 2 also running in the same port (3306)

Create two mysql instances say, mysql_3306, mysql_3307 for slave which is running in port 3306 and 3307 respectively.

Hope you know about configuring the mysql replication.

mysql_3306 instance must be connected with [A] master1
mysql_3307 instance must be connected with [B] master2.

By this way, you can configure the replication for master-master-slave replication

Thanks