PHPBuilder - Using MySQL's Built-In Replication Page 3



RSS Twitter
Articles Databases

Using MySQL's Built-In Replication - Page 3

by: Michael Tanoviceanu
|
September 12, 2000

Step Three: Create A Mutual Master-Slave Relationship

First go ahead and add 'log-bin' to the [mysqld] portion the my.cnf file on B and restart mysqld, then create the account for the replication user on it with:
GRANT FILE ON *.* TO replicate@10.1.1.1 IDENTIFIED BY 'password';
Go ahead and run a 'FLUSH PRIVILEGES' on B to load the new grant tables after adding the replication user, and go back to server A, and add the following to it's 'my.cnf' file:
master-host=10.1.1.2
master-user=replicate
master-password=password
After restarting server A, we now have a mutual master-slave relationship between A and B. A record that is updated or inserted on either server will be replicated to the other. A word of caution: I'm not sure just how fast a slave incorporates bin-log changes, so it might not be good idea to load-balance your insert or update statements to codevent any corruption of your data.
Step Four: Alter Your Database Connection Routine
Now that you have established a mutual relationship between servers A and B, you need to alter your database connection routines to take advantage of this. The function below first attempts to connect to server A, and then to server B if no connection can be made.

<?php

/********************************************************
function db_connect()

returns a link identifier on success, or false on error
********************************************************/
function db_connect(){
$username "replUser";
$password "password";
$primary "10.1.1.1";
$backup "10.1.1.2";

# attempt connection to primary
if(!$link_id = @mysql_connect($primary$username$password))
# attempt connection to secondary
$link_id = @mysql_connect($secondary$username$password)
return 
$link_id;
}

?>
I tested my setup using the above technique under two conditions, with the primary MySQL server shutdown, but otherwise the server was running, and with the primary server shutdown. Connections were made to the backup immediately if just mysqld was shut down on the primary, but if the entire server was shutdown, there was an indefinite hang (I lost track after two minutes - short attention span) while PHP was looking for a non-existent server. Unfortunately, the mysql_connect function does not have any time-out parameters like fsockopen, however we can use fsockopen to fake a timeout for us.

« Previous Page
1
|
2
|
3
|
4
Next Page »

Comment and Contribute

Your comment has been submitted and is pending approval.

Author:
Michael Tanoviceanu

Comment:



Comment:

(Maximum characters: 1200). You have characters left.