Parameters file for MySQL database

Parameter file: Tells the MySQL instance where to find the database file when it starts, and specifies some initialization parameters that define settings such as the size of a memory structure, and also describe the types of the various parameters.

Parameter file

When the MySQL instance starts, MySQL reads a configuration parameter file that is used to locate the various files in the database and specify some initialization parameters, which usually define how large a memory structure is. By default, MySQL instances are taken in a certain order, and you can only find them by ordering MySQL--help|grep my.cnf .

The MySQL parameter file is very similar to the Oracle parameter file, but it is not possible to mount (Mount) operations if the parameter file cannot be found when the Oracle instance is started. MySQL is slightly different, mysql instance can not need parameter file , then all parameter value depends on the default value specified when compiling MySQL and the default value of specified parameter in source code. However, if MySQL cannot find the MySQL schema in the default database directory, the startup will also fail, and you may find the following in the error log file:

090922 16:25:52 mysqld started090922 16:25:53 innodb:started;log Sequence number 8 28010632111! 090922 16:25:53[error]fatal error:can ' t open and Lock privilege tables:table'mysql.host ' doesn ' t exist090922 16:25:53 Mysqld Ended

The MySQL schema in MySQL records access to the instance, and when the schema is not found, the MySQL instance does not start successfully.

Unlike the Oracle parameter file, Oracle's parameter files are divided into binary parameter files (spfile) and text-type parameter files (init.ora), while MySQL's parameter files are only text, and conveniently, you can edit the parameters with some commonly used editing software (such as VI and Emacs).

Basic concepts and operations of parameters

You can think of database parameters as a key/value pair. You can view all the parameters through show variables, or filter the parameter names by like.

Starting with MySQL version 5.1, you can find it through the Global_variables view under the INFORMATION_SCHEMA architecture, as shown here:

SELECT * from Global_variables where variable_name like ' innodb_buffer% ' \g;

Show variables like ' innodb_buffer% ' \g

Oracle's parameters have so-called hidden parameters (undocumented parameter) for use by Oracle "Insiders", and SQL Server has similar parameters. Some DBAs have asked me if there are any such parameters in MySQL. My answer is: no, no need (actually there is). Even if there are some so-called hidden parameters in both Oracle and SQL Server, in the vast majority of cases, these database vendors do not recommend that you make significant adjustments to them in a production environment.

Parameter type

The parameters in the MySQL parameter file can be divided into two categories: Dynamic parameters and Static parameters. Dynamic parameters mean that you can make changes while the MySQL instance is running, and the static parameter description cannot be changed throughout the life of the instance, as if it were read only.

Dynamic parameter values can be modified by the SET command, with the following syntax:

SET

| [Global|session] System_var_name=expr

| [@ @global. | @ @session. | @@] System_var_name=expr

Here you can see the Global and Session keywords, which indicate whether the modification of this parameter is based on the current session or the lifetime of the entire instance.

Some dynamic parameters can only be modified in the session, such as Autocommit;

When some parameters are modified, they will take effect throughout the life cycle of the instance, such as binlog_cache_size;

Some parameters, such as read_buffer_size, can take effect both in the session and throughout the lifetime of the instance.

Examples are as follows:

Set read_buffer_size=524288;

SELECT @ @session. read_buffer_size\g;

SELECT @ @global. read_buffer_size\g;

Adjusting the session value of Read_buffer_size from 2MB to 512KB, you can see that the global read_buffer_size value is still 2MB, that is, if there is another session logged into the MySQL instance, it's Read_buffer_ The value of size is 2MB, not 512KB. This uses the set Global|session to change the value of the dynamic variable.

Use the SET @ @global |@ @session to change

SET @ @global. read_buffer_size=1048576;

SELECT @ @session. read_buffer_size\g;

SELECT @ @global. read_buffer_size\g;

Change the Read_buffer_size global value to 1MB, while the current session read_buffer_size is 512KB. It is important to note that the global value of the variable is modified to be valid for the lifetime of the instance, but the MySQL instance itself does not modify the value in the parameter file. This means that the MySQL instance will still read the parameter file the next time it is started. You must modify the parameter file if you want the database instance to be retained as the currently modified value at the next startup. To know the modifiable range of all MySQL dynamic variables, refer to the relevant content of the MySQL official manual (Dynamic System Variables).

For a static variable, if you modify it, you get an error similar to the following

Set global datadir= '/db/mysql ';

ERROR 1238 (HY000): Variable ' DataDir ' is a read only Variable

Parameters file for MySQL database