Copying datadir directory enough for backing up a database with InnoDB tables?
Please tell me you are not (and not even thinking of) using file system copying on a database server that's up and running? That is virtually guaranteed to lead to corruption of your copies, leaving your backups useless. You won't receive any errors - except, of course, when you try to restore :-)
It's not very clear from your post (correct me if I'm wrong), but you're now running your database on Linux? If so, then use Percona's XtraBackup for your InnoDB tables. This tool performs non-blocking hot backups of InnoDB tables.Take a look at my answer here for some MySQL backup options. For MyISAM, you could look at MyDumper if mysqldump isn't performant.
If your server is still on Windows, then you're out of luck. XtraBackup doesn't work on Windows, nor does MyDumper (AFAIK). You could check this out (disclaimer - never used).
Again, I'm not totally clear on what, exactly, you mean here.
Now that I'm copying a database directory with a mix of InnoDB and MyISAM tables from Linux to Windows, would this approach work as well?
If you mean that you are copying the mysqldump from Linux to Windows, then yes, that will work as any file copy. If, on the other hand, you mean that you are copying (using the file system) data files on a running server and tranferring those copies from Linux to Windows, then your "backups" will be useless on both systems.
Ain't InnoDB tables data spread across various places such as in ibdata1? I don't have to backup ibdata1 or anything right?
If you are backing up the database using mysqldump, then that is sufficient. If you are simply copying files from one location to another on a running database server, then it really doesn't matter what you do or don't copy, because your backup will be useless.
[EDIT] in response to the original poster's comments.
From here (correct answer)
mysqldump -uuser -ppass --single-transaction --routines --triggers --all-databases > backup_db.sql
Just substitute your schema name for --all-databases. You say that your "table" is InnoDB - is the rest of the database/schema InnoDB? If not, you should make it so. You seem to think that the datadir has to be copied in some shape or form - it DOES NOT. The dump contains the necessary data to reconstruct your database.
In the event of a problem, you restore your database from the last BACKUP (i.e. the dump). There are other solutions which allow PITR (Point In Time Recovery), but they are more complex and replication can also address this issue.
See also these posts (1, 2).
My take on the content of these posts (check the reputations of the posters) is that with InnoDB, the above command will allow for a backup while at the same time permitting reads and writes to the database - note that performance may be affected.
Per the Cold Backup section of the MySQL Manual (http://dev.mysql.com/doc/refman/5.6/en/innodb-backup.html), you need ibdata1, ib_logfiles.
Obviously, this requires that you stop the server and flush all caches to disk.