Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Aug 21 '12 at 11:56

This question came from our site for professional and enthusiast programmers.

2  
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? –  Mike Brant Aug 20 '12 at 15:06
    
Is this table MyISAM or InnoDB ??? –  RolandoMySQLDBA Aug 21 '12 at 15:37

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

share|improve this answer
    
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? –  miracle173 Aug 22 '12 at 6:28
    
@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 ) –  RolandoMySQLDBA Aug 22 '12 at 16:23
    
How does pt-online-schema change address the issue of not having enough space in his datadir disk? –  atxdba Aug 22 '12 at 16:32
    
@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. –  RolandoMySQLDBA Aug 22 '12 at 16:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.