How to refresh a testing Cassandra database with production data? (Cassandra)

Context:
Source-Production server: prodcassdb25 (seed), 26, 27
Target-Testing server: testcassdb91 (seed), 92, 93

Steps:
------------------------------------------------------------
Step 1 : scp snapshot files and keyspace definition from Prod to Eng
------------------------------------------------------------
--find and cp the files
[cassandra@prodcassdb25 keyspace_definitions]$ pwd
/cassandra_backup/keyspace_definitions
[cassandra@prodcassdb25 keyspace_definitions]$ ls -lrt|grep 20191213
-rw-r--r-- 1 cassandra dba 49829 Dec 13 03:10 x99b99990_a697_42a0_ad47_c7e139620c98_group_2_ddl_20191213_031001.out
-rw-r--r-- 1 cassandra dba 49829 Dec 13 03:10 x99b99990_a697_42a0_ad47_c7e139620c98_group_3_ddl_20191213_031001.out
-rw-r--r-- 1 cassandra dba 49829 Dec 13 15:10 x99b99990_a697_42a0_ad47_c7e139620c98_group_2_ddl_20191213_151001.out
-rw-r--r-- 1 cassandra dba 49829 Dec 13 15:10 x99b99990_a697_42a0_ad47_c7e139620c98_group_3_ddl_20191213_151001.out
[cassandra@prodcassdb25 keyspace_definitions]$
[cassandra@prodcassdb25 keyspace_definitions]$ cp x99b99990_a697_42a0_ad47_c7e139620c98_group_*ddl_20191213_03*.out /home2/kenchen/bk
[cassandra@prodcassdb25 keyspace_definitions]$


[cassandra@prodcassdb25 snapshots]$ pwd
/cassandra_backup/snapshots
[cassandra@prodcassdb25 snapshots]$ cp -R 20191213_0310_* /home2/kenchen/bk

---do scp in testcassdb91 seednode
scp -r prodcassdb25:/home2/kenchen/bk .

--cp the directories to /cassandra_backup/snapshots
[cassandra@testcassdb91 bk]$ cp -R 20191213_0310_* /cassandra_backup/snapshots
[cassandra@testcassdb91 bk]$ ll /cassandra_backup/snapshots |grep 1213
drwxr-xr-x 43 cassandra dba 4096 Dec 17 09:51 20191213_0310_02
drwxr-xr-x 43 cassandra dba 4096 Dec 17 09:51 20191213_0310_06
[cassandra@testcassdb91 bk]$ ll /cassandra_backup/snapshots
total 16
drwxr-xr-x 43 cassandra dba 4096 Dec 16 14:52 20191116_0310_02
drwxr-xr-x 43 cassandra dba 4096 Dec 16 14:52 20191116_0310_06
drwxr-xr-x 43 cassandra dba 4096 Dec 17 09:51 20191213_0310_02
drwxr-xr-x 43 cassandra dba 4096 Dec 17 09:51 20191213_0310_06
[cassandra@testcassdb91 bk]$

------------------------------------------------------------
Step 2 : clean up target nodes--drop keyspaces from seed node
------------------------------------------------------------
--in seed node drop keyspaces
cassandra@cqlsh> desc keyspaces;

system_auth         x99b99990_a697_42a0_ad47_c7e139620c98_group_2
system              x99b99990_a697_42a0_ad47_c7e139620c98_group_3
system_distributed  system_traces

cassandra@cqlsh> drop keyspace x99b99990_a697_42a0_ad47_c7e139620c98_group_2;
cassandra@cqlsh> drop keyspace x99b99990_a697_42a0_ad47_c7e139620c98_group_3;
cassandra@cqlsh>

verify on all nodes:
cassandra@cqlsh> desc keyspaces;

system_traces  system_auth  system  system_distributed


------------------------------------------------------------
Step 3 : clean up--run node repair from seed node
------------------------------------------------------------

[cassandra@testcassdb91 ~]$ nodetool repair --full
[2019-12-17 09:17:54,550] Starting repair command #5, repairing keyspace system_auth with repair options (parallelism: parallel, primary range: false, incremental: false, job threads: 1, ColumnFamilies: [], dataCenters: [], hosts: [], # of ranges: 768)
[2019-12-17 09:17:55,056] Repair session 29b29cc0-20f1-11ea-b4c2-4d3d3ded966d for range (-2597627288616693569,-2545287888585638866] finished (progress: 0%)
[2019-12-17 09:17:55,259] Repair session 29b2eae3-20f1-11ea-b4c2-4d3d3ded966d for range (-5356597931097554743,-5339397917747191084] finished (progress: 0%)
[2019-12-17 09:17:55,440] Repair session 29b33901-20f1-11ea-b4c2-4d3d3ded966d for range (-2903199422797207225,-2903114251772426092] finished (progress: 0%)
[2019-12-17 09:17:55,573] Repair session 29b36011-20f1-11ea-b4c2-4d3d3ded966d for range (5078614939157582036,5161374894663544739] finished (progress: 1%)
[2019-12-17 09:17:55,601] Repair session 29b38721-20f1-11ea-b4c2-4d3d3ded966d for range (483483848257142510,494502176940626340] finished (progress: 1%)
[2019-12-17 09:17:55,620] Repair session 29b3ae32-20f1-11ea-b4c2-4d3d3ded966d for range (-7299231363705753989,-7280800272545333799] finished (progress: 1%)
...
...
[2019-12-17 09:18:10,765] Repair session 32114061-20f1-11ea-b4c2-4d3d3ded966d for range (1079344578921085961,1099364809618351926] finished (progress: 99%)
[2019-12-17 09:18:10,767] Repair session 32116770-20f1-11ea-b4c2-4d3d3ded966d for range (8074300300555063568,8078332406054179512] finished (progress: 99%)
[2019-12-17 09:18:10,769] Repair session 32116773-20f1-11ea-b4c2-4d3d3ded966d for range (2905063564631007102,2935904027252613794] finished (progress: 100%)
[2019-12-17 09:18:10,779] Repair completed successfully
[2019-12-17 09:18:10,779] Repair command #6 finished in 2 seconds
[cassandra@testcassdb91 ~]$

verify on all nodes:
cassandra@cqlsh> desc keyspaces;

system_traces  system_auth  system  system_distributed




------------------------------------------------------------
Step 4 : clean up--remove physicical directories from all nodes
------------------------------------------------------------

[cassandra@testcassdb93 data]$ pwd
/cassandra/cassandra/data/data
[cassandra@testcassdb93 data]$ ll
total 12
drwxr-xr-x 22 cassandra dba 4096 Apr 11  2019 system
drwxr-xr-x  6 cassandra dba  233 Apr 11  2019 system_auth
drwxr-xr-x  4 cassandra dba  123 Apr 11  2019 system_distributed
drwxr-xr-x  4 cassandra dba  102 Apr 11  2019 system_traces
drwxr-xr-x 43 cassandra dba 4096 Dec 16 15:28 x99b99990_a697_42a0_ad47_c7e139620c98_group_2
drwxr-xr-x 43 cassandra dba 4096 Dec 16 15:28 x99b99990_a697_42a0_ad47_c7e139620c98_group_3
[cassandra@testcassdb93 data]$ rm -rf x99b99990_a697_42a0_ad47_c7e139620c98_group_2
[cassandra@testcassdb93 data]$ rm -rf x99b99990_a697_42a0_ad47_c7e139620c98_group_3
[cassandra@testcassdb93 data]$
[cassandra@testcassdb93 data]$ ll
total 4
drwxr-xr-x 22 cassandra dba 4096 Apr 11  2019 system
drwxr-xr-x  6 cassandra dba  233 Apr 11  2019 system_auth
drwxr-xr-x  4 cassandra dba  123 Apr 11  2019 system_distributed
drwxr-xr-x  4 cassandra dba  102 Apr 11  2019 system_traces
[cassandra@testcassdb93 data]$

------------------------------------------------------------
Step 5 : Restore keyspace meta data from seed node
------------------------------------------------------------
--restore group 2
[cassandra@testcassdb91 bk]$ pwd
/home2/cassandra/bk
[cassandra@testcassdb91 bk]$ ll
total 112
drwxr-xr-x 43 cassandra dba  4096 Dec 17 09:07 20191213_0310_02
drwxr-xr-x 43 cassandra dba  4096 Dec 17 09:07 20191213_0310_06
-rw-r--r--  1 cassandra dba 49829 Dec 17 09:07 x99b99990_a697_42a0_ad47_c7e139620c98_group_2_ddl_20191213_031001.out
-rw-r--r--  1 cassandra dba 49829 Dec 17 09:07 x99b99990_a697_42a0_ad47_c7e139620c98_group_3_ddl_20191213_031001.out
[cassandra@testcassdb91 bk]$ /cassandra/cassandra/bin/cqlsh 10.12.171.130 -u cassandra -p cassandra -f x99b99990_a697_42a0_ad47_c7e139620c98_group_2_ddl_20191213_031001.out

--you can check progress in other nodes
[cassandra@testcassdb92 data]$ ll
total 8
drwxr-xr-x 22 cassandra dba 4096 Apr 11  2019 system
drwxr-xr-x  6 cassandra dba  233 Apr 11  2019 system_auth
drwxr-xr-x  4 cassandra dba  123 Apr 11  2019 system_distributed
drwxr-xr-x  4 cassandra dba  102 Apr 11  2019 system_traces
drwxr-xr-x  7 cassandra dba 4096 Dec 17 09:29 x99b99990_a697_42a0_ad47_c7e139620c98_group_2

--then restore group 3
[cassandra@testcassdb91 bk]$ /cassandra/cassandra/bin/cqlsh 10.12.171.130 -u cassandra -p cassandra -f x99b99990_a697_42a0_ad47_c7e139620c98_group_3_ddl_20191213_031001.out


------------------------------------------------------------
Step 6 : Drain and shut down each Cassandra node, one node at a time.
------------------------------------------------------------
[cassandra@testcassdb93 data]$ nodetool drain
[cassandra@testcassdb93 data]$ sudo /etc/init.d/cassandra_init_script.sh stop
Shutdown Cassandra: pid is 7576
pid 7576 is running
OK


------------------------------------------------------------
Step 7 : On seed node, delete all files in the commitlog and saved_caches directory
------------------------------------------------------------
[cassandra@testcassdb91 bk]$ cd /cassandra/cassandra/data/
[cassandra@testcassdb91 data]$ ll
total 0
drwxr-xr-x 2 cassandra dba  43 Dec 17 09:44 commitlog
drwxr-xr-x 8 cassandra dba 192 Dec 17 09:33 data
drwxr-xr-x 2 cassandra dba  51 Dec 17 07:26 saved_caches
[cassandra@testcassdb91 data]$ rm -r commitlog/* saved_caches/*
[cassandra@testcassdb91 data]$


------------------------------------------------------------
Step 8 : On seed node, run restore script
------------------------------------------------------------

cat SnapshotKeyspace_master.dat to get snapshot name and Keyspace name

[cassandra@prodcassdb25 ~]$ cat /cassandra/admin/logs/SnapshotKeyspace_master.dat|grep -w "ARCHIVING Snapshot" |grep 20191213
Fri Dec 13 03:10:03 PST 2019 | 20191213_031001 | Info | ARCHIVING Snapshot : 20191213_0310_02 : Keyspace : x99b99990_a697_42a0_ad47_c7e139620c98_group_2
Fri Dec 13 03:10:08 PST 2019 | 20191213_031001 | Info | ARCHIVING Snapshot : 20191213_0310_06 : Keyspace : x99b99990_a697_42a0_ad47_c7e139620c98_group_3
Fri Dec 13 15:10:04 PST 2019 | 20191213_151001 | Info | ARCHIVING Snapshot : 20191213_1510_02 : Keyspace : x99b99990_a697_42a0_ad47_c7e139620c98_group_2
Fri Dec 13 15:10:08 PST 2019 | 20191213_151001 | Info | ARCHIVING Snapshot : 20191213_1510_06 : Keyspace : x99b99990_a697_42a0_ad47_c7e139620c98_group_3
[cassandra@prodcassdb25 ~]$

--prepare the commands as below
./restore_cassandra.bash x99b99990_a697_42a0_ad47_c7e139620c98_group_2 20191213_0310_02
./restore_cassandra.bash x99b99990_a697_42a0_ad47_c7e139620c98_group_3 20191213_0310_06


[cassandra@testcassdb91 bk]$ cd /cassandra/admin/scripts/
[cassandra@testcassdb91 scripts]$ ./restore_cassandra.bash x99b99990_a697_42a0_ad47_c7e139620c98_group_2 20191213_0310_02

        Restoring tables from directory, '/cassandra_backup/snapshots/20191213_0310_02', to directory, '/cassandra/cassandra/data/data/x99b99990_a697_42a0_ad47_c7e139620c98_group_2'
        Restore snapshot, '20191213_0310_02', to keyspace, 'x99b99990_a697_42a0_ad47_c7e139620c98_group_2'
Continue (y/n)?y

Restoring table, 'api_portal_apiapppolicybindings'
Restoring table, 'api_portal_apiorgpolicybindings'
Restoring table, 'api_portal_apipolicyparameters'
Restoring table, 'api_portal_portalactionqueue'
Restoring table, 'api_portal_portalapiaccessstore'
Restoring table, 'api_portal_portalapi'
Restoring table, 'api_portal_portalapimethod'
Restoring table, 'api_portal_portalapiquotaconstraintstore'
Restoring table, 'api_portal_portalapiquotadetailsstore'
Restoring table, 'api_portal_portalapiquotastore'
Restoring table, 'api_portal_portalapplicationpermissionstore'
Restoring table, 'api_portal_portalconfigstore'
Restoring table, 'api_portal_portalexports'
Restoring table, 'api_portal_portalidentitystore'
Restoring table, 'api_portal_portalorganizationstore'
Restoring table, 'api_portal_portalorganizationstoreldap'
Restoring table, 'api_portal_portalregistrationtoken'
Restoring table, 'api_portal_portalremotehost'
Restoring table, 'api_portal_portaltimestamp'
Restoring table, 'api_portal_portaluserstore'
Restoring table, 'api_portal_portaluserstoreldap'
Restoring table, 'api_portal_portalvirtualizedapi'
Restoring table, 'api_portal_portalvirtualizedapimethod'
Restoring table, 'api_server_portalapikeystore'
Restoring table, 'api_server_portalapplicationstore'
Restoring table, 'api_server_portalexternalclientstore'
Restoring table, 'api_server_portaloauthresourcesstore'
Restoring table, 'api_server_portaloauthstore'
Restoring table, 'backendcredentials_backendcreds'
Restoring table, 'clientappsuc1_app'
Restoring table, 'clientappsuc1_apporchardclient'
Restoring table, 'counter_snapshot'
Restoring table, 'counter_table'
Restoring table, 'kps_schema'
Restoring table, 'oauth_accesstokens'
Restoring table, 'oauth_authorizations'
Restoring table, 'oauth_authzcodes'
Restoring table, 'oauth_clientaccesstokens'
Restoring table, 'oauth_clientrefreshtokens'

Restoring table, 'oauth_refreshtokens'
Restoring table, 'statuscode_statuscd'
[cassandra@testcassdb91 scripts]$
[cassandra@testcassdb91 scripts]$

[cassandra@testcassdb91 scripts]$ ./restore_cassandra.bash x99b99990_a697_42a0_ad47_c7e139620c98_group_3 20191213_0310_06

        Restoring tables from directory, '/cassandra_backup/snapshots/20191213_0310_06', to directory, '/cassandra/cassandra/data/data/x99b99990_a697_42a0_ad47_c7e139620c98_group_3'
        Restore snapshot, '20191213_0310_06', to keyspace, 'x99b99990_a697_42a0_ad47_c7e139620c98_group_3'
Continue (y/n)?y

Restoring table, 'api_portal_apiapppolicybindings'
Restoring table, 'api_portal_apiorgpolicybindings'
Restoring table, 'api_portal_apipolicyparameters'
Restoring table, 'api_portal_portalactionqueue'
Restoring table, 'api_portal_portalapi'
Restoring table, 'api_portal_portalapiaccessstore'
Restoring table, 'api_portal_portalapimethod'
Restoring table, 'api_portal_portalapiquotaconstraintstore'
Restoring table, 'api_portal_portalapiquotadetailsstore'
Restoring table, 'api_portal_portalapiquotastore'
Restoring table, 'api_portal_portalapplicationpermissionstore'
Restoring table, 'api_portal_portalconfigstore'
Restoring table, 'api_portal_portalexports'
Restoring table, 'api_portal_portalidentitystore'
Restoring table, 'api_portal_portalorganizationstore'
Restoring table, 'api_portal_portalorganizationstoreldap'
Restoring table, 'api_portal_portalregistrationtoken'
Restoring table, 'api_portal_portalremotehost'
Restoring table, 'api_portal_portaltimestamp'
Restoring table, 'api_portal_portaluserstore'
Restoring table, 'api_portal_portaluserstoreldap'
Restoring table, 'api_portal_portalvirtualizedapi'
Restoring table, 'api_portal_portalvirtualizedapimethod'
Restoring table, 'api_server_portalapikeystore'
Restoring table, 'api_server_portalapplicationstore'
Restoring table, 'api_server_portalexternalclientstore'
Restoring table, 'api_server_portaloauthresourcesstore'
Restoring table, 'api_server_portaloauthstore'
Restoring table, 'backendcredentials_backendcreds'
Restoring table, 'clientappsuc1_app'
Restoring table, 'clientappsuc1_apporchardclient'
Restoring table, 'counter_snapshot'
Restoring table, 'counter_table'
Restoring table, 'kps_schema'
Restoring table, 'oauth_accesstokens'
Restoring table, 'oauth_authorizations'
Restoring table, 'oauth_authzcodes'
Restoring table, 'oauth_clientaccesstokens'
Restoring table, 'oauth_clientrefreshtokens'
Restoring table, 'oauth_refreshtokens'
Restoring table, 'statuscode_statuscd'
[cassandra@testcassdb91 scripts]$

------------------------------------------------------------
Step 9 : On other nodes, delete all files in the commitlog and saved_caches directory
------------------------------------------------------------
[cassandra@testcassdb93 data]$ ll
total 0
drwxr-xr-x 2 cassandra dba  80 Dec 17 09:44 commitlog
drwxr-xr-x 8 cassandra dba 192 Dec 17 09:33 data
drwxr-xr-x 2 cassandra dba  51 Dec 17 07:28 saved_caches
[cassandra@testcassdb93 data]$ rm -r commitlog/* saved_caches/*
[cassandra@testcassdb93 data]$




------------------------------------------------------------
Step 10 : On other nodes, delete all files in KEYSPACE directories being restored
------------------------------------------------------------
[cassandra@testcassdb93 data]$ pwd
/cassandra/cassandra/data/data
[cassandra@testcassdb93 data]$ ll
total 4
drwxr-xr-x 22 cassandra dba 4096 Apr 11  2019 system
drwxr-xr-x  6 cassandra dba  233 Apr 11  2019 system_auth
drwxr-xr-x  4 cassandra dba  123 Apr 11  2019 system_distributed
drwxr-xr-x  4 cassandra dba  102 Apr 11  2019 system_traces
drwxr-xr-x  2 cassandra dba    6 Dec 17 10:00 x99b99990_a697_42a0_ad47_c7e139620c98_group_2
drwxr-xr-x  2 cassandra dba    6 Dec 17 10:00 x99b99990_a697_42a0_ad47_c7e139620c98_group_3
[cassandra@testcassdb93 data]$
[cassandra@testcassdb93 data]$ rm -rf x99b99990_a697_42a0_ad47_c7e139620c98_group_2/*
[cassandra@testcassdb93 data]$ rm -rf x99b99990_a697_42a0_ad47_c7e139620c98_group_3/*
[cassandra@testcassdb93 data]$


------------------------------------------------------------
Step 11 : One at a time, start the Cassandra seed node, and then the other nodes
------------------------------------------------------------
wait for each to be in Up/Normal (UN) state in nodetool status before you proceed to the next node.

sudo /etc/init.d/cassandra_init_script.sh start

[cassandra@testcassdb91 scripts]$ sudo /etc/init.d/cassandra_init_script.sh start
Starting Cassandra: OK
[cassandra@testcassdb91 scripts]$ nodetool status
Datacenter: datacenter1
=======================
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
--  Address        Load       Tokens       Owns (effective)  Host ID                               Rack
DN  10.12.171.132  ?          256          100.0%            72a30930-0561-4252-b26a-05a2ee2e6c05  rack1
UN  10.12.171.130  101.16 MB  256          100.0%            62099913-cca1-45c9-b4de-43fbc6492846  rack1
DN  10.12.171.131  ?          256          100.0%            5e97be86-89c2-4068-ac8f-66763d3ce5fd  rack1

[cassandra@testcassdb91 scripts]$



------------------------------------------------------------
Step 12 : run full repair on each node one at a time
------------------------------------------------------------
nodetool repair -pr --full


------------------------------------------------------------
Step 13 : verify
------------------------------------------------------------

/app keep growing in Cassandra (Cassandra)

During nodetool repair job, we found an interesting issue that /app goes up quickly and drops down after job finishing.

The official document says:
"By default, the repair command takes a snapshot of each replica immediately and then sequentially repairs each replica from the snapshots. For example, if you have RF=3 and A, B and C represents three replicas, this command takes a snapshot of each replica immediately and then sequentially repairs each replica from the snapshots (A<->B, A<->C, B<->C) instead of repairing A, B, and C all at once. This allows the dynamic snitch to maintain performance for your application via the other replicas, because at least one replica in the snapshot is not undergoing repair."

So basically during this repair job, data will be checked thru all 3 nodes(if RF=3) and sync any inconsistency (if there is any).

If the repair jobs on all nodes are set start at the same time, this causes HUGE resource issues and data usage issues because snapshots will be created for this job cross all nodes!!

So the solution is:
1) run the repair job on one node, mark the time.
2) cron the jobs accordingly for all nodes without overlapping each other.
3) keep an eye on the cron jobs to ensure they work as expected.