Script d'audit de bases de données MySQL
De wikiGite
Révision datée du 24 septembre 2015 à 14:32 par Frank (discussion | contributions)
De la même façon que l'audit de bases Oracle, ce script produit un rapport au format HTML.
Il y a encore beaucoup à faire !...
Code partagé "as is", sans garantie, à utiliser à vos risques et périls (mais bon, ce ne sont que des select).
-- AUDIT BASES MYSQL v1.0
-- 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>';
