Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Importing Mysql File into new Database

mysqk import database

Best Answer Luthfi, 21 August 2013 - 08:56 AM

AFAIK, there is no built-in mysql tool/function to do that. But you could write your own data pump that take dump data from development USING production scheme. Of course you need to anticipate the possibility of some columns that exist in production but not in development.

Go to the full post


This topic has been archived. This means that you cannot reply to this topic.
8 replies to this topic

#1 Pally

Pally

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 413 posts

Posted 19 August 2013 - 06:41 AM

I made changes to my database by adding new tables, and columns call this database 1

 

now I want to import the existing database from my website call this database 2

 

My goal is to import database 2 into database 1 while leaving all the new tables, columns that I added.

 

 

so this new databases should basically be the two databases combined but only contain the row data from database 2.

 

 

 

I'll summarize again if that was not clear... basically modification were made to the database scheme with new tables and columns being added.. I want to import all of the old existing data into this new scheme keeping the added columns and tables (but no reason to keep row data from the new scheme which I was just using for tests since I only want the online databases rows).


Edited by Roger, 20 August 2013 - 07:01 AM.
tags

Your Friendly Neighborhood Pally

#2 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts

Posted 19 August 2013 - 08:32 AM

It almost sounds like you'd do better to script the new tables into Database 1.


Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#3 Pally

Pally

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 413 posts

Posted 20 August 2013 - 04:38 AM

It almost sounds like you'd do better to script the new tables into Database 1.

 

Really?

 

I imagine this is something people have to do all the time for example facebook.. what do they do if they decide to add a new feature to a page they probably add a column somewhere in there databases to show if the feature is enabled or not.. I can't imagine them having to script their entire databases just to add this new column

 

 

Whats happening is I have a development mysql that I added some new columns to and since the development mysql is behind the live version of the site I just want to push my additions to the sites database


Your Friendly Neighborhood Pally

#4 wim DC

wim DC

    Roar

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 2681 posts

Posted 20 August 2013 - 05:23 AM

hmm, assuming only nullable columns are added and no existing colums / tables changed name, I would guess a data-only (no create/delete table scripts) dump of database 2 would fit in database 1 no?

 

As long as you use --complete-insert as parameter to make sure mysql adds the column names for the insert statements of the database 2 data I don't see a problem for those insert statements not to work on database 1.


Edited by wim DC, 20 August 2013 - 05:24 AM.


#5 Pally

Pally

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 413 posts

Posted 20 August 2013 - 06:58 AM

hmm, assuming only nullable columns are added and no existing colums / tables changed name, I would guess a data-only (no create/delete table scripts) dump of database 2 would fit in database 1 no?

 

As long as you use --complete-insert as parameter to make sure mysql adds the column names for the insert statements of the database 2 data I don't see a problem for those insert statements not to work on database 1.

 

The way things are now my production database is a subset of my development database scheme since my development sql contains all the same table and columns as the production database (and then some additional columns and tables).

 

I know I can import a mysqldump but this results in using the imported scheme.. id like to say "hey import everything but use the existing scheme of our destination".

 

 

 

in mathmatical terms you basically have two sets here..

 

let A be my production database, and B be my development database.

 

set A is a subset of B.

 

The function from A to B is one to one BUT NOT onto.

the function is just using the same table names and column names and matching them up in set A and B.

 

 

No built in method of mysql for doing this?


Edited by Pally, 20 August 2013 - 07:09 AM.

Your Friendly Neighborhood Pally

#6 Luthfi

Luthfi

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1320 posts

Posted 21 August 2013 - 08:56 AM   Best Answer

AFAIK, there is no built-in mysql tool/function to do that. But you could write your own data pump that take dump data from development USING production scheme. Of course you need to anticipate the possibility of some columns that exist in production but not in development.



#7 Pally

Pally

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 413 posts

Posted 21 August 2013 - 11:21 AM

AFAIK, there is no built-in mysql tool/function to do that. But you could write your own data pump that take dump data from development USING production scheme. Of course you need to anticipate the possibility of some columns that exist in production but not in development.

 

Ok thanks I'm surprised there is not anything like this.. no problem as of now my production only generates new rows of stuff

 

thanks again


Your Friendly Neighborhood Pally

#8 Luthfi

Luthfi

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1320 posts

Posted 21 August 2013 - 11:55 PM

No problem, mate!

 

This feature might be available in some commercial data pump software. But, actually it's easy to create your own data pump for this.

 

Use a loop to find existing tables in production, for each table check if it also exists in development, when yes, do another loop for column names and check if the column also exists in both scheme, when yes, add the columns to the "select" list. After all columns of a table have been checked, produce a select command for that table,

 

Finally you can use those select command to dump records from development.



#9 Luthfi

Luthfi

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1320 posts

Posted 11 October 2013 - 11:55 PM

Sorry that I forgot to mention this earlier. I wrote a simple tutorial on how to implement this. While that tutorial was written for Delphi, I think you can get more details to implement in your problem.

 

Dump Records from One Database Using Other's Schema

 

Cheers!


Edited by Luthfi, 11 October 2013 - 11:56 PM.





Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download