MySQL Configuration File Variable Tutorial-Part 2

auto_increment_increment & auto_increment_offset

Both variables are use with master-to-master replication, and can be used to control the operation of AUTO_INCREMENT columns. Both variables have global and session values and each can assume an integer value between 1 and 65,535 inclusive. Setting the value of either of these two variables to 0 causes its value to be set to 1 instead. Attempting to set the value of either of these two variables to an integer greater than 65,535 or less than 0 causes its value to be set to 65,535 instead. These two variables affect AUTO_INCREMENT column behavior. But AUTO_INCREMENT column range is not limited to 65,535.

Click here for more details.

back_log

This variable deals with the number of outstanding connection requests MySQL can have. This comes into play when the main MySQL thread gets very many connection requests in a very short time. It then takes some time (although very little) for the main thread to check the connection and start a new thread. The back_log value indicates how many requests can be stacked during this short time before MySQL momentarily stops answering new requests. You need to increase this only if you expect a large number of connections in a short period of time. In other words, this value is the size of the listen queue for incoming TCP/IP connections.

Click here for more details.

basedir

This variable specifies the path to the MySQL installation directory. All paths are usually resolved relative to this directory. If you are installing MySQL manually than this will play a important role.

Click here for more details.

big-tables

This variable is used to set the flag to store the large result sets. Allow large result sets by saving all temporary sets in files. This option prevents most “table full” errors, but also slows down queries for which in-memory tables would suffice.

Click here for more details.

binlog_cache_size

This variable basically used by that storage engines which having a transactional query support. It gives the size of the cache to hold the SQL statements for the binary log during a transaction. A binary log cache is allocated for each client if the server supports any transactional storage engines and if the server has the binary log enabled (–log-bin option). If you often use large, multiple-statement transactions, you can increase this cache size to get more performance.

Click here for more details.

binlog-format

This variable specifies whether to use row-based, statement-based, or mixed replication.

Click here for more details.

bulk_insert_buffer_size

This variable is used to give a boost performance to the MyISAM engine. MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT … SELECT, INSERT … VALUES (…), (…), …, and LOAD DATA INFILE when adding data to nonempty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB.

Click here for more details.

character-set-filesystem

This variable specified the file system character set. This variable is used to interpret string literals that refer to file names, such as in the LOAD DATA INFILE and SELECT … INTO OUTFILE statements and the LOAD_FILE() function. Such file names are converted from character_set_client to character_set_filesystem before the file opening attempt occurs. The default value is binary, which means that no conversion occurs. For systems on which multi-byte file names are allowed, a different value may be more appropriate. For example, if the system represents file names using UTF-8, set character_set_filesystem to ‘utf8′.

Click here for more details.

character-set-server

This variable specifies the server character set. Use charset_name as the default server character set. If you use this option to specify a nondefault character set, you should also use collation-server to specify the collation.

Click here for more details.