Detailed explanation of pid and socket in MySQL

• Socket file: The file required when connecting using Unix domain socket method.
• pid file: The process ID file of the MySQL instance.

1. Introduction to pid-file

The pid file in MySQL records the pid of the current mysqld process. pid is also known as Process ID. The pid file path and file name can be configured through the pid-file parameter. If this variable is not specified, the pid file defaults to host_name.pid and is stored in the MySQL data directory by default.

It is recommended to specify the pid file name and path, and the pid directory permissions should be released to the mysql system user. The specific configuration can be referred to as follows:

# my.cnf configuration file [mysqld]
pid-file = /data/mysql/tmp/mysqld.pid

# View the mysqld process [root@localhost ~]# ps -ef | grep mysqld
root 8670 1 0 Jun09 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql/data --pid-file=/data/mysql/tmp/mysqld.pid
mysql 9353 8670 0 Jun09 ? 00:01:23 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/logs/error.log --pid-file=/data/mysql/tmp/mysqld.pid --socket=/data/mysql/tmp/mysql.sock

# View the contents of the pid file
 [root@localhost ~]# cat /data/mysql/tmp/mysqld.pid
9353

You can see that the pid file contains only one line, which records the ID of the mysqld process. After the mysqld process is started, a new pid file will be created through the create_pid_file function, the current process number will be obtained through getpid(), and the process ID will be written to the pid file. After the process is running, a file lock will be added to the pid file. Only the process that has obtained the permission to write the pid file can start normally and write its own PID into the file. Other redundant processes of the same program will automatically exit. So the purpose of the pid file is to prevent multiple copies of a process from being started.

Sometimes you may encounter startup failures due to pid file problems. You may have encountered the following types of errors:

Can't start server: can't create PID file: No such file or directory

ERROR! MySQL server PID file could not be found

ERROR! The server quit without updating PID file

The solutions to the above types of pid-related errors are actually similar. First, look at the error log to find the specific error, then check the configuration file to ensure that the pid file directory path is correct and has permissions and space. Then you can check whether the mysqld process exists. If it exists, you can kill it manually. If there are any residual pid files, you can also delete them first. After everything is checked, restart again, which usually succeeds.

2.Socket file introduction

Socket is a Unix socket file. On Unix-like platforms, there are two ways for a client to connect to a MySQL server: TCP/IP and socket file. Unix socket file connections are faster than TCP/IP, but can only be used to connect to servers on the same computer.

The socket file path and name can be configured by setting the socket variable. The default value is /tmp/mysql.sock (the directory may be different for some distribution formats). The reference configuration is as follows:

# my.cnf configuration file [mysqld]
socket = /data/mysql/tmp/mysql.sock
[client]
socket = /data/mysql/tmp/mysql.sock

# View the socket file in the corresponding directory root@localhost tmp]# ls -lh
total 8.0K
srwxrwxrwx 1 mysql mysql 0 Jun 10 15:19 mysql.sock
-rw------ 1 mysql mysql 6 Jun 10 15:19 mysql.sock.lock

# Specify socket login via -S command [root@localhost ~]# mysql -uroot -pxxxx -S /data/mysql/tmp/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> status
--------------
mysql Ver 8.0.22 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id: 12
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.22 MySQL Community Server - GPL
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /data/mysql/tmp/mysql.sock
Binary data as: Hexadecimal
Uptime: 1 hour 27 min 31 sec

Threads: 3 Questions: 27 Slow queries: 0 Opens: 135 Flush tables: 3 Open tables: 56 Queries per second avg: 0.005

From the above connection status, we can see that MySQL can be connected locally via socket. When logging in locally, if the [client] section in the my.cnf configuration file does not specify a socket file path, MySQL will look for /tmp/mysql.sock by default. Therefore, if the socket file generated when the mysqld service is started is not in the default path, the login may fail (ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock'). In fact, you can avoid this problem by configuring specific paths in both the [mysqld] and [client] parts. You can also create a soft link in the tmp path, such as: ln -s /data/mysql/tmp/mysql.sock /tmp/mysql.sock. Likewise, the socket file directory permissions must be granted to the mysql system user.

Summarize:

This article introduces the specific configuration and function of pid and socket files in MySQL. In fact, these two parameters are relatively easy to maintain. Just configure them at the beginning and don't change them. If you encounter a restart error, check slowly according to the error log. If you operate carefully, you will always find the problem.

The above is a detailed explanation of PID and socket in MySQL. For more information about MySQL PID and socket, please pay attention to other related articles on 123WORDPRESS.COM!