MySQL startup options and system variables


The configuration information of MySQL can be realized in two ways. One is the command line form. When starting the MySQL service, the relevant configuration parameters will be brought along. This way will be invalid after the restart of MySQL. Another way is to write the configuration file, such as my.cnf, to start or restart the MySQL service, which will take effect permanently.

Startup options

command line

When the MySQL service command is started, the configuration parameters are included

For the startup mode, please refer to this article:How to start and connect mysql

Command format:
Start command -- start option 1 [= value 1] - start option 2 [= value 2]... -- start option n [= value n]
For example:
Mysqld -- default storage engine = MyISAM // set default storage engine
Error example:
Mysqld -- default storage engine = MyISAM // set default storage engine
Because the equal sign between the start item and the value cannot have spaces

Long and short forms of parameters

Configuration parameters can be divided into long form and short form. Some functions are the same, but they are written differently

--Host = > - H // host
--Port = > - P // port
--User = > - U // user
--Password = > - P // password
--Version = > - V // version
mysqld --port=3306
mysqld -P3306
mysqld -P 3306
be careful:
The password cannot have spaces
mysqld -proot

configuration file

The location of the configuration file my.cnf may be the following. For example, when the configuration file is not specified when the MySQL service is started, it will be read and initialized from the following places.

* /etc/my.cnf 
* /etc/mysql/my.cnf
*Defaults extra file // the specified extra configuration file path
*Compilation and installation of sysconfdir / my.cnf // cmake
* $MYSQL_ Home / my.cnf // set the environment variable and the default installation path
*~ /. My.cnf // user specific options, home directory
*~ /. Mylogin. CNF // user specific login Path Options (client only), MySQL_ config_ Editor modification, not pure file
be careful
1. The startup options specified in the configuration file are not allowed to be prefixed with -- and only one option is specified in each line, and = can be surrounded by white space characters
2. If we set the same startup option in multiple configuration files, the one in the last configuration file shall prevail
3. If the same startup option appears in both the command line and the configuration file, the startup option in the command line shall prevail
4. mysqld --defaults-file=/tmp/myconfig.txt  
//When the program starts, only the configuration file will be searched in the path of / TMP / myconfig.txt. If the file does not exist or cannot be accessed, an error occurs

Configuration group

The configuration file can be divided into the following groups: mysqld, mysqld_ Safe, mysql.server, mysql, mysqladmin and mysqldump can be configured for different groups

MySQL startup options and system variables

Content format
(specific startup options...)
(specific startup options...)
(specific startup options...)
(specific startup options...)
(specific startup options...)
(specific startup options...)
pid-file          = /var/run/mysqld/
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
#Optimize configuration
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
be careful
1. The startup options under the [server] group will act on all server programs, such as mysqld and mysqld_ safe、mysql.server
2. The startup options under the [Client] group will act on all client programs, such as mysql, mysqladmin and mysqldump
3. The priority of multiple groups in the same configuration file will be subject to the startup option in the last group

System variables

In the process of running MySQL server program, many variables that affect the program behavior are used, which are called MySQL system variables.

For example:
1. The number of clients allowed to be connected at the same time is the system variable max_ Connections representation
2. The default storage engine of the table uses the system variable default_ storage_ Engine said
3. The size of the query cache uses the system variable query_ cache_ Size means


Show variables [like matching patterns];
For example:
SHOW VARIABLES LIKE 'default_storage_engine';

MySQL startup options and system variables

set up

Set by startup item

Command line settings
mysqld --default-storage-engine=MyISAM --max-connections=10
Profile settings
default-storage-engine = MyISAM
max-connections = 10
be careful:
For startup options, if the startup option name consists of multiple words, dash - or underline between the words_ It can be connected, but it must be underlined between the words of the corresponding system variables_ Connect (i.e. when viewing through show or setting)

Server program running process settings

The most powerful point of system variables is that for most system variables, their values can be dynamically modified during the running of the server program without stopping and restarting the server

However, system variables can be divided into global and current session scope


Global: a global variable that affects the overall operation of the server.
Session: session variable, which affects the operation of a client connection( It's also called local
1. Set [global|session] system variable name = value;
2. SET [@@(GLOBAL|SESSION).]var_name = XXX;
For example:
1. SET GLOBAL default_storage_engine = InnoDB;
2. SET @@GLOBAL.default_storage_engine = InnoDB;
For example:
1. SET SESSION default_storage_engine = InnoDB;
2. SET @@SESSION.default_storage_engine = InnoDB;
3. SET default_ storage_ engine = InnoDB;// Default session
Format: show [global | session] variables [like matching pattern];
1. SHOW SESSION VARIABLES LIKE 'default_storage_engine';
2. SHOW GLOBAL VARIABLES LIKE 'default_storage_engine';

be careful:
If a client changes a system variable in theGLOBALThe scope value does not affect the scope of the system variable in the currently connected clientSESSIONThe value of will only affect subsequent connected clients in the scope ofSESSIONThe value of.

Supplementary notes
Not all system variables have the scope of global and session
*Some system variables only have global scope, such as max_ Connections, which indicates how many client programs are supported by the server program to connect at the same time
*Some system variables have only session scope, such as insert_ ID, which means that the auto_ The initial value of the column when the table of the increment column is inserted
*The values of some system variables have both global and session scopes, such as the default we used earlier_ storage_ Engine, and in fact, most of the system variables are like this
Some system variables are read-only and cannot be set
For example, version means the current version of MySQL. Our client can't set its value. It can only be viewed in the show variables statement.
The relationship between startup options and system variables

Startup options are some parameters passed by our programmers when the program starts, and system variables are variables that affect the running behavior of the server program

*Most system variables can be passed in as startup options
*Some system variables are generated automatically in the process of program running, and can not be set as startup options, such as auto_ increment_ offset、character_ set_ Client or something
*Some startup options are not system variables, such as defaults file

state variable

In order to let us better understand the operation of the server program, MySQL server program maintains a lot of variables about the running state of the program, which are called state variables.

For example, threads_ Connected indicates how many clients have established a connection with the server. Handler_ Update indicates how many rows of records have been updated

Because the state 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


Show [global | session] status [like matching pattern];
For example:

MySQL startup options and system variables

reference resources:How MySQL works: understanding MySQL from the root

This work adoptsCC agreementReprint must indicate the author and the link of this article


Recommended Today

[Q & A share the second bullet] MySQL search engine, after watching the tyrannical interviewer!

Hello, I’m younger brother. A few days ago, I shared the second interview question, the interview site of search engine in MySQL. This question is the interview at normal temperature. After reading it, I’m sure you will gain something in terms of database engine If you haven’t read my first share, you can refer to […]