Skip to main content

Percona Cluster 5.7 Tarball Minor Version Upgrade

Use the following instructions to upgrade a tarball Percona XtraDB Cluster to v5.7.36 to avoid Moogsoft Enterprise-impacting issues that have been identified in lower versions.

System Setup

This example uses the following sample server configuration for Moogsoft Enterprise and the Percona Cluster:

  • Server1 - Moogsoft Enterprise 8.x.x.x

  • Server2 - Percona Cluster Node1

  • Server3 - Percona Cluster Node2

  • Server4 - Percona Cluster Node3

Assumptions

This procedure guides a user through a tarball-based installation. The same user that performed the Percona installation originally should perform the upgrade.

Definitions

  • IST - Incremental State Transfer. Functionality which instead of whole state snapshot can catch up with the group by receiving the missing writesets, but only if the writeset is still in the donor’s writeset cache.

  • SST - State Snapshot Transfer is the full copy of data from one node to another. Used when a new node joins the cluster; it has to transfer data from an existing node.

Upgrade Steps

Step 1 - Stop all Moog services that connect to the database

$MOOGSOFT_HOME/bin/utils/process_cntl rest_lam stop
$MOOGSOFT_HOME/bin/utils/process_cntl socket_lam stop
$MOOGSOFT_HOME/bin/utils/process_cntl moog_farmd stop
$MOOGSOFT_HOME/bin/utils/process_cntl apache-tomcat stop

Note

While it is technically possible to perform the cluster upgrade when the system is running and processing events, doing so greatly increases the chances of issues and/or SST occurrence later in the upgrade.

Step 2 - Make sure the three database nodes are synced

Perform the following check from any location:

[moogsoft@server1]# curl http://server2:9198
Percona XtraDB Cluster Node is synced.
[moogsoft@server1]# curl http://server3:9198
Percona XtraDB Cluster Node is synced.
[moogsoft@server1]# curl http://server4:9198
Percona XtraDB Cluster Node is synced.

Step 3 - Perform the actions on Server2

  1. Stop the MySQL node:

    • If this is a database-only node (with no Moogsoft Enterprise components installed), OR the $MOOGSOFT_HOME/bin/utils/process_cntl mysql status command does not report ‘running’, run the following commands:

      MYSQL_PID=$(ps -ef | grep mysql | egrep -v 
'grep|mysqld_safe' | awk '{print $2}')
kill -s TERM $MYSQL_PID

    • If this database is on a node with Moogsoft Enterprise installed AND the $MOOGSOFT_HOME/bin/utils/process_cntl mysql status command does report ‘running’, run the following commands:

      $MOOGSOFT_HOME/bin/utils/process_cntl mysql stop

  2. Download the Percona files and copy them into the same folder where the existing Percona installation is located (default is ~/install):

    cd ~/install;



curl -L -O https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.24/binary/tarball/percona-xtrabackup-2.4.24-Linux-x86_64.glibc2.12.tar.gz

curl -L -O https://downloads.percona.com/downloads/Percona-XtraDB-Cluster-57/Percona-XtraDB-Cluster-5.7.36-31.55/binary/tarball/Percona-XtraDB-Cluster-5.7.36-rel39-55.1.Linux.x86_64.glibc2.12.tar.gz

  3. Extract the files into the same folder where the existing Percona installation is located (default is ~/install):

    tar -xf Percona-XtraDB-Cluster-5.7.36-rel39-55.1.Linux.x86_64.glibc2.12.tar.gz;
tar -xf percona-xtrabackup-2.4.24-Linux-x86_64.glibc2.12.tar.gz;

  4. Update the PATH variable in the bashrc file to reference the newer Percona packages::

    sed -i 's;Percona-XtraDB-Cluster-5\.7[^/]\+;Percona-XtraDB-Cluster-5.7.36-rel39-55.1.Linux.x86_64.glibc2.12;' ~/.bashrc;
sed -i 's/percona-xtrabackup-[^/]\+/percona-xtrabackup-2.4.24-Linux-x86_64.glibc2.12/g' ~/.bashrc
source ~/.bashrc;

  5. Back-up and update the .my.cnf file:

    cp ~/.my.cnf ~/.my.cnf.pre5736;
MYSQL_HOME=$(dirname $(dirname $(which mysqld)))
sed -i "s;basedir.*;basedir = ${MYSQL_HOME};" ~/.my.cnf
sed -i "s;wsrep_provider\s*=.*;wsrep_provider = ${MYSQL_HOME}/lib/libgalera_smm.so;" ~/.my.cnf

  6. Backup the grastate.dat file as a safety measure.

    LINE_NUM=$(cat -un ~/.my.cnf | grep '\[mysqld\]' | awk 
'{ print $1 }')
DATADIR=$(awk "NR>${LINE_NUM}" ~/.my.cnf | egrep '^\s*datadir' 
| head -1 | awk -F'=' '{ print $2 }' | tr -d '\t ')
cp ${DATADIR}/grastate.dat ${DATADIR}/grastate.dat.bak

  7. Start the node in standalone mode:

    (mysqld_safe --wsrep-provider='none' > /dev/null 2>/dev/null)&

    There is no console output from this command.

  8. After several seconds, run the following command (enter the password when prompted):

    mysql_upgrade -u root -p -S $(awk "NR>$(cat -un ~/.my.cnf | 
egrep '\[mysqld\]' | awk '{print $1}')" ~/.my.cnf | 
egrep 'socket\s*=' | head -1 | awk -F= '{print $2}')

    The output will resemble the following:

    [moogsoft@server2 ~]# mysql_upgrade -u root -p -S $(awk 
"NR>$(cat -un ~/.my.cnf | egrep '\[mysqld\]' | 
awk '{print $1}')" ~/.my.cnf | egrep 'socket\s*=' | head -1 | 
awk -F= '{print $2}') 
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Found outdated sys schema version 1.5.1.
Upgrading the sys schema.
Checking databases.
historic_moogdb.alert_journal                      OK
historic_moogdb.alerts                             OK
historic_moogdb.alerts_marked_prc_by_user          OK
historic_moogdb.comments                           OK
historic_moogdb.event_codes                        OK
historic_moogdb.events                             OK
historic_moogdb.feedback                           OK
historic_moogdb.invitations                        OK
historic_moogdb.notification                       OK
historic_moogdb.prc_earliest_highest_severity_event OK
historic_moogdb.resolving_thread_entries           OK
historic_moogdb.sig_alerts                         OK
historic_moogdb.sig_correlation_info               OK
historic_moogdb.sig_event_codes                    OK
historic_moogdb.sig_journal                        OK
historic_moogdb.sig_participants_in_room           OK
historic_moogdb.sig_pinpoint                       OK
historic_moogdb.sig_processes                      OK
historic_moogdb.sig_rating                         OK
historic_moogdb.sig_services                       OK
historic_moogdb.sig_teams                          OK
historic_moogdb.sig_view_by_user                   OK
historic_moogdb.sigs                               OK
historic_moogdb.snapshots                          OK
historic_moogdb.thread_entries                     OK
historic_moogdb.thread_entry_comments              OK
historic_moogdb.thread_entry_feedback              OK
historic_moogdb.threads                            OK
moog_intdb.DATABASECHANGELOG                       OK
moog_intdb.DATABASECHANGELOGLOCK                   OK
moog_intdb.broker_profiles                         OK
moog_intdb.broker_stage                            OK
moog_intdb.enabled_features                        OK
moog_intdb.expected_integration_state              OK
moog_intdb.featured_integrations                   OK
moog_intdb.installed_brokers                       OK
moog_intdb.installed_integrations                  OK
moog_intdb.integration_migration                   OK
moog_intdb.jwt_tokens                              OK
moog_reference.alert_properties                    OK
moog_reference.event_streams                       OK
moog_reference.execution_details                   OK
moog_reference.incremental_token_data              OK
moog_reference.one_hop_topo                        OK
moog_reference.schema_upgrades                     OK
moog_reference.schema_version                      OK
moog_reference.token_data                          OK
moog_reference.token_types                         OK
moog_reference.topo_cache                          OK
moog_reference.topo_nodes                          OK
moog_reference.topo_updates                        OK
moog_reference.vertex_entropy_max                  OK
moogdb.action_states                               OK
moogdb.actors                                      OK
moogdb.alert_column_names                          OK
moogdb.alert_filters                               OK
moogdb.alert_filters_access                        OK
moogdb.alert_journal                               OK
moogdb.alerts                                      OK
moogdb.alerts_custom_info_fields                   OK
moogdb.alerts_marked_prc_by_user                   OK
moogdb.assigned_ports                              OK
moogdb.authentication_info                         OK
moogdb.comments                                    OK
moogdb.config_migration                            OK
moogdb.default_filters                             OK
moogdb.default_system_config                       OK
moogdb.departments                                 OK
moogdb.duplicate_historic_alerts                   OK
moogdb.enrichment_static_data                      OK
moogdb.enrichment_static_mappings                  OK
moogdb.enrichments                                 OK
moogdb.entity_catalog                              OK
moogdb.event_codes                                 OK
moogdb.events                                      OK
moogdb.features                                    OK
moogdb.feedback                                    OK
moogdb.feedback_models                             OK
moogdb.feedback_training_queues                    OK
moogdb.functional_passwords                        OK
moogdb.groups                                      OK
moogdb.integration_properties                      OK
moogdb.integration_url_tools                       OK
moogdb.invitations                                 OK
moogdb.invite_handlers                             OK
moogdb.invite_states                               OK
moogdb.known_sig_states                            OK
moogdb.last_job_run_info                           OK
moogdb.latest_insight_ids                          OK
moogdb.licence_info                                OK
moogdb.link_definitions                            OK
moogdb.maintenance_windows                         OK
moogdb.media                                       OK
moogdb.moog_indexer                                OK
moogdb.mtt_insights                                OK
moogdb.mtt_team_insights                           OK
moogdb.net_types                                   OK
moogdb.notification                                OK
moogdb.notification_type                           OK
moogdb.prc_archived_training_alerts_data           OK
moogdb.prc_archived_training_sig_alerts_data       OK
moogdb.prc_archived_training_sigs_data             OK
moogdb.prc_models                                  OK
moogdb.processes                                   OK
moogdb.product_deployment                          OK
moogdb.raw_lam_events                              OK
moogdb.recorded_product_actions                    OK
moogdb.resolving_thread_entries                    OK
moogdb.role_properties                             OK
moogdb.roles                                       OK
moogdb.room_post_comments                          OK
moogdb.room_post_media                             OK
moogdb.room_post_types                             OK
moogdb.room_posts                                  OK
moogdb.rooms                                       OK
moogdb.run_on_startup                              OK
moogdb.schema_upgrades                             OK
moogdb.schema_version                              OK
moogdb.security_realms                             OK
moogdb.service_insights                            OK
moogdb.services                                    OK
moogdb.sessions                                    OK
moogdb.severity                                    OK
moogdb.severity_insights                           OK
moogdb.sig_alerts                                  OK
moogdb.sig_category                                OK
moogdb.sig_correlation_info                        OK
moogdb.sig_event_codes                             OK
moogdb.sig_journal                                 OK
moogdb.sig_participants_in_room                    OK
moogdb.sig_pinpoint                                OK
moogdb.sig_processes                               OK
moogdb.sig_queue                                   OK
moogdb.sig_rating                                  OK
moogdb.sig_services                                OK
moogdb.sig_state                                   OK
moogdb.sig_stats_cache                             OK
moogdb.sig_teams                                   OK
moogdb.sig_view_by_user                            OK
moogdb.significance                                OK
moogdb.sigs                                        OK
moogdb.sigs_custom_info_fields                     OK
moogdb.sigs_lists_helper                           OK
moogdb.sitroom_plugins                             OK
moogdb.situation_column_names                      OK
moogdb.situation_filters                           OK
moogdb.situation_filters_access                    OK
moogdb.snapshots                                   OK
moogdb.split_alert_events                          OK
moogdb.split_alerts                                OK
moogdb.split_events                                OK
moogdb.split_last_timestamp                        OK
moogdb.split_sigs                                  OK
moogdb.status                                      OK
moogdb.status_insights                             OK
moogdb.system_config                               OK
moogdb.system_insights                             OK
moogdb.team_insights                               OK
moogdb.team_services                               OK
moogdb.teams                                       OK
moogdb.temporary_user_insights                     OK
moogdb.thread_entries                              OK
moogdb.thread_entry_comments                       OK
moogdb.thread_entry_feedback                       OK
moogdb.threads                                     OK
moogdb.tools_access                                OK
moogdb.transitions                                 OK
moogdb.ui_statistics                               OK
moogdb.user_insights                               OK
moogdb.user_properties                             OK
moogdb.user_roles                                  OK
moogdb.user_status                                 OK
moogdb.user_teams                                  OK
moogdb.users                                       OK
moogdb.users_realm_identifier                      OK
moogdb.view_configs                                OK
moogdb.workflow_alerts_accessed                    OK
moogdb.workflow_moolets                            OK
moogdb.workflow_situations_accessed                OK
moogdb.workflows                                   OK
sys.sys_config                                     OK
Upgrade process completed successfully.
Checking if update is needed.

  9. Kill the standalone node:

    MYSQL_PID=$(ps -ef | grep mysql | egrep -v 'grep|mysqld_safe' | awk '{print $2}')
kill -s TERM $MYSQL_PID

  10. Add the following property to the [mysqld_safe] section of the Percona Node's configuration file ~/.my.cnf to remove the restriction on SST duration:

    service-startup-timeout = -1

    e.g.:

    [mysqld_safe]
log-error = <INSTALL_DIR>/var/log/mysqld.log
pid-file = <INSTALL_DIR>/var/run/mysqld/mysqld.pid
socket = <INSTALL_DIR>/var/run/mysqld/mysqld.sock
nice = 0
service-startup-timeout = -1

  11. Finally, start the node normally:

    • If this is a database-only node (no Moogsoft Enterprise components installed), run the following command:

      (mysqld_safe > /dev/null 2>/dev/null)&

    • If this is a node with Moogsoft Enterprise components installed, run the following command:

      $MOOGSOFT_HOME/bin/utils/process_cntl mysql start

    It will either sync immediately or spend a short amount of time performing an IST from another node. See the example below from mysqld.log:

    2020-08-20T11:12:45.559572-00:00 0 [Note] WSREP: Receiving 
IST... 13.0% ( 96/739 events) complete.
2020-08-20T11:12:56.030910-00:00 0 [Note] WSREP: Receiving 
IST... 23.8% (176/739 events) complete.
2020-08-20T11:13:06.922647-00:00 0 [Note] WSREP: Receiving 
IST... 34.6% (256/739 events) complete.
2020-08-20T11:13:18.481076-00:00 0 [Note] WSREP: Receiving 
IST... 45.5% (336/739 events) complete.
2020-08-20T11:13:30.433314-00:00 0 [Note] WSREP: Receiving 
IST... 56.3% (416/739 events) complete.
2020-08-20T11:13:42.463826-00:00 0 [Note] WSREP: Receiving 
IST... 67.1% (496/739 events) complete.
2020-08-20T11:13:52.673644-00:00 0 [Note] WSREP: Receiving 
IST... 75.8% (560/739 events) complete.
2020-08-20T11:14:02.812737-00:00 0 [Note] WSREP: Receiving 
IST... 84.4% (624/739 events) complete.
2020-08-20T11:14:06.210387-00:00 0 [Note] WSREP: Receiving 
IST...100.0% (739/739 events) complete.
2020-08-20T11:14:06.211793-00:00 2 [Note] WSREP: IST 
received: beebd113-e080-11ea-8803-6f888df3319a:21784800
2020-08-20T11:14:06.212523-00:00 0 [Note] WSREP: 
0.0 (pxc-node-ldev03): State transfer from 2.0 
(pxc-node-ldev04) complete.
2020-08-20T11:14:06.212564-00:00 0 [Note] WSREP: SST 
leaving flow control
2020-08-20T11:14:06.212574-00:00 0 [Note] WSREP: Shifting 
JOINER -> JOINED (TO: 21784800)
2020-08-20T11:14:06.213148-00:00 0 [Note] WSREP: Member 
0.0 (pxc-node-ldev03) synced with group.
2020-08-20T11:14:06.213174-00:00 0 [Note] WSREP: Shifting 
JOINED -> SYNCED (TO: 21784800)
2020-08-20T11:14:06.213211-00:00 2 [Note] WSREP: Synchronized 
with group, ready for connections
2020-08-20T11:14:06.213234-00:00 2 [Note] WSREP: Setting 
wsrep_ready to true
2020-08-20T11:14:06.213254-00:00 2 [Note] WSREP: 
wsrep_notify_cmd is not defined, skipping notification.
2020-08-20T11:14:44.973932-00:00 0 [Note] InnoDB: 
Buffer pool(s) load completed at 200820 11:14:44

  12. Verify this node has synced before proceeding to the next node:

    [root@server1]#  curl http://server2:9198
Percona XtraDB Cluster Node is synced.

Step 4 - Perform the actions on Server3

Repeat the same actions on Server3 as you performed on Server2 in Step 3.

Step 5 - Server4 actions:

Repeat the same actions on Server4 as you performed on Server2 in Step 3.

Step 6 - Final Cluster Check

Check that all three database nodes are synced:

[root@server1]# curl http://server2:9198
Percona XtraDB Cluster Node is synced.
[root@server1]# curl http://server3:9198
Percona XtraDB Cluster Node is synced.
[root@server1]# curl http://server4:9198
Percona XtraDB Cluster Node is synced.

Step 7 - Restart moog services

$MOOGSOFT_HOME/bin/utils/process_cntl rest_lam start

$MOOGSOFT_HOME/bin/utils/process_cntl socket_lam start

$MOOGSOFT_HOME/bin/utils/process_cntl moog_farmd start

$MOOGSOFT_HOME/bin/utils/process_cntl apache-tomcat start

Fixing the upgraded node if an SST occurs

When the upgraded node rejoins the cluster, it is important that it synchronizes with the cluster using IST. If an SST occurs, you may need to upgrade the data directory structure using mysql_upgrade again to make sure it is compatible with the newer version of the binaries.

Note

Check the error log for statements like the following to determine if an SST occurred:

“Check if state gap can be serviced using IST ... State gap can’t be serviced using IST. Switching to SST” instead of “Receiving IST: ...” lines appropriate to IST synchronization.

Perform the following additional steps to upgrade the data directory structure after SST completes:

  1. Shut down the node that rejoined the cluster using SST:

    • If this is a database-only node (no Moogsoft Enterprise components installed), OR the $MOOGSOFT_HOME/bin/utils/process_cntl mysql status command doesn’t report ‘running’, run the following commands:

      MYSQL_PID=$(ps -ef | grep mysql | egrep -v 'grep|mysqld_safe' |
awk '{print $2}')
kill -s TERM $MYSQL_PID

    • If this database is on a node with Moogsoft Enterprise installed AND the $MOOGSOFT_HOME/bin/utils/process_cntl mysql status command reports ‘running’, run the following commands:

      $MOOGSOFT_HOME/bin/utils/process_cntl mysql stop

  2. Restart the node in standalone mode:

    (mysqld_safe --wsrep-provider='none' > /dev/null 2>/dev/null)&

  3. Run the following command:

    mysql_upgrade -u root -p -S $(awk "NR>$(cat -un ~/.my.cnf | 
egrep '\[mysqld\]' | awk '{print $1}')" ~/.my.cnf | 
egrep 'socket\s*=' | head -1 | awk -F= '{print $2}')

  4. Kill the standalone node:

    MYSQL_PID=$(ps -ef | grep mysql | egrep -v 'grep|mysqld_safe' | 
awk '{print $2}')
kill -s TERM $MYSQL_PID

  5. Restart the node normally and ensure the node rejoins the cluster using IST:

    • If this is a database-only node (no Moogsoft Enterprise components installed), run the following command:

      (mysqld_safe > /dev/null 2>/dev/null)&

    • If this is a node with Moogsoft Enterprise components, run the following command:

      $MOOGSOFT_HOME/bin/utils/process_cntl mysql start
In this section: