PolarDB:Convert a database in DRDS mode to a database in AUTO mode

Prerequisites

The version of the PolarDB-X instance to which the source DRDS database belongs is 5.4.16-16717637 or later. For more information about how to view the version of a PolarDB-X instance, see View the version of an instance.

Usage notes

• During the conversion, a read lock is applied to the source DRDS database to make it read-only. In the read-only state, no DML or DDL statements can be executed on the source DRDS database until the conversion is complete. Therefore, before you covert a database, make sure that the conversion does not affect your business.
• We recommend that you do not execute DML or DDL statements on the source DRDS database or the destination AUTO database during the conversion. Otherwise, the conversion fails.
• During the conversion, the source DRDS database is not deleted or overwritten. Instead, a new destination AUTO database is created based on the source DRDS database. After the conversion is complete, the source DRDS database is still accessible.
• Make sure that the remaining space capacity is sufficient before you convert a database. If the remaining space capacity is insufficient, the conversion fails.

Syntax

CREATE DATABASE [IF NOT EXISTS] auto_database_name
as
drds_database_name
[covert_option_list]

covert_option_list:
      convert_option [convert_option...] [{include_list|exclude_list}]

convert_option:
      mode=auto
    |    dry_run={true|false}
    |    lock={true|false}
    |    create_tables={true|false}

include_list:
      include=table_name [,table_name...]

exclude_list:
      exclude=table_name [,table_name...]

Examples

• Execute the following statement to convert the database named db_drds in DRDS mode to the database named db_auto in AUTO mode. If you execute this statement, only the schemas of the tables in the db_drds database are converted, and no table data is copied.

  CREATE DATABASE db_auto like db_drds mode=auto;

  The following output is returned:

  +-------------+
  | RESULT      |
  +-------------+
  | ALL SUCCESS |
  +-------------+
  1 row in set (10 min 32.17 sec)

• Execute the following statement to convert the database named db_drds in DRDS mode to the database named db_auto in AUTO mode. If you execute this statement, the schemas of the tables in the db_drds database are converted, and the table data is copied.

  CREATE DATABASE db_auto as db_drds mode=auto;

  The following output is returned:

  +-------------+
  | RESULT      |
  +-------------+
  | ALL SUCCESS |
  +-------------+
  1 row in set (10 min 37.30 sec)

• Execute the following statement to migrate the tb1 table in the database named db_drds in DRDS mode to a destination AUTO database that already exists. If you execute this statement, the schema of the tb1 table is converted, and the table data is copied.

  CREATE DATABASE IF NOT EXISTS db_auto_exist as db_drds include=tb1;

  The following output is returned:

  +-------------+
  | RESULT      |
  +-------------+
  | ALL SUCCESS |
  +-------------+
  1 row in set (8 min 12.05 sec)

• Execute the following statement to view the schemas of the tb1 and tb2 tables in the source DRDS database and the schemas of the tables in AUTO mode. If you execute this statement, no conversion is performed.

  CREATE DATABASE db_auto like db_drds dry_run=true include=tb1,tb2;

  The following output is returned:

  +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | TABLE | CREATE_TABLE_DRDS                                                                                                                                                                                                                                   | CREATE_TABLE_AUTO                                                                                                                                                                                                                                              |
  +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | tb1   | CREATE TABLE `tb1` (
          `id` int(11) NOT NULL,
          `k` int(11) NOT NULL DEFAULT '0',
          `c` char(120) NOT NULL DEFAULT '',
          `pad` char(60) NOT NULL DEFAULT '',
          PRIMARY KEY (`id`)
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4  dbpartition by hash(`id`) | CREATE TABLE `tb1` (
          `id` int(11) NOT NULL,
          `k` int(11) NOT NULL DEFAULT '0',
          `c` char(120) NOT NULL DEFAULT '',
          `pad` char(60) NOT NULL DEFAULT '',
          PRIMARY KEY (`id`)
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
  PARTITION BY KEY (`id`) PARTITIONS 32 |
  | tb2   | CREATE TABLE `tb2` (
          `id` int(11) NOT NULL,
          `k` int(11) NOT NULL DEFAULT '0',
          `c` char(120) NOT NULL DEFAULT '',
          `pad` char(60) NOT NULL DEFAULT '',
          PRIMARY KEY (`id`)
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4  dbpartition by hash(`id`) | CREATE TABLE `tb2` (
          `id` int(11) NOT NULL,
          `k` int(11) NOT NULL DEFAULT '0',
          `c` char(120) NOT NULL DEFAULT '',
          `pad` char(60) NOT NULL DEFAULT '',
          PRIMARY KEY (`id`)
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
  PARTITION BY KEY (`id`) PARTITIONS 32 |
  +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  2 rows in set (0.04 sec)

• Execute the following statement to copy data from the source DRDS database to the destination AUTO database. If you execute this statement, no conversion is performed. You must create a destination AUTO database and the corresponding tables in advance. The definitions of the tables in the destination AUTO database must be the same as those in the source DRDS database.

  CREATE DATABASE db_auto as db_drds create_tables=false;

  The following output is returned:

  +-------------+
  | RESULT      |
  +-------------+
  | ALL SUCCESS |
  +-------------+
  1 row in set (5 min 47.75 sec)

View the conversion progress and results

PolarDB-X allows you to use the INFORMATION_SCHEMA.CREATE_DATABASE view to view the progress and results of the conversion. For example, you can execute the following SQL statement to view the progress of the conversion task in which the destination AUTO database is db_auto:

SELECT * FROM INFORMATION_SCHEMA.CREATE_DATABASE where TARGET_SCHEMA = 'db_auto';\G

The following output is returned:

*************************** 1. row ***************************
             DDL_JOB_ID: 1547426040408715264
          SOURCE_SCHEMA: db_drds
          TARGET_SCHEMA: db_auto
              TABLE/SEQ: tb1
                  STAGE: BACKFILL
                 STATUS: RUNNING
                 DETAIL: NULL
                SQL_SRC: CREATE TABLE `tb1` (
        `id` int(11) NOT NULL,
        `k` int(11) NOT NULL DEFAULT '0',
        `c` char(120) NOT NULL DEFAULT '',
        `pad` char(60) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4  dbpartition by hash(`id`)
                SQL_DST: CREATE TABLE IF NOT EXISTS `tb1` (
        `id` int(11) NOT NULL,
        `k` int(11) NOT NULL DEFAULT '0',
        `c` char(120) NOT NULL DEFAULT '',
        `pad` char(60) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY (`id`) PARTITIONS 32
    BACKFILL_START_TIME: 2023-01-01 19:13:01
CURRENT_SPEED(ROWS/SEC): 37632
AVERAGE_SPEED(ROWS/SEC): 216064
          FINISHED_ROWS: 216064
 APPROXIMATE_TOTAL_ROWS: 1
      BACKFILL_PROGRESS: 100%
*************************** 2. row ***************************
             DDL_JOB_ID: 1547426040408715264
          SOURCE_SCHEMA: db_drds
          TARGET_SCHEMA: db_auto
              TABLE/SEQ: tb2
                  STAGE: BACKFILL
                 STATUS: RUNNING
                 DETAIL: NULL
                SQL_SRC: CREATE TABLE `tb2` (
        `id` int(11) NOT NULL,
        `k` int(11) NOT NULL DEFAULT '0',
        `c` char(120) NOT NULL DEFAULT '',
        `pad` char(60) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4  dbpartition by hash(`id`)
                SQL_DST: CREATE TABLE IF NOT EXISTS `tb2` (
        `id` int(11) NOT NULL,
        `k` int(11) NOT NULL DEFAULT '0',
        `c` char(120) NOT NULL DEFAULT '',
        `pad` char(60) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY (`id`) PARTITIONS 32
    BACKFILL_START_TIME: 2023-01-01 19:13:01
CURRENT_SPEED(ROWS/SEC): 36608
AVERAGE_SPEED(ROWS/SEC): 211968
          FINISHED_ROWS: 211968
 APPROXIMATE_TOTAL_ROWS: 1
      BACKFILL_PROGRESS: 100%
2 rows in set (0.01 sec)

PolarDB-X allows you to execute the SHOW FULL DDL statement to check the conversion progress.

For a time-consuming conversion task, you can view the conversion results by executing the SHOW DDL RESULT statement if the connection is closed and you cannot view the returned results. The conversion task still runs if the connection is closed. You can also use the INFORMATION_SCHEMA.CREATE_DATABASE view to view more detailed results.

The time is mainly taken during the period when the data is copied from the source DRDS database to the destination AUTO database. For example, in a PolarDB-X instance with a dedicated specification of 8 cores, 64 GB of memory, four compute nodes, and four storage nodes, create a DRDS database by referring to Sysbench test. The database has four tables, and each table has 50 million rows of data. The total volume of data is approximately 44 GB. In this case, the conversion takes 10 minutes and 37 seconds.

Conversion rules

This section describes some conversion rules that are used by PolarDB-X to automatically convert a database in DRDS mode to a database in AUTO mode. This helps you evaluate whether the destination database and tables that are created by PolarDB-X after the conversion meet your business requirements.

• Single tables and broadcast tables

  Single tables in DRDS mode are converted to broadcast tables in AUTO mode, and broadcast tables in DRDS mode are converted to broadcast tables in AUTO mode.

• Sharded tables

  PolarDB-X converts the splitting functions for database sharding and table partitioning in DRDS mode to the partitioning functions for partitioned tables in AUTO mode. The following table describes the mapping relationships.

  DRDS splitting function type	Splitting function in DRDS mode	Partitioning function in AUTO mode
  Hash	hash(a)	key(a)
  	str_hash(a, startIdx, endIdx)	key(a)
  	uni_hash(a)	key(a)
  	right_shift(a)	key(a)
  	range_hash(a, b, 10)	key(a). A clustered index is created on b. The partitioning function is key(b).
  Date and time	YYYYMM(a)	hash(to_months(a))
  	YYYYWEEK(a)	hash(to_weeks(a))
  	YYYYDD(a)	hash(to_days(a))
  	MM(a)	range(month(a))
  	DD(a)	range(dayofmonth(a))
  	WEEK(a)	range(dayofweek(a))
  	MMDD(a)	range(dayofyear(a))

  Sharded tables in DRDS mode are converted to partitioned tables in AUTO mode. In this case, conversion rules can be classified based on the type of sharded tables in DRDS mode.

  • Database sharding without table partitioning: A table in DRDS mode is mapped to a partitioned table in AUTO mode based on the splitting function for database sharding. Example:

    # The table in DRDS mode.
    create table tb1 (
        id int,
      name varchar(20)
    ) dbpartition by uni_hash(id);
    
    # The mapped table in AUTO mode.
    create table tb1 (
      id int,
      name varchar(20)
    ) partition by key(id);

  • Table partitioning without database sharding: A table in DRDS mode is mapped to a partitioned table in AUTO mode based on the splitting function for table partitioning.

    # The table in DRDS mode.
    create table tb3 (
      id int,
      dt date
    ) tbpartition by week(dt) tbpartitions 4;
    
    # The mapped table in AUTO mode.
    create table tb3 (
      id int,
      dt date
    ) partition by range (dayofweek(`dt`)) (
        partition p2 values less than (2),
        partition p3 values less than (3),
        partition p4 values less than (4),
        partition p5 values less than (5),
        partition p6 values less than (6),
        partition pd values less than maxvalue
    );

  • Database sharding and table partitioning: A table in DRDS mode is mapped to a partitioned table in AUTO mode based on the splitting function for database sharding.

    # The table in DRDS mode.
    create table tb2 (
      buyer_id varchar(20),
      order_id varchar(20)
    ) tbpartition by range_hash(buyer_id,order_id, 10) tbpartitions 4;
    
    # The mapped table in AUTO mode.
    create table tb1 (
      buyer_id varchar(20),
      order_id varchar(20),
      clustered index `auto_cg_order_id`(`order_id`) partition by key (`order_id`) partitions 64
    ) partition by key(buyer_id) partitions 64;
                                

The GROUP sequence, time-based sequence (TIME), and SIMPLE sequence in DRDS mode are all converted to New sequence (NEW) with better comprehensive performance in AUTO mode.

• The CHARSET and COLLATE statements of the destination AUTO database are the same as those of the source DRDS database. Therefore, you cannot manually specify CHARSET or COLLATE in the CREATE DATABASE LIKE or CREATE DATABASE AS statement.
• The LOCALITY attributes of the source DRDS database and its tables are not configured in the destination AUTO database. For more information about the LOCALITY attribute of a DRDS database, see Use the LOCALITY attribute to specify data nodes ( DRDS mode ).