盘点Mysql的登陆方式

  1. 云栖社区>
  2. 博客>
  3. 正文

盘点Mysql的登陆方式

推荐码发放 2020-05-30 07:58:05 浏览704
展开阅读全文

盘点Mysql的登陆方式

前置知识#
我们想登陆到mysql中前提是肯定需要一个用户名和密码:比如 root root

在mysql中用户的信息会存放在 mysql数据库下的 user表中

可以 use mysql 然后select * from userG;查看到系统上的所用的用户信息;

其中有一列叫做HOST,HOST的不同值决定了用户拥有不同的登陆方式:比如:

标识符 含义
% 任意ip均等登陆
localhost 只允许本地登陆
127.0.0.1 只允许本地登陆
sv1 主机名为sv1的机器可登录,主机名可以在 /etc/hostname中查看
::1 本机可登录
所以在登陆前,请确定你的使用的登陆用户的HOST列中有相应的配置

骚气的登陆#
在mac上登陆华为云的服务器

Copy
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

Copy
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

  1. 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命令

你可以直接像下面这样登陆:

Copy
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

  1. 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命令,然后执行登陆的动作

Copy
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

  1. 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

Copy
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

  1. 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的位置。

Copy
MacBook-Pro% netstat -ln | grep mysql
64e3f4c55eb824d7 stream 0 0 64e3f4c5614859a7 0 0 0 /tmp/mysql.sock
记下这个 mysql.sock的地址。接下来我们会创建一个配置文件,你找个看着比较顺眼的目录放置这个配置文件。

我实在这样创建的:

Copy
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 中添加如下的配置:

Copy
[client]
user=root
password=root
socket=/tmp/mysql.sock
好了,现在可以这样实现本地登陆

Copy
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

  1. 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的命令添加别名,像下面这样:

Copy
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

  1. 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

我是bloger 赐我白日梦 , 欢迎关注我,后续一定分享mysql相关知识点

作者: 赐我白日梦

出处:https://www.cnblogs.com/ZhuChangwu/p/12987168.html

网友评论

作者关闭了评论
推荐码发放
+ 关注