Yesterday I spent half of the day finding the bugs in the custom data migration tool. A few teams for a few months couldn’t solve the old problem and I couldn’t too, but it’s not the point. I found and tried simple tools for migrating data - SQLines (http://sqlines.com).
One of these tools - SQLines Data. It’s an open-source (Apache License 2.0) data transfer, schema conversion, and migration validation/testing too. It is really useful and simple.
Let me show a little example. If you want to copy a database from oracle instance to MySQL and you don’t want to think about a data conversion problem, you can just run a single command:
sqldata -sd=oracle, scott/tiger@oracle.example.com:1521/ORADBNAME -td=mysql, mysqluser/mysqlpassw@mysql.example.com:3306,MYSQLDB -t=ORADBNAME.* -vopt = rows -constraints=yes
Where:
-sd Source database connection string
-td Target database connection string
scott/tiger - oracle db user/password
mysqluser/mysqlpassw - mysql db user/password
-t option specifies tables to transfer or validate.
-vopt option specifies validation options for validate command, it could be rowcount (compare the count of rows in source and target tables (this is the default)) or rows (compare data in rows ordered by a primary key, unique constraint or all columns).
You also can specify which tables to exclude from transfer or validation by using -texcl option. That’s all.
Easy and effective.