MySQL远程访问设置终极方法

2024-04-06 07:39:03 23

数据库对于服务端开发人员来说，几乎就是左膀右臂。几乎每一个服务端开发人员都要和数据库打交道。而MySQL又以其开源免费，小巧玲珑，简单易用，功能强大占据中国数据库使用份额的半壁江山。从去年接触到Vagrant以来，Vagrant以其方便易用深深吸引了D瓜哥。为了尽可能地和生产环境保持尽可能的一致性，减少不必要的问题的发生，Vagrant+Ubuntu+MySQL几乎是绝配。

MySQL允许远程访问的设置

1.注释bind-address=127.0.0.1。


>cd/etc/mysql

>sudovimmy.cnf

将bind-address=127.0.0.1注释掉（即在行首加#），如下：


#Insteadofskip-networkingthedefaultisnowtolistenonlyon

#localhostwhichismorecompatibleandisnotlesssecure.

#bind-address         =127.0.0.1

除了注视掉这句话之外，还可以把后面的IP地址修改成允许连接的IP地址。但是，如果只是开发用的数据库，为了方便起见，还是推荐直接注释掉。

从上面的注释中，可以看出，旧版本的MySQL（从一些资料上显示是5.0及其以前的版本）上使用的是skip-networking。所以，善意提醒一下，使用旧版本的小伙伴请注意一下。

2.删除匿名用户

登录进数据库：


>mysql-uroot-p123456

然后，切换到数据库mysql。SQL如下：


usemysql;

然后，删除匿名用户。SQL如下：


deletefromuserwhereuser='';

3.增加允许远程访问的用户或者允许现有用户的远程访问。

接着上面，删除匿名用户后，给root授予在任意主机（%）访问任意数据库的所有权限。SQL语句如下：


mysql>grantallprivilegeson*.*to'root'@'%'identifiedby'123456'withgrantoption;

如果需要指定访问主机，可以把%替换为主机的IP或者主机名。另外，这种方法会在数据库mysql的表user中，增加一条记录。如果不想增加记录，只是想把某个已存在的用户（例如root）修改成允许远程主机访问，则可以使用如下SQL来完成：


updateusersethost='%'whereuser='root'andhost='localhost';

4.退出数据库


mysql>exit

在MySQLShell执行完SQL后，需要退出到Bash执行系统命令，需要执行exit。因为这个太常用也太简单。以下内容就提示“退出”，不再重复列出这个命令。

5.重启数据库

完成上述所有步骤后，需要重启一下数据库，才能让修改的配置生效。执行如下命令重启数据库：


>sudoservicemysqlrestart

到此为止，应该就可以远程访问数据库了。

当然，“雄关漫道真如铁”，世界并不是想象的那么图样图森破，可能还会遇到一些问题，影响到正常使用。下面，D瓜哥把自己遇到的一些问题整理处理，方便各位朋友参考解决。（退一步讲，没有这些问题来撑门面，D瓜哥该叫“终极解决办法”吗？！哈哈）

常见问题解答

华仔说的好啊，“出来混的都是要还的”。对于我们从事挨踢行业的小伙伴来说，“出来混的都是从问题堆里走的”。冯巩也说的好，“作为北京人，兜里没揣两千块钱都不好意思给别人打招呼”。D瓜哥想，对于干挨踢的小伙伴们来说，没遇到过问题，都不好意思说自己是干挨踢的。所以，对于一些常见的问题，或者说是常用操作可能引发的问题来说，有必要整理一下，方便以后随时查阅使用。

ERROR1045(28000)错误的原因以及解决办法


>mysql-uroot-p123456

Warning:Usingapasswordonthecommandlineinterfacecanbeinsecure.

ERROR1045(28000):Accessdeniedforuser'root'@'localhost'(usingpassword:YES)

如果没有按照上面流程，删除匿名用户，再或者你是按照网上查的资料来进行设置（大多都没有“删除匿名用户”这步），都可能导致这个问题。至于这个问题的原因说起来，D瓜哥感觉“怪怪”的。要说清楚这个原因，还得从MySQL数据库的用户认证机制说起。

MySQL中，用户相关的信息存在


mysql>descuser;

+------------------------+-----------

|Field                 |Type

+------------------------+-----------

|Host                  |char(60)

|User                  |char(16)

|Password              |char(41)

|Select_priv           |enum('N','Y')

|Insert_priv           |enum('N','Y')

|Update_priv           |enum('N','Y')

|Delete_priv           |enum('N','Y')

|Create_priv           |enum('N','Y')

|Drop_priv             |enum('N','Y')

|Reload_priv           |enum('N','Y')

|Shutdown_priv         |enum('N','Y')

|Process_priv          |enum('N','Y')

|File_priv             |enum('N','Y')

|Grant_priv            |enum('N','Y')

|References_priv       |enum('N','Y')

|Index_priv            |enum('N','Y')

|Alter_priv            |enum('N','Y')

|Show_db_priv          |enum('N','Y')

|Super_priv            |enum('N','Y')

|Create_tmp_table_priv |enum('N','Y')

|Lock_tables_priv      |enum('N','Y')

|Execute_priv          |enum('N','Y')

|Repl_slave_priv       |enum('N','Y')

|Repl_client_priv      |enum('N','Y')

|Create_view_priv      |enum('N','Y')

|Show_view_priv        |enum('N','Y')

|Create_routine_priv   |enum('N','Y')

|Alter_routine_priv    |enum('N','Y')

|Create_user_priv      |enum('N','Y')

|Event_priv            |enum('N','Y')

|Trigger_priv          |enum('N','Y')

|Create_tablespace_priv|enum('N','Y')

|ssl_type              |enum('','ANY'

|ssl_cipher            |blob

|x509_issuer           |blob

|x509_subject          |blob

|max_questions         |int(11)unsigned

|max_updates           |int(11)unsigned

|max_connections       |int(11)unsigned

|max_user_connections  |int(11)unsigned

|plugin                |char(64)

|authentication_string |text

|password_expired      |enum('N','Y')

+------------------------+-----------

43rowsinset(0.00sec)

数据库mysql的user。然我们看一下该表的结构，命令以及返回值如下：
------------------------+------+-----+---------+-------+ |Null|Key|Default|Extra| ------------------------+------+-----+---------+-------+ |NO |PRI| | | |NO |PRI| | | |NO | | | | |NO | |N | | |NO | |N | | |NO | |N | | |NO | |N | | |NO | |N | | |NO | |N | | |NO | |N | | |NO | |N | | |NO | |N | | |NO | |N | | |NO | |N | | |NO | |N | | |NO | |N | | |NO | |N | | |NO | |N | | |NO | |N | | |NO | |N | | |NO | |N | | |NO | |N | | |NO | |N | | |NO | |N | | |NO | |N | | |NO | |N | | |NO | |N | | |NO | |N | | |NO | |N | | |NO | |N | | |NO | |N | | |NO | |N | | ,'X509','SPECIFIED')|NO | | | | |NO | |NULL | | |NO | |NULL | | |NO | |NULL | | |NO | |0 | | |NO | |0 | | |NO | |0 | | |NO | |0 | | |YES | | | | |YES | |NULL | | |NO | |N | | ------------------------+------+-----+---------+-------+

或者查看一下该表的建表语句。命令如下：


mysql>showcreatetableuser;

由于返回值中掺杂了一些不必要的无用信息，为了方便大家查看，D瓜哥对返回值做了简单的处理，只把最主要的建表语句部分提取出来。如下：


CREATETABLE`user`(

 `Host`char(60)COLLATEutf8_binNOTNULLDEFAULT'',

 `User`char(16)COLLATEutf8_binNOTNULLDEFAULT'',

 `Password`char(41)CHARACTERSETlatin1COLLATElatin1_binNOTNULLDEFAULT'',

 `Select_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 `Insert_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 `Update_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 `Delete_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 `Create_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 `Drop_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 `Reload_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 `Shutdown_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 `Process_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 `File_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 `Grant_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 `References_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 `Index_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 `Alter_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 `Show_db_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 `Super_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 `Create_tmp_table_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 `Lock_tables_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 `Execute_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 `Repl_slave_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 `Repl_client_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 `Create_view_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 `Show_view_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 `Create_routine_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 `Alter_routine_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 `Create_user_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 `Event_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 `Trigger_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 `Create_tablespace_priv`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 `ssl_type`enum('','ANY','X509','SPECIFIED')CHARACTERSETutf8NOTNULLDEFAULT'',

 `ssl_cipher`blobNOTNULL,

 `x509_issuer`blobNOTNULL,

 `x509_subject`blobNOTNULL,

 `max_questions`int(11)unsignedNOTNULLDEFAULT'0',

 `max_updates`int(11)unsignedNOTNULLDEFAULT'0',

 `max_connections`int(11)unsignedNOTNULLDEFAULT'0',

 `max_user_connections`int(11)unsignedNOTNULLDEFAULT'0',

 `plugin`char(64)COLLATEutf8_binDEFAULT'',

 `authentication_string`textCOLLATEutf8_bin,

 `password_expired`enum('N','Y')CHARACTERSETutf8NOTNULLDEFAULT'N',

 PRIMARYKEY(`Host`,`User`)

)ENGINE=MyISAMDEFAULTCHARSET=utf8COLLATE=utf8_binCOMMENT='Usersandglobalprivileges'

从上面两个方法中的任何一个都可以看出，MySQL中，使用(Host,User)这样的组合键作为主键，也就是标识唯一一个用户。换句话说，即使对于同一个用户名，例如root，随着host的不同，也表示不同的用户，就可以由不同的密码和权限。这是，我们可以查看一下该表内的数据。SQL以及返回值如下：


mysql>selectuser,host,passwordfromuser;

+------------------+--------------------------+-------------------------------------------+

|user            |host                    |password                                 |

+------------------+--------------------------+-------------------------------------------+

|root            |%                       |*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9|

|root            |vagrant-ubuntu-trusty-64|*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9|

|root            |127.0.0.1               |*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9|

|root            |::1                     |*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9|

|                 |localhost               |                                          |

|                 |vagrant-ubuntu-trusty-64|                                          |

|debian-sys-maint|localhost               |*9B8E84CA988E1CA66CEB919A0D9D3BF1532942B6|

+------------------+--------------------------+-------------------------------------------+

7rowsinset(0.00sec)

可以看到数据库中有如上这些用户。那么，如果有'root'@'localhost'登录时，怎么匹配呢？

根据MySQL官方文档MySQL::MySQL5.6ReferenceManual::6.2.4AccessControl,Stage1:ConnectionVerification里是如下面这样说的：


Whenmultiplematchesarepossible,theservermustdeterminewhichofthemtouse.Itresolvesthisissueasfollows:

Whenevertheserverreadstheusertableintomemory,itsortstherows.

Whenaclientattemptstoconnect,theserverlooksthroughtherowsinsortedorder.

Theserverusesthefirstrowthatmatchestheclienthostnameandusername.

Theserverusessortingrulesthatorderrowswiththemost-specificHostvaluesfirst.LiteralhostnamesandIPaddressesarethemostspecific.(ThespecificityofaliteralIPaddressisnotaffectedbywhetherithasanetmask,so192.168.1.13and192.168.1.0/255.255.255.0areconsideredequallyspecific.)Thepattern‘%'means“anyhost”andisleastspecific.Theemptystring”alsomeans“anyhost”butsortsafter‘%'.RowswiththesameHostvalueareorderedwiththemost-specificUservaluesfirst(ablankUservaluemeans“anyuser”andisleastspecific).

MySQL远程访问设置终极方法

热门推荐

随机推荐