Database migration, how to

Question

I have two databases DB1 and DB2. In both databases exists this two tables mo_sms and mt_sms. This is the structure of those tables:

CREATE TABLE IF NOT EXISTS `mo_sms` (
  `id_MO` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sms_proceso` char(1) NOT NULL COMMENT 's=SMS c=CHAT d=Descargas',
  `msisdn` varchar(20) NOT NULL,
  `texto_sms` varchar(160) DEFAULT NULL,
  `brand_id` int(10) unsigned NOT NULL,
  `fecha_sms` datetime NOT NULL,
  `comando_id` int(10) unsigned NOT NULL DEFAULT '0',
  `alias_desc` varchar(25) DEFAULT NULL,
  `shortcode_id` int(10) unsigned NOT NULL,
  `precio` float(11,2) unsigned DEFAULT '0.00' COMMENT 'Precio del MO',
  `id_user` int(10) unsigned NOT NULL,
  `state` char(1) NOT NULL DEFAULT '0' COMMENT '0=Por procesar 1=Procesado',
  `tipo_sms` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0=Normal, <>0 dependera del tipopredeterminado',
  `anio_sms` smallint(4) unsigned NOT NULL DEFAULT '0',
  `mes_sms` smallint(2) unsigned zerofill NOT NULL DEFAULT '00',
  PRIMARY KEY (`id_MO`),
  KEY `ix_carrier` (`brand_id`),
  KEY `ix_fecha_sms` (`fecha_sms`),
  KEY `ix_fecha_carrier_keyword` (`fecha_sms`,`brand_id`,`alias_desc`),
  KEY `ix_msisdn` (`msisdn`),
  KEY `ix_sms_proceso` (`sms_proceso`),
  KEY `ix_sms_proceso_state` (`sms_proceso`,`state`),
  KEY `ix_id_user` (`id_user`),
  KEY `ix_fecha_sms_user` (`fecha_sms`,`id_user`),
  KEY `ix_varios` (`anio_sms`,`mes_sms`,`comando_id`,`shortcode_id`,`brand_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Tabla de MO' AUTO_INCREMENT=82972 ;

CREATE TABLE IF NOT EXISTS `mt_sms` (
  `id_MT` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sms_proceso` char(1) NOT NULL DEFAULT 'c' COMMENT 's=SMS c=CHAT d=Descargas',
  `msisdn` varchar(20) NOT NULL,
  `texto_sms` varchar(160) DEFAULT NULL,
  `brand_id` int(10) unsigned NOT NULL,
  `fecha_sms` datetime NOT NULL,
  `comando_id` int(10) unsigned NOT NULL DEFAULT '0',
  `alias_desc` varchar(25) DEFAULT NULL,
  `shortcode_id` int(10) unsigned NOT NULL,
  `id_user` int(10) unsigned NOT NULL,
  `tipo_sms` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '0=Normal, <>0 dependera del tipopredeterminado',
  `id_MO` int(11) unsigned NOT NULL,
  `state` char(1) DEFAULT '0' COMMENT '0=Por Procesar 1=Procesado',
  `anio_sms` smallint(4) unsigned NOT NULL DEFAULT '0',
  `mes_sms` smallint(2) unsigned zerofill NOT NULL DEFAULT '00',
  PRIMARY KEY (`id_MT`),
  KEY `ix_carrier` (`brand_id`),
  KEY `ix_fecha_sms` (`fecha_sms`),
  KEY `ix_fecha_carrier_keyword` (`fecha_sms`,`brand_id`,`alias_desc`),
  KEY `ix_msisdn` (`msisdn`),
  KEY `ix_sms_proceso` (`sms_proceso`),
  KEY `ix_id_user` (`id_user`),
  KEY `ix_fecha_sms_user` (`fecha_sms`,`id_user`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Tabla de MT' AUTO_INCREMENT=93853;

I have some values in DB2 that should be migrated (inserted) in DB1. My problem here is I don't know how to get the records from DB2.mo_sms tables and those relations from DB2.mt_sms and then insert to DB1.mo_sms and DB2.mt_sms. For example I'll get all the records from mo_sms with fecha_sms LIKE '%2013-04-19%' then if I insert those records in DB1.mo_sms new IDs will be generated then the integrity will be lost. Should I make this programatically or there is any way to do this using just SQL queries? I'm using MySQL as DBMS.

Answer 1

Data Extraction into a Sandbox

CREATE DATABASE IF NOT EXIST sms_sandbox;
CREATE TABLE IF NOT EXISTS sms_sandbox.mo_sms LIKE DB2.mo_sms;
CREATE TABLE IF NOT EXISTS sms_sandbox.mt_sms LIKE DB2.mt_sms;
#
# Drop All Indexes From the Sandbox
#
ALTER TABLE sms_sandbox.mo_sms
    DROP INDEX `ix_carrier`,
    DROP INDEX `ix_fecha_sms`,
    DROP INDEX `ix_fecha_carrier_keyword`,
    DROP INDEX `ix_msisdn`,
    DROP INDEX `ix_sms_proceso`,
    DROP INDEX `ix_sms_proceso_state`,
    DROP INDEX `ix_id_user`,
    DROP INDEX `ix_fecha_sms_user`,
    DROP INDEX `ix_varios`;
ALTER TABLE sms_sandbox.mt_sms
    DROP INDEX `ix_carrier`,
    DROP INDEX `ix_fecha_sms`,
    DROP INDEX `ix_fecha_carrier_keyword`,
    DROP INDEX `ix_msisdn`,
    DROP INDEX `ix_sms_proceso`,
    DROP INDEX `ix_id_user`,
    DROP INDEX `ix_fecha_sms_user`;
ALTER TABLE sms_sandbox.mo_sms AUTO_INCREMENT=1;
ALTER TABLE sms_sandbox.mt_sms AUTO_INCREMENT=1;
#
# Load the Data From 2013-04-19
#
INSERT INTO sms_sandbox.mo_sms
SELECT * FROM DB2.mo_sms
WHERE fecha_sms >= '2013-04-19 00:00:00'
AND fecha_sms <= '2013-04-19 23:59:59';
INSERT INTO sms_sandbox.mt_sms
SELECT * FROM DB2.mt_sms A INNER JOIN sms_sandbox.mo_sms B USING (id_MO);

Data Insertion From the Sandbox

#
# Get Maximum id_MO from DB1.mo_sms
# Increment all id_MOs by the Max Value
#
SELECT MAX(id_MO) INTO @Max_id_MO FROM DB1.mo_sms;
UPDATE sms_sandbox.mo_sms SET id_MO = id_MO + @Max_id_MO;
UPDATE sms_sandbox.mt_sms SET id_MO = id_MO + @Max_id_MO;
#
# Load the New Data into DB1
#
ALTER TABLE DB1.mo_sms DISABLE KEYS;
INSERT INTO DB1.mo_sms SELECT * FROM sms_sandbox.mo_sms;
ALTER TABLE DB1.mo_sms ENABLE KEYS;
ALTER TABLE DB1.mt_sms DISABLE KEYS;
INSERT INTO DB1.mt_sms
(`sms_proceso`,`msisdn`,`texto_sms`,`brand_id`,
`fecha_sms`,`comando_id`,`alias_desc`,`shortcode_id`,
`id_user`,`tipo_sms`,`id_MO`,`state`,`anio_sms`,`mes_sms`)
SELECT 
    `sms_proceso`,`msisdn`,`texto_sms`,`brand_id`,
    `fecha_sms`,`comando_id`,`alias_desc`,`shortcode_id`,
    `id_user`,`tipo_sms`,`id_MO`,`state`,`anio_sms`,`mes_sms`
FROM sms_sandbox.mt_sms;
ALTER TABLE DB1.mt_sms ENABLE KEYS;

WARNING : Practice this on a Staging Server, Please.

SUGGESTION

All the mt_sms tables should have an index on id_MO

ALTER TABLE mt_sms ADD INDEX (id_MO);

Give it a Try !!!

Answer 2

You basically need to keep track the new and old id from mo_sms

1. load all DB2.mo_sms that you want to copy
2. for each row in this result set
   1. store the actual DB2.mo_sms id
   2. save this row on DB1.mo_sms
   3. store the new DB1.mo_sms id, you can use LAST_INSERT_ID()
   4. load all DB2.mt_sms entries related to id that you stored on step 2.1
   5. save all entries on DB1.mt_sms changing the mt_sms.id_MO to the new id received on DB1 (the one which you stored on 2.3)

This should work.

Edit 1:

You can do it using an external programming language or stored procedure, if you choose to use a stored procedure, you will need to deal with cursors

Answer 3

You could also use a tool like Liquibase to generate a data diff from your source and then apply it to the destination database.