Upgrade (MySQL) database to Percona

Introduction

From version 11, Easy Redmine support only Percona database server. This manual guides you how to upgrade to this performance optimizing DB server.

The steps

1. Check your recent MySQL Version

Percona Server versions are drop-in compatible with their equivalent MySQL versions only. i.e. MySQL 5.6 should be replaced with Percona Server 5.6 only. Attempting to use mismatched versions may lead to table corruption or prevent the server from starting.

To check which version you are currently running, first connect to MySQL with your current root password:

mysql -u root -p

Then find the current installed version:

mysql -version

This should identify if you need to install Percona Server 5.5 or 5.6. The one edge case is if you are running MariaDB 10.0, which should be replaced with Percona Server 5.6. If you are running a version of MySQL older than 5.5, you should first upgrade MySQL to 5.5 or greater before continuing.

2. Remove MySQL

Before we install Percona server, we will need to remove any MySQL or MariaDB packages that are currently installed, as you should not attempt to run both concurrently on the same data.

!!! You should have a backup of your data and your configuration files before proceeding. !!!

Before uninstalling MySQL, it is advised to stop the database server to prevent data corruption in the event the process isn’t stopped safely during the package removal:

service mysql stop

For Debian based servers, the MySQL server and client packages need to be removed:

apt-get remove mysql-server mysql-client mysql-common

apt-get autoremove

3. Install Percona server

Percona Server may not be in your Linux distribution’s default repositories, as Percona manage their own repositories to ensure updates are pushed to users as quickly as possible. Therefore, we will need to manually add the Percona APT repositories before installation. Follow the instructions below for your server’s OS.

The Debian package released by Percona is signed, meaning APT needs to be informed of the new signing key:

apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

Before we take the next step, make sure you know the distribution you are currently using. If you’re uncertain which distribution version you are using, you can execute the following command:

lsb_release -c

Once you are sure which distribution you are running, we can add the new Percona repositories by appending the following lines to the /etc/apt/sources.list file:

nano /etc/apt/sources.list

Add these lines at the bottom of the file, making sure to replace DIST with your distribution name (that is, you would replace DIST with buster or bullseye etc.):

deb http://repo.percona.com/apt DIST main

deb-src http://repo.percona.com/apt DIST main

Once you have saved the sources file, the Percona packages should next be pinned to ensure that the packages from Percona will always be prioritized over any packages from your distribution’s default repositories. To do this, we first create a new preference file for APT:

touch /etc/apt/preferences.d/00percona.pref

Now open this file at /etc/apt/preferences.d/00percona.pref with your chosen text editor (Vim, nano, etc.), add the following lines, and save:

Package: *

Pin: release o=Percona Development Team

Pin-Priority: 1001

Finally, once the sources are added and pinned, the package list can be updated and we can install the Percona Server package.

(New Only) For a fresh Droplet, it is advised you install the percona-server-server virtual package, which will install the version of Percona Server recommended by the Percona team:

apt-get update

apt-get install percona-server-server

(Replacement Only) Refer to the MySQL or MariaDB version you located earlier. For replacing version 5.5 use the percona-server-server-5.5 package and percona-server-server-5.6 for 5.6. MariaDB 10.0 should be replaced with Percona Server 5.6.

apt-get update

apt-get install percona-server-server-5.6

If this command completes without errors, Percona Server will be installed and successfully running. However, if you get errors during installation, ensure that you have sufficient free memory. More information about any startup errors may be found in Percona Server’s log file at /var/log/mysqld.log.

(New Only) When installing on a fresh system, you may be asked during the installation process to set a root database user password. It is also recommended in this situation to run mysqlsecureinstallation to ensure no obvious security issues remain:

/usr/bin/mysql_secure_installation

4. Configure Percona

If you replaced an existing MySQL installation, you should have made a copy of your configuration file that can now be copied back. You can probably skip this section, although you may find the example settings useful.

However, if you are installing Percona Server on a fresh Droplet, you will need to add a configuration file, as Percona Server is currently running on default values that may not be optimal for your Droplet. Before we update the configuration for the first time, it is advised to stop Percona Server, as the PID file location may change. For future configuration changes, a simple restart should suffice after changes are made.

service mysql stop

Here is an example of configuration we are using on our cloud servers. This file should be written to /etc/mysql/my.cnf

[mysqld]

user                    = mysql

pid-file                = /var/run/mysqld/mysqld.pid

socket                  = /var/run/mysqld/mysqld.sock

port                    = 3306

basedir                 = /usr

datadir                 = /home/mysql

tmpdir                  = /tmp

#log                     = /var/log/mysql/mysqld.log

log-error               = /var/log/mysql/mysqld.err

tmp_table_size          = 128M

max_heap_table_size     = 128M

lc-messages-dir         = /usr/share/mysql

skip-external-locking

bind-address            = 0.0.0.0

key_buffer_size         = 64M

max_allowed_packet      = 64M

thread_stack            = 192K

thread_cache_size       = 8

# myisam-recover          = BACKUP      # unavaliable in new version

max_connections         = 1000

query_cache_limit       = 16M

query_cache_size        = 256M

query_cache_type        = 1

skip-character-set-client-handshake

collation_server        = utf8mb4_unicode_ci

character_set_server    = utf8mb4

# allow authentication based on the password hashing method

default_authentication_plugin=mysql_native_password

innodb_file_format      = BARRACUDA

innodb_file_per_table   = 1

innodb_buffer_pool_size = 4096M

# print innodb deadlocks to error log

innodb_print_all_deadlocks

# flush innodb once per second

innodb_flush_log_at_trx_commit = 2

# writes on diske in 8M blocks without cache

innodb_flush_method     = O_DIRECT

innodb_log_buffer_size  = 8M

innodb_log_file_size    = 1GB

# join performance

join_buffer_size        = 8M

sort_buffer_size        = 8M

table_open_cache        = 4M

open_files_limit        = 250000

# compatibility with version pre 5.6

show_compatibility_56   = on

sql_mode                = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

innodb_large_prefix     = 1

expire_logs_days        = 10

max_binlog_size         = 100M

slow_query_log          = 1

slow_query_log_file     = /var/log/mysql/mysql-slow.log

long_query_time         = 5

[isamchk]

key_buffer              = 16M

5. Check the installation

Now that we have Percona Server installed and running, we can ensure that everything has gone as planned by running a few final checks. First, connect to the database using the mysql client, logging in with your database root user password:

mysql -u root -p

Immediately we can see that the server version in the connection text is now specifying Percona Server. By using the SHOW VARIABLES command, we can dig into further details about the specific version that has been installed:

SHOW VARIABLES LIKE "version%";

Next, you can check that we are taking advantage of XtraDB for any InnoDB based tables:

SHOW STORAGE ENGINES\G

The result will show this block among many others:

...

*************************** 8. row ***************************

      Engine: InnoDB

     Support: DEFAULT

     Comment: Percona-XtraDB, Supports transactions, row-level locking, and foreign keys

Transactions: YES

          XA: YES

  Savepoints: YES

...

9 rows in set (0.00 sec) 

The comment field within the response shows that the XtraDB engine has been loaded as the engine for InnoDB based tables. As a final check, it is advised that you ensure all of your databases and tables are being read properly in the new server.

If all of these checks were passed, you now have Percona Server running successfully. However, if any of these checks were not successful, please ensure you have properly completed all prior steps of this article, paying particular attention to matching MySQL version numbers to Percona Server version numbers.