≡ Menu

How to Allow MySQL Client to Connect to Remote MySQL server

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.

Add your comment

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

Comments on this entry are closed.

  • Aravind July 7, 2011, 6:16 am

    It worked. Thanks a lot!

  • Prasanth Varghese October 18, 2011, 10:51 am

    Worked 4 me.Thanx

  • GiangNT October 19, 2011, 8:43 am

    Thank you very much! 🙂

  • balajisk December 5, 2011, 6:43 am

    worked for me . Thanks a lot

  • Caroline January 10, 2012, 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

  • Anonymous March 6, 2012, 3:48 pm

    Thanks it worked

  • Tim Carlson April 27, 2012, 2:42 pm

    This just saved my sanity!!!! Thanks!

  • zoran May 6, 2012, 5:17 am

    Thanks a lot, this worked very well for me!

  • Anonymous May 8, 2012, 11:44 pm

    Thanks works like charm on MySQL5.1

  • Andy82 May 17, 2012, 12:13 pm

    Thanks for help …. it works perfect

  • ade July 19, 2012, 3:40 am

    why that does not work for me?

    how to update filewall rule?

  • Anonymous July 21, 2012, 10:11 am

    Thanku very muchhhhhhhhhh

  • Thabca Nil Mishra July 30, 2012, 1:12 am

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

  • vijay August 6, 2012, 12:19 am

    worked for me, thanks a ton

  • Sudarson August 17, 2012, 4:32 am

    Worked for myself……..

  • Ethan August 20, 2012, 4:53 am

    It is instructive. I recommand this to these use MySql

  • Jerry August 20, 2012, 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’;

  • pouya September 19, 2012, 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.

  • Anwar Yagoub October 22, 2012, 2:23 am

    Works like charm thanks

  • PPmarcel October 26, 2012, 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).

  • Mann November 1, 2012, 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?

  • prasanth M N December 17, 2012, 11:31 pm

    worked
    thanks

  • Sushil December 31, 2012, 1:41 am

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

  • Keshav Agrawal January 5, 2013, 11:45 am

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

  • Phanindra Ramesh January 22, 2013, 1:19 am

    Worked for me…. Thank you:-)

  • Cct3000 March 21, 2013, 6:25 am

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

    Change to

    Bind-address = 0.0.0.0

  • Steve March 21, 2013, 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!

  • Bhupendra April 5, 2013, 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.

  • iitb April 16, 2013, 2:37 am

    thnks ….
    it really healped 🙂

  • Hang Pan May 14, 2013, 3:31 am

    thanks a lot for your help

  • satya May 24, 2013, 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

  • Hamed Kamrava June 10, 2013, 12:12 am

    Can we write `%` instead client-ip-address to allow clients?

  • Mau June 16, 2013, 7:37 pm

    Thanks for this great info. I would like to add my two cents here since I had a problem with the password hash (I was using MySql Workbench instead of command line).

    You need to query this first:
    select password(‘you_password_goes_here’);

    The result would be something like this

    *502ADA1CCA0BC291A468FBDDA49C46BBC4AE7A2B

    So just change this line to

    IDENTIFIED BY PASSWORD ‘*502ADA1CCA0BC291A468FBDDA49C46BBC4AE7A2B’

    With the value we got above.

    Thanks again. Cheers!

  • cruz July 23, 2013, 10:32 pm

    Thanks guys…

  • Anonymous September 2, 2013, 1:25 am

    thanks a lot.. meaningful explanation

  • Ry September 11, 2013, 8:53 pm

    Thanks. worked for me

  • OUCHEN October 23, 2013, 5:23 am

    merci Mr Jerry

  • Dave November 24, 2013, 8:22 pm

    This post is still helping folks. Great test and resolution.

    Thanks

  • Jochen March 18, 2014, 4:39 am

    Awesome, thanks!

  • NoobUser April 22, 2014, 3:24 am

    thanks, exactly what I needed 😀

  • Marcelo Lopes May 23, 2014, 10:25 am

    Thanks!!!!

  • Java Developer May 29, 2014, 10:51 pm

    hi guys….can u explain me how to connect mysql server from REMOTE CLIENT via internet network ??????????? Means can we be able to connect mysql server thourgh internet as like above explained process ?? Here , only change is Client is Remote machine insteed of that is in same LAN network . Please help me..Thanks a lot..

  • kokhui October 20, 2014, 12:10 pm

    Thanks a lot Man. It works!!!

  • Manis November 20, 2014, 8:46 am

    Hello!,
    I have a mysql server 5.5 installed in IP address 192.168.0.250 and I want my children
    to access their mysql database through their independent user.
    How could i achieve ?
    Awaiting your prompt response.
    Thanks
    Manish Gupta

  • Onyy January 9, 2015, 4:05 am

    thank you very much, works for me
    if you need to connect with phpmyadmin to your remote database just
    #vi /usr/share/phpmyadmin/config.inc.php
    and search for $cfg[‘Servers’][$i][‘host’] = ‘localhost’; and replace localhost with your remote server IP to be like that
    $cfg[‘Servers’][$i][‘host’] = ‘192.168.1.9’;

  • Rizvi March 30, 2015, 2:53 am

    Simple and clear,,,, thanks

  • Sang June 18, 2015, 3:40 am

    It saved my day. Thanks a lot.

  • Sakthivel July 1, 2015, 7:51 am

    It worked for me.. Thanks a lot

  • godwin thaya August 26, 2015, 8:49 am

    Worked for myself

  • Carlos October 12, 2015, 4:37 pm

    Thanks a lot. Finally it worked!

  • Bob December 3, 2015, 5:17 am

    Awesome…it helped me.. Thank You

  • Mike December 16, 2015, 7:47 am

    Thank you so much

  • Nitish Moleshwari February 24, 2016, 10:39 pm

    How can we check the PRIVILEGES are properly set or not ?
    Because after following your steps i am still getting that error.As my remote machine is connect in LAN to mysql server which is not having Firewall in between them.

  • Moises Jafet March 28, 2016, 5:30 pm

    Thanks!

  • polo April 25, 2017, 4:28 am

    thanks !!!