MySQL Tutorial

MySQL Installation and Configuration

MySQL Database Operations

Database Design

MySQL Data Types

MySQL Storage Engines

MySQL Basic Operations of Tables

MySQL Constraints

MySQL Operators

MySQL Function

MySQL Function Overview

MySQL Manipulate Table Data

MySQL View

MySQL Indexes

MySQL Stored Procedure

MySQL Trigger

MySQL Transactions

MySQL Character Set

MySQL User Management

MySQL Database Backup and Recovery

MySQL Log

MySQL Performance Optimization

MySQL My.cnf Configuration File

In the section " Detailed Explanation of MySQL my.ini Configuration File ", we explained the MySQL configuration file my.ini in Windows operating system. The configuration file of MySQL in Linux operating system is my.cnf, which is usually placed in the /etc/my.cnf or /etc/mysql/my.cnf directory. In general, my.cnf is similar to the my.ini configuration file.
For the convenience of readers, we omit the comments in the my.cnf file. The specific meanings of the parameters in my.cnf are introduced separately below. The contents of the file are as follows:

[client]
port=3306
socket=/var/run/mysql/mysql.sock
[mysqldump]
quick
max_allowed_packet = 16M

The above parameters will be read by the MySQL client application. The parameter descriptions are as follows:

port: the port number used by the MySQL client to connect to the server, the default is 3306
socket: the directory where the socket file is located
quick: Supports large database dumps, required when exporting very large tables.
max_allowed_packet: The maximum size of the request packet that the service can handle and the maximum request size the service can handle (necessary when working with large BLOB fields), independent size for each connection, and the size is dynamically increased.

Note: Only client applications shipped with MySQL are guaranteed to read this content. If you want your MySQL application to get these values, you need to specify these options when the MySQL client library is initialized.

[mysqld]

user = mysql
basedir = /usr/local/mysql
datadir = /mydata/mysql/data
port=3306
server-id = 1
socket=/var/run/mysql/mysql.sock

The above parameters are described as follows:

user: The mysqld program will be executed under the given UNIX/Linux account after startup. mysqld must be started from the root account to switch to another account after startup. The mysqld_safe script will start the mysqld program with the user=mysql option by default.
basedir: specifies the absolute path of the MySQL installation.
datadir: specifies the absolute path where MySQL data is stored.
port: service port number, the default is 3306
server-id: The unique number of the MySQL service. The id of each MySQL service must be unique.
socket: the directory where the socket file is located

character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'
lower_case_table_names = 1

key_buffer_size=16M
max_allowed_packet=8M
no-auto-rehash
sql_mode=TRADITIONAL

character-set-server: The default character set of the database, the mainstream character set supports some special emoticons (special emoticons occupy 4 bytes)
collation-server: The database character set corresponds to some sorting and other rules, pay attention to correspond to character-set-server
init_connect: Set the character set when the client connects to mysql to prevent garbled characters
lower_case_table_names: Whether the sql statement is case sensitive, 1 means insensitive
key_buffer_size: used to specify the size of the index buffer
max_allowed_packet: Set the maximum value of one message transmission
no-auto-rehash: Only allow UPDATES and DELETES using key values
sql_mode: A parameter indicating the SQL mode, through which the strictness of the SQL statement can be set

The above content may be different from your configuration file. When there is any need, just copy and use the corresponding parameters.

Edit my.cnf for MySQL Configuration:
- Description: Edit the my.cnf file using a text editor to adjust MySQL server settings.
- Code:
```
sudo nano /etc/mysql/my.cnf
```
  Use your preferred text editor instead of nano if needed.
Changing Buffer Settings in my.cnf:
- Description: Adjust buffer settings like innodb_buffer_pool_size to optimize MySQL's use of memory.
- Code: Edit my.cnf and set values, e.g.:
```
innodb_buffer_pool_size = 256M
```
Reload my.cnf Without Restarting MySQL:
- Description: Reload the MySQL configuration without restarting the server to apply changes.
- Code:
```
sudo systemctl reload mysql
```
  Use the appropriate command based on your system (systemctl, service, or systemctl restart mysqld).