# [RESOLVED] Nginx jail not talking to MYsql jail - Help



## fred974 (Feb 25, 2014)

/------------------------------ My host ------------------------------\
---{ internet } { 82.36.74.191-EstFixIP } --- [ 192.168.0.1-local gateway ] --- [ 192.168.0.198-FreeBSD Host ] --- jails --- [ 192.168.0.115 ] 	webjail
.........................................................................................................................................|---[ 192.168.0.120 ]	DBjail
.........................................................................................................................................|--- [ 192.168.0.125 ]	mailjail

Hello everyone,

I have a problem where when trying to install wordpress or Zabbix on the webserver jail, I cannot establish a connection to the Database..
Here his an example for zabbix:

```
Database type:	MySQL
Database host:	192.168.0.120
Database port:	 0 	[0 - use default port]
Database name:	 zabbix
User:		         zabbix
password	 ********
```
I get 
	
	



```
Error connecting to database [Operation timed out]
```
Could anyone help?
/etc/rc/conf

```
### Network configuration
ifconfig_bge0="inet 192.168.0.198 netmask 255.255.255.0"                                 
defaultrouter="192.168.0.1"                                                                                                                      
### internal network configuration Jail interface aliases                          
ifconfig_bge0_alias0="inet 192.168.0.115 netmask 255.255.255.0" # webjai
ifconfig_bge0_alias1="inet 192.168.0.120 netmask 255.255.255.0" # sqljail
ifconfig_bge0_alias2="inet 192.168.0.125 netmask 255.255.255.0" # mailjail
```
/etc/pf.conf

```
### jail IPs
webjail   ="192.168.0.115"
sqljail   ="192.168.0.120"
mailjail ="192.168.0.125"

### jail Ports
webports ="{ http,https,82 }"
sqlports ="{ 3306 }"
mailports ="{ 25 }"

### nat all jail traffis
nat on $ext_if from 192.168.0.0/24 to any -> ($ext_if)

### Translation
rdr on $ext_if proto tcp from any to $ext_if port $webports -> $webjail
rdr on $ext_if proto tcp from any to $ext_if port $sqlports -> $sqljail
rdr on $ext_if proto tcp from any to $ext_if port $mailports -> $mailjail
... More rule bellow
```
/usr/local/my.cnf

```
[mysqld]
innodb_file_per_table
port = 3306
bind-address=191.168.0.120
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# Setting for utf8 encoding
character-set-server=utf8 # UTF-8
collation-server=utf8_general_ci # UTF-8
skip-character-set-client-handshake # UTF-8

#### MySQL Hardening ####

# Disable symlinks from database
skip-symbolic-links
# Disable unauthorized reading from local files
local-infile=0
# Enable logs in mysql server
log-error = /var/db/mysql/mysqld.log
slow-query-log-file = /var/db/mysql/mysql-slow.log
long_query_time = 5
# Disable TCP networking
skip-networking
# Set non default port
#port = 2913
# Stop database information gathering
skip-show-database
```
/var/db/mysql/mysqld.log

```
2014-02-25 12:41:09 3392 [Note] Plugin 'FEDERATED' is disabled.
2014-02-25 12:41:09 3392 [Note] InnoDB: The InnoDB memory heap is disabled
2014-02-25 12:41:09 3392 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-02-25 12:41:09 3392 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-02-25 12:41:09 3392 [Note] InnoDB: Not using CPU crc32 instructions
2014-02-25 12:41:09 3392 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-02-25 12:41:09 3392 [Note] InnoDB: Completed initialization of buffer pool
2014-02-25 12:41:09 3392 [Note] InnoDB: Highest supported file format is Barracuda.
2014-02-25 12:41:09 3392 [Note] InnoDB: 128 rollback segment(s) are active.
2014-02-25 12:41:09 3392 [Note] InnoDB: Waiting for purge to start
2014-02-25 12:41:09 3392 [Note] InnoDB: 5.6.15 started; log sequence number 13980849
2014-02-25 12:41:09 3392 [Note] Event Scheduler: Loaded 0 events
2014-02-25 12:41:09 3392 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.6.15'  socket: '/tmp/mysql.sock'  port: 0  Source distribution
```
Thank you in advance


----------



## fred974 (Feb 25, 2014)

*Re: Nginx jail not talking to MYsql jail - Help*

Please anybody this is that last thing stopping my setup from working.
I think that PF is not redirecting the database connection properly


----------



## fred974 (Feb 25, 2014)

*Re: Nginx jail not talking to MYsql jail - Help*

Ok, I've been looking at the problem all day without any success
Let's hope I might have some pointer tomorrow


----------



## anlashok (Feb 25, 2014)

*Re: Nginx jail not talking to MYsql jail - Help*

Remove these lines from my.cnf

```
# Disable TCP networking
skip-networking
```
then restart mysql `service mysql-server restart`

The config line skip-networking means that mysql-server is not listening for network traffic at all, i.e. its only accepting connections through the local unix socket, so when you try to connect across the network, as its not listening, your connection times out as in the error message you shared.

There may be other issues in your set up, but at least this means that mysql-server will be listening for network connections because its not currently...


----------



## SirDice (Feb 26, 2014)

*Re: Nginx jail not talking to MYsql jail - Help*

The redirections in pf.conf are unnecessary. They don't work anyway (you cannot redirect traffic on the same interface, it must traverse interfaces).


----------



## fred974 (Feb 26, 2014)

*Re: Nginx jail not talking to MYsql jail - Help*

@junovitch thank you very much for your reply.
In my/usr/local/my.cnf, I have removed the entire #### MySQL Hardening #### section.
Now I have another error message as you predicted

```
Error connecting to database [Host '192.168.0.115' is not allowed to connect to this MySQL server]
```
I also had to remove 
	
	



```
bind-address=191.168.0.120
```
 because MySQL won't start..

```
2014-02-26 09:07:17 3506 [Note] Server hostname (bind-address): '191.168.0.120'; port: 3306
2014-02-26 09:07:17 3506 [Note]   - '191.168.0.120' resolves to '191.168.0.120';
2014-02-26 09:07:17 3506 [Note] Server socket created on IP: '191.168.0.120'.
2014-02-26 09:07:17 3506 [ERROR] Can't start server: Bind on TCP/IP port: Can't assign requested address
2014-02-26 09:07:17 3506 [ERROR] Do you already have another mysqld server running on port: 3306 ?
2014-02-26 09:07:17 3506 [ERROR] Aborting
```

Just one note..I don't have a domain controller and therefore cannot resolve by host name (ip only)..could that be the reason why?


----------



## SirDice (Feb 26, 2014)

*Re: Nginx jail not talking to MYsql jail - Help*

Do you have another MySQL running? Perhaps on the host or on one of the other jails? Make sure they bind to a specific address only. Or else it's going to listen on _all_ IP addresses, including those of the host and other jails.


----------



## fred974 (Feb 26, 2014)

*Re: Nginx jail not talking to MYsql jail - Help*

No I only have MySQL on the database jail server.
I have ran `ps -ef | grep 3306` on all jails and host and none of the environment had the port open
all my jails and host has their own IP addresses in/sshd_config
for webjail

```
ListenAddress 192.168.0.115
```
for DBjail

```
ListenAddress 192.168.0.120
```
for mailjail

```
ListenAddress 192.168.0.125
```

any other way i can find out where port 3306 is used?


----------



## SirDice (Feb 26, 2014)

*Re: Nginx jail not talking to MYsql jail - Help*

Perhaps you tried to restart it too quickly and the old process hadn't released the port yet. I get that sometimes too.


----------



## fred974 (Feb 26, 2014)

*Re: Nginx jail not talking to MYsql jail - Help*



			
				SirDice said:
			
		

> Perhaps you tried to restart it too quickly and the old process hadn't released the port yet. I get that sometimes too.


I still get the message on server reboot.. so I don't think its that.
But for security I know that jails cannot see each others, so how can my webjail communicate with my DBjail annd still be secure?
Is that the reason why people use lo1 interface for?

@SirDice, would you mind sharing your my.cnf file so I can have a look at what a proper configured file look like please?
https://www.dropbox.com/s/mffsmlrhra8akt6/zabbix.PNG


----------



## SirDice (Feb 26, 2014)

*Re: Nginx jail not talking to MYsql jail - Help*



			
				fred974 said:
			
		

> But for security I know that jails cannot see each others, so how can my webjail communicate with my DBjail annd still be secure?


You can still access each other over the network.



> Is that the reason why people use lo1 interface for?
> https://www.dropbox.com/s/mffsmlrhra8akt6/zabbix.PNG


No, that's mainly used if you only have one (external) IP address.


----------



## SirDice (Feb 26, 2014)

*Re: Nginx jail not talking to MYsql jail - Help*

The error looks like your @zabbix user isn't allowed to login from the network. Make sure the account is set up properly in MySQL. Most examples I've seen used "zabbix@localhost" meaning @zabbix is only allowed to login from localhost. In your case you'd need something like "zabbix@192.168.0.115" (assuming 192.168.0.115 is the IP address of the web frontend).


----------



## fred974 (Feb 26, 2014)

*Re: Nginx jail not talking to MYsql jail - Help*

I create the user as follow:

```
grant all privileges on zabbix.* to zabbix@192.168.0.120 identified by 'MyPassword'
```
I did @192.168.0.120 because that is the is IP of the database jail..
I didn't released I need to do @192.168.0.115
I'll try that and let you know


----------



## SirDice (Feb 26, 2014)

*Re: Nginx jail not talking to MYsql jail - Help*

Keep in mind that the zabbix-server component also needs access to that account. So you may need to add another IP address. You can use the wildcard '%' instead of an IP address but do realize that it also allows every other host on your network to login as the @zabbix user (provided they know the password). Zabbix agents do not need to have access to the MySQL database. They only need to be able to connect to the server component.


----------



## fred974 (Feb 26, 2014)

*Re: Nginx jail not talking to MYsql jail - Help*



			
				SirDice said:
			
		

> Keep in mind that the zabbix-server component also needs access to that account.


Are you referring to the zabbix_server.conf file?
I have changed my DB user and I have finally managed to establish a DB connection 

```
grant all privileges on zabbix.* to zabbix@192.168.0.120 identified by 'MyPassword'
```
Just for my own learning, could I see your my.cnf as I feel like mine is really light
Thank you so much @SirDice  :beergrin


----------



## SirDice (Feb 26, 2014)

*Re: Nginx jail not talking to MYsql jail - Help*



			
				fred974 said:
			
		

> Just for my own learning, could I see your my.cnf as I feel like mine is really light


I don't have one and I can't access the one at work right now. Best thing is to run it for a while without one, then run databases/tuning-primer. That should give you some base values to tweak to get the most out of MySQL.


----------



## fred974 (Feb 26, 2014)

*Re: Nginx jail not talking to MYsql jail - Help*



			
				SirDice said:
			
		

> fred974 said:
> 
> 
> 
> ...


Great! I'm glad this problem is now solved;It has been quite a learning curve  :stud  I would have had the same problem wit my wordpressinstallation


----------



## SirDice (Feb 26, 2014)

*Re: Nginx jail not talking to MYsql jail - Help*

Just keep in mind that accounts on MySQL can be limited to access from specific IP addresses or hostnames. That's usually the reason why logins fail. You can test the account on the command line simply by logging in with the mysql(1) command: `mysql -h mysql-server -p -u myuser`. You should be able to login with the credentials you created.


----------



## anlashok (Feb 26, 2014)

*Re: Nginx jail not talking to MYsql jail - Help*



			
				fred974 said:
			
		

> I also had to remove
> 
> 
> 
> ...


The above bind-address was 191.168.0.120 whereas all the other data you presented was 192.168.0.etc., so the Bind error was occurring because the IP address was in a different IP block which you don't control so it couldn't assign it.


----------



## fred974 (Feb 26, 2014)

Well spotted @anlashok!
Thank you very much. Changed it to 192.168.0.120 and all working now


----------

