Shulou(Shulou.com)06/01 Report--

This article mainly explains MySQL startup options and the use of system variables, the content is clear, interested partners can learn about it, I believe you will be helpful after reading.

MySQL configuration information can be implemented in two ways, one is the command line form, with relevant configuration parameters after starting MySQL service, this way will fail after MySQL restart. Another way is to write configuration files, such as my.cnf, start or restart MySQL service will take effect, this way is permanent effect. Start Options Command Line With configuration parameters when MySQL service command is started

Start method can refer to this article: MySQL start and connection method

Command Format: Start Command--Start Option 1[= Value 1] --Start Option 2[= Value 2]... --Start option n[= value n] 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 startup item and the value cannot have a space The configuration parameters have long and short forms, some of which have the same effect, but are written differently--host => -h //host--port => -P //port--user => -u //user--password => -p //password--version => -V //version... Example: mysqld --port=3306mysqld -P3306mysqld -P 3306 Note: Password cannot have spaces mysqld -proot The location of the configuration file my.cnf may be one of the following. For example, if the configuration file is not specified when the MySQL service is started, it will be searched and initialized from the following places. * /etc/my.cnf * /etc/mysql/my.cnf* defaults-extra-file //specified extra configuration file path * SYSCONFDIR/my.cnf //cmake compilation installation * $MYSQL_HOME/my. cnf //setting environment variables, 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

note

1. Startup options specified in the configuration file do not allow the--prefix, and specify only one option per line, and = can be surrounded by white space characters

2. If we set the same boot option in more than one profile, the one in the last profile prevails

3. If the same startup option appears on both the command line and the configuration file, the startup option on the command line prevails

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

//When the program starts, it will only search for configuration files in the path/tmp/myconfig.txt. If the file does not exist or cannot be accessed, an error occurs

Configuration group configuration files can be configured for the following groups: mysqld, mysqld_safe, mysql.server, mysql, mysqladmin, mysqldump, which 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...) Examples: [mysqld]pid-file = /var/run/mysqld/mysqld.pidsocket = /var/run/mysqld/mysqld.sockdatadir = /var/lib/mysqllog-error = /var/log/mysql/error.log#Optimization Configuration wait_timeout=10back_log=600key_buffer_size = 2048Mread_buffer_size = 100Mmax_allowed_packet = 1000Mthread_stack = 192Kthread_cffer_size = 4myisam-recover-options = BACKUPachemax_connections = 4000max_user_connections = 0max_connect_errors = 65535open_files_limit = 10240......

note

1. The startup options under the [server] group apply to all server programs, such as mysqld, mysqld_safe, mysql.server.

2. The startup options under the [client] group will apply to all client programs, such as mysql, mysqladmin, mysqldump

3. Priority of multiple groups in the same profile will be based on the startup option in the last group to appear

MySQL server programs use a number of variables that affect the behavior of the program. These variables are called MySQL system variables.

For example:

1. The number of clients allowed to connect simultaneously is represented by the system variable max_connections

2. The default storage engine for a 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

......

View format: SHOW VARIABLES [LIKE pattern matched]; e.g. SHOW VARIABLES LIKE 'default_storage_engine';

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

Note:

For startup options, if the startup option name consists of multiple words, each word can be connected with a dash-or underscore_, but the corresponding system variable words must be connected with underscore_(that is, when set by show view or set).

One of the cool things about setting system variables during server runs is that for most system variables, their values can be dynamically modified during server runs without stopping and restarting the server.

However, system variables have global and current session scopes

GLOBAL: Global variable that affects the overall operation of the server. SESSION: Session variable that affects the operation of a client connection. (alias 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 Example: 1. SET SESSION default_storage_engine = InnoDB;2. SET @@SESSION.default_storage_engine = InnoDB;3. SET default_storage_engine = InnoDB;//default session viewing format: SHOW [GLOBAL| SESSION] VARIABLES [LIKE matched pattern];1. SHOW SESSION VARIABLES LIKE 'default_storage_engine';2. SHOW GLOBAL VARIABLES LIKE 'default_storage_engine'; NOTE:

If a client changes the value of a system variable in GLOBAL scope, it does not affect the value of SESSION scope of the system variable on the currently connected client, but only affects the value of SESSION scope on the subsequently connected client. supplementary note

* Some system variables have GLOBAL scope only, such as max_connections, which indicates the maximum number of simultaneous client connections supported by the server program.

* Some system variables have only SESSION scope, such as insert_id, which indicates the initial value of a column containing AUTO_INCREMENT when inserting into a table

* There are some system variables whose values have both GLOBAL and SESSION scopes, such as default_storage_engine, which we used earlier, and most system variables are like this

For example, version indicates the current version of MySQL. Our client cannot set its value. It can only be viewed in the SHOW VARIABLES statement.

Startup options are parameters passed by our programmers at program startup, while system variables are variables that affect the behavior of server programs

* Most system variables can be passed in 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 also not system variables, such as defaults-file

State variables In order to better understand the running state of the server program, MySQL server programs maintain a number of variables about the running state of the program, which are called state 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 state variables are used to display the running state of the server program, their values can only be set by the server program itself, and we programmers cannot set them.

View format: SHOW [GLOBAL]| SESSION] STATUS [LIKE pattern matched]; e.g. SHOW STATUS LIKE 'thread %';

After reading the above content, is there a further understanding of MySQL startup options and the use of system variables? If you want to learn more, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.