CREATE PITR

Syntax Description

The CREATE PITR command is used to create a recovery point for Point-in-Time Recovery (PITR). The instance administrator can create instance/database/table level pitr for the current instance. The information for each pitr is only visible to the instances where the pitr was created, ensuring data isolation and security.

Syntax Structure

create pitr <pitr_name> for
    [account <account_name>]|[database <database_name>]|[table <database_name> <table_name>]]
    range <value><unit>

Syntax interpretation

range: int, time range value, 1-100. unit: string Time range unit, optional range h (hour), d (day, default), mo (month), y (year)

Example

Example 1: Create pitr for instance

create pitr account_pitr1 range 2 "h";

mysql> show pitr where pitr_name='account_pitr1';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| pitr_name | created_time | modified_time | pitr_level | account_name | database_name | table_name | pitr_length | pitr_unit |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| account_pitr1 | 2024-12-17 03:13:28 | 2024-12-17 03:13:28 | account | 0193ba05-6cd6-7bca-ba0b-60828b25xxxx | * | * | 2 | h |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.17 sec)

Example 2: Create pitr for database

mysql> create pitr db_pitr1 for database db1 range 1 'y';
Query OK, 0 rows affected (0.01 sec)

mysql> show pitr where pitr_name='db_pitr1';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| pitr_name | created_time | modified_time | pitr_level | account_name | database_name | table_name | pitr_length | pitr_unit |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| db_pitr1 | 2024-12-17 03:14:06 | 2024-12-17 03:14:06 | database | 0193ba05-6cd6-7bca-ba0b-60828b25xxxx | db1 | * | 1 | y |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.07 sec)

Example 3: Create pitr for table

mysql> create pitr tab_pitr1 for database db1 table t1 range 1 'y';
Query OK, 0 rows affected (0.02 sec)

mysql> show pitr where pitr_name='tab_pitr1';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| pitr_name | created_time | modified_time | pitr_level | account_name | database_name | table_name | pitr_length | pitr_unit |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| tab_pitr1 | 2024-12-17 03:14:48 | 2024-12-17 03:14:48 | table | 0193ba05-6cd6-7bca-ba0b-60828b25xxxx | db1 | t1 | 1 | y |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.08 sec)