Solved

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

Posted on 2012-03-17
8
560 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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 110

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

Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
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.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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…

724 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