RESTORE ... FROM SNAPSHOT

Syntax Description

RESTORE ... FROM SNAPSHOT is used to restore data at the tenant/database/table level from a previously created tenant-level snapshot.

Syntax Structure

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

Example

Example 1: Recover tenant

--Execute under instance 0193ba05-6cd6-7bca-ba0b-60828b25xxxx
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 A_snap1 for account `0193ba05-6cd6-7bca-ba0b-60828b25xxxx`;--Create a snapshot
drop database db1;--Delete database 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 `0193ba05-6cd6-7bca-ba0b-60828b25xxxx` FROM snapshot A_snap1;--Restore tenant-level snapshot

mysql> show databases;--Recovery successfully
+-------------------------+
| Database |
+-------------------------+
| db1 |
| db2 |
| information_schema |
| mo_catalog |
| mysql |
| system |
| system_metrics |
+-------------------------+
7 rows in set (0.01 sec)

Example 2: Recovering the database

--Execute under tenant acc1
CREATE database db1;

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

create snapshot A_db_snap1 for account `0193ba05-6cd6-7bca-ba0b-60828b25xxxx`;--Create snapshot
drop database db1;--Delete database db1

mysql> show databases;
+-------------------------+
| Database |
+-------------------------+
| information_schema |
| mo_catalog |
| mysql |
| system |
| system_metrics |
+-------------------------+
6 rows in set (0.01 sec)

restore account `0193ba05-6cd6-7bca-ba0b-60828b25xxxx` database db1 FROM snapshot A_db_snap1;--Recover database-level snapshot

mysql> show databases;--Recovery successfully
+-------------------------+
| Database |
+-------------------------+
| db1 |
| information_schema |
| mo_catalog |
| mysql |
| system |
| system_metrics |
+-------------------------+
7 rows in set (0.00 sec)

Example 3: Recover table

--Execute under 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 A_tab_snap1 for account `0193ba05-6cd6-7bca-ba0b-60828b25xxxx`;--Create a snapshot
truncate TABLE t1;--Clear t1

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

restore account `0193ba05-6cd6-7bca-ba0b-60828b25xxxx` database db1 TABLE t1 FROM snapshot A_tab_snap1;--Restore snapshot

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