Hi All,
I've hit a bit of a snag with restoring from a mysql backup. Having
posted to a MySQL forum with no reply, I'm thinking the solution will be
to use a bit of perl to get things working properly.
I wont go into too much detail, but the basic scenario is this:-
Database A is the old one full of data
Database B is the new one with all the same tables, starting to be
filled with new data.
A mysqldump of database A has been made with the command
mysqldump --opt --skip-add-drop-table --no-create-info --complete-insert
-uuser -ppass -hlocalhost databasename > ./sampledump.bak
So there are no drop tables, etc.
Some of the tables act as tallies for pieces of data, such:-
table ( ID (unique), totalamount )
How can I restore from the backup so that these table rows are merged
rather than replaced?
For example:-
The backup has:-
user1 (ID), 1000 (totalamount)
new database has
user1 (ID), 100 (totalamount)
I want to restore the backup and get:-
user1, 1100
I still have the old database so potentially I can make a new mysqldump
if needed.
The important bit of code in the mysqldump is:-
LOCK TABLES `tally` WRITE;
/*!40000 ALTER TABLE `tally` DISABLE KEYS */;
INSERT INTO `tally` (`ID`, `totalamount`) VALUES
('user1','1000'),('user2','2000'),('user3','3000');
/*!40000 ALTER TABLE `tally` ENABLE KEYS */;
UNLOCK TABLES;
My SQL isn't that great so there may be a simple SQL trick I'm missing...
At the moment I'm thinking it would be too much of a pain to try and
extract from this file which ones needs to be updated and which just
inserted. I'm thinking maybe re-create all the tables with a prefix, add
a prefix to the tables in this dump so I can get all the data into the
new database without disturbing the current database. Then move the non
tally data from one table to another, and finally tally up the data that
needs to be tallied. Seems a bit long winded...
Hoping someone here may have experience with this kind of thing... Or a
better idea...
Lyle
_______________________________________________
BristolBathPM mailing list
[email protected]
http://mailman.bristolbath.org/mailman/listinfo/bristolbathpm
