Trove/Configurations/mysql-options
< Trove | Configurations
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.
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 | 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 | true | false | 1 | 65535 | Maximum number of concurrent connections to allow into the server | |
max_heap_table_size | Yes | true | true | 16384 | 1.84467E+18 | Maximum size to which a user created MEMORY table can grow | |
tmp_table_size | Yes | 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. |
Special Cases
- 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.