MariaDB secure installation Error 2002 after modifying /usr/local/etc/mysql/my.cnf

[paul78au]

Hi Samuel

This is a new installation on Freenas 11.3-U3.2 I have followed your guide to set up datasets and permissions. As I have only 1 pool the apps dataset and iocage dataset are under the main dataset vault. I have included commands from adding storage to the iocage jail.

root@freenas[]# iocage start nextcloud
nextcloud is already running!
root@freenas[]# iocage exec nextcloud mkdir -p /mnt/data
root@freenas[]# iocage exec nextcloud mkdir -p /var/db/mysql
root@freenas[]# iocage exec nextcloud mkdir -p /usr/local/www/nextcloud/config
root@freenas[]# iocage exec nextcloud mkdir -p /usr/local/www/nextcloud/themes
root@freenas[]# iocage fstab -a nextcloud /mnt/vault/cloud /mnt/data nullfs rw 0 0
Successfully added mount to nextcloud's fstab
root@freenas[]# iocage fstab -a nextcloud /mnt/vault/apps/nextcloud/db /var/db/mysql nullfs rw 0 0
Successfully added mount to nextcloud's fstab
root@freenas[]# iocage fstab -a nextcloud /mnt/vault/apps/nextcloud/config /usr/local/www/nextcloud/config nullfs rw 0 0
Successfully added mount to nextcloud's fstab
root@freenas[]# iocage fstab -a nextcloud /mnt/vault/apps/nextcloud/themes /usr/local/www/nextcloud/themes nullfs rw 0 0
Successfully added mount to nextcloud's fstab
root@freenas[]# setenv EDITOR /usr/local/bin/nano
zsh: command not found: setenv
root@freenas[]# zfs set primarycache=metadata vault/apps/nextcloud/db
root@freenas[]# iocage list
+-----+-----------+-------+--------------+--------------+
| JID | NAME | STATE | RELEASE | IP4 |
+=====+===========+=======+==============+==============+
| 1 | nextcloud | up | 11.3-RELEASE | 192.168.1.15 |
+-----+-----------+-------+--------------+--------------+
root@freenas[~]# iocage console nextcloud
FreeBSD 11.3-RELEASE-p9 (FreeNAS.amd64) #0 r325575+588899735f7(HEAD): Mon Jun 1 15:04:31 EDT 2020

Welcome to FreeBSD!

Release Notes, Errata: https://www.FreeBSD.org/releases/
Security Advisories: https://www.FreeBSD.org/security/
FreeBSD Handbook: https://www.FreeBSD.org/handbook/
FreeBSD FAQ: https://www.FreeBSD.org/faq/
Questions List: https://lists.FreeBSD.org/mailman/listinfo/freebsd-questions/
FreeBSD Forums: https://forums.FreeBSD.org/

Documents installed with the system are in the /usr/local/share/doc/freebsd/
directory, or can be installed later with: pkg install en-freebsd-doc
For other languages, replace "en" with a language code like de or fr.

Show the version of FreeBSD installed: freebsd-version ; uname -a
Please include that output and any error messages when posting questions.
Introduction to manual pages: man man
FreeBSD directory layout: man hier

Edit /etc/motd to change this login announcement.
root@nextcloud:~ # pkg update
The package management tool is not yet installed on your system.
Do you want to fetch and install it now? [y/N]: y
Bootstrapping pkg from pkg+http://pkg.FreeBSD.org/FreeBSD:11:amd64/quarterly, please wait...
Verifying signature with trusted certificate pkg.freebsd.org.2013102301... done
[nextcloud] Installing pkg-1.14.6...
[nextcloud] Extracting pkg-1.14.6: 100%
Updating FreeBSD repository catalogue...
[nextcloud] Fetching meta.conf: 100% 163 B 0.2kB/s 00:01
[nextcloud] Fetching packagesite.txz: 100% 6 MiB 1.1MB/s 00:06
Processing entries: 100%
FreeBSD repository update completed. 32052 packages processed.
All repositories are up to date.
root@nextcloud:~ # pkg install nano
Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
All repositories are up to date.
Updating database digests format: 100%
The following 3 package(s) will be affected (of 0 checked):

New packages to be INSTALLED:
gettext-runtime: 0.20.2
indexinfo: 0.3.1
nano: 4.9.2

Number of packages to be installed: 3

The process will require 3 MiB more space.
690 KiB to be downloaded.

Proceed with this action? [y/N]: y
[nextcloud] [1/3] Fetching nano-4.9.2.txz: 100% 522 KiB 534.7kB/s 00:01
[nextcloud] [2/3] Fetching indexinfo-0.3.1.txz: 100% 6 KiB 5.8kB/s 00:01
[nextcloud] [3/3] Fetching gettext-runtime-0.20.2.txz: 24% 40 KiB 41.0kB/s 00:03 E[nextcloud] [3/3] Fetching gettext-runtime-0.20.2.txz: 100% 162 KiB 166.0kB/s 00:01
Checking integrity... done (0 conflicting)
[nextcloud] [1/3] Installing indexinfo-0.3.1...
[nextcloud] [1/3] Extracting indexinfo-0.3.1: 100%
[nextcloud] [2/3] Installing gettext-runtime-0.20.2...
[nextcloud] [2/3] Extracting gettext-runtime-0.20.2: 100%
[nextcloud] [3/3] Installing nano-4.9.2...
[nextcloud] [3/3] Extracting nano-4.9.2: 100%
root@nextcloud:~ # pkg install wget ca_root_nss
Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
All repositories are up to date.
The following 4 package(s) will be affected (of 0 checked):

New packages to be INSTALLED:
ca_root_nss: 3.54
libidn2: 2.3.0_1
libunistring: 0.9.10_1
wget: 1.20.3

Number of packages to be installed: 4

The process will require 7 MiB more space.
2 MiB to be downloaded.

Proceed with this action? [y/N]: y
[nextcloud] [1/4] Fetching wget-1.20.3.txz: 100% 654 KiB 334.9kB/s 00:02
[nextcloud] [2/4] Fetching ca_root_nss-3.54.txz: 100% 285 KiB 291.5kB/s 00:01
[nextcloud] [3/4] Fetching libidn2-2.3.0_1.txz: 100% 110 KiB 112.7kB/s 00:01
[nextcloud] [4/4] Fetching libunistring-0.9.10_1.txz: 7% 40 KiB 41.0kB/s 00:11 ET[nextcloud] [4/4] Fetching libunistring-0.9.10_1.txz: 75% 392 KiB 360.5kB/s 00:00 ET[nextcloud] [4/4] Fetching libunistring-0.9.10_1.txz: 100% 519 KiB 265.6kB/s 00:02
Checking integrity... done (0 conflicting)
[nextcloud] [1/4] Installing libunistring-0.9.10_1...
[nextcloud] [1/4] Extracting libunistring-0.9.10_1: 100%
[nextcloud] [2/4] Installing libidn2-2.3.0_1...
[nextcloud] [2/4] Extracting libidn2-2.3.0_1: 100%
[nextcloud] [3/4] Installing wget-1.20.3...
[nextcloud] [3/4] Extracting wget-1.20.3: 100%
[nextcloud] [4/4] Installing ca_root_nss-3.54...
[nextcloud] [4/4] Extracting ca_root_nss-3.54: 100%

Message from ca_root_nss-3.54:

--
FreeBSD does not, and can not warrant that the certification authorities
whose certificates are included in this package have in any way been
audited for trustworthiness or RFC 3647 compliance.

Assessment and verification of trust is the complete responsibility of the
system administrator.

This package installs symlinks to support root certificates discovery by
default for software that uses OpenSSL.

This enables SSL Certificate Verification by client software without manual
intervention.

If you prefer to do this manually, replace the following symlinks with
either an empty file or your site-local certificate bundle.

• /etc/ssl/cert.pem
• /usr/local/etc/ssl/cert.pem
• /usr/local/openssl/cert.pem
  root@nextcloud:~ # pkg install apache24
  Updating FreeBSD repository catalogue...
  FreeBSD repository is up to date.
  All repositories are up to date.
  The following 10 package(s) will be affected (of 0 checked):

New packages to be INSTALLED:
apache24: 2.4.43
apr: 1.7.0.1.6.1_1
db5: 5.3.28_7
expat: 2.2.8
gdbm: 1.18.1_1
libnghttp2: 1.41.0
libxml2: 2.9.10
pcre: 8.44
perl5: 5.30.3
readline: 8.0.4

Number of packages to be installed: 10

The process will require 151 MiB more space.
35 MiB to be downloaded.

Proceed with this action? [y/N]: y
[nextcloud] [1/10] Fetching apache24-2.4.43.txz: 100% 5 MiB 750.7kB/s 00:07
[nextcloud] [2/10] Fetching libnghttp2-1.41.0.txz: 100% 116 KiB 118.3kB/s 00:01
[nextcloud] [3/10] Fetching libxml2-2.9.10.txz: 100% 822 KiB 420.8kB/s 00:02
[nextcloud] [4/10] Fetching expat-2.2.8.txz: 100% 124 KiB 127.3kB/s 00:01
[nextcloud] [5/10] Fetching perl5-5.30.3.txz: 100% 14 MiB 1.9MB/s 00:08
[nextcloud] [6/10] Fetching pcre-8.44.txz: 100% 1 MiB 627.8kB/s 00:02
[nextcloud] [7/10] Fetching apr-1.7.0.1.6.1_1.txz: 100% 472 KiB 483.3kB/s 00:01
[nextcloud] [8/10] Fetching gdbm-1.18.1_1.txz: 100% 164 KiB 167.5kB/s 00:01
[nextcloud] [9/10] Fetching readline-8.0.4.txz: 100% 356 KiB 365.0kB/s 00:01
[nextcloud] [10/10] Fetching db5-5.3.28_7.txz: 100% 12 MiB 1.4MB/s 00:09
Checking integrity... done (0 conflicting)
[nextcloud] [1/10] Installing readline-8.0.4...
[nextcloud] [1/10] Extracting readline-8.0.4: 100%
[nextcloud] [2/10] Installing expat-2.2.8...
[nextcloud] [2/10] Extracting expat-2.2.8: 100%
[nextcloud] [3/10] Installing gdbm-1.18.1_1...
[nextcloud] [3/10] Extracting gdbm-1.18.1_1: 100%
[nextcloud] [4/10] Installing db5-5.3.28_7...
[nextcloud] [4/10] Extracting db5-5.3.28_7: 100%
[nextcloud] [5/10] Installing libnghttp2-1.41.0...
[nextcloud] [5/10] Extracting libnghttp2-1.41.0: 100%
[nextcloud] [6/10] Installing libxml2-2.9.10...
[nextcloud] [6/10] Extracting libxml2-2.9.10: 100%
[nextcloud] [7/10] Installing perl5-5.30.3...
[nextcloud] [7/10] Extracting perl5-5.30.3: 100%
[nextcloud] [8/10] Installing pcre-8.44...
[nextcloud] [8/10] Extracting pcre-8.44: 100%
[nextcloud] [9/10] Installing apr-1.7.0.1.6.1_1...
[nextcloud] [9/10] Extracting apr-1.7.0.1.6.1_1: 100%
[nextcloud] [10/10] Installing apache24-2.4.43...
===> Creating groups.
Using existing group 'www'.
===> Creating users
Using existing user 'www'.
[nextcloud] [10/10] Extracting apache24-2.4.43: 100%

Message from perl5-5.30.3:

--
The /usr/bin/perl symlink has been removed starting with Perl 5.20.
For shebangs, you should either use:

#!/usr/local/bin/perl

or

#!/usr/bin/env perl

The first one will only work if you have a /usr/local/bin/perl,
the second will work as long as perl is in PATH.

Message from apr-1.7.0.1.6.1_1:

--
The Apache Portable Runtime project removed support for FreeTDS with
version 1.6. Users requiring MS-SQL connectivity must migrate
configurations to use the added ODBC driver and FreeTDS' ODBC features.

Message from apache24-2.4.43:

--
To run apache www server from startup, add apache24_enable="yes"
in your /etc/rc.conf. Extra options can be found in startup script.

Your hostname must be resolvable using at least 1 mechanism in
/etc/nsswitch.conf typically DNS or /etc/hosts or apache might
have issues starting depending on the modules you are using.

• apache24 default build changed from static MPM to modular MPM

• more modules are now enabled per default in the port

• icons and error pages moved from WWWDIR to DATADIR

  If build with modular MPM and no MPM is activated in
  httpd.conf, then mpm_prefork will be activated as default
  MPM in etc/apache24/modules.d to keep compatibility with
  existing php/perl/python modules!

Please compare the existing httpd.conf with httpd.conf.sample
and merge missing modules/instructions into httpd.conf!
root@nextcloud:~ # sysrc apache24_enable=yes
apache24_enable: -> yes
root@nextcloud:~ # service apache24 start
Performing sanity check on apache24 configuration:
AH00558: httpd: Could not reliably determine the server's fully qualified domain name, using 127.0.0.1. Set the 'ServerName' directive globally to suppress this message
Syntax OK
Starting apache24.
AH00558: httpd: Could not reliably determine the server's fully qualified domain name, using 127.0.0.1. Set the 'ServerName' directive globally to suppress this message
root@nextcloud:~ # pkg install mariadb104-server
Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
All repositories are up to date.
The following 10 package(s) will be affected (of 0 checked):

New packages to be INSTALLED:
bash: 5.0.17
boost-libs: 1.72.0_2
galera26: 26.4.5
icu: 67.1,1
libedit: 3.1.20191231,1
libiconv: 1.16
mariadb104-client: 10.4.13_4
mariadb104-server: 10.4.13_4
rsync: 3.1.3_1
unixODBC: 2.3.7

Number of packages to be installed: 10

The process will require 481 MiB more space.
60 MiB to be downloaded.

Proceed with this action? [y/N]: y
[nextcloud] [1/10] Fetching mariadb104-server-10.4.13_4.txz: 0% 40 KiB 41.0kB/s 1[nextcloud] [1/10] Fetching mariadb104-server-10.4.13_4.txz: 1% 392 KiB 360.5kB/s 0[nextcloud] [1/10] Fetching mariadb104-server-10.4.13_4.txz: 2% 928 KiB 548.9kB/s 0[nextcloud] [1/10] Fetching mariadb104-server-10.4.13_4.txz: 5% 2 MiB 1.1MB/s 0[nextcloud] [1/10] Fetching mariadb104-server-10.4.13_4.txz: 10% 3 MiB 1.5MB/s 0[nextcloud] [1/10] Fetching mariadb104-server-10.4.13_4.txz: 15% 5 MiB 1.9MB/s 0[nextcloud] [1/10] Fetching mariadb104-server-10.4.13_4.txz: 22% 7 MiB 2.3MB/s 0[nextcloud] [1/10] Fetching mariadb104-server-10.4.13_4.txz: 30% 10 MiB 2.5MB/s 0[nextcloud] [1/10] Fetching mariadb104-server-10.4.13_4.txz: 38% 12 MiB 2.7MB/s 0[nextcloud] [1/10] Fetching mariadb104-server-10.4.13_4.txz: 48% 15 MiB 3.3MB/s 0[nextcloud] [1/10] Fetching mariadb104-server-10.4.13_4.txz: 59% 19 MiB 3.8MB/s 0[nextcloud] [1/10] Fetching mariadb104-server-10.4.13_4.txz: 72% 23 MiB 4.3MB/s 0[nextcloud] [1/10] Fetching mariadb104-server-10.4.13_4.txz: 86% 28 MiB 4.6MB/s 0[nextcloud] [1/10] Fetching mariadb104-server-10.4.13_4.txz: 100% 32 MiB 2.6MB/s 00:13
[nextcloud] [2/10] Fetching bash-5.0.17.txz: 100% 2 MiB 530.8kB/s 00:03
[nextcloud] [3/10] Fetching rsync-3.1.3_1.txz: 100% 308 KiB 315.9kB/s 00:01
[nextcloud] [4/10] Fetching libiconv-1.16.txz: 100% 610 KiB 312.1kB/s 00:02
[nextcloud] [5/10] Fetching libedit-3.1.20191231,1.txz: 11% 16 KiB 16.4kB/s 00:07 [nextcloud] [5/10] Fetching libedit-3.1.20191231,1.txz: 100% 138 KiB 141.4kB/s 00:01
[nextcloud] [6/10] Fetching unixODBC-2.3.7.txz: 100% 463 KiB 237.0kB/s 00:02
[nextcloud] [7/10] Fetching mariadb104-client-10.4.13_4.txz: 14% 208 KiB 213.0kB/s 0[nextcloud] [7/10] Fetching mariadb104-client-10.4.13_4.txz: 41% 592 KiB 393.2kB/s 0[nextcloud] [7/10] Fetching mariadb104-client-10.4.13_4.txz: 79% 1 MiB 557.1kB/s 0[nextcloud] [7/10] Fetching mariadb104-client-10.4.13_4.txz: 100% 1 MiB 486.1kB/s 00:03
[nextcloud] [8/10] Fetching galera26-26.4.5.txz: 100% 990 KiB 337.9kB/s 00:03
[nextcloud] [9/10] Fetching boost-libs-1.72.0_2.txz: 100% 13 MiB 628.1kB/s 00:21
[nextcloud] [10/10] Fetching icu-67.1,1.txz: 100% 10 MiB 665.3kB/s 00:16
Checking integrity... done (0 conflicting)
[nextcloud] [1/10] Installing icu-67.1,1...
[nextcloud] [1/10] Extracting icu-67.1,1: 100%
[nextcloud] [2/10] Installing libiconv-1.16...
[nextcloud] [2/10] Extracting libiconv-1.16: 100%
[nextcloud] [3/10] Installing libedit-3.1.20191231,1...
[nextcloud] [3/10] Extracting libedit-3.1.20191231,1: 100%
[nextcloud] [4/10] Installing boost-libs-1.72.0_2...
[nextcloud] [4/10] Extracting boost-libs-1.72.0_2: 100%
[nextcloud] [5/10] Installing bash-5.0.17...
[nextcloud] [5/10] Extracting bash-5.0.17: 100%
[nextcloud] [6/10] Installing rsync-3.1.3_1...
[nextcloud] [6/10] Extracting rsync-3.1.3_1: 100%
[nextcloud] [7/10] Installing unixODBC-2.3.7...
[nextcloud] [7/10] Extracting unixODBC-2.3.7: 100%
[nextcloud] [8/10] Installing mariadb104-client-10.4.13_4...
[nextcloud] [8/10] Extracting mariadb104-client-10.4.13_4: 100%
[nextcloud] [9/10] Installing galera26-26.4.5...
[nextcloud] [9/10] Extracting galera26-26.4.5: 100%
[nextcloud] [10/10] Installing mariadb104-server-10.4.13_4...
===> Creating groups.
Creating group 'mysql' with gid '88'.
===> Creating users
Creating user 'mysql' with uid '88'.
===> Creating homedir(s)
[nextcloud] [10/10] Extracting mariadb104-server-10.4.13_4: 100%

Message from boost-libs-1.72.0_2:

--
You have built the Boost library with thread support.

Don't forget to add -pthread to your linker options when
linking your code.

Message from mariadb104-client-10.4.13_4:

--
MariaDB respects hier(7) and doesn't check /etc and /etc/mysql for
my.cnf. Please move existing my.cnf files from those paths to
/usr/local/etc/mysql or /usr/local/etc. Sample
configuration files are provided in /usr/local/etc/mysql
and /usr/local/etc/mysql/conf.d.

This port does NOT include the mytop perl script, this is included in
the MariaDB tarball but the most recent version can be found in the
databases/mytop port

Message from galera26-26.4.5:

--
Find the Galera Cluster documentation at
http://galeracluster.com/documentation-webpages/

Message from mariadb104-server-10.4.13_4:

--
MariaDB respects hier(7) and doesn't check /etc and /etc/mysql for
my.cnf. Please move existing my.cnf files from those paths to
/usr/local/etc/mysql or /usr/local/etc. Sample
configuration files are provided in /usr/local/etc/mysql
and /usr/local/etc/mysql/conf.d.

This port does NOT include the mytop perl script, this is included in
the MariaDB tarball but the most recent version can be found in the
databases/mytop port
root@nextcloud:~ # sysrc mysql_enable=yes
mysql_enable: -> yes
root@nextcloud:~ # nano /usr/local/etc/mysql/my.cnf
GNU nano 4.9.2 /usr/local/etc/mysql/my.cnf

This group is read both by the client and the server

use it for options that affect everything, see

https://mariadb.com/kb/en/configuring-mariadb-with-option-files/#option-groups

[client-server]
port = 3306
socket = /tmp/mysql.sock

include *.cnf from the config directory

!includedir /usr/local/etc/mysql/conf.d/

root@nextcloud:~ # service mysql-server start
Installing MariaDB/MySQL system tables in '/var/db/mysql' ...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

Two all-privilege accounts were created.
One is root@localhost, it has no password, but you need to
be system 'root' user to connect. Use, for example, sudo mysql
The second is mysql@localhost, it has no password either, but
you need to be the system 'mysql' user to connect.
After connecting you can set the password, if you would need to be
able to connect as any of these users with a password and without sudo

See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.

You can start the MariaDB daemon with:
cd '/usr/local' ; /usr/local/bin/mysqld_safe --datadir='/var/db/mysql'

You can test the MariaDB daemon with mysql-test-run.pl
cd '/usr/local/mysql-test' ; perl mysql-test-run.pl

Please report any problems at http://mariadb.org/jira

The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/

Starting mysql.
root@nextcloud:~ # mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none):
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (2)
Enter current password for root (enter for none):

[samuel-emrys]

Looks like the mysql_secure_installation script has been updated to use the default installation location. You can either change the socket value in my.cnf back to /var/run/mysql/mysql.sock, and replace all references throughout the rest of the guide to /tmp/mysql.sock to this new path, or add the --socket flag to the mysql_secure_installation command:

mysql_secure_installation --socket=/tmp/mysql.sock

I'll be updating it to pass the socket flag for consistency with the rest of the guide, with the more substantial revision to /var/run/mysql/mysql.sock at some stage in the future