{"type":"doc","content":[{"type":"paragraph","content":[{"text":"Note","type":"text","marks":[{"type":"strong"}]},{"text":": 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. ","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Installing Percona MySQL 8 on Centos/Red Hat (Recommended for new installs)","type":"text"}]},{"type":"codeBlock","attrs":{"language":"shell"},"content":[{"text":"#become root\nsudo su\n \n# remove any previous installs\nservice mysql stop\nyum remove mysql-dev mysql-server mysql-libs Percona-Server*\n \n#enable the repository\nsudo percona-release setup ps80\nOn RedHat 8 systems it is needed to disable dnf mysql module to install Percona-Server\nDo you want to disable it? [y/N] y\n...\n\n# remove an residual db info\nrm -rf /var/lib/mysql\nrm -f /etc/my.cnf\n \n# install Percona repos\nsudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm\n \n# install percona server (and client)\nsudo yum install percona-server-server\n \n# start the server\nservice mysql start\n \n# get the temp root password\ngrep -o \"temporary password is generated for root@localhost.*\" /var/log/mysqld.log | cut -f2- -d: | tail -1 | sed -e 's/^[[:space:]]*//'\n \n# secure the installation\nmysql_secure_installation -p\n \n# restart the server\nservice mysql restart","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Installing Percona MySQL 5.7 ","type":"text"}]},{"type":"codeBlock","attrs":{"language":"shell"},"content":[{"text":"#become root\nsudo su\n \n# remove any previous installs\nservice mysql stop\nyum remove mysql-dev mysql-server mysql-libs Percona-Server*\n \n# remove an residual db info\nrm -rf /var/lib/mysql\nrm -f /etc/my.cnf\n \n# install Percona repos\nyum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm\n \n# install percona server (and client)\nyum install Percona-Server-server-57\n \n# start the server\nservice mysql start\n \n# get the temp root password\ngrep -o \"temporary password is generated for root@localhost.*\" /var/log/mysqld.log | cut -f2- -d: | tail -1 | sed -e 's/^[[:space:]]*//'\n \n# secure the installation\nmysql_secure_installation -p\n \n# restart the server\nservice mysql restart","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Setting up the database","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Log In to the Database as root","type":"text"}]},{"type":"codeBlock","content":[{"text":"mysql -u root -p","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Run the Create Script MySQL 8","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"-- Create Test databases\nCREATE DATABASE pcr360_test DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\nCREATE DATABASE pcr360_test_archive DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\nCREATE DATABASE pcr360_test_metadata DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\n-- Create the users\nCREATE USER 'pcr360_test'@'%' IDENTIFIED WITH mysql_native_password BY '';\nCREATE USER 'pcr360_test'@'localhost' IDENTIFIED WITH mysql_native_password BY '';\nGRANT USAGE ON *.* TO 'pcr360_test'@'%';\nGRANT USAGE ON *.* TO 'pcr360_test'@'localhost';\nCREATE USER 'pcr360_test_metadata'@'%' IDENTIFIED WITH mysql_native_password BY '';\nCREATE USER 'pcr360_test_metadata'@'localhost' IDENTIFIED WITH mysql_native_password BY '';\nGRANT USAGE ON *.* TO 'pcr360_test_metadata'@'%';\nGRANT USAGE ON *.* TO 'pcr360_test_metadata'@'localhost';\nCREATE USER 'dmpusr'@'localhost' IDENTIFIED WITH mysql_native_password BY '';\nGRANT USAGE ON *.* TO 'dmpusr'@'localhost';\nGRANT PROCESS ON *.* TO 'dmpusr'@'localhost';\n -- Grant database privileges\nGRANT ALL PRIVILEGES ON pcr360_test.* TO 'pcr360_test'@'%';\nGRANT ALL PRIVILEGES ON pcr360_test.* TO 'pcr360_test'@'localhost';\nGRANT ALL PRIVILEGES ON pcr360_test_archive.* TO 'pcr360_test'@'%';\nGRANT ALL PRIVILEGES ON pcr360_test_archive.* TO 'pcr360_test'@'localhost';\nGRANT ALL PRIVILEGES ON pcr360_test_metadata.* TO 'pcr360_test_metadata'@'%';\nGRANT ALL PRIVILEGES ON pcr360_test_metadata.* TO 'pcr360_test_metadata'@'localhost';\nGRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_test.* TO 'dmpusr'@'localhost';\n\n-- Create Production databases \nCREATE DATABASE pcr360_prod DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\nCREATE DATABASE pcr360_prod_archive DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\nCREATE DATABASE pcr360_prod_metadata DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\n-- Create the users\nCREATE USER 'pcr360_prod'@'%' IDENTIFIED WITH mysql_native_password BY '';\nCREATE USER 'pcr360_prod'@'localhost' IDENTIFIED WITH mysql_native_password BY '';\nGRANT USAGE ON *.* TO 'pcr360_prod'@'%';\nGRANT USAGE ON *.* TO 'pcr360_prod'@'localhost';\nCREATE USER 'pcr360_prod_metadata'@'%' IDENTIFIED WITH mysql_native_password BY '';\nCREATE USER 'pcr360_prod_metadata'@'localhost' IDENTIFIED WITH mysql_native_password BY '';\nGRANT USAGE ON *.* TO 'pcr360_prod_metadata'@'%';\nGRANT USAGE ON *.* TO 'pcr360_prod_metadata'@'localhost';\nCREATE USER 'dmpusr'@'localhost' IDENTIFIED WITH mysql_native_password BY '';\nGRANT USAGE ON *.* TO 'dmpusr'@'localhost';\nGRANT PROCESS ON *.* TO 'dmpusr'@'localhost';\n -- Grant database privileges\nGRANT ALL PRIVILEGES ON pcr360_prod.* TO 'pcr360_prod'@'%';\nGRANT ALL PRIVILEGES ON pcr360_prod.* TO 'pcr360_prod'@'localhost';\nGRANT ALL PRIVILEGES ON pcr360_prod_archive.* TO 'pcr360_prod'@'%';\nGRANT ALL PRIVILEGES ON pcr360_prod_archive.* TO 'pcr360_prod'@'localhost';\nGRANT ALL PRIVILEGES ON pcr360_prod_metadata.* TO 'pcr360_prod_metadata'@'%';\nGRANT ALL PRIVILEGES ON pcr360_prod_metadata.* TO 'pcr360_prod_metadata'@'localhost';\nGRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_prod.* TO 'dmpusr'@'localhost';","type":"text"}]},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Run the Create Script MySQL 5.7","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"-- Create Test databases\nCREATE DATABASE pcr360_test DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\nCREATE DATABASE pcr360_test_archive DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\nCREATE DATABASE pcr360_test_metadata DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\n-- Create the users\nCREATE USER 'pcr360_test'@'%' IDENTIFIED BY '';\nGRANT USAGE ON *.* TO 'pcr360_test'@'%';\nGRANT USAGE ON *.* TO 'pcr360_test'@'localhost';\nCREATE USER 'pcr360_test_metadata'@'%' IDENTIFIED BY '';\nGRANT USAGE ON *.* TO 'pcr360_test_metadata'@'%';\nGRANT USAGE ON *.* TO 'pcr360_test_metadata'@'localhost';\nCREATE USER 'dmpusr'@'localhost' IDENTIFIED BY '';\nGRANT USAGE ON *.* TO 'dmpusr'@'localhost';\n-- Grant database privileges\nGRANT ALL PRIVILEGES ON pcr360_test.* TO 'pcr360_test'@'%' IDENTIFIED BY '';\nGRANT ALL PRIVILEGES ON pcr360_test.* TO 'pcr360_test'@'localhost' IDENTIFIED BY '';\nGRANT ALL PRIVILEGES ON pcr360_test_metadata.* TO 'pcr360_test_metadata'@'%' IDENTIFIED BY '';\nGRANT ALL PRIVILEGES ON pcr360_test_metadata.* TO 'pcr360_test_metadata'@'localhost' IDENTIFIED BY '';\nGRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_test.* TO 'dmpusr'@'localhost' IDENTIFIED BY '';\n\n-- Create Production databases\nCREATE DATABASE pcr360_prod DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\nCREATE DATABASE pcr360_prod_metadata DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; \nCREATE DATABASE pcr360_prod_archive DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\n\n-- Create the users\nCREATE USER 'pcr360_prod'@'%' IDENTIFIED BY '';\nGRANT USAGE ON *.* TO 'pcr360_prod'@'%';\nGRANT USAGE ON *.* TO 'pcr360_prod'@'localhost';\nCREATE USER 'pcr360_prod_metadata'@'%' IDENTIFIED BY '';\nGRANT USAGE ON *.* TO 'pcr360_prod_metadata'@'%';\nGRANT USAGE ON *.* TO 'pcr360_prod_metadata'@'localhost';\nCREATE USER 'dmpusr'@'localhost' IDENTIFIED BY '';\nGRANT USAGE ON *.* TO 'dmpusr'@'localhost';\n-- Grant database privileges\nGRANT ALL PRIVILEGES ON pcr360_prod.* TO 'pcr360_prod'@'%' IDENTIFIED BY '';\nGRANT ALL PRIVILEGES ON pcr360_prod.* TO 'pcr360_prod'@'localhost' IDENTIFIED BY '';\nGRANT ALL PRIVILEGES ON pcr360_prod_metadata.* TO 'pcr360_prod_metadata'@'%' IDENTIFIED BY '';\nGRANT ALL PRIVILEGES ON pcr360_prod_metadata.* TO 'pcr360_prod_metadata'@'localhost' IDENTIFIED BY '';\nGRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_prod.* TO 'dmpusr'@'localhost' IDENTIFIED BY '';\n","type":"text"}]},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"bodiedExtension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"excerpt","parameters":{"macroParams":{"atlassian-macro-output-type":{"value":"INLINE"}},"macroMetadata":{"macroId":{"value":"54ef6e42-34c7-46b7-ae39-b00ca2dc7cad"},"schemaVersion":{"value":"1"},"title":"Excerpt"}},"localId":"dd397412-c39c-4934-abc2-c975e8a25a83"},"content":[{"type":"heading","attrs":{"level":3},"content":[{"text":"Create stored functions and procedures","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"-- needs to be run on prod and test\n-- use pcr360_test;\nuse pcr360_prod;\n \n \nDROP FUNCTION IF EXISTS FN_FISCAL_YEAR;\nDELIMITER $$\nCREATE FUNCTION FN_FISCAL_YEAR (\n check_date DATETIME,\n rollover_month INT,\n rollover_day INT,\n roll_forward TINYINT(1)\n) RETURNS INT DETERMINISTIC\n BEGIN\n DECLARE cur_year INT DEFAULT EXTRACT(YEAR FROM CURRENT_DATE);\n # Adjust the current year: if FISCAL year rolls forward as the next ACTUAL year, this adds 1 to current.\n IF (roll_forward = 1) THEN\n SET cur_year := cur_year + 1;\n END IF;\n #\n RETURN cur_year - CAST(\n (DATEDIFF(\n check_date,\n CONCAT_WS('-', EXTRACT(YEAR FROM CURRENT_DATE), rollover_month, rollover_day)\n ) < 0)\n AS UNSIGNED\n );\n END$$\n# End of Function\nDELIMITER ;\n \nDROP PROCEDURE IF EXISTS AUDIT_CLEANUP;\nDELIMITER $$\nCREATE PROCEDURE AUDIT_CLEANUP(IN tableName varchar(40))\nBEGIN\n DECLARE done INT DEFAULT 0;\n DECLARE i INT;\n DECLARE tableRecId INT;\n DECLARE auditRecId INT;\n DECLARE fromData LONGTEXT;\n DECLARE toData LONGTEXT;\n DECLARE currRecId INT DEFAULT 0;\n DECLARE currKey VARCHAR(32);\n DECLARE deleteFlag INT DEFAULT 0;\n DECLARE keysMatch INT;\n \n -- Query the data from the view\n DECLARE curs CURSOR FOR\n SELECT RECID, AUDIT_RECID, FROM_DATA, TO_DATA FROM V_AUDIT_CLEANUP;\n \n DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;\n \n -- 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\n SET @v = concat(\n \"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 \",\n tableName, \" WHERE FROM_DATA IS NOT NULL ORDER BY JSON_EXTRACT(FROM_DATA, '$.RECID'), MODIFIED_DATE\");\n PREPARE stm FROM @v;\n EXECUTE stm;\n DEALLOCATE PREPARE stm;\n \n OPEN curs;\n \n SET done = 0;\n \n getRecsLoop:\n loop\n \n FETCH curs INTO tableRecId, auditRecId, fromData, toData;\n \n SET keysMatch = 1;\n \n -- 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\n IF auditRecId != currRecId\n THEN\n SET currRecId = auditRecId;\n SET deleteFlag = 0;\n END IF;\n \n -- So let's first figure out if the number of keys match in FROM_DATA and TO_DATA\n -- If the number of keys match then we need to check each individual value\n -- If the number of keys do not match then we can skip checking the individual values\n IF JSON_LENGTH(JSON_KEYS(fromData)) = JSON_LENGTH(JSON_KEYS(toData))\n THEN\n SET i = 0;\n jsonLoop:\n WHILE i < JSON_LENGTH(JSON_KEYS(fromData))\n DO\n SET currKey = JSON_EXTRACT(JSON_KEYS(fromData), CONCAT(\"$[\", i, \"]\"));\n \n -- 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\n -- https://pcr360.myjetbrains.com/youtrack/issue/PCR360-3188\n IF TRIM(BOTH '\"' FROM CAST(JSON_EXTRACT(fromData, CONCAT(\"$.\", currKey)) AS CHAR)) !=\n TRIM(BOTH '\"' FROM CAST(JSON_EXTRACT(toData, CONCAT(\"$.\", currKey)) as CHAR))\n THEN\n -- values don't match this is a valid record, unset the flag and leave the loop\n SET keysMatch = 0;\n LEAVE jsonLoop;\n END IF;\n SET i = i + 1;\n END WHILE jsonLoop;\n -- The number of keys don't match so this is a valid record\n ELSE\n SET keysMatch = 0;\n END IF;\n \n -- We have the same number of keys and all of the values match let's check if this is the oldest record\n IF keysMatch\n THEN\n -- We have ordered by modified date so the first record we come across will be the oldest so let's leave it alone\n IF deleteFlag = 0\n THEN\n SET deleteFlag = 1;\n -- We have already found the oldest record so let's delete the rest\n ELSE\n SET @sql = CONCAT(\"DELETE FROM \", tableName, \" WHERE RECID = ?\");\n PREPARE stm from @sql;\n SET @a = tableRecId;\n EXECUTE stm using @a;\n DEALLOCATE PREPARE stm;\n END IF;\n END IF;\n \n COMMIT;\n IF done\n THEN\n LEAVE getRecsLoop;\n END IF;\n \n END LOOP;\n \n CLOSE curs;\nEND$$\nDELIMITER ;\n \nDROP FUNCTION IF EXISTS fnStripTags;\nDELIMITER $$\ncreate function fnStripTags(Dirty varchar(4000)) returns varchar(4000) deterministic\nBEGIN\n DECLARE iStart, iEnd, iLength INT;\n WHILE Locate('<', Dirty) > 0 AND Locate('>', Dirty, Locate('<', Dirty)) > 0\n DO\n BEGIN\n SET iStart = Locate('<', Dirty), iEnd = Locate('>', Dirty, Locate('<', Dirty));\n SET iLength = (iEnd - iStart) + 1;\n IF iLength > 0\n THEN\n BEGIN\n SET Dirty = Insert(Dirty, iStart, iLength, '');\n END;\n END IF;\n END;\n END WHILE;\n RETURN Dirty;\nEND$$\nDELIMITER ;","type":"text"}]},{"type":"paragraph","content":[{"type":"hardBreak"}]}]},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Update the MySQL Configuration file Mysql 8","type":"text"}]},{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"include","parameters":{"macroParams":{"":{"value":"(2024.1) my.cnf settings MySQL 8"}},"macroMetadata":{"macroId":{"value":"a5ba0e94-4961-4b76-b674-710a7602eb67"},"schemaVersion":{"value":"1"},"title":"Include Page"}},"localId":"f9b03bcc-f4c4-47c3-81a6-4239e2a59107"}},{"type":"heading","attrs":{"level":2},"content":[{"text":"Update the MySQL Configuration file Mysql 5.7","type":"text"}]},{"type":"codeBlock","attrs":{"language":"shell"},"content":[{"text":"cat > /etc/mysql/my.cnf << SQL\n#\n# The Percona Server 5.7 configuration file.\n#\n[mysqld]\n#\n# Recommended Settings\n#\ninnodb_buffer_pool_size = 5600M # set value to max out at 70% of the total ram\ninnodb_log_file_size = 256M # set a hard limit on the log file size\ninnodb_log_buffer_size = 1M\ninnodb_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\ninnodb_flush_method = O_DIRECT # avoid double buffering\nsymbolic-links=0 # Disabling symbolic-links is recommended to prevent assorted security risks\n#\n# Make MySQL more strict (for future-proofing MySQL version upgrades)\n#\nsql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'\n#\n# Optional, but often useful, tweaks\n#\njoin_buffer_size = 16M\nkey_buffer_size = 4M\nlog_queries_not_using_indexes = 0\nlong_query_time = 7\nmax_allowed_packet = 32M\nmax_connections = 30\nquery_cache_limit = 2M\nquery_cache_min_res_unit = 2048\nquery_cache_size = 256M\nquery_cache_size = 67108864\nslow_query_log = 1\nslow_query_log_file=/var/log/mysqld-slow.log\ntable_definition_cache = 800\ntable_open_cache = 810\nthread_cache_size = 8\nSQL\n\nsystemctl restart mysql\n ","type":"text"}]},{"type":"paragraph","content":[{"type":"hardBreak"}]}],"version":1}

Browser not supported