Need Help To repair 7gb myisam table

[bhanuprasad1981]

hello guys i need some help to repair one crashed table which is 7+gb in size , i am running latest 5.1.x mysql , type of table is myisam, i tried to repair it using phpmyadmin but i am left with 100% /tmp full issue, my temp is just 5gb in size and its filling up soon, i tried to specify /tmp location in mjy.cnf but it seems not working in mysql 5.1.x please advice how to repair it fast so clients get low down time.

[cPanelTristan]

So you did already try in /etc/my.cnf the following under the [mysqld] section:

tmpdir = /home/mysqltmp

Then saving the file. At that point, you would create the directory:

mkdir /home/mysqltmp
chown mysql:mysql /home/mysqltmp
chmod 1777 /home/mysqltmp

Then you would restart MySQL and check it did change the tmp directory location:

/etc/init.d/mysql restart
mysqladmin var | grep tmpdir

If it were properly changed and the directory existing, then MySQL were restarted, it should work to change the temporary directory for MySQL. I've never seen it not work no matter what MySQL version.

[bhanuprasad1981]

yes i confirmed tmp directory :-

root@lux [~]# mysqladmin var | grep tmpdir
| slave_load_tmpdir | /home/mysqltmp |
| tmpdir | /home/mysqltmp |


but still as i start repair temp is starting to fill within minutes of repair it already reached 60% so i had to stop repair again and rechecked /tmp disk usage is back to 3% of 5gb

[cPanelTristan]

Can you temporarily re-size your tmp to be larger to allow repairing it?

[bhanuprasad1981]

tried already, securetemp is not working well with 15gb config i am ending up with some error while creating /var/tmp

[cPanelTristan]

Can you copy the database to another machine where you repair it there? A machine that doesn't have a separate /tmp at all possibly (such as a test machine).

[bhanuprasad1981]

i dont have separate test machines, but i have another server where temp is 10gb in size, how can i copy this db to that server ?

[cPanelTristan]

You can copy the database with this command:

mysqldump --add-drop-table dbname | gzip > /home/dbname.sql.gz

Here replace dbname with the full name of the database.

On the other machine after copying the file over to it, you can restore the database using this command:

gunzip < /home/dbname.sql.gz | mysql -u root -p

You will need the MySQL root password to run this. You can grab it on the new machine using:

cat /root/.my.cnf