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.

(Dont 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.

Youve 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 youve 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.