Percona Cluster 5.7 Tarball Minor Version Upgrade

Use the following instructions to upgrade Percona XtraDB to 5.7.29 to avoid a potential issue that can cause the cluster to periodically lock up. This issue can affect versions of Percona XtraDB prior to 5.7.29, including the version of Percona shipping with Moogsoft AIOps version 7.3.x.

System Setup

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

  • Server1 - Moogsoft AIOps 7.3.1.1

  • 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 AIOps 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 AIOps 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 5.7.29 files and copy them into the same folder where the existing Percona installation is located (default is ~/install):

    cd ~/install;


    curl -s -L -O --connect-timeout 5 --retry 5 --retry-delay 0 
    --retry-max-time 40 https://www.percona.com/downloads/Percona-
    XtraBackup-2.4/Percona-XtraBackup-2.4.19/binary/tarball/percona-
    xtrabackup-2.4.19-Linux-x86_64.el7.tar.gz;


    curl -s -L -O --connect-timeout 5 --retry 5 --retry-delay 0 
    --retry-max-time 40 https://www.percona.com/downloads/Percona-XtraDB-
    Cluster-57/Percona-XtraDB-Cluster-5.7.29-31.43/binary/tarball/Percona-
    XtraDB-Cluster-5.7.29-rel32-43.1.Linux.x86_64.ssl101.tar.gz;
    
  3. Extract the files into the same folder into the same folder where the existing Percona installation is located (default is ~/install):

    tar -xf Percona-XtraDB-Cluster-5.7.29-rel32-43.1.Linux.x86_64.ssl101.tar.gz;
    tar -xf percona-xtrabackup-2.4.19-Linux-x86_64.el7.tar.gz;
  4. Update the PATH variable:

    sed -i 's/5\.7\.2.-.*ssl101/5.7.29-rel32-43.1.Linux.x86_64.ssl101/g' ~/.bashrc;
    sed -i 's/xtrabackup-2...../xtrabackup-2.4.19/g' ~/.bashrc
    source ~/.bashrc;
  5. Back up and update the .my.cnf file:

    cp ~/.my.cnf ~/.my.cnf.pre5729;
    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:

    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. Finally, start the node normally:

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

      (mysqld_safe > /dev/null 2>/dev/null)&
    • If this is a node with Moogsoft AIOps 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
  11. 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 AIOps 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 AIOps 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 AIOps components installed), run the following command:

      (mysqld_safe > /dev/null 2>/dev/null)&
    • If this is a node with Moogsoft AIOps components, run the following command:

      $MOOGSOFT_HOME/bin/utils/process_cntl mysql start