MySQL create db from .frm, .MYD .MYI

Posted on 2009-12-22
Last Modified: 2013-11-08
Created new database but manged to lose original master admin id root.
Using phpMyAdmin cannot see databases.  The directories holding the data still exist in /var/lib/mysql/datbase names.
How can I re attach these data files so the datbases can be managed?

The part of script that I think caused the problem is:
USE mysql;

REPLACE INTO user (host, user, password)
    VALUES (
-- IMPORTANT: Change this password.

REPLACE INTO db (host, db, user, select_priv, insert_priv, update_priv,
                 delete_priv, create_priv, drop_priv, index_priv)
    VALUES (
        'Y', 'Y', 'Y', 'Y',
        'Y', 'Y', 'Y'
Question by:Masasomi

    Author Comment

    I have further reviwed the script and think the cause is a loss of privileges to root and missed the last line that states;

    PHP My Admin showing root@localhost has no privileges.
    Tried to use the following to grant privileges to root

    GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY 'root password';

    Script fails with message:
    #1045 - Access denied for user 'root'@'localhost' (using password: YES)
    LVL 50

    Expert Comment

    by:Steve Bink
    Yup, that sure would cause a problem.  First, see here:

    That will enable you to reset the root password.  You may also have an issue with permissions to the server, so make revisit the GRANTs you have provided that user.
    LVL 21

    Accepted Solution

    For MyISAM, copying datafiles to data directory will make it available - server restart is required.
    - stop server
    - copy files
    - start server

    About your GRANT query, it should be:
    GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'root password';
    Missing single quotes around USER....


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    1. Introduction As many people are interested in Linux but not as many are interested or knowledgeable (enough) to install Linux on their system, here is a safe way to try out Linux on your existing (Windows) system. The idea is that you insta…
    Creating and Managing Databases with phpMyAdmin in cPanel.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now