The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Need Help To repair 7gb myisam table

Discussion in 'Database Discussions' started by bhanuprasad1981, Jun 23, 2011.

  1. bhanuprasad1981

    bhanuprasad1981 Well-Known Member

    Joined:
    Aug 5, 2008
    Messages:
    222
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    India
    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.
     
  2. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    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:

    Code:
    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:

    Code:
    /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.
     
  3. bhanuprasad1981

    bhanuprasad1981 Well-Known Member

    Joined:
    Aug 5, 2008
    Messages:
    222
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    India
    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
     
    #3 bhanuprasad1981, Jun 23, 2011
    Last edited: Jun 23, 2011
  4. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Can you temporarily re-size your tmp to be larger to allow repairing it?
     
  5. bhanuprasad1981

    bhanuprasad1981 Well-Known Member

    Joined:
    Aug 5, 2008
    Messages:
    222
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    India
    tried already, securetemp is not working well with 15gb config i am ending up with some error while creating /var/tmp
     
  6. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    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).
     
  7. bhanuprasad1981

    bhanuprasad1981 Well-Known Member

    Joined:
    Aug 5, 2008
    Messages:
    222
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    India
    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 ?
     
  8. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    You can copy the database with this command:

    Code:
    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:

    Code:
    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:

    Code:
    cat /root/.my.cnf
     
Loading...

Share This Page