Re: lost mysql root password
On Friday 03 March 2006 09:49 am, Matt Price wrote: Hi folks, a while ago I switched over to mysql-5.0, then forgot about it entirely. Now I'm back to configuring some programs that use mysql, and... I can't seem to login as root or any other user. One option is just that I've forgotten all my passwords (it's been a while since I used any of the programs directly). Another is that some configureation issue has reset permissions or something. In any case I would like to avoid losing all my databases, so I'm wondering whetherthere's any way to force a reset of theroot password, or decrypt the permissions table, or whatever. if you installed using apt, you should have a file called /etc/mysql/debian.cnf . you should be able to use that username and password to log in and reset your password. I am *not* a cracker, I'm just a little incompetent. APpreciate any help you might give. Thanks, Matt -- .''`. Matt Price : :' : Debian User `. `'` hemi-geek `- -- -- anoop [EMAIL PROTECTED] -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of unsubscribe. Trouble? Contact [EMAIL PROTECTED]
Re: lost mysql root password
On 3/3/06, Matt Price [EMAIL PROTECTED] wrote: Hi folks, a while ago I switched over to mysql-5.0, then forgot about it entirely. Now I'm back to configuring some programs that use mysql, and... I can't seem to login as root or any other user. Hi, take a look at: http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html Cheers, Sergio -- Meine Hoffnung soll mich leiten Durch die Tage ohne Dich Und die Liebe soll mich tragen Wenn der Schmerz die Hoffnung bricht
Re: lost mysql root password
Thanks everyone for your help, see below for more: On 3/3/06, anoop aryal [EMAIL PROTECTED] wrote: On Friday 03 March 2006 09:49 am, Matt Price wrote: Hi folks, a while ago I switched over to mysql-5.0, then forgot about it entirely. Now I'm back to configuring some programs that use mysql, and... I can't seem to login as root or any other user. if you installed using apt, you should have a file called /etc/mysql/debian.cnf . you should be able to use that username and password to log in and reset your password. this seemed the simplest of the options presented to me, and fortunately, voila! it owrked. I logged in, and had to use GRSNT statements to reset permissions: mysql grant all privileges on *.* to 'root'@'localhost' identified by 'apassword' with grant option; that worked fine. THen I was looking around and I notice that the user password seemed to have some dublicate rows in it: +--+--+---+ | host | User | Password | +--+--+---+ | localhost| root | hast 1 | anarres | root | hash 1 | | localhost| root | hash 2 | %| root | hash 2 +--+--+---+ [passwords are of course hidden in the above] hmm, that's odd I thought. what to do? It got odder when I tried: select * from user where User='root'; then only the second set of records showed up. on the other hand, mysql select User from user where User LIKE 'root%'; have all four. I guess there must be some white space in the username somewhere. Is there an easy way to identify the precise value of a mysql field (e.g. by dumping to a CSV file)? I'd like to try to figure out what went wrong, and deletethe defective lines. Thanks again for your help, it's so great to have this working. Matt I am *not* a cracker, I'm just a little incompetent. APpreciate any help you might give. Thanks, Matt -- .''`. Matt Price : :' : Debian User `. `'` hemi-geek `- -- -- anoop [EMAIL PROTECTED] -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of unsubscribe. Trouble? Contact [EMAIL PROTECTED]
Re: lost mysql root password
On Friday 03 March 2006 02:20 pm, Matt Price wrote: Thanks everyone for your help, see below for more: On 3/3/06, anoop aryal [EMAIL PROTECTED] wrote: On Friday 03 March 2006 09:49 am, Matt Price wrote: Hi folks, a while ago I switched over to mysql-5.0, then forgot about it entirely. Now I'm back to configuring some programs that use mysql, and... I can't seem to login as root or any other user. if you installed using apt, you should have a file called /etc/mysql/debian.cnf . you should be able to use that username and password to log in and reset your password. this seemed the simplest of the options presented to me, and fortunately, voila! it owrked. I logged in, and had to use GRSNT statements to reset permissions: mysql grant all privileges on *.* to 'root'@'localhost' identified by 'apassword' with grant option; that worked fine. THen I was looking around and I notice that the user password seemed to have some dublicate rows in it: +--+--- ---+---+ | host | User | | Password | +--+--- ---+---+ | localhost| root | | hast 1 | | anarres | root | | hash 1 | | localhost| root | | hash 2 | | %| root | | hash 2 +--+--- ---+---+ [passwords are of course hidden in the above] hmm, that's odd I thought. what to do? It got odder when I tried: select * from user where User='root'; then only the second set of records showed up. on the other hand, mysql select User from user where User LIKE 'root%'; do: select hex(User) from user where User LIKE 'root%'; that should give you the hex values of the characters that are there. have all four. I guess there must be some white space in the username somewhere. Is there an easy way to identify the precise value of a mysql field (e.g. by dumping to a CSV file)? I'd like to try to figure out what went wrong, and deletethe defective lines. Thanks again for your help, it's so great to have this working. Matt I am *not* a cracker, I'm just a little incompetent. APpreciate any help you might give. Thanks, Matt -- .''`. Matt Price : :' : Debian User `. `'` hemi-geek `- -- -- anoop [EMAIL PROTECTED] -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of unsubscribe. Trouble? Contact [EMAIL PROTECTED] -- anoop [EMAIL PROTECTED] -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of unsubscribe. Trouble? Contact [EMAIL PROTECTED]
Re: lost mysql root password
do: select hex(User) from user where User LIKE 'root%'; that should give you the hex values of the characters that are there. have all four. I guess there must be some white space in the username somewhere. Is there an easy way to identify the precise value of a mysql field (e.g. by dumping to a CSV file)? I'd like to try to figure +--+ | hex(User)| +--+ | 726F6F74 | | 726F6F74202020202020202020202020 | | 726F6F74 | | 726F6F74202020202020202020202020 | +--+ 4 rows in set (0.00 sec) thanks anoop! I guess those 02's are spaces then... Looks like most of the user lines from my old db are corrupted in this way as well. wierd. Thanks much for your help! matt
Re: lost mysql root password
On Friday 03 March 2006 03:12 pm, Matt Price wrote: do: select hex(User) from user where User LIKE 'root%'; that should give you the hex values of the characters that are there. have all four. I guess there must be some white space in the username somewhere. Is there an easy way to identify the precise value of a mysql field (e.g. by dumping to a CSV file)? I'd like to try to figure +--+ | hex(User)| +--+ | 726F6F74 | | 726F6F74202020202020202020202020 | | 726F6F74 | | 726F6F74202020202020202020202020 | +--+ 4 rows in set (0.00 sec) thanks anoop! I guess those 02's are spaces then... Looks like most of the user lines from my old db are corrupted in this way as well. wierd. i would check the encoding being used vs. the encoding that was used when it was initially created. it sounds like you were using a wide_char encoding (eg. UTF-8) before and somehow has now reverted back to latin-1 or some other single char encodings. i'm not an expert on encodings etc.. know just enough to be dangerous. but if this is database wide, (look at char/varchar/text fields and they should all display this behavior), this is encoding related. on a wide char encoding (say utf-8), the database reserves multiple bytes per char not knowing what char it will need to save there. when you tell mysql that it's not wide char, it will just show you what it has - including the previously reserved bytes. it's odd that it's using x20 to pad data tho. or something like that. Thanks much for your help! matt -- anoop [EMAIL PROTECTED] -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of unsubscribe. Trouble? Contact [EMAIL PROTECTED]
Re: lost mysql root password
maybe you are not using linux debian distribution. So this way is geereal way. You can setup same version of mysql in an other machine create new mysql root password there and copy. all files under (if your mysql datadir is an other location, look the place of it from /etc/my.cnf ) /var/lib/mysql /mysql/* from new machine to your old machine and restart mysql. And now you can use new root password in your old machine. --- Matt Price [EMAIL PROTECTED] wrote: Hi folks, a while ago I switched over to mysql-5.0, then forgot about it entirely. Now I'm back to configuring some programs that use mysql, and... I can't seem to login as root or any other user. One option is just that I've forgotten all my passwords (it's been a while since I used any of the programs directly). Another is that some configureation issue has reset permissions or something. In any case I would like to avoid losing all my databases, so I'm wondering whetherthere's any way to force a reset of theroot password, or decrypt the permissions table, or whatever. I am *not* a cracker, I'm just a little incompetent. APpreciate any help you might give. Thanks, Matt -- .''`. Matt Price : :' : Debian User `. `'` hemi-geek `- -- -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of unsubscribe. Trouble? Contact [EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of unsubscribe. Trouble? Contact [EMAIL PROTECTED]
Re: lost mysql root password
On Friday 03 March 2006 17:08, anoop aryal wrote: On Friday 03 March 2006 03:12 pm, Matt Price wrote: do: select hex(User) from user where User LIKE 'root%'; that should give you the hex values of the characters that are there. have all four. I guess there must be some white space in the username somewhere. Is there an easy way to identify the precise value of a mysql field (e.g. by dumping to a CSV file)? I'd like to try to figure +--+ | hex(User)| +--+ | 726F6F74 | | 726F6F74202020202020202020202020 | | 726F6F74 | | 726F6F74202020202020202020202020 | +--+ 4 rows in set (0.00 sec) Is that even encoded at all? That looks a bit like it would say root, twice, in ascii to me. Look it up in an ascii table for old 7 bit ascii stuff to be sure. thanks anoop! I guess those 02's are spaces then... Looks like most of the user lines from my old db are corrupted in this way as well. wierd. Not 02, but $20's, eg an ascii space char. i would check the encoding being used vs. the encoding that was used when it was initially created. it sounds like you were using a wide_char encoding (eg. UTF-8) before and somehow has now reverted back to latin-1 or some other single char encodings. i'm not an expert on encodings etc.. know just enough to be dangerous. but if this is database wide, (look at char/varchar/text fields and they should all display this behavior), this is encoding related. on a wide char encoding (say utf-8), the database reserves multiple bytes per char not knowing what char it will need to save there. when you tell mysql that it's not wide char, it will just show you what it has - including the previously reserved bytes. it's odd that it's using x20 to pad data tho. or something like that. Thanks much for your help! matt -- anoop [EMAIL PROTECTED] -- Cheers, Gene People having trouble with vz bouncing email to me should add the word 'online' between the 'verizon', and the dot which bypasses vz's stupid bounce rules. I do use spamassassin too. :-) Yahoo.com and AOL/TW attorneys please note, additions to the above message by Gene Heskett are: Copyright 2006 by Maurice Eugene Heskett, all rights reserved. -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of unsubscribe. Trouble? Contact [EMAIL PROTECTED]
Re: lost mysql root password
On Friday 03 March 2006 04:38 pm, Gene Heskett wrote:
On Friday 03 March 2006 17:08, anoop aryal wrote:
On Friday 03 March 2006 03:12 pm, Matt Price wrote:
do:
select hex(User) from user where User LIKE 'root%';
that should give you the hex values of the characters that are
there.
have all four. I guess there must be some white space in the
username somewhere. Is there an easy way to identify the
precise value of a mysql field (e.g. by dumping to a CSV file)?
I'd like to try to figure
+--+
| hex(User)|
+--+
| 726F6F74 |
| 726F6F74202020202020202020202020 |
| 726F6F74 |
| 726F6F74202020202020202020202020 |
+--+
4 rows in set (0.00 sec)
Is that even encoded at all? That looks a bit like it would say
root, twice, in ascii to me. Look it up in an ascii table
for old 7 bit ascii stuff to be sure.
thanks anoop! I guess those 02's are spaces then... Looks like
most of the user lines from my old db are corrupted in this way as
well. wierd.
Not 02, but $20's, eg an ascii space char.
i would check the encoding being used vs. the encoding that was used
when it was initially created. it sounds like you were using a
wide_char encoding (eg. UTF-8) before and somehow has now reverted
back to latin-1 or some other single char encodings. i'm not an
expert on encodings etc.. know just enough to be dangerous. but if
this is database wide, (look at char/varchar/text fields and they
should all display this behavior), this is encoding related. on a
wide char encoding (say utf-8), the database reserves multiple bytes
per char not knowing what char it will need to save there. when you
tell mysql that it's not wide char, it will just show you what it has
- including the previously reserved bytes. it's odd that it's using
x20 to pad data tho.
again, the database wide funkyness with char fields suggests encodings not
lining up. after counting the chars in the output, it seems like it's
reserving 4 bytes per char (strlen(root)*4) (i know utf-8 causes mysql to
use three bytes per char), and because the later bytes (3 spaces per char)
were uninitialized under old mysql config under, presumably, a different
encoding - because in an encoding like UTF-8, ASCII chars are all 1 byte and
therefore root is the first 4 bytes. the current mysql just spits out x20
for the uninitialized reserved chars??
if(strlen(newFieldValue) == strlen(oldFieldValue) * 4) {
the tables were created using a variable wide chars encoding that supports
up to 4 bytes per chars. but now are under the 'default' (latin-1?) encoding.
}
else {
i dont' have enough encoding-fu to figure this out.
}
:)
or something like that.
Thanks much for your help!
matt
--
anoop
[EMAIL PROTECTED]
--
Cheers, Gene
People having trouble with vz bouncing email to me should add the word
'online' between the 'verizon', and the dot which bypasses vz's
stupid bounce rules. I do use spamassassin too. :-)
Yahoo.com and AOL/TW attorneys please note, additions to the above
message by Gene Heskett are:
Copyright 2006 by Maurice Eugene Heskett, all rights reserved.
--
anoop
[EMAIL PROTECTED]
--
To UNSUBSCRIBE, email to [EMAIL PROTECTED]
with a subject of unsubscribe. Trouble? Contact [EMAIL PROTECTED]
