RPM - Migrate from MySQL to Percona

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

Follow these steps to perform this process with root privileges.

Before you begin

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

  • You have root access to the servers you will use for your database nodes, and to any servers HAProxy will be installed on.

  • 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-db package is installed.

Note

If you have installed the moogsoft-db package 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 the Percona repository:

    yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
  2. Install Percona XtraBackup:

    yum -y install percona-xtrabackup-24.x86_64
  3. Back up MySQL using the Percona XtraBackup tool:

    innobackupex --user=root --password= /backups/mysql

    This is a precaution. You do not have to back up MySQL before setting up the Percona XtraDB cluster.

  4. When the backup is complete, repeat the process using the "apply log" setting to apply any transactions that may have occurred during the backup process:

    innobackupex --apply-log /backups/mysql
  5. Stop MySQL:

    service mysqld stop
  6. Uninstall MySQL:

    yum -y remove mysql-*community*

    Stopping and uninstalling MySQL does not delete the backup data. The backup data is available at /backups/mysql and the data directory used by MySQL remains instact.

  7. Install Percona Cluster Binary 5.7:

    yum -y install Percona-Server-shared-compat-57-5.7.31
    yum -y install Percona-XtraDB-Cluster-server-57-5.7.31
  8. Configure the Percona cluster to start in bootstrap mode.

    1. Edit the /etc/percona-xtradb-cluster.conf.d/wsrep.cnf file so that wsrep_cluster_address is not set to an IP address. For example:

      [mysqld]
      wsrep_cluster_address=gcomm://
    2. Uncomment the following property, to enable the Percona user to be used for replication.

      wsrep_sst_auth="username:password"

      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.

  9. Start the Percona donor node:

    service mysqld start
  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 user credentials:

    mysql -u root -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:

    mysql -u root -e "GRANT PROCESS ON *.* TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'clustercheckpassword\!';"
    
    mysql -u root -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 /etc/percona-xtradb-cluster.conf.d/wsrep.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
  13. Copy or move the backup data from the MySQL data directory at /backups/mysql to the Percona data directory at /var/lib/mysql.

For more information on restoring from backup data, see the Percona documentation on restoring a full backup.

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. Install the Extended Internet Service Daemon xinetd:

    yum -y install xinetd
  2. Create a script to launch clustercheck on request, on port 9198:

    cat > /etc/xinetd.d/mysqlchk << EOF
    # default: on
    # description: mysqlchk
    service mysqlchk
    {
    disable = no
    flags = REUSE
    socket_type = stream
    port = 9198
    wait = no
    user = nobody
    server = /usr/bin/clustercheck
    log_on_failure += USERID
    only_from = 0.0.0.0/0
    per_source = UNLIMITED
    }
    EOF
  3. Add the script mysqlchk as a service to be run on request on port 9198 by xinetd. (edited)

    echo "mysqlchk        9198/tcp                # mysqlchk" >> /etc/services
  4. Restart xinetd:

    systemctl restart xinetd

Configure additional nodes

bash <(curl -s -k https://<myspeedyuser>:<myspeedypassword>@speedy.moogsoft.com/repo/aiops/install_percona_nodes.sh) -i 1.1.1.1,1.1.1.2,1.1.1.3 -u mysstusername -w mysstpassword

Configure for Disaster Recovery

Install HA Proxy