Versions Compared

Version Old Version 25 New Version 26
Changes made by

Samuel Kirby

Chris Welford

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Parameter

Value

character_set_server

utf8mb4

collation_server

utf8mb4_bin

Example installation

...

Encrypted and non-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-20241113-160148.pngImage Added

Example installations

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

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

...

.

...

expand

Install

Code Block
languagebash
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
languagebash
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 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.

Restart MySQL:

Code Block
languagebash
sudo systemctl stop mysql.service
sudo systemctl start mysql.service

Hardening

To harden the installation, run:

Code Block
languagebash
sudo mysql_secure_installation

Respond to the questions in the following way:

Code Block
languagebash
- 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.

...