We have two servers: one for the developer (me) and one for the tester.

While working on the app, I sometimes make little changes in tables's schemas. (Like a new field, or a field type)

I'm trying to find a way to update the TEST's schema from the DEV without altering the TEST data.

I tried to dump the DEV structure only, execute it on the TEST db, and then fill it with the TEST data. But the new schema is replaced with the old TEST schema...

Do you have a way to do that ? Or will my tester be forced to fill the database everytime we update?


  • 1
    This might can be solved a layer above database. Most frameworks are offering helper for migration of database. – frlan Aug 7 '14 at 8:52
  • I must be missing something as cant you simply run the same ALTER TABLE statement on TEST that you ran on DEV ? if you are adding a new column or changing the datatype then this should be fine as a script. – DamagedGoods Aug 7 '14 at 10:42
  • Because my workflow is really bad. I have no changelog, and can't remember the changes I made. That's why I'm looking for an automated solution... – FLX Aug 7 '14 at 11:03

Take a look here (from one of the founders of stackexchange). Be ABSOLUTELY sure to follow the five links therein to K. Scott Allen's series on "the philosophy and practice of database version control" - in the top 1-2% of the best technical writing that I have ever read.

There are many tools (see here and here) for this (pay and Open Source) - the one that seems to get the most coverage around here is liquibase (disclaimer - haven't used any - my DB versioning involved using v. old Unix VCS software.).

  • Thanks for these links. I'm clearly not enough skilled for the moment and I have no time to level up to be able to do something like this, so I think that I will keep making changes manually for this project. But they will be very helpful in some weeks. – FLX Aug 7 '14 at 10:46
  • One thing I do know about IT projects is that they're like babies - they tend to grow, and if you don't impose discipline now, it'll be much more difficult later on :-). Seriously, if you don't have some sort of procedure, you'll soon be in the position of constantly having to fire-fight and get less and less work done. Start with your ordinary VCS - you do have one, right? Start putting scripts/updates into so that you can update incrementally, rather than having to "fill the database everytime we update". Best of luck. – Vérace Aug 7 '14 at 10:52
  • In fact, I never used a VCS (shame on me). I tried GIT but wasn't able to understand it quickly. I know I will need one soon but it's hard to find the time. – FLX Aug 7 '14 at 11:02
  • Find it now, or find more later. Git is complex (even Torvalds says so - and he wrote it!) - try Subversion maybe? – Vérace Aug 7 '14 at 11:04

I am using mysqldbcompare to check the difference between the two databases. I hope it may help:

mysqldbcompare --server1=username:password@hostname:3306 --server2=username:pasword@localhost:3306 DB1:DB1 --run-all-test --difftype=sql --skip-checksum-table --skip-row-count --skip-data-check  changes-for=server2 
  • Replace the DB1:DB1 with database name.
  • Replace the username, password, port and hostname.
  • This parameters will produce output in SQL format and it does not check the table data and row count difference.
  • Send this output to file. Verify and execute the file in server2.

You may check here for official documentation. Give it a try!!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.