2. MySQL control buttons – startup options and system variables

2.MySQL control buttons – startup options and system variables

  • 1. Startup options and configuration files
    • 1.1 Using options on the command line
    • 1.2 Options used in configuration files
      • 1.2.1 Configuration file path
      • 1.2.2 Contents of configuration file
      • 1.2.3 Special option groups for specific MySQL versions
      • 1.2.4 Priority of configuration files
      • 1.2.5 Priority of multiple groups in the same configuration file
      • 1.2.6 Use of defaults-file
    • 1.3 Differences between startup options in the command line and configuration files
  • 2. System variables
    • 2.1 Introduction to system variables
    • 2.2 View system variables
    • 2.3 Set system variables
      • 2.3.1 Setting via startup options
      • 2.3.2 Settings during the running of the server program
      • 2.3.3 The difference between startup options and system variables
  • 3. State variables
  • 4. Summary

1. Startup options and configuration files

MySQL‘s server and client programs have many settings:

  • For the MySQL server program you can specify:
    1. Number of clients allowed to connect simultaneously (default 151)
    2. How client and server communicate
    3. The default storage engine for tables (default InnoDB)
    4. Query cache size and other information
  • For MySQL client programs can specify:
    1. The host name or IP address, user name, password and other information of the host where the server program needs to be connected

The above setting items can all modify the default values when the program starts. The setting items specified when the program starts are also called startup options. These options control the various settings in the bin directory of the program (MySQL installation directory). executable file) after startup. These startup options can be specified on the command line or in a configuration file.

1.1 Using options on the command line

When starting the server program, each client is prohibited from using the TCP/IP network to communicate. You can add the skip-networking startup option to the command line when starting the server program:

mysqld --skip_networking

Note:

When specifying startup options on the command line, you need to prefix the option name with -. If the option name consists of multiple words, they can be connected by a dash – or an underscore _. In other words, skip_networking and skip-networking are equivalent

After starting the server program according to the above command, if you use mysql to start the client program and specify the server host name as 127.0.0.1 (in the form of IP address), the connection failure will be displayed:

mysql -h127.0.0.1 -uroot -p
Enter password:

ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (61)

When starting the client program, the -h parameter is followed by the server IP address, which means that the client requires communication with the server through the TCP/IP network.

If we want to change the default storage engine of the table, we can start the server program like this:

mysqld --default-storage-engine=MyISAM

View table structure: SHOW CREATE TABLE table name\G

To summarize, the general style of specifying startup options after the command to start the server program is as follows:

--Startup option 1[=value 1] --Startup option 2[=value 2] ... --Startup option n[=value n]

We can write each startup option on one line, add — in front of each startup option name, and use blank characters to separate each startup option.

Most MySQL programs provide a –help option that can be used to view all startup options supported by the program and their default values. Checking the startup options supported by mysqld is somewhat special, so you need to use mysqld –verbose –help.

1.2 Using options in configuration files

The startup options set on the command line only take effect for the current startup. If we write all the startup options that need to be set in the configuration file, the corresponding startup options will be loaded from this file every time the server is started. (recommend)

1.2.1 Configuration file path

When the MySQL program starts, it will look for configuration files in multiple paths. Some of these paths are fixed, and some can be specified on the command line. Different operating systems have different paths to find configuration files.

Configuration files for Windows OS

In Windows OS, MySQL will look for configuration files in sequence according to the paths shown in the table below.

Configuration files in UNIX-like OS

In UNIX-like OS, MySQL will look for configuration files in sequence according to the paths shown in the table below.

1.2.2 Contents of the configuration file

The startup options in the configuration file are divided into several groups. Each group has a group name, enclosed in square brackets []:

Several startup options can be defined under each group. Take the [server] group as an example to see how to fill in the startup options:

[server]
option1 # This is option1, this option does not require an option value
option2 = value2 # This is option2, this option requires an option value
...

Only long-form startup options can be used in the configuration file, and the startup options specified in the configuration file are not allowed to be prefixed with -. Only one option can be specified in each line, and there can be blank characters around the equal sign = (in the command line, the option name , =, no blank characters are allowed between option values). We can use # to add comments in the configuration file.

In the configuration file, different option groups are used by different programs. If the option group name is the same as the program name, the options in the group apply specifically to that program. For example: the [mysqld] and [mysql] groups apply to the mysqld server program and mysql client program respectively.

There are two option groups that are special:

  • The startup options under the [server] group will apply to all server programs
  • The startup options under the [client] group will apply to all client programs

It should be noted that both programs mysqld_safe and mysql.server will read the contents of the [mysqld] option group when they are started.

1.2.3 MySQL version-specific option groups

1.2.4 Priority of configuration files

MySQL searches for configuration files under certain fixed paths. You can also specify additional configuration file paths by specifying the default-extra-file startup option on the command line.

MySQL reads the configuration files in sequence, in the order given in Table 2-2 or Table 2-3, depending on the operating system used. Ignored if the file does not exist.

Note:

If the same startup option is set in multiple configuration files, the one in the last configuration file takes precedence.

1.2.5 Priority of multiple groups in the same configuration file

If the same startup option appears in the same configuration file, such as [mysqld], [server] group, the startup option in the last group that appears will be used. shall prevail.

1.2.6 Use of defaults-file

If you do not want MySQL to search for configuration files in the default path, you can specify the defaults-file option on the command line:

mysqld --defaults-file=/tmp/myconfig.txt

In this way, the configuration file will only be searched in the /tmp/myconfig.txt path when the program starts. If the file does not exist or is inaccessible, an error occurs.

The difference between defaults-extra-file and defaults-file:

Use defaults-extra-file to specify additional configuration file paths, and those fixed configuration file paths will also be searched.

1.3 Differences between startup options in the command line and configuration files

Most of the startup options specified on the command line can be placed in the configuration file, but some options are specifically designed for the command line, such as defaults-extra-file and defaults-file. Options such as defaults-extra-file and defaults-file are themselves designed to specify the configuration file. Path, it will be meaningless if used in the configuration file.

Note:

If the same startup option appears in both the command line and the configuration file, the startup option in the command line shall prevail!

2. System variables

2.1 Introduction to system variables

2.2 View system variables

View the system variables supported by the MySQL server program and their current values:

SHOW VARIABLES (LIKE matching pattern);

Since there are too many system variables, if you directly use SHOW VARIABLES to view them, the screen will be refreshed directly. Therefore, a LIKE expression is usually used to specify filtering. condition:

The default storage engine of the server program is InnoDB, and the maximum number of connections allowed by the client is 151.

Note:

The MySQL server actually allows max_connections + 1 client connection, the extra 1 is for the superuser (obviously this is a superuser privilege).

Wildcards can be used in LIKE expressions to perform fuzzy queries:

This finds the values of all system variables starting with default.

2.3 Setting system variables

2.3.1 Set via startup options

Most system variables can be set by passing startup options when starting the server.

  • Add startup options via command line

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

  • Add startup options through configuration files

    [server]
default-storage-engine=MyISAM
max-connections=10

Note:

For startup options, if the startup option name consists of multiple words, it is OK to connect the words with a dash (-) or an underscore (_); but for the corresponding system variables, the words are connected with each other. Must be connected using underscore (_).

2.3.2 Settings during server program running

For most system variables, their values can be modified dynamically while the server program is running without stopping and restarting the server. However, system variables have scopes:

  1. Set system variables with different scopes

    Multiple client programs can connect to a server program at the same time. For the same system variable, different clients may set different values. A sets default_storage_engine to InnoDB, and B sets default_storage_engine to MyISAM. In this way, each client has a private copy of the system variables, which will cause two problems:

    • There are some system variables that are not specific to a single client, such as the number of clients allowed to connect to the server at the same time, max connections, and the size of the query cache. It is obviously inappropriate to make these public system variables private to a client.
    • When a new client connects to the server, how should the value of its corresponding system variable be set.

    In order to solve these two problems, the uncle who designed MySQL proposed the concept of the scope of system variables. Specifically, the scope of action is divided into the following two types.

    • GLOBAL (global scope): Affects the overall operation of the server. System variables with GLOBAL scope can be called global variables.
    • SESSION (session scope): Operations that affect a client connection. System variables with SESSION scope can be called session variables.

    When the server starts, each global variable is initialized to its default value (these default values can be changed via options specified on the command line or in the configuration file).

    The server also maintains a set of session variables for each connected client. The client’s session variables are initialized using the current value of the corresponding global variable when connecting (there are also some session variables that are not initialized based on the corresponding global variable value, but this will not be expanded here) Nagging).

  2. View system variables with different scopes

  3. Precautions

2.3.3 The difference between startup options and system variables

Startup options are some parameters passed by the user when the program starts, while system variables are variables that affect the running behavior of the server program. The relationship between them is as follows:

  • Most system variables can be passed in as startup options
  • Some system variables are automatically generated during program running and cannot be set as startup options, such as character_set_client
  • Some startup options are not system variables, such as defaults-file

3. State variables

4. Summary

Startup options can adjust some behaviors of the server after startup. They can be specified on the command line, or they can be written to a configuration file.

When specifying startup options on the command line, you can write each startup option on one line, add – in front of each startup option name, and use whitespace characters to separate each startup option. Some startup options do not require specifying option values, and some options require specifying option values. When specifying a startup option with a value on the command line, please note that there cannot be any blank characters between the option name, =, and option value. Some commonly used startup options have short-form option names that are prefixed with just a dash – before the option name.

The server program will search for configuration files in some given paths when it starts. The search paths of different operating systems are different.

The startup options in the configuration file are divided into several groups, and each group has a group name, expanded with square brackets [ ]. Startup options specified in the configuration file do not allow the – prefix, only one option per line is specified, and there can be whitespace characters around the equal sign =. We can use # to add comments.

System variables are variables maintained in the server program that affect the behavior of the server. The method of modifying system variables is as follows.

  • Modify when the server starts by adding the corresponding startup options
  • Use the SET statement to modify it at runtime. The following two methods are available:
    • SET [GLOBAL|SESSION] system variable name=value;
    • SET [@@(GLOBAL|SESSION] system variable name = value;

The way to view system variables is as follows:

  • SHOW [GLOBAL|SESSION] VARIABLES [LIKE matching pattern];

Status variables are used to display the running status of the server program. We can use the following command to view it, and it can only be viewed:

  • SHOW [GLOBAL|SESSION] STATUS [LIKE matching pattern];