Skip to main content

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 Percona dependencies on all hosts that you will configure as Percona nodes:

      REPO_URL=http://mirror.centos.org/centos/7/os/x86_64/Packages;
for PACKAGE in libaio libquadmath libgfortran perl perl-Pod-Escapes perl-libs perl-macros xinetd perl-Carp perl-Compress-Raw-Bzip2 perl-Compress-Raw-Zlib perl-DBD-MySQL perl-DBI perl-Data-Dumper perl-Digest perl-Digest-MD5 perl-Encode perl-Exporter perl-File-Path perl-File-Temp perl-Filter perl-Getopt-Long perl-HTTP-Tiny perl-IO-Compress perl-Net-Daemon perl-PathTools perl-PlRPC perl-Pod-Perldoc perl-Pod-Simple perl-Pod-Usage perl-Scalar-List-Utils perl-Socket perl-Storable perl-Text-ParseWords perl-Time-HiRes perl-Time-Local perl-constant perl-parent perl-podlators perl-threads perl-threads-shared socat rsync lsof perl-TermReadKey;
do
	RPM_FILE=$(curl -s -L ${REPO_URL} 2>/dev/null | egrep ">${PACKAGE}-[0-9].*" | egrep 'x86_64|noarch' | sed "s/.*\(${PACKAGE}.*.rpm\).*/\1/")
	echo "Downloading ${PACKAGE}"
	curl -s -L -O ${REPO_URL}/${RPM_FILE}
done;

REPO_URL=http://mirror.centos.org/centos/7/extras/x86_64/Packages;
for PACKAGE in libev;
do
	RPM_FILE=$(curl -s -L ${REPO_URL} 2>/dev/null | egrep ">${PACKAGE}-[0-9].*" | egrep 'x86_64|noarch' | sed "s/.*\(${PACKAGE}.*.rpm\).*/\1/")
	echo "Downloading ${PACKAGE}"
	curl -s -L -O ${REPO_URL}/${RPM_FILE}
done;

curl -L -O http://repo.percona.com/percona/yum/release/7/RPMS/x86_64/qpress-11-1.el7.x86_64.rpm;

yum install *.rpm

    • 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 Enterprise database is installed.

Note

If you have installed the Moogsoft Enterprise 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 -O https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.23/binary/tarball/percona-xtrabackup-2.4.23-Linux-x86_64.glibc2.12.tar.gz

tar -xf percona-xtrabackup-2.4.23-Linux-x86_64.glibc2.12.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

    Note

    The backup location is flexible. You can place the backup anywhere that you have permission to write to.

  3. When the backup is complete, find the name of the directory created under ~/moog_datastore/mysql_backup and repeat the process using the --apply-log setting to apply any transactions that may have occurred during the backup process:

    percona-xtrabackup-*/bin/innobackupex --apply-log ~/moog_datastore/mysql_backup/<directory_created_in_previous_step>/

  4. Stop MySQL:

    $MOOGSOFT_HOME/bin/utils/process_cntl mysql stop

    Stopping MySQL does not delete the backup data. The data persists in the MySQL data directory.

  5. Navigate to the desired location and install Percona Cluster Binary 5.7:

    curl -L -O https://downloads.percona.com/downloads/Percona-XtraDB-Cluster-57/Percona-XtraDB-Cluster-5.7.33-31.49/binary/tarball/Percona-XtraDB-Cluster-5.7.33-rel36-49.1.Linux.x86_64.glibc2.12.tar.gz
tar -xf Percona-XtraDB-Cluster-5.7.33-rel36-49.1.Linux.x86_64.glibc2.12.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.28-el7-x86_64 ~/mysql-5.7.28-el7-x86_64_save

ln -s ~/Percona-XtraDB-Cluster-5.7.33-rel36-49.1.Linux.x86_64.glibc2.12 ~/mysql-5.7.28-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.33-rel36-49.1.Linux.x86_64.glibc2.12/bin:~/percona-xtrabackup-2.4.23-Linux-x86_64.glibc2.12/bin:~/install/usr/bin" >> ~/.bashrc

    4. Add the new Percona paths to your environment variable:

      export PATH=$PATH:~/Percona-XtraDB-Cluster-5.7.33-rel36-49.1.Linux.x86_64.glibc2.12/bin:~/percona-xtrabackup-2.4.23-Linux-x86_64.glibc2.12/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.33-rel36-49.1.Linux.x86_64.glibc2.12/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.33-rel36-49.1.Linux.x86_64.glibc2.12/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.33-rel36-49.1.Linux.x86_64.glibc2.12/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.33-rel36-49.1.Linux.x86_64.glibc2.12/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.33-rel36-49.1.Linux.x86_64.glibc2.12

  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://<myspeedyuser>:<myspeedypassword>@speedy.moogsoft.com/repo/aiops/8.1.0/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

In this section: