Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Essentially what I'm trying to do is sync a production server with a sandbox server, but only the table structures and stored procedures. The procedures aren't any problem since they can be overriden, but the problem is the tables. I want to sync and alter their structures on the production server using mysqldump (or any other way that you can propose) without altering any existing data.

If it helps, I only want to add more columns, not remove any existing ones. Also, I am using mysqlyog.

Is there any way to do this?

share|improve this question

migrated from stackoverflow.com Oct 19 '12 at 12:52

This question came from our site for professional and enthusiast programmers.

3 Answers 3

As long as you don't rename tables and columns and you are not using partitions you might use schemasync .

Basic usage is:

schemasync mysql://user:pass@dev-host:3306/dev_db mysql://user:pass@prod-host:3306/prod_db

This will produce two SQL scripts - one for "patching" the production database, and another one for reverting the changes.

Please note that schema altering will lock the tables being affected, so you will have some downtime anyway. There are tools to avoid this: pt-online-schema-change from Percona and oak-online-alter-table from openarkkit.

Also, as with any possibly destructive changes, test the procedure on a staging (or other non-production use, but production data and schema) database.

share|improve this answer

Since you are trying to update only the table structure from the sandbox to the production, you need to ensure that you do not accidentally damage the production database:

a) Dump the production database structure into a staging database

mysqldump -R --add-drop-table --no-data -h productionhostname -u productionUSER -pproductionPASSWORD productiondbname | mysql -u stagingUSER -pstagingPASSWORD -h staginghostname stagingdatabasename

b) Use a tool like SQL Yog or MySQL Workbench to generate a script to update the structure of the staging database from the sandbox

c) Test the generated script on the staging database

d) Execute the generated script on the production database.

I am not sure if this process can be automated since at times phyiscal database changes need accompanying data updates

share|improve this answer
  1. export data from parent server and remove the table.
  2. import only table structure from sandbox into production.
  3. now once again import data into production

    keep backup of your db ;

share|improve this answer
    
Thank you for your answer Arun. Unfortunately this will result in a server downtime which is what I'm trying to avoid. :( If I can't come up with anything then I will have to go with your solutions though, so thank you. –  Don Quixote Oct 19 '12 at 5:56
    
u can create as temp and rename once you are finished ,so downtime will be only seconds. –  Arun Killu Oct 19 '12 at 5:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.