RPM - Migrate from MySQL to Percona

Navigate to the desired location and install the Percona repository:

yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

Install Percona XtraBackup:

yum -y install percona-xtrabackup-24.x86_64

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.

When the backup is complete, locate the directory created under /backups/mysql and 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/<directory_created_in_previous_step>/

Stop MySQL:

service mysqld stop

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.

Install Percona Cluster Binary 5.7:

yum -y install Percona-XtraDB-Cluster-shared-compat-57-5.7.36 Percona-XtraDB-Cluster-shared-57-5.7.36 Percona-XtraDB-Cluster-client-57-5.7.36 Percona-XtraDB-Cluster-server-57-5.7.36 Percona-XtraDB-Cluster-shared-compat-57-5.7.36 percona-xtrabackup-24-2.4.24

Configure the Percona cluster to start in bootstrap mode.

Start the Percona donor node:

service mysqld start

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';"

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;"

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

Copy or move the backup data from the MySQL data directory at /backups/mysql to the Percona data directory at /var/lib/mysql.

Install the Extended Internet Service Daemon xinetd:

yum -y install xinetd

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

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

Restart xinetd:

systemctl restart xinetd