|
|
| (Une révision intermédiaire par le même utilisateur non affichée) |
| Ligne 3 : |
Ligne 3 : |
| | Il y a encore beaucoup à faire !... | | Il y a encore beaucoup à faire !... |
| | | | |
| − | Code partagé "as is", sans garantie, à utiliser à vos risques et périls (mais bon, ce ne sont que quelques select, Seigneur). | + | Code partagé "as is", sans garantie, à utiliser à vos risques et périls (mais bon, [http://www.ina.fr/video/PUB3216395097 ce ne sont que quelques select, Seigneur]). |
| | | | |
| − | <syntaxhighlight lang="sql" enclose="div">
| + | 09/2018 : transféré sur Github |
| − | -- AUDIT BASES MYSQL v1.0
| + | https://github.com/fsoyer/auditMysqlHTML |
| − | -- Compatible MySQL >=5.0.6 (information_schema.global_status)
| |
| − | -- FSo 2013
| |
| − | -- Changelog
| |
| − | -- 10/2013 v0.1 : Creation du script
| |
| − | -- 08/2015 v1.0 - fin phase 1 : affichage des infos et ratios de base, sans InnoDB.
| |
| − | | |
| − | -- ****** TABLES D'INFORMATION MySQL > 5.0.6 *******
| |
| − | -- mysql> show tables;
| |
| − | -- +---------------------------------------+
| |
| − | -- | Tables_in_information_schema |
| |
| − | -- +---------------------------------------+
| |
| − | -- | CHARACTER_SETS |
| |
| − | -- | COLLATIONS |
| |
| − | -- | COLLATION_CHARACTER_SET_APPLICABILITY |
| |
| − | -- | COLUMNS |
| |
| − | -- | COLUMN_PRIVILEGES |
| |
| − | -- | ENGINES |
| |
| − | -- | EVENTS |
| |
| − | -- | FILES |
| |
| − | -- | GLOBAL_STATUS | <<<<<<
| |
| − | -- | GLOBAL_VARIABLES | <<<<<<
| |
| − | -- | KEY_COLUMN_USAGE |
| |
| − | -- | PARAMETERS |
| |
| − | -- | PARTITIONS |
| |
| − | -- | PLUGINS |
| |
| − | -- | PROCESSLIST |
| |
| − | -- | PROFILING |
| |
| − | -- | REFERENTIAL_CONSTRAINTS |
| |
| − | -- | ROUTINES |
| |
| − | -- | SCHEMATA |
| |
| − | -- | SCHEMA_PRIVILEGES |
| |
| − | -- | SESSION_STATUS |
| |
| − | -- | SESSION_VARIABLES |
| |
| − | -- | STATISTICS |
| |
| − | -- | TABLES |
| |
| − | -- | TABLESPACES |
| |
| − | -- | TABLE_CONSTRAINTS |
| |
| − | -- | TABLE_PRIVILEGES |
| |
| − | -- | TRIGGERS |
| |
| − | -- | USER_PRIVILEGES |
| |
| − | -- | VIEWS |
| |
| − | -- | INNODB_CMP_RESET |
| |
| − | -- | INNODB_TRX |
| |
| − | -- | INNODB_CMPMEM_RESET |
| |
| − | -- | INNODB_LOCK_WAITS |
| |
| − | -- | INNODB_CMPMEM |
| |
| − | -- | INNODB_CMP |
| |
| − | -- | INNODB_LOCKS |
| |
| − | -- +---------------------------------------+
| |
| − | -- mysql> select table_schema, table_name, engine from tables where table_schema in ('information_schema', 'performance_schema', 'mysql');
| |
| − | -- +--------------------+----------------------------------------------+--------------------+
| |
| − | -- | table_schema | table_name | engine |
| |
| − | -- +--------------------+----------------------------------------------+--------------------+
| |
| − | -- | information_schema | CHARACTER_SETS | MEMORY |
| |
| − | -- | information_schema | COLLATIONS | MEMORY |
| |
| − | -- | information_schema | COLLATION_CHARACTER_SET_APPLICABILITY | MEMORY |
| |
| − | -- | information_schema | COLUMNS | MyISAM |
| |
| − | -- | information_schema | COLUMN_PRIVILEGES | MEMORY |
| |
| − | -- | information_schema | ENGINES | MEMORY |
| |
| − | -- | information_schema | EVENTS | MyISAM |
| |
| − | -- | information_schema | FILES | MEMORY |
| |
| − | -- | information_schema | GLOBAL_STATUS | MEMORY |
| |
| − | -- | information_schema | GLOBAL_VARIABLES | MEMORY |
| |
| − | -- | information_schema | KEY_COLUMN_USAGE | MEMORY |
| |
| − | -- | information_schema | PARAMETERS | MyISAM |
| |
| − | -- | information_schema | PARTITIONS | MyISAM |
| |
| − | -- | information_schema | PLUGINS | MyISAM |
| |
| − | -- | information_schema | PROCESSLIST | MyISAM |
| |
| − | -- | information_schema | PROFILING | MEMORY |
| |
| − | -- | information_schema | REFERENTIAL_CONSTRAINTS | MEMORY |
| |
| − | -- | information_schema | ROUTINES | MyISAM |
| |
| − | -- | information_schema | SCHEMATA | MEMORY |
| |
| − | -- | information_schema | SCHEMA_PRIVILEGES | MEMORY |
| |
| − | -- | information_schema | SESSION_STATUS | MEMORY |
| |
| − | -- | information_schema | SESSION_VARIABLES | MEMORY |
| |
| − | -- | information_schema | STATISTICS | MEMORY |
| |
| − | -- | information_schema | TABLES | MEMORY |
| |
| − | -- | information_schema | TABLESPACES | MEMORY |
| |
| − | -- | information_schema | TABLE_CONSTRAINTS | MEMORY |
| |
| − | -- | information_schema | TABLE_PRIVILEGES | MEMORY |
| |
| − | -- | information_schema | TRIGGERS | MyISAM |
| |
| − | -- | information_schema | USER_PRIVILEGES | MEMORY |
| |
| − | -- | information_schema | VIEWS | MyISAM |
| |
| − | -- | information_schema | INNODB_CMP_RESET | MEMORY |
| |
| − | -- | information_schema | INNODB_TRX | MEMORY |
| |
| − | -- | information_schema | INNODB_CMPMEM_RESET | MEMORY |
| |
| − | -- | information_schema | INNODB_LOCK_WAITS | MEMORY |
| |
| − | -- | information_schema | INNODB_CMPMEM | MEMORY |
| |
| − | -- | information_schema | INNODB_CMP | MEMORY |
| |
| − | -- | information_schema | INNODB_LOCKS | MEMORY |
| |
| − | -- | mysql | columns_priv | MyISAM |
| |
| − | -- | mysql | db | MyISAM |
| |
| − | -- | mysql | event | MyISAM |
| |
| − | -- | mysql | func | MyISAM |
| |
| − | -- | mysql | general_log | CSV |
| |
| − | -- | mysql | help_category | MyISAM |
| |
| − | -- | mysql | help_keyword | MyISAM |
| |
| − | -- | mysql | help_relation | MyISAM |
| |
| − | -- | mysql | help_topic | MyISAM |
| |
| − | -- | mysql | host | MyISAM |
| |
| − | -- | mysql | ndb_binlog_index | MyISAM |
| |
| − | -- | mysql | plugin | MyISAM |
| |
| − | -- | mysql | proc | MyISAM |
| |
| − | -- | mysql | procs_priv | MyISAM |
| |
| − | -- | mysql | proxies_priv | MyISAM |
| |
| − | -- | mysql | servers | MyISAM |
| |
| − | -- | mysql | slow_log | CSV |
| |
| − | -- | mysql | tables_priv | MyISAM |
| |
| − | -- | mysql | time_zone | MyISAM |
| |
| − | -- | mysql | time_zone_leap_second | MyISAM |
| |
| − | -- | mysql | time_zone_name | MyISAM |
| |
| − | -- | mysql | time_zone_transition | MyISAM |
| |
| − | -- | mysql | time_zone_transition_type | MyISAM |
| |
| − | -- | mysql | user | MyISAM |
| |
| − | -- | performance_schema | cond_instances | PERFORMANCE_SCHEMA |
| |
| − | -- | performance_schema | events_waits_current | PERFORMANCE_SCHEMA |
| |
| − | -- | performance_schema | events_waits_history | PERFORMANCE_SCHEMA |
| |
| − | -- | performance_schema | events_waits_history_long | PERFORMANCE_SCHEMA |
| |
| − | -- | performance_schema | events_waits_summary_by_instance | PERFORMANCE_SCHEMA |
| |
| − | -- | performance_schema | events_waits_summary_by_thread_by_event_name | PERFORMANCE_SCHEMA |
| |
| − | -- | performance_schema | events_waits_summary_global_by_event_name | PERFORMANCE_SCHEMA |
| |
| − | -- | performance_schema | file_instances | PERFORMANCE_SCHEMA |
| |
| − | -- | performance_schema | file_summary_by_event_name | PERFORMANCE_SCHEMA |
| |
| − | -- | performance_schema | file_summary_by_instance | PERFORMANCE_SCHEMA |
| |
| − | -- | performance_schema | mutex_instances | PERFORMANCE_SCHEMA |
| |
| − | -- | performance_schema | performance_timers | PERFORMANCE_SCHEMA |
| |
| − | -- | performance_schema | rwlock_instances | PERFORMANCE_SCHEMA |
| |
| − | -- | performance_schema | setup_consumers | PERFORMANCE_SCHEMA |
| |
| − | -- | performance_schema | setup_instruments | PERFORMANCE_SCHEMA |
| |
| − | -- | performance_schema | setup_timers | PERFORMANCE_SCHEMA |
| |
| − | -- | performance_schema | threads | PERFORMANCE_SCHEMA |
| |
| − | -- +--------------------+----------------------------------------------+--------------------+
| |
| − | | |
| − | -- ****** TODO LIST ******
| |
| − | -- TODO : IMPORTANT : Creer une table d'historique d'audit !
| |
| − | -- EN PRIORITE : VOIR LES TODOS DANS CHAQUE SECTION
| |
| − | -- TODO : fonction CouleurLimite vert-orange-rouge à créer (procédure stockée ?)
| |
| − | -- TODO : calculer table locks
| |
| − | -- TODO : STATS INNODB A FAIRE
| |
| − | -- TODO : Extraire (reproduire ?) recommandations mysqltuner ?
| |
| − | | |
| − | -- ****** TIPS ***********
| |
| − | -- Nom de la base courante : SELECT DATABASE();
| |
| − | --
| |
| − | -- Equivalents SHOW XXXX avec SELECT :
| |
| − | -- SHOW GLOBAL VARIABLES LIKE "[%]<variable_name>[%]"
| |
| − | -- = SELECT variable_value FROM INFORMATION_SCHEMA.global_variables WHERE variable_name LIKE "[%]<variable_name>[%]"
| |
| − | -- = SELECT @@GLOBAL.<variable_name>
| |
| − | -- SHOW GLOBAL STATUS LIKE "[%]<status_name>[%]"
| |
| − | -- = SELECT variable_value FROM INFORMATION_SCHEMA.global_status WHERE variable_name LIKE "[%]<variable_name>[%]"
| |
| − | --
| |
| − | -- Convertir valeur décimale + unité :
| |
| − | -- IF(variable_value > 1048576, IF(variable_value > 1073741824, round(variable_value/1024/1024/1024,2), round(variable_value/1024/1024,2)), round(variable_value/1024,2)), IF(variable_value > 1048576, IF(variable_value > 1073741824,' Go', ' Mo'), ' Ko')
| |
| − | | |
| − | -- ================================================= SCRIPT D'AUDIT =========================================
| |
| − | -- Lancer par "mysql --skip-column-names --silent -uroot -pPWD < audit_mysql_html.sql > fichier.html 2> fichier.log"
| |
| − | --
| |
| − | -- *************************************** Entete ************************************
| |
| − | select '<!DOCTYPE public "-//w3c//dtd html 4.01 strict//en" "http://www.w3.org/TR/html4/strict.dtd">';
| |
| − | select '<html>';
| |
| − | select '<head>';
| |
| − | select '<meta http-equiv=Content-Type" content="text/html; charset=iso-8859-1">';
| |
| − | select '<meta name="description" content="Audit Oracle HTML">';
| |
| − | select concat('<title>Audit MYSQL (',@@hostname,')</title>');
| |
| − | select '</head>';
| |
| − | select '<BODY BGCOLOR="#003366">';
| |
| − | select '<table border=1 width=100% bgcolor="WHITE">';
| |
| − | select '<tr><td bgcolor="#3399CC" align=center>';
| |
| − | select concat('<font color=WHITE size=+2><b>Audit MYSQL (',@@hostname,')');
| |
| − | select concat(' le ',sysdate(),'</b>');
| |
| − | select '</font></td></tr></table>';
| |
| − | select '<br>';
| |
| − | | |
| − | -- SECTION TEMPLATE
| |
| − | -- *************************************** Section xxxxxx template *******************
| |
| − | -- select '<hr>';
| |
| − | -- select '<div align=center><b><font color="WHITE">SECTION XXXXX</font></b></div>';
| |
| − | --
| |
| − | -- select '<hr>';
| |
| − | -- *************************************** Sous-section xxxxxx
| |
| − | -- select '<table border=1 width=100% bgcolor="WHITE">';
| |
| − | -- select '<tr><td bgcolor="#3399CC" align=center colspan=3><font color="WHITE"><b>TITRE</b></font></td></tr>';
| |
| − | -- select '<tr><td bgcolor="WHITE" align=center width=40%><b>Colonne1</b></td><td bgcolor="WHITE" align=center><b>Colonne2</b></td><td bgcolor="WHITE" align=center><b>Colonne3</b></td></tr>';
| |
| − | -- ...TRAITEMENTS :...
| |
| − | -- SELECT concat('<tr><td bgcolor="LIGHTBLUE" align=left><b>',COLONNE1,'</b></td><td bgcolor="LIGHTBLUE" align=left>',COLONNE2,'</td><td bgcolor="LIGHTBLUE" align=left>',COLONNE3,'</td><tr>') FROM INFORMATION_SCHEMA.XXXX;
| |
| − | -- ...
| |
| − | -- select '</table>';
| |
| − | -- select '<br>';
| |
| − | --
| |
| − | | |
| − | -- *************************************** Debut script audit *****************************
| |
| − | -- *************************************** Section informations *********************
| |
| − | select '<hr>';
| |
| − | select '<div align=center><b><font color="WHITE">SECTION INFORMATIONS</font></b></div>';
| |
| − | | |
| − | select '<hr>';
| |
| − | -- *************************************** Informations generales
| |
| − | select '<table border=1 width=100% bgcolor="WHITE">';
| |
| − | | |
| − | select '<tr><td bgcolor="#3399CC" align=center colspan=3><font color="WHITE"><b>Informations générales</b></font></td></tr>';
| |
| − | select '<tr><td width=20%>Version</td>';
| |
| − | select '<td bgcolor="LIGHTBLUE" colspan=2>';
| |
| − | select 'MySQL ', @@version, ' (OS : ', @@version_compile_os,')';
| |
| − | select @vers := (substring(@@version,5));
| |
| − | select '</td></tr>';
| |
| − | select '<tr><td width=20%>Uptime</td>';
| |
| − | -- uptime to date : J = sec DIV 86400, RESTEH := sec MOD 86400, H := RESTEH DIV 3600, RESTEM := RESTEH MOD 3600 M := RESTEM DIV 60 S := RESTEM MOD 60
| |
| − | select '<td bgcolor="LIGHTBLUE" align=left colspan=2>';
| |
| − | select concat(floor(variable_value/86400),' jours ',floor(mod(variable_value,86400)/3600),' heures ',floor(mod(mod(variable_value,86400),3600)/60),' minutes ',floor(mod(mod(mod(variable_value,86400),3600),60)),' secondes') from information_schema.global_status where variable_name='Uptime';
| |
| − | select '</td></tr>';
| |
| − | select '<tr><td width=20%>Binary logs</td>';
| |
| − | select '<td bgcolor="LIGHTBLUE" align=left colspan=2>';
| |
| − | select variable_value from information_schema.global_variables where variable_name = 'log_bin';
| |
| − | select '</td></tr></table>';
| |
| − | -- ************************************** Liste des bases de donnees
| |
| − | select '<br/><table border=1 width=100% bgcolor="WHITE">';
| |
| − | select '<tr><td bgcolor="#3399CC" align=center colspan=3><font color="WHITE"><b>Liste des bases de données</b></font></td></tr>';
| |
| − | select '<tr><td bgcolor="WHITE" align=center width=40%><b>Database</b></td><td bgcolor="WHITE" align=center><b>Default character set</b></td><td bgcolor="WHITE" align=center><b>Default collation</b></td></tr>';
| |
| − | SELECT concat('<td bgcolor="LIGHTBLUE" align=left><b>',SCHEMA_NAME,'</b></td><td bgcolor="LIGHTBLUE" align=left>',DEFAULT_CHARACTER_SET_NAME,'</td><td bgcolor="LIGHTBLUE" align=left>',DEFAULT_COLLATION_NAME,'</td></tr>') FROM INFORMATION_SCHEMA.SCHEMATA;
| |
| − | select '</table>';
| |
| − | select '<br>';
| |
| − | | |
| − | -- TODO : voir "status" dans Storage Engine Statistics -> mysqltuner
| |
| − | -- TODO : section recommandations ? (voir mysqltuner)
| |
| − | | |
| − | -- *************************************** Section stockage ***************************
| |
| − | select '<hr>';
| |
| − | select '<div align=center><b><font color="WHITE">SECTION STOCKAGE</font></b></div>';
| |
| − | | |
| − | select '<hr>';
| |
| − | select '<table border=1 width=100% bgcolor="WHITE">';
| |
| − | select '<tr><td bgcolor="#3399CC" align=center colspan=4><font color="WHITE"><b>Taille des bases</b></font></td></tr>';
| |
| − | select '<tr><td bgcolor="WHITE" align=center width=40%><b>Database</b></td><td bgcolor="WHITE" align=center><b>Taille tables</b><td bgcolor="WHITE" align=center><b>Taille indexes</b></td></td><td bgcolor="WHITE" align=center><b>Taille totale</b></td></tr>';
| |
| − | SELECT concat('<tr><td bgcolor="LIGHTBLUE" align=left><b>', table_schema, '</b></td><td bgcolor="LIGHTBLUE" align=right>', ROUND(sum(data_length)/1024/1024,2),' Mo</td><td bgcolor="LIGHTBLUE" align=right>', ROUND(sum(index_length)/1024/1024,2),' Mo</td><td bgcolor="LIGHTBLUE" align=right>', ROUND(sum(data_length+index_length)/1024/1024,2),' Mo</td></tr>')
| |
| − | FROM information_schema.tables
| |
| − | -- WHERE table_schema not in ('information_schema','performance_schema')
| |
| − | GROUP BY table_schema;
| |
| − | SELECT concat('<tr><td bgcolor="WHITE" align=left><b>Total sur disque</b></td>', '<td bgcolor="LIGHTBLUE" align=right colspan=3><b>', ROUND(sum(data_length+index_length)/1024/1024,2),' Mo</b></td></tr>')
| |
| − | FROM information_schema.tables;
| |
| − | | |
| − | select '</table>';
| |
| − | select '<br>';
| |
| − | | |
| − | select '<table border=1 width=100% bgcolor="WHITE">';
| |
| − | select '<tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>Taille des tables par type</b></font></td></tr>';
| |
| − | select '<tr><td bgcolor="WHITE" align=center width=40%><b>Type</b></td><td bgcolor="WHITE" align=center><b>Taille actuelle</b></td></tr>';
| |
| − | SELECT concat('<td bgcolor="LIGHTBLUE" align=left><b>', ENGINE, '</b></td><td bgcolor="LIGHTBLUE" align=right>', IF((SUM(DATA_LENGTH)+SUM(INDEX_LENGTH)) > 1048576, IF((SUM(DATA_LENGTH)+SUM(INDEX_LENGTH)) > 1073741824, round(SUM(DATA_LENGTH)/1024/1024/1024,2), round(SUM(DATA_LENGTH)/1024/1024,2)), round(SUM(DATA_LENGTH)/1024,2)), IF((SUM(DATA_LENGTH)+SUM(INDEX_LENGTH)) > 1048576, IF((SUM(DATA_LENGTH)+SUM(INDEX_LENGTH)) > 1073741824,' Go dans ', ' Mo dans '), ' Ko dans '), COUNT(ENGINE), ' tables</td></tr>')
| |
| − | FROM information_schema.TABLES
| |
| − | -- WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND ENGINE IS NOT NULL
| |
| − | WHERE ENGINE IS NOT NULL
| |
| − | GROUP BY ENGINE ORDER BY ENGINE ASC;
| |
| − | select '</table>';
| |
| − | select '<br>';
| |
| − | -- *************************************** Section memoire **************************
| |
| − | select '<hr>';
| |
| − | select '<div align=center><b><font color="WHITE">SECTION MEMOIRE ET CACHES</font></b></div>';
| |
| − | | |
| − | select '<hr>';
| |
| − | -- *************************************** Memoire utilisee
| |
| − | select '<table border=1 width=100% bgcolor="WHITE">';
| |
| − | select '<tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>Mémoire totale utilisée</b></font></td></tr>';
| |
| − | select '<tr><td bgcolor="WHITE" align=center width=40%><b>Type</b></td><td bgcolor="WHITE" align=center><b>Valeur</b></td></tr>';
| |
| − | SELECT concat('<tr><td bgcolor="LIGHTBLUE" align=left>Buffers</td><td bgcolor="LIGHTBLUE" align=right>',round((kbs.variable_value + IF(tts.variable_value > mhts.variable_value, mhts.variable_value, tts.variable_value) + IF(ibps.variable_value IS NOT NULL, ibps.variable_value, 0) + IF(iamps.variable_value IS NOT NULL, iamps.variable_value, 0) + IF(ilbs.variable_value IS NOT NULL, ilbs.variable_value, 0) + IF(qcs.variable_value IS NOT NULL, qcs.variable_value, 0))/1024/1024,2),' Mo</td></tr>')
| |
| − | FROM (select variable_value from INFORMATION_SCHEMA.global_variables where variable_name = 'key_buffer_size') kbs,
| |
| − | (select variable_value from INFORMATION_SCHEMA.global_variables where variable_name = 'max_heap_table_size') mhts,
| |
| − | (select variable_value from INFORMATION_SCHEMA.global_variables where variable_name = 'tmp_table_size') tts,
| |
| − | (select variable_value from INFORMATION_SCHEMA.global_variables where variable_name = 'query_cache_size') qcs,
| |
| − | (select IF(EXISTS(select variable_value from INFORMATION_SCHEMA.global_variables where variable_name = 'innodb_buffer_pool_size')=1,(select variable_value from INFORMATION_SCHEMA.global_variables where variable_name = 'innodb_buffer_pool_size'), 0) variable_value) ibps, -- les variables innodb n'existent pas si innodb desactivé
| |
| − | (select IF(EXISTS(select variable_value from INFORMATION_SCHEMA.global_variables where variable_name = 'innodb_additional_mem_pool_size')=1,(select variable_value from INFORMATION_SCHEMA.global_variables where variable_name = 'innodb_additional_mem_pool_size'), 0) variable_value) iamps,
| |
| − | (select IF(EXISTS(select variable_value from INFORMATION_SCHEMA.global_variables where variable_name = 'innodb_log_buffer_size')=1,(select variable_value from INFORMATION_SCHEMA.global_variables where variable_name = 'innodb_log_buffer_size'), 0) variable_value) ilbs;
| |
| − | select '</table>';
| |
| − | select '<br>';
| |
| − | | |
| − | -- TODO : tables locks (voir mysqlreport)
| |
| − | -- TODO : #Temp : ratio mem/disk (voir mysqlreport)
| |
| − | | |
| − | -- *************************************** Valeurs actuelle des caches
| |
| − | select '<table border=1 width=100% bgcolor="WHITE">';
| |
| − | select '<tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>Valeurs des caches et buffers</b></font></td></tr>';
| |
| − | select '<tr><td bgcolor="WHITE" align=center width=40%><b>Cache</b></td><td bgcolor="WHITE" align=center><b>Valeur</b></td></tr>';
| |
| − | SELECT concat('<tr><td bgcolor="LIGHTBLUE" align=left>',variable_name,'</td><td bgcolor="LIGHTBLUE" align=right>', IF(variable_value > 1048576, ROUND(variable_value/1024/1024,2), ROUND(variable_value/1024,2)),IF(variable_value > 1048576,' Mo',' Ko'),'</td></tr>')
| |
| − | FROM INFORMATION_SCHEMA.global_variables
| |
| − | where variable_name in ('query_cache_size',
| |
| − | 'key_buffer_size',
| |
| − | 'innodb_buffer_pool_size',
| |
| − | 'innodb_additional_mem_pool_size',
| |
| − | 'innodb_log_buffer_size',
| |
| − | 'read_buffer_size',
| |
| − | 'read_rnd_buffer_size',
| |
| − | 'sort_buffer_size',
| |
| − | 'thread_stack',
| |
| − | 'join_buffer_size',
| |
| − | 'binlog_cache_size',
| |
| − | 'max_heap_table_size',
| |
| − | 'tmp_table_size')
| |
| − | UNION
| |
| − | SELECT concat('<tr><td bgcolor="LIGHTBLUE" align=left>',variable_name,'</td><td bgcolor="LIGHTBLUE" align=right>',variable_value,'</td></tr>')
| |
| − | FROM INFORMATION_SCHEMA.global_variables
| |
| − | where variable_name in ('max_connections',
| |
| − | 'table_cache',
| |
| − | 'table_open_cache')
| |
| − | order by 1;
| |
| − |
| |
| − | select '</table>';
| |
| − | select '<br>';
| |
| − | | |
| − | -- TODO : Reprendre calculs output report de mysql-memory-report-storedproc.sql
| |
| − | -- TODO : #Requêtes + query cache ?
| |
| − | | |
| − | -- *************************************** Section performances **************************
| |
| − | select '<hr>';
| |
| − | select '<div align=center><b><font color="WHITE">SECTION PERFORMANCES</font></b></div>';
| |
| − | | |
| − | select '<hr>';
| |
| − | -- *************************************** Tables caches et ratios
| |
| − | select '<table border=1 width=100% bgcolor="WHITE">';
| |
| − | select '<tr><td bgcolor="#3399CC" align=center colspan=3><font color="WHITE"><b>Cache tables</b></font></td></tr>';
| |
| − | select '<tr><td bgcolor="WHITE" align=center width=40%><b>Variable</b></td><td bgcolor="WHITE" align=center><b>Valeur</b></td><td bgcolor="WHITE" align=center><b>Ratio d\'utilisation</b></td></tr>';
| |
| − | -- show variables like 'table_cache'; (< 5.1.3)
| |
| − | -- show variables like 'table_open_cache'; (> 5.1.3)
| |
| − | select concat('<tr><td bgcolor="LIGHTBLUE" align=left>',gs.variable_name,'</td><td bgcolor="LIGHTBLUE" align=right>',gs.variable_value,'</td><td bgcolor="LIGHTBLUE" align=right>',round((gs.variable_value/gv.variable_value)*100,2), '% de ',gv.variable_value,' (table[_open]_cache)</td></tr>')
| |
| − | FROM INFORMATION_SCHEMA.global_status gs, INFORMATION_SCHEMA.global_variables gv
| |
| − | where gs.variable_name ='Open_tables' and (gv.variable_name = 'TABLE_CACHE' or gv.variable_name = 'TABLE_OPEN_CACHE');
| |
| − | select concat('<tr><td bgcolor="LIGHTBLUE" align=left>',gs.variable_name, '</td><td bgcolor="LIGHTBLUE" align=right>',gs.variable_value,'</td><td bgcolor="LIGHTGREY" align=right><img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7"></td></tr>' )
| |
| − | FROM INFORMATION_SCHEMA.global_status gs
| |
| − | where gs.variable_name ='Opened_tables';
| |
| − | select '</table>';
| |
| − | select '<br>';
| |
| − | | |
| − | -- *************************************** Indexes caches et ratios
| |
| − | select '<table border=1 width=100% bgcolor="WHITE">';
| |
| − | select '<tr><td bgcolor="#3399CC" align=center colspan=3><font color="WHITE"><b>Cache indexes (Key cache)</b></font></td></tr>';
| |
| − | select '<tr><td bgcolor="WHITE" align=center width=40%><b>Statistique</b></td><td bgcolor="WHITE" align=center><b>Valeur</b></td><td bgcolor="WHITE" align=center><b>Ratio d\'utilisation</b></td></tr>';
| |
| − | select concat('<tr><td bgcolor="LIGHTBLUE" align=left>',s.variable_name,'</td><td bgcolor="LIGHTBLUE" align=right>',round(s.variable_value/1024,2),' Mo</td><td bgcolor="LIGHTBLUE" align=right>',round((s.variable_value*100/(v.variable_value/1024)),2),'% de ',round(v.variable_value/1024/1024,2),' Mo (key_buffer_size)','</td></tr>') FROM INFORMATION_SCHEMA.global_status s, INFORMATION_SCHEMA.global_variables v where s.variable_name in ('Key_blocks_used') and v.variable_name = 'key_buffer_size';
| |
| − | select concat('<tr><td bgcolor="WHITE" align=left colspan=2>Ratio read hits</td></td><td bgcolor="LIGHTBLUE" align=right>',round(100 - (s.variable_value/rs.variable_value)*100,2),'%</td></tr>') FROM INFORMATION_SCHEMA.global_status s, INFORMATION_SCHEMA.global_status rs where s.variable_name = 'Key_reads' and rs.variable_name = 'Key_read_requests';
| |
| − | select concat('<td bgcolor="WHITE" align=left colspan=2>Ratio write hits</td><td bgcolor="LIGHTBLUE" align=right>',round(100 - (s.variable_value/rs.variable_value)*100,2),'%</td><tr>') FROM INFORMATION_SCHEMA.global_status s, INFORMATION_SCHEMA.global_status rs where s.variable_name = 'Key_writes' and rs.variable_name = 'Key_write_requests';
| |
| − | select '</td></tr></table>';
| |
| − | select '<br>';
| |
| − | | |
| − | -- *************************************** Query cache
| |
| − | select '<table border=1 width=100% bgcolor="WHITE">';
| |
| − | select '<tr><td bgcolor="#3399CC" align=center colspan=3><font color="WHITE"><b>Cache requêtes (Query cache)</b></font></td></tr>';
| |
| − | select '<tr><td bgcolor="WHITE" align=center width=40%><b>Statistique</b></td><td bgcolor="WHITE" align=center><b>Valeur</b></td><td bgcolor="WHITE" align=center><b>Ratio d\'utilisation</b></td></tr>';
| |
| − | SELECT IF(q.variable_value = 'YES',
| |
| − | IF(v.variable_value > 0,
| |
| − | concat('<tr><td bgcolor="LIGHTBLUE" align=left>','Mémoire utilisée','</td><td bgcolor="LIGHTBLUE" align=right>',round((v.variable_value-s.variable_value)/1024/1024,2),' Mo</td><td bgcolor="LIGHTBLUE" align=right>', round(((v.variable_value-s.variable_value)/v.variable_value)*100,2),' % de ', round(v.variable_value/1024/1024,2),' Mo</td></tr>'),
| |
| − | '<tr><td bgcolor="ORANGE">Cache activé mais valeur à 0 !</td><td bgcolor="ORANGE">0</td><td bgcolor="ORANGE">N/A</td></tr>'),
| |
| − | '<tr><td bgcolor="ORANGE">Cache non activé</td><td bgcolor="ORANGE">N/A</td><td bgcolor="ORANGE">N/A</td></tr>')
| |
| − | FROM INFORMATION_SCHEMA.global_status s, INFORMATION_SCHEMA.global_variables v, INFORMATION_SCHEMA.global_variables q
| |
| − | where s.variable_name = 'Qcache_free_memory'
| |
| − | and v.variable_name = 'query_cache_size'
| |
| − | and q.variable_name = 'have_query_cache';
| |
| − | SELECT IF(q.variable_value = 'YES' AND v.variable_value > 0,
| |
| − | concat('<tr><td bgcolor="LIGHTBLUE" align=left>','Ratio QC hits','</td><td bgcolor="LIGHTBLUE" align=right>', sqch.variable_value,'</td><td bgcolor="LIGHTBLUE" align=right>', round((sqch.variable_value/sq.variable_value)*100,2),' % de ', sq.variable_value, ' requêtes','</td></tr>'),
| |
| − | '')
| |
| − | FROM INFORMATION_SCHEMA.global_status sqch, INFORMATION_SCHEMA.global_status sq, INFORMATION_SCHEMA.global_variables v, INFORMATION_SCHEMA.global_variables q
| |
| − | WHERE sqch.variable_name = 'Qcache_hits'
| |
| − | and sq.variable_name = 'questions'
| |
| − | and v.variable_name = 'query_cache_size'
| |
| − | and q.variable_name = 'have_query_cache';
| |
| − | select '</table>';
| |
| − | select '<br>';
| |
| − | | |
| − | -- A integrer : SELECT GRANTEE, PRIVILEGE_TYPE FROM USER_PRIVILEGES;
| |
| − | -- TODO : voir section Questions -> mysqlreport + section Query cache
| |
| − | | |
| − | -- TODO : Performance Metrics (voir mysqltuner)
| |
| − | | |
| − | -- *************************************** Section processus et sessions **************************
| |
| − | select '<hr>';
| |
| − | select '<div align=center><b><font color="WHITE">SECTION PROCESSUS ET SESSIONS</font></b></div>';
| |
| − | | |
| − | select '<hr>';
| |
| − | -- *************************************** Connexions
| |
| − | select '<table border=1 width=100% bgcolor="WHITE">';
| |
| − | select '<tr><td bgcolor="#3399CC" align=center colspan=3><font color="WHITE"><b>Connexions</b></font></td></tr>';
| |
| − | select '<tr><td bgcolor="WHITE" align=center width=40%><b>Statistique</b></td><td bgcolor="WHITE" align=center><b>Nombre</b></td><td bgcolor="WHITE" align=center><b>Ratio d\'utilisation</b></td></tr>';
| |
| − | | |
| − | -- max_connection = HWM of concurrent connections since boot
| |
| − | SELECT concat('<td bgcolor="LIGHTBLUE" align=left>', 'Nombre de connexions simultanées (max. atteint)','</td><td bgcolor="LIGHTBLUE" align=right>', s.variable_value,'</td><td bgcolor="LIGHTBLUE" align=right>', round((s.variable_value/v.variable_value)*100,2),' % de ', v.variable_value, ' (max_connections)') FROM INFORMATION_SCHEMA.global_status s,INFORMATION_SCHEMA.global_variables v WHERE s.variable_name = 'Max_used_connections' and v.variable_name = 'max_connections';
| |
| − | select '</td></tr>';
| |
| − | | |
| − | -- Aborted_clients = connectes puis deconnectes (coupure); Aborted_connects = meme pas connectes (droits)
| |
| − | SELECT concat('<td bgcolor="LIGHTBLUE" align=left>', 'Connexions interrompues','</td><td bgcolor="LIGHTBLUE" align=right colspan=2>', scl.variable_value, '</td></tr><td bgcolor="LIGHTBLUE" align=left>', 'Connexions invalides', '</td><td bgcolor="LIGHTBLUE" align=right colspan=2>', sco.variable_value, '</td>') FROM INFORMATION_SCHEMA.global_status scl, INFORMATION_SCHEMA.global_status sco WHERE scl.variable_name = 'Aborted_clients' AND sco.variable_name = 'Aborted_connects';
| |
| − | | |
| − | select '</td></tr></table>';
| |
| − | select '<br>';
| |
| − | | |
| − | -- *************************************** Threads
| |
| − | select '<table border=1 width=100% bgcolor="WHITE">';
| |
| − | select '<tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>Threads</b></font></td></tr>';
| |
| − | select '<tr><td bgcolor="WHITE" align=center width=40%><b>Statistique</b></td><td bgcolor="WHITE" align=center><b>Valeur</b></td></tr>';
| |
| − | select concat('<td bgcolor="LIGHTBLUE" align=left>',gs.variable_name,'</td><td bgcolor="LIGHTBLUE" align=right>', gs.variable_value, '</td><tr>')
| |
| − | FROM INFORMATION_SCHEMA.global_status gs
| |
| − | where gs.variable_name = 'Threads_created' or gs.variable_name = 'Connections'
| |
| − | order by gs.variable_name ASC;
| |
| − | | |
| − | -- 100 - ((Threads_created / Connections) * 100)
| |
| − | -- Thread hits ratio < 100% indicates a thread_cache_size to increase
| |
| − | select concat('<td bgcolor="WHITE" align=left>Ratio Thread hits (Threads_created / Connections)</td><td bgcolor="LIGHTBLUE" align=right>', round(100 - ((gs.variable_value/gsq.variable_value) * 100), 2), '%</td><tr>')
| |
| − | FROM INFORMATION_SCHEMA.global_status gs, INFORMATION_SCHEMA.global_status gsq
| |
| − | where gs.variable_name = 'Threads_created' and gsq.variable_name = 'Connections';
| |
| − | | |
| − | select '</td></tr></table>';
| |
| − | select '<br>';
| |
| − | | |
| − | -- *************************************** Statistiques reseau
| |
| − | select '<table border=1 width=100% bgcolor="WHITE">';
| |
| − | select '<tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>Statistiques réseau</b></font></td></tr>';
| |
| − | select '<tr><td bgcolor="WHITE" align=center width=40%><b>In/Out</b></td><td bgcolor="WHITE" align=center><b>Valeur</b></td></tr>';
| |
| − | select concat('<tr><td bgcolor="LIGHTBLUE" align=left>Octets envoyés</td><td bgcolor="LIGHTBLUE" align=right>', IF(variable_value > 1048576, IF(variable_value > 1073741824, round(variable_value/1024/1024/1024,2), round(variable_value/1024/1024,2)), round(variable_value/1024,2)), IF(variable_value > 1048576, IF(variable_value > 1073741824,' Go', ' Mo'), ' Ko'), '</td></tr>')
| |
| − | FROM INFORMATION_SCHEMA.global_status
| |
| − | where variable_name = 'Bytes_sent';
| |
| − | select concat('<tr><td bgcolor="LIGHTBLUE" align=left>Octets reçus</td><td bgcolor="LIGHTBLUE" align=right>', IF(variable_value > 1048576, IF(variable_value > 1073741824, round(variable_value/1024/1024/1024,2), round(variable_value/1024/1024,2)), round(variable_value/1024,2)), IF(variable_value > 1048576, IF(variable_value > 1073741824,' Go', ' Mo'), ' Ko'))
| |
| − | FROM INFORMATION_SCHEMA.global_status
| |
| − | where variable_name = 'Bytes_received';
| |
| − | | |
| − | select '</td></tr></table>';
| |
| − | select '<br>';
| |
| − | | |
| − | -- *************************************** Section Requêtes **************************
| |
| − | -- Disk tmp table : stats{'Created_tmp_disk_tables'}
| |
| − | -- tmp Table (RAM) : stats{'Created_tmp_tables'} sur vars{'tmp_table_size'}
| |
| − | -- tmp File : stats{'Created_tmp_files'}
| |
| − | select '<hr>';
| |
| − | select '<div align=center><b><font color="WHITE">SECTION REQUETES</font></b></div>';
| |
| − | | |
| − | select '<hr>';
| |
| − | | |
| − | -- *************************************** Tris
| |
| − | select '<table border=1 width=100% bgcolor="WHITE">';
| |
| − | select '<tr><td bgcolor="#3399CC" align=center colspan=3><font color="WHITE"><b>Tris</b></font></td></tr>';
| |
| − | select '<tr><td bgcolor="WHITE" align=center width=40%><b>Statistique</b></td><td bgcolor="WHITE" align=center><b>Valeur</b></td></tr>';
| |
| − | select concat('<td bgcolor="WHITE" align=left> Total des tris </td>', '<td bgcolor="LIGHTBLUE" align=right>', gss.variable_value + gsr.variable_value, '</td></tr>')
| |
| − | FROM INFORMATION_SCHEMA.global_status gss, INFORMATION_SCHEMA.global_status gsr
| |
| − | where gss.variable_name = 'Sort_scan'
| |
| − | and gsr.variable_name = 'Sort_range';
| |
| − | | |
| − | -- ratio must be < 10%, else increase sort_buffer_size and read_rnd_buffer_size
| |
| − | select concat('<td bgcolor="WHITE" align=left>Tris nécessitant une table temporaire</td>', '<td bgcolor="LIGHTBLUE" align=right>', round((gsmp.variable_value / (gss.variable_value + gsr.variable_value) *100),2), ' %</td></tr>')
| |
| − | FROM INFORMATION_SCHEMA.global_status gss, INFORMATION_SCHEMA.global_status gsr, INFORMATION_SCHEMA.global_status gsmp
| |
| − | where gss.variable_name = 'Sort_scan'
| |
| − | and gsr.variable_name = 'Sort_range'
| |
| − | and gsmp.variable_name = 'Sort_merge_passes';
| |
| − | | |
| − | select '</td></tr></table>';
| |
| − | select '<br>';
| |
| − | | |
| − | -- *************************************** Tables temporaires
| |
| − | select sum(variable_value) into @tmp_total
| |
| − | FROM INFORMATION_SCHEMA.global_status
| |
| − | where variable_name in ( 'Created_tmp_tables', 'Created_tmp_disk_tables', 'Created_tmp_files');
| |
| − | select '<table border=1 width=100% bgcolor="WHITE">';
| |
| − | select '<tr><td bgcolor="#3399CC" align=center colspan=3><font color="WHITE"><b>Tables temporaires</b></font></td></tr>';
| |
| − | select '<tr><td bgcolor="WHITE" align=center width=40%><b>Statistique</b></td><td bgcolor="WHITE" align=center><b>Valeur</b></td><td bgcolor="WHITE" align=center><b>% du total tables temporaires</b></td></tr>';
| |
| − | select concat('<td bgcolor="LIGHTBLUE" align=left>', CASE WHEN gs.variable_name='Created_tmp_disk_tables' THEN 'Tables sur disque' WHEN gs.variable_name='Created_tmp_tables' THEN 'Tables en mémoire' ELSE 'Tables en fichiers' END,'</td><td bgcolor="LIGHTBLUE" align=right>', gs.variable_value,'</td><td bgcolor="LIGHTBLUE" align=right>', round((gs.variable_value/@tmp_total)*100, 2), '%</td><tr>')
| |
| − | FROM INFORMATION_SCHEMA.global_status gs
| |
| − | where gs.variable_name in ( 'Created_tmp_tables', 'Created_tmp_disk_tables', 'Created_tmp_files')
| |
| − | order by gs.variable_name DESC;
| |
| − |
| |
| − | select '</td></tr></table>';
| |
| − | select '<br>';
| |
| − | | |
| − | -- *************************************** Slow queries
| |
| − | select '<table border=1 width=100% bgcolor="WHITE">';
| |
| − | select '<tr><td bgcolor="#3399CC" align=center colspan=2><font color="WHITE"><b>Slow queries</b></font></td></tr>';
| |
| − | select '<tr><td bgcolor="WHITE" align=center width=40%><b>Journalisation slow queries</b></td><td bgcolor="WHITE" align=center><b>Fichier journal</b></td></tr>';
| |
| − | select concat('<td align=left bgcolor="', IF(sq.variable_value = 'ON','LIGHTGREEN','ORANGE'),'">', sq.variable_value, '</td><td bgcolor="LIGHTBLUE" align=right>', sf.variable_value, '</td></tr>')
| |
| − | from INFORMATION_SCHEMA.global_variables sq, INFORMATION_SCHEMA.global_variables sf
| |
| − | where sq.variable_name = 'SLOW_QUERY_LOG'
| |
| − | and sf.variable_name = 'SLOW_QUERY_LOG_FILE';
| |
| − | select IF(sq.variable_value = 'ON',
| |
| − | '<tr><td bgcolor="WHITE" align=center width=40%><b>Statistique</b></td><td bgcolor="WHITE" align=center><b>Valeur</b></td></tr>',
| |
| − | '')
| |
| − | from INFORMATION_SCHEMA.global_variables sq
| |
| − | where sq.variable_name = 'SLOW_QUERY_LOG';
| |
| − | select IF(sq.variable_value = 'ON',
| |
| − | concat('<td bgcolor="LIGHTBLUE" align=left>Nombre slow queries (>', round(gv.variable_value,2), ' s)</td><td bgcolor="LIGHTBLUE" align=right>', gs.variable_value, '</td><tr>'),
| |
| − | '')
| |
| − | FROM INFORMATION_SCHEMA.global_status gs, INFORMATION_SCHEMA.global_variables gv, INFORMATION_SCHEMA.global_variables sq
| |
| − | where gs.variable_name = 'Slow_queries'
| |
| − | and gv.variable_name = 'long_query_time'
| |
| − | and sq.variable_name = 'SLOW_QUERY_LOG';
| |
| − | select IF(sq.variable_value = 'ON',
| |
| − | concat('<td bgcolor="LIGHTBLUE" align=left>Nombre total requêtes</td><td bgcolor="LIGHTBLUE" align=right>', gsq.variable_value, '</td><tr>'),
| |
| − | '')
| |
| − | FROM INFORMATION_SCHEMA.global_status gsq, INFORMATION_SCHEMA.global_variables sq
| |
| − | where gsq.variable_name = 'Questions'
| |
| − | and sq.variable_name = 'SLOW_QUERY_LOG';
| |
| − | select IF(sq.variable_value = 'ON',
| |
| − | concat('<td bgcolor="WHITE" align=left>Ratio (slow queries/requêtes)</td><td bgcolor="LIGHTBLUE" align=right>', round((gs.variable_value * 100) / gsq.variable_value, 2), '%</td><tr>'),
| |
| − | '')
| |
| − | FROM INFORMATION_SCHEMA.global_status gs, INFORMATION_SCHEMA.global_status gsq, INFORMATION_SCHEMA.global_variables sq
| |
| − | where gs.variable_name = 'Slow_queries'
| |
| − | and gsq.variable_name = 'Questions'
| |
| − | and sq.variable_name = 'SLOW_QUERY_LOG';
| |
| − |
| |
| − | select '</td></tr></table>';
| |
| − | select '<br>';
| |
| − | | |
| − | -- *************************************** Section INNODB **************************
| |
| − | select '<hr>';
| |
| − | select '<div align=center><b><font color="WHITE">SECTION INNODB</font></b></div>';
| |
| − | | |
| − | -- select variable_value from INFORMATION_SCHEMA.global_variables where variable_name = 'have_innodb';
| |
| − | -- select variable_value from INFORMATION_SCHEMA.global_variables where variable_name = 'innodb_buffer_pool_size';
| |
| − | | |
| − | select '<hr>';
| |
| − | | |
| − | -- TODO :
| |
| − | -- [!!] InnoDB is enabled but isn't being used -> calculer si 1.activé, 2.utilisé
| |
| − | --
| |
| − | -- InnoDB Buffer Pool ______ innodb_buffer_pool_size 70-80% of memory is a safe bet.
| |
| − | -- Usage 8.00M of 8.00M %Used: 100.00 :
| |
| − | -- Read hit 98.23%
| |
| − | | |
| − | -- $ib_bp_used = ($stats{'Innodb_buffer_pool_pages_total'} -
| |
| − | -- $stats{'Innodb_buffer_pool_pages_free'}) *
| |
| − | -- $stats{'Innodb_page_size'};
| |
| − | -- $ib_bp_total = $stats{'Innodb_buffer_pool_pages_total'} * $stats{'Innodb_page_size'};
| |
| − | -- $ib_bp_read_ratio = sprintf "%.2f",
| |
| − | -- ($stats{'Innodb_buffer_pool_read_requests'} ?
| |
| − | -- 100 - ($stats{'Innodb_buffer_pool_reads'} /
| |
| − | -- $stats{'Innodb_buffer_pool_read_requests'}) * 100 :
| |
| − | -- 0);
| |
| − | | |
| − | -- InnoDB Lock _________________________________________________________
| |
| − | -- Waits 0 0/s
| |
| − | -- Current 0
| |
| − | -- $stats{'Innodb_row_lock_waits'}, t($stats{'Innodb_row_lock_waits'})
| |
| − | | |
| − | -- *************************************** Fin de rapport **************************
| |
| − | select '</body>';
| |
| − | select '</html>';
| |
| − | | |
| − | </syntaxhighlight>
| |
