Percona Installation

Note: This page contains instructions for installing Percona with MySQL 8, or MySQL 5.7. Both Databases are supported by PCR-360, so please make sure to follow the instructions related to the version you are installing. MySQL 8 is the preferred installation option, as support for MySQL 5.7 will start to be replaced by MySQL 8.

Installing Percona MySQL 8 on Centos/Red Hat (Recommended for new installs)

BASH

#become root
sudo su
 
# remove any previous installs
service mysql stop
yum remove mysql-dev mysql-server mysql-libs Percona-Server*
 
#enable the repository
sudo percona-release setup ps80
On RedHat 8 systems it is needed to disable dnf mysql module to install Percona-Server
Do you want to disable it? [y/N] y
...

# remove an residual db info
rm -rf /var/lib/mysql
rm -f /etc/my.cnf
 
# install Percona repos
sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
 
# install percona server (and client)
sudo yum install percona-server-server
 
# start the server
service mysql start
 
# get the temp root password
grep -o "temporary password is generated for root@localhost.*" /var/log/mysqld.log | cut -f2- -d: | tail -1 | sed -e 's/^[[:space:]]*//'
 
# secure the installation
mysql_secure_installation -p
 
# restart the server
service mysql restart

Installing Percona MySQL 5.7

BASH

#become root
sudo su
 
# remove any previous installs
service mysql stop
yum remove mysql-dev mysql-server mysql-libs Percona-Server*
 
# remove an residual db info
rm -rf /var/lib/mysql
rm -f /etc/my.cnf
 
# install Percona repos
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
 
# install percona server (and client)
yum install Percona-Server-server-57
 
# start the server
service mysql start
 
# get the temp root password
grep -o "temporary password is generated for root@localhost.*" /var/log/mysqld.log | cut -f2- -d: | tail -1 | sed -e 's/^[[:space:]]*//'
 
# secure the installation
mysql_secure_installation -p
 
# restart the server
service mysql restart

Setting up the database

Log In to the Database as root

CODE

mysql -u root -p

Run the Create Script MySQL 8

SQL

-- 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_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';

-- 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_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';

Run the Create Script MySQL 5.7

SQL

-- 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 BY '<prod-password>';
GRANT USAGE ON *.* TO 'pcr360_test'@'%';
GRANT USAGE ON *.* TO 'pcr360_test'@'localhost';
CREATE USER 'pcr360_test_metadata'@'%' IDENTIFIED BY '<metadata-password>';
GRANT USAGE ON *.* TO 'pcr360_test_metadata'@'%';
GRANT USAGE ON *.* TO 'pcr360_test_metadata'@'localhost';
CREATE USER 'dmpusr'@'localhost' IDENTIFIED BY '<dmpuser-password>';
GRANT USAGE ON *.* TO 'dmpusr'@'localhost';
-- Grant database privileges
GRANT ALL PRIVILEGES ON pcr360_test.* TO 'pcr360_test'@'%' IDENTIFIED BY '<prod-password>';
GRANT ALL PRIVILEGES ON pcr360_test.* TO 'pcr360_test'@'localhost' IDENTIFIED BY '<prod-password>';
GRANT ALL PRIVILEGES ON pcr360_test_metadata.* TO 'pcr360_test_metadata'@'%' IDENTIFIED BY '<metadata-password>';
GRANT ALL PRIVILEGES ON pcr360_test_metadata.* TO 'pcr360_test_metadata'@'localhost' IDENTIFIED BY '<metadata-password>';
GRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_test.* TO 'dmpusr'@'localhost' IDENTIFIED BY '<dmpuser-password>';

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

-- Create the users
CREATE USER 'pcr360_prod'@'%' IDENTIFIED BY '<prod-password>';
GRANT USAGE ON *.* TO 'pcr360_prod'@'%';
GRANT USAGE ON *.* TO 'pcr360_prod'@'localhost';
CREATE USER 'pcr360_prod_metadata'@'%' IDENTIFIED BY '<metadata-password>';
GRANT USAGE ON *.* TO 'pcr360_prod_metadata'@'%';
GRANT USAGE ON *.* TO 'pcr360_prod_metadata'@'localhost';
CREATE USER 'dmpusr'@'localhost' IDENTIFIED BY '<dmpuser-password>';
GRANT USAGE ON *.* TO 'dmpusr'@'localhost';
-- Grant database privileges
GRANT ALL PRIVILEGES ON pcr360_prod.* TO 'pcr360_prod'@'%' IDENTIFIED BY '<prod-password>';
GRANT ALL PRIVILEGES ON pcr360_prod.* TO 'pcr360_prod'@'localhost' IDENTIFIED BY '<prod-password>';
GRANT ALL PRIVILEGES ON pcr360_prod_metadata.* TO 'pcr360_prod_metadata'@'%' IDENTIFIED BY '<metadata-password>';
GRANT ALL PRIVILEGES ON pcr360_prod_metadata.* TO 'pcr360_prod_metadata'@'localhost' IDENTIFIED BY '<metadata-password>';
GRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_prod.* TO 'dmpusr'@'localhost' IDENTIFIED BY '<dmpuser-password>';

Create stored functions and procedures

SQL

-- needs to be run on prod and test
-- use pcr360_test;
use pcr360_prod;
 
 
DROP FUNCTION IF EXISTS FN_FISCAL_YEAR;
DELIMITER $$
CREATE FUNCTION FN_FISCAL_YEAR (
    check_date DATETIME,
    rollover_month INT,
    rollover_day INT,
    roll_forward TINYINT(1)
) RETURNS INT DETERMINISTIC
    BEGIN
        DECLARE cur_year INT DEFAULT EXTRACT(YEAR FROM CURRENT_DATE);
        # Adjust the current year: if FISCAL year rolls forward as the next ACTUAL year, this adds 1 to current.
        IF (roll_forward = 1) THEN
            SET cur_year := cur_year + 1;
        END IF;
        #
        RETURN cur_year - CAST(
            (DATEDIFF(
                 check_date,
                 CONCAT_WS('-', EXTRACT(YEAR FROM CURRENT_DATE), rollover_month, rollover_day)
             ) < 0)
            AS UNSIGNED
        );
    END$$
# End of Function
DELIMITER ;
 
DROP PROCEDURE IF EXISTS AUDIT_CLEANUP;
DELIMITER $$
CREATE PROCEDURE AUDIT_CLEANUP(IN tableName varchar(40))
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE i INT;
    DECLARE tableRecId INT;
    DECLARE auditRecId INT;
    DECLARE fromData LONGTEXT;
    DECLARE toData LONGTEXT;
    DECLARE currRecId INT DEFAULT 0;
    DECLARE currKey VARCHAR(32);
    DECLARE deleteFlag INT DEFAULT 0;
    DECLARE keysMatch INT;
 
    -- Query the data from the view
    DECLARE curs CURSOR FOR
        SELECT RECID, AUDIT_RECID, FROM_DATA, TO_DATA FROM V_AUDIT_CLEANUP;
 
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
 
    -- We can't have a dynamic table name in a cursor so we create a view with the dynamic table name and the cursor queries the view
    SET @v = concat(
            "CREATE OR REPLACE VIEW V_AUDIT_CLEANUP AS SELECT RECID, JSON_EXTRACT(FROM_DATA, '$.RECID') AS AUDIT_RECID, JSON_REMOVE(FROM_DATA, '$.MODIFIED_DATE', '$.MODIFIED_BY') AS FROM_DATA, JSON_REMOVE(TO_DATA, '$.MODIFIED_DATE', '$.MODIFIED_BY') AS TO_DATA FROM ",
            tableName, " WHERE FROM_DATA IS NOT NULL ORDER BY JSON_EXTRACT(FROM_DATA, '$.RECID'), MODIFIED_DATE");
    PREPARE stm FROM @v;
    EXECUTE stm;
    DEALLOCATE PREPARE stm;
 
    OPEN curs;
 
    SET done = 0;
 
    getRecsLoop:
    loop
 
        FETCH curs INTO tableRecId, auditRecId, fromData, toData;
 
        SET keysMatch = 1;
 
        -- We need to keep track of the current recid so that we know when we get to a new set of records and reset the delete flag
        IF auditRecId != currRecId
        THEN
            SET currRecId = auditRecId;
            SET deleteFlag = 0;
        END IF;
 
        -- So let's first figure out if the number of keys match in FROM_DATA and TO_DATA
        -- If the number of keys match then we need to check each individual value
        -- If the number of keys do not match then we can skip checking the individual values
        IF JSON_LENGTH(JSON_KEYS(fromData)) = JSON_LENGTH(JSON_KEYS(toData))
        THEN
            SET i = 0;
            jsonLoop:
            WHILE i < JSON_LENGTH(JSON_KEYS(fromData))
                DO
                    SET currKey = JSON_EXTRACT(JSON_KEYS(fromData), CONCAT("$[", i, "]"));
 
                    -- I am casting the values to CHAR and trimming double quotes as there is an issue with ints being stored in the JSON as both ints and strings
                    -- https://pcr360.myjetbrains.com/youtrack/issue/PCR360-3188
                    IF TRIM(BOTH '"' FROM CAST(JSON_EXTRACT(fromData, CONCAT("$.", currKey)) AS CHAR)) !=
                       TRIM(BOTH '"' FROM CAST(JSON_EXTRACT(toData, CONCAT("$.", currKey)) as CHAR))
                    THEN
                        -- values don't match this is a valid record, unset the flag and leave the loop
                        SET keysMatch = 0;
                        LEAVE jsonLoop;
                    END IF;
                    SET i = i + 1;
                END WHILE jsonLoop;
            -- The number of keys don't match so this is a valid record
        ELSE
            SET keysMatch = 0;
        END IF;
 
        -- We have the same number of keys and all of the values match let's check if this is the oldest record
        IF keysMatch
        THEN
            -- We have ordered by modified date so the first record we come across will be the oldest so let's leave it alone
            IF deleteFlag = 0
            THEN
                SET deleteFlag = 1;
                -- We have already found the oldest record so let's delete the rest
            ELSE
                SET @sql = CONCAT("DELETE FROM ", tableName, " WHERE RECID = ?");
                PREPARE stm from @sql;
                SET @a = tableRecId;
                EXECUTE stm using @a;
                DEALLOCATE PREPARE stm;
            END IF;
        END IF;
 
        COMMIT;
        IF done
        THEN
            LEAVE getRecsLoop;
        END IF;
 
    END LOOP;
 
    CLOSE curs;
END$$
DELIMITER ;
 
DROP FUNCTION IF EXISTS fnStripTags;
DELIMITER $$
create function fnStripTags(Dirty varchar(4000)) returns varchar(4000) deterministic
BEGIN
    DECLARE iStart, iEnd, iLength INT;
    WHILE Locate('<', Dirty) > 0 AND Locate('>', Dirty, Locate('<', Dirty)) > 0
        DO
            BEGIN
                SET iStart = Locate('<', Dirty), iEnd = Locate('>', Dirty, Locate('<', Dirty));
                SET iLength = (iEnd - iStart) + 1;
                IF iLength > 0
                THEN
                    BEGIN
                        SET Dirty = Insert(Dirty, iStart, iLength, '');
                    END;
                END IF;
            END;
        END WHILE;
    RETURN Dirty;
END$$
DELIMITER ;

Update the MySQL Configuration file Mysql 8

These settings are for a standard hosted server with 2 CPUs and 8GB of RAM

Note: Changes from MySQL 5.7 or earlier

ERROR_FOR_DIVISION_BY_ZERO will eventually be fully deprecated and included in MySQL. This is covered by setting "STRICT_TRANS_TABLES" in the SQL mode. For now you will need to use all three of these values in the configuration.
NO_ZERO_DATE,NO_ZERO_IN_DATE, and ERROR_FOR_DIVISION_BY_ZERO
NO_AUTO_CREATE_USER should be removed, it is now the default behavior.
query_cache settings have been deprecated with MySQL 8
symbolic-links are disabled by default in MySQL 8 and it is not required to set this in the configuration file.

BASH

cat > /etc/mysql/my.cnf << SQL
#
# The Percona Server 8 configuration file.
#
[mysqld]
#
# Recommended Settings
#
default-authentication-plugin=mysql_native_password
innodb_buffer_pool_size = 5600M    # set value to max out at 70% of the total ram
innodb_log_file_size = 256M        # set a hard limit on the log file size
innodb_log_buffer_size = 1M
innodb_flush_log_at_trx_commit = 1 # this is actually the default, but I put it in here to make it easier to adjust, if needed
innodb_flush_method = O_DIRECT     # avoid double buffering
#
# Make MySQL more strict (for future-proofing MySQL version upgrades)
sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ALLOW_INVALID_DATES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO'
#
# Optional, but often useful, tweaks
#
join_buffer_size = 16M
key_buffer_size = 4M
log_queries_not_using_indexes = 0
long_query_time = 7
max_allowed_packet = 32M
max_connections = 30
slow_query_log = 1
slow_query_log_file=/var/log/mysqld-slow.log
table_definition_cache = 800
table_open_cache = 810
thread_cache_size = 8
SQL

systemctl restart mysql

Update the MySQL Configuration file Mysql 5.7

BASH

cat > /etc/mysql/my.cnf << SQL
#
# The Percona Server 5.7 configuration file.
#
[mysqld]
#
# Recommended Settings
#
innodb_buffer_pool_size = 5600M    # set value to max out at 70% of the total ram
innodb_log_file_size = 256M        # set a hard limit on the log file size
innodb_log_buffer_size = 1M
innodb_flush_log_at_trx_commit = 1 # this is actually the default, but I put it in here to make it easier to adjust, if needed
innodb_flush_method = O_DIRECT     # avoid double buffering
symbolic-links=0                   # Disabling symbolic-links is recommended to prevent assorted security risks
#
# Make MySQL more strict (for future-proofing MySQL version upgrades)
#
sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
#
# Optional, but often useful, tweaks
#
join_buffer_size = 16M
key_buffer_size = 4M
log_queries_not_using_indexes = 0
long_query_time = 7
max_allowed_packet = 32M
max_connections = 30
query_cache_limit = 2M
query_cache_min_res_unit = 2048
query_cache_size = 256M
query_cache_size = 67108864
slow_query_log = 1
slow_query_log_file=/var/log/mysqld-slow.log
table_definition_cache = 800
table_open_cache = 810
thread_cache_size = 8
SQL

systemctl restart mysql