I have a 100Gb innodb table on C:\ drive and the C:\ drive is out of space now.I deleted many rows but the the ibd file did not shrink. I have more than 200Gb available on D:\drive and subsequently configured tmpdir to be in d:\drive and tried to optimize table .But when I optimize table it is taking up whatever space is left on c:\ drive and not using d:\
What parameter should I change?
You cannot shrink the innodb data file (default name is ibdata1) without deleting it :S .
Using innodb engine you should use innodb_file_per_table=1
To solve you space issue you should:
1) Execute a full database backup: mysqldump -u root -pMyPassword -R --all-databases > full.sql
2) Stop mysql service
3) Add (or change value of) the parameter innodb_file_per_table=1
4) Drop the existing innodb data file (/$mysqldir/ibdata1)
5) Start mysql service
6) Restore the backup: mylsql -u root -pMyPassword mysql < full.sql
Now the innodb data file will not growth too much anymore, and an optimize table will allow you to shrink the involve table data file since now each table will amnage it's own data file.
Using symbolic links (at least under Linux), it allows you to locate databases on different disks.
There is tmpdir parameter for that. And I think you can set that with
SET GLOBAL tmpdir = D:/temp
before any optimization operation or add following under mysld
set-variable = tmpdir=/var/tmp
As a bonus I strongly suggest you to use
innodb_file_per_table=1
to have separate file for each table if of course you have innodb tables. And also using compressed data will reduce your disk usage.
