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

fred974

Daemon

Thanks: 34
Messages: 1,486

#1
/------------------------------ 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:
Code:
Database type:	MySQL
Database host:	192.168.0.120
Database port:	 0 	[0 - use default port]
Database name:	 zabbix
User:		         zabbix
password	 ********
I get
Code:
Error connecting to database [Operation timed out]
Could anyone help?
/etc/rc/conf
Code:
### 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
Code:
### 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
Code:
[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
Code:
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
 
OP
OP
fred974

fred974

Daemon

Thanks: 34
Messages: 1,486

#2
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
 
OP
OP
fred974

fred974

Daemon

Thanks: 34
Messages: 1,486

#3
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

Member

Thanks: 5
Messages: 21

#4
Re: Nginx jail not talking to MYsql jail - Help

Remove these lines from my.cnf
Code:
# 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

Administrator
Staff member
Administrator
Moderator

Thanks: 6,332
Messages: 27,502

#5
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).
 
OP
OP
fred974

fred974

Daemon

Thanks: 34
Messages: 1,486

#6
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
Code:
Error connecting to database [Host '192.168.0.115' is not allowed to connect to this MySQL server]
I also had to remove
Code:
bind-address=191.168.0.120
because MySQL won't start..
Code:
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?
 
Last edited by a moderator:

SirDice

Administrator
Staff member
Administrator
Moderator

Thanks: 6,332
Messages: 27,502

#7
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.
 
OP
OP
fred974

fred974

Daemon

Thanks: 34
Messages: 1,486

#8
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
Code:
ListenAddress 192.168.0.115
for DBjail
Code:
ListenAddress 192.168.0.120
for mailjail
Code:
ListenAddress 192.168.0.125
any other way i can find out where port 3306 is used?
 

SirDice

Administrator
Staff member
Administrator
Moderator

Thanks: 6,332
Messages: 27,502

#9
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.
 
OP
OP
fred974

fred974

Daemon

Thanks: 34
Messages: 1,486

#10
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
 
Last edited by a moderator:

SirDice

Administrator
Staff member
Administrator
Moderator

Thanks: 6,332
Messages: 27,502

#11
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

Administrator
Staff member
Administrator
Moderator

Thanks: 6,332
Messages: 27,502

#12
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).
 
Last edited by a moderator:
OP
OP
fred974

fred974

Daemon

Thanks: 34
Messages: 1,486

#13
Re: Nginx jail not talking to MYsql jail - Help

I create the user as follow:
Code:
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

Administrator
Staff member
Administrator
Moderator

Thanks: 6,332
Messages: 27,502

#14
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.
 
Last edited by a moderator:
OP
OP
fred974

fred974

Daemon

Thanks: 34
Messages: 1,486

#15
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 :)
Code:
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
 
Last edited by a moderator:

SirDice

Administrator
Staff member
Administrator
Moderator

Thanks: 6,332
Messages: 27,502

#16
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.
 
OP
OP
fred974

fred974

Daemon

Thanks: 34
Messages: 1,486

#17
Re: Nginx jail not talking to MYsql jail - Help

SirDice said:
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.
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

Administrator
Staff member
Administrator
Moderator

Thanks: 6,332
Messages: 27,502

#18
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

Member

Thanks: 5
Messages: 21

#19
Re: Nginx jail not talking to MYsql jail - Help

fred974 said:
I also had to remove
Code:
bind-address=191.168.0.120
because MySQL won't start..
Code:
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
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.
 
Top