Maintenance Expectations: Length & Downtime
Suggested Maintenance Duration: 90 minutes
Minimum Expected Downtime: ~30 minutes of service flapping
The expected MySQL service downtime for this procedure is less than 30 minutes of MySQL service availability going up and down repeatedly (flapping). This figure assumes that all goes smoothly and no unforeseen errors are encountered. The suggested maintenance length allows time for troubleshooting should error arise as well as an extra 30 minutes in the unlikely event that all changes need to be reverted and the maintenance plan aborted.
Maintenance Stages Overview
The plan is broken up into preparation tasks and stages. Preparation tasks should be completed prior to the start time of your maintenance window. Stages are conducted throughout the maintenance event window and walk through the entire procedure for accomplishing each important stage of the outline. Preparation – Pre Maintenance Tasks These two important tasks must be completed prior to the scheduled start time of your maintenance window:- Copy existing MySQL config file
- Stage backup of MySQL database data
cp -pv /etc/my.cnf{,.pre-percona.bak}
Stage backup of MySQL database data:
2) Run the following rsync to clone the MySQL data. [IMPORTANT: trailing slash required]
rsync -vaH /var/lib/mysql{,.pre-percona}/
Note:
Very Large databases will take considerably longer to complete so plan accordingly.
Stage 1 – Shutdown MySQL & Rerun Rsync Final Sync
The purpose of stage one is to ensure a pristine backup of the MySQL data is in place before proceeding. To accomplish this, it is necessary to completely stop MySQL services so all buffers are written and no additional changes are being written to the database. Once stopped, rsync is executed again to copy the differences between the now stopped pristine data and the target backup data. 1) Shutdown MySQL/MariaDB Servicesystemctl stop mysql
2) Rerun the following rsync to finalize the cloned data. [IMPORTANT: trailing slash required]
rsync -vaH /var/lib/mysql{,.pre-percona}/
3) Wait for rsync to complete before moving on to Stage 3.
Stage 2 – Replace MariaDB 5.5 Binaries With Percona 5.5
NOTE:
MariaDB 5.5 is the version installed with Onyx 11 CentOS 7, so this is the starting point.
rpm -e --nodeps mariadb mariadb-libs mariadb-server
2) Remove mariadb-devel package (do this as a separate command from above to avoid removal failure do to missing package)
rpm -e --nodeps mariadb-devel
3) Install Percona repository:
yum install https://www.percona.com/redir/downloads/percona-release/redhat/percona-release-0.1-4.noarch.rpm
4) Install Percona DB server:
yum install Percona-Server-client-55 Percona-Server-server-55 Percona-Server-shared-55
5) Execute the following commands (allows InnoDB plugin to load correctly)
mv /var/lib/mysql/ibdata1 /var/lib/mysql/ibdata1.bak
cp -a /var/lib/mysql/ibdata1.bak /var/lib/mysql/ibdata1
mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.bak
cp -a /var/lib/mysql/ib_logfile0.bak /var/lib/mysql/ib_logfile0
mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1.bak
cp -a /var/lib/mysql/ib_logfile1.bak /var/lib/mysql/ib_logfile1
6) Make sure that there are no processes listening on port 3306 and kill the process if any.
netstat -tulpn | grep 3306
7) Start MySQL (now Percona 5.5)
service mysql start
8) Run mysql_upgrade script
mysql_upgrade -uadmin -p`cat /etc/psa/.psa.shadow`
9) [Optional] Enable Percona XtraDB Cluster UDF from Percona Toolkit:
plesk db -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
plesk db -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"
plesk db -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"
10) Restart MySQL to complete upgrade procedure
systemctl start mysql
Stage 3 – Switch Plesk MySQL Package over to the Plesk MySQL Community package
Running Percona on Plesk is an unsupported configuration. For this reason, to satisfy dependency connections between the Plesk MySQL package and the new Percona binaries, it is necessary to switch the Plesk MySQL release over to using the Plesk MySQL Community release instead. The change is necessary to facilitate upgrading and downgrading Percona versions. 1) Create the necessary repo config file:printf '[PLESK_17_8_11-dist] %s\nname=PLESK_17_5_3 dist %s\nbaseurl=http://autoinstall.plesk.com/PSA_17.8.11/dist-rpm-CentOS-7-x86_64/ %s\nenabled=1 %s\ngpgcheck=1 %s\n' > /etc/yum.repos.d/plesk178.repo
2) Install “plesk-mysql-server-community” package from the new repository:
yum install plesk-mysql-server-community
3) Disable /etc/yum.repos.d/plesk178.repo file as it’s no longer needed:
mv /etc/yum.repos.d/plesk178.repo{,.disabled}
4) Remove ”plesk-mysql-server” package without touching dependencies.
rpm -e --nodeps plesk-mysql-server
Percona should now be installed and working on the base 5.5 version. The next stages are identical to the typical MySQL upgrade process where binaries are upgraded, and then the schema updates are made manually using the mysql_upgrade tool.
Upgrade Percona 5.5 to 5.6
1) Stop MySQL service:service mysql stop
2) Remove Percona 55 packages without dependencies:
rpm -e --nodeps Percona-Server-shared-55 Percona-Server-client-55 Percona-Server-server-55
3) Install Percona 56 packages
yum install Percona-Server-shared-56 Percona-Server-client-56 Percona-Server-server-56
4) Make sure that there are no processes listening on port 3306 and kill the process if any.
netstat -tulpn | grep 3306
5) Start the MySQL service:
service mysql start
6) Upgrade MySQL tables:
mysql_upgrade -uadmin -p`cat /etc/psa/.psa.shadow`
Stage 4 – Upgrade Percona 5.6 to 5.7
1) Stop MySQL service:service mysql stop
2) Remove Percona 56 packages without dependencies:
rpm -e --nodeps Percona-Server-shared-56 Percona-Server-client-56 Percona-Server-server-56
3) Install Percona 57 packages
yum install Percona-Server-shared-57 Percona-Server-client-57 Percona-Server-server-57
4) Make sure that there are no processes listening on port 3306 and kill the process if any.
netstat -tulpn | grep 3306
5) Start the MySQL service:
service mysql start
6) Upgrade MySQL tables:
mysql_upgrade -uadmin -p`cat /etc/psa/.psa.shadow`