linumysql远程登录,及用户权限

1、linuxmysql远程登录，及用户权限在windows和linux上远程登录时，通常会遇到拒绝连接，密码错误，或者权限不足。以致无法正常的使用数据库，如无法创建数据库，看不到系统数据库mysql等等。报错如下。C:Userszouqone>mysql-h38-uroot-p1ERROR1130(00000):Host'1'isnotallowedtoconnecttothisMySQLserver或者权限不足，看不到所有的数据库#mysql-uroot-p1WelcometotheMySQLmonitor.Commandsendwit

2、h;org.YourMySQLconnectionidis10Serverversion:5.5.25a-logMySQLCommunityServer(GPL)Copyright(c)2000,2011,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'h'forhe

3、lp.Type'c'toclearthecurrentinputstatement.mysql>showdatabases;+|Database|+|information_schema|test|+2rowsinset(0.00sec)其中，mysql，无法看到，但是明显mysql是存在的。解决方法：进入数据库，修改系统user表。#mysql-uroot-pEnterpassword:WelcometotheMySQLmonitor.Commandsendwith;org.YourMySQLconnectionidis2Serverversion:5.5.25a-lo

4、gMySQLCommunityServer(GPL)Copyright(c)2000,2011,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'h'forhelp.Type'c'toclearthecurrentinputstatement.mysql

5、>usemysql;Databasechanged查看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|lnsert_priv|enum('N',Y)|NO|N|Update_priv|enum('N',Y)|NO|N|Delete_priv|enum('N

6、',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|lndex_priv|enum(N,Y)|NO|N|Alter_priv|enum(N,Y)|NO|N|Show_db_priv|enu

7、m('N',Y)|NO|N|Super_priv|enum(N,Y)|NO|N|Ceate_tmp_table_piv|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|en

8、um(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|x509Jssuer|blob|NO|NU

9、LL|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|+-42rowsinset(0.00sec)查看所有用户mysql>selecthost,user,password,Proces

10、s_privfromuser;+-+|host|user|password|Process_priv|+-+|%|root|*E6CC90B878B948C35E92B003C792C46C58C4AF40|Y|ytc|root|*E6CC90B878B948C35E92B003C792C46C58C4AF40|Y||root|*E6CC90B878B948C35E92B003C792C46C58C4AF40|Y|:1|root|*E6CC90B878B948C35E92B003C792C46C58C4AF40|Y|ytc|*E6CC90B878B948C35E92B003C

11、792C46C58C4AF40|N|localhost|root|*E6CC90B878B948C35E92B003C792C46C58C4AF40|Y|+-+6rowsinset(0.00sec)host为%的是所有网址都可访问数据，为localhost是本地登录这两个是必须的。其他的host是特殊名登录限于本地如修改root权限：删除原来的host为%,localhost的记录插入两条记录。INSERTINTO'user'VALUES('localhost','root','*E6CC90B878B948C35E92B003C792C

12、46C58C4AF40','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y&

13、#39;,'Y','Y','Y','Y','Y','','','','','0','0','0','0','','');INSERTINTO'user'VALUES('%','root','*E6CC90B878B948C35E92B003C792C46C58C4AF40','Y'

14、;,'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'

15、;,'Y','Y','Y','','','','','0','0','0','0','','');重启服务#servicemysql.serverrestartShuttingdownMySQL*StartingMySQL*linux本地登录#mysql-uroot-pEnterpassword:WelcometotheMySQLmonitor.Commandsendwith;org.Yo

16、urMySQLconnectionidis1Serverversion:5.5.25a-logMySQLCommunityServer(GPL)Copyright(c)2000,2011,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'h'forhelp.Type'c'toclearthecurrentinputstatement.mysql>showdatabases;+|Database|+|information_schema|mysql|performance_schema|test|+4rowsinset(0.00sec)远程登录C:Userszouqone>mysql-h38-uroot-p1WelcometotheMySQLmonitor.Command