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.

score 1 · Answer 1 · edited Jun 15 '20 at 09:05

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 !!!

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

1 Answers1

UPDATE 2012-08-22 11:56 EDT