Common Installation Tasks

These tasks are performed on all DB servers, no matter what role (master or slave) they are to perform.

Attach STORE

STORE must be mounted before installing this role. See Mounting STORE - All Linux except for STORE (Debian/Win2019).

Install MySQL

Configure Repositories

Enable the Debian MySQL 8.x repository and install it. Note, you will get asked some questions. Just get MySQL 8.x and say OK.

wget https://dev.mysql.com/get/mysql-apt-config_0.8.1817-1_all.deb
dpkg -i mysql-apt-config_0.8.1817-1_all.deb

Select debian buster as the desired repository

Select Ok to configure MySQL 8.x 

Remove the Temporary .deb File

rm -f mysql-apt-config_0.8.18-1_all.deb

Update the Package List and Install MySQL Server

apt-get update
apt-get -y install mysql-server

The debian installation will ask you some questions during installation:

• MySQL root Password
• Default authentication plugin - change to "Use Legacy Authentication Method"

The installer will also start and enable the mysql server.

Configure Firewall

Access to the MySQL Server is configured in the firewall:

ufw allow 3306/tcp

Or for a specific ethernet interface:

ufw allow in on eth1 to any port 3306 proto tcp

Configure MySQL

Configuration Files

The Debian MySQL 8.x creates a configuration directory /etc/mysql/mysql.conf.d/ when it installs. The jtel configuration file is downloaded and stored here. This file contains a lot of well commented settings which can be tweaked if required. The main parameter to be changed is the RAM usage of the sever. See below.

wget -P /etc/mysql/mysql.conf.d http://cdn.jtel.de/downloads/configs/jtel-enhanced-8-deb.cnf

Configure Server RAM

In order that the server makes the best use of the RAM available, the configuration file /etc/mysql/mysql.conf.d/jtel-enhanced-8-deb.cnf must be adapted.

The setting innodb_buffer_pool_size should be adapted to about 3/4 of the available RAM, but a minimum of 3-4 GB should remain for the use of other processes. For Servers with 4 GB RAM, this should not exceed 2 GB. 

# For 4 GB RAM
innodb_buffer_pool_size = 2048M

# For 8 GB RAM
innodb_buffer_pool_size = 4192M
 
# For 12 GB RAM
innodb_buffer_pool_size = 8192M
 
# For 16 GB RAM
innodb_buffer_pool_size = 12288M

...
# From 16 GB simply take 3/4 of the RAM

Restart MySQL

The MySQL server is restarted to load all configuration settings.

systemctl restart mysql

Configure Users

After the first restart, user access must be setup.

The following commands configure the users required:

mysql -u root -p<password> -v -e"CREATE USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '<password>'"
mysql -u root -p<password> -v -e"GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION"
mysql -u root -p<password> -v -e"FLUSH PRIVILEGES"

Install UDP Plugin

Next, the UDP plugin is installed. This is required for the communication with further software modules in the system, when certain DB changes are made.

The installation is slightly different for master and slave servers.

Installation

Master Server

cp /home/jtel/shared/JTELCarrierPortal/Libraries/jtel_udf_udpsend/jtel_udf_udpsend.so /usr/lib/mysql/plugin/
chown root:root /usr/lib/mysql/plugin/jtel_udf_udpsend.so
chmod 644 /usr/lib/mysql/plugin/jtel_udf_udpsend.so

Slave Server

cp /home/jtel/shared/JTELCarrierPortal/Libraries/jtel_udf_udpsend/dummy/jtel_udf_udpsend.so /usr/lib/mysql/plugin/
chown root:root /usr/lib/mysql/plugin/jtel_udf_udpsend.so
chmod 644 /usr/lib/mysql/plugin/jtel_udf_udpsend.so

Configuration

To configure the plugin, the following commands are executed:

mysql -u root -p<password> -v -e"DROP FUNCTION IF EXISTS udpsend"
mysql -u root -p<password> -v -e"CREATE FUNCTION udpsend RETURNS STRING SONAME 'jtel_udf_udpsend.so'"