• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1312
  • Last Modified:

MySQL create db from .frm, .MYD .MYI

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 (
        'localhost',
        'root',
-- IMPORTANT: Change this password.
        PASSWORD('abcdefg')
);

REPLACE INTO db (host, db, user, select_priv, insert_priv, update_priv,
                 delete_priv, create_priv, drop_priv, index_priv)
    VALUES (
        'localhost',
        'horde',
        'horde',
        'Y', 'Y', 'Y', 'Y',
        'Y', 'Y', 'Y'
);
0
Masasomi
Asked:
Masasomi
1 Solution
 
MasasomiAuthor Commented:
I have further reviwed the script and think the cause is a loss of privileges to root and missed the last line that states;

FLUSH PRIVILEGES
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)
0
 
Steve BinkCommented:
Yup, that sure would cause a problem.  First, see here:

http://www.cyberciti.biz/tips/recover-mysql-root-password.html

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.
0
 
K VDatabase ConsultantCommented:
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....


0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now