Changer les paramètres de configuration de MySQL à chaud
Eh oui, il est possible de changer certains paramètres de MySQL à chaud.
Bien pratique quand il est nécessaire d’ajuster certains paramètres lorsque le serveur ne peut être arrêté souvent !
Le site de pythian nous donne un tableau comprenant ceux qui peuvent être changés à chaud et ceux qui ne le sont pas.
| Variable Name | Variable Type | SET OFFLINE ONLY | SET GLOBAL | SET SESSION |
| auto_increment_increment | numeric | OFFLINE | ||
| auto_increment_offset | numeric | OFFLINE | ||
| autocommit | boolean | SESSION | ||
| automatic_sp_privileges | boolean | GLOBAL | ||
| back_log | numeric | OFFLINE | ||
| basedir | string | OFFLINE | ||
| bdb_cache_size | numeric | OFFLINE | ||
| bdb_home | string | OFFLINE | ||
| bdb_log_buffer_size | numeric | OFFLINE | ||
| bdb_logdir | string | OFFLINE | ||
| bdb_max_lock | numeric | OFFLINE | ||
| bdb_shared_data | boolean | OFFLINE | ||
| bdb_tmpdir | string | OFFLINE | ||
| big_tables | boolean | SESSION | ||
| binlog_cache_size | numeric | GLOBAL | ||
| bulk_insert_buffer_size | numeric | GLOBAL | SESSION | |
| character_set_client | string | GLOBAL | SESSION | |
| character_set_connection | string | GLOBAL | SESSION | |
| character_set_database | string | OFFLINE | ||
| character_set_filesystem | string | GLOBAL | SESSION | |
| character_set_results | string | GLOBAL | SESSION | |
| character_set_server | string | GLOBAL | SESSION | |
| character_set_system | string | OFFLINE | ||
| character_sets_dir | string | OFFLINE | ||
| collation_connection | string | GLOBAL | SESSION | |
| collation_database | string | OFFLINE | ||
| collation_server | string | GLOBAL | SESSION | |
| completion_type | numeric | GLOBAL | SESSION | |
| concurrent_insert | numeric | GLOBAL | ||
| connect_timeout | numeric | GLOBAL | ||
| datadir | string | OFFLINE | ||
| date_format | — | OFFLINE | ||
| datetime_format | — | OFFLINE | ||
| default_week_format | numeric | GLOBAL | SESSION | |
| delay_key_write | OFF | ON | ALL | GLOBAL | ||
| delayed_insert_limit | numeric | GLOBAL | ||
| delayed_insert_timeout | numeric | GLOBAL | ||
| delayed_queue_size | numeric | GLOBAL | ||
| div_precision_increment | numeric | GLOBAL | SESSION | |
| engine_condition_pushdown | boolean | GLOBAL | SESSION | |
| error_count | numeric | SESSION | ||
| expire_logs_days | numeric | GLOBAL | ||
| flush | boolean | GLOBAL | ||
| flush_time | numeric | GLOBAL | ||
| foreign_key_checks | boolean | SESSION | ||
| ft_boolean_syntax | string | GLOBAL | ||
| ft_max_word_len | numeric | OFFLINE | ||
| ft_min_word_len | numeric | OFFLINE | ||
| ft_query_expansion_limit | numeric | OFFLINE | ||
| ft_stopword_file | string | OFFLINE | ||
| group_concat_max_len | numeric | GLOBAL | SESSION | |
| have_archive | boolean | OFFLINE | ||
| have_bdb | boolean | OFFLINE | ||
| have_blackhole_engine | boolean | OFFLINE | ||
| have_compress | boolean | OFFLINE | ||
| have_crypt | boolean | OFFLINE | ||
| have_csv | boolean | OFFLINE | ||
| have_dynamic_loading | boolean | OFFLINE | ||
| have_example_engine | boolean | OFFLINE | ||
| have_federated_engine | boolean | OFFLINE | ||
| have_geometry | boolean | OFFLINE | ||
| have_innodb | boolean | OFFLINE | ||
| have_isam | boolean | OFFLINE | ||
| have_merge_engine | boolean | OFFLINE | ||
| have_ndbcluster | boolean | OFFLINE | ||
| have_openssl | boolean | OFFLINE | ||
| have_query_cache | boolean | OFFLINE | ||
| have_raid | boolean | OFFLINE | ||
| have_rtree_keys | boolean | OFFLINE | ||
| have_symlink | boolean | OFFLINE | ||
| init_connect | string | OFFLINE | ||
| init_file | string | OFFLINE | ||
| init_slave | string | OFFLINE | ||
| innodb_additional_mem_pool_size | numeric | OFFLINE | ||
| innodb_autoextend_increment | numeric | GLOBAL | ||
| innodb_buffer_pool_awe_mem_mb | numeric | OFFLINE | ||
| innodb_buffer_pool_size | numeric | OFFLINE | ||
| innodb_checksums | boolean | OFFLINE | ||
| innodb_commit_concurrency | numeric | GLOBAL | ||
| innodb_concurrency_tickets | numeric | GLOBAL | ||
| innodb_data_file_path | string | OFFLINE | ||
| innodb_data_home_dir | string | OFFLINE | ||
| innodb_doublewrite | boolean | OFFLINE | ||
| innodb_fast_shutdown | 0,1 or 2 | OFFLINE | ||
| innodb_file_io_threads | numeric | OFFLINE | ||
| innodb_file_per_table | boolean | OFFLINE | ||
| innodb_flush_log_at_trx_commit | 0,1 or 2 | OFFLINE | ||
| innodb_flush_method | Enum | OFFLINE | ||
| innodb_force_recovery | 1 to 6 | OFFLINE | ||
| innodb_lock_wait_timeout | numeric | OFFLINE | ||
| innodb_locks_unsafe_for_binlog | boolean | OFFLINE | ||
| innodb_log_arch_dir | string | OFFLINE | ||
| innodb_log_archive | — | OFFLINE | ||
| innodb_log_buffer_size | numeric | OFFLINE | ||
| innodb_log_file_size | numeric | OFFLINE | ||
| innodb_log_files_in_group | numeric | OFFLINE | ||
| innodb_log_group_home_dir | string | OFFLINE | ||
| innodb_max_dirty_pages_pct | numeric | GLOBAL | ||
| innodb_max_purge_lag | numeric | GLOBAL | ||
| innodb_mirrored_log_groups | numeric | OFFLINE | ||
| innodb_open_files | numeric | OFFLINE | ||
| innodb_support_xa | boolean | GLOBAL | SESSION | |
| innodb_sync_spin_loops | numeric | GLOBAL | ||
| innodb_table_locks | boolean | GLOBAL | SESSION | |
| innodb_thread_concurrency | numeric | GLOBAL | ||
| innodb_thread_sleep_delay | numeric | GLOBAL | ||
| interactive_timeout | numeric | GLOBAL | SESSION | |
| join_buffer_size | numeric | GLOBAL | SESSION | |
| key_buffer_size | numeric | GLOBAL | ||
| key_cache_age_threshold | numeric | OFFLINE | ||
| key_cache_block_size | numeric | OFFLINE | ||
| key_cache_division_limit | 1 to 100 | OFFLINE | ||
| language | string | OFFLINE | ||
| large_files_support | boolean | OFFLINE | ||
| large_page_size | numeric | OFFLINE | ||
| large_pages | boolean | OFFLINE | ||
| lc_time_names | string | GLOBAL | SESSION | |
| license | string | OFFLINE | ||
| local_infile | boolean | GLOBAL | ||
| locked_in_memory | boolean | OFFLINE | ||
| log | boolean | OFFLINE | ||
| log_bin | boolean | OFFLINE | ||
| log_bin_trust_function_creators | boolean | GLOBAL | ||
| log_error | string | OFFLINE | ||
| log_queries_not_using_indexes | boolean | GLOBAL | ||
| log_slave_updates | boolean | OFFLINE | ||
| log_slow_queries | boolean | OFFLINE | ||
| log_warnings | numeric | GLOBAL | ||
| long_query_time | numeric | GLOBAL | SESSION | |
| low_priority_updates | boolean | GLOBAL | SESSION | |
| lower_case_file_system | boolean | OFFLINE | ||
| lower_case_table_names | numeric | OFFLINE | ||
| max_allowed_packet | numeric | GLOBAL | SESSION | |
| max_binlog_cache_size | numeric | GLOBAL | ||
| max_binlog_size | numeric | GLOBAL | ||
| max_connect_errors | numeric | GLOBAL | ||
| max_connections | numeric | GLOBAL | ||
| max_delayed_threads | numeric | GLOBAL | ||
| max_error_count | numeric | GLOBAL | SESSION | |
| max_heap_table_size | numeric | GLOBAL | SESSION | |
| max_insert_delayed_threads | numeric | GLOBAL | ||
| max_join_size | numeric | GLOBAL | SESSION | |
| max_length_for_sort_data | numeric | OFFLINE | ||
| max_prepared_stmt_count | numeric | GLOBAL | ||
| max_relay_log_size | numeric | GLOBAL | ||
| max_seeks_for_key | numeric | GLOBAL | SESSION | |
| max_sort_length | numeric | GLOBAL | SESSION | |
| max_sp_recursion_depth | numeric | OFFLINE | ||
| max_tmp_tables | numeric | GLOBAL | SESSION | |
| max_user_connections | numeric | GLOBAL | ||
| max_write_lock_count | numeric | GLOBAL | ||
| multi_range_count | numeric | GLOBAL | SESSION | |
| myisam_data_pointer_size | numeric | GLOBAL | ||
| myisam_max_sort_file_size | numeric | GLOBAL | SESSION | |
| myisam_recover_options | boolean | OFFLINE | ||
| myisam_repair_threads | numeric | GLOBAL | SESSION | |
| myisam_sort_buffer_size | numeric | GLOBAL | SESSION | |
| myisam_stats_method | enum | GLOBAL | SESSION | |
| net_buffer_length | numeric | GLOBAL | SESSION | |
| net_read_timeout | numeric | GLOBAL | SESSION | |
| net_retry_count | numeric | GLOBAL | SESSION | |
| net_write_timeout | numeric | GLOBAL | SESSION | |
| new | boolean | OFFLINE | ||
| old_passwords | boolean | GLOBAL | SESSION | |
| open_files_limit | numeric | OFFLINE | ||
| optimizer_prune_level | numeric | GLOBAL | SESSION | |
| optimizer_search_depth | numeric | GLOBAL | SESSION | |
| pid_file | string | OFFLINE | ||
| port | numeric | OFFLINE | ||
| preload_buffer_size | numeric | GLOBAL | SESSION | |
| prepared_stmt_count | numeric | OFFLINE | ||
| protocol_version | numeric | OFFLINE | ||
| query_alloc_block_size | numeric | GLOBAL | SESSION | |
| query_cache_limit | numeric | GLOBAL | ||
| query_cache_min_res_unit | numeric | OFFLINE | ||
| query_cache_size | numeric | GLOBAL | ||
| query_cache_type | enumeration | GLOBAL | SESSION | |
| query_cache_wlock_invalidate | boolean | GLOBAL | SESSION | |
| query_prealloc_size | numeric | GLOBAL | SESSION | |
| range_alloc_block_size | numeric | GLOBAL | SESSION | |
| read_buffer_size | numeric | GLOBAL | SESSION | |
| read_only | numeric | GLOBAL | ||
| read_rnd_buffer_size | numeric | GLOBAL | SESSION | |
| relay_log_purge | boolean | OFFLINE | ||
| relay_log_space_limit | numeric | OFFLINE | ||
| rpl_recovery_rank | numeric | GLOBAL | ||
| secure_auth | boolean | GLOBAL | ||
| server_id | numeric | GLOBAL | ||
| skip_external_locking | boolean | OFFLINE | ||
| skip_networking | boolean | OFFLINE | ||
| skip_show_database | boolean | OFFLINE | ||
| slave_compressed_protocol | boolean | GLOBAL | ||
| slave_load_tmpdir | string | OFFLINE | ||
| slave_net_timeout | numeric | GLOBAL | ||
| slave_skip_errors | boolean | OFFLINE | ||
| slave_transaction_retries | numeric | GLOBAL | ||
| slow_launch_time | numeric | GLOBAL | ||
| socket | string | OFFLINE | ||
| sort_buffer_size | numeric | GLOBAL | SESSION | |
| sql_auto_is_null | boolean | SESSION | ||
| sql_big_selects | boolean | SESSION | ||
| sql_big_tables | boolean | SESSION | ||
| sql_buffer_result | boolean | SESSION | ||
| sql_log_bin | boolean | SESSION | ||
| sql_log_off | boolean | SESSION | ||
| sql_log_update | boolean | SESSION | ||
| sql_low_priority_updates | boolean | GLOBAL | SESSION | |
| sql_max_join_size | numeric | GLOBAL | SESSION | |
| sql_mode | enumeration | GLOBAL | SESSION | |
| sql_notes | boolean | SESSION | ||
| sql_quote_show_create | boolean | SESSION | ||
| sql_safe_updates | boolean | SESSION | ||
| sql_select_limit | numeric | SESSION | ||
| sql_slave_skip_counter | numeric | GLOBAL | ||
| sql_warnings | boolean | SESSION | ||
| ssl_ca | string | OFFLINE | ||
| ssl_capath | string | OFFLINE | ||
| ssl_cert | string | OFFLINE | ||
| ssl_cipher | string | OFFLINE | ||
| ssl_key | string | OFFLINE | ||
| storage_engine | enumeration | GLOBAL | SESSION | |
| sync_binlog | numeric | GLOBAL | ||
| sync_frm | boolean | GLOBAL | ||
| system_time_zone | string | OFFLINE | ||
| table_cache | numeric | GLOBAL | ||
| table_lock_wait_timeout | numeric | OFFLINE | ||
| table_type | enumeration | GLOBAL | SESSION | |
| thread_cache_size | numeric | GLOBAL | ||
| thread_stack | numeric | OFFLINE | ||
| time_format | — | OFFLINE | ||
| time_zone | string | GLOBAL | SESSION | |
| timed_mutexes | boolean | OFFLINE | ||
| tmp_table_size | enumeration | GLOBAL | SESSION | |
| tmpdir | string | OFFLINE | ||
| transaction_alloc_block_size | numeric | GLOBAL | SESSION | |
| transaction_prealloc_size | numeric | GLOBAL | SESSION | |
| tx_isolation | enumeration | GLOBAL | SESSION | |
| updatable_views_with_limit | enumeration | GLOBAL | SESSION | |
| version | string | OFFLINE | ||
| version_bdb | string | OFFLINE | ||
| version_comment | string | OFFLINE | ||
| version_compile_machine | string | OFFLINE | ||
| version_compile_os | numeric | OFFLINE | ||
| wait_timeout | numeric | GLOBAL | SESSION |
Source : http://www.pythian.com/blogs/447/table-of-mysql-parameters
