socket文件登录mysql

公司内部会上，他们提了一句这个方式，之前没遇到过，然后就自己尝试尝试。

环境

docker pull mysql
docker run -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 --name=MYSQL mysql
docker run -d -p 3333:3306 -e MYSQL_ROOT_PASSWORD=123456 --name=MYSQL_TOOLS mysql

现场

➜  ~ docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
1827df0687ef        mysql               "docker-entrypoint.s…"   6 minutes ago       Up 6 minutes        33060/tcp, 0.0.0.0:3333->3306/tcp   MYSQL_TOOLS
9e6dcb05c8fb        mysql:latest        "docker-entrypoint.s…"   3 days ago          Up 3 days           0.0.0.0:3306->3306/tcp, 33060/tcp   MYSQL

素材

登录到MYSQL 上，随便搞点数据。

➜  ~ docker exec -it 9e6d /bin/bash
root@9e6dcb05c8fb:/# mysql --version
mysql  Ver 8.0.16 for Linux on x86_64 (MySQL Community Server - GPL)
root@9e6dcb05c8fb:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, 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> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------+
| Tables_in_test    |
+-------------------+
| account           |
| lottery_statistic |
| t1                |
+-------------------+
3 rows in set (0.00 sec)

mysql>

测试

目标是测试socket文件的方式进行登录，所以先要知道下面内容：

• socket文件的位置
• 本地测试，以及远程测试

这就涉及到查看mysql的配置文件查看，以及容器运行的IP等信息。从“现场”部分可以查看到对应容器的id信息。

查看配置文件

root@9e6dcb05c8fb:/# tail /etc/mysql/my.cnf
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Custom config should go here
!includedir /etc/mysql/conf.d/

查看容器IP等信息

➜  ~ docker inspect 9e| grep IPA
            "SecondaryIPAddresses": null,
            "IPAddress": "172.17.0.2",
                    "IPAMConfig": null,
                    "IPAddress": "172.17.0.2",
➜  ~

本地登陆测试
按场景来看，本地登陆测试没有意义，但是还是来试一下。

➜  ~ docker exec -it 9e /bin/bash
root@9e6dcb05c8fb:/# mysql -S /var/run/mysqld/mysqld.sock -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------+
| Tables_in_test    |
+-------------------+
| account           |
| lottery_statistic |
| t1                |
+-------------------+
3 rows in set (0.00 sec)

mysql>

远程登陆测试

➜  ~ docker exec -it 182 /bin/bash
root@1827df0687ef:/# mysql -h 172.17.0.2 -S /var/run/mysqld/mysqld.sock -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 41
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------+
| Tables_in_test    |
+-------------------+
| account           |
| lottery_statistic |
| t1                |
+-------------------+
3 rows in set (0.00 sec)

mysql>

整理

mysql竟然还可以以socket文件的形式进行登录，我竟然才知道。仔细想想，php-fpm不也类似的吗？socket文件以及ip+端口格式两种被NGINX使用。

不过还不晚，亲身测试过后，socket文件登录可以在一定程度上脱敏，是一个不错的选择。