Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

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

by erix (Parson)
on May 22, 2019 at 17:32 UTC ( #11100374=note: print w/replies, xml ) Need Help??


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

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

Replies are listed 'Best First'.
Re^2: Tracking and deploying changes in (MySql/Maria) DB schema ...
by holli (Monsignor) 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.
      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

        my crazy idea: Have a cron job doing hourly (or so)
        mysqldump ... git commit ...
        If the cron job is scheduled often enough, it would register the modifications with enough granularity.
Re^2: Tracking and deploying changes in (MySql/Maria) DB schema ...
by LanX (Archbishop) 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

      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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://11100374]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (4)
As of 2019-12-15 17:44 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?