MySQL连接方式

关于MySQL的连接问题一直是一个最常见、最普遍问题，同时也是最容易被大家忽略的问题。下面就MySQL的连接方式，简单做一下自己的总结：

一、Socket连接方式
前提条件：只能在MySQL客户端和数据库实例在一台服务器上才可以使用。

mysql默认是以socket的方式登陆的。
localhost使用的也是该方式进行登陆的，localhost方式不通过网络传输，不受网络防火墙和网卡相关的限制。
socket方式制定，使用 -S 选项指定使用的socket。

1. 默认方式
   # mysql -uroot -p123456
2. localhost方式
   # mysql -uroot -p123456 -hlocalhost
3. socket方式
   # mysql -uroot -p123456 -S /tmp/mysql.sock

3种方式的结果相同如下（可以自行测试）：

[root@node1 ~]# mysql -uroot -p123456Warning: 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 5Server version: 5.6.30-76.3-25.16-log Percona XtraDB Cluster binary (GPL) 5.6.30-25.16, Revision aa929cb, wsrep_25.16Copyright (c) 2009-2016 Percona LLC and/or its affiliatesCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.(product)root@localhost [(none)]> \s--------------mysql  Ver 14.14 Distrib 5.6.30-76.3, for Linux (x86_64) using  6.0Connection id:          5Current database:Current user:           root@localhost   <------------ 当前连接用户和方式SSL:                    Not in useCurrent pager:          stdoutUsing outfile:          ''Using delimiter:        ;Server version:         5.6.30-76.3-25.16-log Percona XtraDB Cluster binary (GPL) 5.6.30-25.16, Revision aa929cb, wsrep_25.16Protocol version:       10Connection:             Localhost via UNIX socket      <-------------- socket方式连接Server characterset:    utf8Db     characterset:    utf8Client characterset:    utf8Conn.  characterset:    utf8UNIX socket:            /tmp/mysql.sock   <----------- 使用的socket套接字Uptime:                 34 min 43 secThreads: 3  Questions: 12  Slow queries: 0  Opens: 70  Flush tables: 1  Open tables: 63  Queries per second avg: 0.005--------------(product)root@localhost [(none)]> 

注意：在使用socket方式时，为了防止所有用户都可以删除socket，可以对 /tmp 目录设置粘着位。这样只有用户本身和root用户才有权限删除改socket文件。
# chmod +t /tmp
# ls -ld /tmp

二、TCP/IP连接方式
mysql在登陆时，指定-h参数选项时，会采用tcp/ip方式进行连接。
127.0.0.1 默认也是采用该方式，通过网卡传输，依赖网卡，并受到网络防火墙和网卡相关的限制。
如：# mysql -uroot -p123456 -h127.0.0.1 -P3308

(product)root@localhost [(none)]> select host,user from mysql.user;+-----------+-------------+| host      | user        |+-----------+-------------+| %         | anemometer  || %         | pxc-monitor || %         | sbtest      || %         | wujj        || 127.0.0.1 | root       |      <----------- 必须有127.0.0.1的IP地址| localhost | root        || localhost | sst         |+-----------+-------------+7 rows in set (0.02 sec)(product)root@localhost [(none)]> [root@node1 ~]# mysql -uroot -p123456 -h127.0.0.1 -P3308Warning: 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 8Server version: 5.6.30-76.3-25.16-log Percona XtraDB Cluster binary (GPL) 5.6.30-25.16, Revision aa929cb, wsrep_25.16Copyright (c) 2009-2016 Percona LLC and/or its affiliatesCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.(product)root@127.0.0.1 [(none)]> \s--------------mysql  Ver 14.14 Distrib 5.6.30-76.3, for Linux (x86_64) using  6.0Connection id:          8Current database:Current user:           root@localhost    <------------ 当前连接用户和方式SSL:                    Not in useCurrent pager:          stdoutUsing outfile:          ''Using delimiter:        ;Server version:         5.6.30-76.3-25.16-log Percona XtraDB Cluster binary (GPL) 5.6.30-25.16, Revision aa929cb, wsrep_25.16Protocol version:       10Connection:             127.0.0.1 via TCP/IP  <-----------TCP/IP方式连接Server characterset:    utf8Db     characterset:    utf8Client characterset:    utf8Conn.  characterset:    utf8TCP port:               3308Uptime:                 50 min 39 secThreads: 3  Questions: 28  Slow queries: 0  Opens: 70  Flush tables: 1  Open tables: 63  Queries per second avg: 0.009--------------(product)root@127.0.0.1 [(none)]> 

注意：MySQL 登陆时，同时指定-h和-S(套接字)， MySQL 会默认使用tcp/ip的方式连接。因此随便指定一个socket文件都可以登陆。

三、自定义连接方式参数

[root@node1 ~]# mysql --help|grep protocol  -C, --compress      Use compression in server/client protocol.  --protocol=name     The protocol to use for connection (tcp, socket, pipe,                      (pre-4.1.1) protocol.[root@node1 ~]# 

可以发现默认有3个选项：tcp，socket和pipe（pipe就不说了，有兴趣的可以自己研究）

四、常用检查命令
shell> mysqladmin version
shell> mysqladmin variables
shell> mysqladmin -h hostname version variables
shell> mysqladmin -h hostname –port=3306 version
shell> mysqladmin -h host_ip version
shell> mysqladmin –protocol=SOCKET –socket=/tmp/mysql.sock version

链接地址：

http://dev.mysql.com/doc/refman/5.6/en/can-not-connect-to-server.html