Functional Components of the Role

The DATA role can be split on one or more servers. A setup with only one DATA server is only recommended for very small installations. The division corresponds to the following functional components:

From the installation point of view, these functional components have no effect because a complete local (synchronized) version of the database is stored on each server in the network. The distribution of the functions results more from the point of view of the "consumers" in which it is possible to configure which server can be accessed for which tasks. In the Web application server, for example, it is possible to specify the database connection for the primary, reporting, and real-time areas separately, so that it is possible to distribute these roles among different servers.

The only aspect of the installation that is affected by the function distribution is that if you are distributing the functions to different servers, you will need to build and configure an appropriate MySQL replication setup. This means that the "Primary" function is forced to run on a Replication Master, while all other functions can run on Replication Slaves.

Another very special type of configuration is a special setup in which two servers are connected in a master-master replication (which in turn can be used as masters for other slaves). In such a highly available configuration, only one of the masters is used as "primary". The other would normally serve as a passive reserve of the HA cluster. However, it is advisable to use this resource more sensibly by having the passive master perform either the "reporting" or the "statistics" function. The function-related IP addresses are managed by the HA manager. Such a configuration has the advantage of offering a high degree of availability without being too wasteful with resources.

Common installation steps

Independent of the function that a DATA server is to take over, the following installation steps must first be carried out on both master and slave.

Linking the data area

Connect data area as described on the page Connection STORE (All Linux except STORE)

Installing the Software

To include the official MySQL software repositories and install the MySQL server, use the following commands:

MySQL 8.x 

yum -y install libaio
yum -y install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
yum -y install mysql-community-server

 MySQL 5.6

yum -y install http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
yum -y install mysql-community-server

Both variants

The MySQL Server service is added to the list of automatically starting services and started with the following command:

chkconfig mysqld on
service mysqld start

Next, the port shares for the MySQL server service must be entered and permanently stored in the firewall:

firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload

To simplify the configuration of the MySQL server, a directory is now created where modular configuration files can be stored. In order for these to be loaded from MySQL Server, an entry must be made in the main configuration file. This is done by entering the following commands:

mkdir /etc/my.cnf.d
cat <<EOFF >> /etc/my.cnf
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/my.cnf.d/
EOFF
semanage fcontext -a -t mysqld_etc_t "/etc/my\.cnf\.d(/.*)?"
restorecon -R -v /etc/my.cnf.d

These commands create the directory, add the load instruction for the modular configuration files to the main configuration file, create a SELINUX security share for the new configuration directory and generate the corresponding security labels.

Next, a modular configuration file with some commented relevant optimization settings is imported.

MySQL 8.x

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

The File /etc/mycnf.d/jtel-enhanced-8.cnf contains a number of well-commented configuration statements that can be used to optimize the functionality of the MySQL Server. Most of these instructions are commented upon. If necessary, these parameters should be adjusted with caution. However, the default values should be fine for most installations.

MySQL 5.6

wget -P /etc/my.cnf.d http://cdn.jtel.de/downloads/configs/jtel-enhanced.cnf

The File /etc/mycnf.d/jtel-enhanced.cnf contains a number of well-commented configuration statements that can be used to optimize the functionality of the MySQL Server. Most of these instructions are commented upon. If necessary, these parameters should be adjusted with caution. However, the default values should be fine for most installations.

Both Variants

Now the MySQL server must be restarted:

service mysqld restart

After the first start of the MySQL server, the access data for the root user must now be defined.

Since in MySQL a user account consists not only of a username but also of an origin address of the connection, another root user must be created to connect from any origin address. 

MySQL 8.x

MySQL 8.x speichert ein generiertes Passwort für den root Benutzer in der Datei /var/log/mysqld.log

Dieses Passwort muss als erstes extrahiert werden. Da es oft Sonderzeichen enthält, die nicht ohne Weiteres in die Kommandozeile eingegeben werden können, erfolgt die erste Anpassung durch manuelle Eingabe des Passwortes.

mysqladmin -u root -p password '<password>'

Anschließend wird folgende Befehlskette eingegeben um den weiteren User zu erstellen:

ACHTUNG: <password> mit den entsprechenden Passwort ersetzen.

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"

MySQL 5.6

ACHTUNG: <password> mit den entsprechenden Passwort ersetzen.

mysqladmin -u root password '<password>'
mysql -u root -p<password> -v -e"CREATE USER 'root'@'%' IDENTIFIED 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"

Beide Varianten

Als nächstes wird noch ein zusätzliches Plugin-Modul dem MySQL Server hinzugefügt. Dieses Modul wird ab jtel Software Version 3.06 für die Kommunikation mit weiteren Softwarekomponenten benötigt. Bei Neuinstallationen soll es aber auch dann installiert werden, wenn geplant ist, ältere Revisionen der Software einzuspielen, damit einem späteren Update nichts im Wege steht. Dies erfolgt durch folgende Befehle:

cp /home/jtel/shared/JTELCarrierPortal/Libraries/jtel_udf_udpsend/jtel_udf_udpsend.so /usr/lib64/mysql/plugin/
chown root:root /usr/lib64/mysql/plugin/jtel_udf_udpsend.so
chmod 755 /usr/lib64/mysql/plugin/jtel_udf_udpsend.so
chcon system_u:object_r:lib_t:s0  /usr/lib64/mysql/plugin/jtel_udf_udpsend.so

cp /home/jtel/shared/JTELCarrierPortal/Libraries/jtel_udf_udpsend/dummy/jtel_udf_udpsend.so /usr/lib64/mysql/plugin/
chown root:root /usr/lib64/mysql/plugin/jtel_udf_udpsend.so
chmod 755 /usr/lib64/mysql/plugin/jtel_udf_udpsend.so
chcon system_u:object_r:lib_t:s0  /usr/lib64/mysql/plugin/jtel_udf_udpsend.so

Um die zusätzliche Funktion den SQL Prozeduren verfügbar zu machen, muss noch folgender Befehl ausgeführt werden (<password> mit den entsprechenden Passwort ersetzen):

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'"

Anpassung my.cnf auf RAM des Servers

Damit der Server den zur Verfügung gestellten RAM vollständig nutzt, muss eine Konfiguration angepasst werden mit vi.

Diese Einstellung sollte ca. 3/4 des RAMs des Servers entsprechen, wobei 3-4 GB für mysql und andere Prozesse übrig bleiben sollten.

# For 8 GB RAM
innodb_buffer_pool_size = 5120M
 
# 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

MySQL Neustart

Als letztes wird der MySQL Server neu gestartet, damit alle Einstellungen übernommen werden:

service mysqld restart