MySQL variables global session utilisateur
Par PlaceOweb le dimanche, juin 12 2016, 12:01 - SQL - Lien permanent
Accès aux variables de status, système, session, globales, utilisateur.
- Server Status Variables utiliser SHOW STATUS Statement pour voir le status des variables.
- Server System Variables utiliser SHOW VARIABLES Statement pour lister leur valeur ou le format avec 2 @ : @@event_scheduler et la porté globale ou session
- @@GLOBAL.foreign_key_checks
- @@SESSION.foreign_key_checks
- @@foreign_key_checks (identique à @@SESSION.)
- User-defined Variables avec un seul @ : @ma_variable
-- SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern' | WHERE expr]
SHOW STATUS; -- par défaut SESSION, identique à SHOW SESSION STATUS, retourne : Variable_name | Value
SHOW SESSION STATUS;
SHOW GLOBAL STATUS;
SHOW STATUS LIKE 'Uptime'; -- L'uptime MariaDB
SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS; -- retourne : VARIABLE_NAME | VARIABLE_VALUE
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS;
-- SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern' | WHERE expr]
SHOW VARIABLES; -- par défaut SESSION, identique à SHOW SESSION VARIABLES, retourne : Variable_name | Value
SHOW SESSION VARIABLES;
SHOW GLOBAL VARIABLES;
SHOW VARIABLES LIKE 'event_scheduler'; -- L'état du planificateur d'événements
SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES; -- retourne : VARIABLE_NAME | VARIABLE_VALUE
SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES;
SELECT @@event_scheduler;
SELECT @@GLOBAL.event_scheduler;
SELECT @@SESSION.event_scheduler; -- ERROR 1238 (HY000): Variable 'event_scheduler' is a GLOBAL variable
-- Pour avoir simultanément les 2 types de variables (status et système), pas d'autre choix que de passer par le dictionnaire :
SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE 'Uptime'
UNION ALL
SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME LIKE 'event_scheduler';
-- Ou alors par @@GLOBAL et @@SESSION.
SET foreign_key_checks=OFF;
SELECT @@GLOBAL.foreign_key_checks, @@SESSION.foreign_key_checks, @@foreign_key_checks;
+-----------------------------+------------------------------+----------------------+
| @@GLOBAL.foreign_key_checks | @@SESSION.foreign_key_checks | @@foreign_key_checks |
+-----------------------------+------------------------------+----------------------+
| 1 | 0 | 0 |
+-----------------------------+------------------------------+----------------------+
SET foreign_key_checks=ON;
SELECT @@GLOBAL.foreign_key_checks, @@SESSION.foreign_key_checks, @@foreign_key_checks;
+-----------------------------+------------------------------+----------------------+
| @@GLOBAL.foreign_key_checks | @@SESSION.foreign_key_checks | @@foreign_key_checks |
+-----------------------------+------------------------------+----------------------+
| 1 | 1 | 1 |
+-----------------------------+------------------------------+----------------------+
-- Les colonnes retournées :
SHOW STATUS;
/*
+-----------------------------------------------+---------------+
| Variable_name | Value |
+-----------------------------------------------+---------------+
| Aborted_clients | 268 |
| Aborted_connects | 3 |
...
| Uptime | 3169677 |
| Uptime_since_flush_status | 3169677 |
+-----------------------------------------------+---------------+
*/
SHOW STATUS LIKE '%time%';
/*
+-------------------------------------+----------+
| Variable_name | Value |
+-------------------------------------+----------+
| Binlog_group_commit_trigger_timeout | 0 |
| Busy_time | 0.000000 |
| Cpu_time | 0.000000 |
| Feature_timezone | 0 |
| Innodb_row_lock_time | 1275 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 515 |
| Ssl_default_timeout | 0 |
| Ssl_session_cache_timeouts | 0 |
| Uptime | 422769 |
| Uptime_since_flush_status | 422769 |
+-------------------------------------+----------+
*/
SHOW VARIABLES;
/*
+--------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+--------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
| back_log | 80 |
| basedir | /usr |
...
| general_log | OFF |
| general_log_file | /var/lib/mysql/myhostnameserver.log |
...
| innodb_file_per_table | ON |
...
| log_error | /var/log/mysql/error.log |
| log_output | FILE |
...
| version | 5.6.19-0ubuntu0.14.04.1-log |
| version_comment | (Ubuntu) |
| version_compile_machine | x86_64 |
| version_compile_os | debian-linux-gnu |
| wait_timeout | 28800 |
| warning_count | 0 |
+--------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
*/
SHOW VARIABLES LIKE '%time%';
/*
+-----------------------------+-------------------+
| Variable_name | Value |
+-----------------------------+-------------------+
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| deadlock_timeout_long | 50000000 |
| deadlock_timeout_short | 10000 |
| delayed_insert_timeout | 300 |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 2.000000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| progress_report_time | 5 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CEST |
| thread_pool_idle_timeout | 60 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1465899161.605375 |
| wait_timeout | 28800 |
+-----------------------------+-------------------+
*/
SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME LIKE '%time%';
/*
+-----------------------------+-------------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-----------------------------+-------------------+
| INNODB_FLUSH_LOG_AT_TIMEOUT | 1 |
| TIMED_MUTEXES | OFF |
| DELAYED_INSERT_TIMEOUT | 300 |
| SLOW_LAUNCH_TIME | 2 |
| CONNECT_TIMEOUT | 10 |
| DEADLOCK_TIMEOUT_SHORT | 10000 |
| INNODB_ROLLBACK_ON_TIMEOUT | OFF |
| LOCK_WAIT_TIMEOUT | 31536000 |
| THREAD_POOL_IDLE_TIMEOUT | 60 |
| NET_WRITE_TIMEOUT | 60 |
| FLUSH_TIME | 0 |
| LONG_QUERY_TIME | 2.000000 |
| TIME_FORMAT | %H:%i:%s |
| SYSTEM_TIME_ZONE | CEST |
| SLAVE_NET_TIMEOUT | 3600 |
| DEADLOCK_TIMEOUT_LONG | 50000000 |
| INNODB_LOCK_WAIT_TIMEOUT | 50 |
| INNODB_OLD_BLOCKS_TIME | 1000 |
| NET_READ_TIMEOUT | 30 |
| DATETIME_FORMAT | %Y-%m-%d %H:%i:%s |
| LC_TIME_NAMES | en_US |
| PROGRESS_REPORT_TIME | 5 |
| TIME_ZONE | SYSTEM |
| INTERACTIVE_TIMEOUT | 28800 |
| WAIT_TIMEOUT | 28800 |
+-----------------------------+-------------------+
*/
SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS;
/*
+-----------------------------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-----------------------------------------------+----------------+
| ABORTED_CLIENTS | 268 |
| ABORTED_CONNECTS | 3 |
...
| UPTIME | 3170102 |
| UPTIME_SINCE_FLUSH_STATUS | 3170102 |
+-----------------------------------------------+----------------+
*/
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS;
/*
+-----------------------------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-----------------------------------------------+----------------+
| ABORTED_CLIENTS | 268 |
| ABORTED_CONNECTS | 3 |
...
| UPTIME | 3170170 |
| UPTIME_SINCE_FLUSH_STATUS | 3170170 |
+-----------------------------------------------+----------------+
*/