Using SELECT INTO to Backup a Database

[himilecyclist@yahoo.com]

I am trying to use the SQL "SELECT INTO" to create a copy of a MySQL
database.

My PHP code looks like this:

<?php

//Connect to server and database

include ("Connection s/ovrs.inc");

if (isset($_POST['cmdBackup']))
{
$sql = "SELECT * INTO edenbackup FROM edenmaster";


$result = mysql_query($sq l) or die
(ErrorCall(mysq l_error(),"frmB ackup.php","1") );

echo "The Backup is Complete";
}

?>

I get an SQL error when running this. If I change the SQL Statement to
be "SELECT * FROM edenmaster" it works fine, so I know the connection
is OK.

What is the correct syntax for "SELECT * INTO" for MySQL?

Thanks!

[no@emails.thx]

Re: Using SELECT INTO to Backup a Database

On 26 Jul 2006 08:18:17 -0700, himilecyclist@y ahoo.com wrote:

>I am trying to use the SQL "SELECT INTO" to create a copy of a MySQL
>database.
>
>My PHP code looks like this:
>
><?php
>
>//Connect to server and database
>
>include ("Connection s/ovrs.inc");
>
>if (isset($_POST['cmdBackup']))
>{
> $sql = "SELECT * INTO edenbackup FROM edenmaster";
>
>
> $result = mysql_query($sq l) or die
>(ErrorCall(mys ql_error(),"frm Backup.php","1" ));
>
> echo "The Backup is Complete";
>}
>
>?>
>
>I get an SQL error when running this. If I change the SQL Statement to
>be "SELECT * FROM edenmaster" it works fine, so I know the connection
>is OK.
>
>What is the correct syntax for "SELECT * INTO" for MySQL?

Not sure which version of MySQL you are running but see

http://dev.mysql.com/doc/refman/4.1/en/select.html

In particular: "The SELECT ... INTO OUTFILE 'file_name' form of SELECT
writes the selected rows to a file. The file is created on the server
host, so you must have the FILE privilege to use this syntax."

So, the argument specifies a file name, not another database AND you
need to have write permission on the server wherever you are going ot
put the file.

Chris R.

[Miguel Cruz]

Re: Using SELECT INTO to Backup a Database

himilecyclist@y ahoo.com wrote:

What is the correct syntax for "SELECT * INTO" for MySQL?

INSERT INTO edenbackup SELECT * FROM edenmaster;

The two tables would of course have to have the same field types in the
same order for this to work. Otherwise you would have to specify the
field names.

miguel
--
Photos from 40 countries on 5 continents: http://travel.u.nu
Latest photos: Malaysia; Thailand; Singapore; Spain; Morocco
Airports of the world: http://airport.u.nu

[himilecyclist@yahoo.com]

Re: Using SELECT INTO to Backup a Database

Thanks for the suggestions!

The reference I am using, "Sams Teach Yourself SQL", indicates that
SELECT * INTO can be used to copy one table to another:

"To copy the contents of a table into a brand new table (one that is
created on-the-fly) you can use the SELECT INTO statement."

>From what I have read here, that is not an accurate description for

MySQL.

Our goal is some PHP code that will create a copy (for backup purposes)
of a MySQL table residing on a production Linux server. We would like
to create the copy on the user's local machine. Is there a better way
to accomplish that?

Thanks!

[Jeremy]

Re: Using SELECT INTO to Backup a Database

himilecyclist@y ahoo.com wrote:

>
Our goal is some PHP code that will create a copy (for backup purposes)
of a MySQL table residing on a production Linux server. We would like
to create the copy on the user's local machine. Is there a better way
to accomplish that?
>
Thanks!
>

I would use mysqldump instead. Run mysqldump and deliver the result as
a file download.

mysqldump reference:
<URL:http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html>

Jeremy

[Jerry Stuckle]

Re: Using SELECT INTO to Backup a Database

himilecyclist@y ahoo.com wrote:

Thanks for the suggestions!
>
The reference I am using, "Sams Teach Yourself SQL", indicates that
SELECT * INTO can be used to copy one table to another:
>
"To copy the contents of a table into a brand new table (one that is
created on-the-fly) you can use the SELECT INTO statement."
>

>>From what I have read here, that is not an accurate description for

MySQL.
>
Our goal is some PHP code that will create a copy (for backup purposes)
of a MySQL table residing on a production Linux server. We would like
to create the copy on the user's local machine. Is there a better way
to accomplish that?
>
Thanks!
>

Different databases often have some differences in SQL implementation.
AFAIK, SELECT * INTO ... is not standard SQL, where as INSERT INTO xxx
SELECT... is.

You should always use manuals written for your particular database.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Richard Levasseur]

Re: Using SELECT INTO to Backup a Database


himilecyclist@y ahoo.com wrote:

Thanks for the suggestions!
>
The reference I am using, "Sams Teach Yourself SQL", indicates that
SELECT * INTO can be used to copy one table to another:
>
"To copy the contents of a table into a brand new table (one that is
created on-the-fly) you can use the SELECT INTO statement."
>

From what I have read here, that is not an accurate description for

MySQL.
>
Our goal is some PHP code that will create a copy (for backup purposes)
of a MySQL table residing on a production Linux server. We would like
to create the copy on the user's local machine. Is there a better way
to accomplish that?
>
Thanks!

I suggest using MySQL Backup, written in perl. It lets you backup on a
per-table per-database basis, archives, compresses, and emails them to
you, as well as rotates previous backups. Its very handy.

http://worldcommunity.com/opensource/utilities/mysql_backup.html