MySQL configuration file description

MySQL configuration file

The latest MySQL product actually exceeded the version carried by Red Hat Enterprise Linux. However, stability and open source code are very important factors, so it makes sense to use an earlier MySQL version on the operating system.

There are many available configuration files that are included in the mysql-server PRM program. A default configuration file is included in the mysql PRM package.

Configuring the MySQL server is a rich and complex task. In this article, I can only give a superficial description of various options. You can use a total of five MySQL configuration files. The last four are in the/usr/share/doc/mysql-server-*/directory.

·/Etc/my. cnf is the default MySQL configuration file. Modify the configuration of this file. It is designed for the purpose of learning.

· My-small. cnf is designed for small databases. This model should not be used in databases that contain common projects.

· My-medium. cnf is designed for medium-sized databases. If you are using RHEL in an enterprise, there may be much more physical memory than the minimum RAM requirement of the Operating System (256 MB. It can be seen that if there is so much RAM memory available, you can naturally run other services on the same machine.

· My-large. cnf is designed for a computer dedicated to a SQL database. Since it can use up to MB of memory for the database, at least 1 GB of RAM is required for this type of system so that it can simultaneously process the operating system and database applications.

· My-huge. cnf is designed for databases in enterprises. Such databases require dedicated servers and 1 GB or more RAM.

These options are highly dependent on the amount of memory, the computing speed of the computer, the detailed size of the database, the number of users accessing the database, and the number of users loading and accessing data in the database. As the number of databases and users increases, the database performance may change.

I will illustrate these configuration files one by one. If you decide to use one of my-*. cnf files, copy the file to the/etc/my. cnf file first.

For these reasons, you should carefully observe the performance of the database system. If problems are found, you may need to add more RAM or migrate the database to a system containing additional resources (such as multiple CPUs.

Tip: the database becomes very large. Configuring a SQL database directory in a dedicated partition may make more sense. Although a growing database may occupy the entire partition, it does not swallow at least the disk space necessary for RHEL to run.

/Etc/my. cnf File

The default value is the/etc/my. cnf file. It contains six commands, and these six commands are organized in three configuration segments. These configuration segments are similar to the configuration segments in the Samba configuration file and contain the function group name and related commands. This article will explain the default version of this file line by line. If you make any changes, make sure that the commands in the MySQL STARTUP script (that is,/etc/rc. d/init. d/mysqld) are consistent.

[Mysqld]

Within this configuration segment, you will see commands related to the MySQL daemon.

Datadir =/var/lib/mysql

The MySQL server stores the database in the directory defined by the datadir variable.

Socket =/var/lib/mysql. sock

MySQL socket connects the database program to the MySQL client locally or through the network.

Tip: MySQL is configured to use the InnoDB Storage engine. If you do not have an InnoDB database on your system, you need to add the skip-innodb statement to the [mysqld] configuration segment.

[Mysql. server]

Within this configuration segment, you will see commands related to the MySQL server daemon. Earlier versions of this configuration segment are named [mysql_server]. If you use MySQL4.X or MySQL4.X or later, you must change the title of this configuration segment to [mysql_server]. When starting the MySQL service, it uses the options in this configuration segment.

User = mysql

The standard user name associated with the MySQL service is mysql. It should be part of the/etc/passwd file. If it is not found in this file, you may not have installed the Red Hat Enterprise Linux mysql-server RPM package.

Basedir =/var/lib

This indicates the top-level directory of the MySQL database. It acts as a root directory on the MySQL system; other directories in this database are relative to this directory.

[Safe_mysqld]

This configuration section contains commands referenced by the MySQL STARTUP script. If MySQL4.X or later is used, you must change this configuration segment to [mysqld_safe].

Err-log =/var/log/mysqld. log

This is the file that the MySQL associated error is sent. If MySQL4.X or later is used, replace this command with the log-error command.

Pid-file =/var/run/mysqld. pid

Finally, the pid-file command defines the process identifier (PID) of the MySQL server during its operation ). If the MySQL server is not running, the file does not exist.

Tip: You can configure the MySQL configuration file related to the user. To do this, you only need to add the selected configuration command to the. my. cnf implicit file in the specified user's home directory.

My-samll-cnf

This article describes all the commands in the my-small-cnf configuration text. When reviewing other MySQL sample configuration files, we will refer to the meaning of each command and command described in this article. First, analyze the valid commands and commands in the file from the following configuration section:

[Client]

This configuration passes commands to clients related to the MySQL server.

Port = 3306

The standard TCP/IP Port Related to MySQL is 3306. If you need to modify the port number (which can enhance security), make sure that this number is modified in all the corresponding configuration files used for MySQL clients and servers.

Socket =/var/lib/mysql. sock

As defined in the default/etc/my. cnf file, this is a standard socket file that controls communication between MySQL customers and servers.

[Mysqld]

When the MySQL server is started, it is controlled by the commands defined in the [mysqld] configuration section.

Port = 3306

Socket =/var/lib/mysql. sock

Of course, customers and servers related to the same MySQL database must use the same TCP/IP Port and socket.

Skip-locking

Multiple customers may access the same database, so this prevents external customers from locking the MySQL server. The skip-locking command is the skip-external-locking command in MySQL 4.x or later versions.

In general, if you are using MySQL4.X or 4. X as a version, this set-variable command does not need to include these commands in this list.

Set-variable = key_buffer = 16 k

This buffer zone is really small; if a database contains more than several hundred lines of data in a text file, it will overload the buffer capacity. This database may not overload the capacity of a text file address book. If this is not just a database for personal use, this limit will soon be reached. In this case, you may need to consider the quotas related to one of the other configuration files.

Set-variable = max_allowed_packet = 1 M

Of course, the information related to a database is increased to more than the actual data. By default, if the information exceeds 1 MB on a server, MySQL generates an error message.

Set-variable = thread_stack = 64 k

This command limits the stack size for each database thread. The default setting is sufficient for most applications.

Set-variable = table_cache = 4

Users can limit the number of opened tables in a database. The smaller the limit (the default value is 64) is suitable for smaller-scale databases.

Set-variable = sort_buffer = 64 k

When processing a database, you may need the buffer space attached to the memory.

Set-variable = net_buffer_length = 2 k

As defined by the net_buffer_length command, the MySQL server reserves space for incoming requests.

Server-id = 1

In general, if there is a MySQL master server, set its server-id to 1; set the server-id of the MySQL slave server to 2;

[Mysqldump]

You can transmit data between different types of SQL databases, which is controlled by commands in the [mysqldump] configuration section.

Quick

The quick option supports dump of large databases.

Set-variable = max_allowed_packet = 16 M

Of course, the max_allowed_packet used to transmit database tables to other databases is greater than the information package used for simple communication between the customer and the server.

[Mysql]

No-auto-rehash

This configuration section sets the conditions for starting the MySQL service. In this case, no-auto-rehash ensures that the service is started faster.

[Isamchk]

[Myisamchk]

Relational databases such as SQL are processed using the so-called Indexed Sequential Access Method (ISAM. The commands in these two configuration sections are the same. These commands are related to the command used to check and fix database tables with the same name.

Set-variable = key_buffer = 8 M

Set-variable = sort_buffer = 8 M

When talking about MySQL server, you have seen these variables. They are all large here to support quick database check and repair.

[Mysqlhotcopy]

Interactive-timeout

As specified in the [mysqlhotcopy] configuration section, the connection is suspended during a database replication operation. By default, the interactive-timeout variable sets the maximum time for data transmission to 28800 seconds (8 hours ).

My-medium.cnf files

MySQL configuration files related to medium databases contain the same valid configuration segments as those in my-small-cnf configuration files. In the [mysqld] configuration section, the following commands support large-scale Server databases:

Set-variable = key_buffer = 16 M

Set-variable = table_cache = 64

Set-variable = sort_buffer = 512 K

Set-variable = net_buffer_length = 8 K

Log-bin

In general, the commands in this configuration segment support large cache and buffer length on the server. We should see two new commands.

Set-variable = myisam_sort_buffer_size = 8 M

Log-bin

The myisam_sort_buffer_size command allows MySQL to index the database. The second command supports the binary logging method.

[Isamchk]

[Myisamchk]

Of course, the buffer in these two configuration segments is larger than the buffer used for database transmission. This file contains the following commands, which send messages to the server and receive messages from the server.

Set-variable = read_buffer = 2 M

Set-variable = write_buffer = 2 M

My-large.cnf files

MySQL configuration files related to larger databases contain the same valid configuration segments as those in my-samll-cnf configuration files. In this article, we will compare the commands in my-large-cnf and my-medium-cnf sample files. In the [mysqld] configuration section, the following commands support large Server databases:

Set-variable = key_buffer = 256 M

Set-variable = table_cache = 256

Set-variable = sort_buffer = 1 M

Set-variable = myisam_sort_buffer_size = 64 M

Set-variable = net_buffer_length = 8 K

This configuration segment contains three additional commands. The record_buffer command saves scan results for different tables in a database. The thread_cache command is useful for multiple requests. Idle threads are cached at high speed, allowing new search operations to use their own threads. As long as this prevents search operations from starting new server processes, this can reduce the load on the system.

Set-variable = record_buffer = 1 M

Set-variable = thread_cache = 8

Set-variable = thread_concurrency = 8

The thread_concurrency variable specifies the number of concurrent threads. The my-large.cnf sample file recommends that you limit this number to twice the number of CPUs on your computer; this particular setting is equivalent to 4 CPUs.

My-huge.cnf files

The my-huge.cnf file contains the same commands as those in the my-large.cnf configuration file. Of course, the value assigned to most commands is relatively large and suitable for large databases.

As described on wWw.mysql.com, organizations with a large number of databases, such as google, Sabre, and NASA, all adopt MySQL. Although we guess the commands these companies use are different from what users see in the my-huge.cnf file, this at least gives users a concept of MySQL Enterprise databases.