Documentation Home
MySQL 9.2 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.5Mb
PDF (A4) - 40.6Mb
Man Pages (TGZ) - 259.7Kb
Man Pages (Zip) - 366.8Kb
Info (Gzip) - 4.1Mb
Info (Zip) - 4.1Mb


MySQL 9.2 Reference Manual  /  ...  /  Using Options to Set Program Variables

6.2.2.5 Using Options to Set Program Variables

Many MySQL programs have internal variables that can be set at runtime using the SET statement. See Section 15.7.6.1, “SET Syntax for Variable Assignment”, and Section 7.1.9, “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:

Press CTRL+C to copy
mysql --max_allowed_packet=16777216 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 to indicate a multiplier of 1024, 10242 or 10243. (For example, when used to set max_allowed_packet, the suffixes indicate units of kilobytes, megabytes, or gigabytes.) As of MySQL 8.0.14, a suffix can also be T, P, and E to indicate a multiplier of 10244, 10245 or 10246. Suffix letters can be uppercase or lowercase.

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

Press CTRL+C to copy
[mysql] max_allowed_packet=16777216

Or:

Press CTRL+C to copy
[mysql] max_allowed_packet=16M

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

Press CTRL+C to copy
[mysqld] key_buffer_size=512M [mysqld] key-buffer-size=512M

Suffixes for specifying a value multiplier can be used when setting a variable at program invocation time, 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 program invocation time, but the second is not:

Press CTRL+C to copy
$> mysql --max_allowed_packet=16M $> mysql --max_allowed_packet=16*1024*1024

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

Press CTRL+C to copy
mysql> SET GLOBAL max_allowed_packet=16M; mysql> SET GLOBAL max_allowed_packet=16*1024*1024;