mysqlrowcopy 1.0

mysqlrowcopy is a tool that generates insert statements from result sets. It produces output similar to what might result from running mysqldump on a single SELECT query.

This project helps eliminate some of the tedium of moving data between QA and production MySQL databases.

Build:

To build mysqlrowcopy, you should run:

./configure
make

A mysqlrowcopy and mysqlrowcopy.debug file are created. They have identical functionality, the .debug version simply has debugging symbols built in (for gdb).

Since mysqlrowcopy is probably going to be I/O bound with modest CPU and RAM usage, the only reason to even build a 64-bit version is to work around potential issues in dynamic linking 32-bit binaries against 64-bit libraries.

RECIPES

1. Migrating a MySQL user account 'reaper' from QA server to a production server.

e.g. MySQL database server qa3.example.com to prod1.example.com:

$ mysqlrowcopy -h qa3.example.com
'SELECT * FROM db WHERE User = "reaper"' mysql db > reaper.sql
$ mysql -h prod1.example.com mysql < reaper.sql

You could of course simply pipe the output of mysqlrowcopy into mysql and skip the intermediate file.

(Don't forget to RELOAD PRIVILEGES afterwards)

2. Keep your test environment up to date. Populate it with production data every 24 hours. You could run this sequence from cron once a day:

$ mysqlrowcopy -h finance-db.example.com
'SELECT * FROM stocks WHERE modified > DATE_SUB(NOW(),INTERVAL 24 HOUR)'
finance stocks > day-stocks.sql

$ cat day-stocks.sql | mysql -h finance-test.example.com finance

3. Copy data between tables on different servers that have some similar fields.

You've got common data in table 'Zip' on a production database:

mysql> desc Zip;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| ZIPCode | varchar(5) | | PRI | | |
| ZIPCodeType | char(1) | YES | | NULL | |
| City | varchar(32) | YES | | NULL | |
| CityType | char(1) | YES | | NULL | |
| State | varchar(32) | YES | | NULL | |
| StateCode | char(3) | YES | | NULL | |
| AreaCode | char(3) | YES | | NULL | |
| Latitude | varchar(12) | YES | | NULL | |
| Longitude | varchar(12) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

And you've got table 'ZipPosition' in a research database:

mysql> desc ZipPosition;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| ZIPCode | varchar(5) | | | | |
| Latitude | varchar(12) | YES | | NULL | |
| Longitude | varchar(12) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

You want to load data from production Zip into research ZipPosition.

$ mysqlrowcopy -h production 'SELECT ZIPCode,Latitude,Longitude' common ZipPosition > pos.sql
$ cat pos.sql | mysql -h qa research

Note how we specify 'ZipPosition' on the first line to tell mysqlrowcopy what the destination table is going to be.

Product's homepage