Tracking and deploying changes in (MySql/Maria) DB schema ...

LanX has asked for the wisdom of the Perl Monks concerning the following question:

Looking for inspiration ...

Are there recommended solutions to track and replicate changes on MariaDB tables?

I was hoping that DBs like information_schema or mysql might log ALTER TABLE queries.

My research pointed me to 'log_bin':

find the binary log for the relevant time period, use mysqlbinlog to convert it to SQL, then grep for the relevant table name to find the alter table statement

I'd be interested to know about alternatives.

background

My client likes to change from time to time the data model for some of my applications and tends to inform me per email "Please change ..." afterwards.

To make it more difficult this models are used on several different DB Severs.

My application is already adapting to the local model on a server and checking the schema against a "master" server, but I'd like to have better control and allow a semi-automatic update.

A manual solution would be to train my client to record every ALTER TABLE manually after changes

... well ... I'm not too keen for the resulting social tension of enforcing protocols.

Cheers Rolf
_{(addicted to the Perl Programming Language :)

Wikisyntax for the Monastery
FootballPerl is like chess, only without the dice}

Comment on Tracking and deploying changes in (MySql/Maria) DB schema ... Select or Download Code

Replies are listed 'Best First'.
Re: Tracking and deploying changes in (MySql/Maria) DB schema ... by erix (Prior) on May 22, 2019 at 17:32 UTC
I see MariaDB has an auditing module... if that isn't overkill. Otherwise it can't be very hard to pull together a list of checksums of table structure, with their constraints/indexes (and maybe a few more things). Then run that daily/hourly/whatever, redirect to timestamped files, and make sure you notice when a diff appears. (Postgres has a configuration setting 'log_statement' that can be set to 'ddl', which logs CREATE, ALTER, and DROP statements. Ideal, but now you have to go through the social tension of getting your client to migrate to postgresql :P ) `[1]` postgres runtime config - Logging what	[reply] [d/l]
Re^2: Tracking and deploying changes in (MySql/Maria) DB schema ... by holli (Abbot) on May 22, 2019 at 21:11 UTC
That's probably the simplest way. `mysqldump --no-data -u someuser -p mydatabase > current.sql` and then diff it to `howitshouldbe.sql` created in the same manner. holli You can lead your users to water, but alas, you cannot drown them.	[reply] [d/l] [select]
Re^3: Tracking and deploying changes in (MySql/Maria) DB schema ... by LanX (Saint) on May 22, 2019 at 22:55 UTC
Maybe I should have been more explicit about needing the incremental steps (i.e. ALTER TABLEs) and not just a diff between states. Thought it's obviously better done this way. ( and easier documented) Cheers Rolf _{(addicted to the Perl Programming Language :) Wikisyntax for the Monastery FootballPerl is like chess, only without the dice}	[reply]
Re^4: Tracking and deploying changes in (MySql/Maria) DB schema ... by soonix (Chancellor) on May 23, 2019 at 00:58 UTC
Re^5: Tracking and deploying changes in (MySql/Maria) DB schema ... by karlgoethebier (Abbot) on May 23, 2019 at 11:30 UTC
Some notes below your chosen depth have not been shown here
Re^4: Tracking and deploying changes in (MySql/Maria) DB schema ... by erix (Prior) on May 23, 2019 at 21:01 UTC
Re^5: Tracking and deploying changes in (MySql/Maria) DB schema ... by LanX (Saint) on May 23, 2019 at 21:34 UTC
Some notes below your chosen depth have not been shown here
Re^2: Tracking and deploying changes in (MySql/Maria) DB schema ... by LanX (Saint) on May 22, 2019 at 19:28 UTC
Yeah thanks ...I've already been told that professional projects only use postgres, python and mac os ... Cheers Rolf _{(addicted to the Perl Programming Language :) Wikisyntax for the Monastery FootballPerl is like chess, only without the dice}	[reply]
Re^3: Tracking and deploying changes in (MySql/Maria) DB schema ... by karlgoethebier (Abbot) on May 23, 2019 at 11:24 UTC
It is a pity that they are gone. «The Crux of the Biscuit is the Apostrophe» `perl -MCrypt::CBC -E 'say Crypt::CBC->new(-key=>'kgb',-cipher=>"Blowfish")->decrypt_hex($ENV{KARL});'`Help	[reply] [d/l]
Re: Tracking and deploying changes in (MySql/Maria) DB schema ... by Your Mother (Archbishop) on May 22, 2019 at 16:59 UTC
We have a home-rolled, replay from the start, SQL delta management system at work that’s actually pretty good, if slow, so I have no experience with this—DBIx::Class::Schema::Versioned—but I always wanted to try it.	[reply]
Re^2: Tracking and deploying changes in (MySql/Maria) DB schema ... by LanX (Saint) on May 22, 2019 at 19:36 UTC
Well dbix-class would require changing the code base. Home rolling sounds like the second best solution. (I'd probably also try playing around with triggers on our dev server) Probably the easiest way is political: I send an email always requiring explicit ALTER TABLE code for each change and prepare playing the finger pointing game if something crashes. Cheers Rolf _{(addicted to the Perl Programming Language :) Wikisyntax for the Monastery FootballPerl is like chess, only without the dice}	[reply]
Re: Tracking and deploying changes in (MySql/Maria) DB schema ... by talexb (Chancellor) on May 24, 2019 at 04:26 UTC
At the Toronto Perlmongers, we had a talk recently about the sqitch tool (ugh -- can't find the Perlmongers video). It's something that Ovid recommends in this blog post. Alex / talexb / Toronto Thanks PJ. We owe you so much. Groklaw -- RIP -- 2003 to 2013.	[reply]
Re^2: Tracking and deploying changes in (MySql/Maria) DB schema ... by LanX (Saint) on May 24, 2019 at 17:01 UTC
Thanks this looks really nice and really powerful. > (ugh -- can't find the Perlmongers video) Well there are videos in https://sqitch.org/docs/ , which are not best quality but give a solid impression when combined with the available slides. I need to dig deeper to see if and how I can adapt my colleagues to the tool. ;-) Cheers Rolf _{(addicted to the Perl Programming Language :) Wikisyntax for the Monastery FootballPerl is like chess, only without the dice}	[reply]