Solved

importing mysql structures only or restore ignoring non-existing tables and columns?

Posted on 2012-03-17
8
538 Views
Last Modified: 2012-03-27
Hi Friends,

I have 2 servers (1 for development, 1 is Production)

Before going to live I export Mysql Dump from Development & import on Production, both Server have same data Structure in Mysql DB,
but Now developer team made some change in Development DB also Prduction server is open for customer,

If I import DB from development to on Production, I dont want to lossed any data which is customer related e.g User registration,

is there a way (except 3rd party software like mysqldiff.org) to import structure only to an existing data?
0
Comment
Question by:Nilesh Havire
8 Comments
 
LVL 24

Accepted Solution

by:
johanntagle earned 300 total points
ID: 37734171
maybe you can run mysqldump with --no-data option on both databases then compare the output to see what changes what you need to do to the production database.
0
 
LVL 83

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 100 total points
ID: 37734202
If you have phpmyadmin on the system, it will let you export Structure only by unchecking 'Data' on the Export page.  Once you figure out the differences, you can manually change the Production tables or maybe do an ALTER TABLE command with the needed changes.  Note that if you are adding columns to a large table, it won't be an instantaneous event because you are basically updating all the rows.
0
 

Author Comment

by:Nilesh Havire
ID: 37734705
Can anyone suggest me for taking only data from Mysql ?

I got structure  by below command

mysqldump -d -h localhost -u dba -p db_ms > nashik_structure.sql          -------------->Db Structure
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 300 total points
ID: 37734716
You can compare the outputs of mysqldump using unix/linux diff or any text editor that has a diff/compare function.
0
 
LVL 109

Assisted Solution

by:Ray Paseur
Ray Paseur earned 100 total points
ID: 37734928
How many tables are involved?  For something like this, I might want to write a specialized script.  That way I would have a repeatable process that could be tested thoroughly.
0
 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 300 total points
ID: 37735879
Another way:

On the production database:

mysql> create table production_db_tables as select * from information_schema.columns where table_schema='production_db_schema_name';

On the development database:

mysql> create table development_db_tables as select * from information_schema.columns where table_schema='development_db_schema_name';

Export the created tables then import to a temporary database.  Then you can do LEFT JOIN on queries on those tables (join table_name and column_name) to see which columns and tables are not part of each database.  Finding out which columns where modified (e.g. increased size) will be trickier as you will need to use more columns in the join (e.g. datatype, numeric_scale, character_maximum_length) but it should be possible.
0
 

Author Comment

by:Nilesh Havire
ID: 37736289
Total 123 tables in DB
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37775020
Thanks.  I totally missed your other comment - use "--no-create-db --no-create-info" options  to get only data.  See http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html for complete list of options.

What did you end up finally doing, by the way?
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question