|Changing a MySQL DB Password - Unable to Find Where / How to Change PW|
Not seeing a utility in CPanel or PHPMyAdmin for changing the password
| 6:27 pm on Feb 3, 2010 (gmt 0)|
I'm using an app that has a bug in it. The app has a script that has problems handling the password I created for my related MySQL database. Basically, the script wasn't coded correctly to handle passwords with certain non-alphanumeric characters: >, #, etc.
I've have just spent 2 hours groping around CPanel and PHP MyAdmin, and searching the Web, unsuccessfully, for a simple explanation of "How to change your MySQL database password".
I'm a reluctant user of "command lines" but, failing all else, I can buck up and blow up with the best of the know-nothings. :)
I feel so pathetic. It just seems to me that I MUST be missing some easy answer. I mean, for heaven's sake, it's just a password change! How hard can it be . . . ? :(
I fear the answer will be "Really hard, sucka!" :)
| 7:18 pm on Feb 3, 2010 (gmt 0)|
This may be ugly or easy but you can try to create a new db with a new password, then pull one of the backups from the one that isn't working and populate the new one with it... then use the new one and drop the old one.
Not sure how easy that would be for you though.
| 7:25 pm on Feb 3, 2010 (gmt 0)|
You won't find it in PHPMyAdmin, unless you issue a change password select as in the documentation [dev.mysql.com].
update mysql.user set Password=password('newpass') where User='your_user';
It may not work. I'm pretty sure PHPMyAdmin is running under the user of the database you select, not root, so you may not be allowed to issue this command - all you can do is try it.
However, you should be able to find it in CPanel. Hosting interfaces vary, I work with a lot of them, but generally,
- Locate the database you're working with. Should be a link to click into that database.
- when you are in the next screen, you should see add mySQL user and a list of current users. The current users will either have a link to edit or fields to make changes.
- Click the link to edit the mySQL user, and there should be a delete checkbox/link or fields to modify the user name and password, and possibly database permission settings (checkboxes by insert, update, drop, etc.)
The other way to go at it is via command line, and you can set all a user's values from there, as well as updating the user's privileges with the grant command.
| 7:41 pm on Feb 3, 2010 (gmt 0)|
Thanks for the ideas.
My CPanel offers the following actions:
|Create New Database |
Modify Databases = Check DB: Repair DB:
Add New User
Add User To Database
Current Users -> List Users Delete Users
rocknbil, the strange thing about my version of CPanel is that it allows me to create users, add users to databases and to delete users BUT the danged thing has no GUI/link for changing or recovering a user's password. No kidding. Driving me nuts since it seems so obvious that such a function would be . . helpful.
Demaestro, my work around was to create a new user via CPanel, assign that new user to the existing database, and then I had to go around resetting entries in config files to get them to accept the new connection.
Still, I'm open to all approaches or suggestions for changing a password.
Mind boggling that there isn't an easier solution right in the CPanel. Can't figure why they would omit this.
| 1:17 am on Feb 4, 2010 (gmt 0)|
What's in "list users?" No link to edit?
The reason you can't recover a mysql password is it's encrypted, and stored encrypted. When you enter PW via command line or through an app, the encrypted version of what you enter is compared against what's stored. There's no plain-text version to recover.
You should be able to delete that user and re-add it, though. And its strange you even have this problem, I use odd characters all the time.
| 2:36 am on Feb 4, 2010 (gmt 0)|
Nope. Only to delete users.
Thank you for the insights rocknbil.
It's been that kinda "strange" day . . but it ended on an up note. ;)
| 3:35 am on Feb 4, 2010 (gmt 0)|
1. Create a new user with root privileges
2. Delete the old user
3. Re-create the old user with a new password
If you already have a user with root privileges (I always allocate a user with limited privileges to any web accessible app and maintain a separate and unique user with root privileges) then you can eliminate step one.