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.
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 reason: 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 ......
example: mysqld --port=3306 mysqld -P3306 mysqld -P 3306
be careful: The password cannot have spaces mysqld -proot
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
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
[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 ......
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
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 ......
Format: Show variables [like matching patterns];
For example: SHOW VARIABLES LIKE 'default_storage_engine';
Set by startup item
Command line settings
mysqld --default-storage-engine=MyISAM --max-connections=10
[mysqld] 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
Format: 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';
If a client changes a system variable in the
GLOBALThe scope value does not affect the scope of the system variable in the currently connected client
SESSIONThe value of will only affect subsequent connected clients in the scope of
SESSIONThe value of.
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
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
Format: Show [global | session] status [like matching pattern]; For example: SHOW STATUS LIKE 'thread%';
reference resources:How MySQL works: understanding MySQL from the root
This work adoptsCC agreementReprint must indicate the author and the link of this article