We have recently helped a customer with a problem in their MySQL database and thought we should share that with everybody.

The problem was that due to a server move at their hosting company, their data in MySQL became scattered throughout different databases with the same structure. As a result of this the first host, host1, had data from before the move, a few days *after* the move up to two days ago (when the user realized that data was not being stored in localhost but on their previous server) and, the second host had data from before the move and data since the last 2 days.

In order to merge that data we created a backup of each database on one of the mysql installations:

for i in database1 database2 database3 databas4; do
        mysqldump -n -t -q $i > host1_$i.sql
done

Once those were created we took those files to the now active host, host2 and issued the following:

sed -i'' -e 's/^INSERT/REPLACE/'  host1_database1.sql host1_database2.sql host1_database3.sql host1_databas4.sql

What that statement does is change the INSERT INTO statements to REPLACE INTO statements, REPLACE provides a way to insert data that could be duplicated on a table, deleting a row if it already existed on that table before doing the insert. This will effectively leave on the database the data that was not on the backup and insert in a way that appears only once the data that was on the backup (regardless of the data being or not on the original database).

After that its a matter of putting the data in

mysql> use database1
mysql> \. host1_database1.sql
mysql> use database2
mysql> \. host1_database2.sql
mysql> use database3
mysql> \. host1_database3.sql
mysql> use database4
mysql> \. host1_databas4.sql

And that's it! Data is now merged! Remember to create a backup of your current database before doing this!

Comments

Write comment
Name:
Email: