HI Lyle,
Your last idea sounds like a good one. Add the new tables with a suffix
.new and then rename them when you're happy. Once all the tables are in the
same database you can run UPDATE commands to increase the total columns - or
whatever cronjob is computing the totals in the first place might magically
recalculate them for you? ;-)
I use webmin's MySQL module for doing this sort of thing as it helps you
with RENAME and other less common SQL you otherwise have to get the manual
for.
Nige
2008/7/8 Lyle <[EMAIL PROTECTED]>:
> 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
>
_______________________________________________
BristolBathPM mailing list
[email protected]
http://mailman.bristolbath.org/mailman/listinfo/bristolbathpm
