...

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

Minimum requirements

The following are the minimum requirements for a 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 file.

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

The [mysqld] section of the my.cnf file.
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

This must be in the

The [mysqld] section of the my.cnf file.

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

enounter

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

This must be in the

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:

[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 MySQL database creation command given 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:

Example installation

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

It 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.

For Ubuntu 22.04 and MySQL 8.0

...

Expand
Install Code Block language bash sudo apt-get update sudo apt install 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 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 innodb_buffer_pool_size = <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 = 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 distrubtion 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, see here: MySQL Planning. This can be specified

...

either with M (megabytes) or G (gigabytes). 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 (as described above) 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. 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

...

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 innodb_buffer_pool_size = <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 = 1 log_error = /var/log/mysql" | sudo tee /etc/mysql/conf.d/phixflow . cnf 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

...

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:

...