PolarDB for MySQL provides the Fast Import feature, which allows you to query the full data of databases and tables in a restoration task after waiting approximately 5 to 10 minutes (regardless of the data size to be restored) after you submit a database and table restoration task, without waiting for the restoration task to complete.
Scenario overview
The Fast Import feature is compatible with the new database and table restoration process, and query operations can be performed simultaneously with the restoration task.
The Fast Import feature can accelerate the following two scenarios:
-
When data or tables are accidentally deleted and the specific point in time cannot be determined, traditional restoration tasks may take a long time to complete. With the Fast Import feature, you can quickly query and verify data without waiting for the restoration task to complete.
-
When a small amount of data is accidentally deleted but the table is large, the Fast Import feature allows you to use
INSERT ... SELECT ...to quickly extract the required data from the tables in the restoration task and insert it into the table where data was accidentally deleted, thereby improving operational efficiency.
Version requirements
The applicable database engine version is MySQL 8.0.1, and the minor version must be 8.0.1.1.49 or later. You can confirm your cluster version by querying the version number.
Precautions
This feature is currently in the canary release phase. To use this feature, you can Contact usjoin the whitelist to enable it.
-
Only the file-per-table configuration (default configuration) is supported for non-compressed and non-encrypted tables. Data can be queried during the execution of restoration tasks.
-
When the number of tables in the restoration task is less than 1,000, the system enables the
Fast Importfeature by default, allowing you to query the full data during the restoration process.NoteWhen the number of restoration tasks exceeds 1,000, the system does not enable the
Fast Importfeature. The task management flow automatically determines whether to enable theFast Importfeature based on the task you submit. -
Query operations are only supported on read/write (RW) nodes and read-only (RO) nodes. If column store read-only (RO) nodes exist, you can use the HINT syntax to force route queries to read/write (RW) or row store read-only (RO) nodes. Otherwise, no data may be returned.
-
When
buffer poolresources are sufficient, accessing tables in restoration state delivers comparable efficiency to accessing normal tables. However, in scenarios where the cluster has high workloads, the efficiency of accessing tables in restoration state is limited to reduce the impact on normal business operations. -
Before the restoration task is completed, the target tables for restoration can only be accessed in read-only mode.
Usage instructions
Submit a database and table restoration task
After submitting a database and table restoration task, you can go to the task details page through the details link in the upper-right corner of the page.
A database and table restoration task consists of 6 steps:
-
Initialize the task
-
Full restoration on the temporary node
-
Incremental restoration on the temporary node (as needed)
-
Database and table restoration preprocessing
-
Database and table restoration
-
Complete the task and reclaim resources
On the task details page, you can view the real-time progress of the task. After Step 4 is completed, you can confirm that the restoration task has created the target databases or tables by executing statements such as show databases or show tables. At this point, you can query the full data of the target databases or tables.
Read-only fast access
You can query the information_schema.fast_import_tablespaces table to obtain information about all tables that support fast read-only access in the current restoration task.
Tables that are currently being imported can be accessed in fast read-only mode, but specific table names are not displayed in the following query results. You can execute the SHOW PROCESSLIST command to view SQL statements containing dbms_rman.import to obtain the names of tables that are currently being restored.
SELECT * FROM information_schema.fast_import_tablespaces;-
Tables in restoration state can be accessed in read-only mode with full data.
| 106 | root | 127.0.0.1:49055 | NULL | Sleep | 13 | | NULL | | 146 | xxx | xxx | NULL | Sleep | 43 | | NULL | | 366 | xxx | xxx | NULL | Polar Log Dump | 0 | Reading log from innodb | NULL | | 368 | xxx | xxx | NULL | Polar Log Ack | 0 | Receiving from client | NULL | | 788 | xxx | xxx | NULL | Sleep | 295 | | NULL | | xxx | xxx | xxx | NULL | Query | 332 | altering table | /* rds internal mark */ call dbms_rman.import('test', 'sbtest1_bk03211652' | | 216448 | xxx | xxx | information_schema | Sleep | 7 | | NULL | | 216915 | xxx | xxx | NULL | Sleep | 0 | | NULL | | 216916 | xxx | xxx | NULL | Sleep | 0 | | NULL | | 536879884 | xxx | | test | Query | 0 | starting | show processlist | 31 rows in set (0.00 sec) mysql> show tables; +----------------------+ | Tables_in_test | +----------------------+ | sbtest1 | | sbtest1_bk03211652 | | sbtest2 | | sbtest2_bk03211652 | +----------------------+ 4 rows in set (0.00 sec) mysql> select count(*) from sbtest1; +-----------+ | count(*) | +-----------+ | 100000000 | +-----------+ 1 row in set (4.64 sec) mysql> select count(*) from sbtest1_bk03211652; +-----------+ | count(*) | +-----------+ | 100000000 | +-----------+ 1 row in set (48.21 sec) -
Other tables with fast access enabled can be accessed in read-only mode with full data.
mysql> select * from information_schema.fast_import_tablespaces; +----------------+-------------------------+------------------------------------------------------------------+ | LOCAL_SPACE_ID | SPACE_NAME | REMOTE_DATA_PATH | +----------------+-------------------------+------------------------------------------------------------------+ | 18 | test/sbtest2_bk03211652 | xxx/test/sbtest2_bk03211652.ibd | +----------------+-------------------------+------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show tables; +------------------------+ | Tables_in_test | +------------------------+ | sbtest1 | | sbtest1_bk03211652 | | sbtest2 | | sbtest2_bk03211652 | +------------------------+ 4 rows in set (0.00 sec) mysql> select count(*) from sbtest2_bk03211652; +----------+ | count(*) | +----------+ | 1000 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from sbtest2; +----------+ | count(*) | +----------+ | 1000 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from sbtest1; +-----------+ | count(*) | +-----------+ | 100000000 | +-----------+ 1 row in set (19.39 sec) mysql> select count(*) from sbtest1_bk03211652; +-----------+ | count(*) | +-----------+ | 100000000 | +-----------+ 1 row in set (4.39 sec) mysql> show processlist;