After excute an error update insert or delete sql, how to restore cockroach

after excute an error update sql, how to restore cockroach, I want use cockroach in my product env, but i can’t find the answer to this question. I think cockroach need a log recording the update insert and delete sql like mysql’s binlog. when error modify sql occer, this log can reverse db with no lose, but i can’t find this kind of log.

Hi @landsky,

I’m not to clear what you’re referring to. Are you asking about transactions? Or are you asking about restoring from a backup? Or are you referring to something completely different?

At the moment, we do not have anything like mysql’s binlog because we are a distributed system and implementing something similar wouldn’t work.

If you could clarify exactly what you’re asking I would be happy to see what your best options are.

Thanks,

Ron

First insert many data to a database, then backup it, then insert many data to database, then execute delete table where 1=1.
At this time look for the data has delete, if run restore, many data will lose.

Hey @landsky,

Can you give me the exact commands that you’re running in the cockroach sql shell?

Thanks,

Ron

  1. execute sql "create table xxx (a int, b int)
  2. execute sql "insert into xxx (a, b) values (10, 10)
  3. backup data
  4. execute sql "insert into xxx (a, b) values (5, 5)
  5. execute sql "delete from xxx where 1=1
  6. restore data
    the field data (5, 5) can’t come back

Hi @landsky,

It would make sense that the data (5, 5) can’t come back. The reason is you are performing the backup before inserting (5, 5). Which means the restore will not contain that data.

If I understand correctly, you are concerned with a mistake in your production database like delete from xxx where 1=1 deletes everything and you will not be able to recover your data.

I suggest the following:

  1. Read here about incremental backups.

  2. Be sure to provide your database users with only the permissions they need to prevent this from occurring. You can use the sql_safe_updates flag to prevent users from doing things like delete from xxx. However that flag will not work in the case where you have a where clause. (the where clause will bypass the flag.

Read more about granting priveledges here.

If you have enterprise features enabled you can also grant roles to certain users.

Let me know if you have any other questions.

Thanks,
Matt