It’s one of those subjects that is treated by both the course I took and the books I obtained as an “oh yeah and also” tangential subject, very briefly discussed with inadequate examples. :mad:
Hasn’t mattered much so far because the original bd I am replacing with a MySQL as my teeth-cutting / get-your-feet-wet project was one where everyone had equal access anyhow. But to run mysqldump (to make a backup) it really wants an account name and password. So I go to create at least one actual account with an actual password and grant it full permissions to do everything so it can be used for mysqldump.
As root I type grant usage on . to ‘ahunter3’ identified by password ‘yaddayadda’.
MySQL gets grouchy: “Password hash should be a 41-digit hexadecimal number”. Sheesh. Ok freaking hell. grant usage on . to ‘ahunter3’ identified by password '123456789012345678901234567890A" (ya satisfied?). It took that. I exit and then go to log back in as ‘ahunter3’ with that horrid password and it won’t let me in.
TAKE II: grant usage on . to ‘ahunter3’ and in a separate command set password for ahunter3 = password(‘yaddayadda’);. It takes those.
It lets me log in as ahunter3 now. But when I try to ‘use MyDataBaseName’ (to switch to the schema I actually use) it says permission denied.
I log back in as root. grant all privileges on ‘MyDataBaseName’ to ‘ahunter3’;
MySQL: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘‘MyDataBaseName’ to ‘ahunter3’’ at line 1
*grant usage on . to ‘ahunter3’ *
It takes that. I log out as root. Try to log in as ahunter3. MySQL: Access denied for user ‘ahunter3’@‘localhost’ (using password: YES)
I try not specifying the password, to see if it answers back Password and waits on a non-echoing 2nd line for me to provide it (that’s how I log in as root, btw) and instead it just logs me straight in w/o a password. I enter use MyDataBaseName and it says Access denied for user ‘’@‘localhost’ to database ‘MyDataBaseName’
Several attempts at Googling the secret answer were involved in getting this far. I remain stuckered. Anything obvious I’m doing wrong?
I always have to mess around with this when fiddling with MySql.
This is how I would do it:
grant all on mydb.* to ahunter3@"%" identified by ‘secret’;
Of course, this is granting everything (which is what I usually want), but you can whittle it down to taste.
Notes:
[ul][]You have to mention DB| and OBJ|. I did this by using mydb.[]Do not enclose the user in quotes.[]You need to include the host. I use “%” in double quotes to indicate that this user can connect from anywhere. You can use localhost (probably without quotes) and it should work.MySql should accept ‘secret’ as such, without being some long hexadecimal thing. It’s when you are updating the user tables that you use the Password(‘secret’) function that generates the long hash. The “identified by” syntax implicitly does the hash.[/ul]
As mentioned by minor7flat5, a user in MySQL consists of both the username and the host. At this point, you might want to take a look at all the users you’ve got created. As root, you can do the query “select user, host from mysql.user” to see what users are defined. For each user, you can see what privileges are set with the command “show grants for ‘user’@‘host’”. Technically, you don’t need the single quotes around the username or host in some circumstances but it’ll always work to include them.
Doing this, you’ll be able to see which hosts ‘ahunter’ can log in from. You can also see which users have passwords and what privileges (and databases) the users have access to. That might help sort out what’s going on.
It appears that the main problem was that I was not identifying the HOST, as you said. I had to nuke the account and then recreate it and give it a password and grant it all on . but I can now log in as ahunter3 instead of root, and yes it requires (and takes) a password. (For real. I did a select query as ahunter3 and it worked).
Onward to problem TWO:
my actual database is named “ebcoa” in case that’s not obvious. It has several tables. It’s usually syntax (I’m new enough at this that wrong syntax doesn’t leap out at me)… OK lessee…
note that it did not query for password on the following line on those latter two.
Anyone got a light to shine here? Hmm maybe I should flush the privs sure enough, even though it lets me log in as ahunter3, can’t hurt, yes?
Problem 2 has nothing to do with MySQL; it’s an OS permission problem. Whatever user you’re using (on your shell, not MySQL) does not have permission to write the file ebcoabkup2.sql.
Are you in a directory owned by that user, such as the user’s home dir?
I have MySQL installed to /usr/local which is not tolerant of ordinary users writing new files. As shown in terminal output in previous posts, I was in the bin folder of the MySQL folder when the mysqldump command was executed. It did not let me sudo. I even used “su” and logged in as root but mysqldump would not execute at all for me as su. (Weird!).
I realized my first attempt at specifying a complete path to backup location (to make it go somewhere other than right next to the db in /usr/local) was borked because the OS was of course going to start from where I was (therefore /usr/local/mysql/bin/Users/ahunter/Documents – no such place).
I know there are things you can do with dots to “go up the hierarchy” but I decided it was easier to cd to directory root and then specify the entire path to mysqldump to invoke it.
A bit of experimentation showed me it wanted -p but not to enter the password until it asked me for it:
I stand by my original complaint: this was very poorly documented in all the books.
