RESTORE … SNAPSHOT

RESTORE … SNAPSHOT is used to restore data at the cluster/tenant/database/table level from previously created snapshots of the corresponding levels.

Syntax Description

RESTORE ... SNAPSHOT is used to restore data at the cluster/tenant/database/table level from previously created snapshots of the corresponding levels.

Syntax Structure

RESTORE CLUSTER FROM SNAPSHOT <snapshot_name>;

RESTORE ACCOUNT <account_name> FROM SNAPSHOT <snapshot_name>;

RESTORE ACCOUNT <account_name> DATABASE <database_name> FROM SNAPSHOT <snapshot_name>;

RESTORE ACCOUNT <account_name> DATABASE <database_name> TABLE <table_name> FROM SNAPSHOT <snapshot_name>;

RESTORE ACCOUNT <account_name> FROM SNAPSHOT <snapshot_name> TO ACCOUNT <target_account_name>;

• Database-level and table-level restores require specifying the account name explicitly.

• The TO ACCOUNT clause is used for cross-tenant restoration, which is only available from the system tenant.

Examples

Example 1: Restore Cluster

-- Execute in tenants acc1, acc2
CREATE DATABASE db1;

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mo_catalog         |
| mysql              |
| system             |
| system_metrics     |
+--------------------+
6 rows in set (0.01 sec)

-- Execute in system tenant sys
CREATE SNAPSHOT cluster_sp1 FOR CLUSTER;
-- Create cluster snapshot

-- Execute in tenants acc1, acc2
DROP DATABASE db1;
-- Drop database db1

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mo_catalog         |
| mysql              |
| system             |
| system_metrics     |
+--------------------+
5 rows in set (0.01 sec)

-- Execute in system tenant sys
RESTORE CLUSTER FROM SNAPSHOT cluster_sp1;
-- Restore cluster from snapshot

-- Execute in tenants acc1, acc2
mysql> SHOW DATABASES;
-- Restoration successful
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mo_catalog         |
| mysql              |
| system             |
| system_metrics     |
+--------------------+
6 rows in set (0.01 sec)

Example 2: Restore Tenant

-- Execute in tenant acc1
CREATE DATABASE db1;
CREATE DATABASE db2;

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| information_schema |
| mo_catalog         |
| mysql              |
| system             |
| system_metrics     |
+--------------------+
7 rows in set (0.00 sec)

CREATE SNAPSHOT acc1_snap1 FOR ACCOUNT acc1;
-- Create snapshot
DROP DATABASE db1;
-- Drop databases db1, db2
DROP DATABASE db2;

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mo_catalog         |
| mysql              |
| system             |
| system_metrics     |
+--------------------+
5 rows in set (0.01 sec)

RESTORE ACCOUNT acc1 FROM SNAPSHOT acc1_snap1;
-- Restore tenant snapshot

mysql> SHOW DATABASES;
-- Restoration successful
+--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| information_schema |
| mo_catalog         |
| mysql              |
| system             |
| system_metrics     |
+--------------------+
7 rows in set (0.01 sec)

Example 3: Restore Database

-- Execute in tenant acc1
CREATE DATABASE db1;

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mo_catalog         |
| mysql              |
| system             |
| system_metrics     |
+--------------------+
6 rows in set (0.00 sec)

CREATE SNAPSHOT acc1_db_snap1 FOR ACCOUNT acc1;
-- Create snapshot
DROP DATABASE db1;
-- Drop database db1

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mo_catalog         |
| mysql              |
| system             |
| system_metrics     |
+--------------------+
5 rows in set (0.01 sec)

-- Execute in system tenant sys
RESTORE ACCOUNT acc1 DATABASE db1 FROM SNAPSHOT acc1_db_snap1;
-- Restore database snapshot

mysql> SHOW DATABASES;
-- Restoration successful
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mo_catalog         |
| mysql              |
| system             |
| system_metrics     |
+--------------------+
6 rows in set (0.00 sec)

Example 4: Restore Table

-- Execute in tenant acc1
CREATE TABLE t1(n1 INT);
INSERT INTO t1 VALUES(1);

mysql> SELECT * FROM t1;
+------+
| n1   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

CREATE SNAPSHOT acc1_tab_snap1 FOR ACCOUNT acc1;
-- Create snapshot
TRUNCATE TABLE t1;
-- Clear table t1

mysql> SELECT * FROM t1;
Empty set (0.01 sec)

-- Execute in system tenant sys
RESTORE ACCOUNT acc1 DATABASE db1 TABLE t1 FROM SNAPSHOT acc1_tab_snap1;
-- Restore table snapshot

mysql> SELECT * FROM t1;
-- Restoration successful
+------+
| n1   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Example 5: System Tenant Restores Regular Tenant to Itself

-- Execute in tenant acc1
CREATE DATABASE db1;

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mo_catalog         |
| mysql              |
| system             |
| system_metrics     |
+--------------------+
6 rows in set (0.01 sec)

-- Execute in system tenant sys
CREATE SNAPSHOT acc1_snap1 FOR ACCOUNT acc1;
-- Create snapshot for acc1

-- Execute in tenant acc1
DROP DATABASE db1;
-- Drop database db1

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mo_catalog         |
| mysql              |
| system             |
| system_metrics     |
+--------------------+
5 rows in set (0.01 sec)

-- Execute in system tenant sys
RESTORE ACCOUNT acc1 {SNAPSHOT = "acc1_snap1"} TO ACCOUNT acc1;
-- Restore snapshot to acc1

-- Execute in tenant acc1
mysql> SHOW DATABASES;
-- Restoration successful
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mo_catalog         |
| mysql              |
| system             |
| system_metrics     |
+--------------------+
6 rows in set (0.01 sec)

Example 6: System Tenant Restores Regular Tenant to New Tenant

-- Execute in tenant acc1
CREATE DATABASE db1;

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mo_catalog         |
| mysql              |
| system             |
| system_metrics     |
+--------------------+
6 rows in set (0.01 sec)

-- Execute in system tenant sys
CREATE SNAPSHOT acc1_snap1 FOR ACCOUNT acc1;
-- Create snapshot for acc1

-- Execute in tenant acc1
DROP DATABASE db1;
-- Drop database db1

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mo_catalog         |
| mysql              |
| system             |
| system_metrics     |
+--------------------+
5 rows in set (0.01 sec)

-- Execute in system tenant sys
CREATE ACCOUNT acc2 ADMIN_NAME admin IDENTIFIED BY '111';
-- Create target tenant first
RESTORE ACCOUNT acc1 {SNAPSHOT = "acc1_snap1"} TO ACCOUNT acc2;
-- Restore acc1 snapshot to acc2

-- Execute in tenant acc1
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mo_catalog         |
| mysql              |
| system             |
| system_metrics     |
+--------------------+
5 rows in set (0.00 sec)

-- Execute in tenant acc2
mysql> SHOW DATABASES;
-- Restored to acc2
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mo_catalog         |
| mysql              |
| system             |
| system_metrics     |
+--------------------+
6 rows in set (0.01 sec)

Example 7: Restore Hive Partition External Table

Snapshot restore supports databases containing Hive partition external tables. When restoring a database or table from a snapshot, Hive partition external tables are correctly restored along with regular tables.

DROP SNAPSHOT IF EXISTS sp_24559;
DROP DATABASE IF EXISTS restore_hive_24559;

CREATE DATABASE restore_hive_24559;
USE restore_hive_24559;

CREATE TABLE base_t (id INT PRIMARY KEY);
INSERT INTO base_t VALUES (1), (2);

CREATE EXTERNAL TABLE hive_ext (
    id INT,
    amount DOUBLE,
    year INT
) INFILE{
    'filepath'='$resources/hive_partition/single_level/',
    'format'='parquet',
    'hive_partitioning'='true',
    'hive_partition_columns'='year'
};

CREATE SNAPSHOT sp_24559 FOR ACCOUNT;
INSERT INTO base_t VALUES (3);

RESTORE TABLE restore_hive_24559.hive_ext{SNAPSHOT="sp_24559"};
RESTORE DATABASE restore_hive_24559{SNAPSHOT="sp_24559"};

SHOW FULL TABLES FROM restore_hive_24559;

DROP SNAPSHOT IF EXISTS sp_24559;
DROP DATABASE IF EXISTS restore_hive_24559;

Example 8: User Lock State in Snapshot Restore

Snapshot creation and restore preserves user lock state (ALTER USER ... LOCK / UNLOCK). When an account snapshot is taken and later restored, locked users remain locked and unlocked users remain unlocked as they were at the time of the snapshot.

DROP SNAPSHOT IF EXISTS issue21685_user_lock_acc_sp;
DROP ACCOUNT IF EXISTS issue21685_restore_acc;

CREATE ACCOUNT issue21685_restore_acc ADMIN_NAME = 'admin' IDENTIFIED BY '111';
-- @session:id=1&user=issue21685_restore_acc:admin&password=111
CREATE USER issue21685_restore_user IDENTIFIED BY '111';
ALTER USER issue21685_restore_user LOCK;
-- @session

CREATE SNAPSHOT issue21685_user_lock_acc_sp FOR ACCOUNT issue21685_restore_acc;
-- @session:id=1&user=issue21685_restore_acc:admin&password=111
ALTER USER issue21685_restore_user UNLOCK;
-- @session

RESTORE ACCOUNT issue21685_restore_acc {SNAPSHOT = "issue21685_user_lock_acc_sp"};
-- After restore, the user is locked again (snapshot state)

DROP ACCOUNT IF EXISTS issue21685_restore_acc;
DROP SNAPSHOT IF EXISTS issue21685_user_lock_acc_sp;

Limitations

• System tenant can only perform tenant-level restoration when restoring to a new tenant.

• Only system tenant can perform restoration to a new tenant, and only tenant-level restoration is allowed. The new tenant must be created in advance. To avoid object conflicts, it’s recommended to use a newly created tenant.