How do I install MariaDB 10.3 on FreeBSD 12?, How to Install MariaDB 10.3 Database Server on FreeBSD 12?. MariaDB 10.3 is a multi-user, multi-threaded SQL database server. It is a community-developed fork of the MySQL relational database management system.
MariaDB 10.3 Features:
- InnoDB is used as the default storage engine instead of XtraDB.
- System-versioned tables
- FOR loops
- MariaDB Galera Cluster, a synchronous multi-master cluster, is now a standard part of MariaDB.
- Instant ADD COLUMN for InnoDB
- Invisible columns
- Parallel replication
- Multi-source replication
- Common table expressions
- Storage-engine independent column compression
In my last article, I covered:
Below are the steps to install and configure MariaDB 10.3 on FreeBSD 12.
Step 1: Update ports tree
Start by updating your FreeBSD server Ports tree using the
$ [email protected]:~ % su - Password: [email protected]:~# portsnap fetch update Looking up portsnap.FreeBSD.org mirrors... 5 mirrors found. Fetching public key from ec2-ap-southeast-2.portsnap.freebsd.org... done. Fetching snapshot tag from ec2-ap-southeast-2.portsnap.freebsd.org... done. Fetching snapshot metadata... done. Fetching snapshot generated at Sun Dec 9 00:19:28 UTC 2018: Extracting snapshot... done. Verifying snapshot integrity... done. Fetching snapshot tag from ec2-ap-southeast-2.portsnap.freebsd.org... done. Fetching snapshot metadata... done. Updating from Sun Dec 9 00:19:28 UTC 2018 to Sun Dec 9 19:15:19 UTC 2018. Fetching 5 metadata patches... done. Applying metadata patches... done. Fetching 0 metadata files... done. Fetching 78 patches. (78/78) 100.00% done. done. Applying patches... done. Fetching 3 new ports or files... done. /usr/ports was not created by portsnap. You must run 'portsnap extract' before running 'portsnap update'.
When running Portsnap for the first time, you need to extract the snapshot into
# portsnap extract
You should get the message below at the end of the output.
Building new INDEX files... done.
Confirm that everything is up to date.
# portsnap fetch update
Step 2: Install MariaDB 10.3 Database Server
MariaDB 10.3 can be installed using binary or ports method. This installation is from the binary package using the
Check if MariaDB 10.3 is available from your package cache:
# pkg search mariadb | grep 10.3 mariadb103-client-10.3.11 Multithreaded SQL database (client) mariadb103-server-10.3.11 Multithreaded SQL database (server)
From the output, we can see there is version 10.3.11 available. Install both the server and client using the command below:
# pkg install mariadb103-server mariadb103-client Updating FreeBSD repository catalogue... FreeBSD repository is up to date. All repositories are up to date. The following 6 package(s) will be affected (of 0 checked): New packages to be INSTALLED: mariadb103-server: 10.3.11 mariadb103-client: 10.3.11 libedit: 3.1.20170329_2,1 unixODBC: 2.3.7 galera: 25.3.24 boost-libs: 1.68.0_1 Number of packages to be installed: 6 The process will require 406 MiB more space. 44 MiB to be downloaded. Proceed with this action? [y/N]: y [1/6] Fetching mariadb103-server-10.3.11.txz: 100% 29 MiB 159.5kB/s 03:11 [2/6] Fetching mariadb103-client-10.3.11.txz: 100% 2 MiB 101.4kB/s 00:17 [3/6] Fetching libedit-3.1.20170329_2,1.txz: 100% 123 KiB 125.6kB/s 00:01 [4/6] Fetching unixODBC-2.3.7.txz: 100% 456 KiB 93.5kB/s 00:05 [5/6] Fetching galera-25.3.24.txz: 100% 802 KiB 117.3kB/s 00:07 [6/6] Fetching boost-libs-1.68.0_1.txz: 100% 12 MiB 134.6kB/s 01:32 Checking integrity... done (0 conflicting) [1/6] Installing libedit-3.1.20170329_2,1... [1/6] Extracting libedit-3.1.20170329_2,1: 100% [2/6] Installing boost-libs-1.68.0_1... [2/6] Extracting boost-libs-1.68.0_1: 100% [3/6] Installing mariadb103-client-10.3.11... [3/6] Extracting mariadb103-client-10.3.11: 100% [4/6] Installing unixODBC-2.3.7... [4/6] Extracting unixODBC-2.3.7: 100% [5/6] Installing galera-25.3.24... [5/6] Extracting galera-25.3.24: 100% [6/6] Installing mariadb103-server-10.3.11... ===> Creating groups. Creating group 'mysql' with gid '88'. ===> Creating users Creating user 'mysql' with uid '88'. [6/6] Extracting mariadb103-server-10.3.11: 100% Message from boost-libs-1.68.0_1: 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 mariadb103-client-10.3.11: ************************************************************************
Step 3: Start and Enable mysql-server service
After installing MariaDB Database server on FreeBSD 12 server, you’ll need to start the service before you can configure it.
Run the following command in your terminal to enable the
mysql-server service to start on system boot.
# sysrc mysql_enable="YES" mysql_enable: -> YES
Then start the service.
# 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 PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER ! To do so, start the server, then issue the following commands: '/usr/local/bin/mysqladmin' -u root password 'new-password' '/usr/local/bin/mysqladmin' -u root -h freebsd password 'new-password' Alternatively you can run: '/usr/local/bin/mysql_secure_installation' which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. 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:
Step 4: Harden MariaDB server installation
Run the command
mysql_secure_installation to harden MariaDB database server.
# 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 you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation. Set root password? [Y/n] y New password: <ENTER NEW PASSWORD> Re-enter new password: <CONFIRM PASSWORD> Password updated successfully! Reloading privilege tables.. ... Success! By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] Y ... Success! Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] Y ... Success! By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] Y - Dropping test database... ... Success! - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] Y ... Success! Cleaning up... All done! If you've completed all of the above steps, your MariaDB installation should now be secure. Thanks for using MariaDB!
- Set Database root user password
- Remove anonymous users
- Disallow root user remote logins
- Remove test database and access to it
When done, test access using the root user
$ mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 17 Server version: 10.3.11-MariaDB FreeBSD Ports Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> SELECT VERSION(); +-----------------+ | VERSION() | +-----------------+ | 10.3.11-MariaDB | +-----------------+ 1 row in set (0.001 sec) MariaDB [(none)]> QUIT Bye
Step 5: Bind MariaDB Server service to localhost
By default, MariaDB service binds to all available network interfaces as can be seen from:
# sockstat -4 -6 | grep 3306 mysql mysqld 1350 21 tcp46 *:3306 *:*
If you don’t have services connecting to your database server from the network, you can limit this to localhost.
# sysrc mysql_args="--bind-address=127.0.0.1" mysql_args: -> --bind-address=127.0.0.1
You need to restart
mysql-server after making this change
# service mysql-server restart Stopping mysql. Waiting for PIDS: 1350, 1350. Starting mysql.
Verify the change:
# sockstat -4 -6 | grep mysql mysql mysqld 1614 21 tcp4 127.0.0.1:3306 *:*
Your installation of MariaDB 10.3 on FreeBSD 12 has been successful. We went ahead and enabled the service to start on boot and hardened the server. Until next time, thanks for visiting our website. Follow us on our social media pages for instant updates.