Explication détaillée du code pour collecter des informations sur les tables tout en surveillant MySQL (images et texte)

1. Histoire

Peut-être qu'on vous pose souvent des questions sur la croissance mensuelle du volume de données d'une certaine table de la bibliothèque au cours de l'année écoulée. Bien sûr, ce serait plus facile si vous divisiez les tables par mois, une par une show table status S'il n'y a qu'une seule grande table, alors vous devrez probablement exécuter des statistiques SQL pendant la nuit solitaire où tout le monde se repose, car vous le pouvez. obtenez uniquement la table actuelle, les informations historiques ne peuvent pas être retracées.

De plus, en tant qu'administrateur de base de données, vous devez également estimer la croissance de l'espace de la base de données pour planifier la capacité. Les informations du tableau dont nous parlons comprennent principalement :

SELECT IFNULL(@@hostname, @@server_id) SERVER_NAME, %s as HOST, t.TABLE_SCHEMA, t.TABLE_NAME, t.TABLE_ROWS, t.DATA_LENGTH, t.INDEX_LENGTH, t.AUTO_INCREMENT, c.COLUMN_NAME, c.DATA_TYPE, LOCATE('unsigned', c.COLUMN_TYPE) COL_UNSIGNED # CONCAT(c.DATA_TYPE, IF(LOCATE('unsigned', c.COLUMN_TYPE)=0, '', '_unsigned')) FROM information_schema.`TABLES` t LEFT JOIN information_schema.`COLUMNS` c ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME AND c.EXTRA = 'auto_increment' WHERE t.TABLE_SCHEMA NOT IN ( 'mysql', 'information_schema', 'performance_schema', 'sys' ) AND t.TABLE_TYPE = 'BASE TABLE'À propos de

, en plus de prêter attention à l'endroit où se situe la croissance actuelle, nous nous soucions également de la quantité d'espace disponible par rapport à la valeur maximale de auto_increment. La colonne int / bigint a donc été calculée pour enregistrer le ratio actuellement utilisé. autoIncrUsage

Utilisez ensuite le client python d'InfluxDB pour enregistrer dans influxdb par lots. Sans InfluxDB, le résultat sera imprimé

json - il s'agit d'un format couramment pris en charge par les outils de surveillance tels que Zabbix et Open-Falcon. La dernière étape consiste à utiliser Grafana pour dessiner des graphiques à partir de la source de données influxdb.

3. Utilisation
1. Environnement

   Écrit dans l'environnement python 2.7, 2.6 et 3.x n'ont pas été testés.
   

Le fonctionnement nécessite

et MySQLdb deux bibliothèques : influxdb

$ sudo pip install mysql-python influxdb

1. Configuration

   
   Fichier de configurationsettings_dbs.py

2. 
   

•  : la liste stocke les informations de la table d'instance MySQL qui doivent être collectées, et les tuples sont les connexions. Adresse, port, nom d'utilisateur, mot de passeDBLIST_INFOLes utilisateurs ont besoin d'une autorisation pour sélectionner la table, sinon ils ne peuvent pas voir les informations correspondantes.
  

• : Informations de connexion Influxdb, veuillez créer le nom de la base de données à l'avance InfluxDB_INFOmysql_info et définissez-le sur
  Le résultat de sortie peut être json.None

• Créez le. base de données et stratégie de stockage sur influxdb

  Stockage pendant 2 ans, 1 jeu de répliques : (Ajuster selon les besoins)
  

CREATE DATABASE "mysql_info"
CREATE RETENTION POLICY "mysql_info_schema" ON "mysql_info" DURATION 730d REPLICATION 1 DEFAULT

Regardez les grandes informations similaires à :

1. Mettez crontab à exécuter

   Il peut être placé séparément sur le serveur utilisé pour la surveillance, mais il est recommandé de l'exécuter sur l'hôte où se trouve l'instance mysql dans l'environnement de production pour des raisons de sécurité.
   

Les bibliothèques générales auront une migration de données la nuit. Vous pouvez exécuter

avant et après la migration pour les collecter. Pas recommandé trop souvent. mysql_schema_info.py

40 23,5,12,18 * * * /opt/DBschema_info/mysql_schema_info.py >> /tmp/collect_DBschema_info.log 2>&1

1. Générer un graphique

Importer

sous le projet dans le panneau Grafana. L'effet est le suivant : grafana_table_stats.json

Taille des données du tableau et nombre de lignes

Incrément du nombre de lignes par jour, utilisation auto_increment

4. Plus

1. Dans le cas d'une sous-base de données et d'une table, l'ID unique global ne peut pas être calculé dans la table autoIncrUsage

2. C'est effectivement très difficile à mettre en œuvre Simple, le plus important est d'éveiller la conscience de la collecte de ces informations

3. Vous pouvez ajouter le format de sortie Graphite

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!