How do I reindex a MYSQL table using a different disk?

Question

I have my mysql data and index files on disk1

I need to reindex the table and do NOT have enough disk space on disk1

How do I reindex a table on disk1 and use disk2 for the reindex disk space?

For example, table foo is on disk1. I need to add an index on acctnum but do not have enough disk space on disk1. Therefore, I want to reindex on disk2 but keep the data/index files on disk1

alter table foo add index (acctnum);

edits: (by jcolebrand from an answer)

MyISAM is the engine

MySQL version is 5.0.95-log

I need the database operational during the reindex process, unless there is a quick method to load 4.5 million records.

• Mike Brant: Do you need to keep the Database operational during this action? Also, what is the storage engine for the table? And what version of MySQL?
• RolandoMySQLDBA: Is this table MyISAM or InnoDB ???

Answer 1

There is the Percona Tool called pt-online-schema-change

If you can tolerate downtime, you can try the following:

Suppose you have the following

• datadir is /var/lib/mysql
• MyISAM table named mydb.mytable
• You have large disk volume mounted on /backup

You could do the following

mysql -u... -p... -e"CREATE TABLE mydb.mynewtable LIKE mydb.mytable"
mysql -u... -p... -e"ALTER TABLE mydb.mynewtable ADD INDEX (acctnum)"
#
# Create Symlinks Pointing to Another Disk
#
cp /var/lib/mysql/mydb/mynewtable.MY[ID] /backup/.
rm -f /var/lib/mysql/mydb/mynewtable.MY[ID]
chown mysql:mysql /backup/mynewtable.*
ln -s /backup/mynewtable.MYD /var/lib/mysql/mydb/mynewtable.MYD
ln -s /backup/mynewtable.MYI /var/lib/mysql/mydb/mynewtable.MYI
mysql -u... -p... -e"INSERT INTO mydb.mynewtable SELECT * FROM mydb.mytable"
mysql -u... -p... -e"ALTER TABLE mydb.mytable RENAME mydb.myoldtable"
#
# Erase Symlinks
#
rm -f /var/lib/mysql/mydb/mynewtable.MY[ID]
#
# Move Newly Made Table Back
#
mv /backups/*.MY[ID] /var/lib/mysql/mydb/.
mysql -u... -p... -e"FLUSH TABLES"

Seems like a lot of work, eh ??? Try the pt-online-schema-change first.

UPDATE 2012年08月22日 11:56 EDT

I am not sure what would happen, but please hear me out on this suggestion: Try executing a repair on an empty .MYI file.

How do you do that? Take my idea from my first suggestion and augment to swap the new and old .MYI.

mysql -u... -p... -e"CREATE TABLE mydb.mynewtable LIKE mydb.mytable"
mysql -u... -p... -e"ALTER TABLE mydb.mynewtable ADD INDEX (acctnum)"
cp /var/lib/mysql/mydb/mytable.MYI /backup/.
rm -f /var/lib/mysql/mydb/mytable.MYI
cp /var/lib/mysql/mydb/mynewtable.MYI /var/lib/mysql/mydb/mytable.MYI
mysql -u... -p... -e"REPAIR TABLE mydb.mytable"

This should perform an in-place linear index rebuild.

Give it a Try !!!

• miracle173: You are creating the index before you insert the data. This takes a lot of time. I suspect you get a better, smaller index if you create an index after you have inserted the data. is this true?
• RolandoMySQLDBA: @miracle173 not really. There would still be a full copy and append to a temp MYI. I added another suggestion to attempt a linear rebuild of the index. I actually complained about this several years ago to MySQL. They implemented a workaround for index rebuilding ( See my post dba.stackexchange.com/a/2697/877 )
• atxdba: How does pt-online-schema change address the issue of not having enough space in his datadir disk?
• RolandoMySQLDBA: @atxdba I merely suggested its use since the original poster wanted the database operational while reindexing. You can specify temp table renaming options. Thus, you can have pt-online-schema-change work its changes on another disk. It would be a little more insane to script it like I did that to have pt-online-schema-change do it.