How to query and modify parameter status values

Hello everyone , I meet you again , I'm the king of the whole stack .

Q： What is the “ database ”？ A： 　　 That is to say, database instances ,“ database ” = Database software + database ： Stored on disk

1、 There are tables in the database ： Put in datadir Under the table of contents

mysql> show variables like 'datadir'; +---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| datadir       | /mydata/ |
+---------------+----------+

2、 Software is used to manage databases ： Put in basedir Under the table of contents

mysql> show variables like 'basedir'; +---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| basedir       | /usr/local/mysql |
+---------------+------------------+

3、 Overview of working principle of database

　　 Users connect to the database , Operate on the database , Read the data in the database in the disk into the memory ( Physical reads ), The data in memory is read by the user ( Memory reading ), The speed of memory reading ( Basically negligible ) It is tens of thousands of times faster than physics reading .

　　 The reason why the database is fast is , The database has a large memory as a cache , Less reading of disk files , It will reduce the speed .

　　MySQL After starting , First, a small space will be allocated in the memory as the user workspace , Enough can be used. ; As users use the database , Space expands gradually , Maximum to innodb_buffer_pool_size The value of ( The space size can account for at least half of the physical memory , It is generally set to the physical memory of the server 70%).

So here comes the question , How to MySQL Parameters in the database 、 Query the status value 、 What about the revision ？

1、 Query and modification of parameter status value

　　 In the official document Server Option / Variable Reference part , For reference MySQL Parameter variables and state values

　　1、cmd-line Indicates whether the mysql Safe start (mysqld_safe) Set parameters on the command line of –var_name=……

　　2、option file Indicates whether the mysql Parameter file of (my.cnf) Set parameters in

　　3、system var Indicates whether it is a system variable @@： overall situation 、 conversation

　　4、status var Indicates whether it is a state variable

　　5、var scope Represents the range of variables ： overall situation global、 conversation session、both The representation can be at the global level , It can also be used at the session level

　　6、dynamic Indicates whether it is a dynamic parameter ,yes It's dynamic ,no Is static ,varies It depends on the database version

2、 Use official documents for reference MySQL The variable parameters of 、 State parameters ：

　　1、 name

　　2、 effect

　　3、 Modify the range of values

　　4、 Company

　　5、 Whether to restart or not

　　6、 The size of the default value

　　7、 Scope

Be careful ：

　　 If it is the modification of global system variables , It's better to be in mysql> set @@global.var_name=…; After modification , take global.var_name=… The modification of variables is written into the parameter file at the same time , That is to say MySQL Configuration file for my.cnf Keep it in .

Publisher ： Full stack programmer stack length , Reprint please indicate the source ：https://javaforall.cn/108330.html Link to the original text ：https://javaforall.cn