RESTORE … FROM SNAPSHOT¶
RESTORE … SNAPSHOT 用于从之前创建的集群/租户/数据库/表级别的快照中进行集群/租户/数据库/表级别的恢复数据。
语法说明¶
RESTORE ... SNAPSHOT 用于从之前创建的集群/租户/数据库/表级别的快照中进行集群/租户/数据库/表级别的恢复数据。
语法结构¶
RESTORE CLUSTER {SNAPSHOT = <snapshot_name>};
RESTORE ACCOUNT <account_name> {SNAPSHOT = <snapshot_name>} [TO ACCOUNT <target_account_name>];
RESTORE DATABASE [<account_name>.]<database_name> {SNAPSHOT = <snapshot_name>} [TO ACCOUNT <target_account_name>];
RESTORE TABLE [<account_name>.]<database_name>.<table_name> {SNAPSHOT = <snapshot_name>} [TO ACCOUNT <target_account_name>];
RESTORE DATABASE与RESTORE TABLE在省略<account_name>.时默认作用于当前租户，如需恢复其他租户对象请使用带租户前缀的点分格式。快照子句同样支持兼容写法
FROM SNAPSHOT <snapshot_name>或{SNAPSHOT = "<snapshot_name>"}。
示例¶
示例 1：恢复集群¶
--在租户 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)
--在系统租户 sys 下执行
create snapshot cluster_sp1 for cluster;--为集群创建快照
--在租户 acc1,acc2 下执行
drop database db1;--删除数据库 db1
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mo_catalog |
| mysql |
| system |
| system_metrics |
+--------------------+
6 rows in set (0.01 sec)
--在系统租户 sys 下执行
restore cluster{snapshot="cluster_sp1"};--在系统租户下对集群进行快照恢复
--在租户 acc1,acc2 下执行
mysql> show databases;--恢复成功
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mo_catalog |
| mysql |
| system |
| system_metrics |
+--------------------+
6 rows in set (0.01 sec)
示例 2：恢复租户¶
--在租户 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;--创建快照
drop database db1;--删除数据库 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 {snapshot = acc1_snap1};--恢复租户级别快照
mysql> show databases;--恢复成功
+--------------------+
| Database |
+--------------------+
| db1 |
| db2 |
| information_schema |
| mo_catalog |
| mysql |
| system |
| system_metrics |
+--------------------+
7 rows in set (0.01 sec)
示例 3：恢复数据库¶
--在租户 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 acc1_db_snap1 for account acc1;--创建快照
drop database db1;--删除数据库 db1
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mo_catalog |
| mysql |
| system |
| system_metrics |
+--------------------+
6 rows in set (0.01 sec)
--在系统租户 sys 下执行
restore database acc1.db1 {snapshot = acc1_db_snap1};--恢复数据库级别快照
mysql> show databases;--恢复成功
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mo_catalog |
| mysql |
| system |
| system_metrics |
+--------------------+
7 rows in set (0.00 sec)
示例 4：恢复表¶
--在租户 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;--创建快照
truncate TABLE t1;--清空 t1
mysql> SELECT * FROM t1;
Empty set (0.01 sec)
--在系统租户 sys 下执行
restore TABLE acc1.db1.t1 {snapshot = acc1_tab_snap1};--恢复快照
mysql> SELECT * FROM t1;--恢复成功
+------+
| n1 |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
示例 5：系统租户恢复普通租户到普通租户本租户¶
--在租户 acc1 下执行
create database db1;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mo_catalog |
| mysql |
| system |
| system_metrics |
+--------------------+
6 rows in set (0.01 sec)
--在系统租户 sys 下执行
create snapshot acc1_snap1 for account acc1;--为 acc1 创建快照
--在租户 acc1 下执行
drop database db1;--删除数据库 db1
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mo_catalog |
| mysql |
| system |
| system_metrics |
+--------------------+
6 rows in set (0.01 sec)
--在系统租户 sys 下执行
restore account acc1 {snapshot = acc1_snap1} TO account acc1;--在系统租户下对 acc1 进行快照恢复
--在租户 acc1 下执行
mysql> show databases;--恢复成功
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mo_catalog |
| mysql |
| system |
| system_metrics |
+--------------------+
6 rows in set (0.01 sec)
示例 6：系统租户恢复普通租户到新租户¶
--在租户 acc1 下执行
create database db1;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mo_catalog |
| mysql |
| system |
| system_metrics |
+--------------------+
6 rows in set (0.01 sec)
--在系统租户 sys 下执行
create snapshot acc1_snap1 for account acc1;--为 acc1 创建快照
--在租户 acc1 下执行
drop database db1;--删除 db1
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mo_catalog |
| mysql |
| system |
| system_metrics |
+--------------------+
6 rows in set (0.01 sec)
--在系统租户 sys 下执行
create account acc2 ADMIN_NAME admin IDENTIFIED BY '111';--需要提前创建要目标新租户
restore account acc1 {snapshot = acc1_snap1} TO account acc2;--在系统租户下对 acc1 进行快照恢复，恢复至 acc2
--在租户 acc1 下执行
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mo_catalog |
| mysql |
| system |
| system_metrics |
+--------------------+
5 rows in set (0.00 sec)
--在租户 acc2 下执行
mysql> show databases;--恢复至 acc2
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mo_catalog |
| mysql |
| system |
| system_metrics |
+--------------------+
6 rows in set (0.01 sec)
示例 7：恢复包含 Hive 分区外部表的快照¶
快照恢复支持包含 Hive 分区外部表的数据库。从快照恢复数据库或表时，Hive 分区外部表会与普通表一起正确恢复。
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;
示例 8：快照恢复中的用户锁定状态¶
快照创建和恢复会保留用户锁定状态（ALTER USER ... LOCK / UNLOCK）。当创建账户快照并随后恢复时，被锁定的用户保持锁定状态，未被锁定的用户保持解锁状态，与快照创建时的状态一致。
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"};
-- 恢复后，用户重新被锁定（快照时的状态）
DROP ACCOUNT IF EXISTS issue21685_restore_acc;
DROP SNAPSHOT IF EXISTS issue21685_user_lock_acc_sp;
限制¶
系统租户恢复普通租户到新租户只允许租户级别的恢复。
只有系统租户才可以执行恢复数据到新租户，且只允许租户级别的恢复。新租户需提前创建，为了避免对象冲突，新租户最好为新建租户。