When using MariaDB, the following initial database configuration is recommended. However, On this page we state the minimum requirements for a MariaDB installation to support PhixFlow, and present an example installation.

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 below may need to change over time as the work carried out by PhixFlow increases.

...

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.

...

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 in Upgrading PhixFlow.

Publishing space

Notes

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 distrubtion 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
  language bash
  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
  language bash
  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
  language bash
  sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] https://mirrors.ukfast.co.uk/sites/mariadb/repo/10.4/ubuntu bionic main'

Install

sudo apt-get update
sudo apt install mariadb-server

Edit my.cnf

Open your my.cnf file:

sudo nano /etc/mysql/my.cnf

In the [mysqld] section, update or add the below variables to match these values:

[mysqld]
...
datadir = /var/lib/mysql
...
wait_timeout = 28800
...
binlog_format = mixed
log_bin_trust_function_creators = 1
...
binlog_expire_logs_seconds = 172800
...
slow_query_log = 1
long_query_time = 5
log_slow_verbosity = query_plan,explain

Also in the [mysqld] section check that the following are either present, and if not add them (add them next to other innodb settings, although the placement of these does not actually matter, as long as they are in the [mysqld] section):

innodb_file_format = Barracuda
innodb_default_row_format = dynamic
innodb_file_per_table = 1
innodb_strict_mode = ON

Bounce MariaDB:

sudo systemctl stop mysql.service
sudo systemctl start mysql.service

Hardening

To harden the installation, run:

sudo mysql_secure_installation

Respond to the questions in the following way:

- Switch to unix_socket authentication [Y/n] y
- Set root password? [Y/n] n (although if you set a secure password on installation you can safely answer n)
- Remove anonymous users? [Y/n] y
- Disallow root login remotely? [Y/n] y
- Remove test database and access to it? [Y/n] y
- Reload privilege tables now? [Y/n] y

Switching to unix_socket authentication means there is no password for root, instead anybody with sudo access can log in using sudo mysql. 

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.