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
------------------------------------------------------------