?
Solved

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

Posted on 2012-03-17
8
Medium Priority
?
572 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 900 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 84

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 300 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 900 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 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 300 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 900 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

762 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