Due to a glitch not on my database, my script has gone crazy and has overwritten old data in my database already.

Enough fresh backup is not. But there are binary logs for the last 24 hours (overland old data, unfortunately, more than a day). But as I understand it, binary logs only store changes, and old data is not stored (although there was somehow no intelligible materiel in Google).

Is it possible to recover old data in these conditions? If this is still impossible, then what, besides more frequent backups, can be done so that in the future the data can be rolled back in case anything happens?

  • You can version the data: do not update them, but create a more recent version (for performance purposes, you can simply create a table {tablename}_journal , into which to duplicate the changes, and continue to work in the usual way, as if nothing had happened). - etki

1 answer 1

In order to use binary logs, they should be skipped using the mysqlbinlog utility mysqlbinlog

 mysqlbinlog binlog.0000003 > binlog3.sql

As a result, you will receive a text MySQL dump, which you can edit, removing the instructions that are already executed in your backup and the instructions that caused the database to be corrupted. The resulting dump can be applied to the database, for example, dbname , using the mysql utility (the database should contain the latest backup)

 mysql -u root -p dbname < binlog3.sql

Here root is the name of the MySQL user.