Mysql – how to sync local thesql database and live server database

MySQLPHP

My plan is to store data in my local MySQL database and after a time interval it will update my live MySQL database remotely. Is it possible?

I'm planing a inventory management script in PHP MySQL. The real problem is in our country net connection drops suddenly. So I want to first write to the local MySQL database and then it will update the real server. In any situation like internet connection dropped the script will work locally to gather data and retrieve data. When the internet connection comes back it will update the data to the real server. I want to protect against internet connection failures.

Nothing will update the real server in the mean time because the users are only in the office where the internet connection will drop. No one from the outside will able to update the database. Only the information can be retrieved from the live server to the shop's owner.

Best Answer

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";
            }
        }


    }
?>

Related Solutions

Mysql – Master-Slave Database. How to activate a secondary database whenever the primary database is down

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.

MySQL – SQL Stall on Server but Works on Local Machine

This is probably related to indexes and Mysql 5.6 handles certain query differently:
as you can see your local version for table core_url_rewrite Explain shows [Extra] => Using where (your live does not).
Your left join is probably causing the issue.

Try to run the query without the left join on both server and see if you get the same performance.
If you do, you know the left JOIN is cause Mysql to run the query differently on both server. (i'm assuming you have the same amount of data on both).
I don't see catalog_product_website table definition listed. Apparently it is using IDX_CATALOG_PRODUCT_WEBSITE_WEBSITE_ID (which includes the website_id).

Your left join

 LEFT JOIN `core_url_rewrite` AS `ur`
    ON main_table.entity_id = ur.product_id AND ur.category_id IS NULL AND ur.store_id = 2 AND ur.is_system = 1
WHERE ...

IS same as:

 LEFT JOIN `core_url_rewrite` AS `ur`
    ON main_table.entity_id = ur.product_id 
WHERE ...
AND ur.category_id IS NULL AND ur.store_id = 2 AND ur.is_system = 1

On live server, I would add 2 index on table :

ALTER TABLE `core_url_rewrite` ADD INDEX `idx_storeid_is_system_cat_id` (`store_id`,`is_system`,`category_id`);
ALTER TABLE `core_url_rewrite` ADD INDEX `idx_storeid_is_system_prod_id` (`store_id`,`is_system`,`product_id`);

and see which one gets picked up. (Mysql indexes can be tricky)

Optimizing the table could also fix the issue. Sometimes indexes are thrown out of place or get fragmented (if data is often updated / deleted).

Best Answer

Related Solutions

Mysql – Master-Slave Database. How to activate a secondary database whenever the primary database is down

MySQL – SQL Stall on Server but Works on Local Machine

Related Question