Alauda Database Service for MySQL
  • English

    • Migrating MySQL-PXC Data to MySQL-MGR

    TOC

    PrerequisitesNotesWorkflowCheck MySQL-PXC Cluster StatusLocate MySQL-PXC Read/Write NodesStop MySQL-PXC from Providing External ServicesCreate MySQL-MGR InstanceCheck MySQL-MGR Cluster StatusLocate MySQL-MGR Read/Write NodesDetermine Which Databases Need to Be Backed Up from MySQL-PXCDeploy MySQL-MGR Container for Backup and RecoveryCreate PVCCreate PVDeploy MySQL-MGR ContainerBackup MySQL-PXC Business DatabasesRestore Business DataBackup MySQL-PXC Business AccountsImport Business Application AccountsAdditional: Using mysqldump Backup Method on the Compute Node (Host)

    Prerequisites

    • MySQL-PXC nodes must be down.

    • The cluster must have sufficient CPU, memory, storage, and other resources to create a MySQL-MGR cluster with the same specifications as MySQL-PXC.

    Notes

    • The application side should update the MySQL driver to the MySQL-MGR version; you can refer to Connectors and APIs.

    • Use utf8mb4 character set.

      • The application side should use utf8mb4.

      • On the database side, convert the library and tables to utf8mb4.

      The SQL for character conversion is as follows.

      # Convert the database character set to utf8mb4
ALTER DATABASE db_name DEFAULT CHARACTER SET = utf8mb4;

# Convert the table character set to utf8mb4
ALTER TABLE db_name.table_name CONVERT TO CHARACTER SET utf8mb4;

      Tip: The larger the amount of data in the table, the longer the conversion time.

    • MySQL-MGR enforces that tables must have a primary key.

      • Even if there is no primary key in the table, an INVISIBLE primary key will be automatically created during import into MySQL-MGR; it is recommended that business sides use explicit primary keys.

        Tip: It will appear in SHOW TABLE, but the SELECT will hide this field.

      • To find tables in the business database that lack a primary key:

        SELECT
    CONCAT(t1.TABLE_SCHEMA, '.', t1.TABLE_NAME)
FROM
    information_schema.tables t1
        LEFT JOIN
    (SELECT DISTINCT
        t.TABLE_SCHEMA, t.TABLE_NAME, t.COLUMN_KEY
    FROM
        information_schema.`COLUMNS` t
    WHERE
        t.COLUMN_KEY = 'PRI') t2 ON t1.TABLE_SCHEMA = t2.TABLE_SCHEMA
        AND t1.TABLE_NAME = t2.TABLE_NAME
WHERE
    t1.TABLE_SCHEMA NOT IN ('information_schema' , 'mysql', 'performance_schema', 'sys')
        AND t2.COLUMN_KEY IS NULL;

      • Add an auto-incrementing primary key:

        ALTER TABLE table_name ADD COLUMN id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY;

    Workflow

    Note: When using the example code in this document, please replace with actual instance parameters.

    Check MySQL-PXC Cluster Status

    The MySQL-PXC cluster status must be normal before backing up data.

    1. In the left navigation bar, click MySQL-PXC.

    2. Click Namespace Name.

    3. Click Instance Name.

    4. In the Details tab, Basic Information, Resource Configuration, and ProxySQL status should be Running.

    5. In the Topology tab, the status of each ProxySQL and MySQL node should be Normal.

    Locate MySQL-PXC Read/Write Nodes

    1. Find the IP of ProxySQL.

      [root@g1-rm1 ~]# kubectl get pod -n demo-ds1 -owide | grep 'proxysql'
demo-proxysql-0                 3/3     Running   0               4h57m   10.4.0.28     192.168.176.247   <none>           <none>
demo-proxysql-1                 3/3     Running   0               4h57m   10.4.0.49     192.168.177.35    <none>           <none>
demo-proxysql-2                 3/3     Running   0               4h57m   10.4.0.54     192.168.176.25    <none>           <none>

    2. Connect to any ProxySQL Pod to find the read/write nodes.

      # kubectl exec -it demo-proxysql-0 -c proxysql -n demo-ds1 -- mysql -uproxyadmin -h10.4.0.28 -p'jtr8eO0kS?lq' -P6032 -e "SELECT * FROM runtime_mysql_servers WHERE hostgroup_id=11"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------+------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname                                       | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 11           | demo-pxc-0.demo-pxc.demo-ds1.svc.cluster.local | 3306 | 0         | ONLINE | 1001   | 0           | 600             | 0                   | 0       | 0              |         |
+--------------+------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

    From the output, determine the name of the MySQL-PXC to which the read/write node demo-pxc-0.demo-pxc.demo-ds1.svc.cluster.local belongs.

    [root@g1-rm1 ~]# kubectl exec -it demo-pxc-0 -n demo-ds1 -- mysql -uroot -hlocalhost -p'jtr8eO0kS?lq' -e "SHOW GLOBAL STATUS LIKE 'wsrep_cluster_state_uuid'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------------+--------------------------------------+
| Variable_name            | Value                                |
+--------------------------+--------------------------------------+
| wsrep_cluster_state_uuid | 60fd1cc7-1628-11ee-90b2-c6e61fc5eaef |
+--------------------------+--------------------------------------+

[root@g1-rm1 ~]# kubectl exec -it demo-pxc-1 -n demo-ds1 -- mysql -uroot -hlocalhost -p'jtr8eO0kS?lq' -e "SHOW GLOBAL STATUS LIKE 'wsrep_cluster_state_uuid'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------------+--------------------------------------+
| Variable_name            | Value                                |
+--------------------------+--------------------------------------+
| wsrep_cluster_state_uuid | 60fd1cc7-1628-11ee-90b2-c6e61fc5eaef |
+--------------------------+--------------------------------------+

[root@g1-rm1 ~]# kubectl exec -it demo-pxc-2 -n demo-ds1 -- mysql -uroot -hlocalhost -p'jtr8eO0kS?lq' -e "SHOW GLOBAL STATUS LIKE 'wsrep_cluster_state_uuid'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------------+--------------------------------------+
| Variable_name            | Value                                |
+--------------------------+--------------------------------------+
| wsrep_cluster_state_uuid | 60fd1cc7-1628-11ee-90b2-c6e61fc5eaef |
+--------------------------+--------------------------------------+

    Stop MySQL-PXC from Providing External Services

    Note: Do not perform write operations on the application side, as this will result in an incomplete backup.

    1. In the left navigation bar, click MySQL-PXC.

    2. Click Namespace Name.

    3. Click Instance Name.

    4. Click Actions > Update.

    5. Click YAML.

    6. Change the value of the pxc.proxysql.size parameter to 0.

    7. Click Update.

    Create MySQL-MGR Instance

    Note: The resource specifications for the created MySQL-MGR instance should not be lower than those of the MySQL-PXC instance.

    Check MySQL-MGR Cluster Status

    1. In the left navigation bar, click MySQL-MGR.

    2. Click Namespace Name.

    3. Click Instance Name.

    4. In the Details tab, Basic Information, Resource Configuration, and MySQL Router status should be Running.

    5. In the YAML tab, the values of status.conditions.type and status.state should be ready.

    Locate MySQL-MGR Read/Write Nodes

    Nodes with MEMBER_ROLE as PRIMARY are the read/write nodes of MySQL-MGR.

    [root@g1-rm1 ~]# kubectl exec -it demo-0 -c mysql -n demo-ds1 -- mysql -uroot -hlocalhost -p'GLK1nqWF17JqrvpJ' -e "SELECT * FROM performance_schema.replication_group_members"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 5bc1839e-162a-11ee-a212-00000071f44e | demo-0.demo |        3306 | ONLINE       | PRIMARY     | 8.0.30         | MySQL                      |
| group_replication_applier | 6f9f1f36-162a-11ee-a21a-000000ade638 | demo-1.demo |        3306 | ONLINE       | SECONDARY   | 8.0.30         | MySQL                      |
| group_replication_applier | 96300b66-162a-11ee-a33f-00000093bb13 | demo-2.demo |        3306 | ONLINE       | SECONDARY   | 8.0.30         | MySQL                      |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+

    Determine Which Databases Need to Be Backed Up from MySQL-PXC

    • Exclude MySQL system databases: information_schema, mysql, performance_schema, and sys.

    • For business databases: Record the business databases to back up, and when using the mysqldump backup tool, specify the databases with the -B option.

    For example, from the following queried database list, you need to back up the business databases test1, test2, and test3.

    [root@g1-rm1 ~]# kubectl exec -it demo-pxc-0 -n demo-ds1 -c pxc -- mysql -uroot -p'jtr8eO0kS?lq' -h10.4.0.35 -e "SHOW DATABASES"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
| test2              |
| test3              |
+--------------------+

    Deploy MySQL-MGR Container for Backup and Recovery

    Create PVC

    1. In the Container Platform view, in the left navigation bar, click Storage > Persistent Volume Claims.

    2. Click Create Persistent Volume Claim.

    3. Fill in the PVC configuration parameters.

      Note: Use TopoLVM as the storage class.

    4. Click Create; the PVC status should be Pending Binding.

    Create PV

    Please contact the Platform Administrator to create a PV based on the following YAML. After creating the PV, check that the PVC status is Bound.

    apiVersion: v1
kind: PersistentVolume
metadata:
  name: mgr-backcup-pv # pv name
  annotations: {}
  labels: {}
spec:
  capacity:
    storage: 5Gi # specify storage capacity
  csi:
    driver: topolvm.cybozu.com
    volumeHandle: baef103d-d2de-4ca9-bb4b-1dad722975b9
    fsType: xfs
  accessModes:
    - ReadWriteOnce
  claimRef:
    kind: PersistentVolumeClaim
    namespace: demo-ds1	# specify namespace
    name: mgr-backcup-pvc # specify the name to bind PVC
  persistentVolumeReclaimPolicy: Delete
  storageClassName: sc-topolvm # specify storage class
  volumeMode: Filesystem
  nodeAffinity:
    required:
      nodeSelectorTerms:
        - matchExpressions:
            - key: topology.topolvm.cybozu.com/node
              operator: In
              values:
                - 192.168.177.35 # specify the IP of the host machine where PV is created

    Deploy MySQL-MGR Container

    1. Check the MySQL-MGR image address.

      [root@g1-rm1 ~]# kubectl get pod demo-0 -n demo-ds1 -o yaml | grep 'image:' | grep 'mysql-server'
    image: idc1.xmysql.com/3rdparty/mysql/mysql-server:8.0.30-cfd84cde
    image: idc1.xmysql.com/3rdparty/mysql/mysql-server:8.0.30-cfd84cde

    2. In the Container Platform view, in the left navigation bar, click Compute Components > Deployments.

    3. Click Create Deployment.

    4. Select Input method, fill in Image Address, and click OK.

    5. In Pods > Volumes, click Add.

    6. Fill in the volume configuration, using the PVC created in this document as the storage volume for the MySQL container.

    7. Click Add.

    8. Fill in the container configuration.

    9. Click Create.

    Backup MySQL-PXC Business Databases

    1. Obtain the IP of the MySQL-PXC read/write Pod.

      [root@g1-rm1 ~]# kubectl get pod -o wide -n demo-ds1 | grep 'pxc'
demo-pxc-0                      3/3     Running   0              4d4h    10.4.0.35     192.168.176.227   <none>           <none>
demo-pxc-1                      3/3     Running   0              4d4h    10.4.0.70     192.168.176.247   <none>           <none>
demo-pxc-2                      3/3     Running   0              4d4h    10.4.0.162    192.168.176.25    <none>           <none>

    2. Obtain the root password for MySQL-PXC.

      [root@g1-rm1 ~]# kubectl exec -it demo-pxc-0 -n demo-ds1 -c pxc -- /bin/bash
bash-5.1$ env | grep 'MYSQL_ROOT_PASSWORD'
MYSQL_ROOT_PASSWORD=jtr8eO0kS?lq

    3. Enter the MySQL backup and recovery container created in this document, for example, Pod name is mgr-backcup-9f87d46c4-kwkps.

      [root@g1-rm1 ~]# kubectl exec -it mgr-backcup-9f87d46c4-kwkps -n demo-ds1 -- /bin/bash

      Tip: The Pod name can be viewed in the container group list of this deployment.

    4. Backup the business database.

      [root@mgr-backcup-9f87d46c4-kwkps /]# mysqldump --user=root --host=10.4.0.35 --password='jtr8eO0kS?lq' --column-statistics=0 --single-transaction --source-data=1 --set-gtid-purged=AUTO --triggers -R -E -B test1 test2 test3 > /var/lib/mysql/$(date +"%Y%m%d%H%M%S")_fullbackup.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

      Parameter explanation:

      --password: Root password of MySQL-PXC.

      --host: Specify the IP address of the read/write Pod of MySQL-PXC.

      -B: Specify the names of the business databases to back up in MySQL-PXC, separated by spaces.

    5. Check the size of the logical backup.

      [root@mgr-backcup-9f87d46c4-kwkps /]# du -lh /var/lib/mysql/20230705102203_fullbackup.sql
677M	/var/lib/mysql/20230705102203_fullbackup.sql

    Restore Business Data

    [root@g1-rm1 ~]# kubectl get pod demo-0 -n demo-ds1 -o wide
NAME     READY   STATUS    RESTARTS   AGE   IP           NODE             NOMINATED NODE   READINESS GATES
demo-0   3/3     Running   0          20h   10.4.0.170   192.168.177.35   <none>           <none>

[root@g1-rm1 ~]# kubectl exec -it mgr-backcup-9f87d46c4-kwkps -n demo-ds1 -- /bin/bash

[root@mgr-backcup-9f87d46c4-kwkps /]# mysql -uroot -h10.4.0.170 -p'GLK1nqWF17JqrvpJ' < /var/lib/mysql/20230705102203_fullbackup.sql
mysql: [Warning] Using a password on the command line interface can be insecure.


[root@mgr-backcup-9f87d46c4-kwkps /]# mysql -uroot -h10.4.0.170 -p'GLK1nqWF17JqrvpJ'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 367123
Server version: 8.0.30 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Cannot read termcap database;
using dumb terminal settings.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW DATABASES;
+-------------------------------+
| Database                      |
+-------------------------------+
| information_schema            |
| mysql                         |
| mysql_innodb_cluster_metadata |
| performance_schema            |
| sys                           |
| test1                         |
| test2                         |
| test3                         |
+-------------------------------+
8 rows in set (0.01 sec)

mysql> use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLES;
+-----------------+
| Tables_in_test1 |
+-----------------+
| sbtest1         |
| sbtest2         |
| sbtest3         |
| sbtest4         |
| sbtest5         |
| sbtest6         |
+-----------------+
6 rows in set (0.01 sec)

mysql> SELECT COUNT(1) FROM test1.sbtest1;
+----------+
| COUNT(1) |
+----------+
|   200000 |
+----------+
1 row in set (0.03 sec)

mysql> SHOW CREATE TABLE test2.sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`my_row_id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=200003 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE test3.sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=600001 DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)

    Backup MySQL-PXC Business Accounts

    1. Enter the MySQL-MGR Pod to back up the MySQL-PXC user table.

      [root@g1-rm1 ~]# kubectl exec -it mgr-backcup-9f87d46c4-kwkps -n demo-ds1 -- /bin/bash

[root@mgr-backcup-9f87d46c4-kwkps /]# mysqldump --user=root --host=10.4.0.35 --password='jtr8eO0kS?lq' --column-statistics=0 --single-transaction --source-data=1 --set-gtid-purged=AUTO --triggers -R -E mysql user > /var/lib/mysql/$(date +"%Y%m%d%H%M%S")_mysql.user.sql

[root@mgr-backcup-9f87d46c4-kwkps /]# du -lh /var/lib/mysql/20230705132502_mysql.user.sql
8.0K	/var/lib/mysql/20230705132502_mysql.user.sql

    2. Obtain the SQL statements for the accounts: INSERT INTO.

      [root@mgr-backcup-9f87d46c4-kwkps /]# cat /var/lib/mysql/20230705132502_mysql.user.sql | grep 'INSERT INTO'
INSERT INTO `user` VALUES ('localhost','root','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'mysql_native_password','*40C19F6316A46A0E68DCCBC18034271BE5DDD704','N','2023-06-29 02:55:28',NULL,'N'),('localhost','mysql.session','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','Y','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'mysql_native_password','*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE','N','2023-06-29 02:55:09',NULL,'Y'),('localhost','mysql.sys','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'mysql_native_password','*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE','N','2023-06-29 02:55:09',NULL,'Y'),('%','root','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'mysql_native_password','*40C19F6316A46A0E68DCCBC18034271BE5DDD704','N','2023-06-29 02:55:28',NULL,'N'),('%','operator','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'mysql_native_password','*40C19F6316A46A0E68DCCBC18034271BE5DDD704','N','2023-06-29 02:55:28',NULL,'N'),('%','xtrabackup','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'mysql_native_password','*40C19F6316A46A0E68DCCBC18034271BE5DDD704','N','2023-06-29 02:55:28',NULL,'N'),('%','monitor','Y','N','N','N','N','N','Y','N','Y','N','N','N','N','N','N','Y','N','N','N','N','Y','N','N','N','N','N','N','N','N','','','','',0,0,0,100,'mysql_native_password','*40C19F6316A46A0E68DCCBC18034271BE5DDD704','N','2023-06-29 02:55:28',NULL,'N'),('localhost','clustercheck','N','N','N','N','N','N','N','N','Y','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'mysql_native_password','*40C19F6316A46A0E68DCCBC18034271BE5DDD704','N','2023-06-29 02:55:28',NULL,'N'),('%','test_user_2','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'mysql_native_password','*BFC6130681D565DDE4C938BCDCD36073776A8F99','N','2023-07-03 07:13:13',NULL,'N'),('%','test_user_3','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'mysql_native_password','*BFC6130681D565DDE4C938BCDCD36073776A8F99','N','2023-07-03 07:13:13',NULL,'N'),('%','test_user_1','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'mysql_native_password','*BFC6130681D565DDE4C938BCDCD36073776A8F99','N','2023-07-03 07:14:52',NULL,'N');

    3. Delete the MySQL-PXC system users, keeping only the business application accounts.

      • Delete system accounts: ('localhost','root'), ('localhost','mysql.session'), ('localhost','mysql.sys'), ('%','root'), ('%','operator'), ('%','monitor'), ('localhost','clustercheck').

      • Retain business application accounts: test_user_1, test_user_2, test_user_3.

        INSERT INTO `user` VALUES ('%','test_user_2','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'mysql_native_password','*BFC6130681D565DDE4C938BCDCD36073776A8F99','N','2023-07-03 07:13:13',NULL,'N'),('%','test_user_3','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'mysql_native_password','*BFC6130681D565DDE4C938BCDCD36073776A8F99','N','2023-07-03 07:13:13',NULL,'N'),('%','test_user_1','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'mysql_native_password','*BFC6130681D565DDE4C938BCDCD36073776A8F99','N','2023-07-03 07:14:52',NULL,'N');

    Import Business Application Accounts

    1. Modify the inserted account records.

      Note: Do not import the root account.

      MySQL-MGR has added 6 fields to mysql.user compared to MySQL-PXC, so when inserting account records, the following 6 fields need to be added: Create_role_priv: Y, Drop_role_priv: Y, Password_reuse_history: NULL, Password_reuse_time: NULL, Password_require_current: NULL, User_attributes: NULL. Thus, appending the following values to each insert record: , 'Y', 'Y', NULL, NULL, NULL, NULL.

      INSERT INTO `user` VALUES ('%','test_user_2','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'mysql_native_password','*BFC6130681D565DDE4C938BCDCD36073776A8F99','N','2023-07-03 07:13:13',NULL,'N', 'Y', 'Y', NULL, NULL, NULL, NULL),('%','test_user_3','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'mysql_native_password','*BFC6130681D565DDE4C938BCDCD36073776A8F99','N','2023-07-03 07:13:13',NULL,'N', 'Y', 'Y', NULL, NULL, NULL, NULL),('%','test_user_1','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'mysql_native_password','*BFC6130681D565DDE4C938BCDCD36073776A8F99','N','2023-07-03 07:14:52',NULL,'N', 'Y', 'Y', NULL, NULL, NULL, NULL);

    2. Insert account records into MySQL-MGR.

      [root@g1-rm1 ~]# kubectl exec -it mgr-backcup-9f87d46c4-kwkps -n demo-ds1 -- /bin/bash

[root@g1-rm1 ~]# mysql -uroot -h10.4.0.170 -p'GLK1nqWF17JqrvpJ'

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

mysql> INSERT INTO `user` VALUES ('%','test_user_2','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','
Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'mysql_native_password','*BFC6130681D565DDE4C938BCDCD36073776A8F99','N
','2023-07-03 07:13:13',NULL,'N', 'Y', 'Y', NULL, NULL, NULL, NULL),('%','test_user_3','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','N
','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'mysql_native_password','*BFC613068
1D565DDE4C938BCDCD36073776A8F99','N','2023-07-03 07:13:13',NULL,'N', 'Y', 'Y', NULL, NULL, NULL, NULL),('%','test_user_1','Y','Y'
,'Y','Y','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,
'mysql_native_password','*BFC6130681D565DDE4C938BCDCD36073776A8F99','N','2023-07-03 07:14:52',NULL,'N', 'Y', 'Y', NULL, NULL, NUL
L, NULL);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT user,host,plugin FROM mysql.user WHERE user LIKE 'test%';
+-------------+------+-----------------------+
| user        | host | plugin                |
+-------------+------+-----------------------+
| test_user_1 | %    | mysql_native_password |
| test_user_2 | %    | mysql_native_password |
| test_user_3 | %    | mysql_native_password |
+-------------+------+-----------------------+
3 rows in set (0.00 sec)

    3. Verify whether the accounts are functioning correctly.

      [root@g1-rm1 ~]# kubectl get pod -n demo-ds1 -o wide | grep demo
demo-0                          3/3     Running   0               4d11h   10.4.0.170    192.168.177.35    <none>           <none>
demo-1                          3/3     Running   1 (4d11h ago)   4d11h   10.4.0.176    192.168.176.247   <none>           <none>
demo-2                          3/3     Running   1 (4d11h ago)   4d11h   10.4.0.198    192.168.176.227   <none>           <none>

[root@mgr-backcup-9f87d46c4-kwkps /]# mysql -utest_user_1 -h10.4.0.170 -p'zEjQM96Ha1=v' -e "SELECT USER()";
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------------+
| USER()                  |
+-------------------------+
| test_user_1@10.4.210.58 |
+-------------------------+
[root@mgr-backcup-9f87d46c4-kwkps /]# mysql -utest_user_2 -h10.4.0.176 -p'zEjQM96Ha1=v' -e "SELECT USER()";
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------------+
| USER()                  |
+-------------------------+
| test_user_2@10.4.210.58 |
+-------------------------+
[root@mgr-backcup-9f87d46c4-kwkps /]# mysql -utest_user_3 -h10.4.0.198 -p'zEjQM96Ha1=v' -e "SELECT USER()";
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------------+
| USER()                  |
+-------------------------+
| test_user_3@10.4.210.58 |
+-------------------------+

    Additional: Using mysqldump Backup Method on the Compute Node (Host)

    If the current cluster lacks resources and a same-spec MySQL-MGR cluster has not been deployed, you can use mysqldump on the host machine to perform the backup.

    Procedure:

    1. Ensure that the MySQL-PXC cluster status is normal.

    2. Stop business read/write and set the ProxySQL replicas to 0.

    3. Perform a backup of MySQL-PXC.