socket文件登录mysql

Love The Way You Lie 2022-01-09 21:13 229阅读 0赞

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

## 环境 ##

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文件登录可以在一定程度上脱敏，是一个不错的选择。