How to modify the configuration of MySQL

MySQL is a widely used relational database management system. In the process of using MySQL, sometimes you need to modify the MySQL configuration according to your own needs to achieve better usage results. This article will introduce how to modify the configuration of MySQL.

1. Open the MySQL configuration file my.cnf
The MySQL configuration file is my.cnf, and its default location is in the /etc/mysql/ directory. Use the following command to open the my.cnf file:

sudo nano /etc/mysql/my.cnf

2. Modify the MySQL configuration
In the opened my.cnf, there are many MySQL configuration items that can be modified according to your own needs. The following introduces some of the more commonly used configuration items.

2.1 Modify the character set

The default character set of MySQL is Latin1, but for some special data, you need to use character sets such as utf8. To modify the character set in the my.cnf file, you can use the following code:

[client]
default-character-set = utf8

[mysql]
default-character-set = utf8

[mysqld]
init_connect=’SET NAMES utf8′
character-set-server = utf8 
collation-server = utf8_general_ci

2.2 Modify the port number

The default port number of MySQL is 3306. If you want to use other port numbers, you can use my Modified in .cnf. Use the following code to modify the port number:

[mysqld]
port=1234

2.3 Modify the cache size

MySQL uses cache to improve data processing speed. You can modify the cache size in my.cnf. Use the following code to modify the cache size:

[mysqld]
query_cache_size=256M

2.4 Modify log files

MySQL will record various operation logs, such as error logs, access logs, etc. The path and size of the log file can be modified in my.cnf. Use the following code to modify the log file:

[mysqld]
log-error=/var/log/mysql/error.log
log-queries-not-using-indexes
slow_query_log_file=/var/log/mysql/mysql-slow.log
log-bin=/var/log/mysql/mysql-bin.log
max_binlog_size=100M

2.5 Modify the maximum number of connections and the maximum number of concurrencies

MySQL’s default maximum number of connections is 151 and the maximum number of concurrencies is 100, but sometimes it needs to be based on the actual situation to modify. Use the following code to modify the maximum number of connections and the maximum number of concurrencies:

[mysqld]
max_connections = 500
max_user_connections = 60

3. Save and exit my.cnf
After completing the modification, use Ctrl X to exit, and then select Y to save the modification.

4. Restart the MySQL service
After modifying the MySQL configuration, you need to restart the MySQL service for the configuration to take effect. Use the following command to restart the MySQL service:

sudo service mysql restart

5. Verify whether the MySQL configuration is effective
You can use the following command to check whether the MySQL configuration is effective:

mysql> SHOW VARIABLES;

You can see the previously modified configuration Already effective.

Summary
As a powerful database management system, MySQL has many configuration items that can be modified according to the actual situation. By modifying the configuration of MySQL, you can make MySQL perform better and improve data processing efficiency. The above methods of modifying MySQL configuration are relatively basic, and there are some more advanced configurations that can be learned gradually in actual operations.

The above is the detailed content of How to modify the configuration of MySQL. For more information, please follow other related articles on the PHP Chinese website!