[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2012-03-17
8
Medium Priority
?
579 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 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
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…
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 …
Suggested Courses
Course of the Month20 days, 2 hours left to enroll

872 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