Doris快速备份与恢复

最新推荐文章于 2024-08-09 12:59:29 发布

Boring_05

最新推荐文章于 2024-08-09 12:59:29 发布

阅读量962

点赞数

分类专栏： Doris 文章标签：数据库

本文链接：https://blog.csdn.net/Boring_05/article/details/134289259

版权

Doris 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

Doris快速备份与恢复(minio)

doris支持通过腾讯云bos、阿里云oss、hdfs实现备份与恢复，doris部署在线下机房，如采用oss/bos, 大数据备份与恢复比较慢，会占用一定的带宽。为了保险起见，而且能够达到快速的备份恢复，搭建一套minio对象存储。

1.当FE元数据发生损坏无法恢复时，我们能快速的从minio恢复。

2.当需要升级doris时，可以搭建一个测试集群，从minio快速copy数据测试。

1、搭建minio

下载minio安装包

#创建minio目录
mkdir /app/minio

wget https://dl.minio.io/server/minio/release/linux-amd64/minio

#下载完成后授权minio
chmod +x minio

创建目录

#创建data目录
mkdir -p /app/minio/data

#创建minio.conf文件
vim /app/minio/minio.conf

#数据存放目录
MINIO_VOLUMES="/app/minio/data"
##端口号设置
MINIO_OPTS="--address '0.0.0.0:9001' --console-address '0.0.0.0:9000'"
##用户名
MINIO_ROOT_USER="minio"
##密码
MINIO_ROOT_PASSWORD="minioadmin"

配置systemctl启动

#配置systemctl启动
vim /usr/lib/systemd/system/minio.service

[Unit]
Description=MinIO
Documentation=https://docs.min.io
Wants=network-online.target
After=network-online.target
#minio文件具体位置
AssertFileIsExecutable=/app/minio/minio

[Service]
User=root
Group=root
#创建的配置文件 minio.conf
EnvironmentFile=/app/minio/minio.conf
ExecStart=/app/minio/minio server $MINIO_OPTS $MINIO_VOLUMES
Restart=always
LimitNOFILE=65536
TimeoutStopSec=infinity
SendSIGKILL=no

[Install]
WantedBy=multi-user.target

minio应用端口为9001，web页面访问端口为9000

启动minio

#重载
systemctl daemon-reload

#启动minio
systemctl start minio

#查看进程
ps -ef | grep minio

浏览器访问IP:9000

创建桶

在这里插入图片描述

2、Doris备份

创建测试数据库

create database demo;

创建测试数据表

use demo;

CREATE TABLE IF NOT EXISTS demo.example_tbl
(
    `user_id` LARGEINT NOT NULL COMMENT "用户id",
    `date` DATE NOT NULL COMMENT "数据灌入日期时间",
    `city` VARCHAR(20) COMMENT "用户所在城市",
    `age` SMALLINT COMMENT "用户年龄",
    `sex` TINYINT COMMENT "用户性别",
    `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
    `cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",
    `max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
    `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间"
)
AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
PROPERTIES (
    "replication_allocation" = "tag.location.default: 1"
);

MySQL [demo]> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| example_tbl    |
+----------------+
1 row in set (0.00 sec)

创建repository仓库

AWS_ENDPOINT 远端仓库minio地址，端口为程序运行端口并非web页面访问端口

AWS_ACCESS_KEY 访问密钥

AWS_SECRET_KEY 秘密密钥

CREATE REPOSITORY `minio`
WITH S3
ON LOCATION "s3://doris"
PROPERTIES
(
    "AWS_ENDPOINT" = "http://10.108.27.160:9001",
    "AWS_ACCESS_KEY" = "minio",
    "AWS_SECRET_KEY"="minioadmin",
    "AWS_REGION" = "us-east-1",
    "use_path_style" = "true"
);

查看仓库

MySQL [(none)]> show repositories;
+--------+----------+---------------------+------------+------------+--------+------+--------+
| RepoId | RepoName | CreateTime          | IsReadOnly | Location   | Broker | Type | ErrMsg |
+--------+----------+---------------------+------------+------------+--------+------+--------+
| 11059  | minio    | 2023-11-08 13:28:01 | false      | s3://doris | -      | S3   | NULL   |
+--------+----------+---------------------+------------+------------+--------+------+--------+
1 row in set (0.00 sec)

备份指定库的指定表

demo.snapshot_demo demo库名、napshot_demo快照名称

minio repository仓库

example_tbl 表名

BACKUP SNAPSHOT demo.snapshot_demo
TO minio 
ON (example_tbl);

备份指定库的所有表

BACKUP SNAPSHOT demo.snapshot_demo_2023_11_8
TO minio;

查看备份进度

use demo;

show backup;

3、Doris恢复

查看存在哪些备份

MySQL [demo]> SHOW SNAPSHOT ON minio;
+-------------------------+---------------------+--------+
| Snapshot                | Timestamp           | Status |
+-------------------------+---------------------+--------+
| snapshot_demo           | 2023-11-08-13-36-41 | OK     |
| snapshot_demo_2023_11_8 | 2023-11-08-14-16-55 | OK     |
+-------------------------+---------------------+--------+
3 rows in set (0.07 sec)

恢复指定库的所有表

恢复库的所有表,副本数为3

RESTORE SNAPSHOT demo.snapshot_demo_2023_11_8
FROM `minio`
PROPERTIES
(
    "backup_timestamp"="2023-11-08-14-16-55",
    "replication_num" = "3"
);

示例：

MySQL [demo]> drop table example_tbl;
Query OK, 0 rows affected (0.00 sec)

MySQL [demo]> show tables;
Empty set (0.00 sec)

MySQL [demo]> RESTORE SNAPSHOT demo.snapshot_demo_2023_11_8
    -> FROM `minio`
    -> PROPERTIES
    -> (
    ->     "backup_timestamp"="2023-11-08-14-16-55",
    ->     "replication_num" = "3"
    -> );
Query OK, 0 rows affected (0.03 sec)

MySQL [demo]> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| example_tbl    |
+----------------+
1 row in set (0.00 sec)

恢复指定的表

RESTORE SNAPSHOT demo.snapshot_demo
FROM `minio`
ON ( `example_tbl` )
PROPERTIES
(
    "backup_timestamp"="2023-11-08-13-36-41",
    "replication_num" = "1"
);

总结：

如果该表是动态分区表，备份之后会自动禁用动态分区属性，在做恢复的时候需要手动将该表的动态分区属性启用，命令如下

ALTER TABLE tbl1 SET ("dynamic_partition.enable"="true")

批量更改所有分区表脚本：

#!/bin/bash
 
 
DORIS_HOST=xxxxxx
DORIS_PORT=xxxx
DORIS_USER=xxx
DORIS_PASSWORD=xxxx
 
 
databases=`mysql -h $DORIS_HOST -P $DORIS_PORT -u$DORIS_USER -p$DORIS_PASSWORD -e "show databases;"`
for db in ${databases};do
  if [[ $db != 'Database' ]] && [[ $db != 'information_schema' ]];then
 
      tables=`mysql -h $DORIS_HOST -P $DORIS_PORT -u$DORIS_USER -p$DORIS_PASSWORD -e "use $db;show tables;"`
      for table in ${tables};do
            if [[ $table =~ "Tables_in" ]];then
               echo ""
            else
               echo $table
               num=`mysql -h $DORIS_HOST -P $DORIS_PORT -u$DORIS_USER -p$DORIS_PASSWORD -e "show create table $db.$table\G" | grep "dynamic_partition.time_unit" | wc -l`
               if [[ $num == 1 ]];then
                   sql="alter table $db.$table set ('dynamic_partition.enable'='true');"
                   echo $sql
                   mysql -h $DORIS_HOST -P $DORIS_PORT -u$DORIS_USER -p$DORIS_PASSWORD -e "$sql"
               fi
            fi
       done
 
  fi
done