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.

Seem to have broken MySQL

Discussion in 'General Discussion' started by VapourMike, Oct 2, 2009.

  1. VapourMike

    VapourMike Member

    Joined:
    Jul 5, 2003
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    South East UK
    Hi,

    Ive got a small personal server which I use to host approx 20-25 hobby/personal websites, now its been going for about 12 months now without problem, however recently we had some problems on the machine involving a rogue script which was eventually cleaned up etc, but in the process of this one of the admins has managed to cause MySQL problems,

    The service simply fails to start, when looking in the .err file I see the following

    091002 00:35:35 mysqld started
    091002 0:35:37 InnoDB: Started; log sequence number 0 8740736
    091002 0:35:37 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
    091002 00:35:37 mysqld ended

    Ive got two data locations

    /var/lib/mysql
    /home/MySQL

    Both of which I have tried (contain same data) and both have been chown mysql:mysql on ...

    Any ideas? driving me crazy, is it possible to re-install SQL using WHM/Cpanel?

    Everything else is working, whm works, cpanel works, apache works etc etc etc
     
  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
    If the issue is due to corruption of some sort for an InnoDB table, you could add this line to /etc/my.cnf file:

    skip-innodb

    Then restart mysql:

    /etc/init.d/mysql restart

    Of course, you would then need to determine which users have databases with InnoDB and try to resolve the errors on those databases.
     
  3. VapourMike

    VapourMike Member

    Joined:
    Jul 5, 2003
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    South East UK
    Hi,

    Ive added the line to my my.cnf file (both copies) and still nothing

    root@atom2 [/]# service mysql start
    Starting MySQL.Manager of pid-file quit without updating fi[FAILED]

    and then in the .err log file I have

    091002 17:02:07 mysqld started
    091002 17:02:07 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
    091002 17:02:07 mysqld ended

    My CNF file looks like

    [mysqld]
    set-variable = max_connections=300
    datadir=/home/MySQL/
    skip-innodb


    Hmm ...
     
  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
    It seems like MySQL was moved from /var/lib/mysql to a new location possibly? If that was the case, you can ps aux|grep mysql to see where it is loading from for the --datadir= line.

    If someone moved MySQL to /home/MySQL rather than /var/lib/mysql, then a symlink should have been created from /var/lib/mysql to point to that new location rather than having 2 copies of mysql in 2 different locations.
     
  5. VapourMike

    VapourMike Member

    Joined:
    Jul 5, 2003
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    South East UK
    Hi

    The data was (and still is) inside the /var/lib/mysql folder and there is a copy (kind of a backup) in /home/MySQL/ which ive switched to as a temp to see if it loaded (still doesn't).

    root@atom2 [/]# ps aux|grep mysql
    root 7442 0.0 0.0 4112 624 pts/0 S+ 17:20 0:00 grep mysql

    Regardless of the skip-innodb it still produces the same error in the .err file. To make life easier ive removed the /home/MySQL directory (archived off to a working work server)
     
    #5 VapourMike, Oct 2, 2009
    Last edited: Oct 2, 2009
  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
    I apologize on suggesting the ps aux as MySQL isn't running, so you cannot see where it is loading from because of that, sorry.

    The issue is that the error message for several online searches mention trying to load MySQL from a non-default location causing that error. Since you mention someone messed up MySQL functioning in your initial post, what changes did that individual perform precisely?

    Additionally, you can try running /scripts/mysqlup --force as that would try to update MySQL by patching any missing dependancies. If it errors out while running, please post a log of what is occurring.

    You may want to consider opening a ticket with us via WHM on the server in Support Center so that we could investigate the issue directly.
     
    #6 cPanelTristan, Oct 2, 2009
    Last edited: Oct 2, 2009
  7. VapourMike

    VapourMike Member

    Joined:
    Jul 5, 2003
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    South East UK
    Hi,

    To be honest I do not know exactly, I know that person moved the datadir to a different location, the new location ran out of space so they moved it back and then went to start and its refusing to start up, as far as Im aware they run the chown on the folder and done a copy of the datadir.

    The problem is, although they done a copy of the /home/MySQL to /var/lib/mysql ... neither of the dirs work now, even when putting the my.cnf back, it would appear as if a file(s) has gone missing or corrupt.

    Is there any way of reinstalling MySQL and copying in just the users database folders, theres only about 18 of them.

    Thanks
    Mike

    EDIT
    Running the script now, will post its output in a bit
     
    #7 VapourMike, Oct 2, 2009
    Last edited: Oct 2, 2009
  8. VapourMike

    VapourMike Member

    Joined:
    Jul 5, 2003
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    South East UK
    Have got this error ...


    Unable to create mysql privileges database at /scripts/mysqlup line 606.
    This script updates all the mysql privilege tables to be usable by
    the current version of MySQL

    You can safely ignore all 'Duplicate column' and 'Unknown column' errors
    because these just mean that your tables are already up to date.
    This script is safe to run even if your tables are already up to date!

    Got a failure from command:
    cat /usr/share/mysql/mysql_fix_privilege_tables.sql | /usr/bin/mysql --no-defaults --force --user=root --host=localhost --database=mysql
    Please check the above output and try again.

    If you get an 'Access denied' error, you should run this script again and
    give the MySQL root user password as an argument with the --password= option
    Failed to locate MySQL socket. Please check the mysql configuration.
     
  9. 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
    At this point, if you haven't already done so, I'd suggest sending in a support ticket using WHM's Service Center area. Once we do figure out how to resolve it directly, we will explain what we did to fix it so that you know for the future.
     
  10. VapourMike

    VapourMike Member

    Joined:
    Jul 5, 2003
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    South East UK
    I have made a support request as Im stumped by it, however I havnt got priority support for some reason so may take a while
     
Loading...

Share This Page