# remote mysql problem



## s904s (Feb 21, 2013)

Hello I've a site on cpanel, I need that it connects remotely to mysql server that's in a FreeBSD server external. In the mysql console of FreeBSD I've added % to let connect from every ip, in fact if I try to connect with navicat from mac or other computer it connects, but when I try to install whmcs or other cms on cpanel trying to connect to the remote mysql FreeBSD server I can't success. It looks like that FreeBSD not allow php connection to mysql. How I can solve this?
Thank you!


----------



## SirDice (Feb 21, 2013)

This has nothing to do with FreeBSD, it's a MySQL privilege issue.

http://dev.mysql.com/doc/refman/5.5/en/grant.html


----------



## s904s (Feb 21, 2013)

But if I connect to an external windows database it works, how can I solve this problem on mysql for FreeBSD?


----------



## CyberBSD (Feb 21, 2013)

Hi,

In MySQL console of your MySQL installed on the FreeBSD box run the following queries:

```
mysql> grant all privileges on db_name.* to [email]user@hostname.com[/email] identified by 'PASSWORD';
mysql> grant all privileges on db_name.* to [email]user@xxx.xxx.xxx.xxx[/email] identified by 'PASSWORD';
mysql> flush privileges;
```
Please note "hostname.com" must be replaced with the Reverse DNS record of the main IP address your cPanel server. xxx.xxx.xxx.xxx must be replaced with the main IP address of your cPanel server. Additionally make sure the sites located on the cPanel server use the correct MySQL host (FQDN of your FreeBSD server or IP address).


----------



## s904s (Feb 21, 2013)

I've done like you say it still says this error : 

```
WHMCS - The Complete Client Management, Billing & Support Solution

Could not connect to the database - check the database connection details you entered and go back and correct them if necessary
```


----------



## J65nko (Feb 21, 2013)

Have you confirmed that MySQL has been configured to listen on an external IP address?


```
[cmd=$]netstat -an -f inet | grep \.3306[/cmd]
tcp4       0      0  *.3306                 *.*                    LISTEN
```
and not retricted to the loopback address like:

```
[cmd=$]netstat -an -f inet | grep \.3306[/cmd]
tcp          0      0  127.0.0.1.3306         *.*                    LISTEN
```

If you are you running a packet filter or firewall on your server, does it allow connections to the MySQL port 3306?

If you run `$ sudo tcpdump -tttt -ni em0 'port 3306'` does it show that a TCP connection to your MySQL server is being established?


----------



## CyberBSD (Feb 22, 2013)

s904s, run:

[CMD=]telnet freebsd.hostname.com 3306[/CMD]

in terminal of the cPanel server and see if it shows you something like:


```
Trying xxx.xxx.xxx.xxx...
Connected to freebsd.hostname.com.
Escape character is '^]'.
>
5.1.62-community
```

The result provided above demonstrates that the port 3306 (standard MySQL port) isn't blocked on the source and destination servers and something, probably MySQL, listens the port 3306 on the destination host.

If you get something like:


```
Trying xxx.xxx.xxx.xxx...
telnet: connect to address xxx.xxx.xxx.xxx: Connection refused
telnet: Unable to connect to remote host
```

It means the port 3306 is blocked/filtered on the source/destination host OR MySQL installed on your FreeBSD box doesn't listen the port 3306 at all. It could happen if you configured MySQL to listen non standard port, configured MySQL to listed a dedicated IP address or disabled "networking" in MySQL settings at all.

Additionally, I recommend you to do following:

Try to establish MySQL session from your cPanel to the FreeBSD box manually. Just run the command:

[CMD=]mysql -uDB_USERNAME -pDB_PASSWORD -hDB_HOSTNAME DB_NAME[/CMD]

in terminal of the cPanel box and check if the session is established or not. At least the error received by you in the terminal will be more informative than the error showed by WHMCS.


----------



## s904s (Feb 23, 2013)

J65nko said:
			
		

> Have you confirmed that MySQL has been configured to listen on an external IP address?
> 
> 
> ```
> ...



The output of this commands also when i try to connect to mysql is this :

```
root@artaxworld:/root # netstat -an -f inet | grep \.3306
tcp4       0      0 *.3306                 *.*                    LISTEN
root@artaxworld:/root # tcpdump -tttt -ni em0 'port 3306'
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on em0, link-type EN10MB (Ethernet), capture size 65535 bytes
^C
0 packets captured
15 packets received by filter
0 packets dropped by kernel
```

For the CyberBSD advices i can't use the terminal of cpanel server because it's not mine it's of an hoster , I've bought a hosting


----------



## J65nko (Feb 23, 2013)

```
[cmd=root@artaxworld:/root #] netstat -an -f inet | grep \.3306[/cmd]
tcp4       0      0 *.3306                 *.*                    LISTEN
```
Assuming you ran this on the FreeBSD MySQL server, thi
s shows that MySQL can accept connections on all interfaces.

Can you connect to the FreeBSD MySQL server from your laptop/desktop at home/work?
This way you verify if external parties can connect to MySQL.

Do you have SSH access to your webserver?

By the way, if you run MySQL on a publicly accessible IP, you will get visits from uninvited quests probing your MySQL port:

```
02-19 04:32:37.852422 IP 115.47.9.237.6000 > p.q.r.s.3306: S 924975104:924975104(0) win 16384
02-19 06:53:24.657733 IP 115.47.9.237.6000 > p.q.r.s.3306: S 924975104:924975104(0) win 16384
02-19 06:56:08.396698 IP 222.187.96.83.6000 > p.q.r.s.3306: S 1241448448:1241448448(0) win 16384
02-19 08:52:30.468880 IP 115.47.9.237.6000 > p.q.r.s.3306: S 924975104:924975104(0) win 16384
02-19 10:47:05.667603 IP 115.47.9.237.6000 > p.q.r.s.3306: S 924975104:924975104(0) win 16384
02-19 12:43:13.732919 IP 115.47.9.237.6000 > p.q.r.s.3306: S 924975104:924975104(0) win 16384
02-19 14:42:52.571664 IP 115.47.9.237.6000 > p.q.r.s.3306: S 924975104:924975104(0) win 16384
02-19 16:38:45.653306 IP 115.47.9.237.6000 > p.q.r.s.3306: S 924975104:924975104(0) win 16384
02-19 18:26:34.053255 IP 115.47.9.237.6000 > p.q.r.s.3306: S 924975104:924975104(0) win 16384
02-19 20:26:30.084184 IP 115.47.9.237.6000 > p.q.r.s.3306: S 924975104:924975104(0) win 16384
02-20 03:44:53.214753 IP 219.153.64.207.6000 > p.q.r.s.3306: S 924975104:924975104(0) win 16384
02-20 06:22:33.851490 IP 95.254.163.37.53454 > p.q.r.s.3306: S 782488586:782488586(0) win 8192
                      <mss 1452,nop,wscale 8,nop,nop,sackOK>
02-20 06:22:34.843320 IP 95.254.163.37.54247 > p.q.r.s.3306: S 4286098384:4286098384(0) win 8192
                      <mss 1452,nop,wscale 8,nop,nop,sackOK>
02-20 09:45:04.311871 IP 110.200.16.32.1951 > p.q.r.s.3306: S 426704896:426704896(0) win 16384
02-20 12:36:49.479664 IP 92.48.122.218.35310 > p.q.r.s.3306: S 155210381:155210381(0) win 65535
                      <mss 1460,nop,nop,sackOK>
02-21 09:04:45.829425 IP 27.50.139.171.6000 > p.q.r.s.3306: S 924975104:924975104(0) win 16384
02-21 22:42:34.639789 IP 124.90.134.15.29504 > p.q.r.s.3306: S 0:0(0) win 16384
                      <mss 1452,nop,nop,sackOK>
02-22 00:36:16.859523 IP 223.4.35.233.6000 > p.q.r.s.3306: S 60293120:60293120(0) win 16384
```
This is from a server, protected with pf blocking incoming connections from external parties.


----------



## s904s (Feb 23, 2013)

I've tryed from a lot of pc with navicat it works to connect to freebsd mysql server but  if i try to use whmcs to install it on the server it don't connect . If i try from the webserver to install in a mysql windows server it works only on freebsd.


----------



## J65nko (Feb 25, 2013)

If you can make remote connections to your FreeBSD MySQL box, on other computers with Navicat but not with cPanel or http://en.wikipedia.org/wiki/WHMCS]WHCMS[/url] then the problem is the cPanel or WHCMS configuration/installation.

Because you have to pay your hosting company for cPanel/WHCMS, they should be the one to help you to solve this


----------

