MySQL – How to Allow Connections from a Remote Server in MySQL

MySQLremote-access

I googled this and found the answers but even after following all the steps that should have worked I still cannot connect to my SQL server from another server.

this is the my.cnf, the only change i did here was ADD the bind parameter as it was missing from the start

[mysqld]
query-cache-type = 1
query-cache-size = 8M

set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

bind=205.xxx.xxx.xx # the IP of the server that mySQL is located on as well as this my.cnf

# To enable the InnoDB Plugin, uncomment the 2 next lines
#ignore-builtin-innodb
#plugin-load=innodb=ha_innodb_plugin.so

# To enable InnoDB-related INFORMATION_SCHEMA tables
# Join the following options to above directive
  ;innodb_trx=ha_innodb_plugin.so
  ;innodb_locks=ha_innodb_plugin.so
  ;innodb_cmp=ha_innodb_plugin.so
  ;innodb_cmp_reset=ha_innodb_plugin.so
  ;innodb_cmpmem=ha_innodb_plugin.so
  ;innodb_cmpmem_reset=ha_innodb_plugin.so

set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2

Then I did a reboot of mysql server with

/etc/init.d/mysqld restart

then I logged into the mysql server as root

then I did a grant all privileges

mysql> GRANT ALL PRIVILEGES ON *.* TO user@50.xx.xx.xx IDENTIFIED BY "pass";

To my understanding user and pass are the login details for the existing database on the server right? Or do they mean something else? Also the ip of 50.xx.xx.xx is the ip of the remote server that is going to try and make the connection.

Am I wrong in this statement?

after which i flushed all privileges

mysql> FLUSH PRIVILEGES;

then in my PHP scripts on the remote server I just filled in the details for the sql database I want to connect to.

$database = demo_;
$dbpass = 'user';
$dbpass = 'pass';

I used the same user and pass that i specified in the GRANT ALL PRIVILEGES just to make sure things matched.

but in the end i still get this error

SQLSTATE[HY000] [1130] Host 'sandbox.abc.com' is not allowed to connect to this MySQL server

any ideas?

Best Answer

Assuming the permissions are as you say, it should work - however its worth noting that GRANT provides an interface to the underlying access control tables in MySQL - and for a long time it was standard practice to write to these directly; have you checked what is in the mysql.user tables?

Have you checked that the client address seen by the server is the same as you specified in the grant? i.e. 50.xx.xx.xx is the only address for sandbox.abc.com

Also, there is note in the mysql manual regarding this error:

On Linux, another reason that this error might occur is that you are using a binary MySQL version that is compiled with a different version of the glibc library than the one you are using