Outils personnels

Script d'audit de bases de données MySQL : Différence entre versions

De wikiGite

(Page créée avec « 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 à fa... »)
(Aucune différence)

Version du 27 août 2015 à 09:33

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 !...

-- 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&eacute;n&eacute;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&eacute;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&eacute;moire totale utilis&eacute;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&ecirc;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&eacute;moire utilis&eacute;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&eacute; mais valeur &agrave; 0 !</td><td bgcolor="ORANGE">0</td><td bgcolor="ORANGE">N/A</td></tr>'),
		'<tr><td bgcolor="ORANGE">Cache non activ&eacute;</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&ecirc;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&eacute;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&eacute;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&eacute;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&ccedil;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&eacute;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&eacute;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&ecirc;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&ecirc;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>';