0

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?

Thanks...

Improve this question
3
  • 1
    This might can be solved a layer above database. Most frameworks are offering helper for migration of database.
    – frlan
    Commented Aug 7, 2014 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
    Commented Aug 7, 2014 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
    Commented Aug 7, 2014 at 11:03

3 Answers 3

Reset to default
1

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.).

Improve this answer
4
  • 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
    Commented Aug 7, 2014 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
    Commented Aug 7, 2014 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
    Commented Aug 7, 2014 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
    Commented Aug 7, 2014 at 11:04
1

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!!!

Improve this answer
0

You might also consider Skeema.

Skeema is a pure-SQL schema management utility. Simply track your desired schema state in a repo of CREATE statements, and the tool figures out how to apply any changes to your tables and routines. You won't need to code migrations, use obscure DSLs, or write verbose XML ever again.

In other words, you can use your current tables in development as the canonical reference, and Skeema will figure out what ALTER TABLE statements are needed to upgrade the testing schema to match. Sort of similar to mysqldbcompare in the Rathish's answer, but probably more featureful.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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