32

Migration of a MySQL Database to a Kubernetes Cluster Using Asynchronous Replica...

 3 years ago
source link: https://www.percona.com/blog/migration-of-a-mysql-database-to-a-kubernetes-cluster-using-asynchronous-replication/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client
Migration of a MySQL Database to a Kubernetes Cluster Using Asynchronous Replication

Migration of a MySQL Database to a Kubernetes Cluster Using Asynchronous ReplicationNowadays, more and more companies are thinking about the migration of their infrastructure to Kubernetes. Databases are no exception. There were a lot of k8s operators that were created to simplify the different types of deployments and also perform routine day-to-day tasks like making the backups, renewing certificates, and so on.  If a few years ago nobody wanted to even listen about running databases in Kubernetes,  everything has changed now.

At Percona, we created a few very featureful k8s operators for Percona Server for MongoDB, PostgreSQL, and MySQL databases. Today we will talk about using cross-site replication – a new feature that was added to the latest release of Percona Distribution for MySQL Operator. This feature is based on synchronous connection failover mechanism.
The cross-site replication involves configuring one Percona XtraDB Cluster or a single/several MySQL servers as Source, and another Percona XtraDB Cluster (PXC) as a replica to allow asynchronous replication between them.  If an operator has several sources in custom resource (CR), it will automatically handle connection failure of the source DB.
This cross-site replication feature is supported only since MySQL 8.0.23, but you can read about migrating MySQL of earlier versions in this blog post.

The Goal

Migrate the MySQL database, which is deployed on-prem or in the cloud, to the Percona Distribution for MySQL Operator using asynchronous replication. This approach helps you reduce downtime and data loss for your application.

So, we have the following setup:

Migration of MySQL database to Kubernetes cluster using asynchronous replication

The following components are used:

1. MySQL 8.0.23 database (in my case it is Percona Server for MySQL) which is deployed in DO (as a Source) and Percona XtraBackup for the backup. In my test deployment, I use only one server as a Source to simplify the deployment. Depending on your topology of DB deployment, you can use several servers to use synchronous connection failover mechanism on the operator’s end.

2. Google Kubernetes Engine (GKE) cluster where Percona Distribution for MySQL Operator is deployed with PXC cluster (as a target).

3. AWS S3 bucket is used to save the backup from MySQL DB and then to restore the PXC cluster in k8s.

The following steps should be done to perform the migration procedure:

1. Make the MySQL database backup using Percona XtraBackup and upload it to the S3 bucket using xbcloud.

2. Perform the restore of the MySQL database from the S3 bucket into the PXC cluster which is deployed in k8s.

3. Configure asynchronous replication between MySQL server and PXC cluster managed by k8s operator.

As a result, we have asynchronous replication between MySQL server and PXC cluster in k8s which is in read-only mode.

Migration

Configure the target PXC cluster managed by k8s operator:

1. Deploy Percona Distribution for MySQL Operator on Kubernetes (I have used GKE 1.20).

Shell
# clone the git repository
git clone -b v1.9.0 https://github.com/percona/percona-xtradb-cluster-operator
cd percona-xtradb-cluster-operator
# deploy the operator
kubectl apply -f deploy/bundle.yaml

2. Create PXC cluster using the default custom resource manifest (CR).

Shell
# create my-cluster-secrets secret (do no use default passwords for production systems)
kubectl apply -f deploy/secrets.yaml
# create cluster by default it will be PXC 8.0.23
kubectl apply -f deploy/cr.yaml

3. Create the secret with credentials for the AWS S3 bucket which will be used for access to the S3 bucket during the restoration procedure.

# create S3-secret.yaml file with following content, and use correct credentials instead of XXXXXX
apiVersion: v1
kind: Secret
metadata:
  name: aws-s3-secret
type: Opaque
data:
  AWS_ACCESS_KEY_ID: XXXXXX
  AWS_SECRET_ACCESS_KEY: XXXXXX
Shell
# create secret
kubectl apply -f S3-secret.yaml

Configure the Source MySQL Server

1. Install Percona Server for MySQL 8.0.23 and Percona XtraBackup for the backup. Refer to the Installing Percona Server for MySQL and Installing Percona XtraBackup chapters in the documentation for installation instructions.

NOTE:
You need to add the following options to my.cnf to enable GTID support; otherwise, replication will not work because it is used by the PXC cluster  by default.

Shell
[mysqld]
enforce_gtid_consistency=ON
gtid_mode=ON

2. Create all needed users who will be used by k8s operator, the password should be the same as in deploy/secrets.yaml. Also, please note that the password for the root user should be the same as in deploy/secrets.yaml file for k8s the secret.  In my case, I used our default passwords from deploy/secrets.yaml file.

MySQL
CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitory' WITH MAX_USER_CONNECTIONS 100;
GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT, RELOAD ON *.* TO 'monitor'@'%';
GRANT SERVICE_CONNECTION_ADMIN ON *.* TO 'monitor'@'%';
CREATE USER 'operator'@'%' IDENTIFIED BY 'operatoradmin';
GRANT ALL ON *.* TO 'operator'@'%' WITH GRANT OPTION;
CREATE USER 'xtrabackup'@'%' IDENTIFIED BY 'backup_password';
GRANT ALL ON *.* TO 'xtrabackup'@'%';
CREATE USER 'replication'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* to 'replication'@'%';
FLUSH PRIVILEGES;

2. Make the backup of MySQL database using XtraBackup tool and upload it to S3 bucket.

Shell
# export aws credentials
export AWS_ACCESS_KEY_ID=XXXXXX
export AWS_SECRET_ACCESS_KEY=XXXXXX
#make the backup
xtrabackup --backup --stream=xbstream --target-dir=/tmp/backups/ --extra-lsndirk=/tmp/backups/  --password=root_password | xbcloud put --storage=s3 --parallel=10 --md5 --s3-bucket="mysql-testing-bucket" "db-test-1"

Now, everything is ready to perform the restore of the backup on the target database. So, let’s get back to our k8s cluster.

Configure the Asynchronous Replication to the Target PXC Cluster

If you have a completely clean source database (without any data), you can skip the points connected with backup and restoration of the database. Otherwise, do the following:

1. Restore the backup from the S3 bucket using the following manifest:

# create restore.yml file with following content
apiVersion: pxc.percona.com/v1
kind: PerconaXtraDBClusterRestore
metadata:
  name: restore1
spec:
  pxcCluster: cluster1
  backupSource:
    destination: s3://mysql-testing-bucket/db-test-1
      credentialsSecret: aws-s3-secret
      region: us-east-1
Shell
# trigger the restoration procedure
kubectl apply -f restore.yml

As a result, you will have a PXC cluster with data from the source DB. Now everything is ready to configure the replication.

2. Edit custom resource manifest deploy/cr.yaml  to configure spec.pxc.replicationChannels section.

spec:
    replicationChannels:
    - name: ps_to_pxc1
      isSource: false
      sourcesList:
        - host: <source_ip>
          port: 3306
          weight: 100
Shell
# apply CR
kubectl apply -f deploy/cr.yaml


Verify the Replication 

In order to check the replication, you need to connect to the PXC node and run the following commands:

Shell
kubectl exec -it cluster1-pxc-0 -c pxc -- mysql -uroot -proot_password -e "show replica status \G"
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: <ip-of-source-db>
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000004
          Read_Master_Log_Pos: 529
               Relay_Log_File: cluster1-pxc-0-relay-bin-ps_to_pxc1.000002
                Relay_Log_Pos: 738
        Relay_Master_Log_File: binlog.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 529
              Relay_Log_Space: 969
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 9741945e-148d-11ec-89e9-5ee1a3cf433f
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 3
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 9741945e-148d-11ec-89e9-5ee1a3cf433f:1-2
            Executed_Gtid_Set: 93f1e7bf-1495-11ec-80b2-06e6016a7c3d:1,
9647dc03-1495-11ec-a385-7e3b2511dacb:1-7,
9741945e-148d-11ec-89e9-5ee1a3cf433f:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name: ps_to_pxc1
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:

Also, you can verify the replication by checking that the data is changing.

Promote the PXC Cluster as a Primary

As soon as you are ready (your application was reconfigured and ready to work with the new DB) to stop the replication and promote the PXC cluster in k8s to be a primary DB, you need to perform the following simple actions:

1. Edit the deploy/cr.yaml  and comment the replicationChannels

spec:
    #replicationChannels:
    #- name: ps_to_pxc1
    #  isSource: false
    #  sourcesList:
    #    - host: <source_ip>
    #      port: 3306
    #      weight: 100

2. Stop mysqld service on the source server to be sure that no new data is written.

Shell
 systemctl stop mysqld

3. Apply a new CR for k8s operator.

Shell
# apply CR
kubectl apply -f deploy/cr.yaml

As a result, replication is stopped and the read-only mode is disabled for the PXC cluster.

Conclusion

Technologies are changing so fast that a migration procedure to k8s cluster, seeming very complex at first sight, turns out to be not so difficult and nor time-consuming. But you need to keep in mind that significant changes were made. Firstly, you migrate the database to the PXC cluster which has some peculiarities, and, of course, Kubernetes itself.  If you are ready, you can start the journey to Kubernetes right now.

The Percona team is ready to guide you during this journey. If you have any questions,  please raise the topic in the community forum.

The Percona Kubernetes Operators automate the creation, alteration, or deletion of members in your Percona Distribution for MySQL, MongoDB, or PostgreSQL environment.

Learn More About Percona Kubernetes Operators


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK