Friday, January 4, 2013

Replication of local Database to live Database

Recently i was working for one of the Government Office's Intranet based software. I had to develop a module "Sync Database to live". It was only replication (from Localhost to live site).

Mysql has replication feature. As i had little time to implement so i thought understanding of it and implementing into project will take me longer time. Then i developed my own module. Which i m going to describe here.

Firstly i prepared array of tables which are need to be replicated.
 e.g : $tables = array('authors','book_authors','publication','books','book_category');

Using PHPExcel, I generated the  multi-sheet excel file with all the data from given tables in respective sheet dynamically.

Using cURL, I uploaded excel file to target  (live server). Then i had to run a script in live server via cURL which reads the excel file and insert data to respective tables of live database.

        $ch = curl_init();
        $data = array('file' => "@$filepath");
        curl_setopt($ch, CURLOPT_URL, 'http://localhost/upload/upload.php');
        curl_setopt($ch, CURLOPT_POST, 1);
        curl_setopt($ch, CURLOPT_POSTFIELDS, $data);


Sending @ before the filepath makes sure that cURL sends the file as a part of a “multipart/form-data” post. That was what i exactly needed.