Tâches DBA courantes pour les instances de base de données MySQL

Dans le contenu suivant, vous trouverez des descriptions des implémentations spécifiques à Amazon RDS de certaines tâches DBA courantes pour les instances de base de données exécutant le moteur de base de données MySQL. Pour offrir une expérience de service géré, Amazon RDS ne fournit pas l'accès shell aux instances de base de données. Il restreint également l'accès à certaines procédures système et tables qui requièrent des privilèges avancés.

Pour de plus amples informations sur l'utilisation des fichiers journaux MySQL sur Amazon RDS, veuillez consulte Fichiers journaux de base de données MySQL.

Comprendre les utilisateurs prédéfinis

Amazon RDS crée automatiquement plusieurs utilisateurs prédéfinis avec de nouvelles instances de base de données RDS pour MySQL. Les utilisateurs prédéfinis et leurs privilèges ne peuvent pas être modifiés. Vous ne pouvez pas supprimer, renommer ou modifier les privilèges de ces utilisateurs prédéfinis. Toute tentative de ce type génère une erreur.

• rdsadmin — Utilisateur créé pour gérer de nombreuses tâches de gestion que l'administrateur disposant de superuser privilèges exécuterait sur une base de données MySQL autonome. Cet utilisateur est utilisé en interne par RDS for MySQL pour de nombreuses tâches de gestion.

• rdsrepladmin — Utilisateur utilisé en interne par Amazon RDS pour prendre en charge les activités de réplication sur les instances et clusters de bases de données RDS for MySQL.

Mettre fin à une session ou à une requête

Vous pouvez mettre fin aux sessions d'utilisateur ou requêtes sur les instances de base de données à l'aide des commandes rds_kill et rds_kill_query. Connectez-vous d'abord à votre instance de base de données MySQL, puis émettez la commande appropriée comme illustré ci-après. Pour plus d'informations, consultez Connexion à une instance de base de données exécutant le moteur de base de données MySQL.

CALL mysql.rds_kill(thread-ID)
CALL mysql.rds_kill_query(thread-ID) 

Par exemple, pour arrêter la session qui s'exécute sur le thread 99, entrez la commande suivante :

CALL mysql.rds_kill(99); 

Pour arrêter la requête qui s'exécute sur le thread 99, entrez la commande suivante :

CALL mysql.rds_kill_query(99); 

Ignorer une erreur de réplication

Amazon RDS fournit un mécanisme qui vous permet d'ignorer une erreur sur vos réplicas en lecture, si l'erreur entraîne une absence de réponse du réplica en lecture et qu'elle n'affecte pas l'intégrité de vos données.

Note

D'abord, vérifiez que l'erreur concernée peut être ignorée en toute sécurité. Dans un utilitaire MySQL, connectez-vous au réplica en lecture et exécutez la commande MySQL suivante.

SHOW REPLICA STATUS\G 

Pour de plus amples informations sur les valeurs renvoyées, veuillez consulter la documentation MySQL.

Les versions précédentes de MySQL utilisaientSHOW SLAVE STATUS à la place de SHOW REPLICA STATUS. Si vous utilisez une version MySQL antérieure à la version 8.0.23, utilisez alors SHOW SLAVE STATUS.

Vous pouvez ignorer une erreur sur votre réplica en lecture de la manière suivante.

Rubriques

• Appel de la procédure mysql.rds_skip_repl_error
• Définition du paramètre slave_skip_errors

Appel de la procédure mysql.rds_skip_repl_error

Amazon RDS fournit une procédure stockée que vous pouvez appeler pour ignorer une erreur sur vos réplicas en lecture. Connectez-vous d'abord à votre réplica en lecture, puis émettez les commandes appropriées comme illustré ci-après. Pour de plus amples informations, veuillez consulter Connexion à une instance de base de données exécutant le moteur de base de données MySQL.

Pour ignorer l'erreur, émettez la commande suivante.

CALL mysql.rds_skip_repl_error; 

Cette commande n'a aucun effet si vous l'exécutez sur l'instance de base de données source ou sur un réplica en lecture qui n'a rencontré aucune erreur de réplication.

Pour plus d'informations, telles que les versions de MySQL qui prennent en charge mysql.rds_skip_repl_error, consultez mysql.rds_skip_repl_error.

Important

Si vous essayez d'appeler mysql.rds_skip_repl_error et que vous rencontrez l'erreur suivante : ERROR 1305 (42000): PROCEDURE mysql.rds_skip_repl_error does not exist, mettez à niveau votre instance de base de données MySQL avec la dernière version mineure ou avec l'une des versions mineures minimales répertoriées dans mysql.rds_skip_repl_error.

Définition du paramètre slave_skip_errors

Pour ignorer une ou plusieurs erreurs, vous pouvez définir le paramètre statique slave_skip_errors sur le réplica en lecture. Vous pouvez définir ce paramètre pour ignorer un ou plusieurs codes d'erreur de réplication spécifiques. Actuellement, vous pouvez définir ce paramètre uniquement pour les instances de base de données RDS for MySQL 5.7. Après avoir modifié ce paramètre, veillez à redémarrer votre instance de base de données pour que le nouveau paramètre prenne effet. Pour plus d'informations sur le fonctionnement de ces paramètres, consultez la documentation MySQL :

Nous vous recommandons de définir ce paramètre dans un groupe de paramètres de base de données distinct. Vous pouvez associer ce groupe de paramètres de base de données aux réplicas en lecture qui doivent ignorer les erreurs. Le suivi de cette bonne pratique réduit l'impact potentiel sur d'autres instances de base de données et réplicas en lecture.

Important

La définition d'une valeur autre que par défaut pour ce paramètre peut entraîner une incohérence de la réplication. Ne définissez ce paramètre sur une valeur autre que par défaut que si vous avez épuisé d'autres options pour résoudre le problème et que vous êtes sûr de l'impact potentiel sur les données de votre réplica en lecture.

Utilisation des espaces de table InnoDB pour améliorer les temps de récupération sur incident

Chaque table de MySQL se compose d'une définition de table, de données et d'index. Le moteur de stockage MySQL InnoDB stocke les données de table et les index dans un tablespace. InnoDB crée un espace de table global partagé qui contient un dictionnaire de données et autres métadonnées pertinentes, et peut contenir des données de table et des index. InnoDB peut aussi créer des espaces de table distincts pour chaque table et partition. Ces espaces de table distincts sont stockés dans des fichiers ayant .ibd comme extension et l'en-tête de chaque espace de table contient un numéro qui l'identifie de façon unique.

Amazon RDS fournit un paramètre dans un groupe de paramètres MySQL appelé innodb_file_per_table. Ce paramètre contrôle le fait qu'InnoDB ajoute ou non de nouvelles données et de nouveaux index de tables au tablespace partagé (en définissant la valeur du paramètre du 0) ou à des tablespaces individuels (en définissant la valeur du paramètre sur 1). Amazon RDS définit la valeur par défaut pour le paramètre innodb_file_per_table sur 1, ce qui vous permet d'abandonner des tables InnoDB individuelles afin de libérer l'espace de stockage que ces tables utilisent au profit de l'instance de base de données. Dans la plupart des cas d'utilisation, la définition du paramètre innodb_file_per_table à la valeur 1 est celle recommandée.

Vous devez définir le paramètre innodb_file_per_table à la valeur 0 quand vous avez un nombre important de tables, tel que plus de 1 000 tables quand vous utilisez le stockage SSD standard (magnétique) ou à visée générale, ou plus de 10 000 tables quand vous utilisez le stockage IOPS provisionnées. Lorsque vous définissez ce paramètre à la valeur 0, les espaces de table individuels ne sont pas créés et cela peut améliorer le temps nécessaire pour la récupération sur incident de base de données.

MySQL traite chaque fichier de métadonnées, espaces de tables inclus, pendant le cycle de récupération sur incident. Le temps nécessaire à MySQL pour traiter les informations de métadonnées dans l'espace de table partagé est négligeable en comparaison du temps qu'il faut pour traiter des milliers de fichiers d'espace de table quand il y a plusieurs espaces de table. Comme le nombre d'espaces de table est stocké au sein de l'en-tête de chaque fichier, le temps total nécessaire pour lire tous les fichiers d'espace de table peut prendre jusqu'à plusieurs heures. Par exemple, un million d'espaces de table InnoDB sur un stockage standard peut nécessiter entre cinq et huit heures de traitement pendant un cycle de récupération sur incident. Dans certains, InnoDB peut déterminer qu'il a besoin d'un nettoyage supplémentaire après un cycle de récupération sur incident et, par conséquent, entamera un autre cycle de récupération sur incident, ce qui augmente le temps total de récupération. Gardez à l'esprit qu'un cycle de récupération sur incident implique aussi la restauration de transactions, la correction des pages rompues et autres opérations en plus du traitement des informations sur les espaces de table.

Comme le paramètre innodb_file_per_table réside dans un groupe de paramètres, vous pouvez modifier la valeur du paramètre en modifiant le groupe de paramètres utilisé par votre instance de base de données sans avoir à redémarrer celle-ci. Une fois que la valeur est modifiée, de la valeur 1 (créer des tables individuelles) à la valeur 0 (utiliser un espace de table partagé), par exemple, les nouvelles tables InnoDB sont ajoutées à l'espace de table partagé, pendant que les tables existantes continuent d'avoir des espaces de table individuels. Pour déplacer une table InnoDB vers l'espace de table partagé, vous devez utiliser la commande ALTER TABLE.

Migration de plusieurs espaces de table vers l'espace de table partagé

Vous pouvez déplacer les métadonnées d'une table InnoDB de son propre espace de table vers l'espace de table partagé, ce qui recrée les métadonnées de la table selon la valeur du paramètre innodb_file_per_table. Connectez-vous d'abord à votre instance de base de données MySQL, puis émettez les commandes appropriées comme illustré ci-après. Pour plus d'informations, consultez Connexion à une instance de base de données exécutant le moteur de base de données MySQL.

ALTER TABLE table_name ENGINE = InnoDB, ALGORITHM=COPY; 

Par exemple, la requête suivante retourne une instruction ALTER TABLE pour chaque table InnoDB qui ne figure pas dans l'espace de table partagé.

Pour les instances de base de données MySQL 5.7 :

SELECT CONCAT('ALTER TABLE `', 
REPLACE(LEFT(NAME , INSTR((NAME), '/') - 1), '`', '``'), '`.`', 
REPLACE(SUBSTR(NAME FROM INSTR(NAME, '/') + 1), '`', '``'), '` ENGINE=InnoDB, ALGORITHM=COPY;') AS Query 
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES 
WHERE SPACE <> 0 AND LEFT(NAME, INSTR((NAME), '/') - 1) NOT IN ('mysql','');

Pour les instances de base de données MySQL 8.0 :

SELECT CONCAT('ALTER TABLE `', 
REPLACE(LEFT(NAME , INSTR((NAME), '/') - 1), '`', '``'), '`.`', 
REPLACE(SUBSTR(NAME FROM INSTR(NAME, '/') + 1), '`', '``'), '` ENGINE=InnoDB, ALGORITHM=COPY;') AS Query 
FROM INFORMATION_SCHEMA.INNODB_TABLES 
WHERE SPACE <> 0 AND LEFT(NAME, INSTR((NAME), '/') - 1) NOT IN ('mysql','');

La reconstruction d'une table MySQL pour déplacer les métadonnées de la table vers l'espace de table partagé nécessite temporairement un espace de stockage supplémentaire pour recréer la table et, par conséquent, l'instance de base de données doit avoir un espace de stockage disponible. Pendant la reconstruction, la table est verrouillée et inaccessible aux requêtes. Pour les petites tables ou les tables qui ne sont pas fréquemment consultées, ce n'est pas nécessairement un problème. Pour les tables volumineuses ou fréquemment consultées dans un environnement fortement concurrentiel, vous pouvez reconstruire les tables sur un réplica en lecture.

Vous pouvez créer un réplica en lecture et migrer les métadonnées de la table vers l'espace de table partagé du réplica en lecture. Tant que l'instruction ALTER TABLE bloque l'accès sur le réplica en lecture, l'instance de base de données source n'est pas impactée. L'instance de base de données source continue à générer ses journaux binaires, tandis que le réplica en lecture ralentit pendant le processus de reconstruction de la table. Étant donné que la reconstruction exige un espace de stockage supplémentaire et que le fichier journal de relecture peut devenir volumineux, vous devriez créer un réplica en lecture dont la capacité de stockage allouée est supérieure à l'instance de base de données source.

Pour créer un réplica en lecture et reconstruire les tables InnoDB afin d'utiliser l'espace de table partagé, procédez comme suit :

1. Assurez-vous que la rétention des sauvegardes est activée sur l'instance de base de données source de sorte que la journalisation binaire soit activée.

2. Utilisez le AWS Management Console ou AWS CLI pour créer une réplique en lecture pour l'instance de base de données source. Étant donné que la création d'un réplica en lecture implique un grand nombre de processus semblables à ceux de la récupération sur incident, le processus de création peut prendre un certain temps si le nombre d'espaces de table InnoDB est élevé. Allouez plus d'espace de stockage sur le réplica en lecture qu'il n'en est actuellement utilisé sur l'instance de base de données source.

3. Lorsque le réplica en lecture a été créé, créez un groupe de paramètres avec les valeurs de paramètre read_only = 0 et innodb_file_per_table = 0. Associez ensuite le groupe de paramètres au réplica en lecture.

4. Émettez l'instruction SQL suivante pour toutes les tables que vous souhaitez migrer sur le réplica :

   ALTER TABLE name ENGINE = InnoDB

5. Une fois que toutes vos instructions ALTER TABLE sont terminées sur le réplica en lecture, vérifiez que celui-ci est connecté à l'instance de base de données source et que les deux instances sont synchronisées.

6. Utilisez la console ou l'interface de ligne de commande (CLI) pour promouvoir le réplica en lecture comme instance. Assurez-vous que le groupe de paramètres utilisé pour la nouvelle instance de base de données autonome a le paramètre innodb_file_per_table défini sur 0. Modifiez le nom de la nouvelle instance de base de données autonome et pointez toutes les applications vers la nouvelle instance de base de données autonome.

Gestion de l'historique global des statuts (GoSH)

Astuce

Pour analyser les performances des bases de données, vous pouvez également utiliser l'analyse des performances sur Amazon RDS. Pour de plus amples informations, veuillez consulter Surveillance de la charge de la base de données avec Performance Insights sur Amazon RDS.

MySQL gère de nombreuses variables d'état qui fournissent des informations sur son fonctionnement. Leur valeur peut vous aider à détecter les problèmes de verrouillage ou de mémoire d'une instance de base de données. Les valeurs de ces variables d'état se cumulent depuis le dernier démarrage de l'instance de base de données. Vous pouvez réinitialiser à la valeur 0 la plupart des variables d'état à l'aide de la commande FLUSH STATUS.

Pour autoriser la surveillance de ces valeurs au fil du temps, Amazon RDS fournit un ensemble de procédures qui prennent un instantané des valeurs de ces variables et les écrivent dans une table, ainsi que toutes les modifications intervenues depuis le dernier instantané. Cette infrastructure, appelée historique global des statuts (GoSH, Global Status History), est installée sur toutes les instances de base de données MySQL à partir des versions 5.5.23. GoSH est désactivé par défaut.

Pour activer GoSH, vous devez d'abord activer le planificateur d'événement à partir d'un groupe de paramètres de base de données en définissant le paramètre event_scheduler sur ON. Pour les instances de base de données MySQL exécutant MySQL 5.7, définissez également le paramètre show_compatibility_56 sur 1. Pour plus d'informations sur la création et la modification d'un groupe de paramètres DB, consultez Utilisation des groupes de paramètres. Pour obtenir des informations sur les effets secondaires de l'activation de ce paramètre, consultez show_compatibility_56 dans le Manuel de référence de MySQL 5.7.

Vous pouvez ensuite utiliser les procédures du tableau suivant pour activer et configurer GoSH. Connectez-vous d'abord à votre instance de base de données MySQL, puis émettez les commandes appropriées comme illustré ci-après. Pour plus d'informations, consultez Connexion à une instance de base de données exécutant le moteur de base de données MySQL. Pour chaque procédure, entrez ce qui suit :

CALL procedure-name; 

Où procedure-name est l'une des procédures du tableau.

Procédure	Description
mysql.rds_enable_gsh_collector	Active l'infrastructure GoSH pour prendre des instantanés par défaut à intervalles spécifiés par rds_set_gsh_collector.
mysql.rds_set_gsh_collector	Spécifie l'intervalle, en minutes, entre les instantanés. La valeur par défaut est 5.
mysql.rds_disable_gsh_collector	Désactive les instantanés.
mysql.rds_collect_global_status_history	Prend un instantané sur demande.
mysql.rds_enable_gsh_rotation	Active la rotation du contenu de la table mysql.rds_global_status_history en mysql.rds_global_status_history_old à intervalles spécifiés par rds_set_gsh_rotation.
mysql.rds_set_gsh_rotation	Spécifie l'intervalle, en jours, entre deux rotations de table. La valeur par défaut est 7.
mysql.rds_disable_gsh_rotation	Désactive la rotation de table.
mysql.rds_rotate_global_status_history	Effectue une rotation du contenu de la table mysql.rds_global_status_history en mysql.rds_global_status_history_old à la demande.

Lorsque l'infrastructure GoSH est en cours d'exécution, vous pouvez interroger les tables sur lesquelles elle écrit. Par exemple, pour interroger le taux d'accès du groupe de tampons Innodb, vous devez émettre la requête suivante :

select a.collection_end, a.collection_start, (( a.variable_Delta-b.variable_delta)/a.variable_delta)*100 as "HitRatio" 
    from mysql.rds_global_status_history as a join mysql.rds_global_status_history as b on a.collection_end = b.collection_end
    where a. variable_name = 'Innodb_buffer_pool_read_requests' and b.variable_name = 'Innodb_buffer_pool_reads'