Restore cassandra cluster data when acccidentally drop table

Question

As you know, Cassandra cluster have replication to prevent data loss even if some node in the cluster down. But in the case that an admin accidentally drop a table with big amount of data, and that command had already executed by all the replica in cluster, is this means you lost that table and cannot restore it? Is there any suggestion to cope with this kind of disaster with short server down time?

Answer 1

Step-1: I created one table by using the below command

CREATE TABLE Cricket (
    PlayerID uuid,
    LastName varchar,
    FirstName varchar,  
    City varchar,
    State varchar,
    PRIMARY KEY (PlayerID));

Step-2: Insert 3 records by using below command

INSERT INTO Cricket (PlayerID, LastName, FirstName, City, State)
VALUES (now(), 'Pendulkar', 'Sachin', 'Mumbai','Maharastra');
INSERT INTO Cricket (PlayerID, LastName, FirstName, City, State)
VALUES (now(), 'Vholi', 'Virat', 'Delhi','New Delhi');
INSERT INTO Cricket (PlayerID, LastName, FirstName, City, State)
VALUES (now(), 'Sharma', 'Rohit', 'Berhampur','Odisha');

Step-3: Accidentally I deleted Cricket table

drop table Cricket;

Step-4: Need to recover that table by using auto snapshotbackup Note: auto_snapshot (Default: true ) Enable or disable whether a snapshot is taken of the data before keyspace truncation or dropping of tables. To prevent data loss, using the default setting is strongly advised.

Step-5: Find the snapshot locations and files

cassandra@node1:~/data/students_details$ cd cricket-88128dc0960d11ea947b39646348bb4f
cassandra@node1:~/data/students_details/cricket-88128dc0960d11ea947b39646348bb4f$ ls -lrth
total 0
drwxrwxr-x 2 cassandra cassandra  6 May 14 18:05 backups
drwxrwxr-x 3 cassandra cassandra 43 May 14 18:06 snapshots
Step-6: You will get one .cql file in that snapshot location which having tables DDL.

cassandra@node1:~/data/students_details/cricket-88128dc0960d11ea947b39646348bb4f/snapshots/dropped-1589479603749-cricket$ ls -lrth
total 44K
-rw-rw-r-- 1 cassandra cassandra   92 May 14 18:06 md-1-big-Summary.db
-rw-rw-r-- 1 cassandra cassandra   61 May 14 18:06 md-1-big-Index.db
-rw-rw-r-- 1 cassandra cassandra   16 May 14 18:06 md-1-big-Filter.db
-rw-rw-r-- 1 cassandra cassandra  179 May 14 18:06 md-1-big-Data.db
-rw-rw-r-- 1 cassandra cassandra   92 May 14 18:06 md-1-big-TOC.txt
-rw-rw-r-- 1 cassandra cassandra 4.7K May 14 18:06 md-1-big-Statistics.db
-rw-rw-r-- 1 cassandra cassandra    9 May 14 18:06 md-1-big-Digest.crc32
-rw-rw-r-- 1 cassandra cassandra   43 May 14 18:06 md-1-big-CompressionInfo.db
-rw-rw-r-- 1 cassandra cassandra  891 May 14 18:06 schema.cql
-rw-rw-r-- 1 cassandra cassandra   31 May 14 18:06 manifest.json
cassandra@node1:~/data/students_details/cricket-88128dc0960d11ea947b39646348bb4f/snapshots/dropped-1589479603749-cricket$

more schema.cql
cassandra@node1:~/data/students_details/cricket-88128dc0960d11ea947b39646348bb4f/snapshots/dropped-1589479603749-cricket$ more schema.cql
CREATE TABLE IF NOT EXISTS students_details.cricket (
        playerid uuid PRIMARY KEY,
        city text,
        firstname text,
        lastname text,
        state text)
        WITH ID = 88128dc0-960d-11ea-947b-39646348bb4f
        AND bloom_filter_fp_chance = 0.01
        AND dclocal_read_repair_chance = 0.1
        AND crc_check_chance = 1.0
        AND default_time_to_live = 0
        AND gc_grace_seconds = 864000
        AND min_index_interval = 128
        AND max_index_interval = 2048
        AND memtable_flush_period_in_ms = 0
        AND read_repair_chance = 0.0
        AND speculative_retry = '99PERCENTILE'
        AND comment = ''
        AND caching = { 'keys': 'ALL', 'rows_per_partition': 'NONE' }
        AND compaction = { 'max_threshold': '32', 'min_threshold': '4', 'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy' }
        AND compression = { 'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor' }
        AND cdc = false
        AND extensions = {  };

Step-7: Login to the database and create table using that DDL.

    apiadmin@cqlsh:coopersdev> use students_details;
    apiadmin@cqlsh:students_details> CREATE TABLE IF NOT EXISTS students_details.cricket (
                        ...         playerid uuid PRIMARY KEY,
                        ...         city text,
                        ...         firstname text,
                        ...         lastname text,
                        ...         state text)
                        ...         WITH ID = 88128dc0-960d-11ea-947b-39646348bb4f
                        ...         AND bloom_filter_fp_chance = 0.01
                        ...         AND dclocal_read_repair_chance = 0.1
                        ...         AND crc_check_chance = 1.0
                        ...         AND default_time_to_live = 0
                        ...         AND gc_grace_seconds = 864000
                        ...         AND min_index_interval = 128
                        ...         AND max_index_interval = 2048
                        ...         AND memtable_flush_period_in_ms = 0
                        ...         AND read_repair_chance = 0.0
                        ...         AND speculative_retry = '99PERCENTILE'
                        ...         AND comment = ''
                        ...         AND caching = { 'keys': 'ALL', 'rows_per_partition': 'NONE' }
                        ...         AND compaction = { 'max_threshold': '32', 'min_threshold': '4', 'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy' }
                        ...         AND compression = { 'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor' }
                        ...         AND cdc = false
                        ...         AND extensions = {  };
apiadmin@cqlsh:students_details>

Step-8: copy all the files on snapshot folder to existing cricket table folder

cassandra@node1:~/data/students_details/cricket-88128dc0960d11ea947b39646348bb4f/snapshots/dropped-1589479603749-cricket$ pwd
/home/cassandra/data/students_details/cricket-88128dc0960d11ea947b39646348bb4f/snapshots/dropped-1589479603749-cricket
cassandra@node1:~/data/students_details/cricket-88128dc0960d11ea947b39646348bb4f/snapshots/dropped-1589479603749-cricket$ cp * /home/cassandra/data/students_details/cricket-88128dc0960d11ea947b39646348bb4f
cassandra@node1:~/data/students_details/cricket-88128dc0960d11ea947b39646348bb4f/snapshots/dropped-1589479603749-cricket$ cd /home/cassandra/data/students_details/cricket-88128dc0960d11ea947b39646348bb4f
cassandra@node1:~/data/students_details/cricket-88128dc0960d11ea947b39646348bb4f$ ls -lrth
total 44K
drwxrwxr-x 2 cassandra cassandra    6 May 14 18:05 backups
drwxrwxr-x 3 cassandra cassandra   43 May 14 18:06 snapshots
-rw-rw-r-- 1 cassandra cassandra  891 May 14 18:11 schema.cql
-rw-rw-r-- 1 cassandra cassandra   92 May 14 18:11 md-1-big-TOC.txt
-rw-rw-r-- 1 cassandra cassandra   92 May 14 18:11 md-1-big-Summary.db
-rw-rw-r-- 1 cassandra cassandra 4.7K May 14 18:11 md-1-big-Statistics.db
-rw-rw-r-- 1 cassandra cassandra   61 May 14 18:11 md-1-big-Index.db
-rw-rw-r-- 1 cassandra cassandra   16 May 14 18:11 md-1-big-Filter.db
-rw-rw-r-- 1 cassandra cassandra    9 May 14 18:11 md-1-big-Digest.crc32
-rw-rw-r-- 1 cassandra cassandra  179 May 14 18:11 md-1-big-Data.db
-rw-rw-r-- 1 cassandra cassandra   43 May 14 18:11 md-1-big-CompressionInfo.db
-rw-rw-r-- 1 cassandra cassandra   31 May 14 18:11 manifest.json
cassandra@node1:~/data/students_details/cricket-88128dc0960d11ea947b39646348bb4f$

Step-9: start restore table data using sstableloader by using below command

cassandra@node1:~$ sstableloader -d 10.213.61.21 -username cassandra --password cassandra /home/cassandra/data/students_details/cricket-d3576f60960f11ea947b39646348bb4f/snapshots
    Established connection to initial hosts
    Opening sstables and calculating sections to stream

    Summary statistics:
       Connections per host    : 1
       Total files transferred : 0
       Total bytes transferred : 0.000KiB
       Total duration          : 2920 ms
       Average transfer rate   : 0.000KiB/s
       Peak transfer rate      : 0.000KiB/s

Step-10: Table restored successfully.Please verify.

 playerid                             | city      | firstname | lastname  | state
--------------------------------------+-----------+-----------+-----------+------------
 d7b12c90-960f-11ea-947b-39646348bb4f | Berhampur |     Rohit |    Sharma |     Odisha
 d7594890-960f-11ea-947b-39646348bb4f |     Delhi |     Virat |     Vholi |  New Delhi
 d7588540-960f-11ea-947b-39646348bb4f |    Mumbai |    Sachin | Pendulkar | Maharastra

Answer 2

From cassandra docs:

auto_snapshot (Default: true ) Enable or disable whether a snapshot is taken of the data before keyspace truncation or dropping of tables. To prevent data loss, using the default setting is strongly advised. If you set to false, you will lose data on truncation or drop.

Answer 3

If the administrator has been deleted the data and replicated in all the nodes it is difficult to recover the data without a consistent backup.

Maybe considering that the deletes in cassandra are not executed instantly you can recover the data. When you delete data, cassandra replace the data with a tombstone.The tombstone can then be propagated to replicas that missed the initial remove request.

See http://wiki.apache.org/cassandra/DistributedDeletes

Columns marked with a tombstone exist for a configured time period (defined by the gc_grace_seconds value set on the column family), and then are permanently deleted by the compaction process after that time has expired. The default value is 10 days.

Following the explanation in About Deletes maybe if you shutdown some of the nodes and wait until the compaction succeed and the data is completely delete from the SSTables and then turn on again the nodes the data could appear again. But this will only happen if you dont make periodical repair operations on the node.

I have never tried this before, it is only an idea that comes to me reading the cassandra documentation.