Detailed explanation of MySQL startup options and system variables examples

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.

You may also be interested in:
  • MySQL multi-instance configuration solution
  • MySQL database introduction: detailed explanation of multi-instance configuration method
  • MySQL 8.0 installation and configuration tutorial
  • MySQL5.7 single instance self-starting service configuration process

<<:  A brief discussion on the use of React.FC and React.Component

>>:  Vue realizes the progress bar change effect

Recommend

MySQL kill command usage guide

KILL [CONNECTION | QUERY] processlist_id In MySQL...

The whole process of configuring reverse proxy locally through nginx

Preface Nginx is a lightweight HTTP server that u...

Native js implements shopping cart logic and functions

This article example shares the specific code of ...

Tutorial on how to create a comment box with emoticons using HTML and CSS

HTML comment box with emoticons. The emoticons ar...

SMS verification code login function based on antd pro (process analysis)

Table of contents summary Overall process front e...

select the best presets to create full compatibility with all browsersselect

We know that the properties of the select tag in e...

Implementation of running SQL Server using Docker

Now .net core is cross-platform, and everyone is ...

Alibaba Cloud applies for a free SSL certificate (https) from Cloud Shield

Because the project needs to use https service, I...

CentOS 7.2 builds nginx web server to deploy uniapp project

Panther started as a rookie, and I am still a roo...

HTML+CSS merge table border sample code

When we add borders to table and td tags, double ...

Examples of using Docker and Docker-Compose

Docker is an open source container engine that he...

How to modify the port mapping of a running Docker container

Preface When docker run creates and runs a contai...

React State state and life cycle implementation method

1. Methods for implementing components:組件名稱首字母必須大...