MySQL Configuration

Check

To see the current values used by a running server ...

SHOW VARIABLES;  

You can modify it further like any query:

SHOW VARIABLES LIKE '%pool%';

Or query the information schema with something like...

SELECT @@variable_name

or...

SELECT variable_name,

       variable_value 

  FROM performance_schema.global_variables

 WHERE variable_name LIKE '%pool%';

To see the values that a server uses based on its compiled-in defaults and any option files that it reads...

mysqld --verbose --help

To see the values that a server uses based on only its compiled-in defaults, ignoring any option files...

mysqld --no-defaults --verbose --help

Variables

  • innodb_flush_log_at_trx_commit
  • innodb_log_buffer_size
  • innodb_log_file_size
  • innodb_log_files_in_group
  • innodb_log_group_home_dir
  • innodb_rollback_segments
  • long_query_time
  • slow_query_log
  • slow_query_log_always_write_time
  • slow_query_log_file
  • slow_query_log_use_global_control
  • log_slow_admin_statements
  • log_slow_filter
  • log_slow_rate_limit
  • log_slow_rate_type
  • log_slow_slave_statements
  • log_slow_sp_statements
  • log_slow_verbosity
  • log_queries_not_using_indexes
  • log_throttle_queries_not_using_indexes
  • binlog_expire_logs_seconds
  • expire_log_days (deprecated)
  • max_binlog_size
  • max_binlog_files
  • log_replica_updates
  • log_slave_updates (deprecated)
  • innodb_default_row_format
  • innodb_file_per_table
  • innodb_checksum_algorithm
  • max_heap_table_size
  • innodb_buffer_pool_size
  • innodb_buffer_pool_chunk_size
  • innodb_buffer_pool_instances
  • max_execution_time
  • max_connections
  • interactive_timeout
  • wait_timeout
  • innodb_file_format
  • open_files_limit
  • innodb_io_capacity
  • innodb_io_capacity_max
  • innodb_flush_method
  • innodb_fsync_threshold
  • innodb_use_fdatasync
  • innodb_use_native_aio
  • innodb_write_io_threads
  • innodb_read_io_threads
  • ssl_ca
  • ssl_cert
  • ssl_key
  • ssl_version
  • generated_random_password_length
  • default_authentication_plugin

Update

SET GLOBAL parameter=value;

To make the change permanent you should also update /etc/my.cnf

Sample my.cnf

[mysqld]

user = mysql

default-storage-engine = InnoDB

datadir = /var/lib/mysql

socket = /var/lib/mysql/mysql.sock

pid-file = /var/run/mysqld/mysqld.pid

log-error = /var/log/mysqld.log

open-files-limit = 10000

binlog-format = ROW

binlog_expire_logs_seconds = 2592000

log-bin = /var/lib/mysql/log

log-replica-updates = ON

sync-binlog = 1

max_binlog_size = 100M

server-id = 0

innodb-buffer-pool-size = 5G

innodb-buffer-pool-instances = 8

innodb-flush-method = O_DIRECT

innodb-file-per-table = 1

innodb-checksum-algorithm = crc32

innodb-flush-log-at-trx-commit = 1

innodb-log-file-size = 1024M

slow-query-log-file = /var/log/mysq-slow.log

ssl-ca = CA.cer

ssl-cert = cert.pem

ssl-key = key.pem

tls-version = TLSv1.2

secure-log-path = /var/log

Bibliography