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: