MySQL + Git-Struktur-Synchronisation

    Um Projektdateien zu synchronisieren und den Verlauf zu speichern, verwenden wir Versionskontrollsysteme, zum Beispiel Git. Als ich jedoch eine Frage zur Versionskontrolle der Struktur der MySQL-Datenbank hatte, konnte ich keine zufriedenstellende Lösung finden.
    Ich stelle fest, dass es in vielen Frameworks und ORMs notwendige Mechanismen gibt - Migration, Versionierung usw. Aber für die native Arbeit mit MySQL müssen Sie alles mit Stiften machen. Und es entstand die Idee, ein automatisches System zur Nachverfolgung von Änderungen zu schaffen.

    Herausforderung


    Ich wollte die Datenbankstruktur auf dem Entwicklungsserver ändern, sie automatisch auf dem Produktionsserver aktualisieren und auch den Verlauf aller Änderungen in Git anzeigen, da sie bereits zur Steuerung des Codes verwendet wurden. Und damit alles kostenlos und einfach ist!
    Dazu benötigen Sie Informationen zu allen Änderungsanforderungen (CREATE, ALTER, DROP).

    Entscheidungsbeginn


    MySQL unterstützt drei Arten der Verwaltung von Protokollen: Fehlerprotokolle (Fehlerprotokoll), Protokolle aller Abfragen (allgemeines Protokoll) und langsame Abfrageprotokolle (langsames Protokoll).
    Ich habe die erste Option noch nicht verwendet, aber es gibt Ideen (Details unten). Nun zu den beiden anderen Optionen.
    Protokolle können entweder in MySQL-Tabellen oder in Dateien geschrieben werden. Das Protokolldateiformat ist ziemlich unpraktisch und ich habe mich für die Verwendung von Tabellen entschieden.

    Achtung, da es sich um ALLE MySQL-Protokolle handelt, sollte diese Lösung nur auf einem Dev-Server ohne MySQL-Last verwendet werden!

    Ein wichtiger Punkt ist die Definition der Datenbank, an die die Abfrage gesendet wird, da diese Informationen möglicherweise nicht im SQL-Text der Abfrage selbst enthalten sind.
    CREATE TABLE  /*DB_NAME.*/TABLE_NAME
    

    Es stellte sich heraus, dass das allgemeine Protokoll nur die Server-Thread-Nummer schreibt, und um die Datenbank zu bestimmen, müsste man nach einem Datensatz für diesen Thread mit der verwendeten Datenbank suchen. Darüber hinaus enthalten die Protokolle Informationen zum Herstellen und Trennen einer Verbindung zum Server.

    Die Struktur von mysql.general_log


    Aber slow_log hat gerade alles gefunden, was Sie brauchen: Erstens enthalten die Protokolle nur Informationen zu den Anforderungen, und zweitens wird der Name der Datenbank gespeichert, in deren Kontext die Anforderungen gespeichert werden.

    Mysql.slow_log-Struktur Das Einrichten eines


    langsamen Protokolls zum Aufzeichnen aller Anforderungen in my.cnf ist sehr einfach. Zum
    log-output=TABLE
    slow_query_log = 1
    long_query_time = 0
    log_slow_admin_statements = 1

    Aufzeichnen von ALTER-Anforderungen sind log_slow_admin_statements erforderlich.

    Protokollverarbeitung


    Wir müssen also ständig alle Abfragen aufgreifen, die Anforderungen zur Änderung der Datenbankstruktur auswählen und den Rest löschen.

    Die Tabelle mysql.slow_log enthält kein Schlüsselfeld und kann nicht gesperrt werden (und daher Einträge teilweise löschen). Deshalb erstellen wir einen Tisch, der zu uns passt.

    Struktur change_structure_log


    Für die Protokollrotation eine kleine Prozedur:
    USE mysql;
    DELIMITER $$
    CREATE PROCEDURE `change_structure_log_rotate`()
    BEGIN
    -- Definition start
    drop table if exists slow_log_copy;
    CREATE TABLE slow_log_copy LIKE slow_log;
    RENAME TABLE slow_log TO slow_log_old, slow_log_copy TO slow_log;
    insert into change_structure_log (start_time,query_time,sql_text, db) select start_time, query_time, sql_text,db from slow_log_old where sql_text like "ALTER%" OR sql_text like "CREATE%" OR sql_text like "DROP%";
    drop table slow_log_old;
    -- Definition end
    END
    $$
    


    Und es kann zum MySQL-Scheduler hinzugefügt werden:
    CREATE EVENT `event_archive_mailqueue`
      ON SCHEDULE EVERY 5 MINUTE STARTS CURRENT_TIMESTAMP
      ON COMPLETION NOT PRESERVE
      ENABLE
      COMMENT ''  DO
    call change_structure_log_rotate();
    


    Wir haben also eine Tabelle mit allen Anträgen auf Umstrukturierung. Jetzt werden wir ein kleines Skript schreiben, um es zu verarbeiten. Ich werde keine bestimmte Sprache verwenden (ich persönlich schreibe in PHP, aber wegen der großen Anzahl von Abhängigkeiten im Code macht es keinen Sinn, den Code hochzuladen).

    Also:
    1. Wir durchlaufen alle Einträge in der Tabelle change_structure_log.
    2. Für sql_text ziehen wir regelmäßig den Datenbanknamen heraus, falls er beispielsweise existiert.
    ^ALTER\s+TABLE\s+(?:(?:ONLINE|OFFLINE)\s+)?(?:(?:IGNORE)\s+)?(?:([^\s\.]+)\.\s*)?([^\s\.]+)
    3. Wenn der Name db in der Abfrage nicht angegeben ist, verwenden Sie ihn aus dem Feld db.
    4. Wir schreiben mit Git alle Datensätze der entsprechenden Datenbank in den Projektordner. Beispiel: 20140508150500.sql.log. Für Abfragen ohne Datenbank schreiben wir zu Beginn mit $ DB;
    5. Löschen Sie alle verarbeiteten Datensätze.

    In unserem Projektordner gibt es also neue Dateien mit Anforderungen zum Ändern der Datenbank. Jetzt können wir sie in unserem Git-Client im normalen Modus festschreiben.

    Als nächstes schreiben wir auf dem Produktionsserver ein Skript, das das Erscheinen neuer Dateien verfolgt und sie in mysql ausführt. Wenn wir also das Git-Repository auf dem Produktionsserver zusammen mit dem Code aktualisieren, ändern wir die Datenbank in den Status auf dem Entwicklungsserver.

    Upd. Außerdem (an der Eingabeaufforderung von DsideSPb ) können Sie den Hook für das Git-Post-Checkout verwenden, wodurch die Update-Iteration kontinuierlich und ohne externe Listener ausgeführt wird.

    Ich muss gleich sagen, dass diese Lösung ziemlich primitiv ist und nicht viele Git-Funktionen unterstützt. Darauf aufbauend können wir jedoch noch coolere Dinge tun: indem wir bestimmte Tabellen ändern - zum Beispiel unsere ORM-Dateien automatisch ändern.
    Oder erstellen Sie automatisch Yaml-Schemas - mit einem beliebigen MySQL-Client ohne zusätzliche Plugins.
    Es ist beispielsweise auch möglich, Änderungen an Daten in bestimmten Tabellen zu verfolgen, ohne die Datenbankstruktur selbst zu ändern (Trigger usw.), was für verschiedene CMS nützlich sein kann.

    PS Wenn wir auch etwas über langsame Abfragen lernen möchten, können wir dies in unser System integrieren. Dazu müssen wir den Filter aus der Prozedur entfernen und in unserem Skript eine Anfrage für langsame Abfragen stellen und diese speichern.

    Jetzt auch beliebt: