Document toolboxDocument toolbox

(2024.1) MySQL

MySQL version 5.7 or greater is required.

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