On this page we state the minimum requirements for a MySQL installation to support PhixFlow, and present an example installation.
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.
Windows/Antivirus
MySQL may not be compatible with on-access anti-virus software, so this must be disabled for the MySQL 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\[mysql install dir]\bin\mysqld.exe
Minimum requirements
The following are the minimum requirements for a MySQL installation to support PhixFlow.
Installation
Option | Setting |
|---|---|
Version | |
The following configuration parameters must be set in | |
| Ensure that the data directory being used has sufficient space for the initial period of PhixFlow operation. This is in the |
|
This must be added to the |
|
This must be in the |
The following configuration parameters can optionally be set in my.cnf | |
|
This must be in the 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:
[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 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:
Option | Setting |
|---|---|
|
|
|
|
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:
Install
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)
Check whether you have example configuration files included with your distrubtion under /etc/mysql/mariadb.conf.d - if you do, note that these may override settings you apply via other files.
Determine a suitable innodb_buffer_pool_size, see MySQL Planning. This can be specified in M (megabytes) or G (gigabytes).
You may want to override the default binary log retention settings. By default https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_expire_logs_seconds 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 below, 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 https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_log_bin but make sure to pay attention to how the variables relate to each other.
echo "[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 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 log_error = /var/log/mysql" | sudo tee /etc/mysql/conf.d/phixflow.cnf
Restart MySQL:
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 MySQL.
For Windows
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 installations are 32-bit but will work perfectly well on a 64-bit computer.
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
Allow the download to complete and start the installation package
At Choosing a Setup Type, choose Server only
Follow prompts to complete the installation
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,
by default, the root user doesn't allow remote access
You can add admin users using mysql:
cmd> mysql -uroot -p password: **** mysql> grant all privileges on *.* to 'auser'@'%' identified by 'apassword' with grant option;