Tâches DBA courantes pour les instances de base de données MySQL - Amazon Relational Database Service

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

Cette section décrit les implémentations spécifiques à Amazon RDS de certaines tâches d'administration de base de données 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 bases de données et limite l'accès à certaines tables et procédures système qui requièrent des privilèges avancés.

Pour plus d'informations sur l'utilisation des fichiers journaux MySQL sur Amazon RDS, consultez Fichiers journaux de base de données MySQL

Arrêt d'un session ou d'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. 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.

Note

D'abord, vérifiez que l'erreur 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 SLAVE STATUS\G

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

Pour ignorer l'erreur, vous pouvez émettre 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.

Utilisation des tablespaces 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 tableaux au tablespace partagé (en réglant la valeur du paramètre à 0) ou à des tablespaces individuels (en réglant la valeur du paramètre à 1). Amazon RDS règle la valeur par défaut pour le paramètre innodb_file_per_table à 1, ce qui vous permet de vous débarrasser de tableaux InnoDB individuels afin de libérer de l'espace de stockage utilisé par ces tableaux pour 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é.

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','');
Note

Cette requête est prise en charge sur MySQL 5.6 et versions ultérieures.

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 AWS Management Console ou AWS CLI pour créer un réplica 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 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)

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 DB en définissant le paramètre event_scheduler avec la valeur ON. Pour plus d'informations sur la création et la modification d'un groupe de paramètres DB, consultez Utilisation de groupes de paramètres de base de données.

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;

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'