Restore MySQL Dump File But I want it to restore with different names?
Linux - NewbieThis 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
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
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.
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.
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.
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.
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
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.