8 replies to this topic

[Pally]

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

[WingedPanther73]

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

[Pally]

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

[wim DC]

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.

[Pally]

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.

[Luthfi]

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.

[Pally]

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

[Luthfi]

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.

[Luthfi]

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.