【mysql基础】06、mysql连接相关

yanchuen

　　1、localhost与127.0.0.1的概念和工作原理之不同
　　1）要比较两个东西有什么不同首先要弄清两者的概念。所以我们从概念开始
　　localhost也叫local正确的解释是本地服务器
　　127.0.0.1在windows等系统的正确解释是本机地址(本机服务器)
　　它们的解析通过本机的host文件自动将localhost解析为127.0.0.1
　　2）一个是“本地”一个是“本机”。不过从这两个词来看还是不能比较两者的区别我们再看看他们的工作原理
　　localhot是不经网卡传输的它不受网络防火墙和网卡相关的的限制。
　　127.0.0.1是通过网卡传输的它依赖网卡并受到网络防火墙和网卡相关的限制。
　　通过上述概念和工作原理的比较我想现在大家都心里有谱了吧知道localhost与127.0.0.1有什么不同了。如果localhost不能访问但127.0.0.1可以访问的问题究其原因很可能是localhost访问时系统带的本机当前用户的权限去访问而用ip的时候等于本机是通过网络再去访问本机可能涉及到网络用户的权限。另外我们现在也应该明白了一个道理那就是一般设置程序时本地服务用localhost是最好的localhost不会解析成IP也不会占用网卡、网络资源。
　　2、mysql客户端连接服务器
　　mysql客户端使用mysql -h 127.0.0.1连接mysql服务器时使用TCP/IP连接mysql server认为该连接来自于127.0.0.1或者是"localhost.localdomain"
　　mysql -h localhost 的时候是不使用TCP/IP连接的而使用Unix socket此时mysql server则认为该client是来自"localhost"
　　如果客户端和服务的在同一台服务器上可以配置客户端和服务的基于socket通信在内存中交换数据而不经过tcp/ip协议栈封装
　　测试：
　　为root在不同主机上登录时设置不同的密码
MariaDB [(none)]> select user,host,password from mysql.user;　　
+------+-----------+-------------------------------------------+
　　
| user | host      | password                                  |
　　
+------+-----------+-------------------------------------------+
　　
| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
　　
| root | node5     | *962A429C7BB4542923310C7E4A156A3F7FE97CC6 |
　　
| root | 127.0.0.1 | *78D02788CBC4FB05D363AC13FDABB60C106B1584 |
　　
| root | ::1       | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
　　
| root | %         | *C4E4B59963A6AF043FBE4C36C3209771914E8483 |
　　
+------+-----------+-------------------------------------------+
　　
5 rows in set (0.00 sec)
　　使用localhost登录时：
[root@Node5 mysql]# mysql -hlocalhost -uroot -p123　　
Welcome to the MariaDB monitor.  Commands end with ; or \g.
　　
Your MariaDB connection id is 8
　　
Server version: 10.1.21-MariaDB Source distribution
　　

　　
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
　　

　　
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
　　

　　
MariaDB [(none)]> status
　　
--------------
　　
mysql  Ver 15.1 Distrib 10.1.21-MariaDB, for Linux (x86_64) using readline 5.1
　　

　　
Connection id:8
　　
Current database:
　　
Current user:root@localhost
　　
SSL:Not in use
　　
Current pager:stdout
　　
Using outfile:''
　　
Using delimiter:;
　　
Server:MariaDB
　　
Server version:10.1.21-MariaDB Source distribution
　　
Protocol version:10
　　
Connection:Localhost via UNIX socket
　　
Server characterset:utf8
　　
Db     characterset:utf8
　　
Client characterset:utf8
　　
Conn.  characterset:utf8
　　
UNIX socket:/tmp/mysql.sock
　　
Uptime:2 min 39 sec
　　

　　
Threads: 1  Questions: 6  Slow queries: 0  Opens: 17  Flush tables: 1  Open tables: 11  Queries per second avg: 0.037
　　
--------------
　　

　　
MariaDB [(none)]>
　　使用127.0.0.1时：
[root@Node5 mysql]# mysql -h127.0.0.1 -p123　　
Welcome to the MariaDB monitor.  Commands end with ; or \g.
　　
Your MariaDB connection id is 12
　　
Server version: 10.1.21-MariaDB Source distribution
　　

　　
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
　　

　　
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
　　

　　
MariaDB [(none)]> status
　　
--------------
　　
mysql  Ver 15.1 Distrib 10.1.21-MariaDB, for Linux (x86_64) using readline 5.1
　　

　　
Connection id:12
　　
Current database:
　　
Current user:root@localhost
　　
SSL:Not in use
　　
Current pager:stdout
　　
Using outfile:''
　　
Using delimiter:;
　　
Server:MariaDB
　　
Server version:10.1.21-MariaDB Source distribution
　　
Protocol version:10
　　
Connection:127.0.0.1 via TCP/IP
　　
Server characterset:utf8
　　
Db     characterset:utf8
　　
Client characterset:utf8
　　
Conn.  characterset:utf8
　　
TCP port:3306
　　
Uptime:11 min 40 sec
　　

　　
Threads: 2  Questions: 31  Slow queries: 0  Opens: 17  Flush tables: 1  Open tables: 11  Queries per second avg: 0.044
　　
--------------
　　我们可以看到虽然用的是不同的通信方式但密码都是使用"root"@"localhost"的密码为什么会这样呢
　　基于以下原因MySQL服务端会在内存中维护着一份host信息 包括三部分IP主机名和错误信息。主要用于非本地TCP连接。
　　1. 通过在第一次建立连接时缓存IP和host name的映射关系同一主机的后续连接将直接查看host cache而不用再次进行DNS解析。
　　2. host cache中同样会包含IP登录失败的错误信息。可根据这些信息对这些IP进行相应的限制。
　　host cache的信息可通过performance_schema中host_cache表查看。
　　那么IP和host name的映射关系是如何建立的呢
　　1. 当有一个新的客户端连接进来时MySQL Server会为这个IP在host cache中建立一个新的记录包括IP主机名和client lookup validation flag分别对应host_cache表中的IPHOST和HOST_VALIDATED这三列。第一次建立连接因为只有IP没有主机名所以HOST将设置为NULLHOST_VALIDATED将设置为FALSE。
　　2. MySQL Server检测HOST_VALIDATED的值如果为FALSE它会试图进行DNS解析如果解析成功它将更新HOST的值为主机名并将HOST_VALIDATED值设为TRUE。如果没有解析成功判断失败的原因是永久的还是临时的如果是永久的则HOST的值依旧为NULL且将HOST_VALIDATED的值设置为TRUE后续连接不再进行解析如果该原因是临时的则HOST_VALIDATED依旧为FALSE后续连接会再次进行DNS解析。
　　另解析成功的标志并不只是通过IP获取到主机名即可这只是其中一步还有一步是通过解析后的主机名来反向解析为IP判断该IP是否与原IP相同如果相同才判断为解析成功才能更新host cache中的信息。
　　基于上面的总结下面谈谈 host cache的优缺点
　　缺点当有一个新的客户端连接进来时MySQL Server都要建立一个新的记录如果DNS解析很慢无疑会影响性能。如果被允许访问的主机很多也会影响性能这个与host_cache_size有关这个参数是5.6.5引入的。5.6.8之前默认是128,5.6.8之后默认是-1,基于max_connections的值动态调整。所以如果被允许访问的主机很多基于LRU算法先前建立的连接可能会被挤掉这些主机重新进来时会再次进行DNS查询。
　　优点通常情况下主机名是不变的而IP是多变的。如果一个客户端的IP经常变化那基于IP的授权将是一个繁琐的过程。因为你很难确定IP什么时候变化。而基于主机名只需一次授权。而且基于host cache中的失败信息可在一定程度上阻止外界的暴力破解***。
　　关于阻止外界的暴力破解***涉及到max_connect_errors参数默认为100官方的解释如下
If more than this many successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections.　　如果某个客户端的连接达到了max_connect_errors的限制将被禁止访问并提示以下错误
Host 'host_name' is blocked because of many connection errors.　　
Unblock with 'mysqladmin flush-hosts'
　　下面来模拟一下
　　首先设置max_connect_errors的值：
mysql> show variables like 'max_connect_errors';　　
+--------------------+-------+| Variable_name      | Value |+--------------------+-------+| max_connect_errors | 100   |+--------------------+-------+1 row in set (0.00 sec)
　　

　　
mysql> set global max_connect_errors=2;
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
mysql> show variables like 'max_connect_errors';
　　
+--------------------+-------+| Variable_name      | Value |+--------------------+-------+| max_connect_errors | 2     |+--------------------+-------+1 row in set (0.00 sec)
　　通过telnet模拟interrupted without a successful connection。
[root@mysql-slave1 ~]# telnet 192.168.244.145 3306Trying 192.168.244.145...　　
Connected to 192.168.244.145.
　　
Escape character is '^]'.
　　
N5.6.26-log
　　
          K]qA1nYT!w|+ZhxF1c#|kmysql_native_password^]!#08S01Got packets out of orderConnection closed by foreign host.
　　
[root@mysql-slave1 ~]# telnet 192.168.244.145 3306Trying 192.168.244.145...
　　
Connected to 192.168.244.145.
　　
Escape character is '^]'.
　　
N
　　
Y#>PVB(>!Bl}NKnjIj]sMmysql_native_password^]!#08S01Got packets out of orderConnection closed by foreign host.
　　
[root@mysql-slave1 ~]# mysql -h192.168.244.145 -uroot -p123456
　　
Warning: Using a password on the command line interface can be insecure.
　　
ERROR 1129 (HY000): Host '192.168.244.144' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
　　即便后来使用了正确的账号和密码登录依旧会被阻止。
　　再来看看host_cache表中的信息sum_connect_errors为2了。
mysql> select ip,host,host_validated,sum_connect_errors,count_authentication_errors from performance_schema.host_cache;　　
+-----------------+------+----------------+--------------------+-----------------------------+| ip              | host | host_validated | sum_connect_errors | count_authentication_errors |+-----------------+------+----------------+--------------------+-----------------------------+| 192.168.244.144 | NULL | YES            |                  2 |                           0 |+-----------------+------+----------------+--------------------+-----------------------------+1 row in set (0.00 sec)
　　该阻止会一直生效直到采取以下操作
　　1. mysql> flush hosts;
　　2. # mysqladmin flush-hosts
　　3. truncate table performance_schema.host_cache;
　　4. 或者等待该记录从host cache中被挤掉。
　　如果要禁止DNS解析可设置skip_name_resolve参数这样mysql.user表中基于主机名的授权将无法使用且错误日志中会提示
[Warning] 'user' entry 'root@mysql-slave1' ignored in --skip-name-resolve mode.　　这里通过mysql-slave1访问将会拒绝访问：
[root@mysql-slave1 ~]# mysql -h192.168.244.145 -uroot -p123　　
Warning: Using a password on the command line interface can be insecure.
　　
ERROR 1045 (28000): Access denied for user 'root'@'192.168.244.144' (using password: YES)
　　host cache是默认开启的如果要禁掉可将host_cache_size设置为0该参数是个动态参数可在线修改。
　　如果要完全禁掉TCP/IP连接,可在MySQL启动时设置skip-networking参数。
　　总结
　　1. 从原理上看DNS解析一般只针对客户端的第一次连接客户端数据量比较小的情况下开销其实不大完全不必禁掉skip_name_resolve参数带来的好处就是为客户端和多变的IP直接解耦只需对主机名进行一次授权。
　　可通过\s查看当前连接使用的是socket还是TCP。
　　2. 奇怪的是对于skip_name_resolve参数虽然官方文档说的是布尔值
　　但如果在配置文件中指定了无论是skip_name_resolve=off或者skip_name_resolve=0。
　　最后通过show variables like '%skip_name_resolve%'查看均显示ON。将该参数设置为OFF的唯一办法是不写该参数因为它默认值即为OFF。
　　因为mysql客户端连接服务器时mysql服务器默认会将ip地址反解成主机名将127.0.0.1解析成localhost导致登录的mysql服务器的帐号就发生了改变容易造成权限问题应该禁止名称解析包括主机名还能加快Mysql的连接速度
　　在/etc/my.cnf文件中添加skip_name_resolve = on在用rpm安装OS提供的5.1.73版本的mysql里使用skip_name_resolve否则会报错选项跳过名称反解。
　　关掉名称反解后root使用localhost和127.0.0.1的密码就不同
　　通过测试也知道了当"root"@"localhost"和"root"@"127.0.0.1"单独设置了密码时要使用设置的密码而不是"root"@"%"的密码当对localhost和127.0.0.1没这是密码时就可以使用"root"@“%”的密码。
　　3、可以在配置文件客户端段写上帐号和密码就默认使用该帐号和密码登录
　　[mysql]
　　user = root
　　host = localhost
　　password = anyfish
　　
　　4、修改mysql用户密码的方法
　　1mysqladmin命令shell中
　　格式如下
　　mysqladmin [-h HOST] -u USER password “新密码” [-p"旧密码"]
　　mysqladmin -u UserName -h Host -p flush-privileges  # 重读授权表
　　2改表mysql命令行中
　　这种方式必须是先用root帐户登入mysql然后执行
　　UPDATE user SET password=PASSWORD('123456') WHERE user='root';
　　FLUSH PRIVILEGES;
　　3SET PASSWORD 语句mysql命令行中
　　这种方式也需要先用root命令登入mysql然后执行
　　SET PASSWORD FOR "root"@"host"=PASSWORD('123456');
　　5、linux下mysql的root密码忘记解决方法
　　1）首先确认服务器出于安全的状态也就是没有人能够任意地连接MySQL数据库。 因为在重新设置MySQL的root密码的期间MySQL数据库完全出于没有密码保护的 状态下其他的用户也可以任意地登录和修改MySQL的信息。可以采用将MySQL对外的端口封闭并且停止Apache以及所有的用户进程的方法实现服务器的准安全状态。最安全的状态是到服务器的Console上面操作并且拔掉网线。
　　2）修改MySQL的登录设置
　　# vi /etc/my.cnf
　　在[mysqld]的段中加上一句skip_grant_tables
　　3）重新启动mysqld
　　4）登录并修改MySQL的root密码
　　此时不能使用mysqladmin工具和SET PASSWORD语句来修改密码只能使用UPDATE user SET语句来修改root的密码
　　5）将MySQL的登录设置修改回来
　　6）重新启动mysqld