Moogsoft Docs

Tarball - Migrate from MySQL to Percona

This topic describes the Tarball migration procedure from MySQL to Percona XtraDB Cluster.

Follow these steps if you need to run the process as a non-root user, or you want the ability to deploy to a non-default location and install all components under one directory.

Before you begin

Before you begin the migration process, ensure you have met the following requirements:

  • You have enlisted an administrator to perform the following tasks, which require root privileges:

    • Install the Perl dependencies on all hosts that you will configure as Percona nodes:

      sudo yum -y install perl-Digest-MD5 perl-DBD-MySQL
    • Install HA Proxy after you have configured the Percona nodes.

  • You have credentials to connect to the "speedy" Moogsoft package repository.

Configure the Percona XtraDb Cluster donor node

To migrate to Percona XtraDB Cluster, perform the following steps on the server where the Moogsoft AIOps database is installed.

Note

If you have installed the Moogsoft AIOps database on more than one server, for example in a master/standby configuration, use the master as the donor node.

  1. Navigate to the desired location and install Percona XtraBackup:

    curl -L https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.14/binary/tarball/percona-xtrabackup-2.4.14-Linux-x86_64.libgcrypt153.tar.gz -O
    
    tar xzf percona-xtrabackup-2.4.14-Linux-x86_64.libgcrypt153.tar.gz
  2. Back up MySQL using the Percona XtraBackup tool. Use the --password option to enter your MySQL root password. For example:

    percona-xtrabackup-*/bin/innobackupex --user=root --password=root -H 127.0.0.1 ~/moog_datastore/mysql_backup
  3. When the backup is complete, repeat the process using the --apply-log option, to apply any transactions that may have occurred during the backup process:

    percona-xtrabackup-*/bin/innobackupex --apply-log ~/moog_datastore/mysql_backup
  4. Stop MySQL:

    $MOOGSOFT_HOME/bin/utils/process_cntl mysql stop
  5. Navigate to the desired location and install Percona Cluster Binary 5.7:

    curl -L https://www.percona.com/downloads/Percona-XtraDB-Cluster-LATEST/Percona-XtraDB-Cluster-5.7.26-31.37/binary/tarball/Percona-XtraDB-Cluster-5.7.26-rel29-31.37.1.Linux.x86_64.ssl102.tar.gz -O
    
    tar xzf Percona-XtraDB-Cluster-5.7.26-rel29-31.37.1.Linux.x86_64.ssl102.tar.gz
  6. Install dependencies for Percona cluster:

    mkdir ~/install
    
    cd ~/install
    
    curl -L http://mirror.centos.org/centos/7/os/x86_64/Packages/socat-1.7.3.2-2.el7.x86_64.rpm -O
    
    rpm2cpio socat-1.7.3.2-2.el7.x86_64.rpm | cpio -idmv
    
    cd
  7. Replace the MySQL binaries with the Percona binaries, as follows.

    1. Back up the MySQL directory and link MySQL to the new Percona location:

      mv ~/mysql-5.7.22-el7-x86_64 ~/mysql-5.7.22-el7-x86_64_save
      
      ln -s ~/Percona-XtraDB-Cluster-5.7.26-rel29-31.37.1.Linux.x86_64.ssl102 ~/mysql-5.7.22-el7-x86_64
    2. If the path to MySQL is set in the ~/.bashrc file, remove it.

    3. Add the new Percona paths to the ~/.bashrc file:

      cd ~; echo "export PATH=$PATH:~/Percona-XtraDB-Cluster-5.7.26-rel29-31.37.1.Linux.x86_64.ssl102/bin:~/percona-xtrabackup-2.4.14-Linux-x86_64/bin:~/install/usr/bin" >> ~/.bashrc
    4. Add the new Percona paths to your environment variable:

      export PATH=$PATH:~/Percona-XtraDB-Cluster-5.7.26-rel29-31.37.1.Linux.x86_64.ssl102/bin:~/percona-xtrabackup-2.4.14-Linux-x86_64/bin:~/install/usr/bin
    5. Set the path to the Percona configuration file:

      sed -i -e 's/WSREP_SST_OPT_CONF="$2".*/WSREP_SST_OPT_CONF="~\/\.my\.cnf"/' ~/Percona-XtraDB-Cluster-5.7.26-rel29-31.37.1.Linux.x86_64.ssl102/bin/wsrep_sst_common
  8. Edit the ~/.my.cnf file as follows:

    1. Add the following properties to the end of the file. This configures the Percona cluster to start in bootstrap mode.

      [mysqld]
      wsrep_provider=/home/admin/Percona-XtraDB-Cluster-5.7.26-rel29-31.37.1.Linux.x86_64.ssl102/lib/libgalera_smm.so
      binlog_format=ROW
      default_storage_engine=InnoDB
      wsrep_slave_threads= 8
      wsrep_log_conflicts
      innodb_autoinc_lock_mode=2
      wsrep_cluster_name=pxc-cluster
      wsrep_node_name=pxc-cluster-node-1
      pxc_strict_mode=ENFORCING
      wsrep_sst_method=xtrabackup-v2
      wsrep_sst_auth="username:password"
      wsrep_cluster_address=gcomm://

      Note

      The Percona SST (State Transfer) user in the wsrep_sst_auth property does not exist yet. Set the authentication details here and you will create the user below.

    2. Configure the wsrep_provider property to contain the full path to the Galera Replication Plugin (libgalera_smm.so) if it differs from the default.

    3. Enter your user credentials in the wsrep_sst_auth property.

  9. Start the Percona donor node:

    ~/Percona-XtraDB-Cluster-5.7.26-rel29-31.37.1.Linux.x86_64.ssl102/bin/mysqld_safe &
  10. Create a Percona SST user, grant the required permissions and add the user to the bootstrapped node.

    Run the following command, replacing the username and password with your chosen credentials:

    ~/Percona-XtraDB-Cluster-5.7.26-rel29-31.37.1.Linux.x86_64.ssl102/bin/mysql -u root -proot -e "GRANT PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'username'@'localhost' identified by 'password';"
  11. Run the following commands to create the clustercheck user required by HA Proxy. Replace password with your details:

    mysql -u root -proot -e "GRANT PROCESS ON *.* TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'clustercheckpassword\!';"
    
    mysql -u root -proot -e "FLUSH PRIVILEGES;"
  12. Add the IP addresses for the remaining nodes to the Percona configuration file.

    Configure the wsrep_cluster_address property in the ~/.my.cnf file to contain a comma-separated list of all of the nodes you will add to the cluster. For example:

    [mysqld]
    wsrep_cluster_address=gcomm://1.1.1.1,1.1.1.2,1.1.1.3
Configure the Percona clustercheck script

The Percona clustercheck script is distributed as part of Percona XtraDB Cluster. It works with HA Proxy to monitor nodes in the cluster and performs health checks on backend servers.

To configure and deploy the script:

  1. Configure the path to clustercheck app

    INSTALLER_DIR=~/install
    DB_PACKAGE_DIR=~/Percona-XtraDB-Cluster-5.7.26-rel29-31.37.1.Linux.x86_64.ssl102
  2. Create a script to launch clustercheck upon request on port 9198:

    cat > $INSTALLER_DIR/inetd << EOF
    while :
    do
    ${DB_PACKAGE_DIR}/bin/clustercheck clustercheckuser clustercheckpassword\!|nc -l 9198
    done
    EOF
  3. Start the clustercheck script in the background:

    chmod +x $INSTALLER_DIR/inetd
    $INSTALLER_DIR/inetd &> /dev/null & disown $!
Configure additional nodes
bash <(curl -s -k https://myspeedyusername:myspeedypassword@speedy.moogsoft.com/repo/install_percona_nodes_tarball.sh) -i 1.1.1.1,1.1.1.2,1.1.1.3 -u mysstusername -w mysstpassword
Configure for DR
Install HA Proxy