Re: Recovery from a MySQL dump is too long !

[Reindl Harald]

Am 14.08.2012 09:42, schrieb Bob Sauvage:
> Hello *, 
> 
> My INNODB database has a size of 80GO. I've a replication setup on 3 slaves 
> and I backup my db from them. 
> If a problem occurs on the database, a recovery from a dump takes around 6H ! 
> That's too long for us. 
> 
> 2 ideas : 
> 1. Stop the slave and rsync the folder /var/lib/mysql to another server
> 2. Stop the slave, take a dump and when it's finish, pull the dump in another 
> MySQL instance daily. 

i do the stop slave and rsync data-dir since many years
over WAN connections for offsite backups, never was a
friend of dumping databases to textfiles

if something is damaged due a power-interruption this
is also the fastest restore because rsync is much faster
even with --checksums as any dump



signature.asc
Description: OpenPGP digital signature

Re: recovery help needed

[prabhat kumar]

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 mysql844 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=li...@olindata.com
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=mart...@crystal-labs.nl
> >
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com
>
>


-- 
Best Regards,

Prabhat

Re: recovery help needed

[Martijn Engler]

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=li...@olindata.com
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=mart...@crystal-labs.nl
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

R: Re: recovery help needed

[Claudio Nanni]

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

Re: recovery help needed

[Todd Lyons]

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

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:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: recovery help needed

[Joe]

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=li...@olindata.com



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: recovery help needed

[Carlos Proal]

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 mysql844 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=arch...@jab.org

Re: recovery help needed

[Walter Heck - OlinData.com]

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=li...@olindata.com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: recovery question

[Per Jessen]

Gleb Paharenko wrote:

> Hello.
> 
> REPAIR TABLE ... USE_FRM helps in difficult cases. See:
>   http://dev.mysql.com/doc/mysql/en/repair-table.html
> 

Thanks Gleb.  I'd forgotten about that option. 

To others who try the same thing - make sure you have enough space in your 
TMPDIR or set
TMPDIR/--tmpdir to a place where you have sufficient space.  I started the 
REPAIR, which ran
for a while, then stopped and appeared to be idling.  It took me a few hours 
before I checked
the mysqld.log and found out that it had run out of space in /tmp and was 
waiting for some to
be cleared up. 


-- 
/Per Jessen, Zürich


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: recovery question

[Angelo Zanetti]

try

REPAIR TABLE 'tablename'


Gleb Paharenko wrote:

>Hello.
>
>
>
>REPAIR TABLE ... USE_FRM helps in difficult cases. See:
>
>  http://dev.mysql.com/doc/mysql/en/repair-table.html
>
>
>
>
>
>
>
>
>
>
>
>Per Jessen <[EMAIL PROTECTED]> wrote:
>
>  
>
>>All,
>>
>>
>
>  
>
>>I've got a table with about 25mill rows that was victim of a crash recently. 
>>(power-failure).
>>
>>
>
>  
>
>>I've been trying to recover it, but I'm not making much progress. 
>>
>>
>
>  
>
>
>  
>
>>>From the most recent attempts:
>>
>>
>
>  
>
>
>  
>
>>myisamchk --safe-recover --force 
>>
>>
>
>  
>
>>- recovering (with keycache) MyISAM-table ''
>>
>>
>
>  
>
>>Data records: 21622679
>>
>>
>
>  
>
>>4988000
>>
>>
>
>  
>
>>7256000
>>
>>
>
>  
>
>>10627000
>>
>>
>
>  
>
>>myisamchk: error: 126 for record at pos 1589881104
>>
>>
>
>  
>
>>MyISAM-table '' is not fixed because of errors
>>
>>
>
>  
>
>
>  
>
>>myisamchk -r --force --tmpdir=/data2/tmp 
>>
>>
>
>  
>
>>- recovering (with sort) MyISAM-table ''
>>
>>
>
>  
>
>>Data records: 12876899
>>
>>
>
>  
>
>>- Fixing index 1
>>
>>
>
>  
>
>>- Fixing index 2
>>
>>
>
>  
>
>>- Fixing index 3
>>
>>
>
>  
>
>>Key 3 - Found wrong stored record at 0
>>
>>
>
>  
>
>>MyISAM-table '' is not fixed because of errors
>>
>>
>
>  
>
>
>  
>
>
>  
>
>>Where do I go from here?  I've got a backup of the table, but I'm not sure 
>>what sort of state it
>>
>>
>
>  
>
>>is in.
>>
>>
>
>  
>
>
>  
>
>
>
>
>  
>

Re: recovery question

[Gleb Paharenko]

Hello.



REPAIR TABLE ... USE_FRM helps in difficult cases. See:

  http://dev.mysql.com/doc/mysql/en/repair-table.html











Per Jessen <[EMAIL PROTECTED]> wrote:

> All,

> I've got a table with about 25mill rows that was victim of a crash recently. 
> (power-failure).

> I've been trying to recover it, but I'm not making much progress. 

> 

>>From the most recent attempts:

> 

> myisamchk --safe-recover --force 

> - recovering (with keycache) MyISAM-table ''

> Data records: 21622679

> 4988000

> 7256000

> 10627000

> myisamchk: error: 126 for record at pos 1589881104

> MyISAM-table '' is not fixed because of errors

> 

> myisamchk -r --force --tmpdir=/data2/tmp 

> - recovering (with sort) MyISAM-table ''

> Data records: 12876899

> - Fixing index 1

> - Fixing index 2

> - Fixing index 3

> Key 3 - Found wrong stored record at 0

> MyISAM-table '' is not fixed because of errors

> 

> 

> Where do I go from here?  I've got a backup of the table, but I'm not sure 
> what sort of state it

> is in.

> 

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: recovery question

[Per Jessen]

Per Jessen wrote:

> I've got a backup of the table, but I'm not sure what sort of state
> it is in.

Correction - no backup is available.  This table has got to be recoverable. 


-- 
/Per Jessen, Zürich


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: recovery of a very large table?

[jon]

Not a bad suggestion... but when I try it, I get the following output:
Checking MyISAM file: theTable
Data records: 22906970   Deleted blocks:   0
myisamchk: warning: Table is marked as crashed and last repair failed
- check file-size
myisamchk: error: Size of indexfile is: 2049552384Should be: 
19229444096- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
myisamchk: error: Can't read indexpage from filepos: -1
- check records and index references
myisamchk: error: Record at:  0  Can't find key for index:  1
MyISAM-table 'theTable' is corrupted
Fix it using switch "-r" or "-o"


Renato Golin wrote:
On Wednesday 06 April 2005 20:05, jon wrote:
Normal recovery seems to grab 490 rows... but, originally there were
some 22 million rows in there.

Seems your data file was corruped too not only the indexes. and probably broke 
when updating the 491st registry... try use myisamchk -e

  -e, --extend-check  Try to recover every possible row from the data file
  Normally this will also find a lot of garbage rows;
  Don't use this option if you are not totally desperate.
it could take a very long time to run also... be warned! ;)
--rengolin


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: recovery of a very large table?

[Renato Golin]

On Wednesday 06 April 2005 20:05, jon wrote:
> Normal recovery seems to grab 490 rows... but, originally there were
> some 22 million rows in there.

Seems your data file was corruped too not only the indexes. and probably broke 
when updating the 491st registry... try use myisamchk -e

  -e, --extend-check  Try to recover every possible row from the data file
  Normally this will also find a lot of garbage rows;
  Don't use this option if you are not totally desperate.

it could take a very long time to run also... be warned! ;)

--rengolin


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Recovery in MySql

[Inbal Ovadia]

Hi
My tables are of type MYISAM.
What exactly REPAIR does?
Is this enough? If my database in not consistent is this command will help?

Thanks, Inbal

-Original Message-
From: R. Hannes Niedner [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 29, 2003 10:10 PM
To: Inbal Ovadia; MySQL Mailinglist
Subject: Re: Recovery in MySql


On 1/29/03 5:13 AM, "Inbal Ovadia" <[EMAIL PROTECTED]> wrote:

> Hi All,
> I have MySql on Windows.
> Today i had an electrical power interruption in the middle of working.
> The database remain not consistent and i could not continue working with
it.
> 
> Is there any Recovery after crash mechanism in mySql?
> Thanks, Inbal

If you tables are of type MYISAM (find out with SHOW CREATE TABLE table)
then most of the answers are here (myisamchk):
<http://www.mysql.com/doc/en/Table_maintenance.html>

Hth/h

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Recovery in MySql

[R. Hannes Niedner]

On 1/29/03 5:13 AM, "Inbal Ovadia" <[EMAIL PROTECTED]> wrote:

> Hi All,
> I have MySql on Windows.
> Today i had an electrical power interruption in the middle of working.
> The database remain not consistent and i could not continue working with it.
> 
> Is there any Recovery after crash mechanism in mySql?
> Thanks, Inbal

If you tables are of type MYISAM (find out with SHOW CREATE TABLE table)
then most of the answers are here (myisamchk):


Hth/h


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Recovery in MySql

[Andrey V. Ignatov]

Hello Inbal,

Wednesday, January 29, 2003, 4:13:24 PM, you wrote:

IO> Hi All,
IO> I have MySql on Windows.
IO> Today i had an electrical power interruption in the middle of working.
IO> The database remain not consistent and i could not continue working with it.

IO> Is there any Recovery after crash mechanism in mySql?
IO> Thanks, Inbal

http://www.mysql.com/doc/en/Disaster_Prevention.html

-- 
Best regards,
 Andreymailto:[EMAIL PROTECTED]



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Recovery with binary logs.

[Manuel Villasante]

Hi Martin,

Thank you very much. My system is Linux based so your answers are exactly
what I need. One more question though: when you use the --record_log_pos
option in mysqlhotcopy, where is that position recorded? In the logging
output of the program or somewhere else?

Thanks again,

Manuel

-Original Message-
From: Martin Waite [mailto:[EMAIL PROTECTED]]
Sent: Thursday, November 28, 2002 3:19 AM
To: Manuel Villasante
Cc: '[EMAIL PROTECTED]'
Subject: Re: Recovery with binary logs.


Hi Manuel,

On Tue, 2002-11-26 at 21:38, Manuel Villasante wrote:
> Hi,
> 
> I have a few questions regarding recovery of a database using binary logs.
> 
> 
> 1) If you have a set of binary logs in your directory mylog-bin.001 to
> mylog-bin.nmp, is there an easy way to find out which logs you need to run
> since the last backup? In other words, when according to the instructions,
> after replacing the database files with the backup ones, you run the
> command:
> 
>   mysqlbinlog mylog-bin.[0-9]* | mysql
> 
> does it know automatically which set to include so as to not incorporate
> logs that are too old? Or do we have to manually perform a selection? If
so,
> is there a way to figure out easily the subset if one has not been
watching
> it?
> 

At the time of backup, you need to record the master position of the
server you are backing up - or perform "reset master", but this might 
threaten your recovery if you have a failure during the current backup 
process and have to roll-forward from your previous dump (requiring the
binary logs that reset master have just deleted).  

If you record the master position (file name, offset) during your dump,
you need to ensure all tables involved in the dump are locked. 
mysqlhotcopy can do all this for you, see the --record_log_pos
option.  Unfortunately, mysqlhotcopy only works on Unix-like OSes,
and so you will need to roll your own if your OS is not supported.

> 2) If a loss of data has been caused by an unwanted statement like DROP
> DATABASE... or DROP TABLE VeryImportantOne, how can one delete that
> statement from the bin-log before using it for recovery and repeat the
> mistake?
> 

You could write the output of mysqlbinlog to file, edit the file, and
then pipe the file into the mysql monitor:

mysqlbinlog mylog-bin.[0-9]* > file.sql
edit file.sql
mysql < file.sql

good luck,

Martin


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Recovery with binary logs.

[Martin Waite]

Hi Manuel,

On Tue, 2002-11-26 at 21:38, Manuel Villasante wrote:
> Hi,
> 
> I have a few questions regarding recovery of a database using binary logs.
> 
> 
> 1) If you have a set of binary logs in your directory mylog-bin.001 to
> mylog-bin.nmp, is there an easy way to find out which logs you need to run
> since the last backup? In other words, when according to the instructions,
> after replacing the database files with the backup ones, you run the
> command:
> 
>   mysqlbinlog mylog-bin.[0-9]* | mysql
> 
> does it know automatically which set to include so as to not incorporate
> logs that are too old? Or do we have to manually perform a selection? If so,
> is there a way to figure out easily the subset if one has not been watching
> it?
> 

At the time of backup, you need to record the master position of the
server you are backing up - or perform "reset master", but this might 
threaten your recovery if you have a failure during the current backup 
process and have to roll-forward from your previous dump (requiring the
binary logs that reset master have just deleted).  

If you record the master position (file name, offset) during your dump,
you need to ensure all tables involved in the dump are locked. 
mysqlhotcopy can do all this for you, see the --record_log_pos
option.  Unfortunately, mysqlhotcopy only works on Unix-like OSes,
and so you will need to roll your own if your OS is not supported.

> 2) If a loss of data has been caused by an unwanted statement like DROP
> DATABASE... or DROP TABLE VeryImportantOne, how can one delete that
> statement from the bin-log before using it for recovery and repeat the
> mistake?
> 

You could write the output of mysqlbinlog to file, edit the file, and
then pipe the file into the mysql monitor:

mysqlbinlog mylog-bin.[0-9]* > file.sql
edit file.sql
mysql < file.sql

good luck,

Martin



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

re: Recovery

[Egor Egorov]

Massimo,
Friday, November 22, 2002, 2:59:38 PM, you wrote:

MB> I'd want to backup my database. Which is the best method between mysqldump and 
copying the /usr/local/var directory?

For InnoDB tables take a look at InnoDB Hot Backup software (non-free). It is a must 
to have on a production box. We, at ensita.net, enjoy using it: 
http://www.innodb.com/hotbackup.html

For both MyISAM and InnoDB tables mysqldump is a nice solution, too. 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Recovery

[Dyego Souza do Carmo]

Dobrý den,
sexta-feira, 22 de novembro de 2002, 10:59:38, napsal jste:

MB> I'd want to backup my database. Which is the best method between mysqldump and 
copying the /usr/local/var directory?

MB> Thanks, Massimo.

 you use InnoDB ? the best and fast metod to backup your InnoDB
 databases is using the non-free Hotbackup tool ( see
 http://www.innodb.com/hotbackup.html ) and after this copy the .frm
 files from mysql data dir , else you must use mysqldump.


 you use MyISAM ? use mysqldump or copy the /usr/local/var.
 if you running on windows , you must to execute a "flush tables"
 before start copy of .MYD , .MYI and .frm files.


 bye,

-
  ++  Dyego Souza do Carmo   ++   Dep. Desenvolvimento   
-
 E S C R I B A   I N F O R M A T I C A
-
The only stupid question is the unasked one (somewhere in Linux's HowTo)
Linux registred user : #230601
-- 
$ look into "my eyes"
look: cannot open my eyes
-
   Reply: [EMAIL PROTECTED]



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Recovery Problem

[Simon Green]

I have been told that once deleted you can not get the data back (can some
one correct me)...
Have you got all your update logs or binary logs? If you have you should be
able to run the sql quires form there and so just recreate your data!
This mite be stating the obvious but like any data base they has humane
users backups are needed!

I hope this help?

Simon

-Original Message-
From: Paul Kelly [mailto:[EMAIL PROTECTED]]
Sent: 25 June 2001 17:16
To: '[EMAIL PROTECTED]'
Subject: Recovery Problem


Hi All,
sorry if this is duplicated...

I'm trying to recover data from a table after all rows in the table were
accidentally deleted.

I have looked at the .MYD file and all of the information still seems to be
there, albeit, flagged as deleted (I assume).

Is there any way to recover this information, and "undelete" it? and if so,
How?

I don't fancy the idea of having to type everything in again.

Any help would be much appreciated.

rgds,
Paul Kelly.


Software Architect
TERMINAL FOUR Software Ltd.
1, Herbert Street,
Dublin 2,
Ireland.
T: +353 1 2403110
F: +353 1 6615504


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: recovery help

[Steve Ruby]

Elekes Attila wrote:
> 
> Steve Ruby wrote:
> 
> > Elekes Attila wrote:
> > >
> > > Hi!
> > >
> > > The mysql server (version 3.22.32-4) has hanged up, and the restarting
> > > causes a part of the
> > > *.ISM and *.ISD files are gone. Unfortuanetly, I have not got a backup
> > > from these files :(
> > > Is there any method to recover the mysql tables from the remaining
> > > *.MYD, *MYI and *.frm
> > > files ? Thanks for ANY help!!!
> > >
> > > Attila
> >


I may have confused things here.. sorry... If you have version 3.22.32
you Cannot use the MYI and MYD files those are the MyISAM tables, the
ISM and ISD files are ISAM tables which were the starndard format for
pre 3.23 versions...

If you are running 3.22.32 how did you get MYI and MYD files? You should
have only one or the other depending on the type fo the tables and
with 3.22.x you must have only the ISM/ISD files.

Can you explain what happend?  If you converted the tables from ISAM
to MyISAM format the MYD .IS* filew will go away, but you will be
required to use 3.23.x version with the .MY* files

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: recovery help

[Elekes Attila]

Steve Ruby wrote:

> Elekes Attila wrote:
> >
> > Hi!
> >
> > The mysql server (version 3.22.32-4) has hanged up, and the restarting
> > causes a part of the
> > *.ISM and *.ISD files are gone. Unfortuanetly, I have not got a backup
> > from these files :(
> > Is there any method to recover the mysql tables from the remaining
> > *.MYD, *MYI and *.frm
> > files ? Thanks for ANY help!!!
> >
> > Attila
>
> First you should backup what you do have left of your files. There just
> incase you mess things up worse trying to fix them.
>
> Then, on a copy of the "bad" tables have you tried running
> myisamchk -r .MYI
>
> or atleast
> myisamchk .MYI
> so you can tell how bad off they are, mysql may simply be able to fix
> them itself.  See also CHECK TABLE and REPAIR TABLE in the mysql manual
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Hello Steve!

First, thank you very much for your help. I tried to run the myisamchk, the
tables looks good
and the result is e. g.:

# myisamchk -r TurosHat.MYI
Checking MyISAM file: CikktorzsTaki.MYI
Data records:  17   Deleted blocks:   0
- check file-size
- check key delete-chain
- check record delete-chain
- check index reference
- check records and index references

After running the myisamchk, I tried to check and repair the table:

mysql> check table CikktorzsTaki;
ERROR 1064: You have anmysql> check table CikktorzsTaki;
mysql> repair table CikktorzsTaki;
ERROR 1064: You have an error in your SQL syntax near 'check table CikktorzsTaki'
at line 1

Have you got any idea ?

Attila



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: recovery help

[Steve Ruby]

Elekes Attila wrote:
> 
> Hi!
> 
> The mysql server (version 3.22.32-4) has hanged up, and the restarting
> causes a part of the
> *.ISM and *.ISD files are gone. Unfortuanetly, I have not got a backup
> from these files :(
> Is there any method to recover the mysql tables from the remaining
> *.MYD, *MYI and *.frm
> files ? Thanks for ANY help!!!
> 
> Attila


First you should backup what you do have left of your files. There just
incase you mess things up worse trying to fix them.

Then, on a copy of the "bad" tables have you tried running
myisamchk -r .MYI

or atleast
myisamchk .MYI
so you can tell how bad off they are, mysql may simply be able to fix
them itself.  See also CHECK TABLE and REPAIR TABLE in the mysql manual

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php