Alauda Database Service for MySQL-PXC

Migrating MySQL-PXC Data to MySQL-MGR

TOC

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 BITINT 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>
  1. 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.

ON THIS PAGE