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 may need to change over time as the work carried out by PhixFlow increases.
The following the minimum requirements for a MariaDB installation to support PhixFlow.
Option | Setting |
Version | |
The following configuration parameters must be set in my.cnf | |
datadir | Ensure that the data dir 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 must be added to the [mysqld] section of the file. For details on why this setting is recommended - and the alternatives if you do not want to set in this my.cnf - see Running non-deterministic functions |
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:
|
The following settings are required for the PhixFlow database.
All of these parameters are reflected in the example MariaDB database creation command in Configure the Database. |
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 |
|---|---|
character_set_server | utf8 |
collation_server | utf8_bin |
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.
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.
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.
MariaDB is not compatible with on-access anti-virus software so this must be disabled for the MariaDB data folder(s).
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
Install the prerequisites and add the signing key:
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:
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:
sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] https://mirrors.ukfast.co.uk/sites/mariadb/repo/10.4/ubuntu bionic main' |
sudo apt-get update sudo apt install mariadb-server |
my.cnfOpen 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 |
For versions earlier than 10.6, the setting |
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 |
Check the |
Bounce MariaDB:
sudo systemctl stop mysql.service sudo systemctl start mysql.service |
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.
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).
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:
|