Mike,
----- Original Message ----- From: "Mike Debnam" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Friday, March 04, 2005 6:15 AM
Subject: Correct way to use innodb_file_per_table?
I've added a second drive to my database machine and want to split my database tables between the two drives to balance the load and improve performance. I know I'll need to drop and restore the db to get MySQL to create the tables in their own files. What's the correct way to use innodb_file_per_table? If my datadir is /var/db/mysql , should I let MySQL create all the tables in /var/db/mysql then move the files and create symlinks in /var/db/mysql?
the best way would be to symlink whole database directories under the datadir. Then also an ALTER TABLE keeps the new .ibd file on the drive you intended it to be on. If you symlink a single .ibd file, then an ALTER will create the new .ibd file as not symlinked.
As an example, let us say you have three databases: 'database1', 'database2', and 'test'. You may shut down mysqld, copy all the contents of /datadir/database2 to drive 2, and then symlink the directory /datadir/database2 to drive 2.
I see a note in the InnoDB docs that says you can't move the *.ibd files around, what does that refer to?
You cannot move .ibd files between different MySQL instances (installations). You CAN move .MYI and .MYD files.
.ibd files cannot live without the 'system tablespace' in ibdata1. That is why you cannot move .ibd files between different MySQL instances.
System is FreeBSD 5.3, MySQL 4.10, current ibdata file is about 25 gb.
Thanks for any pointers.
Mike
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables
http://www.innodb.com/order.php
------------------------------------------ Save the Pacific Northwest Tree Octopus! | http://zapatopi.net/treeoctopus.html | ------------------------------------------
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
