(2024.1) MySQL

MySQL version 5.7 or greater is required.

1 Install Notes
2 Installation
3 Configuration
- 3.1 Run the Create Script MySQL 8
- 3.2 Run the Create Script MySQL 5.7
4 Set MySQL to Start on Boot

Install Notes

Included in the installation package is a file named mysql-create.sql; log into the main database and execute these commands. Also included is a file named mysql-create-metadata.sql; log into the metadata database and execute these commands.

Installation

We highly recommend installing MySQL on a dedicated database server. To communicate with MySQL on that server completing this installation will require the username, password, hostname and port (e.g. 3306).

Configuration

Configuring MySQL can be necessary to ensure optimal performance. These options are not required but could help with performance.

vi /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

max_allowed_packet = 32M
query_cache_size = 67108864
innodb_buffer_pool_size=1024M
sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
innodb_fast_shutdown=0

thread_cache_size = 8
max_connections = 30
key_buffer_size = 4M
query_cache_size = 256M
log_queries_not_using_indexes = 0
slow_query_log = 1
slow_query_log_file=/var/log/mysqld-slow.log
long_query_time = 7
table_open_cache = 810
table_definition_cache = 800
query_cache_min_res_unit = 2048
query_cache_limit = 2M
join_buffer_size = 16M
innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 1M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Run the Create Script MySQL 8

-- Create Test databases
CREATE DATABASE pcr360_test DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE pcr360_test_archive DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE pcr360_test_metadata DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Create the users
CREATE USER 'pcr360_test'@'%' IDENTIFIED WITH mysql_native_password BY '<password>';
CREATE USER 'pcr360_test'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';
GRANT USAGE ON *.* TO 'pcr360_test'@'%';
GRANT USAGE ON *.* TO 'pcr360_test'@'localhost';

CREATE USER 'pcr360_test_archive'@'%' IDENTIFIED WITH mysql_native_password BY '<password>';
CREATE USER 'pcr360_test_archive'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';
GRANT USAGE ON *.* TO 'pcr360_test_archive'@'%';
GRANT USAGE ON *.* TO 'pcr360_test_archive'@'localhost';

CREATE USER 'pcr360_test_metadata'@'%' IDENTIFIED WITH mysql_native_password BY '<password>';
CREATE USER 'pcr360_test_metadata'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';
GRANT USAGE ON *.* TO 'pcr360_test_metadata'@'%';
GRANT USAGE ON *.* TO 'pcr360_test_metadata'@'localhost';

CREATE USER 'dmpusr'@'localhost' IDENTIFIED WITH mysql_native_password BY '<dmpuser-password>';
GRANT USAGE ON *.* TO 'dmpusr'@'localhost';
GRANT PROCESS ON *.* TO 'dmpusr'@'localhost';
 -- Grant database privileges
GRANT ALL PRIVILEGES ON pcr360_test.* TO 'pcr360_test'@'%';
GRANT ALL PRIVILEGES ON pcr360_test.* TO 'pcr360_test'@'localhost';
GRANT ALL PRIVILEGES ON pcr360_test_archive.* TO 'pcr360_test'@'%';
GRANT ALL PRIVILEGES ON pcr360_test_archive.* TO 'pcr360_test'@'localhost';
GRANT ALL PRIVILEGES ON pcr360_test_metadata.* TO 'pcr360_test_metadata'@'%';
GRANT ALL PRIVILEGES ON pcr360_test_metadata.* TO 'pcr360_test_metadata'@'localhost';
GRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_test.* TO 'dmpusr'@'localhost';  
GRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_test_archive.* TO 'dmpusr'@'localhost';
GRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_test_metadata.* TO 'dmpusr'@'localhost';
 

-- Create Production databases 
CREATE DATABASE pcr360_prod DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE pcr360_prod_archive DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE pcr360_prod_metadata DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create the users
CREATE USER 'pcr360_prod'@'%' IDENTIFIED WITH mysql_native_password BY '<password>';
CREATE USER 'pcr360_prod'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';
GRANT USAGE ON *.* TO 'pcr360_prod'@'%';
GRANT USAGE ON *.* TO 'pcr360_prod'@'localhost';

CREATE USER 'pcr360_prod_archive'@'%' IDENTIFIED WITH mysql_native_password BY '<password>';
CREATE USER 'pcr360_prod_archive'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';
GRANT USAGE ON *.* TO 'pcr360_prod_archive'@'%';
GRANT USAGE ON *.* TO 'pcr360_prod_archive'@'localhost';

CREATE USER 'pcr360_prod_metadata'@'%' IDENTIFIED WITH mysql_native_password BY '<password>';
CREATE USER 'pcr360_prod_metadata'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';
GRANT USAGE ON *.* TO 'pcr360_prod_metadata'@'%';
GRANT USAGE ON *.* TO 'pcr360_prod_metadata'@'localhost';
CREATE USER 'dmpusr'@'localhost' IDENTIFIED WITH mysql_native_password BY '<dmpuser-password>';
GRANT USAGE ON *.* TO 'dmpusr'@'localhost';
GRANT PROCESS ON *.* TO 'dmpusr'@'localhost';

 -- Grant database privileges
GRANT ALL PRIVILEGES ON pcr360_prod.* TO 'pcr360_prod'@'%';
GRANT ALL PRIVILEGES ON pcr360_prod.* TO 'pcr360_prod'@'localhost';
GRANT ALL PRIVILEGES ON pcr360_prod_archive.* TO 'pcr360_prod'@'%';
GRANT ALL PRIVILEGES ON pcr360_prod_archive.* TO 'pcr360_prod'@'localhost';
GRANT ALL PRIVILEGES ON pcr360_prod_metadata.* TO 'pcr360_prod_metadata'@'%';
GRANT ALL PRIVILEGES ON pcr360_prod_metadata.* TO 'pcr360_prod_metadata'@'localhost';
GRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_prod.* TO 'dmpusr'@'localhost';
GRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_prod.* TO 'dmpusr'@'localhost';

Run the Create Script MySQL 5.7

Set MySQL to Start on Boot

1. Add it to chkconfig

2. Make sure it is in the chkconfig.

3. Set it to autostart