Many MySQL programs have internal variables that can be set at runtime using the SET statement. See Section 13.7.4, "SET Syntax", and Section 5.1.5, "Using System Variables".

Most of these program variables also can be set at server startup by using the same syntax that applies to specifying program options. For example, mysql has a max_allowed_packet variable that controls the maximum size of its communication buffer. To set the max_allowed_packet variable for mysql to a value of 16MB, use either of the following commands:

shell> mysql
        --max_allowed_packet=16777216shell> mysql
        --max_allowed_packet=16M

The first command specifies the value in bytes. The second specifies the value in megabytes. For variables that take a numeric value, the value can be given with a suffix of K, M, or G (either uppercase or lowercase) to indicate a multiplier of 1024, 1024² or 1024³. (For example, when used to set max_allowed_packet, the suffixes indicate units of kilobytes, megabytes, or gigabytes.)

In an option file, variable settings are given without the leading dashes:

[mysql]max_allowed_packet=16777216

Or:

[mysql]max_allowed_packet=16M

If you like, underscores in a variable name can be specified as dashes. The following option groups are equivalent. Both set the size of the server's key buffer to 512MB:

[mysqld]key_buffer_size=512M[mysqld]key-buffer-size=512M

A variable can be specified by writing it in full or as any unambiguous prefix. For example, the max_allowed_packet variable can be set for mysql as --max_a, but not as --max because the latter is ambiguous:

shell> mysql --max=1000000mysql: ambiguous option '--max=1000000' (max_allowed_packet, max_join_size)

Be aware that the use of variable prefixes can cause problems in the event that new variables are implemented for a program. A prefix that is unambiguous now might become ambiguous in the future.

Suffixes for specifying a value multiplier can be used when setting a variable at server startup, but not to set the value with SET at runtime. On the other hand, with SET you can assign a variable's value using an expression, which is not true when you set a variable at server startup. For example, the first of the following lines is legal at server startup, but the second is not:

shell> mysql --max_allowed_packet=16Mshell> mysql --max_allowed_packet=16*1024*1024

Conversely, the second of the following lines is legal at runtime, but the first is not:

mysql> SET GLOBAL
        max_allowed_packet=16M;mysql> SET GLOBAL
        max_allowed_packet=16*1024*1024;