MySQL: Set Server Variables

There are more than one way to set or change paramenter values.

1.Set my.cnf file.

Parameter values are read into memory during server start up.

Parameter is entered in the form of key value pair. All the parameter should enter in the my,cnf file so that the server will apply the parameter values in start up. After any change to parameter values using Set Global option, values should also change in the parameter file.

The syntax of the paramer key value paire is:

parameter=value
key_buffer_size=1073741824;

To translate GB, MB, KG to byte, use the following formula:
1GB = 1*1024*1024*1024
1MB = 1*1024*1024
1KB = 1*1024
1.5GB = 1.5*1024*1024*1024

2.Enter in command line or in the start up script

You may find this is not as convince as using the configuration file.

shell> mysqld --key_buffer_size=64M --table_cache=256 \
--sort_buffer_size=4M --read_buffer_size=1M &

3.Use Set GLOBAL command for Dynamic Variable from mysql client

If the parameter is a dynamic varable, we can use MYSQL client to change the value in run time.

Mysql> SET GLOBAL key_buffer_size=1073741824;

To translate values to bytes, you can use the following formula to enter GB, MB, or KB.

1GB = 1*1024*1024*1024
1MB = 1*1024*1024
1KB = 1*1024
1.5GB = 1.5*1024*1024*1024

MYSQL> SET GLOBAL key_buffer_size=1*1024*1024*1024;

The parameter values are load when the sessions started. If we want to use the new values that we just changed using the SET GLOBAL command, we need to start a new session.