restoring mysql db doesn"t restore user passwords

I'm running Fedora 11 i386 with Mysql 5.1.32. I dumped my mysql
databases with:

mysqldump -u root -pxxxxxxxxxxx --lock-all-tables --all-databases >
/root/mysql-backup/all-db.sql

and then wiped the operating system and reinstalled. Then I ran

mysqladmin -u root password

to set my root password. Then I needed to restore all of the databases
so I ran:

mysql --verbose -u root -p < /root/mysql-backup/all-db.sql

and that seems to have restored everything, there is the correct data in
my databases, however, users can not connect. For example, this user
was working prior to the reinstall:

mysql -u adam -pxxxxxxxxx
ERROR 1045 (28000): Access denied for user 'adam'@'localhost' (using
password: YES)

but in the mysql.user table, adam exists and has his password set from
the dump file:

mysql> select User,Password from mysql.user where user = 'adam';
+------+------------------+
| User | Password |
+------+------------------+
| adam | 2bf6b1712b10928e |
| adam | 2bf6b1712b10928e |
| adam | 2bf6b1712b10928e |
+------+------------------+
3 rows in set (0.06 sec)

It is the same with all of the other users also. They have their
password set from the dump file, but they can't log in with what their
password was either. Any ideas on how to get the passwords restored?




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: restoring mysql db doesn"t restore user passwords

My theory would be that it's an OLD-PASSWORDS issue. It would seem that
you might have used the
old_passwords=3D1 in your original configuration my.cnf but it's not in
your new configuration file.


-----Original Message-----
From: Adam Williams [mailto:awilliam@mdah.state.ms.us]=20
Sent: Monday, June 15, 2009 7:41 PM
To: mysql@lists.mysql.com
Subject: restoring mysql db doesn't restore user passwords

I'm running Fedora 11 i386 with Mysql 5.1.32. I dumped my mysql=20
databases with:

mysqldump -u root -pxxxxxxxxxxx --lock-all-tables --all-databases >=20
/root/mysql-backup/all-db.sql

and then wiped the operating system and reinstalled. Then I ran

mysqladmin -u root password

to set my root password. Then I needed to restore all of the databases=20
so I ran:

mysql --verbose -u root -p < /root/mysql-backup/all-db.sql

and that seems to have restored everything, there is the correct data in

my databases, however, users can not connect. For example, this user=20
was working prior to the reinstall:

mysql -u adam -pxxxxxxxxx
ERROR 1045 (28000): Access denied for user 'adam'@'localhost' (using=20
password: YES)

but in the mysql.user table, adam exists and has his password set from=20
the dump file:

mysql> select User,Password from mysql.user where user =3D 'adam';
+------+------------------+
| User | Password |
+------+------------------+
| adam | 2bf6b1712b10928e |
| adam | 2bf6b1712b10928e |
| adam | 2bf6b1712b10928e |
+------+------------------+
3 rows in set (0.06 sec)

It is the same with all of the other users also. They have their=20
password set from the dump file, but they can't log in with what their=20
password was either. Any ideas on how to get the passwords restored?




--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Dtlittle@tgrnet.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: restoring mysql db doesn"t restore user passwords

you're a genius! I had old_passwords=1 in my.cnf, changed it to 0,
restarted mysql, and then the users worked like a charm. thanks!

Little, Timothy wrote:
> My theory would be that it's an OLD-PASSWORDS issue. It would seem that
> you might have used the
> old_passwords=1 in your original configuration my.cnf but it's not in
> your new configuration file.
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org