Example analysis of MySQL startup and connection methods

This article uses examples to describe how to start and connect to MySQL. Share with you for your reference, the details are as follows:

MySQL operation consists of two parts, one is the server-side program mysqld, and the other is the client-side program mysql. Only when mysqld is started can the mysql client connect to it. There are 4 ways to start mysqld, and there are also 4 ways to connect to mysql.

How to start mysqld

Method 1: mysqld

mysqld is an executable command and also a server-side program. Starting this program is equivalent to starting the MySQL server-side process. But this command is not commonly used, the mysqld_safe command is often used.

Command format:
mysqld [OPTIONS]

like:
mysqld --defaults-file=/etc/my.cnf

View Help:
mysqld --verbose --help

Method 2: mysqld_safe

mysqld_safe is a startup script that indirectly references mysqld. When starting the mysqld_safe script, when starting the MySQL server process, a daemon process will also be started to monitor mysqld. If the mysqld service hangs up, a mysqld service will be restarted immediately.

In addition, the mysqld_safe startup method will also output the error log and other diagnostic information of the running process to a file, which makes it easier for us to troubleshoot and solve problems. This startup method is the most common one.

Command format:
mysqld_safe [OPTIONS] &

like:
mysqld_safe --defaults-file=/etc/my.cnf &

View Help:
mysqld_safe --help

Method 3: mysqld_multi

If you need to run multiple MySQL instances on a single host, you can use mysqld_multi to manage different mysqld services. This startup method is very helpful for MySQL clusters.

Command format:
mysqld_multi [OPTIONS] {start|reload|stop|report} [GNR,GNR,GNR...]

View Help:
mysqld_multi --help

Method 4: mysql.server

mysql.server is also a startup script. It will indirectly call mysqld_safe. When calling mysql.server, you can start the server program by specifying the start parameter at the end. This startup mode is generally generated by MySQL installed through the RPM package.

The mysql.server file is actually a link file. Its actual file is ../support-files/mysql.server

Command format:
mysql.server start|restart|stop|status

mysql connection method

Method 1: TCP/IP sockets

It can be used in both Unix-like and Windows operating systems. TCP/IP socket is a network protocol. When the MySQL server starts, it listens to a port. When the client starts the connection, it can connect by specifying the host IP+port of the MySQL server.

In this communication mode, the MySQL server and client can be deployed on different hosts, which is also the most commonly used connection method.

mysql -h host IP -P port -u user -p
like:
mysql -h127.0.0.1 -P3306 -uroot -p 

Method 2: Unix sockets

Only available on Unix-like operating systems. Unix sockets are not a network protocol and can only be used when the MySQL server and client are on the same host.

If we specify the host name as localhost when starting the client program, or specify the startup parameter of --protocol=socket, then the server program and the client program can communicate through the Unix domain socket file.

The default Unix domain socket file path that the MySQL server program listens to is /tmp/mysql.sock, and the client program also connects to this Unix domain socket file by default.

1. mysql -uroot -p //Default is to communicate through socket, default is localhost
2. mysql -hlocalhost -uroot -p
3. mysql -S socket file path -uroot -p
like:
mysql -S /var/run/mysqld/mysqld.sock -uroot -p 

View the socket file location:
show variables like 'socket'; 

Method 3: Named Pipe

It can only be used under Windows-like operating systems and can only be used when the MySQL server and client are on the same host.

You need to add the --enable-named-pipe parameter to the command that starts the server program, and then add the --pipe or --protocol=pipe parameter to the command that starts the client program.

–enable-named-pipe=on/off;

Method 4: Shared Memory

It can only be used under Windows-like operating systems and can only be used when the MySQL server and client are on the same host.

You need to add the --shared-memory parameter to the command that starts the server program. After the server is successfully started, shared memory becomes the default connection method for local client programs. However, we can also add the --protocol=memory parameter to the command that starts the client program to explicitly specify the use of shared memory for communication.

–shared-memory=on/off;

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.