Solved

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

Posted on 2012-03-17
8
544 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

808 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