Restore MySQL Dump File But I want it to restore with different names?
 

Hi All,

I've just been looking at my dump files and realised that
A:- they include drop table if exist statements so a restore would overwrite current data
AND
B:- That the restoration would of course occur using the same table name as before. Which is a bit of a problem because although I could just restore the table to a different database instead for example. Thats not really what i need to do.

So into the mysqldump script i've added --skip-add-drop-table, so that should stop that part of the problem and should stop the possibility i hope of over writting all the latest data in a table. But now i wanted to work out how i could restore the table but to the same database but to a different table name. Not sure how possible that is as within the dump file i see every command is of course as it should be... inserting into the table name it was before. That is no good to me and i wonder if its possible to change this. Or perhaps there's a way to force the dump file to save the data as a different table name (did i miss that somewhere?). Or worst case would be to do something with sed and re-write all the table names within the dump file... 1- i don't know if that would work 2- it probably not guaranteed to only be changing the table names and chances are it might go and change some of the data as well.

If anyone understands what i'm trying to achieve please let me know any possible answers.

Thanks.

Just use sed to change every occurrence of "undesired tablename" to "desired tablename".

It would work, but these are valid concerns. I'd suggest first using grep to check the pattern(s) matching the table name(s) to be changed. If you find some false positives you can tweak the pattern, and then create your sed statement once the pattern is right.

An alternative (or complementary) approach would be to make the substitution using sed, but redirect the output into a new file rather than changing the original (i.e. don't use the -i flag). Then use diff on the original and modified files, to check the changes that have been made.

SQL dumpfile are basically backup files, so replacing existing tables with the stored data is a valid approach. I'm not sure why you would want to have the same data structure and data rows stored twice in the same database, but you can use any contemporary editor to do string replace operation on the table names. Changing all tablenames is likely to render some or all applications or scripts useless that rely on certain table names.

Linux

Hi All,
thanks for your replies this has been very useful information.

jeromeNP7 - The reason i'm wanting to do this is its not the same data in the tables, the table is made up of logging data which every night is dumped and then refreshed for the next day. Thus if i were to restore then i'd have lost all the latest data which is unacceptable. Of course the only thing is these tables are huge so i suppose using sed could take ages to process and change everything inside the table. I'll have to give it a go and see how long it takes.

Thanks for the help peep's.
Regards.
M

helptonewbie, why do you want to restore the dumped data in the same databse. You can create another dummy database for such activities. This will not interfere with your current data and feasible to remove as well