Outils personnels

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

De wikiGite

(Contenu remplacé par « 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... »)
 
Ligne 5 : Ligne 5 :
 
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]).  
 
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&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>';
 
 
 
</syntaxhighlight>
 

Version actuelle datée du 20 septembre 2018 à 08:57

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 quelques select, Seigneur).

09/2018 : transféré sur Github https://github.com/fsoyer/auditMysqlHTML