Thursday, June 19, 2008

MySQL: How to merge two databases

I'm currently managing a couple of databases in a production server. The problem: I need to know what new tables were created from Database 1 and merge them to Database 2.

Here's the solution:

0. Backup the target database.
% mysqldump -h [HOST] -P [PORT] -u [USERNAME] -p[PASSWORD] Database2 > Database2.sql

1. Extract the table names from the source and target databases.
% echo show tables | mysql -h [HOST] -P [PORT] -u [USERNAME] -p[PASSWORD] Database1 | sed '1d' > one.tables
% echo show tables | mysql -h [HOST] -P [PORT] -u [USERNAME] -p[PASSWORD] Database2 | sed '1d' > two.tables

2. Perform the merge.
% mysqldump -h [HOST] -P [PORT] -u [USERNAME] -p[PASSWORD] Database1 \
`diff --unchanged-line-format='' --new-line-format='%l ' two.tables one.tables` | \

mysql [HOST] -P [PORT] -u [USERNAME] -p[PASSWORD] Database2

No comments: