What are pid and socket in MySQL?

Foreword:

I don't know if you have noticed, the pid and socket file path need to be configured when MySQL starts. Occasionally there will be startup failures due to the pid file not being found. So what exactly are the pid and socket files used for? Let's take a look at this article together.

1.Introduction to pid-file

The pid file in MySQL records the pid of the current mysqld process, which is also the 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 is named host_name.pid by default, and the storage path is placed in the MySQL data directory by default.

It is recommended to specify the pid file name and path. The permissions of the pid directory should be released to the mysql system user. The specific configuration can refer to the following:

# my.cnf 配置文件
[mysqld]
pid-file  = /data/mysql/tmp/mysqld.pid

# 查看mysqld进程
[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

# 查看pid文件内容 
[root@localhost ~]# cat /data/mysql/tmp/mysqld.pid
9353

You can see that there is only one line in the pid file, which records the ID of the mysqld process. After the mysqld process is started, it will create a new pid file through the create_pid_file function, get the current process number through getpid() and write the process ID into the pid file. After the process runs, a file lock is added to the pid file. Only the process that has the permission to write the pid file can start normally and write its own PID into the file, and other redundant processes of the same program will automatically exit. Therefore, the function of the pid file is to prevent multiple process copies from being started.

Sometimes you may encounter startup failure due to pid file problems. You may have encountered these 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 above several types of pid-related error solutions are actually similar. First, look at the error log to find the specific error, and then check the configuration file to ensure that the pid file directory path is correct and there is permission and space. After that, you can check whether the mysqld process exists. If it exists, it can be manually killed. If there are residual pid files, it can also be deleted first. After all the troubleshooting is complete, restart it again, and it will usually succeed.

2. Socket file introduction

Socket is the Unix socket file. On Unix-like platforms, there are two ways for the client to connect to the MySQL server, namely the TCP/IP method and the socket file method. Unix socket file connection speed is faster than TCP/IP, but it can only be used by connecting to a server on the same computer.

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

# my.cnf 配置文件
[mysqld]
socket = /data/mysql/tmp/mysql.sock
[client]
socket = /data/mysql/tmp/mysql.sock

# 查看对应目录下的socket文件
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

# 通过 -S 命令指定socket登录
[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

Checking the above connection status shows that MySQL can be connected locally via socket. When logging in locally, if the socket file path is not specified in the [client] section of the my.cnf configuration file, mysql will look for /tmp/mysql.sock by default, so if the mysqld service starts, the generated socket file is not the default path If you log in, an error may be reported (ERROR 2002 (HY000): Can't connect to local MySQL server through socket'/tmp/mysql.sock'). In fact, both the [mysqld] part and the [client] part are configured with specific paths to avoid this problem. You can also establish a soft connection under the tmp path, such as: ln -s /data/mysql/tmp/mysql.sock /tmp/mysql.sock . Similarly, the permissions of the socket file directory must be released to the mysql system user.

summary:

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. It is good to configure them at the beginning. If you encounter a restart error, check it slowly according to the error log. If you operate carefully, you will always find the problem.