MariaDB – Multithreaded SQL Database Server/Client
Last Updated on 2025-02-03 22:53 by Sture
Description:
MariaDB is a database server that offers drop-in replacement functionality for MySQL. MariaDB is built by some of the original authors of MySQL, with assistance from the broader community of Free and open-source software developers. In addition to the core functionality of MySQL, MariaDB offers a rich set of feature enhancements, including alternate storage engines, server optimizations, and patches.
Developers primarily drive MariaDB at Monty Program, a company founded by Michael “Monty” Widenius, the original author of MySQL, but this is not the whole story about MariaDB. On the “About MariaDB” page, you will find more information about all participants in the MariaDB community, including storage engines XtraDB and PBXT.
WWW: https://mariadb.org/.
Creat ZFS datasets for MariaDB databas storage
One area that definitely needs Specialized Tuning is when you create a ZFS mount specifically for a database.
N.B.: This should be done on a new MariaDB install. It is assumed mySQL has never run on this machine, so the data directories are not populated (on FreeBSD, the first run of mySQL creates the data files. If you have run it, back up any data, then:
### WARNING!, WARNING!, WARNING ###
This will delete any existing databases!
user@freebsdsrv:~ $ sudo rm -fR /var/db/mysql/* [enter]
user@freebsdsrv:~ $Create ZFS datasets for MariaDB with:
user@freebsdsrv:~ $ sudo zfs create zroot/db ; sudo zfs create zroot/db/logs ; sudo zfs create zroot/db/innodb ; sudo zfs create zroot/db/myisam [enter]
user@freebsdsrv:~ $Set global ZFS paraemters with:
user@freebsdsrv:~ $ sudo zfs set zfs:zfs_nocacheflush=1 zroot/db [enter]
user@freebsdsrv:~ $user@freebsdsrv:~ $ sudo zfs set setuid=off zroot/db [enter]
user@freebsdsrv:~ $user@freebsdsrv:~ $ sudo zfs set primarycache=metadata zroot/db [enter]
user@freebsdsrv:~ $user@freebsdsrv:~ $ sudo zfs set atime=off zroot/db [enter]
user@freebsdsrv:~ $user@freebsdsrv:~ $ sudo zfs set sync=disabled zroot/db [enter]
user@freebsdsrv:~ $user@freebsdsrv:~ $ sudo zfs set compression=off zroot/db [enter]
user@freebsdsrv:~ $user@freebsdsrv:~ $ sudo zfs set logbias=throughput zroot/db [enter]
user@freebsdsrv:~ $Set individual parameters (blocksizes) with:
user@freebsdsrv:~ $ sudo zfs set recordsize=16k zroot/db/innodb [enter]
user@freebsdsrv:~ $user@freebsdsrv:~ $ sudo zfs set recordsize=128k zroot/db/logs [enter]
user@freebsdsrv:~ $user@freebsdsrv:~ $ sudo zfs set recordsize=8k zroot/db/myisam [enter]
user@freebsdsrv:~ $Create directories, set the mount points and then set ownership with:
user@freebsdsrv:~ $ sudo mkdir /var/log/mysql [enter]
user@freebsdsrv:~ $user@freebsdsrv:~ $ sudo zfs set mountpoint=/var/log/mysql zroot/db/logs [enter]
user@freebsdsrv:~ $user@freebsdsrv:~ $ sudo mkdir /var/db/mysql [enter]
user@freebsdsrv:~ $user@freebsdsrv:~ $ sudo zfs set mountpoint=/var/db/mysql zroot/db/myisam [enter]
user@freebsdsrv:~ $user@freebsdsrv:~ $ sudo mkdir /var/db/mysql-innodb [enter]
user@freebsdsrv:~ $user@freebsdsrv:~ $ sudo zfs set mountpoint=/var/db/mysql-innodb zroot/db/innodb [enter]
user@freebsdsrv:~ $To start the MariaDB Server on system boot:
user@freebsdsrv:~ $ sudo sysrc mysql_enable="YES" [enter]
mysql_enable: -> YES
user@freebsdsrv:~ $Installation
Search for “mariadb” in the remote package repositories with:
user@freebsdsrv:~ $ pkg search mariadb1 [enter]
mariadb1011-client-10.11.10_1 Multithreaded SQL database (client)
mariadb1011-server-10.11.10_1 Multithreaded SQL database (server)
mariadb105-client-10.5.27 Multithreaded SQL database (client)
mariadb105-server-10.5.27 Multithreaded SQL database (server)
mariadb106-client-10.6.20 Multithreaded SQL database (client)
mariadb106-server-10.6.20 Multithreaded SQL database (server)
mariadb114-client-11.4.4 Multithreaded SQL database (client)
mariadb114-server-11.4.4 Multithreaded SQL database (server)
user@freebsdsrv:~ $In this example, mariadb114-server and mariadb114-client will be installed with;
user@freebsdsrv:~ $ sudo pkg install -y mariadb114-server mariadb114-client [enter]
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.2.37
boost-libs: 1.85.0
galera26: 26.4.16_3
icu: 74.2_1,1
libfmt: 10.2.1
mariadb114-client: 11.4.3
mariadb114-server: 11.4.3
rsync: 3.3.0
unixODBC: 2.3.12_1
xxhash: 0.8.2_1
Number of packages to be installed: 10
The process will require 591 MiB more space.
82 MiB to be downloaded.
[1/10] Fetching galera26-26.4.16_3.pkg: 100% 908 KiB 929.6kB/s 00:01
[2/10] Fetching boost-libs-1.85.0.pkg: 100% 15 MiB 15.2MB/s 00:01
[3/10] Fetching unixODBC-2.3.12_1.pkg: 100% 547 KiB 559.9kB/s 00:01
[4/10] Fetching mariadb114-server-11.4.3.pkg: 100% 49 MiB 51.3MB/s 00:01
[5/10] Fetching icu-74.2_1,1.pkg: 100% 13 MiB 13.2MB/s 00:01
[6/10] Fetching xxhash-0.8.2_1.pkg: 100% 92 KiB 93.9kB/s 00:01
[7/10] Fetching bash-5.2.37.pkg: 100% 2 MiB 1.8MB/s 00:01
[8/10] Fetching mariadb114-client-11.4.3.pkg: 100% 3 MiB 2.9MB/s 00:01
[9/10] Fetching rsync-3.3.0.pkg: 100% 396 KiB 405.7kB/s 00:01
[10/10] Fetching libfmt-10.2.1.pkg: 100% 178 KiB 182.2kB/s 00:01
Checking integrity... done (0 conflicting)
[1/10] Installing icu-74.2_1,1...
[1/10] Extracting icu-74.2_1,1: 100%
[2/10] Installing boost-libs-1.85.0...
[2/10] Extracting boost-libs-1.85.0: 100%
[3/10] Installing xxhash-0.8.2_1...
[3/10] Extracting xxhash-0.8.2_1: 100%
[4/10] Installing libfmt-10.2.1...
[4/10] Extracting libfmt-10.2.1: 100%
[5/10] Installing galera26-26.4.16_3...
[5/10] Extracting galera26-26.4.16_3: 100%
[6/10] Installing unixODBC-2.3.12_1...
[6/10] Extracting unixODBC-2.3.12_1: 100%
[7/10] Installing bash-5.2.37...
[7/10] Extracting bash-5.2.37: 100%
[8/10] Installing mariadb114-client-11.4.3...
===> Creating groups
Creating group 'mysql' with gid '88'
===> Creating users
Creating user 'mysql' with uid '88'
===> Creating homedir(s)
[8/10] Extracting mariadb114-client-11.4.3: 100%
[9/10] Installing rsync-3.3.0...
[9/10] Extracting rsync-3.3.0: 100%
[10/10] Installing mariadb114-server-11.4.3...
===> Creating groups
Using existing group 'mysql'
===> Creating users
Using existing user 'mysql'
===> Creating homedir(s)
[10/10] Extracting mariadb114-server-11.4.3: 100%
=====
Message from boost-libs-1.85.0:
--
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 galera26-26.4.16_3:
--
Find the Galera Cluster documentation at
https://galeracluster.com/library/documentation/index.html
=====
Message from mariadb114-client-11.4.3:
--
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.
The rc(8) script no longer uses /var/db/mysql/my.cnf for configuration
nor /var/db/mysql for logs and PID-file.
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
%%WSREP%%The Using wsrep clustering requires additional configuration.
%%WSREP%%Edit /usr/local/etc/mysql/conf.d/wsrep.conf and change what you need there.
The hashicorp_key_management, s3 and spider plugins must be enabled
before they can be used, check the specific .cnf files in /usr/local/etc/mysql/conf.d/
=====
Message from rsync-3.3.0:
--
Some scripts provided by rsync, such as rrsync,
require Python, which is not installed by default.
=====
Message from mariadb114-server-11.4.3:
--
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.
The rc(8) script no longer uses /var/db/mysql/my.cnf for configuration
nor /var/db/mysql for logs and PID-file.
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
The Using wsrep clustering requires additional configuration.
Edit /usr/local/etc/mysql/conf.d/wsrep.conf and change what you need there.
The hashicorp_key_management, s3 and spider plugins must be enabled
before they can be used, check the specific .cnf files in /usr/local/etc/mysql/conf.d/
user@freebsdsrv:~ $Change owner on MaraDB directories with:
user@freebsdsrv:~ $ sudo chown mysql:mysql /var/log/mysql ; sudo chown mysql:mysql /var/db/mysql ; sudo chown mysql:mysql /var/db/mysql-innodb [enter]
user@freebsdsrv:~ $Initialize the MariaDB data directory and sart the MySQL server with:
user@freebsdsrv:~ $ sudo service mysql-server start [enter]
Installing MariaDB/MySQL system tables in './data' ...
OK
To start mariadbd at boot time you have to copy
support-files/mariadb.service 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 https://mariadb.com/kb
You can start the MariaDB daemon with:
cd '/usr/local' ; /usr/local/bin/mariadbd-safe --datadir='./data'
You can test the MariaDB daemon with mariadb-test-run.pl
cd '/usr/local/' ; perl mariadb-test-run.pl
Please report any problems at https://mariadb.org/jiraThe latest information about MariaDB is available at https://mariadb.org/.
Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/
user@freebsdsrv:~ $Verify status with:
user@freebsdsrv:~ $ sudo service mysql-server status [enter]
mysql is running as pid 14018.
user@freebsdsrv:~ $Set MariaDB server root password with:
user@freebsdsrv:~ $ sudo /usr/local/bin/mariadb-admin -u root password 'DBrootPassWd' [enter]
user@freebsdsrv:~ $Confirm that the MariaDB service is up and running by checking its status with:
user@freebsdsrv:~ $ sudo service mysql-server status [enter]
mysql is running as pid 6971.
user@freebsdsrv:~ $Display file /var/log/mysql/mysqld.err with:
user@freebsdsrv:~ $ sudo cat /var/log/mysql/mysqld.err [enter]2025-02-03 22:42:02 0 [Warning] 'wsrep-causal-reads' was removed. It does nothing now and exists only for compatibility with old my.cnf files.
2025-02-03 22:42:05 0 [Note] Starting MariaDB 11.4.4-MariaDB source revision e9a502df08bad16aa8a354e854f3c014b1380e32 server_uid DByzGKgo5W7evv3aCV6HD1tucBU= as process 14018
2025-02-03 22:42:05 0 [Note] InnoDB: Compressed tables use zlib 1.3.1
2025-02-03 22:42:05 0 [Note] InnoDB: Number of transaction pools: 1
2025-02-03 22:42:05 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2025-02-03 22:42:05 0 [Note] InnoDB: Initializing buffer pool, total size = 128.000MiB, chunk size = 2.000MiB
2025-02-03 22:42:05 0 [Note] InnoDB: Completed initialization of buffer pool
2025-02-03 22:42:06 0 [Note] InnoDB: End of log at LSN=47907
2025-02-03 22:42:06 0 [Note] InnoDB: Opened 3 undo tablespaces
2025-02-03 22:42:06 0 [Note] InnoDB: 128 rollback segments in 3 undo tablespaces are active.
2025-02-03 22:42:06 0 [Note] InnoDB: Setting file './ibtmp1' size to 12.000MiB. Physically writing the file full; Please wait ...
2025-02-03 22:42:06 0 [Note] InnoDB: File './ibtmp1' size is now 12.000MiB.
2025-02-03 22:42:06 0 [Note] InnoDB: log sequence number 47907; transaction id 16
2025-02-03 22:42:06 0 [Note] InnoDB: Loading buffer pool(s) from /var/db/mysql/ib_buffer_pool
2025-02-03 22:42:06 0 [Note] Plugin 'FEEDBACK' is disabled.
2025-02-03 22:42:06 0 [Note] Plugin 'wsrep-provider' is disabled.
2025-02-03 22:42:06 0 [Warning] 'wsrep-causal-reads' was removed. It does nothing now and exists only for compatibility with old my.cnf files.
2025-02-03 22:42:06 0 [Warning] 'wsrep-causal-reads' was removed. It does nothing now and exists only for compatibility with old my.cnf files.
2025-02-03 22:42:06 0 [Note] InnoDB: Buffer pool(s) load completed at 250203 22:42:06
2025-02-03 22:42:09 0 [Note] Server socket created on IP: '0.0.0.0'.
2025-02-03 22:42:09 0 [Note] mariadbd: Event Scheduler: Loaded 0 events
2025-02-03 22:42:09 0 [Note] /usr/local/libexec/mariadbd: ready for connections.
Version: '11.4.4-MariaDB' socket: '/var/run/mysql/mysql.sock' port: 3306 FreeBSD PortsHarden the MariaDB database server with:
user@freebsdsrv:~ $ sudo mariadb-secure-installation [enter]
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): <--DBrootPassWd [enter]
OK, successfully used password, moving on...
Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.
You already have your root account protected, so you can safely answer 'n'.
Switch to unix_socket authentication [Y/n] [enter]
Enabled successfully!
Reloading privilege tables..
... Success!
You already have your root account protected, so you can safely answer 'n'.
Change the root password? [Y/n] n [enter]
... skipping.
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] [enter]
... 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] [enter]
... 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] [enter]
- 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] [enter]
... 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!
user@freebsdsrv:~ $Access MariaDB using the root user:
user@freebsdsrv:~ $ sudo mysql -u root -p [enter]
Enter password: <-- DBrootPassWd [enter]
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 11.4.4-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.
root@localhost [(none)]>Exit MariaDB root with:
root@localhost [(none)]> exit [enter]
Bye
user@freebsdsrv:~ $Lists open Internet or UNIX domain sockets MariaDB service binds to with:
user@freebsdsrv:~ $ sockstat | grep 3306 [enter]
mysql mariadbd 43037 24 tcp4 *:3306 *:*
user@freebsdsrv:~ $To prevent remote access to MariaDB, we must make sure that the daemon of MariaDB only listens to the localhost with:
user@freebsdsrv:~ $ sudo sysrc mysql_args="--bind-address=127.0.0.1" [enter]
mysql_args: -> --bind-address=127.0.0.1
user@freebsdsrv:~ $user@freebsdsrv:~ $ sudo service mysql-server restart [enter]
Stopping mysql.
Waiting for PIDS: 51757.
Starting mysql.
user@freebsdsrv:~ $Lists open Internet or UNIX domain sockets MariaDB service binds to with:
user@freebsdsrv:~ $ sockstat | grep 3306 [enter]
mysql mariadbd 50299 24 tcp4 127.0.0.1:3306 *:*
user@freebsdsrv:~ $Test MariaDB on FreeBSD
Test the connectivity to the databases with:
user@freebsdsrv:~ $ sudo mysql -u root -p -e "show databases" [enter]
Enter password: DBrootPassWd [enter]
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
user@freebsdsrv:~ $