Et encore ERREUR 1040 ...
Le support technique reçoit de nombreuses plaintes concernant cette erreur notoire: ERROR 1040: Too many connections - trop de connexions. Le problème est évident: l'application ou les utilisateurs créent plus de connexions que le serveur ne le permet, c'est-à-dire que le nombre actuel de connexions dépasse la valeur de la variable max_connections .
La situation elle-même est un problème pour les utilisateurs finaux, mais si vous n'avez toujours pas accès au serveur pour diagnostiquer et corriger la cause, tout devient très mauvais. En règle générale, vous devez terminer l'instance et la redémarrer afin de récupérer.
L'utilisateur root ne peut pas non plus se connecter! Pourquoi?!
Dans un environnement correctement configuré, un utilisateur disposant du privilège SUPER pourra accéder à l'instance et diagnostiquer la cause de l'erreur 1040, en raison de laquelle il n'y a pas suffisamment de connexions. Ceci est décrit dans le manuel:
mysqld autorise max_connections + 1 connexions client. Une connexion supplémentaire est réservée aux comptes disposant de privilèges SUPER . Lorsque ces privilèges sont accordés aux administrateurs, et non aux utilisateurs ordinaires (qui n'en ont pas besoin), un administrateur qui dispose également du privilège PROCESS peut se connecter au serveur et utiliser SHOW PROCESSLIST pour diagnostiquer les problèmes, même si le nombre maximal de clients sans privilèges est connecté.
Mais beaucoup de gens accordent des privilèges SUPER à leurs utilisateurs de l'application ou du script - en raison des exigences de l'application (dangereux!) Ou du manque de connaissance des conséquences, puis l'utilisateur ordinaire occupe la connexion réservée et l'utilisateur administratif (généralement root ) ne peut pas se connecter.
Comment garantir l'accès à une instance
Vous pouvez utiliser le hack bien connu avec GDB, qu'Aurimas a conseillé il y a 100 ans pour l'erreur 1040, mais maintenant il existe de meilleures solutions. Certes, ils doivent d'abord être allumés.
Avec Percona Server 5.5.29 et supérieur et MySQL 8.0.14 et supérieur, vous pouvez configurer un autre port avec un nombre supplémentaire de connexions. L'application n'utilisera pas ces interfaces. Ils sont réservés aux administrateurs de bases de données et aux agents de surveillance et de vérification de l'intégrité (voir la remarque ci-dessous).
Configurer Percona Server
À partir de Percona Server 5.5.29, vous pouvez simplement ajouter extra_port à my.cnf , et au prochain redémarrage, le port sera disponible et attendra des données sur la même bind_address que les connexions normales. Si vous ne configurez pas la variable extra_port , il n'y aura pas de port supplémentaire par défaut.
Vous pouvez également définir extra_max_connections pour spécifier le nombre de connexions que ce port gérera. Le nombre par défaut est 1.
Par exemple, j'ai pris toutes les connexions au port des utilisateurs réguliers à partir de l'instance où j'ai déjà configuré extra_port et extra_max_connections dans my.cnf :
résultat
Par ailleurs, extra_port a été supprimé dans Percona Server 8.0.14 et supérieur, car admin_port avec les mêmes fonctions est implémenté dans MySQL Community. Modifiez donc my.cnf lors de la mise à niveau vers Percona Server 8.0.14 ou supérieur si vous avez déjà défini extra_port.
Comme je l'ai dit, cela nécessite MySQL 8.0.14, où WorkLog 12138 est utilisé .
Pour activer l'interface d'administration, vous devez définir admin_addres , qui doit être le seul et unique (sans caractères génériques) IPv4, IPv6, adresse mappée IPv4 ou nom d'hôte auquel l'interface d'administration attendra que les données soient transmises. Si cette variable n'est pas définie, l'interface n'est pas activée.
Vous pouvez toujours définir le port, mais ce n'est pas nécessaire. Par défaut, il s'agit du port 33062 . Si ce port est libre, cette valeur n'a pas besoin d'être configurée. Si vous configurez, placez les deux variables dans la section [mysqld] de my.cnf .
Enfin, vous pouvez configurer create_admin_listener_thread (désactivé par défaut), ce qui crée un thread distinct pour gérer les connexions entrantes. Cela peut être utile dans certaines situations.
Une autre différence est que la documentation Oracle indique que:
Le nombre de connexions administratives est illimité.
(Et nous avons la valeur par défaut de 1). Je ne sais pas ce que cela signifie, mais je ferais attention de ne pas établir accidentellement 1 million de connexions. Bien sûr, elles ne sont pas limitées, mais elles consomment toujours des ressources.
Utilisation pour la surveillance et les contrôles de santé
De manière pratique, non seulement les gens peuvent utiliser une interface ou un port supplémentaire en cas d'urgence lorsque nous avons atteint max_connections . Un système de surveillance proxy / équilibreur de charge / détection de service peut y être connecté.
Les scripts de surveillance pourront récupérer des données pour les diagrammes, de sorte que plus tard vous découvrirez d'où proviennent autant de connexions. Et les scripts de vérification de l'état rendront compte de la détérioration de l'état du serveur, et certains codes peuvent indiquer qu'il y a beaucoup de connexions, mais le serveur est en train de gérer (c'est-à-dire qu'il peut le comprendre et qu'il vaut mieux attendre un peu plus longtemps avant qu'il échoue).
Assurez-vous d'installer une seule connexion à la fois pour la surveillance et les vérifications de l'état, afin de ne pas obstruer extra_max_connections dans Percona Server et de ne pas créer un million de threads dans MySQL. Autrement dit, les scripts ne doivent pas être connectés à nouveau si la demande ou la connexion précédente à la base de données est toujours active.
Voici le même exemple, mais avec MySQL .
Pour Percona Server 8.0.14 et supérieur, le processus sera le même que pour la communauté MySQL.
Au secours! Je dois y entrer, mais tous les ports sont occupés!
Si c'est la raison pour laquelle vous lisez ce post, utilisez un hack fou avec GDB (pas d'infraction, Aurimas, cela semble juste risqué :-D) ou mettez fin à l'instance. Heureusement, une instance peut presque toujours être correctement terminée avec SIGTERM (-15) au lieu de SIGKILL (-9). Ainsi, le serveur fera un arrêt net et les threads auront la possibilité de s'arrêter normalement. Suivez simplement les instructions:
1) Obtenez le PID:
marcos.albe in ~/ pgrep -x mysqld; 650
2) Envoyez SIGTERM à ce PID:
marcos.albe in ~/ kill -15 650;
3) Vérifiez dans le journal des erreurs comment l'arrêt est effectué. Cela ressemblera à ceci:
2019-07-11T13:43:28.421244Z 0 [Note] Giving 0 client threads a chance to die gracefully 2019-07-11T13:43:28.521238Z 0 [Note] Shutting down slave threads 2019-07-11T13:43:28.521272Z 0 [Note] Forcefully disconnecting 0 remaining clients
Cela marque le début du processus d'achèvement. L'instance sera terminée lorsque vous verrez une ligne similaire:
2019-07-11T13:43:31.292836Z 0 [Note] /opt/percona_server/5.7.26/bin/mysqld: Shutdown complete