Die Replikation ist eine der bekannten Funktionen, mit denen Sie eine identische Kopie einer Datenbank erstellen können. Es wird in fast jedem relationalen Datenbankverwaltungssystem (RDBMS) unterstützt. Die Replikation bietet erhebliche Vorteile, insbesondere hohe Verfügbarkeit und Lastausgleich. Was aber, wenn eine Replikation zwischen zwei Datenbanken mit unterschiedlicher Struktur wie MySQL und PostgreSQL erforderlich ist? Ist es möglich, Änderungen von einer MySQL-Datenbank kontinuierlich in eine PostgreSQL-Datenbank zu replizieren? Die Antwort auf diese Frage ist das Replikationstool pg_chameleon .
Für die Replikation kontinuierlicher Änderungen verwendet pg_chameleon die MySQL-Replikationsbibliothek, mit der Sie logische Kopien von Zeilen aus der MySQL-Datenbank abrufen können, die in ein jsonb-Objekt konvertiert werden. Die Funktion pl / pgsql in Postgres dekodiert das jsonb-Objekt und reproduziert die Änderungen in der Postgres-Datenbank. Um diesen Replikationstyp zu konfigurieren, muss die Variable binlog_format für die MySQL-Datenbank auf ROW (Zeichenfolge) festgelegt werden.
Einige Punkte, die Sie vor dem Einrichten dieses Tools beachten sollten:
- Die Tabellen, die repliziert werden müssen, müssen einen Primärschlüssel haben.
- Das Tool funktioniert in Versionen von PostgreSQL über 9.5 und dem MySQL-System über 5.5
- Um diese Replikation zu konfigurieren, muss die Variable binlog_format auf ROW gesetzt werden.
- Die Python-Version muss höher als 3.3 sein
Beim Starten der Replikation ruft pg_chameleon Daten aus MySQL im CSV-Format mit einer Aufteilung in Gruppen einer bestimmten Länge ab, um eine Speicherüberlastung zu vermeiden. Diese Daten werden mit dem Befehl COPY an Postgres übertragen. Wenn das Kopieren fehlschlägt, wird der Befehl INSERT ausgeführt, was den Prozess verlangsamen kann. Wenn der Befehl INSERT fehlschlägt, geht die Zeile verloren.
Um Änderungen von MySQL zu replizieren, emuliert pg_chameleon das Verhalten des MySQL-Replikats (Slave). Dadurch wird ein Schema in Postgres erstellt, das anfängliche Laden der Daten durchgeführt, eine Verbindung zum MySQL-Replikationsprotokoll hergestellt und die Zeilen in die Postgres-Tabelle kopiert. Gleichzeitig sorgen die entsprechenden Postgres-Funktionen für die Dekodierung und Änderung von Zeichenfolgen. Dies ähnelt dem Speichern von Übertragungsprotokollen in Postgres-Tabellen und deren Anwendung auf ein Postgres-Schema. Das Erstellen eines Postgres-Datenbankschemas mit einer beliebigen Datenbeschreibungssprache ist nicht erforderlich. Für die Tabellen, die bei der Konfiguration der Replikation angegeben wurden, führt das Tool pg_chameleon dies automatisch aus. Wenn Sie Typen auf eine bestimmte Weise konvertieren müssen, können Sie dies in der Konfigurationsdatei angeben.
Das Folgende ist eine Übung, mit der Sie experimentieren können. Verwenden Sie die bereitgestellten Optionen, wenn sie Ihren Anforderungen vollständig entsprechen. Wir haben diese Tests unter CentOS Linux Version 7.4 durchgeführt.
Umweltvorbereitung
Konfigurieren Sie Percona Server für MySQL
Installieren Sie MySQL Version 5.7 und fügen Sie die entsprechenden Optionen für die Replikation hinzu.
In dieser Übung habe ich Percona Server für MySQL Version 5.7 mithilfe des YUM-Repositorys installiert.
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm yum install Percona-Server-server-57 echo "mysql ALL=(ALL) NOPASSWD: ALL" >> /etc/sudoers usermod -s /bin/bash mysql sudo su - mysql
Für pg_chameleon müssen die folgenden Parameter in der Datei my.cnf konfiguriert werden (Datei mit MySQL-Serverparametern). Sie können der Datei /etc/my.cnf die folgenden Optionen hinzufügen
binlog_format= ROW binlog_row_image=FULL log-bin = mysql-bin server-id = 1
Nachdem Sie die obigen Parameter in die Datei my.cnf aufgenommen haben, starten Sie nun den MySQL-Server.
$ service mysql start
Rufen Sie das temporäre Kennwort für das Root-Konto aus der Datei mysqld.log ab und setzen Sie das Root-Passwort mit dem Befehl mysqladmin zurück.
$ grep "temporary" /var/log/mysqld.log $ mysqladmin -u root -p password 'Secret123!'
Stellen Sie nun eine Verbindung zu Ihrer eigenen Instanz der MySQL-Datenbank her und erstellen Sie ein Beispielschema / Tabellen. Ich habe auch eine Emp-Tabelle zur Überprüfung erstellt.
$ wget http://downloads.mysql.com/docs/sakila-db.tar.gz $ tar -xzf sakila-db.tar.gz $ mysql -uroot -pSecret123! < sakila-db/sakila-schema.sql $ mysql -uroot -pSecret123! < sakila-db/sakila-data.sql $ mysql -uroot -pSecret123! sakila -e "create table emp (id int PRIMARY KEY, first_name varchar(20), last_name varchar(20))"
Erstellen Sie einen Benutzer, um die Replikation mit dem Tool pg_chameleon zu konfigurieren, und gewähren Sie ihm die entsprechenden Rechte, indem Sie die folgenden Schritte ausführen.
$ mysql -uroot -p create user 'usr_replica'@'%' identified by 'Secret123!'; GRANT ALL ON sakila.* TO 'usr_replica'@'%'; GRANT RELOAD, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'usr_replica'@'%'; FLUSH PRIVILEGES;
Beim Erstellen eines Benutzers auf einem MySQL-Server ('usr_replica' @ '%') muss möglicherweise das Zeichen "%" durch die entsprechende IP-Adresse oder den Hostnamen des Servers ersetzt werden, auf dem pg_chameleon ausgeführt wird.
Konfigurieren Sie PostgreSQL
Installieren Sie PostgreSQL und führen Sie eine Kopie der Datenbank aus.
Befolgen Sie diese Schritte, um PostgreSQL Version 10.x zu installieren.
yum install https://yum.postgresql.org/10/redhat/rhel-7.4-x86_64/pgdg-centos10-10-2.noarch.rpm yum install postgresql10* su - postgres $/usr/pgsql-10/bin/initdb $ /usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data start
Wie Sie den folgenden Protokollen entnehmen können, müssen Sie in PostgreSQL einen Benutzer erstellen, mit dem pg_chameleon geänderte Daten in PostgreSQL schreiben kann. Erstellen Sie auch eine Zieldatenbank.
postgres=# CREATE USER usr_replica WITH ENCRYPTED PASSWORD 'secret'; CREATE ROLE postgres=# CREATE DATABASE db_replica WITH OWNER usr_replica; CREATE DATABASE
Schritte zum Installieren und Konfigurieren der Replikation mit pg_chameleon
Stufe 1. In dieser Übung habe ich den Python-Interpreter Version 3.6 und pg_chameleon Version 2.0.8 installiert, indem ich die folgenden Schritte ausgeführt habe. Wenn Sie bereits die erforderliche Version des Python-Interpreters installiert haben, können Sie die Schritte zum Installieren überspringen. Wir können eine virtuelle Umgebung erstellen, wenn das Betriebssystem standardmäßig nicht Python Version 3.x enthält.
yum install gcc openssl-devel bzip2-devel wget cd /usr/src wget https://www.python.org/ftp/python/3.6.6/Python-3.6.6.tgz tar xzf Python-3.6.6.tgz cd Python-3.6.6 ./configure --enable-optimizations make altinstall python3.6 -m venv venv source venv/bin/activate pip install pip --upgrade pip install pg_chameleon
Stufe 2. Für dieses Tool ist eine Konfigurationsdatei erforderlich, in der Informationen zu den Quell- und Zielservern gespeichert werden, sowie ein Ordner zum Speichern von Protokollen. Verwenden Sie den folgenden Befehl, damit pg_chameleon eine Konfigurationsdateivorlage und entsprechende Ordner erstellt.
$ chameleon set_configuration_files
Wenn dieser Befehl ausgeführt wird, werden die folgenden Ergebnisse angezeigt. Sie zeigen, dass dieser Befehl mehrere Ordner und Dateien an der Stelle erstellt hat, an der Sie ihn ausgeführt haben.
creating directory /var/lib/pgsql/.pg_chameleon creating directory /var/lib/pgsql/.pg_chameleon/configuration/ creating directory /var/lib/pgsql/.pg_chameleon/logs/ creating directory /var/lib/pgsql/.pg_chameleon/pid/ copying configuration example in /var/lib/pgsql/.pg_chameleon/configuration//config-example.yml
Kopieren Sie die Beispielkonfigurationsdatei in eine andere Datei, z. B. default.yml
$ cd .pg_chameleon/configuration/ $ cp config-example.yml default.yml
So sieht meine Datei default.yml aus, nachdem alle erforderlichen Parameter darin enthalten sind. In dieser Datei können Sie optional die Konvertierung von Datentypen, Tabellen, die während der Replikation übersprungen werden sollen, und Ereignisse der Datenbearbeitungssprache angeben, die für die ausgewählte Liste von Tabellen ignoriert werden sollen.
--- #global settings pid_dir: '~/.pg_chameleon/pid/' log_dir: '~/.pg_chameleon/logs/' log_dest: file log_level: info log_days_keep: 10 rollbar_key: '' rollbar_env: '' # type_override allows the user to override the default type conversion into a different one. type_override: "tinyint(1)": override_to: boolean override_tables: - "*" #postgres destination connection pg_conn: host: "localhost" port: "5432" user: "usr_replica" password: "secret" database: "db_replica" charset: "utf8" sources: mysql: db_conn: host: "localhost" port: "3306" user: "usr_replica" password: "Secret123!" charset: 'utf8' connect_timeout: 10 schema_mappings: sakila: sch_sakila limit_tables: # - delphis_mediterranea.foo skip_tables: # - delphis_mediterranea.bar grant_select_to: - usr_readonly lock_timeout: "120s" my_server_id: 100 replica_batch_size: 10000 replay_max_rows: 10000 batch_retention: '1 day' copy_max_memory: "300M" copy_mode: 'file' out_dir: /tmp sleep_loop: 1 on_error_replay: continue on_error_read: continue auto_maintenance: "disabled" gtid_enable: No type: mysql skip_events: insert: # - delphis_mediterranea.foo #skips inserts on the table delphis_mediterranea.foo delete: # - delphis_mediterranea #skips deletes on schema delphis_mediterranea update:
Schritt 3. Erstellen Sie ein Replikat (Zieldatenbank) mit dem folgenden Befehl:
$ chameleon create_replica_schema --debug
Der obige Befehl erstellt ein Schema und neun Tabellen in der PostgreSQL-Datenbank, die in der Datei .pg_chameleon / configuration / default.yml angegeben ist. Diese Tabellen sind erforderlich, um die Replikation von der Quellendatenbank zum Ziel zu steuern. Das gleiche ist in der nächsten Zeitschrift zu sehen.
db_replica=# \dn List of schemas Name | Owner
Schritt 4. Fügen Sie die Quelldatenbankdaten mit dem folgenden Befehl zu pg_chameleon hinzu. Geben Sie den Namen der Quellendatenbank an, wie in der Konfigurationsdatei angegeben. In diesem Beispiel lautet der Name der Quellendatenbank mysql, und das Ziel ist die Postgres-Datenbank, die als pg_conn definiert ist.
$ chameleon add_source --config default --source mysql --debug
Nach dem Ausführen des angegebenen Befehls sehen Sie, dass die Daten der ursprünglichen Datenbank zur Tabelle t_sources hinzugefügt werden.
db_replica=# select * from sch_chameleon.t_sources; -[ RECORD 1 ]-------+---------------------------------------------- i_id_source | 1 t_source | mysql jsb_schema_mappings | {: } enm_status | ready t_binlog_name | i_binlog_position | b_consistent | t b_paused | f b_maintenance | f ts_last_maintenance | enm_source_type | mysql v_log_table | {t_log_replica_mysql_1,t_log_replica_mysql_2} $ chameleon show_status --config default Source id Source name Type Status Consistent Read lag Last read Replay lag Last replay ----------- ------------- ------ -------- ------------ ---------- ----------- ------------ ------------- 1 mysql mysql ready Yes N/AN/A
Schritt 5. Initialisieren Sie das Replikat (Zieldatenbank) mit dem folgenden Befehl. Geben Sie die Quellendatenbank an, aus der die Änderungen in die PostgreSQL-Datenbank repliziert werden.
$ chameleon init_replica --config default --source mysql --debug
Die Initialisierung umfasst die folgenden Aufgaben auf dem MySQL-Server (Quelle).
- Leeren Sie den Tabellencache und setzen Sie die schreibgeschützte Sperre
- Holen Sie sich die Koordinaten der Quellendatenbank
- Daten kopieren
- Entsperren
Der obige Befehl erstellt automatisch das Schema der Postgres-Zieldatenbank.
In der Datei default.yml haben wir die folgenden Schemazuordnungen (schema_mappings) erwähnt.
schema_mappings: sakila: sch_sakila
In der Zieldatenbank db_replica wurde jetzt ein neues Scott-Schema erstellt.
db_replica=# \dn List of schemas Name | Owner
Schritt 6. Starten Sie nun die Replikation mit dem folgenden Befehl.
$ chameleon start_replica --config default --source mysql
Schritt 7. Überprüfen Sie den Replikationsstatus und die Fehler mit den folgenden Befehlen.
$ chameleon show_status --config default $ chameleon show_errors
So sieht der Replikationsstatus aus:
$ chameleon show_status --source mysql Source id Source name Type Status Consistent Read lag Last read Replay lag Last replay ----------- ------------- ------ -------- ------------ ---------- ----------- ------------ ------------- 1 mysql mysql running No N/AN/A == Schema mappings == Origin schema Destination schema --------------- -------------------- sakila sch_sakila == Replica status == --------------------- --- Tables not replicated 0 Tables replicated 17 All tables 17 Last maintenance N/A Next maintenance N/A Replayed rows Replayed DDL Skipped rows
Jetzt können Sie sehen, dass die Änderungen ständig von der MySQL-Datenbank in die PostgreSQL-Datenbank repliziert werden.
Schritt 8. Zur Überprüfung können Sie einen Datensatz in die MySQL-Datenbanktabelle einfügen, die wir erstellt haben, um die Replikation in der Postgres-Datenbank zu überprüfen.
$ mysql -u root -pSecret123! -e "INSERT INTO sakila.emp VALUES (1,'avinash','vallarapu')" mysql: [Warning] Using a password on the command line interface can be insecure. $ psql -d db_replica -c "select * from sch_sakila.emp" id | first_name | last_name ----+------------+----------- 1 | avinash | vallarapu (1 row)
Aus dem obigen Protokoll ist ersichtlich, dass der in die MySQL-Tabelle eingefügte Datensatz in die Tabelle in der Postgres-Datenbank repliziert wurde.
Sie können der Postgres-Zieldatenbank auch mehrere Quellreplikationsdatenbanken hinzufügen.
Link
In dieser Dokumentation erfahren Sie mehr über die vielen zusätzlichen Funktionen von pg_chameleon.