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 OptionsCommand LineWhen starting the MySQL service command, bring the configuration parameters 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 parametersConfiguration 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 FilesThe 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
Configuration GroupThe 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 ......
System variablesThe MySQL server program uses many variables that affect program behavior during its operation. They are called MySQL system variables.
CheckFormat: SHOW VARIABLES [LIKE matched pattern]; For example: SHOW VARIABLES LIKE 'default_storage_engine'; set upVia startup settingsCommand line settingsmysqld --default-storage-engine=MyISAM --max-connections=10 Profile Settings[mysqld] default-storage-engine = MyISAM max-connections = 10 ......
Set during server program runningThe 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. ScopeGLOBAL: 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; globalFor example: 1. SET GLOBAL default_storage_engine = InnoDB; 2. SET @@GLOBAL.default_storage_engine = InnoDB; sessionFor example: 1. SET SESSION default_storage_engine = InnoDB; 2. SET @@SESSION.default_storage_engine = InnoDB; 3. SET default_storage_engine = InnoDB; //Default session CheckFormat: SHOW [GLOBAL|SESSION] VARIABLES [LIKE matching pattern]; 1. SHOW SESSION VARIABLES LIKE 'default_storage_engine'; 2. SHOW GLOBAL VARIABLES LIKE 'default_storage_engine'; Notice: Additional NotesNot all system variables have GLOBAL and SESSION scopes.
Some system variables are read-only and cannot be set.
Relationship between startup options and system variablesStartup 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.
State variablesIn 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. CheckFormat: 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:
|
<<: A brief discussion on the use of React.FC and React.Component
>>: Vue realizes the progress bar change effect
KILL [CONNECTION | QUERY] processlist_id In MySQL...
Preface Nginx is a lightweight HTTP server that u...
This article example shares the specific code of ...
HTML comment box with emoticons. The emoticons ar...
Table of contents summary Overall process front e...
We know that the properties of the select tag in e...
Now .net core is cross-platform, and everyone is ...
This article uses examples to illustrate the sear...
Because the project needs to use https service, I...
Panther started as a rookie, and I am still a roo...
Simply use CSS to achieve all the effects of corn...
When we add borders to table and td tags, double ...
Docker is an open source container engine that he...
Preface When docker run creates and runs a contai...
1. Methods for implementing components:組件名稱首字母必須大...