How to Allow MySQL Client to Connect to Remote MySQL server

by Ramesh Natarajan on August 12, 2010

By default, MySQL does not allow remote clients to connect to the MySQL database.

If you try to connect to a remote MySQL database from your client system, you will get “ERROR 1130: Host is not allowed to connect to this MySQL server” message as shown below.

$ mysql -h 192.168.1.8 -u root -p
Enter password:
ERROR 1130: Host '192.168.1.4' is not allowed to connect to this MySQL server

You can also validate this by doing telnet to 3306 mysql port as shown below, which will also give the same “host is not allowed to connect to this mysql server” error message as shown below.

$ telnet 192.168.1.8 3306
host 192.168.1.4 is not allowed to connect to this mysql server

If you want to allow a specific client ip-address (for example: 192.168.1.4) to access the mysql database running on a server, you should execute the following command on the server that is running the mysql database.

$ mysql -u root -p
Enter password:

mysql> use mysql

mysql> GRANT ALL ON *.* to root@'192.168.1.4' IDENTIFIED BY 'your-root-password'; 

mysql> FLUSH PRIVILEGES;

Also, update firewall rules to make sure port# 3306 is open on the server that is running the mysql database.

After the above changes, when you try to connect to the mysql database from a remote client, you’ll not get the “Host is not allowed to connect to this MySQL server” error message anymore.


Linux Sysadmin Course Linux provides several powerful administrative tools and utilities which will help you to manage your systems effectively. If you don’t know what these tools are and how to use them, you could be spending lot of time trying to perform even the basic administrative tasks. The focus of this course is to help you understand system administration tools, which will help you to become an effective Linux system administrator.
Get the Linux Sysadmin Course Now!

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

{ 31 comments… read them below or add one }

1 Aravind July 7, 2011 at 6:16 am

It worked. Thanks a lot!

2 Prasanth Varghese October 18, 2011 at 10:51 am

Worked 4 me.Thanx

3 GiangNT October 19, 2011 at 8:43 am

Thank you very much! :)

4 balajisk December 5, 2011 at 6:43 am

worked for me . Thanks a lot

5 Caroline January 10, 2012 at 3:28 am

And ‘root’ can also be the name of a particular database? I prefer to keep all other databases on the same IP untouched.

Best,
Caroline

6 Anonymous March 6, 2012 at 3:48 pm

Thanks it worked

7 Tim Carlson April 27, 2012 at 2:42 pm

This just saved my sanity!!!! Thanks!

8 zoran May 6, 2012 at 5:17 am

Thanks a lot, this worked very well for me!

9 Anonymous May 8, 2012 at 11:44 pm

Thanks works like charm on MySQL5.1

10 Andy82 May 17, 2012 at 12:13 pm

Thanks for help …. it works perfect

11 ade July 19, 2012 at 3:40 am

why that does not work for me?

how to update filewall rule?

12 Anonymous July 21, 2012 at 10:11 am

Thanku very muchhhhhhhhhh

13 Thabca Nil Mishra July 30, 2012 at 1:12 am

Thanx for your query. It really working fine. Thanks again

14 vijay August 6, 2012 at 12:19 am

worked for me, thanks a ton

15 Sudarson August 17, 2012 at 4:32 am

Worked for myself……..

16 Ethan August 20, 2012 at 4:53 am

It is instructive. I recommand this to these use MySql

17 Jerry August 20, 2012 at 5:40 pm

If you want to allow all clients to connect you may do following

GRANT ALL ON *.* to root@’%’ IDENTIFIED BY ‘your-root-password’;

18 pouya September 19, 2012 at 10:11 am

hi but why we do that? is it secure to make a connection with remote computer this way?
Is it practical?

I recommend to use ssh for remote communication it’s easy and secure
anyway remote computer firewall also needs to accept communication with the target mysql. so in the firewall settings of the remote sever u should make a new rule to accept communication from local ip address.

19 Anwar Yagoub October 22, 2012 at 2:23 am

Works like charm thanks

20 PPmarcel October 26, 2012 at 9:47 am

Hi, thank you for this, it helped me.

Just a precision : FLUSH PRIVILEGES is not needed with GRANT command.
You only need it when you change a table manually (UPDATE, DELETE, etc).

21 Mann November 1, 2012 at 12:28 am

I am also trying to configure MySQL in client-server architecture.
1) Do I need to install mysql client on all machine, which I want to use as client?
2) What are the things, I need to take care?

22 prasanth M N December 17, 2012 at 11:31 pm

worked
thanks

23 Sushil December 31, 2012 at 1:41 am

U saved my day..thanks a lot..it worked.

24 Keshav Agrawal January 5, 2013 at 11:45 am

It did save my day.. Thanks a lot.. was banging my head whats wrong.. thanks a lot.. this really helped

25 Phanindra Ramesh January 22, 2013 at 1:19 am

Worked for me…. Thank you:-)

26 Cct3000 March 21, 2013 at 6:25 am

Have to edit /etc/mysql/my.cnf
.
.
Bind-address = 127.0.0.1

Change to

Bind-address = 0.0.0.0

27 Steve March 21, 2013 at 12:59 pm

When I got this message, it was because of a typo in one of the /etc/hosts files — I had “.con” instead of “.com” at the end of an entry.

If what’s recommended here doesn’t work for you, check name resolution!

28 Bhupendra April 5, 2013 at 1:27 am

Hi,

Thanks for info , but it’s only working in local machine not on remote machine.
it’s replacing localhost to ip of machine ,
But if i want to database from another machine , is there any way to access that.

29 iitb April 16, 2013 at 2:37 am

thnks ….
it really healped :)

30 Hang Pan May 14, 2013 at 3:31 am

thanks a lot for your help

31 satya May 24, 2013 at 1:28 am

How to Allow MySQL Client to Connect to Remote MySQL server using PHP page. it is connecting using command line i.e. $mysql -h 0.0.0.0 -u xyz -p

Leave a Comment

Previous post:

Next post: