LinuxQuestions.org
Register a domain and help support LQ
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices

Reply
 
Search this Thread
Old 07-07-2009, 05:18 PM   #1
helptonewbie
Member
 
Registered: Aug 2006
Location: England Somewhere
Distribution: Mandriva, PCLinuxOS, Karoshi, Suse, Redhat, Ubuntu
Posts: 517

Rep: Reputation: 39
Question 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.
 
Old 07-07-2009, 05:35 PM   #2
Tinkster
Moderator
 
Registered: Apr 2002
Location: in a fallen world
Distribution: slackware by choice, others too :} ... android.
Posts: 22,962
Blog Entries: 11

Rep: Reputation: 865Reputation: 865Reputation: 865Reputation: 865Reputation: 865Reputation: 865Reputation: 865
Just use sed to change every occurrence of "undesired tablename" to "desired tablename".
 
Old 07-07-2009, 05:49 PM   #3
Robhogg
Member
 
Registered: Sep 2004
Location: Old York, North Yorks.
Distribution: Debian 7 (mainly)
Posts: 653

Rep: Reputation: 85
Quote:
Originally Posted by helptonewbie View Post
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.
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.

Last edited by Robhogg; 07-07-2009 at 05:52 PM.
 
Old 07-08-2009, 04:02 AM   #4
jeromeNP7
Member
 
Registered: Jun 2009
Posts: 101

Rep: Reputation: 19
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

Last edited by jeromeNP7; 09-04-2009 at 09:20 PM.
 
Old 07-08-2009, 04:48 AM   #5
helptonewbie
Member
 
Registered: Aug 2006
Location: England Somewhere
Distribution: Mandriva, PCLinuxOS, Karoshi, Suse, Redhat, Ubuntu
Posts: 517

Original Poster
Rep: Reputation: 39
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
 
Old 07-08-2009, 05:09 AM   #6
PMP
Member
 
Registered: Apr 2009
Location: ~
Distribution: RHEL, Fedora
Posts: 381

Rep: Reputation: 58
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
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Weird, file names changing case after restore... s2cuts Linux - General 2 03-03-2007 01:39 PM
Trying to restore mysql dump, error 1062: Duplicate entry mackdav Linux - Software 1 04-25-2006 03:02 AM
restore file or dir from dump file brumela Linux - Newbie 3 09-08-2004 04:31 AM
dump/restore moger Linux - Software 4 12-31-2002 04:34 AM
dump and restore nat Linux - General 1 04-26-2002 04:08 AM


All times are GMT -5. The time now is 07:57 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration