MySQL, backup a table in PHP

Hi,

Assuming the table MYTABLE, i want to run a script to backup the table.
But there does not seem to be a straight forward function in MySQL to
achieve it,

Something like
COPY TABLE MYTABLE, bkpMYTABLE;

What would be the easiest way to do that using php?

I know that i can create a table bkpMYTABLE and INSERT the data from MYTABLE
to bkpMYTABLE but the problem is that i will be using the script to run
updates so i will not always know the structure of the data that the user is
copying, (because the script(s) will be precisely used to update the
structure of the table).

Maybe there is a function to retrieve the current structure and then copy
the data?

Also is there a way of testing if a table exists? and to test if a field
within that table exists?

Many thanks

Simon

On Fri, 19 Mar 2004 12:15:23 +0200, "Sims" <siminfrance@hotmail.com> wrote:
[color=blue]
>Assuming the table MYTABLE, i want to run a script to backup the table.
>But there does not seem to be a straight forward function in MySQL to
>achieve it,
>
>Something like
>COPY TABLE MYTABLE, bkpMYTABLE;
>
>What would be the easiest way to do that using php?
>
>I know that i can create a table bkpMYTABLE and INSERT the data from MYTABLE
>to bkpMYTABLE but the problem is that i will be using the script to run
>updates so i will not always know the structure of the data that the user is
>copying, (because the script(s) will be precisely used to update the
>structure of the table).
>
>Maybe there is a function to retrieve the current structure and then copy
>the data?[/color]

CREATE TABLE bkpMYTABLE
AS
SELECT * FROM MYTABLE;
[color=blue]
>Also is there a way of testing if a table exists? and to test if a field
>within that table exists?[/color]

You can run DESCRIBE as a mysql_query() and get a result set back which will
contain information on the table.

You might also want to look at the mysqldump command, which would be more
convenient for backups as it actually gets the data out of the database into a
file. Or mysqlhotcopy if you have access to do it at the datafile level, which
would be quicker.

--
Andy Hassall <andy@andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>

"Sims" <siminfrance@hotmail.com> wrote in message
news:c3eh94$27gpc9$1@ID-162430.news.uni-berlin.de...[color=blue]
> Hi,
>
> Assuming the table MYTABLE, i want to run a script to backup the table.
> But there does not seem to be a straight forward function in MySQL to
> achieve it,
>
> Something like
> COPY TABLE MYTABLE, bkpMYTABLE;
>
> What would be the easiest way to do that using php?
>
> I know that i can create a table bkpMYTABLE and INSERT the data from[/color]
MYTABLE[color=blue]
> to bkpMYTABLE but the problem is that i will be using the script to run
> updates so i will not always know the structure of the data that the user[/color]
is[color=blue]
> copying, (because the script(s) will be precisely used to update the
> structure of the table).
>
> Maybe there is a function to retrieve the current structure and then copy
> the data?
>
> Also is there a way of testing if a table exists? and to test if a field
> within that table exists?
>
> Many thanks
>
> Simon[/color]

How about doing a dump of the database or table? It's safer than just
replicating the table.
http://www.mysql.com/doc/en/mysqldump.html
shell> mysqldump [options] database [tables]
OR mysqldump [options] --databases [options] DB1 [DB2 DB3...]
OR mysqldump [options] --all-databases [options]Then zip and copy it
somewhere safe or mail it to yourself.I do something similar with all our
clients postgres databases using cron and wget.--
Andrew @ Rockface
np: [winamp not running]
andrew@rockface-records.co.uk
www.rockface-records.co.uk

> Assuming the table MYTABLE, i want to run a script to backup the table.[color=blue]
> But there does not seem to be a straight forward function in MySQL to
> achieve it,
>
> Something like
> COPY TABLE MYTABLE, bkpMYTABLE;[/color]

CREATE TABLE bpkMYTABLE SELECT * FROM MYTABLE;

JP

--
Sorry, <devnull@cauce.org> is een "spam trap".
E-mail adres is <jpk"at"akamail.com>, waarbij "at" = @.

But how would you do this, as an automated function, to say run everynight
at 23:00? Backing up the DB in question to a remote server.? I got two
different server with two ISP's, so I don't have control over the servers,
and thus can't set them up as master / slave?

--

Kind Regards
Rudi Ahlers
+27 (82) 926 1689

Greater love has no one than this, that he lay down his life for his friends
(John 15:13).
"Jan Pieter Kunst" <devnull@cauce.org> wrote in message
news:devnull-8F1143.19235806042004@news1.news.xs4all.nl...[color=blue]
> Assuming the table MYTABLE, i want to run a script to backup the table.
> But there does not seem to be a straight forward function in MySQL to
> achieve it,
>
> Something like
> COPY TABLE MYTABLE, bkpMYTABLE;[/color]

CREATE TABLE bpkMYTABLE SELECT * FROM MYTABLE;

JP

--
Sorry, <devnull@cauce.org> is een "spam trap".
E-mail adres is <jpk"at"akamail.com>, waarbij "at" = @.

In article <sbednf3XdOozjO7dRVn-sA@is.co.za>,
"Rudi Ahlers" <SP4M_Rudi@SP4M_Bonzai.org.za_SP4M> wrote:
[color=blue]
> But how would you do this, as an automated function, to say run everynight
> at 23:00? Backing up the DB in question to a remote server.? I got two
> different server with two ISP's, so I don't have control over the servers,
> and thus can't set them up as master / slave?[/color]

I do stuff like that with a few simple shell scripts (executed at fixed
times with cron):

dump database on server 1:
[server1:~] mysqldump database > database.sql

remote copy the dump from server 1 to server 2:
[server2:~] scp server1:~/database.sql .

read the dump on server 2:
[server2:~] mysql -u user --password=secret database < database.sql

Of course you need shell access at both ISP's to be able to do this.

JP

--
Sorry, <devnull@cauce.org> is een "spam trap".
E-mail adres is <jpk"at"akamail.com>, waarbij "at" = @.