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

Réplication de bases MySQL : trois approches

Organizations typically choose MySQL database replication over Redshift due to MySQL's multiple data stores and improved analytical query speed.

•  Le transfert et la récupération de données complets (Full Dump and Load). Il s’agit de l’approche la plus simple de réplication MySQL. MySQL décharge régulièrement les tables pour les recharger dans RedShift. Les tables RedShift correspondantes sont recrées lors du processus de chargement. Bien que cette méthode soit simple, elle comporte toutefois certains risques. Par exemple, la base de données peut être verrouillée lors des opérations de transfert. Cela peut provoquer une forte latence, surtout si la table est volumineuse. Il est donc suggéré de décharger les tables à partir de réplicas en lecture et non de la base en production.
• Le transfert et la récupération de type incrémental. A l’inverse du transfert et de la récupération complets, une requête est paramétrée pour aller vérifier régulièrement les mises à jour. RedShift ne charge ensuite que les nouvelles données ajoutées et celles mises à jour. Une requête de consolidation (MERGE) reconstruit la table originale. Avec cette approche, seules les mises à jour sont extraites et chargées dans RedShift. Les requêtes périodiques réduisent donc les chargements – seulement une petite partie de la base de données est mise à jour entre deux opérations consécutives de chargement. Cette approche a plusieurs inconvénients. Les lignes supprimées ne peuvent pas être capturées- les développeurs ne peuvent ainsi pas les retourner dans une requête. Une requête de consolidation reconstruit la table originale lors de la réplication vers RedShift.  La meilleure approche consiste à utiliser un réplica en lecture au lieu de la base en production, car les opérations de consolidation ajoutent de la complexité au déploiement.
• Réplication des logs binaires. Les logs binaires de MySQL journalisent les logs pour chaque opération effectuée par la base de données. Après le premier déchargement dans la base MySQL, le log binaire diffuse en continu et charge les données dans RedShift. Avec cette approche, les tables ne sont pas verrouillées ; les lignes supprimées et les modifications des tables sont aussi prises en compte.

Transférer vers S3, copier vers RedShift

Une autre approche de réplication : MySQL transfert une copie vers S3 et utilise la commande de RedShift pour charger les données dans l’entrepôt de données d’AWS. Cette méthode est adaptée à de petites bases de données.

Pour réaliser cette opération, identifiez-vous dans la console d’administration AWS pour créer et configurer un cluster RedShift.

Puis, configurez le nœud.

Créez le cluster RedShift, puis configurez les groupes de sécurité pour autoriser l’accès Host. Après cela, exportez les tables vers RedShift.

Dans l’exemple suivant, une commande est entrée pour sélectionner toutes les transactions depuis le 1^er janvier 2016. Ils sont ensuite splittés, avec un maximum d’1 million de lignes par fichier :

mysql>SELECT * FROM test.tbl_product

WHERE DATE(created) < '2016-06-06'

AND DATE(created) >= '2016-01-01'

LIMIT 0,1000000

INTO OUTFILE '/tmp/tbl_product_1.csv'

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\n';

Puis appelez la procédure :

mysql>call export_csv_split('tbl_product', 1000000);

Après avoir créé le .CSV, vous le déplacez dans votre bucket S3. Pour cela, utilisez les commandes ci-dessous :

$ mkdir ~/s3-Redshift

$ mv /tmp/*.csv ~/s3-Redshift/

Vous pouvez installer S3cmd, un outil en lignes de commande pour gérer S3 à distance. Si c’est le cas, configurez les clés AWS.

Puis, chargez les données dans RedShift. Utilisez le standard PostgreSQL pour accéder au cluster RedShift :

$ sudo apt-get install –y postgresql

Utilisez le programme psql pour accéder au cluster :

$psql --host=mysql-Redshift.xxxx.xxx.Redshift.amazonaws.com --port=5439 --username=Redshift --dbname=analytical

Puis créez une table identique à celle dans MySQL :

$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)

);

Exécutez la commande copy pour accéder au ficher CSV dans S3 puis chargez les dans RedShift :

$ copy tbl_product FROM 's3://s3-rshift/s3-Redshift/tbl_product*' credentials

'aws_access_key_id=your_aws_access_key;aws_secret_access_key=your_aws_secret_key' delimiter ',' removequotes;

Après ce chargement des données à partir de S3, exécutez une commande VACUUM pour réorganiser les données et la commande ANALYSE pour mettre à jour les statistiques de la table :

$ vacuum tbl_product

$analyze table_product

Les données sont désormais disponibles dans le cluster RedShift. Pour vérifier les données, utilisez certaines commandes depuis la console psql :

$ analytical=# \d tbl_product;

Pour vérifier si la table s’est chargée correctement :

$ analytical=# SELECT COUNT(*) FROM tbl_product;

Il existe également des outils Open Source, comme Tungsten ou Embulk, pour migrer de gros volumes de données d’un système à l’autre. Il est important de savoir si vous avez besoin d’une forte capacité de traitement, d’une latence élevée ou des chargements fréquents de schémas. Si c’est le cas, considérez un autre outil.

Traduit et adapté par la rédaction