MySQL, backup a table in PHP

[Sims]

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

[Andy Hassall]

On Fri, 19 Mar 2004 12:15:23 +0200, "Sims" <si*********@ho tmail.com> wrote:

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?
CREATE TABLE bkpMYTABLE
AS
SELECT * FROM MYTABLE;
Also is there a way of testing if a table exists? and to test if a field
within that table exists?

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 <an**@andyh.co. uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk > / <http://www.andyhsoftwa re.co.uk/space>

[Andrew]

"Sims" <si*********@ho tmail.com> wrote in message
news:c3******** *****@ID-162430.news.uni-berlin.de...

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

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]
an****@rockface-records.co.uk
www.rockface-records.co.uk

[Jan Pieter Kunst]

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

CREATE TABLE bpkMYTABLE SELECT * FROM MYTABLE;

JP

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

[Rudi Ahlers]

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

Greater love has no one than this, that he lay down his life for his friends
(John 15:13).
"Jan Pieter Kunst" <de*****@cauce. org> wrote in message
news:de******** *************** ****@news1.news .xs4all.nl...

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;

CREATE TABLE bpkMYTABLE SELECT * FROM MYTABLE;

JP

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

[Jan Pieter Kunst]

In article <sb************ ********@is.co. za>,
"Rudi Ahlers" <SP4M_Rudi@SP4M _Bonzai.org.za_ SP4M> wrote:

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?

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, <de*****@cauce. org> is een "spam trap".
E-mail adres is <jpk"at"akamail .com>, waarbij "at" = @.