MySQL远程访问设置终极方法
数据库对于服务端开发人员来说,几乎就是左膀右臂。几乎每一个服务端开发人员都要和数据库打交道。而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的user。然我们看一下该表的结构,命令以及返回值如下:
mysql>descuser; +------------------------+-----------------------------------+------+-----+---------+-------+ |Field |Type |Null|Key|Default|Extra| +------------------------+-----------------------------------+------+-----+---------+-------+ |Host |char(60) |NO |PRI| | | |User |char(16) |NO |PRI| | | |Password |char(41) |NO | | | | |Select_priv |enum('N','Y') |NO | |N | | |Insert_priv |enum('N','Y') |NO | |N | | |Update_priv |enum('N','Y') |NO | |N | | |Delete_priv |enum('N','Y') |NO | |N | | |Create_priv |enum('N','Y') |NO | |N | | |Drop_priv |enum('N','Y') |NO | |N | | |Reload_priv |enum('N','Y') |NO | |N | | |Shutdown_priv |enum('N','Y') |NO | |N | | |Process_priv |enum('N','Y') |NO | |N | | |File_priv |enum('N','Y') |NO | |N | | |Grant_priv |enum('N','Y') |NO | |N | | |References_priv |enum('N','Y') |NO | |N | | |Index_priv |enum('N','Y') |NO | |N | | |Alter_priv |enum('N','Y') |NO | |N | | |Show_db_priv |enum('N','Y') |NO | |N | | |Super_priv |enum('N','Y') |NO | |N | | |Create_tmp_table_priv |enum('N','Y') |NO | |N | | |Lock_tables_priv |enum('N','Y') |NO | |N | | |Execute_priv |enum('N','Y') |NO | |N | | |Repl_slave_priv |enum('N','Y') |NO | |N | | |Repl_client_priv |enum('N','Y') |NO | |N | | |Create_view_priv |enum('N','Y') |NO | |N | | |Show_view_priv |enum('N','Y') |NO | |N | | |Create_routine_priv |enum('N','Y') |NO | |N | | |Alter_routine_priv |enum('N','Y') |NO | |N | | |Create_user_priv |enum('N','Y') |NO | |N | | |Event_priv |enum('N','Y') |NO | |N | | |Trigger_priv |enum('N','Y') |NO | |N | | |Create_tablespace_priv|enum('N','Y') |NO | |N | | |ssl_type |enum('','ANY','X509','SPECIFIED')|NO | | | | |ssl_cipher |blob |NO | |NULL | | |x509_issuer |blob |NO | |NULL | | |x509_subject |blob |NO | |NULL | | |max_questions |int(11)unsigned |NO | |0 | | |max_updates |int(11)unsigned |NO | |0 | | |max_connections |int(11)unsigned |NO | |0 | | |max_user_connections |int(11)unsigned |NO | |0 | | |plugin |char(64) |YES | | | | |authentication_string |text |YES | |NULL | | |password_expired |enum('N','Y') |NO | |N | | +------------------------+-----------------------------------+------+-----+---------+-------+ 43rowsinset(0.00sec)
或者查看一下该表的建表语句。命令如下:
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).