La réplication est l'une des fonctionnalités bien connues qui vous permettent de créer une copie identique d'une base de données. Il est pris en charge dans presque tous les systèmes de gestion de bases de données relationnelles (SGBDR). La réplication offre des avantages importants, notamment une haute disponibilité et un équilibrage de charge. Mais que se passe-t-il si la réplication est requise entre deux bases de données avec une structure différente, comme MySQL et PostgreSQL? Est-il possible de répliquer en continu les modifications d'une base de données MySQL vers une base de données PostgreSQL? La réponse à cette question est l' outil de réplication pg_chameleon .
Pour la réplication de changement continu, pg_chameleon utilise la bibliothèque de réplication MySQL, qui vous permet d'obtenir des copies logiques des lignes de la base de données MySQL, qui sont converties en objet jsonb. La fonction pl / pgsql dans Postgres décode l'objet jsonb et reproduit les modifications dans la base de données Postgres. Pour configurer ce type de réplication, la variable binlog_format de la base de données MySQL doit être définie sur ROW (chaîne).
Quelques points à savoir avant de mettre en place cet outil:
- Les tables qui doivent être répliquées doivent avoir une clé primaire.
- L'outil fonctionne dans les versions de PostgreSQL supérieures à 9.5 et du système MySQL supérieures à 5.5
- Pour configurer cette réplication, la variable binlog_format doit être définie sur ROW.
- La version Python doit être supérieure à 3,3
Lors du démarrage de la réplication, pg_chameleon récupère les données de MySQL au format CSV avec une répartition en groupes d'une certaine longueur pour éviter une surcharge de mémoire. Ces données sont transférées vers Postgres avec la commande COPY. Si la copie échoue, la commande INSERT est exécutée, ce qui peut ralentir le processus. Si la commande INSERT échoue, la ligne est perdue.
Pour répliquer les modifications de MySQL, pg_chameleon émule le comportement de la réplique MySQL (esclave). Cela crée un schéma dans Postgres, effectue le chargement initial des données, se connecte au protocole de réplication MySQL et copie les lignes de la table Postgres. Dans le même temps, les fonctions Postgres correspondantes fournissent le décodage et les modifications de chaîne. Cela revient à stocker des journaux de transfert dans des tables Postgres et à les appliquer à un schéma Postgres. La création d'un schéma de base de données Postgres à l'aide d'un langage de description de données n'est pas requise. Pour les tables spécifiées lors de la configuration de la réplication, l'outil pg_chameleon le fait automatiquement. Si vous devez convertir n'importe quel type d'une manière spécifique, vous pouvez le spécifier dans le fichier de configuration.
Voici un exercice que vous pouvez expérimenter. Utilisez les options fournies si elles répondent pleinement à vos besoins. Nous avons effectué ces tests sur CentOS Linux version 7.4.
Préparation de l'environnement
Configurer Percona Server pour MySQL
Installez MySQL version 5.7 et ajoutez les options appropriées pour la réplication.
Dans cet exercice, j'ai installé Percona Server pour MySQL version 5.7 à l'aide du référentiel YUM.
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
Pour pg_chameleon, les paramètres suivants doivent être configurés dans le fichier my.cnf (fichier avec les paramètres du serveur MySQL). Vous pouvez ajouter les options suivantes au fichier /etc/my.cnf
binlog_format= ROW binlog_row_image=FULL log-bin = mysql-bin server-id = 1
Maintenant, après avoir inclus les paramètres ci-dessus dans le fichier my.cnf, démarrez le serveur MySQL.
$ service mysql start
Obtenez le mot de passe temporaire pour le compte root à partir du fichier mysqld.log et réinitialisez le mot de passe root à l'aide de la commande mysqladmin.
$ grep "temporary" /var/log/mysqld.log $ mysqladmin -u root -p password 'Secret123!'
Connectez-vous maintenant à votre propre instance de la base de données MySQL et créez un exemple de schéma / tables. J'ai également créé une table emp pour vérification.
$ 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))"
Créez un utilisateur pour configurer la réplication à l'aide de l'outil pg_chameleon et accordez-lui les droits appropriés en suivant ces étapes.
$ 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;
Lors de la création d'un utilisateur sur un serveur MySQL ('usr_replica' @ '%'), il peut être nécessaire de remplacer le caractère "%" par l'adresse IP ou le nom d'hôte correspondant du serveur exécutant pg_chameleon.
Configurer PostgreSQL
Installez PostgreSQL et exécutez une copie de la base de données.
Suivez ces étapes pour installer la version 10.x de PostgreSQL.
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
Comme vous pouvez le voir dans les journaux suivants, vous devez créer un utilisateur dans PostgreSQL avec lequel pg_chameleon pourra écrire des données modifiées dans PostgreSQL. Créez également une base de données cible.
postgres=# CREATE USER usr_replica WITH ENCRYPTED PASSWORD 'secret'; CREATE ROLE postgres=# CREATE DATABASE db_replica WITH OWNER usr_replica; CREATE DATABASE
Étapes pour installer et configurer la réplication à l'aide de pg_chameleon
Étape 1. Dans cet exercice, j'ai installé l'interpréteur Python version 3.6 et pg_chameleon version 2.0.8 en suivant ces étapes. Si la version requise de l'interpréteur Python est déjà installée, vous pouvez ignorer les étapes pour l'installer. Nous pouvons créer un environnement virtuel si le système d'exploitation n'inclut pas Python version 3.x par défaut.
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
Étape 2. Cet outil nécessite un fichier de configuration où les informations sur les serveurs source et cible seront stockées, et un dossier pour stocker les journaux. Pour que pg_chameleon crée un modèle de fichier de configuration et les dossiers correspondants, utilisez la commande suivante.
$ chameleon set_configuration_files
Lorsque cette commande est exécutée, les résultats suivants s'affichent. Ils montrent que cette commande a créé plusieurs dossiers et fichiers à l'endroit où vous l'avez exécutée.
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
Copiez l'exemple de fichier de configuration dans un autre fichier, par exemple default.yml
$ cd .pg_chameleon/configuration/ $ cp config-example.yml default.yml
Voici à quoi ressemble mon fichier default.yml après y avoir inclus tous les paramètres nécessaires. Dans ce fichier, si nécessaire, vous pouvez spécifier la conversion des types de données, des tables à ignorer lors de la réplication et des événements du langage de manipulation des données à ignorer pour la liste de tables sélectionnée.
--- #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:
Étape 3. Créez une réplique (base de données cible) à l'aide de la commande:
$ chameleon create_replica_schema --debug
La commande ci-dessus crée un schéma et neuf tables dans la base de données PostgreSQL spécifiée dans le fichier .pg_chameleon / configuration / default.yml. Ces tables sont nécessaires pour contrôler la réplication de la base de données source vers la cible. La même chose peut être vue dans le prochain magazine.
db_replica=# \dn List of schemas Name | Owner
Étape 4. Ajoutez les données de la base de données source à pg_chameleon à l'aide de la commande suivante. Spécifiez le nom de la base de données source comme spécifié dans le fichier de configuration. Dans cet exemple, le nom de la base de données source est mysql et la cible est la base de données Postgres, définie comme pg_conn.
$ chameleon add_source --config default --source mysql --debug
Après avoir exécuté la commande spécifiée, vous verrez que les données de la base de données d'origine sont ajoutées à la table t_sources.
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
Étape 5. Initialisez la réplique (base de données cible) à l'aide de la commande suivante. Spécifiez la base de données source à partir de laquelle les modifications sont répliquées dans la base de données PostgreSQL.
$ chameleon init_replica --config default --source mysql --debug
L'initialisation comprend les tâches suivantes sur le serveur MySQL (source).
- Vider le cache de la table et définir le verrou en lecture seule
- Obtenez les coordonnées de la base de données source
- Copier les données
- Débloquer
La commande ci-dessus crée automatiquement le schéma de la base de données Postgres cible.
Dans le fichier default.yml, nous avons mentionné les mappages de schémas suivants (schema_mappings).
schema_mappings: sakila: sch_sakila
Un nouveau schéma scott a été créé dans la base de données cible db_replica.
db_replica=# \dn List of schemas Name | Owner
Étape 6. Maintenant, lancez la réplication à l'aide de la commande suivante.
$ chameleon start_replica --config default --source mysql
Étape 7. Vérifiez l'état et les erreurs de réplication à l'aide des commandes suivantes.
$ chameleon show_status --config default $ chameleon show_errors
Voici à quoi ressemble l'état de réplication:
$ 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
Vous pouvez maintenant voir que les modifications sont constamment répliquées de la base de données MySQL vers la base de données PostgreSQL.
Étape 8. Pour la vérification, vous pouvez insérer un enregistrement dans la table de base de données MySQL que nous avons créée pour vérifier la réplication dans la base de données Postgres.
$ 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)
Le journal ci-dessus montre que l'enregistrement inséré dans la table MySQL a été répliqué dans la table de la base de données Postgres.
Vous pouvez également ajouter plusieurs bases de données de réplication source à la base de données Postgres cible.
Lien
À partir de cette documentation, vous pouvez découvrir les nombreuses fonctionnalités supplémentaires disponibles dans pg_chameleon.