How to convert a database in DRDS mode to a database in AUTO mode - PolarDB

This topic describes how to convert a database in Distributed Relational Database Service (DRDS) mode to a database in AUTO mode.

Background information

PolarDB-X allows you to execute the CREATE DATABASE LIKE or CREATE DATABASE AS statement to convert a database in DRDS mode to a database in AUTO mode. For information about databases in AUTO mode and DRDS mode, see Databases in AUTO mode and DRDS mode. In the following sections, the database in DRDS mode to be converted is referred to as the source DRDS database, and the database in AUTO mode after conversion is referred to as the destination AUTO database.

If you execute the CREATE DATABASE LIKE statement, PolarDB-X only converts the schemas of the tables in the source DRDS database, but does not copy the table data.
If you execute the CREATE DATABASE AS statement, PolarDB-X converts the schemas of the tables in the source DRDS database and copies the table data.

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

Parameters

Parameter	Description
auto_database_name	The name of the destination AUTO database to be created.
drds_database_name	The name of the source DRDS database. Enter the name of a valid database in DRDS mode.
mode	The mode of the created database. Set the value to auto.
dry_run	Specifies whether to enable the dry-run mode of the conversion process. In the dry_run mode, you can view the schemas of the tables in the source DRDS database and the schemas of the tables in AUTO mode. For more information about how PolarDB-X converts databases, see Schema conversion rules. Valid values: true: enables the dry_run mode of the conversion process. false: disables the dry_run mode of the conversion process. This is the default value. Note The dry_run parameter is used only to allow you to view how the table schemas in DRDS mode are converted to the table schemas in AUTO mode. No table schema is created and no table data is copied during a dry run. If you set the dry_run parameter to true, the source DRDS database is not affected. For example, the source DRDS database does not become read-only.
lock	Specifies whether to apply a read lock to the source DRDS database during the conversion. After a read lock is applied, the source DRDS database becomes read-only, and no DML or DDL statements can be executed on the source DRDS database. Valid values: true: applies a read lock to the source DRDS database. This is the default value. false: removes a read lock from the source DRDS database. Important If you set this parameter to false, data is inconsistent between the source DRDS database and the destination AUTO database.
create_tables	Specifies whether to create table schemas in the destination AUTO database during the conversion. Valid values: true: true: If you set create_tables to true, PolarDB-X automatically converts the table schemas and creates tables in the destination AUTO database. This is the default value. false: If you set create_tables to false, you must create a destination AUTO database in advance and create tables in the destination AUTO database that correspond to all tables in the source DRDS database. Note If the tables that are automatically converted by PolarDB-X are not applicable to your scenarios, you can disable the create_tables parameter and create a destination AUTO database and tables based on your business requirements. The tables in the destination AUTO database must have the same table names and column definitions as the tables in the source DRDS database. You can customize the partition mode in the destination AUTO database. If the create_tables parameter is set to false, when you execute the `CREATE DATABASE AS` statement, PolarDB-X only copies data from the source DRDS database to the destination AUTO database, but does not create the destination AUTO database or tables.
include	The schemas of the tables to be converted in the source DRDS database. If you configure the include parameter, only the schemas of the tables specified by the include parameter are converted.
exclude	The schemas of the tables that are not to be converted in the source DRDS database. If you configure the exclude parameter, the schemas of the tables specified by the exclude parameter are not converted.

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

Time taken for the conversion

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.

Note

The specific time taken to convert the database depends on various factors, such as your instance type and database size. If you want to get an exact time taken to convert the database, we recommend that you perform a drill during off-peak hours or in a test environment. You can set the lock parameter to false during the drill.

Schema 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

The following table describes the function mappings.

DRDS sharding function type	Sharding 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)	co_hash(right(a,n), right(b,n))
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))

Database sharding is performed and table partitioning is not performed.
```
# 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);
```
Note
The function mapping table shows:
- Database sharding functions are converted to partitioning functions.
- Table schemas are converted to tables that have only one level of partitions in AUTO mode.

Table partitioning is performed and database sharding is not performed.

# 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
);

Note

The function mapping table shows:

The table sharding functions are converted to the corresponding partitioning functions.
Table schemas are converted to tables that have only one level of partitions in AUTO mode.

Database sharding and table partitioning are performed by using the same rules.
```
# The table in DRDS mode.
create table tb2 (
  buyer_id varchar(20),
  order_id varchar(20)
) dbpartition by range_hash(buyer_id,order_id, 10) tbpartition by range_hash(buyer_id,order_id, 10) tbpartitions 4; 

# The mapped table in AUTO mode.
create table tb2 (
  buyer_id varchar(20),
  order_id varchar(20)
) partition by co_hash(right(buyer_id,10)), right(order_id,10)) partitions 64;
```
Note
The function mapping table shows:
- Database sharding functions are converted to corresponding partitioning functions.
- The number of partitions is equal to the product of the number of database shards and the number of table shards.
- Table schemas are converted to tables that have only one level of partitions in AUTO mode.

Database sharding and table partitioning are performed by using different rules.
```
# The table in DRDS mode.
create table tb5 (
  buyer_id varchar(20),
  order_id varchar(20)
) dbpartition by hash(buyer_id) tbpartition by hash(order_id) tbpartitions 4; 

# The mapped table in AUTO mode.
create table tb5 (
  buyer_id varchar(20),
  order_id varchar(20)
) partition by key(buyer_id) partitions 16 subpartition by key(order_id) subpartitions 4;
```
Note
The function mapping table shows:
- Database sharding functions are converted to corresponding level-1 partition functions. The number of level-1 partitions is equal to the number of database shards.
- Table partitioning functions are converted to corresponding level-2 partitioning functions. The number of level-2 partitions is equal to the number of table shards.
- Table schemas are converted to tables that have two levels of partitions in AUTO mode.

Sequence conversion rules

The Group sequence, time-based sequence (TIME), and SIMPLE sequence in DRDS mode are all converted to New Sequences with better comprehensive performance in AUTO mode. For more information, see Sequence.

Usage notes

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