Detailed explanation of MySQL startup options and system variables examples

This article describes the MySQL startup options and system variables. Share with you for your reference, the details are as follows:

MySQL configuration information can be implemented in two ways. One is the command line form, which brings relevant configuration parameters after starting the MySQL service. This method will become invalid after MySQL is restarted. Another way is to write to the configuration file, such as my.cnf, which will take effect when you start or restart the MySQL service. This method is permanent.

Boot Options

Command Line

When starting the MySQL service command, bring the configuration parameters

For the startup method, please refer to this article: MySQL startup and connection method

Command format:
Startup command --startup option 1 [= value 1] --startup option 2 [= value 2] ... --startup option n [= value n]

For example:
mysqld --default-storage-engine=MyISAM //Set the default storage engine

Wrong example:
mysqld --default-storage-engine = MyISAM //Set the default storage engine Reason:
Because there cannot be a space between the equal sign of the startup item and the value

Long and short forms of parameters

Configuration parameters have long and short forms. Some of them have the same function, but are written differently.

--host => -h //Host--port => -P //Port--user => -u //User--password => -p //Password--version => -V //Version......

example:
mysqld --port=3306
mysqld -P3306
mysqld -P 3306

Notice:
The password cannot have spaces mysqld -proot

Configuration Files

The location of the configuration file my.cnf may be the following. If no configuration file is specified when the MySQL service is started, it will be searched, read and initialized from the following locations.

* /etc/my.cnf 
* /etc/mysql/my.cnf
* defaults-extra-file //Specified additional configuration file path* SYSCONFDIR/my.cnf //cmake compilation and installation situation* $MYSQL_HOME/my.cnf //Set environment variables, default installation path* ~/.my.cnf //User-specific options, home directory* ~/.mylogin.cnf //User-specific login path options (client only), modified by mysql_config_editor, not a pure file

Notice

1. The startup options specified in the configuration file are not allowed to be prefixed with --, and only one option is specified per line, and there can be blank characters around =
2. If we set the same startup options in multiple configuration files, the last configuration file will take precedence.
3. If the same startup option appears in both the command line and the configuration file, the startup option in the command line takes precedence.
4. mysqld --defaults-file=/tmp/myconfig.txt
//When the program starts, it will only search for configuration files in the /tmp/myconfig.txt path. If the file does not exist or cannot be accessed, an error occurs

Configuration Group

The configuration file can have the following groups: mysqld, mysqld_safe, mysql.server, mysql, mysqladmin, mysqldump, and can be configured for different groups

Content Format

[server]
(Specific startup options...)
[mysqld]
(Specific startup options...)
[mysqld_safe]
(Specific startup options...)
[client]
(Specific startup options...)
[mysql]
(Specific startup options...)
[mysqladmin]
(Specific startup options...)

example:
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log
#Optimize configuration wait_timeout=10
back_log=600
key_buffer_size = 2048M
read_buffer_size = 100M
max_allowed_packet = 1000M
thread_stack = 192K
thread_cache_size = 4
myisam-recover-options = BACKUP
max_connections = 4000
max_user_connections = 0
max_connect_errors = 65535
open_files_limit = 10240
......

Notice
1. The startup options under the [server] group will apply to all server programs, such as mysqld, mysqld_safe, and mysql.server.
2. The startup options under the [client] group will apply to all client programs, such as mysql, mysqladmin, and mysqldump
3. The priority of multiple groups in the same configuration file will be based on the startup options in the last group that appears.

System variables

The MySQL server program uses many variables that affect program behavior during its operation. They are called MySQL system variables.

For example:
1. The number of clients allowed to connect simultaneously is indicated by the system variable max_connections
2. The default storage engine of the table is represented by the system variable default_storage_engine
3. The size of the query cache is represented by the system variable query_cache_size
......

Check

Format:
SHOW VARIABLES [LIKE matched pattern];

For example:
SHOW VARIABLES LIKE 'default_storage_engine'; 

set up

Via startup settings

Command line settings

mysqld --default-storage-engine=MyISAM --max-connections=10

Profile Settings

[mysqld]
default-storage-engine = MyISAM
max-connections = 10
......

Notice:
For startup options, if the startup option name consists of multiple words, the words can be connected with a hyphen - or an underscore _, but the words of the corresponding system variables must be connected with an underscore _ (that is, when viewing through show or setting through set)

Set during server program running

The cool thing about system variables is that, for most system variables, their values ​​can be modified dynamically while the server is running without having to stop and restart the server.

However, system variables have global and current session scopes.

Scope

GLOBAL: Global variables that affect the overall operation of the server.
SESSION: Session variables that affect the operation of a client connection. (Alternative name: LOCAL)

Format:
1. SET [GLOBAL|SESSION] system variable name = value;
2. SET [@@(GLOBAL|SESSION).]var_name = XXX;

global

For example:
1. SET GLOBAL default_storage_engine = InnoDB;
2. SET @@GLOBAL.default_storage_engine = InnoDB;

session

For example:
1. SET SESSION default_storage_engine = InnoDB;
2. SET @@SESSION.default_storage_engine = InnoDB;
3. SET default_storage_engine = InnoDB; //Default session

Check

Format: SHOW [GLOBAL|SESSION] VARIABLES [LIKE matching pattern];
1. SHOW SESSION VARIABLES LIKE 'default_storage_engine';
2. SHOW GLOBAL VARIABLES LIKE 'default_storage_engine';

Notice:
If a client changes the value of a system variable in the GLOBAL scope, it will not affect the value of the system variable in the SESSION scope of the currently connected client, but will only affect the value of the system variable in the SESSION scope of subsequent clients.

Additional Notes

Not all system variables have GLOBAL and SESSION scopes.

* Some system variables have only GLOBAL scope, such as max_connections, which indicates the maximum number of client programs that the server program supports to connect at the same time
* Some system variables have only SESSION scope, such as insert_id, which indicates the initial value of a column when inserting into a table containing an AUTO_INCREMENT column.
* Some system variables have both GLOBAL and SESSION scopes, such as the default_storage_engine we used earlier, and in fact most system variables are like this

Some system variables are read-only and cannot be set.

For example, version indicates the current MySQL version. Our client cannot set its value and can only view it in the SHOW VARIABLES statement.

Relationship between startup options and system variables

Startup options are some parameters that we programmers pass when the program starts, while system variables are variables that affect the running behavior of the server program.

* Most system variables can be passed as startup options
* Some system variables are automatically generated during program execution and cannot be set as startup options, such as auto_increment_offset, character_set_client, etc.
* Some startup options are not system variables, such as defaults-file

State variables

In order to help us better understand the operation of the server program, the MySQL server program maintains many variables about the program's running status, which are called status variables.

For example, Threads_connected indicates how many clients are currently connected to the server, and Handler_update indicates how many rows have been updated.

Since status variables are used to display the running status of the server program, their values ​​can only be set by the server program itself, and we programmers cannot set them.

Check

Format:
SHOW [GLOBAL|SESSION] STATUS [LIKE matched_pattern];
For example:
SHOW STATUS LIKE 'thread%';

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.