Trove/Configurations/mysql-options

Overview

These are a list of the options that can be part of a configuration group for mysql.

Options that are not dynamic require a restart of the mysql service.

Some variables require creating a new mysql connection so that they will be applied.

Booleans

option	Configurable by User	default template (512MB)	dynamic	special case	min	max	description
innodb_file_per_table	Yes		false	false	0	1	Create a separate tablespace for each innodb table when enabled
autocommit	Yes		true	false	0	1	Enable or disable MySQL's autocommit feature
local_infile	Yes	0	true	false	0	1	Enable client side import files for clients not supporting mysqldump formatted files

Integers

option	Configurable by User	default template (512MB)	dynamic	special case	min	max	description
key_buffer_size	Yes	50M	true	false	0	4294967296	MyISAM index buffer
join_buffer_size	Yes		true	false	0	4294967296	Memory buffer used for joins on multiple tables that do not use an index
sort_buffer_size	Yes		true	false	2097144	1.84467E+19	Buffer used for all queries that perform a sort action
innodb_buffer_pool_size	Yes	150M	false	false	0	68719476736	Buffer used by innodb to cache data and indexes of it's tables
innodb_flush_log_at_trx_commit	Yes		true	false	0	2	Frequency by which Innodb will flush it's logs to disk
innodb_log_buffer_size	Yes		false	false	1048576	4294967295	Buffer that InnoDB uses to write the log files on disk
innodb_log_file_size	Yes		false	true	1048576	4294967295	Size in bytes of each log file in a log group used by InnoDB
innodb_open_files	Yes		false	false	10	4294967295	Maximum number of open .ibd files
innodb_thread_concurrency	Yes		true	false	0	1000	Number of threads kept open concurrently inside InnoDB
sync_binlog	Yes		true	false	0	1.84467E+19	Synchronize transactions to disk up to X number of transactions
auto_increment_increment	Yes		true	false	1	65535	Used for master to master replication. Represents the increment of auto_incremented values in MySQL
auto_increment_offset	Yes		true	false	1	65535	Used for master to master replication. Represents the offset of auto_incremented values in MySQL
bulk_insert_buffer_size	Yes		true	false	0	1.84467E+19	Cache for use with bulk inserts
expire_logs_days	Yes		true	false	1	65535	Number of days for automatic binary log file removal
ft_min_word_len	Yes		false	true	1	65535	Minimum length of the word to be included in a FULLTEXT index
connect_timeout	Yes		true	false	1	65535	Number of seconds that mysql will wait for a connect packet before responding with a bad handshake
interactive_timeout	Yes		true	false	1	65535	The number of seconds the server waits for activity on an interactive connection before closing it
max_allowed_packet	Yes	1M	true	false	1024	1073741824	Maximum size of one packet or any generated/intermediate string
max_connect_errors	Yes		true	false	1	1.84467E+19	When X number of connection errors are met by a given host it is blocked indefinitely until FLUSH HOSTS is run on the server
max_connections	Yes	100+10	true	false	1	65535	Maximum number of concurrent connections to allow into the server
max_heap_table_size	Yes	16M	true	true	16384	1.84467E+18	Maximum size to which a user created MEMORY table can grow
tmp_table_size	Yes	16M	true	true	1024	4294967295	Maximum size of internal in-memory temporary tables
myisam_sort_buffer_size	Yes		true	false	4	1.84467E+19	Size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE
server_id	Yes		false	false	1	100000	The server's id for binary log shipping. This is mainly used for replication.
wait_timeout	Yes		true	false	1	31536000	The number of seconds the server waits for activity on a noninteractive connection before closing it.

Strings

option	Configurable by User	default template (512MB)	dynamic	special case	description
character_set_client	Yes		true	false	Character set for statements that arrive from the client
character_set_connection	Yes		true	false	Character set used for literals that do not have a character set introducer and for number-to-string
character_set_database	Yes		true	false	Character set used by the default database
character_set_filesystem	Yes		true	false	File system character set
character_set_results	Yes		true	false	Character set used for returning query results
character_set_server	Yes		true	false	Server's default character set
collation_connection	Yes		true	false	Collation of the connection character set
collation_database	Yes		true	false	Collation used by the default database
collation_server	Yes		true	false	Server's default collation
ft_stopword_file	Yes		false	true	File from which to read the list of stopwords for full text searches
default_time_zone	Yes		true	true	The default time zone to use for date/time functions

Special Cases

(todo) These will be implemented after initial work is done to create a configuration.

• ft_stopword_file requires the ability to upload a file to the server. We may not be able to support this feature immediately but it is critical to support this as soon as possible.
• tmp_table_size cannot be more than max_heap_table_size or else the lesser of the two will be the size that is applied to system generated tmp tables. This only applies to tmp tables and not to user created heap/memory tables.
• innodb_log_file_size requires the following steps. Stop the service, backup and remove the old /var/lib/mysql/ib_logfileXX files, add the variable setting to the my.cnf, start the service and verify that the service started. If the service didn't come back up, stop the service, remove the newly generated files and replace them with the backed up files. If everything restarted successfully you don't need to keep the backed up files around, delete them.
• if you change ft_min_word_length or ft_max_word_length you must rebuild all indexes on all tables with full text indexes. This is done with REPAIR TABLE <tablename> QUICK;
• default_time_zone must be set as default_time_zone in the my.cnf file but must be set as time_zone in the mysql client.