recovery help needed

We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we really
need some help regaining access to. While attempting to
adjust/add remote user access, we accidentally did the
following:

use mysql;
update user set host = 'SomeBogusIP' where user = 'root';

Now, we can't get into the DB to fix it:

# mysql test
ERROR 1044 (42000): Access denied for user ''@'localhost' to
database 'test'
# mysql mysql
ERROR 1044 (42000): Access denied for user ''@'localhost' to
database 'mysql'

We are not MySQL experts by any stretch, so any help is
appreciated.


Here are the files we evidently touched:
# ls -ltr /var/lib/mysql/mysql/
-rw-r----- 1 mysql mysql 5256 Aug 25 17:33 db.MYD
-rw-r----- 1 mysql mysql 844 Aug 25 17:35 user.MYD
-rw-r----- 1 mysql mysql 2048 Aug 25 17:50 user.MYI
-rw-r----- 1 mysql mysql 4096 Aug 25 17:50 db.MYI

We do have a months-old copy of the 'mysql' db directory.

Thanks in advance.

--
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: recovery help needed

Hey Joe,

stop the server, start it with --skip-grant-tables, change the root
entry in mysql.user to your liking, and then restart the server
without --skip-grant-tables.

viola!

Walter

On Wed, Aug 26, 2009 at 02:12, Joe wrote:
> We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we really
> need some help regaining access to.  While attempting to
> adjust/add remote user access, we accidentally did the
> following:
>
>  use mysql;
>  update user set host =3D 'SomeBogusIP' where user =3D 'root';
>
> Now, we can't get into the DB to fix it:
>
> # mysql test
> ERROR 1044 (42000): Access denied for user ''@'localhost' to
> database 'test'
> # mysql mysql
> ERROR 1044 (42000): Access denied for user ''@'localhost' to
> database 'mysql'
>
> We are not MySQL experts by any stretch, so any help is
> appreciated.
>
>
> Here are the files we evidently touched:
> # ls -ltr /var/lib/mysql/mysql/
> -rw-r----- 1 mysql mysql   5256 Aug 25 17:33 db.MYD
> -rw-r----- 1 mysql mysql    844 Aug 25 17:35 user.MYD
> -rw-r----- 1 mysql mysql   2048 Aug 25 17:50 user.MYI
> -rw-r----- 1 mysql mysql   4096 Aug 25 17:50 db.MYI
>
> We do have a months-old copy of the 'mysql' db directory.
>
> Thanks in advance.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=3Dlists@o=
lindata.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: recovery help needed

You have to reset the permissions.
http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions .html

Carlos

On 8/25/2009 7:12 PM, Joe wrote:
> We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we really
> need some help regaining access to. While attempting to
> adjust/add remote user access, we accidentally did the
> following:
>
> use mysql;
> update user set host = 'SomeBogusIP' where user = 'root';
>
> Now, we can't get into the DB to fix it:
>
> # mysql test
> ERROR 1044 (42000): Access denied for user ''@'localhost' to
> database 'test'
> # mysql mysql
> ERROR 1044 (42000): Access denied for user ''@'localhost' to
> database 'mysql'
>
> We are not MySQL experts by any stretch, so any help is
> appreciated.
>
>
> Here are the files we evidently touched:
> # ls -ltr /var/lib/mysql/mysql/
> -rw-r----- 1 mysql mysql 5256 Aug 25 17:33 db.MYD
> -rw-r----- 1 mysql mysql 844 Aug 25 17:35 user.MYD
> -rw-r----- 1 mysql mysql 2048 Aug 25 17:50 user.MYI
> -rw-r----- 1 mysql mysql 4096 Aug 25 17:50 db.MYI
>
> We do have a months-old copy of the 'mysql' db directory.
>
> Thanks in advance.
>
>


--
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: recovery help needed

OK, thanks, that got me in. But upon inspection, the user.host=20
values do not look fouled up as I thought they were (it appears=20
the bogus update may have aborted). But my access problem=20
remains

If I start with --skip-grant-tables, 'show databases' shows all=20
DBs. But without that flag, I only see the 'information_schema'=20
DB.

Any suggestions as to where I look from here?

On Tuesday 25 August 2009 @ 18:17, Walter Heck - OlinData.com=20
wrote:
> Hey Joe,
>
> stop the server, start it with --skip-grant-tables, change the
> root entry in mysql.user to your liking, and then restart the
> server without --skip-grant-tables.
>
> viola!
>
> Walter
>
> On Wed, Aug 26, 2009 at 02:12, Joe=20
wrote:
> > We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we
> > really need some help regaining access to.  While attempting
> > to adjust/add remote user access, we accidentally did the
> > following:
> >
> >  use mysql;
> >  update user set host =3D 'SomeBogusIP' where user =3D 'root';
> >
> > Now, we can't get into the DB to fix it:
> >
> > # mysql test
> > ERROR 1044 (42000): Access denied for user ''@'localhost' to
> > database 'test'
> > # mysql mysql
> > ERROR 1044 (42000): Access denied for user ''@'localhost' to
> > database 'mysql'
> >
> > We are not MySQL experts by any stretch, so any help is
> > appreciated.
> >
> >
> > Here are the files we evidently touched:
> > # ls -ltr /var/lib/mysql/mysql/
> > -rw-r----- 1 mysql mysql   5256 Aug 25 17:33 db.MYD
> > -rw-r----- 1 mysql mysql    844 Aug 25 17:35 user.MYD
> > -rw-r----- 1 mysql mysql   2048 Aug 25 17:50 user.MYI
> > -rw-r----- 1 mysql mysql   4096 Aug 25 17:50 db.MYI
> >
> > We do have a months-old copy of the 'mysql' db directory.
> >
> > Thanks in advance.
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:  
> >  http://lists.mysql.com/mysql?unsub=3Dlists@olindata.co m



--
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: recovery help needed

>> On Wed, Aug 26, 2009 at 02:12, Joe
> wrote:
>> > We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we
>> > really need some help regaining access to. =A0While attempting
>> > to adjust/add remote user access, we accidentally did the
>> > following:
>> >
>> > =A0use mysql;
>> > =A0update user set host =3D 'SomeBogusIP' where user =3D 'root';
>> > Now, we can't get into the DB to fix it:
>> > # mysql test
>> > ERROR 1044 (42000): Access denied for user ''@'localhost' to
>> > database 'test'

So connect to it from somewhere other than localhost. :-)
Specifically from the bogus IP you set it to.

Let's say you used the bogus IP of 10.200.100.20.
1. Give the mysql server the IP 10.200.100.10 and a netmask 255.255.255.0.
2. On some other machine on the same LAN, give it the bogus IP with
the same netmask.
3. No need to worry about routes, it's on the same LAN.
4. On the other machine, connect using 'mysql -h10.200.100.10 -uroot
-p'. When you enter the correct password, it should let you in.

* I don't know if you'll need to restart mysql for it to bind to the
new IP. I don't think so, but then again I have not tested it.

--=20
Regards... Todd

--
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

R: Re: recovery help needed

--0003255597fe353616047204787d
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

When you are in without the flag , issue the following:

Select current_user();

It should return root.
Then do this:
Grant all privileges on *.* 'root'@'%' identified by 'letmein'

It should work If you did not mess too much with grant tables.

Claudio

Il giorno 26 ago, 2009 4:36 m., "Todd Lyons" ha scritto:

>> On Wed, Aug 26, 2009 at 02:12, Joe > wrote: >> >
We have an inaccessible...
So connect to it from somewhere other than localhost. :-)
Specifically from the bogus IP you set it to.

Let's say you used the bogus IP of 10.200.100.20.
1. Give the mysql server the IP 10.200.100.10 and a netmask 255.255.255.0.
2. On some other machine on the same LAN, give it the bogus IP with
the same netmask.
3. No need to worry about routes, it's on the same LAN.
4. On the other machine, connect using 'mysql -h10.200.100.10 -uroot
-p'. When you enter the correct password, it should let you in.

* I don't know if you'll need to restart mysql for it to bind to the
new IP. I don't think so, but then again I have not tested it.

--
Regards... Todd

-- MySQL General Mailing List For list archives:
http://lists.mysql.com/mysql To unsubscribe: h...

--0003255597fe353616047204787d--

Re: recovery help needed

I actually get the feeling you are not connecting as root.
Try mysql -uroot -p test instead of just mysql test

Have a nice day,

- Martijn

On Wed, Aug 26, 2009 at 03:02, Joe wrote:
> OK, thanks, that got me in. =A0But upon inspection, the user.host
> values do not look fouled up as I thought they were (it appears
> the bogus update may have aborted). =A0But my access problem
> remains
>
> If I start with --skip-grant-tables, 'show databases' shows all
> DBs. =A0But without that flag, I only see the 'information_schema'
> DB.
>
> Any suggestions as to where I look from here?
>
> On Tuesday 25 August 2009 @ 18:17, Walter Heck - OlinData.com
> wrote:
>> Hey Joe,
>>
>> stop the server, start it with --skip-grant-tables, change the
>> root entry in mysql.user to your liking, and then restart the
>> server without --skip-grant-tables.
>>
>> viola!
>>
>> Walter
>>
>> On Wed, Aug 26, 2009 at 02:12, Joe
> wrote:
>> > We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we
>> > really need some help regaining access to. =A0While attempting
>> > to adjust/add remote user access, we accidentally did the
>> > following:
>> >
>> > =A0use mysql;
>> > =A0update user set host =3D 'SomeBogusIP' where user =3D 'root';
>> >
>> > Now, we can't get into the DB to fix it:
>> >
>> > # mysql test
>> > ERROR 1044 (42000): Access denied for user ''@'localhost' to
>> > database 'test'
>> > # mysql mysql
>> > ERROR 1044 (42000): Access denied for user ''@'localhost' to
>> > database 'mysql'
>> >
>> > We are not MySQL experts by any stretch, so any help is
>> > appreciated.
>> >
>> >
>> > Here are the files we evidently touched:
>> > # ls -ltr /var/lib/mysql/mysql/
>> > -rw-r----- 1 mysql mysql =A0 5256 Aug 25 17:33 db.MYD
>> > -rw-r----- 1 mysql mysql =A0 =A0844 Aug 25 17:35 user.MYD
>> > -rw-r----- 1 mysql mysql =A0 2048 Aug 25 17:50 user.MYI
>> > -rw-r----- 1 mysql mysql =A0 4096 Aug 25 17:50 db.MYI
>> >
>> > We do have a months-old copy of the 'mysql' db directory.
>> >
>> > Thanks in advance.
>> >
>> > --
>> > MySQL General Mailing List
>> > For list archives: http://lists.mysql.com/mysql
>> > To unsubscribe:
>> > =A0http://lists.mysql.com/mysql?unsub=3Dlists@olindata.com
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmartijn@cryst=
al-labs.nl
>
>

--
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: recovery help needed

--000e0cd217a05107ca04720870d0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Step # 1 : Stop mysql service
# /etc/init.d/mysql stop

Step # 2: Start to MySQL server w/o password:
# mysqld_safe --skip-grant-tables &

Step # 3: Connect to mysql server using mysql client:
# mysql -u root

Step # 4: Setup new MySQL root user password
mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where
User='root';
mysql> flush privileges;
mysql> quit

Step # 5: Stop MySQL Server:
# /etc/init.d/mysql stop

Step # 6: Start MySQL server and test it
# /etc/init.d/mysql start
# mysql -u root -p


Note: You can check structure of table 'user'
mysql> SHOW CREATE TABLE 'user';

And update other required fields also.

Enjoy ;-)

On Wed, Aug 26, 2009 at 2:36 PM, Martijn Engler wrote:

> I actually get the feeling you are not connecting as root.
> Try mysql -uroot -p test instead of just mysql test
>
> Have a nice day,
>
> - Martijn
>
> On Wed, Aug 26, 2009 at 03:02, Joe wrote:
> > OK, thanks, that got me in. But upon inspection, the user.host
> > values do not look fouled up as I thought they were (it appears
> > the bogus update may have aborted). But my access problem
> > remains
> >
> > If I start with --skip-grant-tables, 'show databases' shows all
> > DBs. But without that flag, I only see the 'information_schema'
> > DB.
> >
> > Any suggestions as to where I look from here?
> >
> > On Tuesday 25 August 2009 @ 18:17, Walter Heck - OlinData.com
> > wrote:
> >> Hey Joe,
> >>
> >> stop the server, start it with --skip-grant-tables, change the
> >> root entry in mysql.user to your liking, and then restart the
> >> server without --skip-grant-tables.
> >>
> >> viola!
> >>
> >> Walter
> >>
> >> On Wed, Aug 26, 2009 at 02:12, Joe
> > wrote:
> >> > We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we
> >> > really need some help regaining access to. While attempting
> >> > to adjust/add remote user access, we accidentally did the
> >> > following:
> >> >
> >> > use mysql;
> >> > update user set host = 'SomeBogusIP' where user = 'root';
> >> >
> >> > Now, we can't get into the DB to fix it:
> >> >
> >> > # mysql test
> >> > ERROR 1044 (42000): Access denied for user ''@'localhost' to
> >> > database 'test'
> >> > # mysql mysql
> >> > ERROR 1044 (42000): Access denied for user ''@'localhost' to
> >> > database 'mysql'
> >> >
> >> > We are not MySQL experts by any stretch, so any help is
> >> > appreciated.
> >> >
> >> >
> >> > Here are the files we evidently touched:
> >> > # ls -ltr /var/lib/mysql/mysql/
> >> > -rw-r----- 1 mysql mysql 5256 Aug 25 17:33 db.MYD
> >> > -rw-r----- 1 mysql mysql 844 Aug 25 17:35 user.MYD
> >> > -rw-r----- 1 mysql mysql 2048 Aug 25 17:50 user.MYI
> >> > -rw-r----- 1 mysql mysql 4096 Aug 25 17:50 db.MYI
> >> >
> >> > We do have a months-old copy of the 'mysql' db directory.
> >> >
> >> > Thanks in advance.
> >> >
> >> > --
> >> > MySQL General Mailing List
> >> > For list archives: http://lists.mysql.com/mysql
> >> > To unsubscribe:
> >> > http://lists.mysql.com/mysql?unsub=lists@olindata.com
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=martijn@crystal-labs.nl
> >
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=aim.prabhat@gmail.com
>
>


--
Best Regards,

Prabhat

--000e0cd217a05107ca04720870d0--