Geek Answers Handbook

Create a new ibdata * file for each database in InnoDB

Is it possible to create a unique ibdate file for each database created in InnoDB?

Because if I have several databases made in InnoDB, all of them will be stored in ibdata1, ibdata2, etc.

Because of this, it is difficult to simply restore 1 database, and not all clients that have InnoDB. MyISAM creates several .frm, MYD, and MYI files for each table, which simplify recovery.

How can we make sorting that recovery easier in InnoDB. Should I make a DUMP.sql file or another solution?

thanks

+4
source share
1 answer

You cannot separate InnoDB data from the database, but you can separate them by table, and the tables are stored in the appropriate subdirectory, like in MyISAM tables. See http://dev.mysql.com/doc/refman/5.1/en/innodb-multiple-tablespaces.html

But regardless of whether you use the central table space file or the file behind the table, you should not back up or restore InnoDB databases by moving files to the file system. Even if you do not use queries to write to InnoDB tables, there are background threads that process ib_logfile, undo logs, insert buffers, etc. Your data is distributed in different places at any given time, and if you try to move InnoDB files, you will corrupt them.

Instead, use mysqldump to safely store logical dumps of InnoDB data while MySQL is running.

+5
source

More articles:


All Articles