Comment améliorer les requêtes avec la réplication MySQL / RedShift (2)

Utiliser MySQL avec une réplication RedShift

Suivez les étapes ci-dessous pour implémenter MySQL avec une réplication RedShift. D’abord, créez une table produit MySQL avec 3 champs, tels que ID, nom et prix :

$mysql> create table product(

-> id INT NOT NULL AUTO_INCREMENT,

   -> name VARCHAR(100) NOT NULL,

   -> price INT(40) NOT NULL,

  -> PRIMARY KEY ( id )

 -> );

Cette commande crée une table Product sous le format ; ID – name – price :

1    book 100

2    toys 150

3    clothes    200

Puis, créez la même table (cible) dans RedShift  avant d’extraire les données de MySQL. Cela est un point important, car certaines propriétés, comme le type de colonnes, ne peuvent être définies que lors de la création de la table.

Dans RedShift, les équipes IT doivent créer 2 tables. La première renferme l’état reconstruit de la table MySQL original, la seconde toutes les mises à jour depuis la dernière consolidation. Ces deux tables ont la même structure ; elles peuvent être fusionnées pour consolidation.

Les différences entre RedShift et MySQL

MySQL et RedShift ont des approches différentes en certains points. Par exemple, IN et FLOAT dans une table MySQL sont équivalents à INTEGER et REAL dans une table RedShift. Mais pour TIME et UNSIGNED INT dans MySQL, il n’existe pas d’équivalent dans les tables RedShift. Les développeurs doivent remplacer cela lorsqu’il l’intègre dans une table RedShift.

En matière de clés, la contrainte PRIMARY KEY dans MySQL est appliquée; ce n’est pas le cas avec RedShift. Les clés primaires doivent être identiques et valides. Lors de la consolidation avec RedShift, il se peut qu’il y ait plusieurs lignes avec une clé primaire identique – ce qui peut retourner des valeurs erronées. Les clés doivent donc être appliquées lors de la procédure ETL, qui extrait les données d’un système source – ici MySQL – pour les porter vers l’entrepôt de données – ici RedShift.

Pour créer une table identique dans RedShift, tout en définissant la distribution des clés :

$Redshift> create table product (

Id integer not null,

Name varchar (64) not null,

Price integer not null,

Primary key (id),

Distkey (name),

Compound sortkey (name, price),

);

Après avoir créé la table de destination dans RedShift, opérez le transfert et le chargement des données MySQL vers la table RedShift. Après cela, toutes les mises à jour s’exécuteront en continu. Les équipes IT doivent définir les formats des logs binaires pour éviter les pertes de données et les incohérences.

Pour cela, ajoutez les lignes suivantes au fichier my.cnf du serveur MySQL :

[mysqld]

log-bin=mysql-bin

binlog_format = ROW

Comprendre les formats et les tables des logs binaires

MySQL comporte trois types de formats de logs binaires :

• STATEMENT. Avec ce format, une seule requête met à jour toutes les lignes de la table et apparait comme une unique entrée dans le log binaire.
• ROW. Une unique requête met à jour toutes les lignes de la table et crée une entrée pour chaque ligne dans le log binaire, avec le format de la ligne. Les développeurs utilisent ce format pour suivre l’état de chaque ligne, au lieu de contrôler l’état général de la table.
• MIXED. Cette option est un format mixte qui associe les deux précédents.

Avant d’opérer un transfert de MySQL, il est important de sauvegarder les tables critiques et d’identifier le log binaire – la réplication de ce dernier envoie des événements à partir d’un instant t. Pour effectuer cette opération, créez un utilisateur avec les permissions select et lock tables.

$mysql> use test;  [test est le nom de la base de données]

$mysql> LOCK TABLES product;

$mysql> FLUSH product with READ LOCK;

$mysql> mysqldump test product > sql.dump; 

$mysql> show MASTER STATUS;

Le résultat est le suivant :

$Mysql> UNLOCK product;

Une fois la table déchargée, utilisez la commande LOAD dans RedShift pour charger cette table dans AWS.

Il faut ensuite consolider la table. La table de consolidation stocke les derniers états (reconstruits) de la table MySQL et la table des logs binaires stocke toutes les mises à jour. Cette opération de consolidation réunit la table consolidée et la table des logs binaires, puis extrait les derniers états de chaque ligne. La commande ci-dessous crée une vue ID :

$create view id as select name and value from product;

Quelques points sont toutefois à noter.  Par exemple, si la structure d’une table est modifiée ou si les logs sont reçus dans le désordre, une erreur peut être générée. Il convient alors de concevoir avec minutie le pipeline de donnée et de contrôler régulièrement les alertes de MySQL.

Traduit et adapté par la rédaction