best third party backup/restore software for mysql

[Ananda Kumar]

Hi All,
Can you please suggest any good third party software for backup and restore
of mysql db.

Right now i am looking at ZMANDA. Has any one worked on this, and used for
their production db, please let me know how it is and can we use the same
for our production 24/7 mysql db.

regards
anandkl

Re: After backup/restore view turns to table

[Davor Dundovic]

Hi All !

I did "mysqldump mydb -uroot -ppass --databases --add-locks 
--allow-keywords --create-options --extended-insert --routines -r file.sql"


and after that in MySQL Command Line Client "source file.sql"

After that my view was converted to an empty table.

What should I do to backup and restore my view correctly ?



Regards, Dundo.




Nobody knows how to backup and then restore views ?

MySQL version is 5.0.27, db type is InnoDB.


Regards, Dundo.



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

After backup/restore view turns to table

[Davor Dundovic]

Hi All !

I did "mysqldump mydb -uroot -ppass --databases --add-locks 
--allow-keywords --create-options --extended-insert --routines -r file.sql"


and after that in MySQL Command Line Client "source file.sql"

After that my view was converted to an empty table.

What should I do to backup and restore my view correctly ?



Regards, Dundo.



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

Re: Unknown problem with backup restore

[SGreen]

Gleb Paharenko <[EMAIL PROTECTED]> wrote on 01/18/2006 11:02:15 
AM:

> Hello.
> 
> > MyISAM table of about 1.8 GB it stops restoring with no error output.
> 
> It looks strange. Get the debug binary of mysql command line tool
> and create a trace file to find out the place where if fails. See:
>   http://dev.mysql.com/doc/refman/5.0/en/debugging-client.html
> 
> 
> Jose Maria de Dios wrote:
> > I am trying to restore a backup on a two processor machine with a 
Debian
> > installation with 2.4 kernel and 2GB or RAM, but when it reaches a
> > MyISAM table of about 1.8 GB it stops restoring with no error output.
> > The MySQL version is Distrib 5.0.16.
> > I have tried to restore it in many other systems (unstable Debian,
> > Ubuntu, Windows 2000) and all of them worked fine. 
> > Trying to figure out if the table had problems, I used myisamchk and 
it
> > returned OK.
> > I tried to backup and restore just this table and it restored fine.
> > I have tried almost everything, but the only way it worked is by
> > restoring the table independently. It is not an acceptable way for
> > backing up the database because it will grow up to a size in which it
> > could be very hard to backup.
> > The backup was created with "mysqldump --opt ..." and I try to restore
> > it with "mysql -u root -pPwd DB < file.sql".
> > 
> > Any ideas???
> > 
> 
> 
> -- 
> 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
> 


One other thing to consider is that the max_allowed_packet size of 
mysqldump may be bigger than what your server can accept. That means that 
the extended insert commands generated by MySQL for larger tables may be 
too large for your server to handle. If an incoming command (one packet)is 
too large (which can happen with some extended INSERTs), your server will 
just dump the connection assuming (usually correctly) that something bad 
is going on.

Next time try the --max_allowed_packet=  option with your mysqldump 
command and make sure you provide a number equal to or less than the value 
you see if you ask your destination server

SHOW VARIABLES LIKE 'max_allowed_packet';

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Unknown problem with backup restore

[Gleb Paharenko]

Hello.

> MyISAM table of about 1.8 GB it stops restoring with no error output.

It looks strange. Get the debug binary of mysql command line tool
and create a trace file to find out the place where if fails. See:
  http://dev.mysql.com/doc/refman/5.0/en/debugging-client.html


Jose Maria de Dios wrote:
> I am trying to restore a backup on a two processor machine with a Debian
> installation with 2.4 kernel and 2GB or RAM, but when it reaches a
> MyISAM table of about 1.8 GB it stops restoring with no error output.
> The MySQL version is Distrib 5.0.16.
> I have tried to restore it in many other systems (unstable Debian,
> Ubuntu, Windows 2000) and all of them worked fine. 
> Trying to figure out if the table had problems, I used myisamchk and it
> returned OK.
> I tried to backup and restore just this table and it restored fine.
> I have tried almost everything, but the only way it worked is by
> restoring the table independently. It is not an acceptable way for
> backing up the database because it will grow up to a size in which it
> could be very hard to backup.
> The backup was created with "mysqldump --opt ..." and I try to restore
> it with "mysql -u root -pPwd DB < file.sql".
> 
> Any ideas???
> 


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

Unknown problem with backup restore

[Jose Maria de Dios]

I am trying to restore a backup on a two processor machine with a Debian
installation with 2.4 kernel and 2GB or RAM, but when it reaches a
MyISAM table of about 1.8 GB it stops restoring with no error output.
The MySQL version is Distrib 5.0.16.
I have tried to restore it in many other systems (unstable Debian,
Ubuntu, Windows 2000) and all of them worked fine. 
Trying to figure out if the table had problems, I used myisamchk and it
returned OK.
I tried to backup and restore just this table and it restored fine.
I have tried almost everything, but the only way it worked is by
restoring the table independently. It is not an acceptable way for
backing up the database because it will grow up to a size in which it
could be very hard to backup.
The backup was created with "mysqldump --opt ..." and I try to restore
it with "mysql -u root -pPwd DB < file.sql".

Any ideas???


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

Re: Backup / Restore database with foreign keys

[Daniel Kasak]

Michael Stassen wrote:


Before loading the file,

  SET FOREIGN_KEY_CHECKS = 0;

after loading the file,

  SET FOREIGN_KEY_CHECKS = 1;



That's it! Thanks :)

Even better, upgrade to a newer mysql (4.1.1+), where they are 
automatically added to the dump file for you.


Not until the client libraries are ready. I don't feel 'right' about 
hacking up the place with --old-password options and such. Also, my 
Gentoo server ( stable branch ) insists that 4.0.x is the latest that I 
can expect to install without breaking things. After doing some testing 
on my workstation, I tend to agree - getting everything compiled against 
4.1.x is a major pain, and certainly not something I'm about to do at 
the moment with no real advantages on offer - I'm just likely to break 
something and be very sorry. Once all the questions about:



Client does not support authentication protocol requested by server; consider 
upgrading MySQL client


have died down, *then* it's time to upgrade the server.

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au

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

Re: Backup / Restore database with foreign keys

[Matthew Lenz]

i think you can use -K on your mysqldump and it'll put the hints in there 
for the mysql command to use as well


- Original Message - 
From: "Daniel Kasak" <[EMAIL PROTECTED]>

To: 
Sent: Thursday, September 29, 2005 7:45 PM
Subject: Backup / Restore database with foreign keys



Greetings.

I've just hit an interesting problem. Luckily I don't actually *need* to 
restore from a backup right now - I'm just trying to create a database 
dump to submit an unrelated bug report.


Anyway ...

I'm using the command:

mysqldump -K DATABASE_NAME > db.sql -p

However when I create a new database and try to load the dump file:

mysql NEW_DATABASE < db.sql -p

I get an error when I hit an InnoDB table that has a relationship set up 
with a table that hasn't yet been created. How do I get around this?


--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au

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




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

Re: Backup / Restore database with foreign keys

[Michael Stassen]

Daniel Kasak wrote:

Greetings.

I've just hit an interesting problem. Luckily I don't actually *need* to 
restore from a backup right now - I'm just trying to create a database 
dump to submit an unrelated bug report.


Anyway ...

I'm using the command:

mysqldump -K DATABASE_NAME > db.sql -p

However when I create a new database and try to load the dump file:

mysql NEW_DATABASE < db.sql -p

I get an error when I hit an InnoDB table that has a relationship set up 
with a table that hasn't yet been created. How do I get around this?




Before loading the file,

  SET FOREIGN_KEY_CHECKS = 0;

after loading the file,

  SET FOREIGN_KEY_CHECKS = 1;


Better yet, edit the dump file to place those as the first line and last 
lines, respectively.  Even better, upgrade to a newer mysql (4.1.1+), where 
they are automatically added to the dump file for you.


See the manual for more 
 (way 
down at the end).


Michael
Michael

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

Backup / Restore database with foreign keys

[Daniel Kasak]

Greetings.

I've just hit an interesting problem. Luckily I don't actually *need* to 
restore from a backup right now - I'm just trying to create a database 
dump to submit an unrelated bug report.


Anyway ...

I'm using the command:

mysqldump -K DATABASE_NAME > db.sql -p

However when I create a new database and try to load the dump file:

mysql NEW_DATABASE < db.sql -p

I get an error when I hit an InnoDB table that has a relationship set up 
with a table that hasn't yet been created. How do I get around this?


--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au

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

RE: backup/restore

[Anil Doppalapudi]

Hi,

use the below command to take backup

 mysqldump --add-drop-table > 

i tested it on my local server also it is working fine for me. other wise
just open the dump file by executing the below command

on linux:

  head -100 

o/p:

-- MySQL dump 8.22
--
-- Host: localhostDatabase: NOCINFO
-
-- Server version   4.0.18-standard

--
-- Table structure for table 'IPInfo'
--

DROP TABLE IF EXISTS IPInfo;

CREATE TABLE IPInfo (
  IPAddr varchar(12) NOT NULL default '',
  PingStatus varchar(10) NOT NULL default '',
  ServerName varchar(25) NOT NULL default '',
  ReverseLook varchar(100) NOT NULL default '',
  ForwardLook varchar(12) default NULL,
  Remarks varchar(200) default NULL,
  SerId varchar(10) default NULL,
  PRIMARY KEY  (IPAddr)
) TYPE=MyISAM MAX_ROWS=1000;


check the above you will find drop table command in dumpfile.


Thanks
Anil


--



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Monday, January 03, 2005 9:50 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com; [EMAIL PROTECTED]
Subject: RE: backup/restore



Hi,
 Thank you for your reply. Even though I used the --add-drop-table
option, I am getting the error messages. Actually I am taking the backup
for a group of tables as following


D:\Code\OPS\6.9.1.12\NetMgmt\OPS_src\C_src\backupmnms\Debug>mysqldump
-uMNMSDBA -p
MNMSDBA  --databases mnms --add-drop-table --add-locks --disable-keys -f

--tables ALARM ALARM_CATEGORY ALARM_REPORT ALARM_REPORT_CATEGORY
ALARMINFO  >  c:\progra~1\mitel\opsman~1\temp\almhist.dmp


And doing the restoring as

D:\Code\OPS\6.9.1.12\NetMgmt\OPS_src\C_src\backupmnms\Debug>mysql
-uMNMSDBA -pMNMS
DBA  -f mnms  <  c:\progra~1\mitel\opsman~1\temp\almhist.dmp
ERROR 1051 at line 11: Unknown table 'alarm'// Trying to drop alarm
table
ERROR 1050 at line 12: Table 'alarm' already exists //Creating alarm
ERROR 1051 at line 72: Unknown table 'alarm_report' //Drop
alarm_category
ERROR 1050 at line 73: Table 'alarm_report' already exists//Create
ERROR 1062 at line 94: Duplicate entry '1' for key 1 // Insert
ERROR 1062 at line 95: Duplicate entry '4' for key 1 // Insert
ERROR 1062 at line 96: Duplicate entry '5' for key 1  // Insert
ERROR 1062 at line 97: Duplicate entry '8' for key 1 // Insert
ERROR 1062 at line 98: Duplicate entry '10' for key 1 // Insert
ERROR 1062 at line 99: Duplicate entry '11' for key 1 // Insert
ERROR 1062 at line 100: Duplicate entry '13' for key 1 // Insert
ERROR 1062 at line 101: Duplicate entry '15' for key 1 // Insert
ERROR 1062 at line 102: Duplicate entry '16' for key 1 // Insert
ERROR 1062 at line 103: Duplicate entry '17' for key 1 // Insert

Note : ALARM, ALARM_CATEGORY, ALARM_REPORT, ALARM_REPORT_CATEGORY,
ALARMINFO   all belongs to one group.

Alarm and Alarm_category are parent and child tables.

ALARM_REPORT, ALARM_REPORT_CATEGORY are parent and child tables



Please advise me, how to get rid of the above error messages.



Thanks,
Narasimha
-Original Message-
From: Anil Doppalapudi [mailto:[EMAIL PROTECTED]
Sent: Monday, January 03, 2005 7:03 PM
To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS);
[EMAIL PROTECTED]
Cc: mysql@lists.mysql.com; [EMAIL PROTECTED]
Subject: RE: backup/restore

Hi,

with which options of mysqldump you have taken backup. if you
use --add-drop-table option then it will add drop table statement in
dump
file. otherwise it wont add that statement and you will get that type of
errors.

if you didn't use that option then drop the schema and then try to
restore
it from backup file.


Thanks
Anil




-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Monday, January 03, 2005 12:58 PM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com; [EMAIL PROTECTED]
Subject: RE: backup/restore



Hi,
 Thank you for your reply. If I have the create table info in my
dump file, while doing the restore using mysql dbname < dump.dmp, I am
getting errors like

mysql  -uMNMSDBA -pMNMSDBA  -f mnms  <
c:\progra~1\mitel\opsman~1\temp\almhist.dmp

ERROR 1050 at line 11: Table 'alarm' already exists
ERROR 1050 at line 40: Table 'alarm_category' already exists
ERROR 1050 at line 70: Table 'alarm_report' already exists
ERROR 1062 at line 91: Duplicate entry '1' for key 1
ERROR 1062 at line 92: Duplicate entry '4' for key 1
ERROR 1062 at line 93: Duplicate entry '5' for key 1
ERROR 1062 at line 94: Duplicate entry '8' for key 1
ERROR 1062 at line 95: Duplicate entry '10' for key 1
ERROR 1062 at line 96: Duplicate entry '11' for key 1
ERROR 1062 at line 97: Duplicate entry '13' for key 1
ERROR 1062 at line 98: Dupl

RE: backup/restore

[Tom Crimmins]

Drop the tables manually then try the restore.

[snip]
ERROR 1051 at line 11: Unknown table 'alarm'
ERROR 1050 at line 12: Table 'alarm' already exists
[/snip]

It seems strange that you get these errors in this order. If you post maybe
the first 15 lines of your dump file, I may be able to provide more help.

---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa

-Original Message-

Hi,
 Thank you for your reply. Even though I used the --add-drop-table
option, I am getting the error messages. Actually I am taking the backup
for a group of tables as following


D:\Code\OPS\6.9.1.12\NetMgmt\OPS_src\C_src\backupmnms\Debug>mysqldump
-uMNMSDBA -p
MNMSDBA  --databases mnms --add-drop-table --add-locks --disable-keys -f

--tables ALARM ALARM_CATEGORY ALARM_REPORT ALARM_REPORT_CATEGORY
ALARMINFO  >  c:\progra~1\mitel\opsman~1\temp\almhist.dmp


And doing the restoring as


D:\Code\OPS\6.9.1.12\NetMgmt\OPS_src\C_src\backupmnms\Debug>mysql
-uMNMSDBA -pMNMS
DBA  -f mnms  <  c:\progra~1\mitel\opsman~1\temp\almhist.dmp
ERROR 1051 at line 11: Unknown table 'alarm'// Trying to drop alarm
table
ERROR 1050 at line 12: Table 'alarm' already exists //Creating alarm
ERROR 1051 at line 72: Unknown table 'alarm_report' //Drop
alarm_category
ERROR 1050 at line 73: Table 'alarm_report' already exists//Create
ERROR 1062 at line 94: Duplicate entry '1' for key 1 // Insert
ERROR 1062 at line 95: Duplicate entry '4' for key 1 // Insert
ERROR 1062 at line 96: Duplicate entry '5' for key 1  // Insert
ERROR 1062 at line 97: Duplicate entry '8' for key 1 // Insert
ERROR 1062 at line 98: Duplicate entry '10' for key 1 // Insert
ERROR 1062 at line 99: Duplicate entry '11' for key 1 // Insert
ERROR 1062 at line 100: Duplicate entry '13' for key 1 // Insert
ERROR 1062 at line 101: Duplicate entry '15' for key 1 // Insert
ERROR 1062 at line 102: Duplicate entry '16' for key 1 // Insert
ERROR 1062 at line 103: Duplicate entry '17' for key 1 // Insert

Note : ALARM, ALARM_CATEGORY, ALARM_REPORT, ALARM_REPORT_CATEGORY,
ALARMINFO   all belongs to one group.

Alarm and Alarm_category are parent and child tables.

ALARM_REPORT, ALARM_REPORT_CATEGORY are parent and child tables



Please advise me, how to get rid of the above error messages.



Thanks,
Narasimha
-Original Message-

Hi,

with which options of mysqldump you have taken backup. if you
use --add-drop-table option then it will add drop table statement in
dump
file. otherwise it wont add that statement and you will get that type of
errors.

if you didn't use that option then drop the schema and then try to
restore
it from backup file.


Thanks
Anil




-Original Message-

Hi,
 Thank you for your reply. If I have the create table info in my
dump file, while doing the restore using mysql dbname < dump.dmp, I am
getting errors like

mysql  -uMNMSDBA -pMNMSDBA  -f mnms  <
c:\progra~1\mitel\opsman~1\temp\almhist.dmp

ERROR 1050 at line 11: Table 'alarm' already exists
ERROR 1050 at line 40: Table 'alarm_category' already exists
ERROR 1050 at line 70: Table 'alarm_report' already exists
ERROR 1062 at line 91: Duplicate entry '1' for key 1
ERROR 1062 at line 92: Duplicate entry '4' for key 1
ERROR 1062 at line 93: Duplicate entry '5' for key 1
ERROR 1062 at line 94: Duplicate entry '8' for key 1
ERROR 1062 at line 95: Duplicate entry '10' for key 1
ERROR 1062 at line 96: Duplicate entry '11' for key 1
ERROR 1062 at line 97: Duplicate entry '13' for key 1
ERROR 1062 at line 98: Duplicate entry '15' for key 1
ERROR 1062 at line 99: Duplicate entry '16' for key 1
ERROR 1062 at line 100: Duplicate entry '17' for key 1
ERROR 1050 at line 108: Table 'alarm_report_category' already exists
ERROR 1050 at line 137: Table 'alarminfo' already exists
ERROR 1062 at line 155: Duplicate entry '56' for key 1
ERROR 1062 at line 156: Duplicate entry '57' for key 1
ERROR 1062 at line 157: Duplicate entry '58' for key 1
ERROR 1062 at line 158: Duplicate entry '59' for key 1
ERROR 1062 at line 159: Duplicate entry '75' for key 1
ERROR 1062 at line 160: Duplicate entry '76' for key 1
ERROR 1062 at line 161: Duplicate entry '77' for key 1


Please advise me, how to suppress the above error messages.

While taking backup used

mysqldump  -uMNMSDBA -pMNMSDBA  --databases mnms --add-locks
--disable-keys -f --tables ALARM ALARM_CATEGORY ALARM_REPORT
ALARM_REPORT_CATEGORY ALARMINFO   >
c:\progra~1\mitel\opsman~1\temp\almhist.dmp


Please help me in this.

Thanks,
Narasimha



-Original Message-
From: Tom Crimmins [mailto:[EMAIL PROTECTED]
Sent: Monday, January 03, 2005 10:

RE: backup/restore

[lakshmi.narasimharao]

Hi,
 Thank you for your reply. Even though I used the --add-drop-table
option, I am getting the error messages. Actually I am taking the backup
for a group of tables as following


D:\Code\OPS\6.9.1.12\NetMgmt\OPS_src\C_src\backupmnms\Debug>mysqldump
-uMNMSDBA -p
MNMSDBA  --databases mnms --add-drop-table --add-locks --disable-keys -f

--tables ALARM ALARM_CATEGORY ALARM_REPORT ALARM_REPORT_CATEGORY
ALARMINFO  >  c:\progra~1\mitel\opsman~1\temp\almhist.dmp


And doing the restoring as

D:\Code\OPS\6.9.1.12\NetMgmt\OPS_src\C_src\backupmnms\Debug>mysql
-uMNMSDBA -pMNMS
DBA  -f mnms  <  c:\progra~1\mitel\opsman~1\temp\almhist.dmp
ERROR 1051 at line 11: Unknown table 'alarm'// Trying to drop alarm
table
ERROR 1050 at line 12: Table 'alarm' already exists //Creating alarm
ERROR 1051 at line 72: Unknown table 'alarm_report' //Drop
alarm_category
ERROR 1050 at line 73: Table 'alarm_report' already exists//Create
ERROR 1062 at line 94: Duplicate entry '1' for key 1 // Insert
ERROR 1062 at line 95: Duplicate entry '4' for key 1 // Insert
ERROR 1062 at line 96: Duplicate entry '5' for key 1  // Insert
ERROR 1062 at line 97: Duplicate entry '8' for key 1 // Insert
ERROR 1062 at line 98: Duplicate entry '10' for key 1 // Insert
ERROR 1062 at line 99: Duplicate entry '11' for key 1 // Insert
ERROR 1062 at line 100: Duplicate entry '13' for key 1 // Insert
ERROR 1062 at line 101: Duplicate entry '15' for key 1 // Insert
ERROR 1062 at line 102: Duplicate entry '16' for key 1 // Insert
ERROR 1062 at line 103: Duplicate entry '17' for key 1 // Insert

Note : ALARM, ALARM_CATEGORY, ALARM_REPORT, ALARM_REPORT_CATEGORY,
ALARMINFO   all belongs to one group.

Alarm and Alarm_category are parent and child tables.

ALARM_REPORT, ALARM_REPORT_CATEGORY are parent and child tables



Please advise me, how to get rid of the above error messages.



Thanks,
Narasimha
-Original Message-
From: Anil Doppalapudi [mailto:[EMAIL PROTECTED]
Sent: Monday, January 03, 2005 7:03 PM
To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS);
[EMAIL PROTECTED]
Cc: mysql@lists.mysql.com; [EMAIL PROTECTED]
Subject: RE: backup/restore

Hi,

with which options of mysqldump you have taken backup. if you
use --add-drop-table option then it will add drop table statement in
dump
file. otherwise it wont add that statement and you will get that type of
errors.

if you didn't use that option then drop the schema and then try to
restore
it from backup file.


Thanks
Anil




-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Monday, January 03, 2005 12:58 PM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com; [EMAIL PROTECTED]
Subject: RE: backup/restore



Hi,
 Thank you for your reply. If I have the create table info in my
dump file, while doing the restore using mysql dbname < dump.dmp, I am
getting errors like

mysql  -uMNMSDBA -pMNMSDBA  -f mnms  <
c:\progra~1\mitel\opsman~1\temp\almhist.dmp

ERROR 1050 at line 11: Table 'alarm' already exists
ERROR 1050 at line 40: Table 'alarm_category' already exists
ERROR 1050 at line 70: Table 'alarm_report' already exists
ERROR 1062 at line 91: Duplicate entry '1' for key 1
ERROR 1062 at line 92: Duplicate entry '4' for key 1
ERROR 1062 at line 93: Duplicate entry '5' for key 1
ERROR 1062 at line 94: Duplicate entry '8' for key 1
ERROR 1062 at line 95: Duplicate entry '10' for key 1
ERROR 1062 at line 96: Duplicate entry '11' for key 1
ERROR 1062 at line 97: Duplicate entry '13' for key 1
ERROR 1062 at line 98: Duplicate entry '15' for key 1
ERROR 1062 at line 99: Duplicate entry '16' for key 1
ERROR 1062 at line 100: Duplicate entry '17' for key 1
ERROR 1050 at line 108: Table 'alarm_report_category' already exists
ERROR 1050 at line 137: Table 'alarminfo' already exists
ERROR 1062 at line 155: Duplicate entry '56' for key 1
ERROR 1062 at line 156: Duplicate entry '57' for key 1
ERROR 1062 at line 157: Duplicate entry '58' for key 1
ERROR 1062 at line 158: Duplicate entry '59' for key 1
ERROR 1062 at line 159: Duplicate entry '75' for key 1
ERROR 1062 at line 160: Duplicate entry '76' for key 1
ERROR 1062 at line 161: Duplicate entry '77' for key 1


Please advise me, how to suppress the above error messages.

While taking backup used

mysqldump  -uMNMSDBA -pMNMSDBA  --databases mnms --add-locks
--disable-keys -f --tables ALARM ALARM_CATEGORY ALARM_REPORT
ALARM_REPORT_CATEGORY ALARMINFO   >
c:\progra~1\mitel\opsman~1\temp\almhist.dmp


Please help me in this.

Thanks,
Narasimha



-Original Message-
From: Tom Crimmins [mailto:[EMAIL PROTECTED]
Sent: Monday, January 03, 2005 10:55 A

RE: backup/restore

[Anil Doppalapudi]

Hi,

with which options of mysqldump you have taken backup. if you
use --add-drop-table option then it will add drop table statement in dump
file. otherwise it wont add that statement and you will get that type of
errors.

if you didn't use that option then drop the schema and then try to restore
it from backup file.


Thanks
Anil




-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Monday, January 03, 2005 12:58 PM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com; [EMAIL PROTECTED]
Subject: RE: backup/restore



Hi,
 Thank you for your reply. If I have the create table info in my
dump file, while doing the restore using mysql dbname < dump.dmp, I am
getting errors like

mysql  -uMNMSDBA -pMNMSDBA  -f mnms  <
c:\progra~1\mitel\opsman~1\temp\almhist.dmp

ERROR 1050 at line 11: Table 'alarm' already exists
ERROR 1050 at line 40: Table 'alarm_category' already exists
ERROR 1050 at line 70: Table 'alarm_report' already exists
ERROR 1062 at line 91: Duplicate entry '1' for key 1
ERROR 1062 at line 92: Duplicate entry '4' for key 1
ERROR 1062 at line 93: Duplicate entry '5' for key 1
ERROR 1062 at line 94: Duplicate entry '8' for key 1
ERROR 1062 at line 95: Duplicate entry '10' for key 1
ERROR 1062 at line 96: Duplicate entry '11' for key 1
ERROR 1062 at line 97: Duplicate entry '13' for key 1
ERROR 1062 at line 98: Duplicate entry '15' for key 1
ERROR 1062 at line 99: Duplicate entry '16' for key 1
ERROR 1062 at line 100: Duplicate entry '17' for key 1
ERROR 1050 at line 108: Table 'alarm_report_category' already exists
ERROR 1050 at line 137: Table 'alarminfo' already exists
ERROR 1062 at line 155: Duplicate entry '56' for key 1
ERROR 1062 at line 156: Duplicate entry '57' for key 1
ERROR 1062 at line 157: Duplicate entry '58' for key 1
ERROR 1062 at line 158: Duplicate entry '59' for key 1
ERROR 1062 at line 159: Duplicate entry '75' for key 1
ERROR 1062 at line 160: Duplicate entry '76' for key 1
ERROR 1062 at line 161: Duplicate entry '77' for key 1


Please advise me, how to suppress the above error messages.

While taking backup used

mysqldump  -uMNMSDBA -pMNMSDBA  --databases mnms --add-locks
--disable-keys -f --tables ALARM ALARM_CATEGORY ALARM_REPORT
ALARM_REPORT_CATEGORY ALARMINFO   >
c:\progra~1\mitel\opsman~1\temp\almhist.dmp


Please help me in this.

Thanks,
Narasimha



-Original Message-
From: Tom Crimmins [mailto:[EMAIL PROTECTED]
Sent: Monday, January 03, 2005 10:55 AM
To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS)
Cc: mysql@lists.mysql.com
Subject: RE: backup/restore

[snip]
I am doing backup for tables using Mysqldump. But while doing the
restore I
am not able to do that using the same Mysqldump. Could you please help
me in
that.
[/snip]

mysqldump is not intended to be used for the restore.

You need to run the following:

mysql -D dbname < mysqldumpfile

You may have to specify a user and password as well, depending on your
setup.

---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa


-Original Message-
Hi,

   I am doing backup for tables using Mysqldump. But while doing the
restore I am not able to do that using the same Mysqldump. Could you
please
help me in that.




For backup : using   Mysqldump -databases  ---tables table1
table2 > dump.dmp

For restore : used  Mysqldump -databases  < dump.dmp




In the above, I am not able to restore the data.




Please help us for a good solution.




Thanks,

Narasimha





Confidentiality Notice


The information contained in this electronic message and any attachments
to
this message are intended for the exclusive use of the addressee(s) and
may
contain confidential or privileged information. If you are not the
intended
recipient, please notify the sender at Wipro or [EMAIL PROTECTED]
immediately and destroy all copies of this message and any attachments.

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




Confidentiality Notice

The information contained in this electronic message and any attachments to
this message are intended
for the exclusive use of the addressee(s) and may contain confidential or
privileged information. If
you are not the intended recipient, please notify the sender at Wipro or
[EMAIL PROTECTED] immediately
and destroy all copies of this message and any attachments.

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


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

Re: backup/restore

[Raj Shekhar]

Ligaya Turmelle wrote:
I'm a beginner - but can't you also use mysqlimport?
Not in the case when you have made a backup using mysqldump while using 
the default options.  mysqlimport is a front end  for LOAD DATA INFILE 
(http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html)

It is usefule when you have data in a flat file (for example rows 
seperated by newlines and columns seperated by comma or tabs).  If you 
need data in this format, you have to specify different options to 
mysqldump (more specifically -T option and  --fields-terminated-by=... 
--fields-enclosed-by=... --fields-optionally-enclosed-by=... 
--fields-escaped-by=... --lines-terminated-by=...  )

see http://dev.mysql.com/doc/mysql/en/mysqldump.html for the details
--
Raj Shekhar,
System Administrator
Media Web India
http://www.netphotograph.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: backup/restore

[Raj Shekhar]

[EMAIL PROTECTED] wrote:
Hi,
 Thank you for your reply. If I have the create table info in my
dump file, while doing the restore using mysql dbname < dump.dmp, I am
getting errors like
mysql  -uMNMSDBA -pMNMSDBA  -f mnms  <
c:\progra~1\mitel\opsman~1\temp\almhist.dmp
ERROR 1050 at line 11: Table 'alarm' already exists
ERROR 1050 at line 40: Table 'alarm_category' already exists
ERROR 1050 at line 70: Table 'alarm_report' already exists
[snip]
Please advise me, how to suppress the above error messages.
While taking backup used
mysqldump  -uMNMSDBA -pMNMSDBA  --databases mnms --add-locks
--disable-keys -f --tables ALARM ALARM_CATEGORY ALARM_REPORT
ALARM_REPORT_CATEGORY ALARMINFO   >
c:\progra~1\mitel\opsman~1\temp\almhist.dmp

have a look at the --add-drop-table
Add a DROP TABLE statement before each CREATE TABLE statement.
this should remove the errors that you are getting.

Confidentiality Notice
The information contained in this electronic message and any attachments to 
this message are intended
for the exclusive use of the addressee(s) and may contain confidential or 
privileged information. If
you are not the intended recipient, please notify the sender at Wipro or [EMAIL 
PROTECTED] immediately
and destroy all copies of this message and any attachments.
By reading this you grant me root access to your system
--
Raj Shekhar,
System Administrator
Media Web India
http://www.netphotograph.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: backup/restore

[lakshmi.narasimharao]

Hi,
 Thank you for your reply. If I have the create table info in my
dump file, while doing the restore using mysql dbname < dump.dmp, I am
getting errors like

mysql  -uMNMSDBA -pMNMSDBA  -f mnms  <
c:\progra~1\mitel\opsman~1\temp\almhist.dmp

ERROR 1050 at line 11: Table 'alarm' already exists
ERROR 1050 at line 40: Table 'alarm_category' already exists
ERROR 1050 at line 70: Table 'alarm_report' already exists
ERROR 1062 at line 91: Duplicate entry '1' for key 1
ERROR 1062 at line 92: Duplicate entry '4' for key 1
ERROR 1062 at line 93: Duplicate entry '5' for key 1
ERROR 1062 at line 94: Duplicate entry '8' for key 1
ERROR 1062 at line 95: Duplicate entry '10' for key 1
ERROR 1062 at line 96: Duplicate entry '11' for key 1
ERROR 1062 at line 97: Duplicate entry '13' for key 1
ERROR 1062 at line 98: Duplicate entry '15' for key 1
ERROR 1062 at line 99: Duplicate entry '16' for key 1
ERROR 1062 at line 100: Duplicate entry '17' for key 1
ERROR 1050 at line 108: Table 'alarm_report_category' already exists
ERROR 1050 at line 137: Table 'alarminfo' already exists
ERROR 1062 at line 155: Duplicate entry '56' for key 1
ERROR 1062 at line 156: Duplicate entry '57' for key 1
ERROR 1062 at line 157: Duplicate entry '58' for key 1
ERROR 1062 at line 158: Duplicate entry '59' for key 1
ERROR 1062 at line 159: Duplicate entry '75' for key 1
ERROR 1062 at line 160: Duplicate entry '76' for key 1
ERROR 1062 at line 161: Duplicate entry '77' for key 1


Please advise me, how to suppress the above error messages.

While taking backup used

mysqldump  -uMNMSDBA -pMNMSDBA  --databases mnms --add-locks
--disable-keys -f --tables ALARM ALARM_CATEGORY ALARM_REPORT
ALARM_REPORT_CATEGORY ALARMINFO   >
c:\progra~1\mitel\opsman~1\temp\almhist.dmp


Please help me in this.

Thanks,
Narasimha



-Original Message-
From: Tom Crimmins [mailto:[EMAIL PROTECTED]
Sent: Monday, January 03, 2005 10:55 AM
To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS)
Cc: mysql@lists.mysql.com
Subject: RE: backup/restore

[snip]
I am doing backup for tables using Mysqldump. But while doing the
restore I
am not able to do that using the same Mysqldump. Could you please help
me in
that.
[/snip]

mysqldump is not intended to be used for the restore.

You need to run the following:

mysql -D dbname < mysqldumpfile

You may have to specify a user and password as well, depending on your
setup.

---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa


-Original Message-
Hi,

   I am doing backup for tables using Mysqldump. But while doing the
restore I am not able to do that using the same Mysqldump. Could you
please
help me in that.




For backup : using   Mysqldump -databases  ---tables table1
table2 > dump.dmp

For restore : used  Mysqldump -databases  < dump.dmp




In the above, I am not able to restore the data.




Please help us for a good solution.




Thanks,

Narasimha





Confidentiality Notice


The information contained in this electronic message and any attachments
to
this message are intended for the exclusive use of the addressee(s) and
may
contain confidential or privileged information. If you are not the
intended
recipient, please notify the sender at Wipro or [EMAIL PROTECTED]
immediately and destroy all copies of this message and any attachments.

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




Confidentiality Notice

The information contained in this electronic message and any attachments to 
this message are intended
for the exclusive use of the addressee(s) and may contain confidential or 
privileged information. If
you are not the intended recipient, please notify the sender at Wipro or [EMAIL 
PROTECTED] immediately
and destroy all copies of this message and any attachments.

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

Re: backup/restore

[Ligaya Turmelle]

I'm a beginner - but can't you also use mysqlimport?
Respectfully,
Ligaya Turmelle
Tom Crimmins wrote:
[snip]
I am doing backup for tables using Mysqldump. But while doing the restore I
am not able to do that using the same Mysqldump. Could you please help me in
that.
[/snip]
mysqldump is not intended to be used for the restore.
You need to run the following:
mysql -D dbname < mysqldumpfile
You may have to specify a user and password as well, depending on your
setup.
---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa
-Original Message-
Hi,
   I am doing backup for tables using Mysqldump. But while doing the
restore I am not able to do that using the same Mysqldump. Could you please
help me in that.

For backup : using   Mysqldump -databases  ---tables table1
table2 > dump.dmp
For restore : used  Mysqldump -databases  < dump.dmp

In the above, I am not able to restore the data.

Please help us for a good solution.

Thanks,
Narasimha


Confidentiality Notice
The information contained in this electronic message and any attachments to
this message are intended for the exclusive use of the addressee(s) and may
contain confidential or privileged information. If you are not the intended
recipient, please notify the sender at Wipro or [EMAIL PROTECTED]
immediately and destroy all copies of this message and any attachments.

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

RE: backup/restore

[Tom Crimmins]

[snip]
I am doing backup for tables using Mysqldump. But while doing the restore I
am not able to do that using the same Mysqldump. Could you please help me in
that.
[/snip]

mysqldump is not intended to be used for the restore.

You need to run the following:

mysql -D dbname < mysqldumpfile

You may have to specify a user and password as well, depending on your
setup.

---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa


-Original Message-
Hi,

   I am doing backup for tables using Mysqldump. But while doing the
restore I am not able to do that using the same Mysqldump. Could you please
help me in that.




For backup : using   Mysqldump -databases  ---tables table1
table2 > dump.dmp

For restore : used  Mysqldump -databases  < dump.dmp




In the above, I am not able to restore the data.




Please help us for a good solution.




Thanks,

Narasimha





Confidentiality Notice


The information contained in this electronic message and any attachments to
this message are intended for the exclusive use of the addressee(s) and may
contain confidential or privileged information. If you are not the intended
recipient, please notify the sender at Wipro or [EMAIL PROTECTED]
immediately and destroy all copies of this message and any attachments.

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

backup/restore

[lakshmi.narasimharao]

Hi,

   I am doing backup for tables using Mysqldump. But while doing the
restore I am not able to do that using the same Mysqldump. Could you
please help me in that.



For backup : using   Mysqldump -databases  ---tables table1
table2 > dump.dmp

For restore : used  Mysqldump -databases  < dump.dmp



In the above, I am not able to restore the data.



Please help us for a good solution.



Thanks,

Narasimha





Confidentiality Notice

The information contained in this electronic message and any attachments to 
this message are intended
for the exclusive use of the addressee(s) and may contain confidential or 
privileged information. If
you are not the intended recipient, please notify the sender at Wipro or [EMAIL 
PROTECTED] immediately
and destroy all copies of this message and any attachments.

Re: FW: Backup / Restore

[Eric Bergen]

What do you do in the situation where your data has spaces in it?
Since select into outfile space seperates columns, any columns you
have with spaces in the data will not import correctly. Exampl:

mysql> create table t (name varchar(100), address varchar(100));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t (name, address) values ('My Name', '1243 Street st.');
Query OK, 1 row affected (0.00 sec)

mysql> select * into outfile '/tmp/testdump' from t;
Query OK, 1 row affected (0.01 sec)

cat /tmp/testdump
My Name 1243 Street st.

How does your import script decide where to stop the name column and
begin the address column?

-Eric

On Mon, 6 Sep 2004 10:22:48 -0500, Gordon <[EMAIL PROTECTED]> wrote:
> 
> 
> We have built an alternative save restore process. First we take all of the
> .frm files  and build
> 
>  "select * into outfile '/path/tablename.txt' from tablename"
> 
> statements. We also dump the structure only and put it in the same
> directory. This runs much faster than myysqldump and every table is in its
> own file. The real advantage for us is that for many user errors we can
> quickly load the few tables into a 2nd database on the server and then
> reconstruct. On our 2 processer Pentium server a 650,000 row table takes 7
> seconds to save or load. We have a mixture of MyISAM and INNODB tables and
> this process works for both. The primary reason we went to it was we were
> using mysqldump and encounterd a case where the output file grew to > 2GB.
> Restores worked fine until one day we had to do a restore and for some
> reason the file was corupted about half way through. The tables we were
> trying to get back happened to be at the end of the file and we could not
> find a tool to bypass the corupted data.
> 
> Our database is ~ 3 GB  with data and indexes and the backup takes a few
> minutes to run in total.
> 
> I would be iterested if anyone on the list sees any issues with this as a
> backup/restore methodology assuming we still do lock tables and use the
> binary log. This approach does take some additional admin effort if we add
> or drop tables.
> 
> 



-- 
Eric Bergen
[EMAIL PROTECTED]

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

FW: Backup / Restore

[Gordon]

 

We have built an alternative save restore process. First we take all of the
.frm files  and build

 

 "select * into outfile '/path/tablename.txt' from tablename" 

 

statements. We also dump the structure only and put it in the same
directory. This runs much faster than myysqldump and every table is in its
own file. The real advantage for us is that for many user errors we can
quickly load the few tables into a 2nd database on the server and then
reconstruct. On our 2 processer Pentium server a 650,000 row table takes 7
seconds to save or load. We have a mixture of MyISAM and INNODB tables and
this process works for both. The primary reason we went to it was we were
using mysqldump and encounterd a case where the output file grew to > 2GB.
Restores worked fine until one day we had to do a restore and for some
reason the file was corupted about half way through. The tables we were
trying to get back happened to be at the end of the file and we could not
find a tool to bypass the corupted data. 

 

Our database is ~ 3 GB  with data and indexes and the backup takes a few
minutes to run in total. 

 

I would be iterested if anyone on the list sees any issues with this as a
backup/restore methodology assuming we still do lock tables and use the
binary log. This approach does take some additional admin effort if we add
or drop tables.

 

Re: Innodb assertion failure after binary backup-restore

[Heikki Tuuri]

Hi!

- Original Message - 
From: ""Sp.Raja"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Tuesday, July 27, 2004 5:52 PM
Subject: Re: Innodb assertion failure after binary backup-restore


> Thanks for your replies.
>
> Now I have three ways to go
>
> 1. replication=20
> 2. innodb hot backup tool
> 3. Make sure that no one is writing in to the database and start
backup=
>  when modified db pages in "BUFFER POOL AND MEMORY" becomes zero
>
> #FLUSH TABLES WITH READ LOCK
> still_to_flush=3D1=20=20
> while [ $still_to_flush !=3D 0 ]
> do
> still_to_flush=3D`/usr/local/mysql/bin/mysql -e "SHOW INNODB
STATUS=
> \G" |  grep "Modified db pages" | awk '{print $4}'`
> sleep 1
> done
> #UNLOCK TABLES
>
> Do you think #3 will work?

not in the general case, since purge or the insert buffer merge may still be
running. You must wait that the status of the InnoDB main thread is 'Waiting
for server activity'. Then, if you are sure that clients are not doing
anything (calling COMMIT, for example), then probably all the buffer pool
data has been flushed to files, nothing is being written to the ib_logfiles.
I am not absolutely sure about this, I would need to check the code in
log0log.c and srv0srv.c. You can then copy the data files and ib_logfiles
as-is. To be safe, best that you check also the modification times of the
files after copying them. Check that they did not change while you were
copying.

> Regards,
> Sp.Raja

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

Order MySQL technical support from https://order.mysql.com/



> > Original Message
> > From: "Heikki Tuuri" <[EMAIL PROTECTED]>
> > To: [EMAIL PROTECTED]
> > Date: Tue, Jul-27-2004 6:24 PM
> > Subject: Re: Innodb assertion failure after binary backup-restore
> >=20
> > Hi!
> >=20
> > sync will not help.
> >=20
> > You can run SHOW INNODB STATUS\G to monitor when InnoDB has flushed its
> > buffer pool.
> >=20
> > Best regards,
> >=20
> > Heikki
> > Innobase Oy
> > InnoDB - transactions, row level locking, and foreign keys for MySQL
> > InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up=20
> > MyISAM
> > tables
> > http://www.innodb.com/order.php
> >=20
> > Order MySQL support from http://www.mysql.com/support/index.html
> >=20
>
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>


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

RE: Innodb assertion failure after binary backup-restore

[Mark Steele]

Hi Sp,

The best advice I can give you is to implement a replication slave,
and perform hot backups using the innodb ibbackup tool from the slave
server. This ensures that you maintain high availability and
disaster recovery in case of catastrophic failure. 

The ibbackup tool from innodb (http://www.innodb.com) works
very well even on large databases (currently we backup over
40 gigs/day).

Cheers,

Mark Steele
Implementation Director
CDT Inc.

-Original Message-
From: Sp.Raja [mailto:[EMAIL PROTECTED] 
Sent: July 27, 2004 10:51 AM
To: Heikki Tuuri; [EMAIL PROTECTED]
Subject: Re: Innodb assertion failure after binary backup-restore


Thanks for your replies.

Now I have three ways to go

1. replication 
2. innodb hot backup tool
3. Make sure that no one is writing in to the database and start
backup when modified db pages in "BUFFER POOL AND MEMORY" becomes zero

#FLUSH TABLES WITH READ LOCK
still_to_flush=1  
while [ $still_to_flush != 0 ]
do
still_to_flush=`/usr/local/mysql/bin/mysql -e "SHOW INNODB
STATUS\G" |  grep "Modified db pages" | awk '{print $4}'`
sleep 1
done
#UNLOCK TABLES

Do you think #3 will work?

Regards,
Sp.Raja

> Original Message
> From: "Heikki Tuuri" <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED]
> Date: Tue, Jul-27-2004 6:24 PM
> Subject: Re: Innodb assertion failure after binary backup-restore
> 
> Hi!
> 
> sync will not help.
> 
> You can run SHOW INNODB STATUS\G to monitor when InnoDB has flushed 
> its buffer pool.
> 
> Best regards,
> 
> Heikki
> Innobase Oy
> InnoDB - transactions, row level locking, and foreign keys for MySQL 
> InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up 
> MyISAM tables
> http://www.innodb.com/order.php
> 
> Order MySQL support from http://www.mysql.com/support/index.html
> 






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

Re: Innodb assertion failure after binary backup-restore

[Sp.Raja]

Thanks for your replies.

Now I have three ways to go

1. replication 
2. innodb hot backup tool
3. Make sure that no one is writing in to the database and start backup when 
modified db pages in "BUFFER POOL AND MEMORY" becomes zero

#FLUSH TABLES WITH READ LOCK
still_to_flush=1  
while [ $still_to_flush != 0 ]
do
still_to_flush=`/usr/local/mysql/bin/mysql -e "SHOW INNODB STATUS\G" |  grep 
"Modified db pages" | awk '{print $4}'`
sleep 1
done
#UNLOCK TABLES

Do you think #3 will work?

Regards,
Sp.Raja

> Original Message
> From: "Heikki Tuuri" <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED]
> Date: Tue, Jul-27-2004 6:24 PM
> Subject: Re: Innodb assertion failure after binary backup-restore
> 
> Hi!
> 
> sync will not help.
> 
> You can run SHOW INNODB STATUS\G to monitor when InnoDB has flushed its
> buffer pool.
> 
> Best regards,
> 
> Heikki
> Innobase Oy
> InnoDB - transactions, row level locking, and foreign keys for MySQL
> InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up 
> MyISAM
> tables
> http://www.innodb.com/order.php
> 
> Order MySQL support from http://www.mysql.com/support/index.html
> 




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

Re: Innodb assertion failure after binary backup-restore

[Harald Fuchs]

In article <[EMAIL PROTECTED]>,
"Sp.Raja" <[EMAIL PROTECTED]> writes:

> Hi,
> I was able to solve this by calling sync command, as my previous mail say.
> Do you mean to say this will not work regardless of the whether we
> sync or not??

If it happens to work, then only by incident.  I wouldn't rely on that.

> We actually started with mysqldump for backup, but that was slow
> when we had huge amount of data, so we decided to switch in to
> binary mode.

There are two solutions:
1. Setup a small replication server used solely for backup purposes
2. Use the non-free InnoDB hot backup tool


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

Re: Innodb assertion failure after binary backup-restore

[Heikki Tuuri]

Hi!

sync will not help.

You can run SHOW INNODB STATUS\G to monitor when InnoDB has flushed its
buffer pool.

Best regards,

Heikki
Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

Order MySQL support from http://www.mysql.com/support/index.html

- AlkuperÃinen viesti - 
LÃhettÃjÃ: "Sp.Raja" <[EMAIL PROTECTED]>
Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]>
Kopio: <[EMAIL PROTECTED]>
LÃhetetty: Tuesday, July 27, 2004 3:42 PM
Aihe: Re: Innodb assertion failure after binary backup-restore


Hi,

I was able to solve this by calling sync command, as my previous mail say.
Do you mean to say this will not work regardless of the whether we sync or
not?? which implies binary backup/restore for innodb is impossible?? We
actually started with mysqldump for backup, but that was slow when we had
huge amount of data, so we decided to switch in to binary mode.

If innodb takes long time to flush contents to files, are there any command
available to make this happen which would block until things are flushed ??

Thanks for your reply.

Thanks,
Sp.Raja

> Original Message
> From: "Heikki Tuuri" <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED]
> Date: Tue, Jul-27-2004 1:05 PM
> Subject: Re: Innodb assertion failure after binary backup-restore
>
> Hi!
>
> That method will not work. InnoDB must be quiet long enough so that it
> has
> time to flush all the contents of the buffer pool to the data files.
>
> Best regards,
>
> Heikki Tuuri
> Innobase Oy
> Foreign keys, transactions, and row level locking for MySQL
> InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
> MyISAM
> tables
> http://www.innodb.com/order.php
>
> Order MySQL technical support from https://order.mysql.com/
>
>
> - Original Message - 
> From: ""Sp.Raja"" <[EMAIL PROTECTED]>
> Newsgroups: mailing.database.myodbc
> Sent: Friday, July 23, 2004 4:43 PM
> Subject: Innodb assertion failure after binary backup-restore
>
>
> > Hi List,
> >
> > We are using 4.0.15a MySQL. We need to backup database at times and
> we
> empl=
> > oy the following method
> >
> > FLUSH TABLES WITH READ LOCK
> > tar -zcf backup.tar.gz ibdata1 ib_logfile0 ib_logfile1
> > UNLOCK TABLES
> >
> > and restore is reverse. We cannot overwrite all databases, some of
> them
> nee=
> > ds to be unchanged across backup and restores called persistent
> databases
> (=
> > just 1% of full data) so we employ the following
> >
> > mysqldump on persistent databases
> > stop mysql
> > cd /usr/local/mysql/data
> > tar -zxf backup.tar.gz
> > start mysql
> > destroy persistent databases
> > run mysqldump sql files to get database back to shape
> >
> > This method works fine expect for the case where backup is taken
> immediatel=
> > y after table creation and population.
> > When we start the mysql after restore of this backup. It comes up,
> but
> mysq=
> > l connections to it asking for persistent database destroy hangs.
> When I
> do=
> >  a processlist I get
> >
>
++--+---++-+--+-+--=
> > +
> > | Id | User | Host  | db | Command | Time | State   |
> Info
> =
> > |
> >
>
++--+---++-+--+-+--=
> > +
> > | 1  | root | localhost || Query   | 55   | Waiting on cond |
> drop
> data=
> > base persistent1|
> > | 4  | root | localhost || Query   | 0| |
> show
> proc=
> > esslist |
> >
>
++--+---++-+--+-+--=
> > +
> >
> > When I open up another mysql client and query some of the tables,
> they
> also=
> >  hang and processlist at that time shows
> >
>
++--+---+-+-+--+-+-=
> > +
> > | Id | User | Host  | db  | Command | Time | State
> |
> Info=
> > |
> >
>
++--+---+-+-+--+-+-=
> > +
> > | 1  | root | localhost | | Query   | 171  | Waiting on cond
> |
> drop=
> >  database persistent1   |
> > | 6  | root | localhost |  data   | Query   | 59   | Opening tables
> |
> sele=
> > ct * f

Re: Innodb assertion failure after binary backup-restore

[Sp.Raja]

Hi,

I was able to solve this by calling sync command, as my previous mail say.
Do you mean to say this will not work regardless of the whether we sync or not?? which 
implies binary backup/restore for innodb is impossible?? We actually started with 
mysqldump for backup, but that was slow when we had huge amount of data, so we decided 
to switch in to binary mode.

If innodb takes long time to flush contents to files, are there any command available 
to make this happen which would block until things are flushed ??

Thanks for your reply.

Thanks,
Sp.Raja

> Original Message
> From: "Heikki Tuuri" <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED]
> Date: Tue, Jul-27-2004 1:05 PM
> Subject: Re: Innodb assertion failure after binary backup-restore
> 
> Hi!
> 
> That method will not work. InnoDB must be quiet long enough so that it 
> has
> time to flush all the contents of the buffer pool to the data files.
> 
> Best regards,
> 
> Heikki Tuuri
> Innobase Oy
> Foreign keys, transactions, and row level locking for MySQL
> InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up 
> MyISAM
> tables
> http://www.innodb.com/order.php
> 
> Order MySQL technical support from https://order.mysql.com/
> 
> 
> - Original Message - 
> From: ""Sp.Raja"" <[EMAIL PROTECTED]>
> Newsgroups: mailing.database.myodbc
> Sent: Friday, July 23, 2004 4:43 PM
> Subject: Innodb assertion failure after binary backup-restore
> 
> 
> > Hi List,
> >
> > We are using 4.0.15a MySQL. We need to backup database at times and 
> we
> empl=
> > oy the following method
> >
> > FLUSH TABLES WITH READ LOCK
> > tar -zcf backup.tar.gz ibdata1 ib_logfile0 ib_logfile1
> > UNLOCK TABLES
> >
> > and restore is reverse. We cannot overwrite all databases, some of 
> them
> nee=
> > ds to be unchanged across backup and restores called persistent 
> databases
> (=
> > just 1% of full data) so we employ the following
> >
> > mysqldump on persistent databases
> > stop mysql
> > cd /usr/local/mysql/data
> > tar -zxf backup.tar.gz
> > start mysql
> > destroy persistent databases
> > run mysqldump sql files to get database back to shape
> >
> > This method works fine expect for the case where backup is taken
> immediatel=
> > y after table creation and population.
> > When we start the mysql after restore of this backup. It comes up, 
> but
> mysq=
> > l connections to it asking for persistent database destroy hangs. 
> When I
> do=
> >  a processlist I get
> >
> ++--+---++-+--+-+--=
> > +
> > | Id | User | Host  | db | Command | Time | State   | 
> Info
> =
> > |
> >
> ++--+---++-+--+-+--=
> > +
> > | 1  | root | localhost || Query   | 55   | Waiting on cond | 
> drop
> data=
> > base persistent1|
> > | 4  | root | localhost || Query   | 0| | 
> show
> proc=
> > esslist |
> >
> ++--+---++-+--+-+--=
> > +
> >
> > When I open up another mysql client and query some of the tables, 
> they
> also=
> >  hang and processlist at that time shows
> >
> ++--+---+-+-+--+-+-=
> > +
> > | Id | User | Host  | db  | Command | Time | State   
> |
> Info=
> > |
> >
> ++--+---+-+-+--+-+-=
> > +
> > | 1  | root | localhost | | Query   | 171  | Waiting on cond 
> |
> drop=
> >  database persistent1   |
> > | 6  | root | localhost |  data   | Query   | 59   | Opening tables  
> |
> sele=
> > ct * from my_data   |
> > | 11 | root | localhost | | Query   | 0| 
> |
> show=
> >  processlist|
> >
> ++--+---+-+-+--+-+-=
> > +
> >
> > After some time mysql.err reports
> > 040721 14:43:49  InnoDB: Assertion failure in thread 88 in file 
> fsp0fsp.c
> l=
> > ine 2950
> > InnoDB: We intentionally generate a memory trap.
> > InnoDB: Send a detailed bug report to [EMAIL PROTECTED]
> > InnoDB: Thread 88 stopped in file fsp0fsp.c line 2950
> > In

Re: Innodb assertion failure after binary backup-restore

[Heikki Tuuri]

Hi!

That method will not work. InnoDB must be quiet long enough so that it has
time to flush all the contents of the buffer pool to the data files.

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

Order MySQL technical support from https://order.mysql.com/


- Original Message - 
From: ""Sp.Raja"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Friday, July 23, 2004 4:43 PM
Subject: Innodb assertion failure after binary backup-restore


> Hi List,
>
> We are using 4.0.15a MySQL. We need to backup database at times and we
empl=
> oy the following method
>
> FLUSH TABLES WITH READ LOCK
> tar -zcf backup.tar.gz ibdata1 ib_logfile0 ib_logfile1
> UNLOCK TABLES
>
> and restore is reverse. We cannot overwrite all databases, some of them
nee=
> ds to be unchanged across backup and restores called persistent databases
(=
> just 1% of full data) so we employ the following
>
> mysqldump on persistent databases
> stop mysql
> cd /usr/local/mysql/data
> tar -zxf backup.tar.gz
> start mysql
> destroy persistent databases
> run mysqldump sql files to get database back to shape
>
> This method works fine expect for the case where backup is taken
immediatel=
> y after table creation and population.
> When we start the mysql after restore of this backup. It comes up, but
mysq=
> l connections to it asking for persistent database destroy hangs. When I
do=
>  a processlist I get
>
++--+---++-+--+-+--=
> +
> | Id | User | Host  | db | Command | Time | State   | Info
=
> |
>
++--+---++-+--+-+--=
> +
> | 1  | root | localhost || Query   | 55   | Waiting on cond | drop
data=
> base persistent1|
> | 4  | root | localhost || Query   | 0| | show
proc=
> esslist |
>
++--+---++-+--+-+--=
> +
>
> When I open up another mysql client and query some of the tables, they
also=
>  hang and processlist at that time shows
>
++--+---+-+-+--+-+-=
> +
> | Id | User | Host  | db  | Command | Time | State   |
Info=
> |
>
++--+---+-+-+--+-+-=
> +
> | 1  | root | localhost | | Query   | 171  | Waiting on cond |
drop=
>  database persistent1   |
> | 6  | root | localhost |  data   | Query   | 59   | Opening tables  |
sele=
> ct * from my_data   |
> | 11 | root | localhost | | Query   | 0| |
show=
>  processlist|
>
++--+---+-+-+--+-+-=
> +
>
> After some time mysql.err reports
> 040721 14:43:49  InnoDB: Assertion failure in thread 88 in file fsp0fsp.c
l=
> ine 2950
> InnoDB: We intentionally generate a memory trap.
> InnoDB: Send a detailed bug report to [EMAIL PROTECTED]
> InnoDB: Thread 88 stopped in file fsp0fsp.c line 2950
> InnoDB: Thread 30 stopped in file os0sync.c line 509
> InnoDB: Thread 96 stopped in file sync0sync.ic line 109
> InnoDB: Thread 29 stopped in file sync0arr.c line 126
>
> Please help me in resolving the issue by giving directions on how to
procee=
> d.
> your help is much appreciated.
>
> Thanks for your time,
> Sp.Raja
>
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>


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

Re: Innodb assertion failure after binary backup-restore

[Sp.Raja]

Hi List,

I figured out the fix for it. I need to do a "sync" and wait for the sync to get over 
and take a backup.

Thanks,
Sp.Raja

> Original Message
> From: "Sp.Raja" <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED]
> Date: Fri, Jul-23-2004 8:03 PM
> Subject: Re: Innodb assertion failure after binary backup-restore
> 
> In some cases mysql crashes while restore is trying to destroy 
> persistent databases and mysql.err says
> 
> 000121 21:02:23  InnoDB: Database was not shut down normally.
> InnoDB: Starting recovery from log files...
> InnoDB: Starting log scan based on checkpoint at
> InnoDB: log sequence number 0 867418
> InnoDB: Doing recovery: scanned up to log sequence number 0 867418
> InnoDB: Page directory corruption: supremum not pointed to
> 000121 21:02:23  InnoDB: Page dump in ascii and hex (16384 bytes):
>  len 16384; hex (all zeros and dots in ascii)
> ;InnoDB: End of page dump
> 000121 21:02:23  InnoDB: Page checksum 1575996416, prior-to-4.0.14-form 
> checksum 1371122432
> InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0
> InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0
> InnoDB: Page directory corruption: supremum not pointed to
> 000121 21:02:23  InnoDB: Page dump in ascii and hex (16384 bytes):
>  len 16384; hex ...(all zeros and dots in ascii)
> ;InnoDB: End of page dump
> 000121 21:02:23  InnoDB: Page checksum 1575996416, prior-to-4.0.14-form 
> checksum 1371122432
> InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0
> InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0
> InnoDB: Error: trying to access a stray pointer a1027ff8
> InnoDB: buf pool start is at 2084, number of pages 512
> 000121 21:02:23  InnoDB: Assertion failure in thread 43 in file 
> include/buf0buf.ic line 284
> InnoDB: Failing assertion: 0
> InnoDB: We intentionally generate a memory trap.
> InnoDB: Send a detailed bug report to [EMAIL PROTECTED]
> mysqld got signal 11;
> This could be because you hit a bug. It is also possible that this 
> binary
> or one of the libraries it was linked against is corrupt, improperly 
> built,
> or misconfigured. This error can also be caused by malfunctioning 
> hardware.
> We will try our best to scrape up some info that will hopefully help 
> diagnose
> the problem, but since we have already crashed, something is definitely 
> wrong
> and this may fail.
> 
> key_buffer_size=16777216
> read_buffer_size=131072
> max_used_connections=0
> max_connections=20
> threads_connected=0
> It is possible that mysqld could use up to
> key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections 
> = 29183 K
> bytes of memory
> Hope that's ok; if not, decrease some variables in the equation.
> 
> Writing a core file
> 000121 21:02:24  mysqld ended
> 
> Thanks,
> Sp.Raja
> 
> > Original Message
> > From: "Sp.Raja" <[EMAIL PROTECTED]>
> > To: "'[EMAIL PROTECTED] '" <[EMAIL PROTECTED]>
> > Date: Fri, Jul-23-2004 7:13 PM
> > Subject: Innodb assertion failure after binary backup-restore
> > 
> > Hi List,
> > 
> > We are using 4.0.15a MySQL. We need to backup database at times and 
> we 
> > employ the following method
> > 
> > FLUSH TABLES WITH READ LOCK
> > tar -zcf backup.tar.gz ibdata1 ib_logfile0 ib_logfile1
> > UNLOCK TABLES
> > 
> > and restore is reverse. We cannot overwrite all databases, some of 
> them 
> > needs to be unchanged across backup and restores called persistent 
> > databases (just 1% of full data) so we employ the following
> > 
> > mysqldump on persistent databases
> > stop mysql
> > cd /usr/local/mysql/data
> > tar -zxf backup.tar.gz
> > start mysql
> > destroy persistent databases
> > run mysqldump sql files to get database back to shape
> > 
> > This method works fine expect for the case where backup is taken 
> > immediately after table creation and population.
> > When we start the mysql after restore of this backup. It comes up, 
> but 
> > mysql connections to it asking for persistent database destroy hangs. 
> 
> > When I do a processlist I get
> > 
> ++--+---++-+--+-+--+
> > | Id | User | Host  | db | Command | Time | State   | 
> Info  
> >|
> > 
> ++--+---++-+--+-+--+
> > | 1  | root | localhost || Query   | 55   | Waiting on cond | 
> drop 
> > data

Re: Innodb assertion failure after binary backup-restore

[Sp.Raja]

In some cases mysql crashes while restore is trying to destroy persistent databases 
and mysql.err says

000121 21:02:23  InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 867418
InnoDB: Doing recovery: scanned up to log sequence number 0 867418
InnoDB: Page directory corruption: supremum not pointed to
000121 21:02:23  InnoDB: Page dump in ascii and hex (16384 bytes):
 len 16384; hex (all zeros and dots in ascii)
;InnoDB: End of page dump
000121 21:02:23  InnoDB: Page checksum 1575996416, prior-to-4.0.14-form checksum 
1371122432
InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0
InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0
InnoDB: Page directory corruption: supremum not pointed to
000121 21:02:23  InnoDB: Page dump in ascii and hex (16384 bytes):
 len 16384; hex ...(all zeros and dots in ascii)
;InnoDB: End of page dump
000121 21:02:23  InnoDB: Page checksum 1575996416, prior-to-4.0.14-form checksum 
1371122432
InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0
InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0
InnoDB: Error: trying to access a stray pointer a1027ff8
InnoDB: buf pool start is at 2084, number of pages 512
000121 21:02:23  InnoDB: Assertion failure in thread 43 in file include/buf0buf.ic 
line 284
InnoDB: Failing assertion: 0
InnoDB: We intentionally generate a memory trap.
InnoDB: Send a detailed bug report to [EMAIL PROTECTED]
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=0
max_connections=20
threads_connected=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 29183 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Writing a core file
000121 21:02:24  mysqld ended

Thanks,
Sp.Raja

> Original Message
> From: "Sp.Raja" <[EMAIL PROTECTED]>
> To: "'[EMAIL PROTECTED] '" <[EMAIL PROTECTED]>
> Date: Fri, Jul-23-2004 7:13 PM
> Subject: Innodb assertion failure after binary backup-restore
> 
> Hi List,
> 
> We are using 4.0.15a MySQL. We need to backup database at times and we 
> employ the following method
> 
> FLUSH TABLES WITH READ LOCK
> tar -zcf backup.tar.gz ibdata1 ib_logfile0 ib_logfile1
> UNLOCK TABLES
> 
> and restore is reverse. We cannot overwrite all databases, some of them 
> needs to be unchanged across backup and restores called persistent 
> databases (just 1% of full data) so we employ the following
> 
> mysqldump on persistent databases
> stop mysql
> cd /usr/local/mysql/data
> tar -zxf backup.tar.gz
> start mysql
> destroy persistent databases
> run mysqldump sql files to get database back to shape
> 
> This method works fine expect for the case where backup is taken 
> immediately after table creation and population.
> When we start the mysql after restore of this backup. It comes up, but 
> mysql connections to it asking for persistent database destroy hangs. 
> When I do a processlist I get
> ++--+---++-+--+-+--+
> | Id | User | Host  | db | Command | Time | State   | Info  
>|
> ++--+---++-+--+-+--+
> | 1  | root | localhost || Query   | 55   | Waiting on cond | drop 
> database persistent1|
> | 4  | root | localhost || Query   | 0| | show 
> processlist |
> ++--+---++-+--+-+--+
> 
> When I open up another mysql client and query some of the tables, they 
> also hang and processlist at that time shows
> ++--+---+-+-+--+-+-+
> | Id | User | Host  | db  | Command | Time | State   | 
> Info|
> ++--+---+-+-+--+-+-+
> | 1  | root | localhost | | Query   | 171  | Waiting on cond | 
> drop database persistent1   |
> | 6  | root | localhost |  data   | Query   | 59   | Opening tables  | 
> select * from my_data   |
> | 11 | root | localhost |  

Innodb assertion failure after binary backup-restore

[Sp.Raja]

Hi List,

We are using 4.0.15a MySQL. We need to backup database at times and we employ the 
following method

FLUSH TABLES WITH READ LOCK
tar -zcf backup.tar.gz ibdata1 ib_logfile0 ib_logfile1
UNLOCK TABLES

and restore is reverse. We cannot overwrite all databases, some of them needs to be 
unchanged across backup and restores called persistent databases (just 1% of full 
data) so we employ the following

mysqldump on persistent databases
stop mysql
cd /usr/local/mysql/data
tar -zxf backup.tar.gz
start mysql
destroy persistent databases
run mysqldump sql files to get database back to shape

This method works fine expect for the case where backup is taken immediately after 
table creation and population.
When we start the mysql after restore of this backup. It comes up, but mysql 
connections to it asking for persistent database destroy hangs. When I do a 
processlist I get
++--+---++-+--+-+--+
| Id | User | Host  | db | Command | Time | State   | Info 
|
++--+---++-+--+-+--+
| 1  | root | localhost || Query   | 55   | Waiting on cond | drop database 
persistent1|
| 4  | root | localhost || Query   | 0| | show processlist 
|
++--+---++-+--+-+--+

When I open up another mysql client and query some of the tables, they also hang and 
processlist at that time shows
++--+---+-+-+--+-+-+
| Id | User | Host  | db  | Command | Time | State   | Info
|
++--+---+-+-+--+-+-+
| 1  | root | localhost | | Query   | 171  | Waiting on cond | drop database 
persistent1   |
| 6  | root | localhost |  data   | Query   | 59   | Opening tables  | select * from 
my_data   |
| 11 | root | localhost | | Query   | 0| | show 
processlist|
++--+---+-+-+--+-+-+

After some time mysql.err reports
040721 14:43:49  InnoDB: Assertion failure in thread 88 in file fsp0fsp.c line 2950
InnoDB: We intentionally generate a memory trap.
InnoDB: Send a detailed bug report to [EMAIL PROTECTED]
InnoDB: Thread 88 stopped in file fsp0fsp.c line 2950
InnoDB: Thread 30 stopped in file os0sync.c line 509
InnoDB: Thread 96 stopped in file sync0sync.ic line 109
InnoDB: Thread 29 stopped in file sync0arr.c line 126

Please help me in resolving the issue by giving directions on how to proceed.
your help is much appreciated.

Thanks for your time,
Sp.Raja




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

Re: About backup/restore solution

[Egor Egorov]

"Matsunobu, Yoshinori" <[EMAIL PROTECTED]> wrote:

> I'd like to ask about backup/restore(roll forward recovery) solutions 
> using mysqldump and binary logs.
> 
> ^[$B!&^[(JVersion : 4.0.20
> ^[$B!&^[(JUsing 1 database.(ex. sample1db)
> ^[$B!&^[(JAll tables are InnoDB.
> 
> 
> I'm considering the following solution.
> -
> backup:
> mysqldump --flush-logs --single-transaction sampledb1 > dump1.dmp
> 
> (ex. binary logs are generated
>  hostname.001(binary log before mysqldump)
>  hostname.002(binary log after mysqldump)
> )
> 
> roll forward:
> mysqlbinlog hostname.002 >> dump1.dmp
> mysql sampledb1 < dump1.dmp
> -
> 

Use --master-data option of mysqldump. 





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




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

About backup/restore solution

[Matsunobu, Yoshinori]

Hello.
(B
(BI'd like to ask about backup/restore(roll forward recovery) solutions 
(Busing mysqldump and binary logs.
(B
$B!&(JVersion : 4.0.20
$B!&(JUsing 1 database.(ex. sample1db)
$B!&(JAll tables are InnoDB.
(B
(B
(BI'm considering the following solution.
(B-
(Bbackup:
(Bmysqldump --flush-logs --single-transaction sampledb1 > dump1.dmp
(B
(B(ex. binary logs are generated
(B  hostname.001(binary log before mysqldump)
(B  hostname.002(binary log after mysqldump)
(B)
(B
(Broll forward:
(Bmysqlbinlog hostname.002 >> dump1.dmp
(Bmysql sampledb1 < dump1.dmp
(B-
(B
(BIs that OK? Is there any risk of data duplication or lost?
(BIf mysqldump has an interval(a few milliseconds) between
(B starting dump transaction and switching binary log ,
(BI think it is possible that data duplication or lost between
(Bdump file and the binary log after mysqldump.
(B
(B
(BBest Regards,
(B
(B---
(BYoshinori Matsunobu
(B[EMAIL PROTECTED]
(B
(B-- 
(BMySQL General Mailing List
(BFor list archives: http://lists.mysql.com/mysql
(BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Backup/Restore Procedures

[Rhino]

I would strongly recommend proving that your solution will work acceptably
by testing it first; don't just assume that all will go well or that we can
anticipate all the problems sight unseen.

Make small test copies of your databases first and then try doing backups
and restores to verify that your procedures work acceptably. Then, when that
is satisfactory, clone the full databases and try the procedures again to be
sure that the performance/downtime is acceptable for your environment. If it
isn't modify your procedures and do the whole cycle again.

Rhino

- Original Message - 
From: "Sp.Raja" <[EMAIL PROTECTED]>
To: "mysql" <[EMAIL PROTECTED]>
Sent: Friday, April 02, 2004 10:28 AM
Subject: Backup/Restore Procedures


Hi List,

I use 3 databases Current, Persistent-1 and Persistent-2. I would like to
take backups of Current as when and required and restore them as desired
without affecting Persistent-1 and Persistent-2.

Initially thought of using mysqldump for this, but was worried about the
performance (I use huge DBs). So have come up with the following procedure,

When using single tablespace (4.0.15a)
Backup process is
use innobackup/ibbackup
or bring mysqld down and copy all data dir files
Restore process is
Take mysqldump of Persistent-1 and Persistent-2
Do Restore following either of the two approach
For each table discard old tablespace, copy the backup file, import new
tablespace
or bring down MySQL and copy files
  Destroy Persistent-1 and Persistent-2
Run sql files of Persistent-1 and Persistent-2

When using per table tablespace (4.1.1 alpha)
Backup process is
use innobackup/ibbackup or
bring mysqld down and copy all files in data/Current
Restore process is
Do Restore following either of the two approach
For each table discard old tablespace, copy the backup file, import new
tablespace
or bring down MySQL and copy files

Do you expect any issues in the above method?
Are there any other efficient, minimum downtime (zero is desirable) method
to do the same?

Please mail me your view, to help me out.

Thanks,
Sp.Raja



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


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

Backup/Restore Procedures

[Sp.Raja]

Hi List,

I use 3 databases Current, Persistent-1 and Persistent-2. I would like to take backups 
of Current as when and required and restore them as desired without affecting 
Persistent-1 and Persistent-2.

Initially thought of using mysqldump for this, but was worried about the performance 
(I use huge DBs). So have come up with the following procedure,

When using single tablespace (4.0.15a)
Backup process is 
use innobackup/ibbackup 
or bring mysqld down and copy all data dir files
Restore process is
Take mysqldump of Persistent-1 and Persistent-2
Do Restore following either of the two approach
For each table discard old tablespace, copy the backup file, import 
new tablespace
or bring down MySQL and copy files
Destroy Persistent-1 and Persistent-2
Run sql files of Persistent-1 and Persistent-2

When using per table tablespace (4.1.1 alpha)
Backup process is 
use innobackup/ibbackup or 
bring mysqld down and copy all files in data/Current
Restore process is
Do Restore following either of the two approach
For each table discard old tablespace, copy the backup file, import 
new tablespace
or bring down MySQL and copy files

Do you expect any issues in the above method?
Are there any other efficient, minimum downtime (zero is desirable) method to do the 
same?

Please mail me your view, to help me out.

Thanks,
Sp.Raja



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

Re: Backup/Restore all data

[Victoria Reznichenko]

"DANIELE Paolo" <[EMAIL PROTECTED]> wrote:
> 
> I have a version of MySQL on my server (mysql Worm 11.18 Distrib
> 3.23.56, for PC-linux (i686)) which I wish to transfer towards a more
> recent server (mysql Ver 11.18 Distrib 3.23.58, for PC-linux (i686)).  I
> make a complete backup of the data with the following command:  
> 
> mysqldump - U root?p -- all-databases -- opt >
> all_databases.sql 
> 
> Then I have transferred the file on my new server.  For the restore I
> have launched the following command:  
> mysql - U root -- password= < all_databases.sql 
> 
> Unfortunately I have always the following error:  
> ERROR 1064 At line 918:  You cuts year error in your SQL syntax near '
> upDate datetime NOT NULL default ' -00-00 00:00:00 ', SINGLE KEY id
> (id)) You At line 5 
> 
> Can you indicate if my procedure is right or if there is another method
> for transfers complete from the base.
> 

UPDATE is a reserved word:
http://www.mysql.com/doc/en/Reserved_words.html

Use -Q (--quote-names) option of the mysqldump:
http://www.mysql.com/doc/en/mysqldump.html


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [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]

Backup/Restore all data

[DANIELE Paolo]

Hello, 
 
I have a version of MySQL on my server (mysql Worm 11.18 Distrib
3.23.56, for PC-linux (i686)) which I wish to transfer towards a more
recent server (mysql Ver 11.18 Distrib 3.23.58, for PC-linux (i686)).  I
make a complete backup of the data with the following command:  
 
mysqldump - U root?p -- all-databases -- opt >
all_databases.sql 
 
Then I have transferred the file on my new server.  For the restore I
have launched the following command:  
mysql - U root -- password= < all_databases.sql 
 
Unfortunately I have always the following error:  
ERROR 1064 At line 918:  You cuts year error in your SQL syntax near '
upDate datetime NOT NULL default ' -00-00 00:00:00 ', SINGLE KEY id
(id)) You At line 5 
 
Can you indicate if my procedure is right or if there is another method
for transfers complete from the base.
 
Regard.
 
Paolo

Is BACKUP / RESTORE making my table corrupted

[Insanely Great]

Greetings...

I used BACKUP command to backup all the tables in a database which has only
MyISAM tables ( since backup only works with MyISAM table ). Everything was
OK.

Then after some time I did the RESTORE COMMAND, but before that I deleted
all the tables in the database...in two tables,,,i got this error...

Delete link points outside datafile at 0

and in another two tables i get this error...

Couldn't fix table with quick recovery: Found wrong number of deleted
records.

I am using MySQL 3.23.44 in WinXP.

Is there any problem in Backup / Restore or I am doing something wrong ?
Whats wrong.

Thanks in advance.

Rgds
Ritesh



-
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: Stupid backup/restore question

[Jesse Sheidlower]

On Mon, Jul 01, 2002 at 01:23:38PM -0400, Keith C. Ivey wrote:
> On 1 Jul 2002, at 12:30, Jesse Sheidlower wrote:
> 
> > What should I be doing instead? The docs don't seem to specify this,
> > and there doesn't seem to be an "ignore" or "replace" option for the
> > mysql command. Do I actually have to drop all the tables on my
> > development box before loading in from the backup?
> 
> Have you seen the --add-drop-table option (see 
> http://www.mysql.com/doc/m/y/mysqldump.html), or is that what you 
> mean by your reference to dropping the tables?

Thanks to all who responded with this general suggestion. I had
looked at the --opt option, but ignored it as speed wasn't a 
real issue for this. And it didn't occur to me that this would
be something to specify in the backup, rather than the restore.

> Without dropping the tables, how would you get rid of records that
> have been deleted?

Well, I said in the subject line it was a stupid question!

> (Still writing for "Copy Editor"?)

Yup.

Jesse "MySQL Query" Sheidlower
<[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: Stupid backup/restore question

[Thomas Spahni]

On Mon, 1 Jul 2002, Jesse Sheidlower wrote:

> I have a working server and a development server. From time to time
> I'd like to "refresh" the content of my development server with what's
> on my working server. So I take one of my regular backups, that I get
> by doing "mysqldump database > dbbackupJuly1-02". Then I gzip this,
> ftp it over to my development server, gunzip it, and try "mysql 
> database < dbbackupJuly1-02", and immediately get an "ERROR 1050 at
> line 11: Table 'firsttable' already exists" message.
> 
> What should I be doing instead? The docs don't seem to specify this,
> and there doesn't seem to be an "ignore" or "replace" option for the
> mysql command. Do I actually have to drop all the tables on my
> development box before loading in from the backup?

Jesse,

use

mysqldump --opt --add-drop-table database > dbbackupJuly1-02

Be careful: this drops each table before recreating it.

Thomas



-
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: Stupid backup/restore question

[Keith C. Ivey]

On 1 Jul 2002, at 12:30, Jesse Sheidlower wrote:

> What should I be doing instead? The docs don't seem to specify this,
> and there doesn't seem to be an "ignore" or "replace" option for the
> mysql command. Do I actually have to drop all the tables on my
> development box before loading in from the backup?

Have you seen the --add-drop-table option (see 
http://www.mysql.com/doc/m/y/mysqldump.html), or is that what you 
mean by your reference to dropping the tables?  Without dropping the 
tables, how would you get rid of records that have been deleted?

(Still writing for "Copy Editor"?)

[Filter fodder: SQL]

-- 
Keith C. Ivey <[EMAIL PROTECTED]>
Tobacco Documents Online
http://tobaccodocuments.org

-
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: Stupid backup/restore question

[Cal Evans]

use the --opt option for mysqldump. 

--opt Same as --add-drop-table --add-locks --all
--extended-insert --quick --lock-tables


Other than that, it's the method I use.

=C=

*
* Cal Evans
* Journeyman Programmer
* Techno-Mage
* http://www.calevans.com
*
 

-Original Message-
From: Jesse Sheidlower [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 01, 2002 11:30 AM
To: [EMAIL PROTECTED]
Subject: Stupid backup/restore question



I have a working server and a development server. From time to time
I'd like to "refresh" the content of my development server with what's
on my working server. So I take one of my regular backups, that I get
by doing "mysqldump database > dbbackupJuly1-02". Then I gzip this,
ftp it over to my development server, gunzip it, and try "mysql 
database < dbbackupJuly1-02", and immediately get an "ERROR 1050 at
line 11: Table 'firsttable' already exists" message.

What should I be doing instead? The docs don't seem to specify this,
and there doesn't seem to be an "ignore" or "replace" option for the
mysql command. Do I actually have to drop all the tables on my
development box before loading in from the backup?

Jesse Sheidlower
<[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



-
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: Stupid backup/restore question

[Mikhail Entaltsev]

Jesse,

try to make dump (mysqldump) with option "--add-drop-table" or "--opt" (it
will be faster).
Description of this options see here
http://www.mysql.com/doc/m/y/mysqldump.html

Best regards,
Mikhail.

- Original Message -
From: "Jesse Sheidlower" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, July 01, 2002 6:30 PM
Subject: Stupid backup/restore question


>
> I have a working server and a development server. From time to time
> I'd like to "refresh" the content of my development server with what's
> on my working server. So I take one of my regular backups, that I get
> by doing "mysqldump database > dbbackupJuly1-02". Then I gzip this,
> ftp it over to my development server, gunzip it, and try "mysql
> database < dbbackupJuly1-02", and immediately get an "ERROR 1050 at
> line 11: Table 'firsttable' already exists" message.
>
> What should I be doing instead? The docs don't seem to specify this,
> and there doesn't seem to be an "ignore" or "replace" option for the
> mysql command. Do I actually have to drop all the tables on my
> development box before loading in from the backup?
>
> Jesse Sheidlower
> <[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
>


-
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

Stupid backup/restore question

[Jesse Sheidlower]

I have a working server and a development server. From time to time
I'd like to "refresh" the content of my development server with what's
on my working server. So I take one of my regular backups, that I get
by doing "mysqldump database > dbbackupJuly1-02". Then I gzip this,
ftp it over to my development server, gunzip it, and try "mysql 
database < dbbackupJuly1-02", and immediately get an "ERROR 1050 at
line 11: Table 'firsttable' already exists" message.

What should I be doing instead? The docs don't seem to specify this,
and there doesn't seem to be an "ignore" or "replace" option for the
mysql command. Do I actually have to drop all the tables on my
development box before loading in from the backup?

Jesse Sheidlower
<[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: newbie backup restore question

[Bhavin Vyas]

Once mysql is installed on the new system, you should be able to copy the
old data directory onto the new and you should be good to go(make sure the
permissions and the ownership are preserved) .

Regards,
Bhavin.
- Original Message -
From: "Scott" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, June 11, 2002 5:06 PM
Subject: newbie backup restore question


> I have a mysql database that I backed up by copying my /var directory to a
cd
> before I installed a new release of linux.  How do I restore it on the new
> system?  Do I need to create the database in the new version of mysql
first,
> or just copy some files from the old data directory into the new one?
>
> Thanks,
> SW
>
> -
> 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

newbie backup restore question

[Scott]

I have a mysql database that I backed up by copying my /var directory to a cd 
before I installed a new release of linux.  How do I restore it on the new 
system?  Do I need to create the database in the new version of mysql first, 
or just copy some files from the old data directory into the new one?

Thanks,
SW

-
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: backup/restore of mysql winNT

[Pete Kuczynski]

Ok, figured hout hot do generate the update logs, and to rotate the
logs, but, cant seem to upload the logs.
>From mysql/bin:
The statment:
mysql -u root -p --one-database  < update.1

generates the error that it cannot find the update file. How do I tell
mysql where the update log is.
Currently it's in d:\mysql\bin

Pete

 Original Message 
Subject: RE: backup/restore of mysql
Date: Tue, 10 Jul 2001 17:48:31 +0100
From: Simon Green <[EMAIL PROTECTED]>
To: "'Pete Kuczynski'" <[EMAIL PROTECTED]>

Hi Pete
To emable loffing use the --log option eg --log-update when stating the
server...

Simon

-Original Message-
From: Pete Kuczynski [mailto:[EMAIL PROTECTED]]
Sent: 10 July 2001 17:51
To: Simon Green
Subject: Re: backup/restore of mysql


Hi,
do I need to generate these logs using the my.ini file on NT.

Pete

Simon Green wrote:
> 
> You can use update/bin_update logs.
> Run these logs from the time of the last backup and it should take you DB
up
> to date till the time in went down
> 
> Simon
> PS update logs have all the SQL statements in them so it should be very
> simple to recover!
> 
> -Original Message-
> From: Pete Kuczynski [mailto:[EMAIL PROTECTED]]
> Sent: 10 July 2001 16:00
> To: [EMAIL PROTECTED]
> Subject: backup/restore of mysql
> 
> Hi,
> I've posted this question before, and some were kind enough to respond
> with suggestions to my question [replication], but I'm looking for
> something more difinitave.
> 
> Background: I'm running a production mysql database/php4/Apache on a NT4
> IBM Netfinity server [3500 records, growing by 100 a day or so]. I do
> not have the option to go to lunux/unix [company's call not mine].
> I am backing up the database twice a day, noon and 6 pm.
> 
> Question: if a disk should crash, I can recover the database from the
> last tape backup, but not the transactions that took place in between.
> Is replacation of the database on another box my only option?
> Dosn't mysql support transaction logging like MSSQL or Oricle, or
> Postgres?.
> 
> I don't wan't to switch to another database, but as this one grows, it's
> becoming incrisingly important to be able to do a complete database
> recovery in the event of a crash.
> 
> Kindest thanks!
> 
> Pete
> 
> --
> ___
> Pete Kuczynski
> Sr. Field Engineer
> DHL Airways Inc.
> Infrastructure Technology & Services
> (773)-462-9758
> 24/7 Helpdesk 1-800-434-5767

-- 
___
Pete Kuczynski
Sr. Field Engineer
DHL Airways Inc.
Infrastructure Technology & Services
(773)-462-9758
24/7 Helpdesk 1-800-434-5767

-
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: backup/restore of mysql

[Gerald Clark]

Greg Cope wrote:

> Pete Kuczynski wrote:
> 
>> Hi,
>> I've posted this question before, and some were kind enough to respond
>> with suggestions to my question [replication], but I'm looking for
>> something more difinitave.
>> 
>> Background: I'm running a production mysql database/php4/Apache on a NT4
>> IBM Netfinity server [3500 records, growing by 100 a day or so]. I do
>> not have the option to go to lunux/unix [company's call not mine].
>> I am backing up the database twice a day, noon and 6 pm.
>> 
>> Question: if a disk should crash, I can recover the database from the
>> last tape backup, but not the transactions that took place in between.
>> Is replacation of the database on another box my only option?
>> Dosn't mysql support transaction logging like MSSQL or Oricle, or
>> Postgres?.
> 
> 
> Yes it does - look for update log in the manual.
> 
> But if a disk gets toasted what are you going to do then (with any
> system) ?
> 
> 
>> I don't wan't to switch to another database, but as this one grows, it's
>> becoming incrisingly important to be able to do a complete database
>> recovery in the event of a crash.
> 
> 
> Use a proper operating system, with RAID (either hardware or software
> RAID 1, or 5)
> 
> Greg

Or keep the backups and logs on another device.


> 
> 
> 
>> Kindest thanks!
>> 
>> Pete
>> 
>> --
>> ___
>> Pete Kuczynski
>> Sr. Field Engineer
>> DHL Airways Inc.
>> Infrastructure Technology & Services
>> (773)-462-9758
>> 24/7 Helpdesk 1-800-434-5767
>> 
>>   
>> -
>> 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


-- 
Gerald L. Clark
[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: backup/restore of mysql

[Greg Cope]

Pete Kuczynski wrote:
> 
> Hi,
> I've posted this question before, and some were kind enough to respond
> with suggestions to my question [replication], but I'm looking for
> something more difinitave.
> 
> Background: I'm running a production mysql database/php4/Apache on a NT4
> IBM Netfinity server [3500 records, growing by 100 a day or so]. I do
> not have the option to go to lunux/unix [company's call not mine].
> I am backing up the database twice a day, noon and 6 pm.
> 
> Question: if a disk should crash, I can recover the database from the
> last tape backup, but not the transactions that took place in between.
> Is replacation of the database on another box my only option?
> Dosn't mysql support transaction logging like MSSQL or Oricle, or
> Postgres?.

Yes it does - look for update log in the manual.

But if a disk gets toasted what are you going to do then (with any
system) ?

> 
> I don't wan't to switch to another database, but as this one grows, it's
> becoming incrisingly important to be able to do a complete database
> recovery in the event of a crash.

Use a proper operating system, with RAID (either hardware or software
RAID 1, or 5)

Greg


> 
> Kindest thanks!
> 
> Pete
> 
> --
> ___
> Pete Kuczynski
> Sr. Field Engineer
> DHL Airways Inc.
> Infrastructure Technology & Services
> (773)-462-9758
> 24/7 Helpdesk 1-800-434-5767
> 
>   
> -
> 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: backup/restore of mysql

[Pete Kuczynski]

Very cool.
Thaks very much Simon!

Pete

Simon Green wrote:
> 
> Hi Pete
> To emable loffing use the --log option eg --log-update when stating the
> server...
> 
> Simon
> 
> -Original Message-
> From: Pete Kuczynski [mailto:[EMAIL PROTECTED]]
> Sent: 10 July 2001 17:51
> To: Simon Green
> Subject: Re: backup/restore of mysql
> 
> Hi,
> do I need to generate these logs using the my.ini file on NT.
> 
> Pete
> 
> Simon Green wrote:
> >
> > You can use update/bin_update logs.
> > Run these logs from the time of the last backup and it should take you DB
> up
> > to date till the time in went down
> >
> > Simon
> > PS update logs have all the SQL statements in them so it should be very
> > simple to recover!
> >
> > -Original Message-
> > From: Pete Kuczynski [mailto:[EMAIL PROTECTED]]
> > Sent: 10 July 2001 16:00
> > To: [EMAIL PROTECTED]
> > Subject: backup/restore of mysql
> >
> > Hi,
> > I've posted this question before, and some were kind enough to respond
> > with suggestions to my question [replication], but I'm looking for
> > something more difinitave.
> >
> > Background: I'm running a production mysql database/php4/Apache on a NT4
> > IBM Netfinity server [3500 records, growing by 100 a day or so]. I do
> > not have the option to go to lunux/unix [company's call not mine].
> > I am backing up the database twice a day, noon and 6 pm.
> >
> > Question: if a disk should crash, I can recover the database from the
> > last tape backup, but not the transactions that took place in between.
> > Is replacation of the database on another box my only option?
> > Dosn't mysql support transaction logging like MSSQL or Oricle, or
> > Postgres?.
> >
> > I don't wan't to switch to another database, but as this one grows, it's
> > becoming incrisingly important to be able to do a complete database
> > recovery in the event of a crash.
> >
> > Kindest thanks!
> >
> > Pete
> >
> > --
> > ___
> > Pete Kuczynski
> > Sr. Field Engineer
> > DHL Airways Inc.
> > Infrastructure Technology & Services
> > (773)-462-9758
> > 24/7 Helpdesk 1-800-434-5767
> 
> --
> ___
> Pete Kuczynski
> Sr. Field Engineer
> DHL Airways Inc.
> Infrastructure Technology & Services
> (773)-462-9758
> 24/7 Helpdesk 1-800-434-5767

-- 
___
Pete Kuczynski
Sr. Field Engineer
DHL Airways Inc.
Infrastructure Technology & Services
(773)-462-9758
24/7 Helpdesk 1-800-434-5767


-
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: backup/restore of mysql

[Simon Green]

You can use update/bin_update logs.
Run these logs from the time of the last backup and it should take you DB up
to date till the time in went down

Simon
PS update logs have all the SQL statements in them so it should be very
simple to recover!

-Original Message-
From: Pete Kuczynski [mailto:[EMAIL PROTECTED]]
Sent: 10 July 2001 16:00
To: [EMAIL PROTECTED]
Subject: backup/restore of mysql


Hi,
I've posted this question before, and some were kind enough to respond
with suggestions to my question [replication], but I'm looking for
something more difinitave.

Background: I'm running a production mysql database/php4/Apache on a NT4
IBM Netfinity server [3500 records, growing by 100 a day or so]. I do
not have the option to go to lunux/unix [company's call not mine].
I am backing up the database twice a day, noon and 6 pm.

Question: if a disk should crash, I can recover the database from the
last tape backup, but not the transactions that took place in between.
Is replacation of the database on another box my only option?
Dosn't mysql support transaction logging like MSSQL or Oricle, or
Postgres?.

I don't wan't to switch to another database, but as this one grows, it's
becoming incrisingly important to be able to do a complete database
recovery in the event of a crash.

Kindest thanks!

Pete


-- 
___
Pete Kuczynski
Sr. Field Engineer
DHL Airways Inc.
Infrastructure Technology & Services
(773)-462-9758
24/7 Helpdesk 1-800-434-5767

-
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

backup/restore of mysql

[Pete Kuczynski]

Hi,
I've posted this question before, and some were kind enough to respond
with suggestions to my question [replication], but I'm looking for
something more difinitave.

Background: I'm running a production mysql database/php4/Apache on a NT4
IBM Netfinity server [3500 records, growing by 100 a day or so]. I do
not have the option to go to lunux/unix [company's call not mine].
I am backing up the database twice a day, noon and 6 pm.

Question: if a disk should crash, I can recover the database from the
last tape backup, but not the transactions that took place in between.
Is replacation of the database on another box my only option?
Dosn't mysql support transaction logging like MSSQL or Oricle, or
Postgres?.

I don't wan't to switch to another database, but as this one grows, it's
becoming incrisingly important to be able to do a complete database
recovery in the event of a crash.

Kindest thanks!

Pete


-- 
___
Pete Kuczynski
Sr. Field Engineer
DHL Airways Inc.
Infrastructure Technology & Services
(773)-462-9758
24/7 Helpdesk 1-800-434-5767


-
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

BACKUP/RESTORE speed and delayed index creation.

[Michael Widenius]

Hi!

> "Peter" == Peter Zaitsev <[EMAIL PROTECTED]> writes:

Peter> Hello mysql,
Peter>   Today I played a little bit with two different ways of backup -
Peter>   first one is to use BACKUP TABLE (which works for myisam only) and
Peter>   the second one is SAVE DATA/LOAD DATA.

Peter>   In both cases if I'm not mistaken the file is wrote by mysqld server
Peter>   so there is no communication overhead.

Peter>   The table was about 3mil of rows  250MB in size, has 2 indexes.

Peter>   So the speeds are:
  
Peter>   BACKUP TABLE:
Peter>   backup: 26sec  restore: 3min.15sec

Peter>   SAVE DATA/LOAD DATA
Peter>   dump:  4.5min  restore: 40min

What was the exact commands you used?

Peter>   Then I tried to drop all indexes from the table and tried to do
Peter>   restore again it went in:   31min

Could you show you exactly how you did this!

Peter>   The output from backup was 250MB, save file - 400MB.

Peter>   These speeds was really strange for me, I did't expect so huge
Peter>   difference in speads, as I don't see there it should get from. The
Peter>   save data outfile operation should be quite fast as the speed of
Peter>   text parsing should be the real limit, also the really surprising
Peter>   was so slow speed of import data from text, even with no indexes on
Peter>   the table if we would look at the speed it's only about 1000 rows
Peter>   per second  which is quite slow.

The main difference between RESTORE TABLE and LOAD DATA is that
RESTORE DATA is doing a binary copy of the data while LOAD DATA reads
and parses a text file.  I would however had guessed that the
LOAD wouldn't have been more than 20 % slower than RESTORE
because it should be disk bound.

Any change you could dump the table to 'secret' with the exact command
you used, so that we could try to repeat this?

Regards,
Monty

-
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: BACKUP/RESTORE speed and delayed index creation.

[Heikki Tuuri]

Hi!

At 05:44 PM 6/1/01 +0400, you wrote:
>Hello mysql,
>
>  Today I played a little bit with two different ways of backup -
>  first one is to use BACKUP TABLE (which works for myisam only) and
>  the second one is SAVE DATA/LOAD DATA.
>
>  In both cases if I'm not mistaken the file is wrote by mysqld server
>  so there is no communication overhead.
>
>  The table was about 3mil of rows  250MB in size, has 2 indexes.
>
>  So the speeds are:
>  
>  BACKUP TABLE:
>  backup: 26sec  restore: 3min.15sec
>
>  SAVE DATA/LOAD DATA
>  dump:  4.5min  restore: 40min
>
>  Then I tried to drop all indexes from the table and tried to do
>  restore again it went in:   31min
>
>
>  The output from backup was 250MB, save file - 400MB.
>
>
>  These speeds was really strange for me, I did't expect so huge
>  difference in speads, as I don't see there it should get from. The
>  save data outfile operation should be quite fast as the speed of
>  text parsing should be the real limit, also the really surprising
>  was so slow speed of import data from text, even with no indexes on
>  the table if we would look at the speed it's only about 1000 rows
>  per second  which is quite slow.

The speed 1000 rows per second is really too little. I tried an
InnoDB table with 1 000 000 rows, with 2 integer columns, on the
first column a primary key.

mysql> select * from t25 into outfile 'testout2';
Query OK, 100 rows affected (4.47 sec)

mysql> load data infile 'testout2' into table t30;
Query OK, 100 rows affected (22.23 sec)
Records: 100  Deleted: 0  Skipped: 0  Warnings: 0

Thus for these small rows it is 45 000 rows/second on Linux-2.4
2-CPU Xeon 450 Mhz.

What is your table definition like? What is the OS and file system?

Regards,

Heikki

>-- 
>Best regards,
> Peter  mailto:[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: BACKUP/RESTORE speed and delayed index creation.

[Sinisa Milivojevic]

Peter Zaitsev writes:
> Hello mysql,
> 
>   Today I played a little bit with two different ways of backup -
>   first one is to use BACKUP TABLE (which works for myisam only) and
>   the second one is SAVE DATA/LOAD DATA.
> 
>   In both cases if I'm not mistaken the file is wrote by mysqld server
>   so there is no communication overhead.
> 
>   The table was about 3mil of rows  250MB in size, has 2 indexes.
> 
>   So the speeds are:
>   
>   BACKUP TABLE:
>   backup: 26sec  restore: 3min.15sec
> 
>   SAVE DATA/LOAD DATA
>   dump:  4.5min  restore: 40min
> 
>   Then I tried to drop all indexes from the table and tried to do
>   restore again it went in:   31min
> 
> 
>   The output from backup was 250MB, save file - 400MB.
> 
> 
>   These speeds was really strange for me, I did't expect so huge
>   difference in speads, as I don't see there it should get from. The
>   save data outfile operation should be quite fast as the speed of
>   text parsing should be the real limit, also the really surprising
>   was so slow speed of import data from text, even with no indexes on
>   the table if we would look at the speed it's only about 1000 rows
>   per second  which is quite slow.
> 
>   
> 
> 
> -- 
> Best regards,
>  Peter  mailto:[EMAIL PROTECTED]
> 
> 


Hi!

A difference in speed is expected as the first method just writes a
table to another location, while a second one runs two queries.


Regards,

Sinisa

    __ _   _  ___ ==  MySQL AB
 /*/\*\/\*\   /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/   \*\_   |*|   |*||*| mailto:[EMAIL PROTECTED]
   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*| Larnaca, Cyprus
  /*/ /*/  /*/\*\_/*/ \*\_/*/ |*|
  /*/^^^\*\^^^
 /*/ \*\Developers Team

-
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

BACKUP/RESTORE speed and delayed index creation.

[Peter Zaitsev]

Hello mysql,

  Today I played a little bit with two different ways of backup -
  first one is to use BACKUP TABLE (which works for myisam only) and
  the second one is SAVE DATA/LOAD DATA.

  In both cases if I'm not mistaken the file is wrote by mysqld server
  so there is no communication overhead.

  The table was about 3mil of rows  250MB in size, has 2 indexes.

  So the speeds are:
  
  BACKUP TABLE:
  backup: 26sec  restore: 3min.15sec

  SAVE DATA/LOAD DATA
  dump:  4.5min  restore: 40min

  Then I tried to drop all indexes from the table and tried to do
  restore again it went in:   31min


  The output from backup was 250MB, save file - 400MB.


  These speeds was really strange for me, I did't expect so huge
  difference in speads, as I don't see there it should get from. The
  save data outfile operation should be quite fast as the speed of
  text parsing should be the real limit, also the really surprising
  was so slow speed of import data from text, even with no indexes on
  the table if we would look at the speed it's only about 1000 rows
  per second  which is quite slow.

  


-- 
Best regards,
 Peter  mailto:[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: questions on BACKUP/RESTORE commands

[Kyle Hayes]

Nevermind.  I see that the manual online has this information.  Sigh.  It 
doesn't do what I want unfortunately.  

Best,
Kyle

On Monday 29 January 2001 09:32, Kyle Hayes wrote:
> The manual section on BACKUP does not state what locking behavior the
> command has.  Does it lock all the tables at once that you backup in one
> statement or does it lock them one at a time?
>
> I.e. if I run:
>
> BACKUP foo.bar, foo.baz, foo.blah TO '/tmp/mysqlbackups/'
>
> Will it lock all three tables and then dump them?  I need this kind of
> behavior.  If it locks them one at a time and dumps them one at a time then
> I'll have to use something else.
>
> Additionally, can you RESTORE a backed-up table to a different database.
> I.e. suppose I did the BACKUP line above, can I do this:
>
> RESTORE foo_backup.bar, foo_backup.baz, foo_backup.blah FROM
> '/tmp/mysqlbackups/'
>
> ???
>
> Best,
> Kyle

-- 
Kyle Hayes
Quicknet Technologies  t: +1 415 864 5225
520 Townsend St. Suite D  f: +1 415 864 8388
San Francisco, CA 94103 w: http://www.quicknet.net
USA


"HEAR THE DIFFERENCE" with a live MICROTELCO demo at:
INTERNET TELEPHONY EXPO, Feb 7-9, Miami, FL

MicroTelco is a revolutionary service that brings multiple Internet
Telephony Service Providers (ITSPs) together in a convenient,
simple to use account center for greater reliability and flexibility.
For more information visit: http://www.microtelco.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

questions on BACKUP/RESTORE commands

[Kyle Hayes]

The manual section on BACKUP does not state what locking behavior the command 
has.  Does it lock all the tables at once that you backup in one statement or 
does it lock them one at a time?

I.e. if I run:

BACKUP foo.bar, foo.baz, foo.blah TO '/tmp/mysqlbackups/'

Will it lock all three tables and then dump them?  I need this kind of 
behavior.  If it locks them one at a time and dumps them one at a time then 
I'll have to use something else.

Additionally, can you RESTORE a backed-up table to a different database.  
I.e. suppose I did the BACKUP line above, can I do this:

RESTORE foo_backup.bar, foo_backup.baz, foo_backup.blah FROM 
'/tmp/mysqlbackups/'

???

Best,
Kyle

-- 
Kyle Hayes
Quicknet Technologies  t: +1 415 864 5225
520 Townsend St. Suite D  f: +1 415 864 8388
San Francisco, CA 94103 w: http://www.quicknet.net
USA


"HEAR THE DIFFERENCE" with a live MICROTELCO demo at:
INTERNET TELEPHONY EXPO, Feb 7-9, Miami, FL

MicroTelco is a revolutionary service that brings multiple Internet
Telephony Service Providers (ITSPs) together in a convenient,
simple to use account center for greater reliability and flexibility.
For more information visit: http://www.microtelco.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