MySQL Configuration Settings

The MySQL server has many operating parameters, which can be set through configuration file or option file (/etc/my.cnf). It is also possible to change many parameters at runtime. ScaleGrid console offers a simple user interface (UI) to configure some of the important parameters for your MySQL deployments.

Finding Your MySQL Configuration Settings

Here are the steps to access your MySQL configuration settings:

Log into the ScaleGrid console.
Go to your MySQL page, then click into your MySQL cluster you wish to configure.
Select the Admin tab at the top.
Click on the Configuration menu option on the left.

As shown above, MySQL configuration parameter names, their current values, and a brief description about each parameter is presented in a tabular format in your admin configurations.

📘
Non-editable field values
The value field for some of the parameters are grayed out to indicate that these are not editable parameters for the deployment.

Updating Your MySQL Parameters

There is a search bar at the top as highlighted below for the easy access of the parameter that you would like to change.

For example, the screenshot below shows the parameter ‘sql_mode’ that can take a combination of many values and can be selected from a drop down list. Once selections are made, click on the green Save button to set this configuration for your MySQL deployment.

After clicking the Save button, you will see a Confirmation Dialog showing a summary of changes you have made as below.

After clicking the green Confirm button, the configuration will be applied to your deployment through an ‘Update Configuration’ job that can be monitored from the Jobs tab.

Updating MySQL Configuration Parameters That Need a Server Restart

Some of the configuration settings in MySQL take effect only if the server is restarted. One such parameter is ‘innodb_log_file_size’. If you set this parameter and click on the Save button - the Confirmation dialog will show a Warning message that the config value will require a MySQL restart.

If you Confirm, then the configuration is applied on your MySQL deployment in a rolling fashion one server at a time and the server is restarted.

For instance, in a 3-node master-slave setup, the configuration is first applied on the slave nodes and then they are restarted. The Master is stepped down and one of the Slaves is promoted as a new Master. The configuration will then be applied on the old Master. The whole operation is done with minimal down time.

This rolling job can be monitored from the Jobs tab as below:

MySQL Parameter Names and Descriptions

Check out the list of MySQL parameters available to review and configure at ScaleGrid:

Parameter	Description	Editable
port	MySQL server port	No
skip_name_resolve	Disable DNS name resolution	No
user	User account that runs mysql service.	No
pid_file	PID file	No
socket	Socket file	No
require_secure_transport	Server mandates secure connections.	No
ssl	Enable SSL on the server.	No
log_bin	Basename with path of binary log files.	No
expire_logs_days	Number of days for automatic binary log file removal.	Yes
sync_binlog	Number of binary log commit groups to collect before synchronizing the binary log to disk.	No
binlog_group_commit_sync_delay	Number of microseconds the binary log commit waits before synchronizing the binary log file to disk.	Yes
max_connect_errors	Max number of connection errors from a host before these server blocks it from further connections.	Yes
max_allowed_packet	Maximum size of a packet (Bytes) that the server can send and receive.	Yes
max_heap_table_size	Maximum size to which user-created MEMORY tables are permitted to grow.	No
max_connections	Maximum permitted number of simultaneous client connections.	Yes
max_user_connections	Maximum permitted number of simultaneous client connections.	No
thread_cache_size	Number of threads the server should cache for reuse.	Yes
open_files_limit	Number of file descriptors available to mysqld.	Yes
table_open_cache	Number of open tables for all threads.	No
table_definition_cache	Number of table definitions that can be stored in definition cache.	No
relay_log	Basename with path of relay log files.	No
gtid_mode	Enable GTID based logging	No
enforce_gtid_consistency	Server allows execution of only statements that can be safely logged using a GTID.	No
binlog_format	Binary log format	No
log_slave_updates	Controls whether updates received by a slave server from a master server should be logged to the slave's own binary log.	No
slave_net_timeout	Number of seconds to wait for more data from a master/slave connection before aborting the read.	Yes
master_info_repository	To determine whether master info is maintained in a file or table.	No
relay_log_info_repository	To determine whether the relay logs are maintained as a file or table.	No
sync_master_info	Number of events after which slave updates the IO thread position in its master info repository table.	No
sync_relay_log	Number of events to relay log after which slave flushes the relay logs to disk.	No
relay_log_recovery	Enable automatic relay log recovery immediately following server startup.	No
slave_parallel_workers	Number of slave applier threads for executing replication transactions in parallel.	Yes
slave_preserve_commit_order	Ensure transaction commit happens in the same order as they appear in slave relay log.	No
slave_parallel_type	Policy to decide parallel transaction execution.	No
rpl_semi_sync_master_timeout	Time in milliseconds after which master switches to async mode of replication if it has not received back from slave.	Yes
rpl_semi_sync_master_wait_for_slave_count	The number of slave acknowledgments the master must receive per transaction before proceeding.	No
rpl_semi_sync_master_wait_no_slave	Controls whether the master waits for the timeout period configured by rpl_semi_sync_master_timeout to expire, even if the slave count drops to less than the number of slaves configured by rpl_semi_sync_master_wait_for_slave_count during the timeout period.	No
relay_log_space_limit	An upper limit on the total size (in bytes) of all relay logs on the slave. A value of 0 means "no limit".	Yes
max_relay_log_size	Maximum size (in bytes) of a slave relay log after which it rotates. Value of 0 means this size will be same as max_binlog_size.	Yes
max_binlog_size	Maximum size (in bytes) of a binary log after which it rotates.	Yes
datadir	The path to the MySQL server data directory.	No
general_log	Enable General log	No
general_log_file	General Log File	No
log_error	Error log file	No
default_storage_engine	Storage Engine for MySQL	No
innodb_log_files_in_group	Number of log files in the log_group.	No
innodb_flush_log_at_trx_commit	Frequency of transactions at which InnoDB log buffer is written to the log and flushed to disk.	No
innodb_flush_method	Method used to flush data to InnoDB data and log files.	No
innodb_file_per_table	Determines whether each newly created table will have its data and index as part of separate ibd file.	No
innodb_log_file_size	Defines the size (in bytes) of each log file in a log_group.	Yes
innodb_buffer_pool_size	Size (in bytes) of memory used by InnoDB to cache table and index data.	Yes
innodb_io_capacity	Upper limit on the number of I/O operations performed per second by InnoDB background tasks.	Yes
innodb_adaptive_hash_index	Indicates whether InnoDB adaptive hash indexes are enabled or disabled.	Yes
innodb_lock_wait_timeout	Length of time in seconds an InnoDB transaction waits for a row lock before giving up.	Yes
log_queries_not_using_indexes	Logs queries that are expected to retrieve all rows to slow query log.	Yes
log_slow_admin_statements	Include slow administrative statements in the statements written to the slow query log.	Yes
log_throttle_queries_not_using_indexes	Variable to limit the number of slow queries per minute that can be written to the slow query log.	Yes
long_query_time	If a query takes longer than this many seconds, it is considered as a slow query.	Yes
slow_query_log	Generate slow query log files	No
slow_query_log_file	Name of the slow query log file	No
symbolic_links	Enable symbolic link support	No
interactive_timeout	Number of seconds the server waits for activity on an interactive connection before closing it.	Yes
div_precision_increment	Number of seconds the server waits for activity on an interactive connection before closing it.	Yes
sql_mode	Set of options that decides syntax supported by MySQL and also data validation checks it performs.	Yes
event_scheduler	State of the Event Scheduler	Yes

Finding Your MySQL Configuration Settings

📘Non-editable field values

Updating Your MySQL Parameters

Updating MySQL Configuration Parameters That Need a Server Restart

MySQL Parameter Names and Descriptions

📘
Non-editable field values