Managing a MySQL database within a Jail

[Louis2]

Hi,

A lot of jails / plugins are using a MySQL DB. Starting with a simple one like famp.

Of course, if you are a command line fan, you can manage that database via 'the command line' . However I am not. I prefer a GUI and in this case preferable the MySQL-workbench.

On a GUI based system, you can run that application, however you can also run the MySQL-workbench on e.g. an PC and manage a remote MySQL-instances on servers. That is what I use to do.

My question is ..... how do I connect an MySQL instance running in a jail from the MySQL-workbench running on some remote machine (my windows PC )

 

[sretalla]

Do you want to expose the jail DB port to the outside network (or would you rather keep that within the jail only... probably the right thing to recommend for security).

you could install the PHPMyAdmin tool in that jail and connect to that.

As far as I can see, there's no port for FreeBSD of a recent version of MySQL-Workbench... the last version seems to be from 2010, so probably not working, so using X11 to access it as a GUI app directly in the jail is out of the question as far as I can see.

Maybe mysqlviz would serve the purpose (its a CLI app that you could install in the jail).

 

[Louis2]

No, I am not going to expose mysql to the internet. However:
- I could manage the MySQL instance from the advanced MySQL-Workbench from my PC via my own local network probably IPV4-only. Futher on I could only permit one specific external adress (my PC) and next to a password I could also use a key. And I can only enable the interface in the jail at moments that I need it. So I do not regard that as a security risk
- by the way putting a database in a separate jail, separated from the web gui, is more secure that having front application and DB on the same machine / in the same jail

Problem is that I do not know how to set-up a connection between the DB in the jail and the Workbench somewhere else. Normally the procedure is:
- configure MySQL to listen to port 3306 on a certain IP for a given range of remote IP-addresses
- open the firewall of the MySQL host machine to allow port 3306 for MySQL
- allow the route between the server and the PC for port 3306
- allow the connection on the local port on the PC

In case of a jail, I am sure you should tell the jail to listen to the address you would like to use for mysql. But I do not know which additional actions are necessary. It is probably not very complex, but I hope somebody allready did that / knows exactly what to do

 

[Louis2]

Hi,

It was not too complicated. You have to take the following steps (you will need mysql 8013 or higher, which should not be a problem):
- add an extra IPV4-address (what is just a local address = more secure). Do not use the same address as you use for normal jail access (security)
- cd /usr/local/etc/mysql
- backup my.cnf => cp my.cnf my.cnf.org
- nano my.cnf
- add an extra bind-address => bind-address = 127.0.0.1,<the extra ip>
- login to mysql as root => mysql -u root -p
# enter password
- create user with "mysql root autorisation CREATE USER 'name_remote_user'@'ip-your-management-pc' IDENTIFIED BY '<somepassword>';
- GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'name_remote_user'@'ip-your-management-pc' WITH GRANT OPTION;
- quit

Now you should be capable to access mysql using a remote management application running from the given ip. Of course assuming your firewall does permit the connection (default is port 3306).

Success

 

[Jailer]

Curious, what exactly are you managing on the database that would require a remote connection that can't be done at the shell by command prompt?

 

[Patrick M. Hausen]

My question is ..... how do I connect an MySQL instance running in a jail from the MySQL-workbench running on some remote machine (my windows PC )

MySQL workbench supports tunneling MySQL over SSH. So create an interactive shell user in your jail with an SSH key, enable sshd in the jail, use that SSH login in MySQL workbench and 127.0.0.1 as the DB server ...