Successful Install of Mysql in Jail on Freenas 11.2

[chravis]

Hi everyone, I had a desire to install Mysql in a jail on my Freenas 11.2 server. I found many, many tutorials and discussions. Some were old and didn't seem to apply. Some seemed newer, but when followed resulted in errors that I could not explain. I did end up getting something to work using a combination of various posts I found. I am posting this here to hopefully help others in the same situation, but to also ask if there are steps I missed, or shouldn't have done, or could have done a different way. I hope this is beneficial to others. And for the record, I set up my Freenas server mainly as a file backup and Plex server. After I got everything working (thanks to these forums) I pretty much forgot about it and it just works. I am in no shape or form a server expert when it comes to commands and things so don't be surprised if I've done something silly.

Step 1 - Create a Jail
Use the Freenas GUI to create a new jail. I named mine "mysql".
IPv4 Interface = em0
IPv4 Address = 192.168.1.20 (or whatever makes sense for your setup)
IPv4 Netmask = 24
I did not set a Default Router or any other configs.

Step 2 - Create a dataset and mount point (optional)
This step can be reversed with Step 1
Create a dataset in your pool to house the mysql data if you like. I guess this step could be optional.
Go to your jail using the GUI and create a mount point where the source is /mnt/<your pool>/<your dataset name> and the destination is /mnt/<your pool>/iocage/jails/mysql/root/var/db/mysql. Note that /var/db/mysql probably won't exist, so just type it in.

I found one tutorial where three datasets were created and mounted, innodb_data, innodb_log, and datadir. When following those instructions (https://devpro.media/mariadb-server-freenas/#creating-zfs-datasets) I could not ever get the mysql server to start and I couldn't figure out why.

Step 3 - Create a group and user
Using the gui, I used the shell page to jump into the new jail:

Code:

iocage console mysql

Create user-group mysql:

Code:

pw group add -n mysql

Create user and add it to the mysql group:

Code:

pw user add -n mysql -g mysql -s /usr/local/bin/bash

Change the mounted directory ownership so that mysql could write in this directory:

Code:

chown -R mysql:mysql /var/db/mysql/

Step 4 - Install Mysql Server

Code:

pkg install mysql57-server (or whatever version you need)
sysrc mysql_enable=yes
service mysql-server start
mysql_secure_installation (follow prompts for this)

Step 5 - Allow access to server from outside the jail
I wanted to be able to use tools on my Windows machine to administer the database, such as MySQL Workbench. But I also didn't want to expose my root user (and hopefully I haven't). So, for my purposes I just have one particular database that I care about and I'm going to create a super user just for that database.

Code:

mysql --user=root --password
mysql> CREATE database my_database;
mysql> CREATE USER 'my_db_admin'@'localhost' IDENTIFIED BY '<password>';
mysql> GRANT ALL PRIVILEGES ON my_database.* TO 'my_db_admin'@'localhost';
mysql> CREATE USER 'my_db_admin'@'%' IDENTIFIED BY '<password>';
mysql> GRANT ALL PRIVILEGES ON my_database.* TO 'my_db_admin'@'%';

After doing the above, I was still not able to connect using MySQL Workbench. So I did the following two things. Unfortunately I don't know which one solved the problem or if it took both (or maybe neither!)

Code:

mysql> flush privileges;

Then I also edited the my.cnf file. I had trouble finding this, but eventually found one in /usr/local/etc/mysql, so I did the following:

Code:

nano /usr/local/etc/mysql/my.cnf

and commented out the bind-address line. I am not sure if that's right or not. One website I found said that you needed a proper value there in order to connect from outside, but another said you needed to comment it out. Commenting it out worked for me I think.

I am now able to connect to a database running on my Freenas server using MySQL Workbench on a different computer.

 

[cdoucet]

Thank you!