将 MySQL-PXC 数据迁移至 MySQL-MGR

前提条件

MySQL-PXC 节点必须处于停止状态。
集群必须具备足够的 CPU、内存、存储及其他资源，以创建与 MySQL-PXC 规格相同的 MySQL-MGR 集群。

注意事项

应用端应将 MySQL 驱动升级为 MySQL-MGR 版本，参考文档：Connectors and APIs。

使用 utf8mb4 字符集。

应用端应使用 utf8mb4。
数据库端需将库和表转换为 utf8mb4。

字符转换的 SQL 如下：

# 将数据库字符集转换为 utf8mb4
ALTER DATABASE db_name DEFAULT CHARACTER SET = utf8mb4;

# 将表字符集转换为 utf8mb4
ALTER TABLE db_name.table_name CONVERT TO CHARACTER SET utf8mb4;

提示：表中数据越多，转换时间越长。

MySQL-MGR 强制要求表必须有主键。

即使表中没有主键，导入 MySQL-MGR 时也会自动创建一个 INVISIBLE 主键，建议业务侧使用显式主键。

提示：该主键会在 SHOW TABLE 中显示，但 SELECT 查询时会隐藏此字段。

查询业务库中缺少主键的表：

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;

添加自增主键：

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

操作流程

注意：文档中的示例代码请替换为实际实例参数。

检查 MySQL-PXC 集群状态

备份数据前，MySQL-PXC 集群状态必须正常。

在左侧导航栏点击 MySQL-PXC。
点击 Namespace Name。
点击 Instance Name。
在 Details 标签页，基本信息、资源配置 和 ProxySQL 状态应为 Running。
在 Topology 标签页，各 ProxySQL 和 MySQL 节点状态应为 Normal。

定位 MySQL-PXC 读写节点

查询 ProxySQL 的 IP。

[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>

连接任一 ProxySQL Pod，查询读写节点。

# 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              |         |
+--------------+------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

根据输出，确定读写节点 demo-pxc-0.demo-pxc.demo-ds1.svc.cluster.local 所属的 MySQL-PXC 名称。

[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 |
+--------------------------+--------------------------------------+

停止 MySQL-PXC 对外服务

注意：应用端请停止写操作，否则备份不完整。

在左侧导航栏点击 MySQL-PXC。
点击 Namespace Name。
点击 Instance Name。
点击操作 > 更新。
点击 YAML。
将 pxc.proxysql.size 参数值修改为 0。
点击更新。

创建 MySQL-MGR 实例

注意：创建的 MySQL-MGR 实例资源规格不得低于 MySQL-PXC 实例。

检查 MySQL-MGR 集群状态

在左侧导航栏点击 MySQL-MGR。
点击 Namespace Name。
点击 Instance Name。
在 Details 标签页，基本信息、资源配置 和 MySQL Router 状态应为 Running。
在 YAML 标签页，status.conditions.type 和 status.state 的值应为 ready。

定位 MySQL-MGR 读写节点

MEMBER_ROLE 为 PRIMARY 的节点即为 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                      |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+

确定需备份的 MySQL-PXC 数据库

排除 MySQL 系统库：information_schema、mysql、performance_schema 和 sys。
业务库：记录需备份的业务库，使用 mysqldump 备份工具时，使用 -B 参数指定库名。

例如，以下查询出的数据库列表中，需备份业务库 test1、test2 和 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              |
+--------------------+

部署 MySQL-MGR 容器用于备份与恢复

创建 PVC

在 容器平台视图左侧导航栏，点击存储 > 持久卷声明。
点击 创建持久卷声明。
填写 PVC 配置参数。

注意：存储类使用 TopoLVM。
点击创建，PVC 状态应为 Pending Binding。

创建 PV

请联系 平台管理员，根据以下 YAML 创建 PV。创建完成后，确认 PVC 状态为 Bound。

apiVersion: v1
kind: PersistentVolume
metadata:
  name: mgr-backcup-pv # pv 名称
  annotations: {}
  labels: {}
spec:
  capacity:
    storage: 5Gi # 指定存储容量
  csi:
    driver: topolvm.cybozu.com
    volumeHandle: baef103d-d2de-4ca9-bb4b-1dad722975b9
    fsType: xfs
  accessModes:
    - ReadWriteOnce
  claimRef:
    kind: PersistentVolumeClaim
    namespace: demo-ds1	# 指定命名空间
    name: mgr-backcup-pvc # 指定绑定的 PVC 名称
  persistentVolumeReclaimPolicy: Delete
  storageClassName: sc-topolvm # 指定存储类
  volumeMode: Filesystem
  nodeAffinity:
    required:
      nodeSelectorTerms:
        - matchExpressions:
            - key: topology.topolvm.cybozu.com/node
              operator: In
              values:
                - 192.168.177.35 # 指定创建 PV 的宿主机 IP

部署 MySQL-MGR 容器

查看 MySQL-MGR 镜像地址。

[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

在 容器平台视图左侧导航栏，点击 计算组件 > 部署。
点击 创建部署。
选择输入方式，填写 镜像地址，点击确定。
在 Pods > 卷中点击添加。
填写卷配置，使用本文档创建的 PVC 作为 MySQL 容器的存储卷。
点击添加。
填写容器配置。
点击创建。

备份 MySQL-PXC 业务数据库

获取 MySQL-PXC 读写 Pod 的 IP。

[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>

获取 MySQL-PXC root 密码。

[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

进入本文档创建的 MySQL 备份恢复容器，例如 Pod 名为 mgr-backcup-9f87d46c4-kwkps。
[root@g1-rm1 ~]# kubectl exec -it mgr-backcup-9f87d46c4-kwkps -n demo-ds1 -- /bin/bash
提示：Pod 名称可在该部署的容器组列表中查看。

备份业务数据库。

[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.

参数说明：

--password：MySQL-PXC root 密码。

--host：指定 MySQL-PXC 读写 Pod 的 IP。

-B：指定需备份的 MySQL-PXC 业务库名称，多个库名用空格分隔。

查看逻辑备份文件大小。

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

恢复业务数据

[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)

备份 MySQL-PXC 业务账号

进入 MySQL-MGR Pod，备份 MySQL-PXC 用户表。

[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

获取账号的 SQL 语句：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');

删除 MySQL-PXC 系统用户，仅保留业务应用账号。

删除系统账号：('localhost','root')、('localhost','mysql.session')、('localhost','mysql.sys')、('%','root')、('%','operator')、('%','monitor')、('localhost','clustercheck')。

保留业务应用账号：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');

导入业务应用账号

修改插入的账号记录。

注意：不要导入 root 账号。

MySQL-MGR 相较 MySQL-PXC 在 mysql.user 表中新增了 6 个字段，插入账号记录时需添加以下 6 个字段：Create_role_priv: Y、Drop_role_priv: Y、Password_reuse_history: NULL、Password_reuse_time: NULL、Password_require_current: NULL、User_attributes: NULL。即在每条插入记录后追加：, '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);

将账号记录插入 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)

验证账号是否正常使用。

[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 |
+-------------------------+

额外说明：在计算节点（宿主机）使用 mysqldump 备份方式

若当前集群资源不足，且未部署同规格 MySQL-MGR 集群，可在宿主机使用 mysqldump 进行备份。

操作步骤：

确认 MySQL-PXC 集群状态正常。
停止业务读写，设置 ProxySQL 副本数为 0。
对 MySQL-PXC 进行备份。

#将 MySQL-PXC 数据迁移至 MySQL-MGR

#目录

#前提条件

#注意事项

#操作流程

#检查 MySQL-PXC 集群状态

#定位 MySQL-PXC 读写节点

#停止 MySQL-PXC 对外服务

#创建 MySQL-MGR 实例

#检查 MySQL-MGR 集群状态

#定位 MySQL-MGR 读写节点

#确定需备份的 MySQL-PXC 数据库

#部署 MySQL-MGR 容器用于备份与恢复

#创建 PVC

#创建 PV

#部署 MySQL-MGR 容器

#备份 MySQL-PXC 业务数据库

#恢复业务数据

#备份 MySQL-PXC 业务账号

#导入业务应用账号

#额外说明：在计算节点（宿主机）使用 mysqldump 备份方式