Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

The example installation incorporates the minimum requirements for MariaDB. It is suitable as an initial configuration for many instances of PhixFlow, but note that some of the options may need to change over time as the work carried out by PhixFlow increases.

Windows/Antivirus

MariaDB is not compatible with on-access anti-virus software, so this must be disabled for the MariaDB data folder(s).

For full details for creating an exception see https://support.microsoft.com/en-us/windows/add-an-exclusion-to-windows-security-811816c0-4dfd-af4a-47e4-c301afe13b26#:~:text=Go%20to%20Start%20%3E%20Settings%20%3E%20Update,%2C%20file%20types%2C%20or%20process. However, for example, if you are on Windows, and using Windows Defender, do the following:

  • Start → Settings → Update & Security → Windows Security → Virus & threat protection → [Virus & threat protection Manage settings] → Exclusions [Add or remove exclusions] → Add an exclusion

  • Select Process and enter the exact path and name of the executable for the service e.g. C:\Program Files\MariaDB 10.4\bin\mysqld.exe

Minimum requirements

The following are the minimum requirements for a MariaDB installation to support PhixFlow.

Installation

Option

Setting

Version

See System Requirements and Compatibility.

The following configuration parameters must be set in my.cnf

datadir

Ensure that the data directory being used has sufficient space for the initial period of PhixFlow operation.

This is in the [mysqld] section of the my.cnf file.

binlog_format

mixed

This must be added to the [mysqld] section of the my.cnf file.

wait_timeout

28800 (this is the default, but some installation processes set this to a much lower value)

This must be in the [mysqld] section of the my.cnf file.

The following configuration parameters can optionally be set in my.cnf

log_bin_trust_function_creators

1

If set in my.cnf, this This must be added to in the [mysqld] section of the my.cnf file.

For details on why this setting is recommended, and the alternatives if you do not want to set in this my.cnf, see below: https://phixflow.atlassian.net/wiki/spaces/HELPTRUNKR/pages/8642920464/MariaDB+installation#Running-non-deterministic-functions.This setting is needed because PhixFlow's migration scripts sometimes require the use of non-deterministic functions

Example

If you add all parameters above to my.cnf, including the optional parameters, then the [mysqld] section of your my.cnf file should look something like this:

Code Block
languagebash
[mysqld]
...
datadir = /var/lib/mysql
...
wait_timeout = 28800
...
binlog_format = mixed
log_bin_trust_function_creators = 1

Database

The following settings are required for the PhixFlow database. All of these parameters are reflected in the example MariaDB database creation command given here: Install the PhixFlow Database Schema.

...

Option

Setting

character_set_server

utf8

collation_server

utf8_bin

Running non-deterministic functions

PhixFlow's migration scripts sometimes require the use of non-deterministic functions. To run, these require either a setting (log_bin_trust_function_creators) to be updated, or to be run with sufficient privileges.

Permanent setting change to allow non-deterministic functions

You can permanently allow non-deterministic functions to run in migration scripts by updating the setting log_bin_trust_function_creators in the my.cnf file. This is convenient, because you don't have to remember to make any changes to your session when running PhixFlow migration scripts. If you don't want to make this permanent change, see the following section.

Instructions for setting log_bin_trust_function_creators in the my.cnf file are given above: https://phixflow.atlassian.net/wiki/spaces/HELPTRUNKR/pages/8642920464/MariaDB+installation#Installation.

Session change to allow non-deterministic functions

If you do not update the log_bin_trust_function_creators setting in my.cnf, you must make sure that your session either has this setting updated, or that you run migration scripts with sufficient privileges. Full details for doing this are given here: Upgrading PhixFlow.

Windows/Antivirus

MariaDB is not compatible with on-access anti-virus software, so this must be disabled for the MariaDB data folder(s).

Example installation

The following configuration is suggested as a guide. It incorporates the minimum requirements for MariaDB databases above.

It is based on an installation of MariaDB on the Ubuntu distribution of Linux. Installation on other distributions of Linux will follow a very similar pattern. For Debian-based distributions, many of the commands will be identical. For RHEL-based distributions, the commands will be similar, replacing apt with yum. However, in all cases, please check with in the MariaDB documentation (https://mariadb.com/kb/en/getting-installing-and-upgrading-mariadb/) to check the exact details for installation on your platform. In particular, instructions for downloading and installing tailored to your Linux distribution and target version of MariaDB are provided here: https://downloads.mariadb.org/mariadb/repositories.

For Ubuntu 18.04 and MariaDB 10.3/10.4:

Configure repository

  • Install the prerequisites and add the signing key:

    Code Block
    languagebash
    sudo apt-get install software-properties-common
    sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
  • Either add the repo for 10.3:

    Code Block
    languagebash
    sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] https://mirrors.ukfast.co.uk/sites/mariadb/repo/10.3/ubuntu bionic main'
  • Or 10.4:

    Code Block
    languagebash
    sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] https://mirrors.ukfast.co.uk/sites/mariadb/repo/10.4/ubuntu bionic main'

Install

Code Block
languagebash
sudo apt-get update
sudo apt install mariadb-server

Edit my.cnf

Open your my.cnf file:

Code Block
languagebash
sudo nano /etc/mysql/my.cnf

...

Code Block
languagebash
innodb_default_row_format = dynamic
innodb_file_per_table = 1
innodb_strict_mode = ONOFF
Note

Check the innodb_buffer_pool_size parameter is set to a suitable size depending on the specification of the server. See MariaDB planning

Bounce MariaDB:

Code Block
languagebash
sudo systemctl stop mysql.service
sudo systemctl start mysql.service

Hardening

To harden the installation, run:

...

If the first option does not come up, respond to Set root password? with y instead. This should only happen if you're installing an old version of MariaDB.

Post-install

Firewall

If you want to access your database from another server:

  • Open your firewall for inbound TCP connections to your server's port (default port is 3306).

Add Admin Users

You could now use the root user for everything, but

...