On this page we state the minimum requirements for a MariaDB MySQL installation to support PhixFlow, and present an example installation.

The example installation incorporates the minimum requirements for MariaDBMySQL. 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.

Table of Contents

minLevel	1
maxLevel	6
include
outline	false
indent
style	disc
exclude
type	list
class
printable	true

Minimum requirements

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

Installation

...

Option

...

The following parameters must be set:
Parameter
Value
Location
Notes
Version
See System Requirements and Compatibility.
`datadir`
The
following configuration parameters must be set in
`[mysqld]` section of the `my.cnf`
`datadir`
file.
Ensure that the data directory being used has sufficient space for the initial period of PhixFlow operation.
This is in the
`mixed`
This must be added to the
`binlog_format`
`mixed`
The `[mysqld]` section of the `my.cnf` file.
`binlog_format`
`sql_generate_invisible_primary_key`
`0`
The `[mysqld]` section of the `my.cnf` file.
`wait_timeout`
`28800`
The `[mysqld]` section of the `my.cnf` file.
`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
...
parameters can also optionally be set
...
:
Parameter
Value
Location
Notes
`log_bin_trust_function_creators`
`1`
If set in `my.cnf`, this must be added to the
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:
This setting is needed because PhixFlow's migration scripts sometimes require the use of non-deterministic functions. If you do not set this, then at some point you may encounter an upgrade where you are required to turn this setting on for the duration of the upgrade - this will be noted in the Special Upgrade Instructions .
`require_secure_transport`
`1`
The `[mysqld]` section of the `my.cnf` file.
This setting requires that all client connections are encrypted.

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

language	bash

[mysqld]
...
datadir = /var/lib/mysql
...
wait_timeout = 28800
...
binlog_format = mixed
log_bin_trust_function_creators = 1
sql_generate_invisible_primary_key = 0
require_secure_transport = 1
...

Database

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

You will need a database, and a login with all privileges on that database. The database should be created with the following options:

Option

Parameter

Setting

Value
`character_set_server`

utf8

utf8mb4

collation_server

utf8

utf8mb4_bin

...

Encrypted and non-

...

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 in MariaDB 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.

...

encrypted connections

Most cloud-native services (such as Amazon RDS for MySQL or Azure Database for MySQL) will require an encrypted database connection. To configure this, you will need the following settings:

require_secure_transport = 1 in the database - for many public cloud provider database services, this will be set by default
useSSL=true in the database URL used by PhixFlow to connect to MySQL

However, there are scenarios in which a secure connection is not required - such as when MySQL and Tomcat are both installed on the same server. For this type of configuration to work, you will need the following settings:

require_secure_transport = 0 in the database
allowPublicKeyRetreival=true in the database URL used by PhixFlow to connect to MySQL

For instructions on how to set the database URL, see here: Install the PhixFlow Webapp.

For official MySQL documentation on database connections, see here: https://dev.mysql.com/doc/refman/8.0/en/connection-options.html.

For Azure MySQL Flexible Server

Expand

For Azure Database for MySQL (including the Flexible Server option), the only setting you need to udpate is sql_generate_invisible_primary_key. For all other settings you can rely on the defaults.

You can update this (to OFF) in the Server Parameters section of the database server page in the Azure portal:

Image Added

Optional parameter updates

You can also update other parameters as needed to tune the database or comply with your company’s configuration standards, but this is optional. However, you must make sure that the minimum requirements as stated above (https://phixflow.atlassian.net/wiki/spaces/HELP12/pages/edit-v2/9615611787#Minimum-requirements) are still met.

Example installations

The following configurations are suggested as a guide. They incorporate the minimum requirements for MySQL databases above.

For Ubuntu 22.04 and MySQL 8.0

Expand
This example is based on an installation of MySQL Community Edition 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

...

MySQL documentation (https://

...

dev.mysql.com

...

/doc/refman/8.0/en/

...

linux-installation.html) to check the exact details for installation on your platform.

...

Configure repository

Install

...

Code Block

language	bash

sudo apt-get

...

update
sudo apt

...

install

...

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

Code Block

language	bash

sudo apt-get update
sudo apt install mariadb-server

Edit `my.cnf`

Open your my.cnf file:

Code Block

language	bash

sudo nano

mysql-server

Set configuration

The command below sets the configuration you need to run PhixFlow. This creates a PhixFlow specific configuration file (/etc/mysql/conf.d/phixflow.cnf) which will be included by the main configuration file (/etc/mysql/my.cnf

...

Code Block

language	bash

).

Code Block

language	bash

echo "[mysqld]

...

datadir = /var/lib/mysql

...

wait_timeout = 28800

...

binlog_format = mixed
log_bin_trust_function_creators = 1

...


sql_generate_invisible_primary_key = 0
binlog_expire_logs_seconds = 172800

...

slow_query_log = 1
long_query_time = 5

log

innodb_buffer_

slow

pool_

verbosity

size =

query_plan,explain

Info
For versions earlier than 10.6, the setting `expire_logs_days = 2` is used instead of `binlog_expire_logs_seconds`.

Also in the [mysqld] section check that the following are either present, and if not 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 BUFFER POOL SIZE>M OR <INNODB BUFFER POOL SIZE>G
innodb_default_row_format = dynamic
innodb_file_per_table = 1
innodb_strict_mode = OFF
table_open_cache = 2000
require_secure_transport =

ON

Note

Check the

 1
log_error = /var/log/mysql" | sudo tee /etc/mysql/conf.d/phixflow.cnf

Note
Check whether you have example configuration files included with your distribution under `/etc/mysql/mariadb.conf.d` - if you do, note that these may override settings you apply via other files.

To help determine a suitable innodb_buffer_pool_size

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

...

, see here: MySQL Planning. This can be specified either with M (megabytes) or G (gigabytes).

You may want to override the default binary log retention settings. By default, this is set to 30 days. Note that the binary logs for a busy database can become large - over 30 days, this could be equivalent to the size of the database itself, or even larger if you are deleting and inserting a large number of records.

The simplest option is to reduce the value of binlog_expire_logs_secondsto a smaller period - in the example above, this has been reduced to 172800, which is a period of 2 days.

If you do not intend to use binary logs for database recovery (i.e. you are relying on a different backup solution), you can turn binary logging off. See here for more details: https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_log_bin - make sure to pay attention to how the variables relate to each other.

Restart MySQL:

Code Block

language	bash

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

Hardening

To harden the installation, run:

Code Block

language	bash

sudo mysql_secure_installation

Respond to the questions in the following way:

Code Block

language	bash

- 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

...

MySQL.

For Windows

Expand

Install

MySQL includes many configuration options for installing on Windows. The guide below is a recommended installation for mostly MySQL installations on Windows, espcially for development and local testing. If you are installing MySQL on Windows for long term use in a corporate envrionment, make sure you have met any installation standards that apply to database installations in general, and MySQL installations in particular.

Go to https://dev.mysql.com/downloads/windows/installer/8.0.html and select either download - mysql-installer-web-community-8.0.23.msi is recommended if you have a good internet connection, otherwise choose mysql-installer-community-8.0.23.msi. Note that both of these installation packages are 32-bit but can install either the 32-bit or 64-bit versions of MySQL.

At the next page you are invited to create an account but you do not have to - note the link No thanks, just start my download at the bottom of the page

Image Added

Allow the download to complete and start the installation package

At Choosing a Setup Type, choose Server only

Image Added

On the Type and Networking step, select the installation type as appropriate; leave other settings on this page (including networking settings) as default

Image Added

Follow prompts to complete the installation, accepting defaults
- This will include setting a root password - make sure you note this down
- You may wish to turn off MySQL starting when you start your computer

Image Added

Add mysql to path

It is useful to add the mysql command to the path

From Settings, open Environment Variables
Edit the System variable Path

Image Added

Add the path C:\Program Files\MySQL\MySQL Server 8.0\bin

Image Added

Save your changes

Now you can refer to the program mysql from a CMD or PowerShell window

Add PhixFlow settings

Apply PhixFlow settings in the file C:\ProgramData\MySQL\MySQL Server 8.0\my.ini. At a minimum apply settings listed in https://phixflow.atlassian.net/wiki/spaces/HELP100/pages/9106727895/Install+MySQL#Installation above. The following is an example configuration.

Example configuration

Add the following settings in the [mysqld] section of the file

Code Block

language	bash

[mysqld]
wait_timeout = 28800
binlog_format = mixed
log_bin_trust_function_creators = 1
sql_generate_invisible_primary_key = 0
binlog_expire_logs_seconds = 172800
innodb_default_row_format = dynamic
innodb_strict_mode = OFF

Update the following settings, which should already be in the file:

innodb_buffer_pool_size to <INNODB BUFFER POOL SIZE>M OR <INNODB BUFFER POOL SIZE>G, e.g. for a development or test installation on a PC with 16 GB of memory, 1 GB; for full details, see MySQL Planning
table_open_cache to 2000

Note that the following settings are not mandatory for PhixFlow but are recommended; however, these are already set by default to PhixFlow’s recommended settings (i.e. you do not need to do anything to apply these)

Code Block

language	bash

slow_query_log = 1
long_query_time = 5
innodb_file_per_table = 1

Once you have updated this file, restart MySQL via services to pick up these settings.

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 it is better practice from a security viewpoint to create individual users with their own passwords. Also,

...

by default, the root user doesn't allow remote access.

You can add admin users using mysql:

...

Version	Old Version 6	New Version Current
Changes made by	Samuel Kirby	Chris Welford
Saved on	Jan 17, 2022	Nov 13, 2024

`sql_generate_invisible_primary_key`	`0`	The `[mysqld]` section of the `my.cnf` file.
`wait_timeout`	`28800`	The `[mysqld]` section of the `my.cnf` file.	`28800`

This setting is needed because PhixFlow's migration scripts sometimes require the use of non-deterministic functions. If you do not set this, then at some point you may encounter an upgrade where you are required to turn this setting on for the duration of the upgrade - this will be noted in the Special Upgrade Instructions .
`require_secure_transport`	`1`	The `[mysqld]` section of the `my.cnf` file.	This setting requires that all client connections are encrypted.

Versions Compared

Key

Minimum requirements

Installation

Example

Database

Encrypted and non-

Permanent setting change to allow non-deterministic functions

Session change to allow non-deterministic functions

Windows/Antivirus

Example installation

encrypted connections

For Azure MySQL Flexible Server

Optional parameter updates

Example installations

For Ubuntu 22.04 and MySQL 8.0

Configure repository

Install

Code Block
language bash
sudo apt-get

Install

Edit `my.cnf`

Set configuration

Hardening

For Windows

Install

Add mysql to path

Add PhixFlow settings

Example configuration

Post-install

Firewall

Add

admin users

Parameter	Value	Location	Notes
Version	See System Requirements and Compatibility.
`datadir`		The


`binlog_format`	`mixed`	The `[mysqld]` section of the `my.cnf` file.

Page Comparison

Versions Compared

Key

Minimum requirements

Installation

Example

Database

Encrypted and non-

Permanent setting change to allow non-deterministic functions

Session change to allow non-deterministic functions

Windows/Antivirus

Example installation

encrypted connections

For Azure MySQL Flexible Server

Optional parameter updates

Example installations

For Ubuntu 22.04 and MySQL 8.0

Configure repository

Install

Code Blocklanguagebashsudo apt-get

Install

Edit my.cnf

Set configuration

Hardening

For Windows

Install

Add mysql to path

Add PhixFlow settings

Example configuration

Post-install

Firewall

Add

admin users

Code Block
language bash
sudo apt-get

Edit `my.cnf`