SOLVED Trouble setting up MySQL in a jail

[DGenerateKane]

I've had a mysql 5.5 server installed in a jail for over three years now for my Kodi installs, but my machine is running 11.1-U7 so it's in a Warden jail. Before breaking my Kodi installs I wanted to make sure I could properly install mysql 5.7 in an iocage jail, so I updated my other machine to 11.2-U8 and attempted installing mysql there. When I installed the original mysql server, I followed the guide here https://kodi.wiki/view/MySQL/Setting_up_MySQL#tab=FreeNAS_and_freeBSD but that is horribly outdated and isn't possible to follow precisely. I've searched a lot and finally got it installed and running mostly, but the last set of steps on the Kodi wiki are where I'm stuck. When I enter the command:

CREATE USER 'kodi' IDENTIFIED BY 'kodi';

I get the error:

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

After more searching I tried this command:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

Even though I had previously set the policy to the lowest, I had to change my password to a mix of upper and lowercase letters, numbers and symbols before it would work. However when I tried the CREATE USER command again, I get the exact same error message, so I'm now at a loss on how to proceed. What am I missing?

 

[Alecmascot]

I made these brief notes when installing mysql :

get the first use password from $HOME/.mysql_secret
type in

mysql -u root -p

use the first use password to login

then you must set a root password :
set password = password("New_Password");

Since then I have moved to mariadb 10.4 which seems to outperform MySQL as a Kodi database server.

 

[DGenerateKane]

Now when I try the CREATE USER command it says my password doesn't satisfy the current policy requirements, which is not true at all. I'm fine with going with Mariadb instead of mysql, but I'm also struggling to find up to date instructions for installing it. I just found some through a link in a post here, hopefully they will work. https://devpro.media/mariadb-server-freenas/#creating-zfs-datasets There is a lot more instructions than I expected, though it looks like I'll be skipping the last two sections as I don't have a low latency device available and I don't think I need TLS. Do those instructions look accurate to you?

 

[Alecmascot]

thats a total overkill for your purposes.
You do not need to setup external datasets.
Stripping out all the guff :

pkg install mariadb104-server
pkg install nano
(nano is a text editor you will need shortly)
We now need to enable mysql in rc.conf
nano /etc/rc.conf
insert the line below into the rc.conf file
mysql_enable="YES"
save the file and exit (see bottom of nano edit page for keystrokes info)
for remote access set bind-address to 0.0.0.0 in /usr/local/etc/mysql/my.cnf
service mysql-server start
mysql -u root -p (blank password)

You are now in mysql administration

CREATE USER 'kodi' IDENTIFIED BY 'kodi';
GRANT ALL ON *.* TO 'kodi';
flush privileges;
quit;

and restore the database from KODI
Job done.

 

[DGenerateKane]

After entering mysql -u root -p I get the error

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (2)

mysql.sock doesn't appear to exist. I also notice when running service mysql-server status it says it isn't running, even after entering service mysql-server start again. I have no idea what to do now. Ugh. Why can't this be easier.

 

[Alecmascot]

mysql.sock is only created when mysql starts.
Have you done any customisation ?
If so roll it out and see if mysql starts.
if not then remove it all, pkg remove mariadb104-client.
If you do "client" then it removes both client and server.
Then reinstall and without any changes, except rc.conf see if it starts.

 

[Basil Hendroff]

After entering mysql -u root -p I get the error

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (2)

mysql.sock doesn't appear to exist. I also notice when running service mysql-server status it says it isn't running, even after entering service mysql-server start again. I have no idea what to do now. Ugh. Why can't this be easier.

Possibly a permissions issue. User mysql requires write access to /var/run/mysql. Check the ownership of this directory stat /var/run/mysql. If the owner:group is root:wheel change this to mysql:mysql chown mysql:mysql /var/run/mysql. Start mysql service mysql start. Check that the service is now running service mysql status.

 

[DGenerateKane]

No idea why it wouldn't start, but removing it and installing it again worked, it started just fine the first try. I've also imported my Kodi Library to it successfully. Thanks for the help.

 

[skymoo]

I've been trying to do this very thing, I've installed mariadb105-server in a jail and all the commands mentioned above, but I can only access the server from the jail and not from another machine on my local network. Is there another step to get the mariadb server to accept remote connections? Besides setting bind-address to 0.0.0.0?

 

[Alecmascot]

Did you change the bind address here :
mariadb config file: /usr/local/etc/mysql/conf.d/server.cnf

 

[skymoo]

Did you change the bind address here :
mariadb config file: /usr/local/etc/mysql/conf.d/server.cnf

Yes, then I restarted the server with:

Code:

/usr/local/etc/rc.d/mysql-server stop
/usr/loca/etc/rc.d/mysql-server start

and still the server only appears to be listening from localhost.

 

[Alecmascot]

are you sure that mariadb is running ?

 

[skymoo]

It was running, I was able to connect to the server from localhost just not any other machine. I finally managed to get i working, not sure how but it's working now.