CODEWITHSUNDEEP
mysqldatabasebackup
Mattia Merlini
Mattia Merlini

Reputation: 663

Backup and restore part of the database

I explain my scenario. I have few sites (let's say Wordpress for example, but my question is general) with auto-update, some plugins with auto-update, an admin (standard pc user) who writes posts, creates pages, .. Everyday I do a full backup of files and database of each sites and store it on a storage device for 6 months.

Let's suppose that on day 1 everything work. On day 2 a my site has an auto-update with success. On day 3 the admin inserts a lot of stuff, such as many pages and many posts. On day 4 the admin notices that a plugin stops to work properly (due to the update probably). On day 5 the admin advices who maintains the site (me) about the problem with the plugin.

The problem: restore the db with the backup of the day 1, but insert stuff from the backup of day 3. On Wordpress probably I can fix the problem manually by restoring the day 1 db, and replace the wp_post table with the one of day 3 db.

Up to now my question is more general: how can I save in an SQL file (or other format) everyday (or every hour) only modified, added or removed rows of the database? So in the explained scenario, I should have backup files like these:

day1diffs.sql
day2diffs.sql
...

How can I obtain this result? Or better, can be useful to follow this line?

Upvotes: 1

Views: 360

Answers (2)

Steve E.
Steve E.

Reputation: 9343

Do daily backups and turn on MySQL binary logging to maintain a complete step by step change list. With this you can restore to any point in time that you have a backup and logs for.

log-bin=mysql-bin.log
relay-log=mysqld-relay-bin
server-id=1
binlog-format=MIXED
expire_logs_days=29

You can use the binary logs to replay all queries from the time the last backup was taken and there are tools to extract the data from the logs in different forms.

Issue a "FLUSH LOGS" query to the database when the backup is taken to create a new log file from the backup point.

Getting more advanced, there are also tools to copy the logs in near realtime to a remote host for extra peace of mind.

Upvotes: 1

Bernd Buffen
Bernd Buffen

Reputation: 15057

That a difficult question. When we talk only over InnoDB Tables you have 2 reasons:

first:

Use xtrabackup, you can make incremental Backups on each time ie. 1 of month you can make a backup 0 (full), every sunday level 1. it is only the difference between level 0 and at the other days a level 2 backup and every hour a level 3 backup. so you have very small pieces and can go back to each point in time. This backup dont stops or block the Server (only a very short moment) makes a binary copy from each Tablediff and after this it save the piece of binary logs.

second:

Make a mysqldump once a week or what ever you want and hold the binary log more than a week. you can copy it with rsync to archive it. So you can also go to each point in time, but you must restore the backup and after this you must insert the binlogs from the backup-point to to point you want. If your mysqldump 5 days old so you must import all!!! binlogs begin with the dump date to the date you want. This can take much time.

Upvotes: 1

Related Questions