innodb tables - how to do incremental backups

[sfullman]

Hi All,

I'd never go back to non-innodb tables because of ref. integrity - however I need to mature on my backup techniques, please help here!

OK so for innodb tables ALL data from all the db's is stored in a massive file named ibdata1 or something like that at the mysql database root. Any time any field in any innodb table in any database is changed, ibdata1 changes as well.

1. How would I be able to tell specifically what tables have been changed and the last date they were changed? For myIsam tables this is easy, just check the timestamp on the "file" which represents the table and you've got it.

2. How would I copy a table or database? Again with myIsam tables you just copy the files with the table root name, or rename them, and you have a new table; also you just copy the entire folder and you have copied the database. But how would I do this with Innodb?

Thanks much,
Samuel

[tomhath]

This will get you started. There are some quirks so do a bit more searching around after you get the hang of it. And convince yourself that the restore will work by actually trying it and retesting your application on the new database; lots of people make backups that won't be usable when they really need them...

http://blog.spikesource.com/mysql_hotbackup.htm