On Upgrades to Percona Server 5.7 GA: CentOS 6 Case

As you probably noted, Percona had finally released Percona Server 5.7 GA two days ago. I had not paid much attention to this branch during last month or so, but as it's GA now I decided to check what's there, how it works and install/upgrade different boxes and VMs to use it.

For testing purposes and for this series of blog posts I am going to build it from Git source (at least on Fedora Core 23), upgrade to it on 32-bit Ubuntu 15.04 VM, upgrade to it on 64-bit Ubuntu 14.04 laptop, upgrade to it on 64-bit CentOS 6.7 VM and maybe more. If any of the attempts to install/upgrade ends up with something worth sharing, I am going to write about the experience, problems (if any) and solutions.

Yesterday I've spent some time upgrading on Ubuntu 15.04 VM and some observations are discussed on Facebook already. Then I tried to upgrade on my main CentOS 6.7 VM, where I had a "full stack" of Percona software for Percona Server 5.6.28, including TokuDB. The idea was to make sure everything I need works as expected after the upgrade. In this post I am going to share the observations from that experince.

Smart user should definitely start with reading the manual, in this case - "Percona Server In-Place Upgrading Guide: From 5.6 to 5.7"? as this is what I planned to do, in-place upgrade. Reading and following the directions there can save you notable time, but I am goign to show what happens when one tries naive "trial and failure" approach.

So, without reading anything, I just logged in as root, run yum update and then checked what Percona software I had there:

[root@centos ~]# yum update
...
No Packages marked for Update
[root@centos ~]# rpm -qa | grep -i percona
Percona-Server-shared-56-5.6.28-rel76.1.el6.x86_64
Percona-Server-test-56-5.6.28-rel76.1.el6.x86_64
Percona-Server-tokudb-56-5.6.28-rel76.1.el6.x86_64
Percona-Server-56-debuginfo-5.6.28-rel76.1.el6.x86_64
percona-release-0.1-3.noarch
Percona-Server-shared-51-5.1.73-rel14.12.625.rhel6.x86_64
Percona-Server-server-56-5.6.28-rel76.1.el6.x86_64
percona-nagios-plugins-1.1.6-1.noarch
percona-toolkit-2.2.16-1.noarch
percona-xtrabackup-2.3.3-1.el6.x86_64
Percona-Server-client-56-5.6.28-rel76.1.el6.x86_64
Percona-Server-devel-56-5.6.28-rel76.1.el6.x86_64

Great, so automatic upgrade to 5.7 GA doesd NOT happen. First thing to take into account for naive user like me. Manual mentioned above wisely suggests, as a next step, to stop MySQL server and then remove all Percona Server software without dependecies:

$ rpm -qa | grep Percona-Server | xargs rpm -e --nodeps

But I do not read the manuals (as many users) and previously upgrades worked for me without removing everything, so I just proceeded in a straighforward manner:

[root@centos ~]# yum install Percona-Server-server-57
...
--> Finished Dependency Resolution
Error: Percona-Server-client-57 conflicts with Percona-Server-client-56-5.6.28-rel76.1.el6.x86_64
Error: Percona-Server-server-57 conflicts with Percona-Server-server-56-5.6.28-rel76.1.el6.x86_64
Error: Percona-Server-shared-51 conflicts with Percona-Server-shared-57-5.7.10-3.1.el6.x86_64
 You could try using --skip-broken to work around the problem
** Found 1 pre-existing rpmdb problem(s), 'yum check' output follows:
Percona-Server-shared-51-5.1.73-rel14.12.625.rhel6.x86_64 has installed conflicts mysql-libs: Percona-Server-shared-51-5.1.73-rel14.12.625.rhel6.x86_64

So, removing seems unavoidable and I proceeded step by step (surely I had not cared about dependencies, who do, yum will tell us anyway or fix it for us, right?):

[root@centos ~]# yum remove Percona-Server-server-56
...
Removed:
  Percona-Server-server-56.x86_64 0:5.6.28-rel76.1.el6

Dependency Removed:
  Percona-Server-tokudb-56.x86_64 0:5.6.28-rel76.1.el6

Complete!

Let's remember that we need to install TokuDB for 5.7 separately, and proceed:

[root@centos ~]# yum remove Percona-Server-client-56 Percona-Server-shared-51
...
Transaction Summary
================================================================================
Remove        8 Package(s)

Installed size: 369 M
...
Removed:
  Percona-Server-client-56.x86_64 0:5.6.28-rel76.1.el6
  Percona-Server-shared-51.x86_64 0:5.1.73-rel14.12.625.rhel6

Dependency Removed:
  Percona-Server-test-56.x86_64 0:5.6.28-rel76.1.el6
  percona-toolkit.noarch 0:2.2.16-1
  percona-xtrabackup.x86_64 0:2.3.3-1.el6
  perl-DBD-MySQL.x86_64 0:4.013-3.el6
  php-mysql.x86_64 0:5.3.3-46.el6_7.1
  sysbench.x86_64 0:0.5-6.el6

Complete!

As we had not cared much about dependencies, we've got a lot of software removed that depended on old shared libraries. Let's save the list to install it back later. Now, we can try to install Percona Server 5.7 again:

[root@centos ~]# yum install Percona-Server-server-57
...
Dependencies Resolved

================================================================================
 Package                  Arch   Version           Repository              Size
================================================================================
Installing:
 Percona-Server-server-57 x86_64 5.7.10-3.1.el6    percona-release-x86_64  40 M
Installing for dependencies:
 Percona-Server-client-57 x86_64 5.7.10-3.1.el6    percona-release-x86_64 8.7 M
 Percona-Server-shared-57 x86_64 5.7.10-3.1.el6    percona-release-x86_64 902 k

Transaction Summary
================================================================================
Install       3 Package(s)
...

Transaction Check Error:
  file /usr/bin/mysql_config from install of Percona-Server-client-57-5.7.10-3.1.el6.x86_64 conflicts with file from package Percona-Server-devel-56-5.6.28-rel76.1.el6.x86_64

Error Summary
-------------

Great, so we also have a conflict with Percona-Server-devel-56 package that yum had not informed us about initially (Percona manual was probably right, remove them all, Percona-Srerver-* pacvkages first!). Let's remember to install it back for 5.7, remove it and install Percona Server 5.7 finally:

[root@centos ~]# yum remove Percona-Server-devel-56
...
Removed:
  Percona-Server-devel-56.x86_64 0:5.6.28-rel76.1.el6

Complete!
[root@centos ~]# yum install Percona-Server-server-57
...
Dependencies Resolved

================================================================================
 Package                  Arch   Version           Repository              Size
================================================================================
Installing:
 Percona-Server-server-57 x86_64 5.7.10-3.1.el6    percona-release-x86_64  40 M
Installing for dependencies:
 Percona-Server-client-57 x86_64 5.7.10-3.1.el6    percona-release-x86_64 8.7 M
 Percona-Server-shared-57 x86_64 5.7.10-3.1.el6    percona-release-x86_64 902 k

Transaction Summary
================================================================================
Install       3 Package(s)
...
Installed:
  Percona-Server-server-57.x86_64 0:5.7.10-3.1.el6

Dependency Installed:
  Percona-Server-client-57.x86_64 0:5.7.10-3.1.el6
  Percona-Server-shared-57.x86_64 0:5.7.10-3.1.el6

Complete!

Now, let's try to use it:

[root@centos ~]# mysql -uroot test
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
[root@centos ~]# ps aux | grep mysql
root      2665  0.0  0.0 103304   884 pts/0    S+   21:09   0:00 grep mysql

So, it is NOT started, as we had NOT upgraded it, we removed Percona Server 5.6.x first! Let's start the server now (at this step on recent Ubuntu etc you may find yourself in troubles):

[root@centos ~]# service mysql start
Starting mysqld:                                           [  OK  ]
[root@centos ~]# mysql -uroot  test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.10-3-log Percona Server (GPL), Release 3, Revision 63dafaf

Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

So, I've got it up and running. Now let me install some software back to replace those older packages I've removed:

[root@centos ~]# yum install Percona-Server-devel-57 Percona-Server-test-57 Percona-Server-tokudb-57
...

Installed:
  Percona-Server-devel-57.x86_64 0:5.7.10-3.1.el6
  Percona-Server-test-57.x86_64 0:5.7.10-3.1.el6
  Percona-Server-tokudb-57.x86_64 0:5.7.10-3.1.el6

Complete!

So, we have missing Percona Server packages for 5.7, now let me try to install dependecies back:

[root@centos ~]# yum install percona-toolkit percona-xtrabackup perl-DBD-MySQL php-mysql sysbench
...
Installed:
  percona-toolkit.noarch 0:2.2.16-1    percona-xtrabackup.x86_64 0:2.3.3-1.el6
  perl-DBD-MySQL.x86_64 0:4.013-3.el6  php-mysql.x86_64 0:5.3.3-46.el6_7.1
  sysbench.x86_64 0:0.5-6.el6

Dependency Installed:
  Percona-Server-shared-51.x86_64 0:5.1.73-rel14.12.625.rhel6

Complete!

Works well! Now, let me check if "full stack" works as expected. I had xtrabackup and toolkit installed, so do we have xtrabackup capable to work with 5.7 GA as result? Let's check:

[root@centos ~]# xtrabackup --version
xtrabackup version 2.3.3 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 525ca7d)
[root@centos ~]# xtrabackup --backup
160224 21:28:08  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock' as 'mysql'  (using password: NO).
160224 21:28:08  version_check Connected to MySQL server
160224 21:28:08  version_check Executing a version check against the server...
DBD::mysql::db selectall_hashref failed: Table 'performance_schema.session_variables' doesn't exist at - line 1221.
160224 21:28:10  version_check Done.
160224 21:28:10 Connecting to MySQL server host: localhost, user: mysql, password: not set, port: 0, socket: /var/lib/mysql/mysql.sock
Error: failed to execute query SHOW VARIABLES: Table 'performance_schema.session_variables' doesn't exist

Well, I was not sure (let's assume) if xtrabackup 2.3.3 is supposed to work with 5.7 (it was released recently, no upgrade happened, why not?), so I tried to use it. But it does not work as we do not have that performance_schema.session_variables table. Surely it's not there, it does not exist in MySQL 5.6. We missed important step that manual clearly mentiones, running mysql_upgrade and restart of service:

[root@centos ~]# mysql_upgrade
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
...
Upgrade process completed successfully.
Checking if update is needed.
[root@centos ~]# service mysql restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

Let's try again now:

[root@centos ~]# xtrabackup --backup
160224 21:31:00  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock' as 'mysql'  (using password: NO).
160224 21:31:00  version_check Connected to MySQL server
160224 21:31:00  version_check Executing a version check against the server...
160224 21:31:00  version_check Done.
160224 21:31:00 Connecting to MySQL server host: localhost, user: mysql, password: not set, port: 0, socket: /var/lib/mysql/mysql.sock
Error: Unsupported server version: '5.7.10-3-log'. Please report a bug at https://bugs.launchpad.net/percona-xtrabackup

No luck. Indeed, as anyone can find out after some reading, only Percona Xtrabackup 2.4 (that was also released as GA on February 23) is supposed to work with Percona Server or MySQL 5.7. We have to install it explicitly as "it was decided" that Percona does not want it to be used by default, yet:

[root@centos ~]# yum install percona-xtrabackup-24
...
--> Processing Conflict: percona-xtrabackup-24-2.4.1-1.el6.x86_64 conflicts percona-xtrabackup
--> Finished Dependency Resolution
Error: percona-xtrabackup-24 conflicts with percona-xtrabackup-2.3.3-1.el6.x86_64
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest
[root@centos ~]# yum remove percona-xtrabackup
...
Removed:
  percona-xtrabackup.x86_64 0:2.3.3-1.el6

Complete!
[root@centos ~]# yum install percona-xtrabackup-24
...
Running Transaction
  Installing : percona-xtrabackup-24-2.4.1-1.el6.x86_64                     1/1
  Verifying  : percona-xtrabackup-24-2.4.1-1.el6.x86_64                     1/1

Installed:
  percona-xtrabackup-24.x86_64 0:2.4.1-1.el6

Complete!

So, one has to explicitly remove percona-xtrabackup package and then install percona-xtrabackup-24. Now we can finally backup our new Percona Server 5.7 online (after exp;licitly specifying root user for backup, I wonder why, and doing some cleanup from the previous failed attempt):

[root@centos ~]# xtrabackup --backup
160224 21:35:46  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock' as 'mysql'  (using password: NO).
160224 21:35:46  version_check Connected to MySQL server
160224 21:35:46  version_check Executing a version check against the server...
160224 21:35:46  version_check Done.
160224 21:35:46 Connecting to MySQL server host: localhost, user: mysql, password: not set, port: 0, socket: /var/lib/mysql/mysql.sock
Using server version 5.7.10-3-log
xtrabackup version 2.4.1 based on MySQL server 5.7.10 Linux (x86_64) (revision id: a2dc9d4)
xtrabackup: uses posix_fadvise().
...
160224 21:35:53 Executing LOCK TABLES FOR BACKUP...
Error: failed to execute query LOCK TABLES FOR BACKUP: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
[root@centos ~]# xtrabackup --user=root --backup
...
xtrabackup: Can't create/write to file '/root/xtrabackup_backupfiles/xtrabackup_logfile' (Errcode: 17 - File exists)
xtrabackup: error: failed to open the target stream for 'xtrabackup_logfile'.
[root@centos ~]# rm -rf xtrabackup_backupfiles/
[root@centos ~]# xtrabackup --user=root --backup
...
160224 21:37:40 Backup created in directory '/root/xtrabackup_backupfiles/'
160224 21:37:40 [00] Writing backup-my.cnf
160224 21:37:40 [00]        ...done
160224 21:37:40 [00] Writing xtrabackup_info
160224 21:37:40 [00]        ...done
xtrabackup: Transaction log of lsn (1018105090) to (1018105141) was copied.
160224 21:37:40 completed OK!

Now the last step for this post, let's try if magic tools from Percona Toolkit works with 5.7, at least pt-mysql-summary is something to test for sure:

[root@centos ~]# pt-mysql-summary
# Percona Toolkit MySQL Summary Report #######################
              System time | 2016-02-24 19:38:09 UTC (local TZ: EET +0200)
# Instances ##################################################
  Port  Data Directory             Nice OOM Socket
  ===== ========================== ==== === ======
        /var/lib/mysql             0    0   /var/lib/mysql/mysql.sock
# MySQL Executable ###########################################
       Path to executable | /usr/sbin/mysqld
              Has symbols | No
# Report On Port 3306 ########################################
                     User | root@localhost
                     Time | 2016-02-24 21:38:09 (EET)
                 Hostname | centos
                  Version | 5.7.10-3-log Percona Server (GPL), Release 3, Revision 63dafaf
                 Built On | Linux x86_64
                  Started | 2016-02-24 21:30 (up 0+00:07:22)
                Databases | 6
                  Datadir | /var/lib/mysql/
                Processes | 1 connected, 1 running
              Replication | Is not a slave, has 0 slaves connected
                  Pidfile | /var/run/mysqld/mysqld.pid (exists)
...

So, it works and recongnizes the version properly, but at this moment I've noted I miss debug symbols for 5.7 (and I'll need them as soon as I attach gdb to running server, something I do on a regular basis). So, I have to install them:

[root@centos ~]# yum install Percona-Server-57-debuginfo
...
  file /usr/lib/debug/usr/share/mysql-test/lib/My/SafeProcess/my_safe_process.debug from install of Percona-Server-57-debuginfo-5.7.10-3.1.el6.x86_64 conflicts with file from package Percona-Server-56-debuginfo-5.6.28-rel76.1.el6.x86_64

Error Summary
-------------
[root@centos ~]# yum remove Percona-Server-56-debuginfo
Removed:
  Percona-Server-56-debuginfo.x86_64 0:5.6.28-rel76.1.el6

Complete!
[root@centos ~]# yum install Percona-Server-57-debuginfo
...
Installed:
  Percona-Server-57-debuginfo.x86_64 0:5.7.10-3.1.el6

Complete!
[root@centos ~]# rpm -qa | grep -i percona
Percona-Server-shared-56-5.6.28-rel76.1.el6.x86_64
Percona-Server-shared-57-5.7.10-3.1.el6.x86_64
Percona-Server-test-57-5.7.10-3.1.el6.x86_64
percona-toolkit-2.2.16-1.noarch
percona-xtrabackup-24-2.4.1-1.el6.x86_64
Percona-Server-tokudb-57-5.7.10-3.1.el6.x86_64
percona-release-0.1-3.noarch
percona-nagios-plugins-1.1.6-1.noarch
Percona-Server-server-57-5.7.10-3.1.el6.x86_64
Percona-Server-shared-51-5.1.73-rel14.12.625.rhel6.x86_64
Percona-Server-client-57-5.7.10-3.1.el6.x86_64
Percona-Server-devel-57-5.7.10-3.1.el6.x86_64
Percona-Server-57-debuginfo-5.7.10-3.1.el6.x86_64

So, after dealing with one more conflict I seem to have a "full stack" of Percona tools that are ready to work with Percona Server 5.7 GA.

To summarize my experience of a naive user, one should note the following while upgrading to Percona Server 5.7 on CentOS 6.x:

1. Read the manual about upgrade carefully and don't ignore it. You'll have to remove all Percona-Server-* packages first, deal with dependencies somehow and then install all Percona-Server-57-* packages you need.
2. mysql_upgrade is NOT executed automatically upon installation when there is some older datadir from the previous version. You have to execute it explicitly, check the outputs and then restart mysql service before any further use.
3. If you need to use xtrabackup with Percona Server 5.7, you have to remove percona-xtrabackup package and then install percona-xtrabackup-24 explicitly.

I hope this step by step guide will be useful to some of you. I'll try to upgrade "full stack" of Percona software on 64-bit Ubuntu 14.04 laptop tonight and carefully note all steps. I may end up with the next blog post in this series after that, depending on the results, so stay tuned!