Cassandra备份恢复

1 备份键空间和表结构

./cqlsh -u cassandra -p cassandra 192.168.137.110 9042 -e "desc keyspace school" &> keyspace_table.cql

keyspace和表的创建语句都在keyspace_table.cql里面，但是没有任何数据

2 恢复键空间和表结构

将keyspace_table.cql上传到新的服务器

./cqlsh -u cassandra -p cassandra 192.168.137.111 9042

source 'keyspace_table.cql';#单引号和结尾分号都不要省略

3 备份表数据

./nodetool snapshot -t 20240815 school

备份的school的表的数据都存储在${data_file_directories}/school/表名-uuid/snapshots/20240815/数据

如果data_file_directories没有在配置文件中配置，默认为Cassandra的家目录/data/data

4 表数据恢复

将每张表的20240815/数据打包并传输到新的服务器

将数据复制到${data_file_directories}/school/表名-uuid/下面

执行命令刷新数据：

./nodetool refresh school 表名

5 导出数据到csv文件(补充)

copy student(id,address,age,education,gender,interest,name,phone) to '/tmp/student.csv' WITH HEADER = true;

以通过设置WITH HEADER=true来确保导出的CSV文件包含列名，这对于后续的数据处理和分析非常有用。

6 备份脚本

1、创建备份目录
mkdir -p /app/module/package/cassandra_backup

2、创建备份脚本
vim /app/module/package/cassandra_backup/Cassandra_backup.sh
#!/bin/bash
keyspace_name=school
cassandra_user=cassandra
cassandra_pass=cassandra
cassandra_host=192.168.137.110
cassandra_port=9042
cassandra_backups=/app/module/package/cassandra_backup
cassandra_basedir=/app/module/cassandra
cassandra_datadir=/data/cassandra/data
cassandra_backups_saveDay=10
###############################################################
##检测备份目录是否存在
[ ! -d ${cassandra_backups} ] && mkdir -p ${cassandra_backups}
#获取时间戳
backup_time=$(date +%Y%m%d)
backup_time_path=${cassandra_backups}/${backup_time}
#创建时间戳目录
rm -rf ${backup_time_path} && mkdir -p ${backup_time_path}
#生成快照需要JAVA_HOME变量
#export JAVA_HOME=/app/module/JDK/jdk1.8
source /etc/profile
###############################################################
backup_time_begin=$(date +'%Y-%m-%d %H:%M:%S')
echo "###############################################################"
echo "数据备份开始，时间: ${backup_time_begin}"
#备份库和表结构
${cassandra_basedir}/bin/cqlsh -u ${cassandra_user} -p ${cassandra_pass} ${cassandra_host} ${cassandra_port} -e "DESC KEYSPACE ${keyspace_name}" > ${backup_time_path}/${keyspace_name}.cql
#生成快照
${cassandra_basedir}/bin/nodetool snapshot -t ${backup_time} ${keyspace_name}
#查找快照并压缩
find ${cassandra_datadir} -name "${backup_time}"|while read line
do
  table_name=$(echo $line|awk -F '[/-]' '{print $6}')
  cd $line/../
  mv ${backup_time} ${table_name}
  tar -zcf ${backup_time_path}/${table_name}.tar.gz ${table_name}
done
#清理快照
${cassandra_basedir}/bin/nodetool clearsnapshot ${keyspace_name}
backup_time_end=$(date +'%Y-%m-%d %H:%M:%S')
echo "数据备份结束，时间: ${backup_time_end}"
echo "###############################################################"
###############################################################
clean_time=$(date -d "-${cassandra_backups_saveDay} day" +%Y%m%d)
clean_time_path=${cassandra_backups}/${clean_time}
[ -d ${clean_time_path} ] && rm -rf ${clean_time_path}
echo "###############################################################"
echo "清理备份路径${clean_time_path}"
echo "###############################################################"

3、修改脚本
注意修改用户名、密码、端口号、IP

4、定时任务
#Cassandra备份脚本
0 2 * * * sh /app/module/package/cassandra_backup/Cassandra_backup.sh &>> /app/module/package/cassandra_backup/Cassandra_backup.log