2

There are two types of backup techniques I am interested in:

a) SCHEMA backup, which recover database schema (add or remove column, change type of column, add table, etc)

b) DATA backup, which recover data (update, read from one table to another).

Let me explain by example:

  1. First I create the entity 'customer'

Customer

| id | name |

| 11 | jack |

  1. Now I create a snapshot using nodetool
  2. After that I alter the table adding a new column 'test'

| id | name | test |

| 11 | jack | a value |

  1. Make a second snapshot using the nodetool
  2. Now I try to recover the first snapshot by copying the snapshot data in the correct directory (as described here) and unfortunately I get:

| id | name | test |

| 11 | jack | NULL |

... instead of the expected ...

| id | name |

| 11 | jack |

How can I get the expected first snapshot?

Update 1

Related issues:

lidox
  • 1,901
  • 3
  • 21
  • 40

2 Answers2

3

To restore at the same state you also need to restore the system keyspaces. Check, for instance, system.schema_columns and you will see your table structure.

Also, have in mind that if you want to restore just one table, you also need the table schema. So, run desc table before the snapshot.

Before restoring the snapshot, you need the schema to be in place.

Horia
  • 2,942
  • 7
  • 14
  • Thank you for your answer! :) do mean mean something like that: https://stackoverflow.com/a/28829255/1386969 ? – lidox Feb 27 '18 at 14:12
  • 1
    @lidox - I updated my answer. It's better to restore just the keyspace or table instead of the whole system. – Horia Mar 02 '18 at 15:52
  • Oh thanks! :) I search for a script to store and recover. Let me know, if you have some nice options to do so – lidox Mar 02 '18 at 16:23
3

Use the following command to export schema for specific keyspace:

cqlsh 10.0.0.1 -u username -e "DESC keyspace testkeyspace" > testkeyspace.cql

Also use the following command for importing the schema: open the cqlsh in the directory that contains the testkeyspace.cql fie and then run below command: 

source 'testkeyspace.cql';
Amirio
  • 628
  • 1
  • 6
  • 12
  • before 'source 'testkeyspace.cql';' i need to drop all schemas within keyspace, right? example: cqlsh -e "DROP KEYSPACE mykeyspace;" – lidox Feb 28 '18 at 08:22
  • 1
    Why do you need to drop them keyspace? Do not you need it anymore? If you do not need it, delete it. But the above example is where the current schema is raised elsewhere. – Amirio Feb 28 '18 at 08:32
  • If I don't drop, I get following message: Error from server: code=2200 [Invalid query] message="Index sasi_customermail already exists" and lots of more like: Keyspace 'bla bla' already exists – lidox Feb 28 '18 at 08:40
  • 1
    With these definitions, I recommend taking back all the keyspaces and then dropping one of them. – Amirio Feb 28 '18 at 08:42