Beyond Linux® From Scratch (systemd Edition) - Version 11.2

Chapter 22. Databases

MariaDB-10.6.9

Introduction to MariaDB

MariaDB is a community-developed fork and a drop-in replacement for the MySQL relational database management system.

This package is known to build and work properly using an LFS-11.2 platform.

Package Information

[Note]

Note

The installed size of MariaDB is 658 MB, but this can be reduced by about 250 MB, if desired, by removing the /usr/share/mysql/test directory after installation.

MariaDB Dependencies

Required

CMake-3.24.1

Recommended

libevent-2.1.12

Optional

Boost-1.80.0, libaio-0.3.113, libxml2-2.10.0, Linux-PAM-1.5.2, MIT Kerberos V5-1.20, pcre2-10.40, Ruby-3.1.2, unixODBC-2.3.11, Valgrind-3.19.0, Groonga, KyTea, Judy, lz4, MeCab, MessagePack, mruby, MyRocks, Snappy, Sphinx, and ZeroMQ

User Notes: https://wiki.linuxfromscratch.org/blfs/wiki/mariadb

Installation of MariaDB

[Warning]

Warning

MariaDB and MySQL cannot be installed on the same system without extensive changes to the build configuration of one of the two applications.

For security reasons, running the server as an unprivileged user and group is strongly encouraged. Issue the following (as root) to create the user and group: 

groupadd -g 40 mysql &&
useradd -c "MySQL Server" -d /srv/mysql -g mysql -s /bin/false -u 40 mysql

Install MariaDB by running the following commands: 

mkdir build &&
cd    build &&

cmake -DCMAKE_BUILD_TYPE=Release                      \
      -DCMAKE_INSTALL_PREFIX=/usr                     \
      -DINSTALL_DOCDIR=share/doc/mariadb-10.6.9       \
      -DINSTALL_DOCREADMEDIR=share/doc/mariadb-10.6.9 \
      -DINSTALL_MANDIR=share/man                      \
      -DINSTALL_MYSQLSHAREDIR=share/mysql             \
      -DINSTALL_MYSQLTESTDIR=share/mysql/test         \
      -DINSTALL_PLUGINDIR=lib/mysql/plugin            \
      -DINSTALL_SBINDIR=sbin                          \
      -DINSTALL_SCRIPTDIR=bin                         \
      -DINSTALL_SQLBENCHDIR=share/mysql/bench         \
      -DINSTALL_SUPPORTFILESDIR=share/mysql           \
      -DMYSQL_DATADIR=/srv/mysql                      \
      -DMYSQL_UNIX_ADDR=/run/mysqld/mysqld.sock       \
      -DWITH_EXTRA_CHARSETS=complex                   \
      -DWITH_EMBEDDED_SERVER=ON                       \
      -DSKIP_TESTS=ON                                 \
      -DTOKUDB_OK=0                                   \
      .. &&
make

To test the results, issue: make test. One test, test-connect, is known to fail.

[Note]

Note

A more extensive set of tests can be run with the following: 

pushd mysql-test
./mtr --parallel <N> --mem --force
popd

Where N is the number of tests to run in parallel. Over 5400 tests are run in about 24 SBU with N=4. A few tests may fail, mainly due to character set issues.

Now, as the root user: 

make install

Command Explanations

-DWITH_EMBEDDED_SERVER=ON: This switch enables compiling the embedded server library needed by certain applications, such as Amarok.

-DWITH_EXTRA_CHARSETS=complex: This switch enables support for the complex character sets.

-DSKIP_TESTS=ON: This switch disables tests for MariaDB Connector/C which are not supported without additional setup.

-DWITHOUT_SERVER=ON: Use this switch if you don't want the server and would like to build the client only.

[Note]

Note

There are numerous options available to cmake. Check the output of the cmake . -LH for additional customization options.

Configuring MySQL

Config Files

/etc/mysql/my.cnf and ~/.my.cnf

Configuration Information

Create basic /etc/mysql/my.cnf using the following command as the root user: 

install -v -dm 755 /etc/mysql &&
cat > /etc/mysql/my.cnf << "EOF"
# Begin /etc/mysql/my.cnf

# The following options will be passed to all MySQL clients
[client]
#password       = your_password
port            = 3306
socket          = /run/mysqld/mysqld.sock

# The MySQL server
[mysqld]
port            = 3306
socket          = /run/mysqld/mysqld.sock
datadir         = /srv/mysql
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
sort_buffer_size = 512K
net_buffer_length = 16K
myisam_sort_buffer_size = 8M

# Don't listen on a TCP/IP port at all.
skip-networking

# required unique id between 1 and 2^32 - 1
server-id       = 1

# Uncomment the following if you are using BDB tables
#bdb_cache_size = 4M
#bdb_max_lock = 10000

# InnoDB tables are now used by default
innodb_data_home_dir = /srv/mysql
innodb_log_group_home_dir = /srv/mysql
# All the innodb_xxx values below are the default ones:
innodb_data_file_path = ibdata1:12M:autoextend
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 128M
innodb_log_file_size = 48M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

# End /etc/mysql/my.cnf
EOF

You can now install a database and change the ownership to the unprivileged user and group (perform as the root user): 

mysql_install_db --basedir=/usr --datadir=/srv/mysql --user=mysql &&
chown -R mysql:mysql /srv/mysql

Further configuration requires that the MariaDB server is running. Start the server using the following commands as the root user: 

install -v -m755 -o mysql -g mysql -d /run/mysqld &&
mysqld_safe --user=mysql 2>&1 >/dev/null &

A default installation does not set up a password for the administrator, so use the following command as the root user to set one. 

mysqladmin -u root password

Configuration of the server is now finished. Shut the server down using the following command as the root user: 

mysqladmin -p shutdown

Systemd Unit

Install the mysqld.service unit included in the blfs-systemd-units-20220720 package as the root user to start the MariaDB server during system boot-up. 

make install-mysqld
[Note]

Note

If you have an existing database already and this installation of binaries was just an upgrade to a newer version, check the upstream documentation for upgrading. It is recommended to run: 

mariadb-upgrade

Contents

Installed Programs: aria_chk, aria_dump_log, aria_ftdump, aria_pack, aria_read_log, aria_s3_copy, galera_new_cluster, galera_recovery, innochecksum, mariabackup, mariadb, mariadbd, mariadb-access, mariadb-admin, mariadb-backup, mariadb-binlog, mariadb-check, mariadb-client-test, mariadb-client-test-embedded, mariadb-conv, mariadb-convert-table-format, mariadb-find-rows, mariadbd-multi, mariadbd-safe, mariadbd-safe-helper, mariadb-dump, mariadb-dumpslow, mariadb-embedded, mariadb-fix-extensions, mariadb-hotcopy, mariadb-import, mariadb-install-db, mariadb-ldb, mariadb-plugin, mariadb-secure-installation, mariadb-setpermission, mariadb-service-convert, mariadb-show, mariadb-slap, mariadb-test, mariadb-test-embedded, mariadb-tzinfo-to-sql, mariadb-upgrade, mariadb-waitpid, mariadb_config, mbstream, msql2mysql, my_print_defaults, myisamchk, myisam_ftdump, myisamlog, myisampack, mysql_config, mytop, perror, replace, resolve_stack_dump, resolveip, sst_dump, wsrep_sst_common, wsrep_sst_mariabackup, wsrep_sst_mysqldump, and wsrep_sst_rsync
Installed Libraries: libmariadbclient.a, libmariadb.so, libmariadbd.so, libmysqlclient.a, libmysqlclient_r.a (symbolic links to libmariadbclient.a), libmysqld.{so,a}, libmysqlservices.a, and several under /usr/lib/mysql/plugin/
Installed Directories: /etc/mysql, /usr/{include,lib,share}/mysql, /usr/share/groonga{-normalizer-mysql}, and /usr/share/doc/mariadb-10.6.9

Short Descriptions

There are several symlinks from mysql* to their mariadb counterparts. These are maintained for compatibility.

The Perl DBI modules must be installed for some of the MariaDB support programs to function properly.

Short Descriptions

aria_chk

is used to check, repair, optimize, sort and get information about Aria tables

aria_dump_log

is a tool to dump the contents of Aria log pages

aria_ftdump

displays full-text index information

aria_pack

is a tool to generate compressed, read-only Aria tables

aria_read_log

displays Aria log file contents

aria_s3_copy

copies an aria table to and from AWS S3

galera_new_cluster

bootstraps a new Galera cluster

galera_recovery

recovers data from a Galera cluster

innochecksum

prints checksums for InnoDB files

mariabackup

is an open source backup tool for InnoDB and XtraDB

mariadb

is a simple SQL shell with input line editing capabilities

mariadbd

is the MySQL server daemon

mariadb-access

checks the access privileges for a host name, user name, and database combination

mariadb-admin

is a client for performing administrative operations

mariadb-binlog

reads binary log files

mariadb-check

performs table maintenance: It checks, repairs, optimizes, or analyzes tables

mariadb-client-test

is used for testing aspects of the MySQL client API that cannot be tested using mysqltest and its test language

mariadb-client-test-embedded

is a tool to test the client API for the embedded server

mariadb-conv

converts character sets for use with MariaDB

mariadb-convert-table-format

converts the tables in a database to use a particular storage engine

mariadbd-multi

is designed to manage several mysqld processes that listen for connections on different Unix socket files and TCP/IP ports

mariadbd-safe

is the recommended way to start a mysqld server on Unix and NetWare

mariadb-dump

is a backup program

mariadb-dumpslow

parses MySQL slow query log files and prints a summary of their contents

mariadb-embedded

is a MySQL client statically linked to libmariadbd

mariadb-find-rows

reads files containing SQL statements and extracts statements that match a given regular expression or that contain USE db_name or SET statements

mariadb-fix-extensions

converts the extensions for MyISAM (or ISAM) table files to their canonical forms

mariadb-hotcopy

locks the table, flushes the table and then performs a copy of the database

mariadb-import

reads a range of data formats, and inserts the data into a database

mariadb-install-db

initializes the MySQL data directory and creates the system tables that it contains, if they do not exist

mariadb-ldb

is the RocksDB tool

mariadb-plugin

is a utility that enables MySQL administrators to manage which plugins a MySQL server loads

mariadb-secure-installation

is a tool to improve MySQL installation security

mariadb-service-convert

generates a systemd unit based on the current mariadb settings

mariadb-setpermission

sets permissions in the MySQL grant tables

mariadb-show

shows the structure of a MariaDB database

mariadb-slap

is a diagnostic program designed to emulate client load for a MySQL server and to report the timing of each stage

mariadb-test

runs a test case against a MySQL server and optionally compares the output with a result file

mariadb-test-embedded

is similar to the mysqltest command but is built with support for the libmysqld embedded server

mariadb-tzinfo-to-sql

loads the time zone tables in the mysql database

mariadb-upgrade

examines all tables in all databases for incompatibilities with the current version of MySQL Server

mariadb-waitpid

signals a process to terminate and waits for the process to exit

mariadb_config

gets compiler flags for using the MariaDB Connector/C

mbstream

is an utility for sending InnoDB and XTraDB backups over a stream

msql2mysql

is a tool to convert mSQL programs for use with MySQL

my_print_defaults

displays the options from option groups of option files

myisam_ftdump

displays information about FULLTEXT indexes in MyISAM tables

myisamchk

gets information about your database tables or checks, repairs, or optimizes them

myisamlog

displays MyISAM log file contents

myisampack

is a tool for compressing MyISAM tables

mysql_config

provides you with useful information for compiling your MySQL client and connecting it to MySQL

mytop

is a console-based tool for monitoring the threads and overall performance of a MySQL server

perror

is a utility that displays descriptions for system or storage engine error codes

replace

is a MariaDB/MySQL extension to the SQL standard

resolve_stack_dump

resolves a numeric stack dump to symbols

resolveip

is a utility for resolving IP addresses to host names and vice versa

sst_sump

dumps the content of sst files (the format used by RocksDB)