南大通用GBase 8a常见操作 - 复制一张表(通过数据文件恢复表)

本文介绍GBase 8a数据库日常操作,将表A复制一份表B,也就是备份一份。其中方案1可以复制完整表结构和数据,方案2只复制数据和部分表结构,请根据实际需求进行选择。

复制表方案1

建一张新表

通过create table B like A创建一张结构完全一样的表。 如下以 student 为模板,创建一个student_bak1的表。

gbase> show create table student;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE "student" (
  "sno" varchar(20) DEFAULT NULL,
  "sname" varchar(20) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  "sex" int(11) DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> create table student_bak1 like student;
Query OK, 0 rows affected (Elapsed: 00:00:00.12)

gbase> show create table student_bak1;
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                     |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student_bak1 | CREATE TABLE "student_bak1" (
  "sno" varchar(20) DEFAULT NULL,
  "sname" varchar(20) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  "sex" int(11) DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase>

复制数据

通过inset into B select from A 方法,向新建的表复制数据。

gbase> select * from student;
+----------+--------+------+------+
| sno      | sname  | age  | sex  |
+----------+--------+------+------+
| 20210001 | 张三   |   18 |    1 |
| 20210002 | 李四   |   19 |    1 |
| 20210003 | 王五   |   18 |    0 |
| 20210004 | 赵六   |   18 |    0 |
| 20210099 | 张三   |   18 |    0 |
+----------+--------+------+------+
5 rows in set (Elapsed: 00:00:00.01)

gbase> insert into student_bak1 select * from student;
Query OK, 5 rows affected (Elapsed: 00:00:00.08)
Records: 5  Duplicates: 0  Warnings: 0

gbase> select * from student_bak1;
+----------+--------+------+------+
| sno      | sname  | age  | sex  |
+----------+--------+------+------+
| 20210001 | 张三   |   18 |    1 |
| 20210002 | 李四   |   19 |    1 |
| 20210003 | 王五   |   18 |    0 |
| 20210004 | 赵六   |   18 |    0 |
| 20210099 | 张三   |   18 |    0 |
+----------+--------+------+------+
5 rows in set (Elapsed: 00:00:00.01)

复制表方案2

通过create table B as select * from A直接复制表,但本方法只复制了数据,不会复制原始表的一些索引,备注之类的属性。

如下例子我们创建一个hash索引,然后通过create table student_bak2 as select * from student来复制表,从结果可以看到,数据完全一样,但备份表里没有原始表的索引。

所以本方案只适合复制数据,不适合复制表结构。

gbase> create index idx_sno on student(sno) using hash global;
Query OK, 0 rows affected (Elapsed: 00:00:01.27)
Records: 0  Duplicates: 0  Warnings: 0

gbase> show create table student;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                           |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE "student" (
  "sno" varchar(20) DEFAULT NULL,
  "sname" varchar(20) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  "sex" int(11) DEFAULT NULL,
  KEY "idx_sno" ("sno") USING HASH GLOBAL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> create table student_bak2 as select * from student;
Query OK, 5 rows affected (Elapsed: 00:00:00.19)

gbase> select * from student_bak2;
+----------+--------+------+------+
| sno      | sname  | age  | sex  |
+----------+--------+------+------+
| 20210001 | 张三   |   18 |    1 |
| 20210002 | 李四   |   19 |    1 |
| 20210003 | 王五   |   18 |    0 |
| 20210004 | 赵六   |   18 |    0 |
| 20210099 | 张三   |   18 |    0 |
+----------+--------+------+------+
5 rows in set (Elapsed: 00:00:00.01)

gbase> show create table student_bak2;
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                     |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student_bak2 | CREATE TABLE "student_bak2" (
  "sno" varchar(20) DEFAULT NULL,
  "sname" varchar(20) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  "sex" int(11) DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase>

通过底层数据文件复制一张表

适合表数据量非常大,走insert select耗时长的情况。当然原表不能改变,否则复制的文件可能不准确。

创建新表

必须是同一个库下面,不能跨库。

create table t2_bak like t2;

复制分片数据

每个节点,每个分片都要操作,如下以1个节点的_n3分片为例

进入userdata目录,将元数据和数据覆盖过来

建议用md5sum验证一下。 cp操作也可以用rsync等工具,更快一些。

cd /opt/gbase/gnode/userdata/gbase/testdb/metadata/t2_bak_n3.GED/
rm * -fr
cp ../t2_n3.GED/* .

验证一下是否文件正确
cd ..
md5sum t2_bak_n3.GED/*
md5sum t2_n3.GED/* 

cd ../sys_tablespace/t2_bak_n3/
rm * -fr
cp ../t2_n3/* .

验证一下是否文件正确
cd ..
md5sum t2_n3/*
md5sum t2_bak_n3/*

重启分片gbased服务

gcluster_services gbase restart

验证分片数据

gbase> select * from t2_bak_n3;
+-------+
| id    |
+-------+
|     1 |
|  1000 |
| 10000 |
+-------+
3 rows in set (Elapsed: 00:00:00.01)

所有分片都完成后,从集群层就可以查询到复制的完整数据了。