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 [email protected] 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?
sandbox.abc.com
instead of IP address in yourGRANT
command.'user'@'%'
. If works -- play around (check the logs etc). depending on your setup/circumstances you may consider restricting access from specific IP not in MySQL but at firewall.skip-name-resolve
in your config (so only IPs are used and not domain names): dev.mysql.com/doc/refman/5.5/en/…