For something like this, you may want to think about a unique architecture known as a Star Topology.
Picture the following:
- Server HQDB with 25 databases (1 for each branch)
- Server BranchDB001 with database `branch001``
- Server BranchDB002 with database
branch002
- ...
- Server BranchDB025 with database
branch025
Setup Data for HQDB
- On HQDB, create the 25 databases
branch001 through branch025
- Load data for each branch into their respective databases
Setup each Branch
- Load HQDB's
branch001 into branch001 on Server BranchDB001
- mysqldump branch001 database on HQDB into a script
- Run that script in
branch001 on Server BranchDB001
- Repeat this for all Branch Databases
Setup Replication
Here is the tricky part. You want to make the HQDB the Slave of Every Branch. Unfortunately, MySQL Replication is inherently designed to be a Slave reading from one Master. (NOTE: MultiMaster (MultiSource) Replication is being implemented in the soon-to-be-released MariaDB 10.0. Since I am a MySQL DBA, I leave that to more adventurous DBAs. I need to more free time to learn MariaDB.)
Getting on with MySQL Replication, the idea is
RESET MASTER on BranchDB001 (Clears Binary Logs)- make HQDB a Slave of a Branch (say
branch01)
- Start Replication loading changes from BranchDB001's
branch001 into HQDB's branch001
RESET MASTER on BranchDB002- make HQDB a Slave of a Branch (say
branch02)
- Start Replication loading changes from BranchDB002's
branch002 into HQDB's branch002
- ...
RESET MASTER on BranchDB025- make HQDB a Slave of a Branch (say
branch25)
- Start Replication loading changes from BranchDB025's
branch025 into HQDB's branch025
If you have common tables to ship to every branch, simply mysqldump each branch's copy of that table and load into each branch.
All these things could be done in a maintenance cycle to update HQDB's copy of every branch
I have discussed this wild idea before
CAVEAT
- Make sure your firewalls allow for DB Access in both directions
- Branch to HQ
- HQ to Branch
- SSH Tunneling, please
Hello you can sync your local database data with your live mysql server. I had done it making a local sync file that will present all the tables of the local database that I wanted to update to live database and then calling a call back function in footer file that function calls the function to sync local data to live server
Footer.php
jQuery(document).ready(function($){
working = false;
var do_sync = function(){
if ( working ) { return; }
working = true;
jQuery.post(
"<?php echo $this->config->item("sync_url"); ?>",
{},
function(ret){
working = false;
}
);
}
window.setInterval(do_sync, 10000);
});
Config.php
$config['sync_server_url'] = "http://livesite.com/index.php/sync_server";
$config['sync_url'] = "http://localhost/localsite/index.php/sync";
sync.php contents Also you need to on CURL extension in local site wamp so that it can communicate with the server side, If database record that is already synced to server edited then sync field in DB should be set to 0 so that on edit it can overwrite server DB again.
Sync.php
<?php
class Sync extends CI_Controller {
private $tables;
function __construct() {
parent::__construct();
$this->load->database();
$this->tables = array(
"customers" => array("per_id"),
"db_log" => array("id"),
"inventory" => array("tr_id"),
"material" => array("id_mat"),
"mtracking" => array("id_tra"),
"orecipie" => array("id_rec"),
"moutlets" => array("id_st"),
"opeople" => array("per_id"),
"oreceivings" => array("rec_id"),
"osales" => array("sal_id"),
"osales_items" => array("sal_id", "itm_id"),
"osales_payments" => array("sal_id"),
"osales_sus" => array("sal_id", "emp_id"),
"osales_sus_items" => array("sal_id", "itm_id"),
"osales_sus_payments" => array("sal_id"),
"osuppliers" => array("per_id"),
);
$this->id_store = $this->config->item('id_store');
}
function index() {
echo '<br>'.date("H:i:s A");
$newLine = "\r\n";
$output = $update = "";
foreach($this->tables as $table_name => $columns ){
$col_check = "SHOW COLUMNS FROM `{$table_name}` LIKE 'is_sync'";
$col_exists = $this->db->query($col_check);
if ( $col_exists->num_rows > 0 ) { // is_sync column exists for this table
$sql = "SELECT * FROM {$table_name} WHERE is_sync = 0 LIMIT 500" ;
$rows = $this->db->query($sql);
if ( $rows->num_rows > 0 ) { // where is_sync = 0
foreach ( $rows->result_array() as $row ) {
$col_val = $update_col_val = $already_exists = array();
foreach($row as $name => $val) {
if ( is_null( $val ) ) {
continue;
}
$val = mysql_real_escape_string($val);
$update_col_val[] = " `{$name}` = '{$val}' ";
if ( "is_sync" === $name ) {
$val = 1;
}
if ( "id_store" === $name ) {
$val = $this->id_store;
}
$col_val[] = " `{$name}` = '{$val}' ";
if ( $name === $columns[0] ){
$already_exists[] = " `$columns[0]` = '{$val}'";
}
if ( $name === $columns[1] ){
$already_exists[] = " `$columns[1]` = '{$val}'";
}
}
if ( is_array($col_val) && count($col_val) > 0 ) {
//echo $this->table_keys[$table_name];
$output .= "SELECT * FROM `{$table_name}` WHERE `id_store` = {$this->id_store} AND " . implode(" AND ", $already_exists) . " ||";
$output .= "INSERT INTO `{$table_name}` SET " . implode(",", $col_val) . " ||";
$output .= "UPDATE `{$table_name}` SET " . implode(",", $col_val) . " WHERE `id_store` = {$this->id_store} AND " . implode(" AND ", $already_exists);
$output .= " ;; ";
$update .= "UPDATE {$table_name} SET is_sync = 1 WHERE " . implode(" AND ", $update_col_val) . " ;; ";
}
}
} // if is_sync = 0
} // if is_sync column exists
}
if ( !empty($output) && $this->post_data($output) ) {
$update = explode(" ;; ", $update);
foreach($update as $upq){
if (!empty($upq) ) {
$this->db->query($upq);
}
}
} else {
echo $result;
}
echo date("H:i:s A");
}
function post_data($qry){
$qry = htmlspecialchars(urlencode($qry));
$data = "qry=" . $qry;
$ch = curl_init();
curl_setopt( $ch, CURLOPT_URL,$this->config->item("sync_server_url") );
curl_setopt( $ch, CURLOPT_AUTOREFERER, 1);
curl_setopt ( $ch, CURLOPT_HTTPHEADER, array ( 'Content-length: ' . strlen($qry) ) );
curl_setopt( $ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt( $ch, CURLOPT_POST,1);
curl_setopt( $ch, CURLOPT_POSTFIELDS,$data);
curl_setopt( $ch, CURLOPT_CRLF, 1);
$result = curl_exec($ch);
curl_close($ch);
if ( 1 === intval($result) ) {
return TRUE;
} else {
echo $result;
return $result;
}
}
}
?>
Then after that you need to make a controller on live server I named it sync_server.php
sync_server.php
<?php
class Sync_server extends CI_Controller {
function __construct() {
parent::__construct();
$this->load->database();
}
function index() {
if (isset( $_POST['qry'] ) ) {
$qry = htmlspecialchars_decode( urldecode( $_POST['qry']));
//$this->connect_local_db();
$qry = explode(" ;; ", $qry);
foreach($qry as $q) {
$q = explode( "||", $q );
//print_r($q);
$exists = mysql_query( $q[0] ) or die ("<hr/>" . mysql_error() ."<br/>" . $q[0]);
if ( $exists && mysql_num_rows( $exists ) ) {
mysql_query( $q[2] ) or die ("<hr/>" . mysql_error()."<br />".$q[2]);
} else{
mysql_query( $q[1] ) or die ("<hr/>" . mysql_error()."<br />".$q[1]);
}
//$this->db->query($q);
}
die("1");
} else {
echo "qry not found";
}
}
}
?>
Best Answer
In addition to setting up replication, which will copy the data from your master to your slave, you need a failover technology to make the system use the slave when the master is down.
You can do this manually, but you can also use something like MMM (http://mysql-mmm.org/). It is open-source, so you can look at the code to see the logic of what it is doing. Basically, it is moving aliases (like yourdb-master and yourdb-slave) between two physical servers, so the system always connects to a yourdb-master, wherever that is physically.