Shantanu's Blog

We are going to learn how update the production data to be in sync with the development server. We will use 3 utilities.

1) diff : This is linux built-in command to compare 2 files.

2) Maatkit: This will be used to compare checksum values of 2 tables and the data differences.
http://www.maatkit.org/doc/

3) schemasync: This utility is used to compare structural differences.
http://schemasync.org/

______________________________________

I) Schema sync

We will first make sure that the table structure of localhost mktest table matches with the SERVER_A table structure.
In the following example, the column "mytime" is missing. How do we know that? How do we create the alter table statement?

server:localhost
schema: test

mysql> select * from mktest;
+----+----------+--------+------+
| id | f_amount | myname | city |
+----+----------+--------+------+
| 1 | NULL | abc | NULL |
| 3 | NULL | abc | NULL |
| 6 | NULL | abc | NULL |
| 8 | NULL | abc | NULL |
| 9 | NULL | abc | NULL |
| 21 | NULL | abc | NULL |
+----+----------+--------+------+
6 rows in set (0.00 sec)


server: SERVER_A
schema: test

mysql> select * from mktest;
+----+----------+--------+------+--------+
| id | f_amount | myname | city | mytime |
+----+----------+--------+------+--------+
| 1 | NULL | abc | NULL | NULL |
| 3 | NULL | abc | NULL | NULL |
| 5 | NULL | abc | NULL | NULL |
| 6 | NULL | abc | NULL | NULL |
| 8 | NULL | abc | NULL | NULL |
| 9 | NULL | abc | NULL | NULL |
| 11 | NULL | abc | NULL | NULL |
+----+----------+--------+------+--------+
7 rows in set (0.00 sec)


# diff <(mysqldump -hSERVER_A -uroot -proot@123 test mktest --skip-extended-insert -d -R) <(mysqldump -hlocalhost -uroot -proot@123 test mktest --skip-extended-insert -d -R) --side-by-side --suppress-common-lines --width=190 | more
-- Host: SERVER_A Database: test | -- Host: localhost Database: test
`mytime` datetime default NULL, <
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1; | ) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=latin1;

*** mytime column needs to be added and the table type needs to be changed from InnoDB to MyISAM ***
_____

The diff utility does not generate ready to execute alter statements. We need a third party utility called schemasync.

# schemasync mysql://root:root@123@localhost:3306/test mysql://root:root@123@SERVER_A:3306/test

# cat /home/shantanu/test.20110720.patch.sql
USE `test`;
ALTER TABLE `mktest` ADD COLUMN `mytime` datetime NULL AFTER `city`;

It will also generate an alter table statement to change the table type.

______________________________________

II) Data Sync:

Now that our structure is in order, let's see what records are added / updated.

server:localhost
schema: test

mysql> select * from mktest;
+----+----------+--------+------+--------+
| id | f_amount | myname | city | mytime |
+----+----------+--------+------+--------+
| 1 | NULL | abc | NULL | NULL |
| 3 | NULL | abc | NULL | NULL |
| 6 | NULL | abc | NULL | NULL |
| 8 | NULL | abc | NULL | NULL |
| 9 | NULL | abc | NULL | NULL |
| 21 | NULL | abc | NULL | NULL |
+----+----------+--------+------+--------+
6 rows in set (0.00 sec)

_____

server: SERVER_A
schema: test

mysql> select * from mktest;
+----+----------+--------+------+--------+
| id | f_amount | myname | city | mytime |
+----+----------+--------+------+--------+
| 1 | NULL | abc | NULL | NULL |
| 3 | NULL | abc | NULL | NULL |
| 5 | NULL | abc | NULL | NULL |
| 6 | NULL | abc | NULL | NULL |
| 8 | NULL | abc | NULL | NULL |
| 9 | NULL | abc | NULL | NULL |
| 11 | NULL | abc | NULL | NULL |
+----+----------+--------+------+--------+
7 rows in set (0.00 sec)


a) Checksum match:
We need to find out if the checksum values of these 2 tables match. If the row count and checksum values are the same, the table are exactly the same.

# /usr/bin/mk-table-checksum h=SERVER_A,u=root,p=root@123,P=3306 --databases test --tables mktest h=localhost,u=root,p=root@123,P=3306 --databases test --tables mktest --count | /usr/bin/mk-checksum-filter
test mktest 0 SERVER_A MyISAM 7 4d38da45 0 0 NULL NULL
test mktest 0 localhost MyISAM 6 f1e08b3d 0 0 NULL NULL

There is a difference of 1 row and the checksum value does not match. So we need to find out what are those records.
_____

b) Finding differences:

# diff <(mysqldump -hSERVER_A -uroot -proot@123 test mktest --skip-extended-insert) <(mysqldump -hlocalhost -uroot -proot@123 test mktest --skip-extended-insert) --side-by-side --suppress-common-lines --width=190 | more
-- Host: SERVER_A Database: test | -- Host: localhost Database: test
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1; | ) ENGINE=MyISAM AUTO_INCREMENT=22 DEFAULT CHARSET=latin1;
INSERT INTO `mktest` VALUES (5,NULL,'abc',NULL,NULL); <
INSERT INTO `mktest` VALUES (11,NULL,'abc',NULL,NULL); | INSERT INTO `mktest` VALUES (21,NULL,'abc',NULL,NULL);

The above screen shows that one row is missing from localhost while the another row ID should be 11 instead of 21.
_____

c) Generate SQL:
So we need one insert statement and one update. The maatkit table sync utility has generated delete / insert combo instead of update as shown below:

# /usr/bin/mk-table-sync --print h=SERVER_A,u=root,p=root@123,P=3306 --databases test --tables mktest h=localhost,u=root,p=root@123,P=3306 --databases test --tables mktest
DELETE FROM `test`.`mktest` WHERE `id`='21' LIMIT 1 ;
INSERT INTO `test`.`mktest`(`id`, `f_amount`, `myname`, `city`, `mytime`) VALUES ('5', NULL, 'abc', NULL, NULL) ;
INSERT INTO `test`.`mktest`(`id`, `f_amount`, `myname`, `city`, `mytime`) VALUES ('11', NULL, 'abc', NULL, NULL) ;

Labels: mysql FAQ, mysql tips