I had a MySQL database called MyReferences to keep track of articles and books that I've read. I don't know how, but it disappeared. I never made a native MySQL backup (with mysqldump or such), but I did make backups of all the files in the computer.
This is a Debian system, and the files that contain MySQL databases are in
/var/lib/mysql/. On my backup system there is a copy of
/var/lib/mysql/MyReferences/. I copied that directory into
/var/lib/mysql/ on my active system.
I had to change file ownerships and permissions to make the database “visible” in MySQL. They look like this:
-rw-r--r-- 1 mysql mysql 0 2009-09-02 20:20 debian-5.0.flag
-rw-rw---- 1 mysql mysql 10M 2009-11-27 17:48 ibdata1
-rw-rw---- 1 mysql mysql 5.0M 2009-11-27 17:49 ib_logfile0
-rw-rw---- 1 mysql mysql 5.0M 2009-06-03 22:28 ib_logfile1
drwx------ 2 mysql mysql 4.0K 2009-11-28 22:58 MyReferences
drwxr-xr-x 2 mysql mysql 4.0K 2009-09-02 20:20 mysql
-rw------- 1 mysql mysql 7 2009-06-03 22:28 mysql_upgrade_info
drwx------ 2 mysql mysql 4.0K 2009-06-11 22:42 NYM2009
NYM2009/ corresponds to an empty database, and its contents look like
-rw-rw---- 1 mysql mysql 65 2009-06-11 22:42 db.opt
This empty database was created on this system and never had to be restored from backup, so I assume the owner and permissions are correct.
The contents of MyReferences/ are
-rw-rw---- 1 mysql mysql 65 2009-11-28 22:58 db.opt
-rw-rw---- 1 mysql mysql 9.1K 2009-11-28 22:58 MyRefs.frm
-rw-rw---- 1 mysql mysql 1.5M 2009-11-28 22:58 MyRefs.MYD
-rw-rw---- 1 mysql mysql 28K 2009-11-28 22:58 MyRefs.MYI
The owner, group, and permissions are the same as for that NYM2009 database.
I ran MySQL as root and issued
Code:
GRANT ALL ON MyReferences.* TO 'Will'@'localhost';
and it seemed to work normally.
But, when I ran MySQL as Will and tried to
Code:
LOAD DATA INFILE "/home/Will/RefInput" INTO TABLE MyRefs FIELDS TERMINATED BY '#';
I got this error:
Code:
ERROR 1045 (28000): Access denied for user 'Will'@'localhost' (using password: YES)
When I run MySQL as root, the LOAD DATA command works fine.
After reading some LinuxQuestions.org posts, I tried adding
Code:
IDENTIFIED BY 'mypassword'
to the GRANT command, but it didn’t make any difference. I also tried changing my MySQL password and re-issuing the GRANT command.
I can run SELECT statements and view the stucture of the MyReferences database, so I have partial access. Why not LOAD DATA INFILE? Somehow, my GRANT command doesn't seem to be working, even though MySQL acknowledges it normally.
Thanks for any ideas,
Will
