...
Installing Percona MySQL 8 on Centos/Red Hat (Recommended for new installs)
Code Block | ||||
---|---|---|---|---|
| ||||
#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
Code Block | ||||
---|---|---|---|---|
| ||||
#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 |
...
Log In to the Database as root
Code Block | |
---|---|
RDark | mysql -u root -p |
Run the Create Script MySQL 8
Code Block | ||||
---|---|---|---|---|
| ||||
-- 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
Code Block | ||||
---|---|---|---|---|
| ||||
-- 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>'; |
Excerpt | |||||||
---|---|---|---|---|---|---|---|
Create stored functions and procedures
|
Update the MySQL Configuration file Mysql 8
Include Page | ||||
---|---|---|---|---|
|
Update the MySQL Configuration file Mysql 5.7
Code Block | ||||
---|---|---|---|---|
| ||||
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 |
...