Table Backup Question

rgermain

Is there any easy way to duplicate a table or back it up. I don't want to reset the auto_increment field if possible.

LordShryku

Duplicating is easy....

CREATE TABLE backup AS
  SELECT * FROM otherTable

rgermain

Okay, I feel stupid....I tried to you syntax and I get an error.
This is exactly how I wrote it:

CREATE TABLE bkup_tbl AS SELECT * FROM story_tbl

I also went to the MySQL website to look and see what I was doing wrong and I noticed they had something a little different and I tried that, but still no luck here is what the query I tried looked like:

CREATE TABLE bkup_tbl SELECT * FROM story_tbl

The same error each time "syntax error near 'AS SELECT * FROM story_tbl' on line 1.

The other error is the same except it start at the SELECT. Any ideas what I am doing wrong?

LordShryku

What version of MySQL are you running? Might not be able to do sub-queries. Also check the manual for mysql dump and look here
http://www.phpbuilder.com/board/showthread.php?s=&threadid=10253791&highlight=mysql+dump

rgermain

I am running version 3.23.39 of mysql.

stolzyboy

Originally posted by rgermain
I am running version 3.23.39 of mysql.

did ya go to LordShryku's link or didn't that work for ya

and you can't do subqueries with version prior to mysql 4.1 and that is yet in alpha version

LordShryku

And boy do I use a lot of subqueries......

rgermain

Yeah, I checked out the link and gave it a shot, but didn't work. I am probably stuck then huh?

stolzyboy

Originally posted by rgermain
Yeah, I checked out the link and gave it a shot, but didn't work. I am probably stuck then huh?

it should work, that is the same version of mysql i have, did it give you any errors?

rgermain

Ok....Here are the errors I am currently getting...

Warning: lstat failed for /home/dlonline/public_html/dlonline-
092603-0355pm_sql.tar.gz (errno=2 - No such file or directory) 
in /home/dlonline/public_html/test/help.php on line 63

Warning: fopen("/home/dlonline/public_html/dlonline-092603-
0355pm_sql.tar.gz", "rb") - No such file or directory 
in /home/dlonline/public_html/test/help.php on line 69

Warning: stat failed for /home/dlonline/public_html/dlonline-
092603-0355pm_sql.tar.gz (errno=2 - No such file or directory) 
in /home/dlonline/public_html/test/help.php on line 70

Warning: Supplied argument is not a valid File-Handle resource
 in /home/dlonline/public_html/test/help.php on line 70

Warning: Supplied argument is not a valid File-Handle resource 
in /home/dlonline/public_html/test/help.php on line 71

Database backup created and sent! File 
name /home/dlonline/public_html/dlonline-092603-
0355pm_sql.tar.gz

Oh, I got an email also, but the zip file was empty which I suspected it would be.

rgermain

Ok...Thanks I figured it all out now. Read through the script a couple of times and figured out what was going on.

 Thanks Again.