Using Options to Set MySQL Program Variables

Many of the MySQL programs have internal variables that are set during runtime using the SET statement. Most of these program variables can also be set at server startup with the help of the same syntax that applies to specifying the program options.

Example 1

The mysql has a max_allowed_packet variable which controls the maximum size of its communication buffer.

To set this max_allowed_packet variable for mysql to a value of 16MB, either of the below mentioned commands can be used −

mysql --max_allowed_packet=16777216
(or)
mysql --max_allowed_packet=16M

The first command specifies the value in terms of bytes. On the other hand, the second command specifies the value in megabytes (MB, i.e M).

For variables that have a numeric value, the value could be given with the help of a suffix of K, M, or G that would indicate a multiplier of 1024, 10242 or 10243.

Example 2

In an option file, variable settings will be provided without the leading dashes −

[mysql]
max_allowed_packet=16777216
(or)
[mysql]
max_allowed_packet=16M

If required, underscores can be specified in an option name as dashes. The below mentioned option groups are both equivalent.

Both of them will set the size of the server's key buffer to 512MB.

[mysqld]
key_buffer_size=512M
(or)
[mysqld]
key-buffer-size=512M