(2022.1) Percona Upgrade to MySQL 8

Upgrading from MySQL 5.7 to MySQL 8 is extensively covered by MySQL and Percona documentation. Before you upgrade your systems, please review the following documentation based on your installation. Once you have reviewed the documentation and upgrade MySQL, you will need to update the configuration file my.cnf for PCR-360 to run properly. The new settings can be found at the bottom of this page and on the Percona Installation page.

Percona MySQL

Why Upgrade?

https://www.percona.com/blog/2021/04/02/upgrading-to-mysql-8/

Percona Server In-Place Upgrading

https://www.percona.com/doc/percona-server/8.0/upgrading_guide.html

MySQL Server

Note: Even if you have Percona installed, this is worth reviewing.

https://dev.mysql.com/doc/refman/8.0/en/upgrading.html

Troubleshooting

In one upgrade the mysql-community-tools did not uninstall with the rest of the old version. This led to an install failure for Percona 8. Using apt to remove the mysql-community-tools package before installing percona resolved the issue.

my.cnf settings for 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.

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