白日夢的MySQL專題（第33篇）：各種登陸MySQL的騷操作

閱讀原文

前置知識

我們想登陸到mysql中前提是肯定需要一個使用者名稱和密碼：比如

mysql -uroot -proot

在mysql中使用者的資訊會存放在 mysql資料庫下的 user表中
可以像下面這樣檢視到所有使用者資訊

mysql> use mysql
Database changed
mysql> select * from user\G
*************************** 1. row ***************************
                  Host: localhost
                  User: root
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *C85A9826269E1AD748DFC3CEC32D040735B27207
      password_expired: N
 password_last_changed: 2019-11-07 14:39:30
     password_lifetime: NULL
        account_locked: N
*************************** 2. row ***************************
                  Host: localhost
                  User: mysql.session
           Select_priv: N

其中有一列叫做HOST，HOST的不同值決定了使用者擁有不同的登陸方式：比如：

識別符號	含義
%	任意ip均等登陸
localhost	只允許本地登陸
127.0.0.1	只允許本地登陸
sv1	主機名為sv1的機器可登入，主機名可以在 /etc/hostname中檢視
::1	本機可登入

所以在登陸前，請確定你的使用的登陸使用者的HOST列中有相應的配置

騷氣的登陸

在mac上登陸華為雲的伺服器

MacBook-Pro% ssh 'root'@'139.9.92.123'
root@139.9.92.123's password:
Last failed login: Fri May 29 11:03:42 CST 2020 from 202.85.208.14 on ssh:notty
There was 1 failed login attempt since the last successful login.
Last login: Thu May 28 16:36:32 2020 from 202.85.208.7

	Welcome to Huawei Cloud Service

-bash: warning: setlocale: LC_CTYPE: cannot change locale (UTF-8): No such file or directory
[root@139 ~]#

在mac上遠端登陸伺服器上的mysql

MacBook-Pro% ./mysql -h139.9.92.123 -uroot -reqw123.. -P3306
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 2174
Server version: 5.7.29 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |

mac登陸本地的mysql

如果你有配置環境變數，或者你的mysql的可執行檔案在/etc/bin中，那你可以在任何目錄中使用mysql命令

你可以直接像下面這樣登陸：

MacBook-Pro% mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.30 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>

如果你沒有配置環境變數，系統就不能直接識別mysql命令，需要你進入到mysql安裝目錄下的bin檔案下，找到mysql命令，然後執行登陸的動作

MacBook-Pro% /usr/local/mysql/bin/mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.30 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>

也可以用遠端登陸的方式登陸本地mysql

MacBook-Pro% mysql -h127.0.0.1 -uroot -proot -P3306
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 4
Server version: 5.7.30 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| assignment         |
| cal                |

本地登陸

我們可以藉助mysql.sock實現本地登陸。

那這個mysql.sock是什麼？

看起來我們需要了解一下mysql.sock的作用，因為通過它我們可以實現mysql的本地登陸。

mysql.sock應該是mysql的主機和客戶機在同一host（物理伺服器）上的時候，使用unix domain socket做為通訊協議的載體，它比tcp快。

通過命令可以檢視到mysql.sock的位置。

MacBook-Pro% netstat -ln | grep mysql
64e3f4c55eb824d7 stream  0   0  64e3f4c5614859a7   0   0   0 /tmp/mysql.sock

記下這個 mysql.sock的地址。接下來我們會建立一個配置檔案，你找個看著比較順眼的目錄放置這個配置檔案。

比如就像下面這樣：

MacBook-Pro% sudo mkdir etc
MacBook-Pro% ls -l
total 552
-rw-r--r--   1 root    wheel   275235 Mar 24 01:35 LICENSE
-rw-r--r--   1 root    wheel      587 Mar 24 01:35 README
drwxr-xr-x  40 root    wheel     1280 Mar 24 02:45 bin
drwxr-x---  27 _mysql  _mysql     864 May 28 20:44 data
drwxr-xr-x   5 root    wheel      160 Mar 24 02:44 docs
drwxr-xr-x   2 root    wheel       64 May 29 11:39 etc
drwxr-xr-x  53 root    wheel     1696 Mar 24 02:44 include
drwxr-x---   3 _mysql  _mysql      96 May 28 20:44 keyring
drwxr-xr-x  11 root    wheel      352 May 13 09:16 lib
drwxr-xr-x   4 root    wheel      128 Mar 24 02:44 man
drwxr-xr-x  39 root    wheel     1248 Mar 24 02:44 share
drwxr-xr-x   6 root    wheel      192 May 28 19:20 support-files
MacBook-Pro% cd etc
MacBook-Pro% sudo touch user.root.cnf
MacBook-Pro% sudo vim user.root.cnf

然後在 user.root.cnf 中新增如下的配置：

[client]
user=root
password=root
socket=/tmp/mysql.sock

好了，現在可以這樣實現本地登陸

MacBook-Pro% ../bin/mysql --defaults-extra-file=./user.root.cnf
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.30 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>

花裡胡哨的本地登陸

有時候，你可能會看到其他大佬登陸mysql時直接使用命令： mysql.local 就騷氣十足的本地登陸mysql

他是怎麼做到的呢？其實很簡單、藉助alias+mysql.sock實現：

為我們的登陸mysql的命令新增別名，像下面這樣：

MacBook-Pro% alias mysql.local='/usr/local/mysql/bin/mysql --defaults-extra-file=/usr/local/mysql/etc/user.root.cnf'
MacBook-Pro% mysql.local
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.30 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>

從此，你也可以騷氣登陸mysql

白日夢的MySQL專題（第33篇）：各種登陸MySQL的騷操作

閱讀原文

前置知識

騷氣的登陸

推薦閱讀

相關文章