In-place upgrade for Percona XtraDB Cluster 5.6.24-72.2 -> 5.6.32-25.17

[Marko Sutic]

Hi all,

Can I perform in-place upgrade of Percona XtraDB Cluster from 5.6.24-72.2 -> 5.6.32-25.17 using this method:

1. On node1 stop mysqld process

2. Remove old binaries

3. Install new version binaries

4. Start node outside of the cluster and run mysql_upgrade

5. Join node to existing cluster

Then perform exact steps for other two nodes.

Are steps 2 and 4 needed in this upgrade process?

Also, I suppose that putting running nodes in read_only mode is not needed for minor upgrade.

Thanks for your answers.

Best Regards,

Marko

[Wagner Bianchi]

Hello Marko,

We have done these minor upgrades on Percona Managed Services and I would say that this is feasible to be done. Take out of the cluster one node at a time, remove or update (yum update package_name) all old binaries, setup new ones in case you choose to remove packages, start mysqld back up online. You don't need to run mysql_upgrade in this case and when you start mysqld having newer binaries in place, depending on the size of configured gcache, it's going to perform an IST or even, a SST. As you're going to take the node out of rotation and out of cluster, you don't need to worry about configuring it as read_only.

** if you do that in a window maintenance where no one is writing data, it's the best yet scenario as you don't need to SST in any case it's needed, considering that, in most cases, data is too big (TB++) and SST time can be some hours.

So, a sequence of actions to summarize this will be, considering a three nodes cluster:

1-) take out node03 form rotation, run the update packages, start mysqld back, check if it's going to SST or IST, wait for it to finish;

2-) take out node02 form rotation, run the update packages, start mysqld back, check if it's going to SST or IST, wait for it to finish;

3-) take out node01 form rotation, run the update packages, start mysqld back, check if it's going to SST or IST, wait for it to finish;

I would like to use the below as an example:

#: three nodes cluster

| wsrep_incoming_addresses     | 192.168.50.11:3306,192.168.50.12:3306,192.168.50.13:3306 |

#: servers are...

node01::192.168.50.11:3306

node02::192.168.50.12:3306

node03::192.168.50.13:3306

#: packages currently installed

[vagrant@node02 ~]$ sudo rpm -qa | grep Percona

Percona-XtraDB-Cluster-client-56-5.6.28-25.14.1.el6.x86_64

Percona-XtraDB-Cluster-server-56-5.6.28-25.14.1.el6.x86_64

Percona-XtraDB-Cluster-galera-3-3.15-1.rhel6.x86_64

Percona-XtraDB-Cluster-shared-56-5.6.28-25.14.1.el6.x86_64

Percona-XtraDB-Cluster-devel-56-5.6.28-25.14.1.el6.x86_64

#: let's take out node02 to update packages

[vagrant@node02 ~]$ sudo /etc/init.d/mysql stop

Shutting down MySQL (Percona XtraDB Cluster).... SUCCESS!

[vagrant@node02 ~]$ sudo yum update Percona-XtraDB-Cluster-client-56-5.6.28-25.14.1.el6.x86_64 Percona-XtraDB-Cluster-server-56-5.6.28-25.14.1.el6.x86_64 Percona-XtraDB-Cluster-galera-3-3.15-1.rhel6.x86_64 Percona-XtraDB-Cluster-shared-56-5.6.28-25.14.1.el6.x86_64 Percona-XtraDB-Cluster-devel-56-5.6.28-25.14.1.el6.x86_64

...

Setting up Update Process

Resolving Dependencies

--> Running transaction check

---> Package Percona-XtraDB-Cluster-client-56.x86_64 1:5.6.28-25.14.1.el6 will be updated

---> Package Percona-XtraDB-Cluster-client-56.x86_64 1:5.6.32-25.17.1.el6 will be an update

---> Package Percona-XtraDB-Cluster-devel-56.x86_64 1:5.6.28-25.14.1.el6 will be updated

---> Package Percona-XtraDB-Cluster-devel-56.x86_64 1:5.6.32-25.17.1.el6 will be an update

---> Package Percona-XtraDB-Cluster-galera-3.x86_64 0:3.15-1.rhel6 will be updated

---> Package Percona-XtraDB-Cluster-galera-3.x86_64 0:3.17-1.rhel6 will be an update

---> Package Percona-XtraDB-Cluster-server-56.x86_64 1:5.6.28-25.14.1.el6 will be updated

---> Package Percona-XtraDB-Cluster-server-56.x86_64 1:5.6.32-25.17.1.el6 will be an update

---> Package Percona-XtraDB-Cluster-shared-56.x86_64 1:5.6.28-25.14.1.el6 will be updated

---> Package Percona-XtraDB-Cluster-shared-56.x86_64 1:5.6.32-25.17.1.el6 will be an update

...

#: new packages in place after yum update - here, make sure you run yum clean all before yum update

[root@node02 ~]# rpm -qa | grep Percona

Percona-XtraDB-Cluster-shared-56-5.6.32-25.17.1.el6.x86_64

Percona-XtraDB-Cluster-galera-3-3.17-1.rhel6.x86_64

Percona-XtraDB-Cluster-devel-56-5.6.32-25.17.1.el6.x86_64

Percona-XtraDB-Cluster-client-56-5.6.32-25.17.1.el6.x86_64

Percona-XtraDB-Cluster-server-56-5.6.32-25.17.1.el6.x86_64

[root@node02 ~]# mysqld --version

mysqld  Ver 5.6.32-78.1-56 for Linux on x86_64 (Percona XtraDB Cluster (GPL), Release rel78.1, Revision 979409a, WSREP version 25.17, wsrep_25.17)

#: let's start it

[root@node02 ~]# /etc/init.d/mysql start

Starting MySQL (Percona XtraDB Cluster).. SUCCESS!

Let us know please if you have any other questions, cheers.

Bianchi

[Wagner Bianchi]

Doing additional tests, I found that this is required an update on pecona-xtrabackup as well in case you have the wsrep_sst_method as xtrabackup-v2.

I repeat the process I told you we can execute I found the below issues regarding xtrabackup current version on one of the nodes:

2016-10-26 20:47:15 5227 [Note] WSREP: Flow-control interval: [28, 28]
2016-10-26 20:47:15 5227 [Note] WSREP: Shifting OPEN -> PRIMARY (TO: 52613)
2016-10-26 20:47:15 5227 [Note] WSREP: State transfer required:
 Group state: 63788863-1f8c-11e6-a8cc-12f338870ac3:52613
 Local state: 63788863-1f8c-11e6-a8cc-12f338870ac3:52611
2016-10-26 20:47:15 5227 [Note] WSREP: New cluster view: global state: 63788863-1f8c-11e6-a8cc-12f338870ac3:52613, view# 9: Primary, number of nodes: 3, my index: 2, protocol version 3
2016-10-26 20:47:15 5227 [Warning] WSREP: Gap in state sequence. Need state transfer.
2016-10-26 20:47:15 5227 [Note] WSREP: Running: 'wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.50.12' --datadir '/var/lib/mysql/' --defaults-file '/etc/my.cnf' --defaults-group-suffix '' --parent '5227'  '' '
WSREP_SST: [ERROR] FATAL: The innobackupex version is 2.3.4. Needs xtrabackup-2.3.5 or higher to perform SST (20161026 20:47:15.307)
2016-10-26 20:47:15 5227 [ERROR] WSREP: Failed to read 'ready <addr>' from: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.50.12' --datadir '/var/lib/mysql/' --defaults-file '/etc/my.cnf' --defaults-group-suffix '' --parent '5227'  ''
 Read: '(null)'

When the main message the describe the situation in which we need to update percona-xtrabackup package is the extract below:

WSREP_SST: [ERROR] FATAL: The innobackupex version is 2.3.4. Needs xtrabackup-2.3.5 or higher to perform SST (20161026 20:47:15.307)
2016-10-26 20:47:15 5227 [ERROR] WSREP: Failed to read 'ready <addr>' from: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.50.12' --datadir '/var/lib/mysql/' --defaults-file '/etc/my.cnf' --defaults-group-suffix '' --parent '5227'  ''

So, update the percona-xtrabackup on all three cluster's nodes:

[root@node02 vagrant]# yum update percona-xtrabackup
Loaded plugins: fastestmirror, versionlock
Determining fastest mirrors
...
--> Running transaction check
---> Package percona-xtrabackup.x86_64 0:2.3.4-1.el6 will be updated
---> Package percona-xtrabackup.x86_64 0:2.3.5-1.el6 will be an update

After that...start mysql again

[root@node02 vagrant]# /etc/init.d/mysql start
Starting MySQL (Percona XtraDB Cluster)...State transfer in progress, setting sleep higher
.. SUCCESS!
...
2016-10-26 21:51:38 3426 [Note] WSREP: State transfer required:
 Group state: 63788863-1f8c-11e6-a8cc-12f338870ac3:52613
 Local state: 63788863-1f8c-11e6-a8cc-12f338870ac3:52611
2016-10-26 21:51:38 3426 [Note] WSREP: New cluster view: global state: 63788863-1f8c-11e6-a8cc-12f338870ac3:52613, view# 2: Primary, number of nodes: 2, my index: 0, protocol version 3
2016-10-26 21:51:38 3426 [Warning] WSREP: Gap in state sequence. Need state transfer.
2016-10-26 21:51:38 3426 [Note] WSREP: Running: 'wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.50.12' --datadir '/var/lib/mysql/' --defaults-file '/etc/my.cnf' --defaults-group-suffix '' --parent '3426'  '' '
WSREP_SST: [INFO] Streaming with xbstream (20161026 21:51:39.023)
WSREP_SST: [INFO] Using socat as streamer (20161026 21:51:39.025)
WSREP_SST: [INFO] Evaluating timeout -s9 100 socat -u TCP-LISTEN:4444,reuseaddr stdio | xbstream -x; RC=( ${PIPESTATUS[@]} ) (20161026 21:51:39.100)
2016-10-26 21:51:39 3426 [Note] WSREP: Prepared SST request: xtrabackup-v2|192.168.50.12:4444/xtrabackup_sst//1
...
2016-10-26 21:51:39 3426 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 52613)
2016-10-26 21:51:39 3426 [Note] WSREP: Requesting state transfer: success, donor: 1
WSREP_SST: [INFO] Proceeding with SST (20161026 21:51:39.871)
WSREP_SST: [INFO] Evaluating socat -u TCP-LISTEN:4444,reuseaddr stdio | xbstream -x; RC=( ${PIPESTATUS[@]} ) (20161026 21:51:39.873)
WSREP_SST: [INFO] Cleaning the existing datadir and innodb-data/log directories (20161026 21:51:39.876)
...
WSREP_SST: [INFO] Moving the backup to /var/lib/mysql/ (20161026 21:51:55.826)
WSREP_SST: [INFO] Evaluating innobackupex --defaults-file=/etc/my.cnf  --defaults-group=mysqld --no-version-check  --datadir=/var/lib/mysql/ --move-back --force-non-empty-directories ${DATA} &>${DATA}/innobackup.move.log (20161026 21:51:55.829)
WSREP_SST: [INFO] Move successful, removing /var/lib/mysql//.sst (20161026 21:51:55.859)
...
Version: '5.6.32-78.1-56'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Percona XtraDB Cluster (GPL), Release rel78.1, Revision 979409a, WSREP version 25.17, wsrep_25.17
2016-10-26 21:51:56 3426 [Note] WSREP: 0.0 (pxc01): State transfer from 1.0 (pxc01) complete.
2016-10-26 21:51:56 3426 [Note] WSREP: Shifting JOINER -> JOINED (TO: 52613)
2016-10-26 21:51:56 3426 [Note] WSREP: Member 0.0 (pxc01) synced with group.
2016-10-26 21:51:56 3426 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 52613)
2016-10-26 21:51:56 3426 [Note] WSREP: Synchronized with group, ready for connections
2016-10-26 21:51:56 3426 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

So, summarizing what was said here and on the last e-mail, your plan will be:

1-) take out one node at a time;

2-) shutdown mysqld;

3-) update below packages (or the corresponding ones your running for 5.6.24-72.2):

[vagrant@node02 ~]$ sudo rpm -qa | grep Percona
Percona-XtraDB-Cluster-client-56-5.6.28-25.14.1.el6.x86_64
Percona-XtraDB-Cluster-server-56-5.6.28-25.14.1.el6.x86_64
Percona-XtraDB-Cluster-galera-3-3.15-1.rhel6.x86_64
Percona-XtraDB-Cluster-shared-56-5.6.28-25.14.1.el6.x86_64
Percona-XtraDB-Cluster-devel-56-5.6.28-25.14.1.el6.x86_64

4-) update percona-xtrabackup on all cluster's nodes to avoid issues as explained in this note.

WSREP_SST: [ERROR] FATAL: The innobackupex version is 2.3.4. Needs xtrabackup-2.3.5 or higher to perform SST (20161026 20:47:15.307)

...
[root@node01 ~]# yum update percona-xtrabackup

...
[root@node02 ~]# xtrabackup --version
xtrabackup version 2.3.5 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 45cda89)

5-) start mysqld back online to grab the cluster' state;

Let us know please how it goes, cheers

Bianchi

On Saturday, October 8, 2016 at 1:40:36 PM UTC-3, Marko Sutic wrote:

[Wagner Bianchi]

...continuing...

[Marko Sutic]

Hi Wagner!

Thank you for your detailed answer.

I have performed upgrade of cluster last week basically with the same steps you noted (also updated percona-xtrabackup) so this post was more like confirmation to me.

My cluster was running on Ubuntu 14.04 server so removing/installing packages wasn't performed with yum but the steps are the same.

Heavy DML application was paused for 15-30 minutes to avoid possible SST, which made whole upgrade process finish quicker.

We have load balancer running which was sending requests to running nodes so everything could be performed without downtime.

As in test environment I haven't experienced problems in production also.

Thank you again for your answer.

Best Regards,

Marko