Versions Compared

Old Version 23

changes.mady.by.user Samuel Kirby

Saved on

compared with

New Version 24

changes.mady.by.user Samuel Kirby

Saved on

Key

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

...

Table of Contents
minLevel1
maxLevel6
include
outlinefalse
indent
stylediscnone
exclude
typelist
class
printabletrue

Minimum requirements

...

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 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 distrubtion distribution under /etc/mysql/mariadb.conf.d - if you do, note that these may override settings you apply via other files.

Determine To help determine a suitable innodb_buffer_pool_size, see here: MySQL Planning. This can be specified in 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. You may want to override the default binary log retention settings. By default 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/replicationconnection-options-binary-log.html#sysvar_binlog_expire_logs_seconds.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 belowabove, 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 but - 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.

...

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 RemovedImage Added

Allow the download to complete and start the installation package

  • At Choosing a Setup Type, choose Server only

Image RemovedImage 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 RemovedImage 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 RemovedImage 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 RemovedImage Added

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

Image RemovedImage 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
languagebash
[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
languagebash
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.

...