Master-Slave replication

user41094 · 07-04-2014, 07:22 AM

I'm using Master-Slave replication mechanism. I have 2 webservers that have the exact same files. The Master accepts statements like SELECT, INSERT, UPDATE, etc.. from the user. While the Slave is 'read-only', and only accepts statements that don't change the database, such as SELECT.

When the Master goes down, the HAProxy sends the request to the next available server, in this case that server is the Slave.

As I said the slave is 'read-only', so what it should do is send the 'write' statement over to the master and the master write it to its database and only then replicate to the slave.

My problem is that the slave is writing to its database instead of sending it over to the Master.

My friends told me to change the IP address to where I'm connecting in mysql_connect and incase the Master is down the Slave echo's a message saying "Master database unavailable".

How can I do that?

Master: 192.168.20.10 Slave: 192.168.20.40

This is how I'm connecting to the database in both Master and slave:

Code:

<?php
        $conn_error = 'Could not connect.';
        $mysql_db = 'grupo5';

    //host, user, password
        if(!mysql_connect('localhost', 'vitorpnm', '') || !mysql_select_db($mysql_db)) {
        die($conn_error);
    }
?>

TenTenths · 07-11-2014, 02:13 AM

You will need to design your application to cope with a master/slave configuration.

You should define TWO database connections, one for anything that modifies the database and one for read-only.

Specify that the "write" connect always uses 192.168.20.10

Specify that the "read" connect always uses "localhost"

Alternatively you could make use of "circular replication" which would allow you to do writes on both servers.

There are also some proxies (can't remember off the top of my head which ones, but I know certain appliance load balancers like Citrix NetScaler) that can do MySQL query analysis and direct write queries to different server(s) from reads.