MySQL Backup and Recover

Question

I have been running mysqldump on my set of MySQL databases (MyISAM and INNDOB). Recently I wanted to restore the databases to another server, and so I created the databases and imported the dump file. It was then that I rezlied that the IBD files were not created.

I was under the impressions that what I was doing was a backup, but in the case of INNODB it seems I have to do a backup of /var/lib/mysql//.ibd as well -- right?

If that's the case, then what is the point of a dumpfile if I have to take a backup of IBD files as well?

I am sure I am missing the obvious...

Answer 1

I might be wrong, but I think the usage of .IBD files depend on InnoDB settings&version. They are needed in newer InnoDB innstallations in addition of ibdata file.

If you have dumps you do not need either.

Answer 2

The .ibd files your a talking about are only used/generated for all tables you generate after setting innodb_file_per_table = 1 in your mysqld's configuration. Otherwise all InnoDB table's stuff will be stored in the global ibdata* file.

So i guess, the server you did your restore on, was not using innodb_file_per_table, which per se is no problem ...

Answer 3

mysqldump is sufficient. there are other tools like innodb hot copy or xtrabackup that can make backup based on ibd files... but they are alternative solution to regular mysql dump.