Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

remote host is not allowed to connect to this mysql server

Posted on 2005-09-30
7
Medium Priority
?
130,116 Views
Last Modified: 2011-08-18
Hi,

Please kindly assist how can we remotly login to mysql ?

running on fedora core 3 (fresh install) everything is default.

how can we change the root (mysql) password? are there commands we need to run to get mysql setup and ready to rock n roll?  :)

I'm on a windows client trying to connect to mysql, error mesasge:remote host is not allowed to connect to this mysql server

thanks.
0
Comment
Question by:EdwardPeter
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 10

Assisted Solution

by:mensuck
mensuck earned 200 total points
ID: 14994878
#1

mysqladmin -u [user] -p password 'newpassword'

(you will be prompted for current password, hit enter if it's blank)

#2

mysql -u root -h hostname.domain.com -p password 'newpassword'

(you will be prompted for current password, hit enter if it's blank)


ms!


0
 
LVL 12

Assisted Solution

by:str_kani
str_kani earned 800 total points
ID: 14998119
1)By default mysql does not support remote access...

2) you will have to specify this in the server first, (ofcourse you will not be able to do this from a remote machine!!! unless you have remote desktop access)

3)To enable access from a client machine called nile, do the following after starting mysqld (or mysqld_safe) on the server, named delaware:

% mysql -u root -p
mysql> use mysql;
mysql> grant all privileges on *.* to usrname1@'%'
identified by 'test' with grant option;
mysql> insert into host values('nile', '%', 'Y', 'Y', 'Y', 'Y', 'Y',
'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');
mysql> exit


Hope this helps you!

The user name, usrname1, and the password, test, should be replaced by the real value.
One can then run mysql on nile as follows:

  % mysql --host=delaware --user=usrname1 -ptest

To restrict access to a user from a specific host, change '%' in the grant command to a valid hostname or ip address.

0
 
LVL 26

Accepted Solution

by:
Umesh earned 800 total points
ID: 14998377
Hi!!!

Identical to above...

To change the password(for root or any user)...

on Linux machine..

[ushastry@mmkserv test]#mysqladmin -uUserName -pOldPassword 'NewPassword'

///remote host is not allowed to connect to this mysql server

{From DOCS}
If the following error occurs when you try to connect from a host other than the one on which the MySQL server is running, it means that there is no row in the user table with a Host value that matches the client host:

Host ... is not allowed to connect to this MySQL server

You can fix this by setting up an account for the combination of client hostname and username that you are using when trying to connect.

If you do not know the IP number or hostname of the machine from which you are connecting, you should put a row with '%' as the Host column value in the user table. After trying to connect from the client machine, use a SELECT USER() query to see how you really did connect. (Then change the '%' in the user table row to the actual hostname that shows up in the log. Otherwise, your system is left insecure because it allows connections from any host for the given username.)

Login to MySQL Server(host on which MySQL Server is running)

[root@mmkserv test]# mysql -usqladmin -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.0.20-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> GRANT ALL ON *.* TO 'EdwardPeter'@'%' IDENTIFIED BY '123';
Query OK, 0 rows affected (0.14 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.16 sec)

mysql> \q
Bye


From windows machine...

C:\mysql\bin\>mysql -hhostname -uEdwardPeter -p123



0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 3

Assisted Solution

by:Cyberstorm22
Cyberstorm22 earned 200 total points
ID: 15035351
Hi

If you are not sure how to configure, follow these steps:

1. Checking the mysql configuation; edit my.cnf

there a 2 different parameters depending on your mysql version

*for mysql < 4.1  (i think so) find the parameter    skip-networking    and comment it (#skip-networking)
*for mysql > 4.1  (i think so) find the parameter    bind-address        and comment it (#bind-adress)

also check the mysql listening port  (find parameter  port  and have a look which one is set)  normally it is set to 3306, we need that in 3.

restart mysql


2. Set mysql rights for an existing  user to get remote access rights. Login to mysql thorigh onsole as root and execute:

GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'myclientip' IDENTIFIED BY 'mypassword';  


or create a new user with remote acces rights, with:

CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypassword';
GRANT ALL ON *.* to myuser;


3. Firewall configuration
Normally it should work now, but if not:

If the client computer is behind a firewall or behind a router check if the needed mysql port (see point 1, normally 3306) is open to your client maschine.
If not try to pass the port through your router to your client maschine (better then open it for everyone, it depends on your purpose)
Also open the port at your client firewall.
For testing purpose it's always good to temporarly disable the client firewall (windows firewall or another product you use)

4. Now use whatever you want to connect, ODBC, PHP, your programm

MySQL ODBC Connector can be downloaded here:   http://dev.mysql.com/downloads/connector/odbc/3.51.html


-- Christian from Austria
0
 
LVL 3

Expert Comment

by:Cyberstorm22
ID: 15035370
LOL

I should read my statements before sending them. Well its late here in Autria (2:40 am, so please forgive me)
In 2. i mean: ...login to mysql through mysql-console as root-user

-- Christian from Austria
0
 

Expert Comment

by:slalji
ID: 20135408
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

this will give access to user monty with some_pass as thier password to access to mysql from a client like navicat or mysqlfront

the '@%' is a wildcard and therefore you dont have to specify your host ip here.
0
 
LVL 16

Expert Comment

by:Rose Babu
ID: 26512998
Hi,

i've installed php5 and mysql in my cloud server.

mysql is get connected in the putty console and as well as heidi sql.

but while using those db credentials in the hosting pages the DB is not get connected. and i couldnt figure out the error message too.

both root access and user access (new user is created and granted all privileges too) for mysql are not connected in the php pages.
but it does in the putty and heidi sql.

the ports 3306, 3307 were opened. in iptables it is set to open.

should i install any connector for mysql in cloud server?

please provide some solutions to connect the mysql db in the php pages.

Thanks in advance,
Rose.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question