• Technical
  • Data Migrator and Merger: Synchronize MySQL databases using PHP
Header

Data Migrator and Merger: Synchronize MySQL databases using PHP

Hi Folks,

Last year, I published a package in PHPClasses to synchronize the records of two versions of the same database schema and also to synchronize the schema. You can check out the link here – http://www.phpclasses.org/package/7448-PHP-Synchronize-MySQL-tables-with-another-database.html.

url

The purpose of this package is simple. Many a times, there is problem while development in a team that a few things are working on a developer’s machine and the same things are not working on another developer’s machine or showing exceptions/errors. After hours of debugging we find that the data entered in database are different and therefore is the reason
of inconsistent behaviour of code (if code is meeting the business requirements).  So, in this case, this package will help to migrate records between two versions of the same database schema and synchronize different schema too and hence to debug easily.

 

Here is the old schema whose data needs to be changed: oldschema.sql

-- phpMyAdmin SQL Dump
-- version 3.3.10deb1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Apr 04, 2012 at 09:43 PM
-- Server version: 5.1.61
-- PHP Version: 5.3.5-1ubuntu7.4

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `oldschema`
--

-- --------------------------------------------------------

--
-- Table structure for table `user_table`
--

CREATE TABLE IF NOT EXISTS `user_table` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_uname` varchar(255) COLLATE utf8_bin NOT NULL,
  `user_email` varchar(255) COLLATE utf8_bin NOT NULL,
  `user_passwd` blob NOT NULL,
  `user_fname` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_lname` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_token` varchar(255) COLLATE utf8_bin NOT NULL,
  `user_gender` varchar(20) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_address` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_city` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_postal` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_country` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_contact_no` varchar(255) COLLATE utf8_bin NOT NULL,
  `user_status` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT 'Inactive',
  `user_online` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT 'No',
  `user_last_login` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `user_logout_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `user_uname` (`user_uname`),
  UNIQUE KEY `user_email` (`user_email`),
  UNIQUE KEY `user_token` (`user_token`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=24 ;

--
-- Dumping data for table `user_table`
--

INSERT INTO `user_table` (`user_id`, `user_uname`, `user_email`, `user_passwd`, `user_fname`, `user_lname`, `user_token`, `user_gender`, `user_address`, `user_city`, `user_postal`, `user_country`, `user_contact_no`, `user_status`, `user_online`, `user_last_login`, `user_logout_time`) VALUES
(1, 'admin', 'admin@el.com', 0x9df9yuyyb96e442b, 'Super', 'Administrator', 'none', 'Male', '', '', '', '', '', 'Active', 'No', '2012-02-16 15:52:01', '2011-12-13 15:57:10'),
(2, 'guser', 'nitesh.apte@el.com', 0fgdx9df9b96e442b, 'Nitesh', 'Apte', 'wsse4d', 'Male', '', '', '', '', '', 'Inactive', 'No', '2011-10-19 13:47:50', '0000-00-00 00:00:00'),
(8, '32c786bf', 'el.comr@gmail.com', 0x3332633738366266, 'F1', 'L1', '32c786bf', '', '', '', '', '', '', 'Active', 'No', '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(9, '9cae8262', 'nitesh.el.comapte@el.com', 0x3963616538323632, 'F2', 'L2', '9cae8262', '', '', '', '', '', '', 'Active', 'No', '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(10, '2b15e984', 'chanel.comep@gmail.com', 0x3262313565393834, 'F3', 'L3', '2b15e984', '', '', '', '', '', '', 'Inactive', 'No', '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(11, '233618a0', 'prael.comdra@csinc.in', 0x3233333631386130, 'F4', 'L4', '233618a0', '', '', '', '', '', '', 'Inactive', 'No', '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(14, '7330895f', 'promotional@el.com', 0x0f98f8ad04fc638d, 'Promotional', 'Admin', '7330895f', '', '', '', '', '', '', 'Active', 'No', '2011-11-25 22:15:01', '2011-11-25 22:19:14'),
(15, 'cbdbb6f8', 'event@el.com', 0xd0c5aa7bbaf038b7, 'Event', 'Admin', 'cbdbb6f8', '', '', '', '', '', '', 'Active', 'No', '2011-12-09 17:21:49', '2011-11-25 16:38:08'),
(16, '4b8c38a0', 'news@el.com', 0xa0921d3ab950c3df, 'News', 'Admin', '4b8c38a0', '', '', '', '', '', '', 'Active', 'No', '2011-12-09 16:52:45', '2011-11-23 12:32:45'),
(17, '705d0284', 'question@el.com', 0x0f157e726f84036a, 'Question', 'Admin', '705d0284', '', '', '', '', '', '', 'Active', 'No', '2011-12-15 11:50:40', '2011-11-25 17:29:27'),
(18, '6276efda', 'nitesh@el.com', 0xdfa26468c432b172, 'Nitesh1', 'Apte', '6276efda', '', '', '', '', '', '9922939828', 'Active', 'No', '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(20, '21dd5f14', 'some@some.com', 0xa868a82732644567, 'SomeFirstname', 'SomeLAstName', '21dd5f14', '', '', '', '', '', '3442344545251244', 'Active', 'No', '2011-11-11 19:44:54', '2011-11-01 00:00:00'),
(21, 'cb950bf8', 'rael.comdha.mel.com@radmoh.com', 0xd0c5e7aa34ed34bb, 'Radha', 'Mohan', 'cb950bf8', '', '', '', '', '', '98978989989', 'Inactive', 'No', '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(22, '738e2bc6', 'niteel.comshapte@el.com', 0x0f984f5ed6bbaa13, 'Nitesh', 'Apte', '738e2bc6', '', '', '', '', '', '99999999999', 'Inactive', 'No', '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(23, '4eb8e6b2', 'dada@assdfs.com', 0xa0eea493cafbb134, 'nitesh', 'apye', '4eb8e6b2', '', '', '', '', '', '808534085034', 'Inactive', 'No', '0000-00-00 00:00:00', '0000-00-00 00:00:00');

And the new schema is below: newschema.sql

-- phpMyAdmin SQL Dump
-- version 3.3.10deb1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Apr 04, 2012 at 09:41 PM
-- Server version: 5.1.61
-- PHP Version: 5.3.5-1ubuntu7.4

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `newschema`
--

-- --------------------------------------------------------

--
-- Table structure for table `user_table`
--

CREATE TABLE IF NOT EXISTS `user_table` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_uname` varchar(255) COLLATE utf8_bin NOT NULL,
  `user_email` varchar(255) COLLATE utf8_bin NOT NULL,
  `user_passwd` blob NOT NULL,
  `user_fname` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_lname` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_token` varchar(255) COLLATE utf8_bin NOT NULL,
  `user_gender` varchar(20) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_address` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_city` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_postal` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_country` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_contact_no` varchar(255) COLLATE utf8_bin NOT NULL,
  `user_status` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT 'Inactive',
  `user_online` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT 'No',
  `user_created_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `user_last_login` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `user_logout_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `user_uname` (`user_uname`),
  UNIQUE KEY `user_email` (`user_email`),
  UNIQUE KEY `user_token` (`user_token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

--
-- Dumping data for table `user_table`
--

Below is the class that will do the trick: class.DataMigratorMerger.php

<?php 
/**
 * Data Migrator Mreger is for merging the data/records of two database of completely same or similar schema.
 * 
 * @author Nitesh Apte
 */

class DataMigratorMreger {

    private $conn;
    private $sourceDB;
    private $destinationDB;

    public function __construct() {
        $this->conn = mysql_connect("localhost", "root", "");
    }

    public function mergeData($oldSchema, $newSchema, $reverse = FALSE) {
        mysql_query("SET FOREIGN_KEY_CHECKS = 0");

        if($reverse) {
            $this->destinationDB = $oldSchema;
            $this->sourceDB = $newSchema;
        } else {
            $this->destinationDB = $newSchema;
            $this->sourceDB = $oldSchema;
        }

        mysql_query("use ".$oldSchema);

        $tables = $this->getAssoc("show tables");

        for($i=0;$i<count($tables);$i++) {

            /* Disable this in case you want to merge the data */
            $trun = "TRUNCATE $this->destinationDB.".$tables[$i]['Tables_in_'.$this->sourceDB];
            mysql_query($trun);

            $values = $this->getAssoc("describe ".$tables[$i]['Tables_in_'.$oldSchema]);

            // put $j=1 in case of merging. Generally, first column is auto incremented
 id
            for($j=0;$j<count($values);$j++) {
                $val[$i][] =  $values[$j]['Field'];
            }

            $field = implode(',', $val[$i]);

            $sql = "INSERT INTO $this->destinationDB.".$tables[$i]['Tables_in_'.$oldSchema]." ($field) SELECT ".$field." FROM $this->sourceDB.".$tables[$i]['Tables_in_'.$oldSchema];

            mysql_query($sql);
        }
        mysql_query("SET FOREIGN_KEY_CHECKS = 1");
    }

    public function getAssoc($queryString) {

        $rs = mysql_query($queryString, $this->conn);

        while($rw = @mysql_fetch_array($rs, MYSQL_ASSOC)){
            $values[] = $rw;
        }
        return $values;
    }
}
?>

And below is the test script describing the usage of this class: test.php

<?php
ini_set("display_errors", 1);
error_reporting("E_ALL ~ E_NOTICE");

include_once 'class.DataMigratorMerger.php';

$obj = new DatabaseMergerMigrator();

//migrate/merge from oldschema to newschema
$obj->mergeData("oldschema","newschema");

// Put third parameter as TRUE if you want to migrate/merge from newschema to old schema.
$obj->mergeData("oldschema","newschema", TRUE);

?>

Quite a handy tool, isn’t it? Hope this will help to save a lot of time in debugging and development.

Please feel free to improve and share it.

Github: You can clone it from https://github.com/niteshapte/mysql-data-merger-migrator

 

That’s all folks for today.

Critics/suggestion are very much welcome.

Have a nice day ahead.

7,183 total views, 1 views today

This entry was posted in Technical

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.

Follow Me