Setup MySQL on Red Hat / CentOS Linux

Introduction

Out of the box, Senzing is configured to use an embedded SQLite database for the entity repository to accelerate getting started. This article describes the steps to configure Senzing to use MySQL as the entity repository.

CentOS 7 was used in testing the steps outlined herein and the latest version of MySQL available at the time; 8.0.12. This article assumes you are already a MySQL user or familiar with MySQL, it only briefly covers the installation steps of MySQL.

Link

For additional MySQL information:

• MySQL Homepage
• MySQL Documentation
• Repository Downloads
• Installing and Upgrading MySQL

 

Install and Basic MySQL Setup

The following is a brief overview of the steps required to install MySQL. For full details on installing MySQL on Linux please see the provided links above.

Warn

CentOS 7 will install MariaDB if you use yum to try and install MySQL. MariaDB is a fork of MySQL and is generally preferred on CentOS. If you'd like to use MariaDB we have an article specifically for MariaDB.

Download the repository installation package directly from the yum repository website or with the wget command.

Website Download

• MySQL Yum Repository
• Locate the Red Hat Enterprise Linux 7 / Oracle Linux 7 package
• Select Download
• If you don't have a MySQL account choose "No thanks, just start my download."

wget Download

• On the MySQL Yum Repository website locate the Red Hat Enterprise Linux 7 / Oracle Linux 7 package and then the package name in brackets under the title

• Use wget with this name string to directly download the package
  • wget https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
• Once downloaded add the repository information by installing the package, and then installing MySQL.
  • sudo yum localinstall mysql80-community-release-el7-1.noarch.rpm
  • sudo yum install mysql-community-server

Info

If Senzing is installed on a client machine accessing a MySQL database on another server, you must install the MySQL client packages on the client machine:
sudo yum install mysql-community-client mysql-community-common mysql-community-libs

 

• Start MySQL and check the status
  • sudo systemctl start mysqld
  • sudo systemctl status mysqld
  • If started successfully you will see output similar to the following

• MySQL will autostart at system boot, if you'd like to disable autostarting issue
  • sudo systemctl disable mysqld
• It is highly recommended to harden security settings. First acquire the default root password post installation and then run the MySQL security hardening script
  • sudo grep 'temporary password' /var/log/mysqld.log
  • sudo mysql_secure_installation
    • Use the temporary password shown from the grep command above
    • The new password must be at least 12 chars, one upper, one lower, one number and one special character
    • Answer 'Y' to all questions (unless your organization's policies differ)

Test Connection and Setup a Senzing User

Check the following permissions meet your organization's policies. The user name 'senzing' is used in the following outline, change as appropriate to the user you will be using Senzing with. 

• mysql -u root -p
  
  • You will be prompted for your new root password
• create user 'senzing'@'%' identified by '<user_password>';
  • Change <user_password> to your desired password to access MySQL
  • Password must be at least 12 chars, one upper, one lower, one number and one special character
• grant all privileges on G2.* to 'senzing'@'%';
• \q

• Test the new user and login ready to create the Senzing database
  • mysql -u senzing -p
    • You will be prompted for the password you used for <user_password> 
      
      

Create New Database & Add Senzing Schema

• Create the database to use as the Senzing entity repository
  
  • create database G2 character set=utf8 collate=utf8_bin;
  • show databases;
  • \q
•  Add Senzing schema to the new database
  • mysql -u senzing -p G2 < <project_path>/resources/schema/g2core-schema-mysql-create.sql

    Where:
    • senzing = User id previously used in above commands, substitute your user if different
    • -p = Will prompt for the password you used for <user_password>
    • <project_path> = Your Senzing project path

Configure G2Module.ini

Modify the ini file to reference the new MySQL database and schema. The file is located in <project_path>/etc/. You can comment out the current lines by prefixing them with # and adding the modified ones below.

• G2Module.ini - Change or add a new CONNECTION entry

  CONNECTION=mysql://senzing:password4g2@127.0.0.1:3306/?schema=G2

Where:

• senzing = Senzing user
• password4g2 = Password for the Senzing user
• 127.0.0.1 = Senzing database server address
• 3306 = MySQL port number

Configure Database Parameters

Set the database parameters for the Senzing workload. Be sure to stop and start MySQL for the changes to be effective.

Run a Test Load and Export

Perform a test load of the supplied sample data and perform an export to test the new database setup.

• Source setupEnv
  • cd <project_path>/python
  • . ../setupEnv
• Load the sample data
  • python3 G2Loader.py -P -p demo/sample/project.csv
• Create the export file
  • python3 G2Export.py