How do I copy a table for backup and restore purposes

Hi there,

I have a small database running in mySQL on a web-hotel. I use PHP to access
the database.

Unfortunately, the web-hotel does not offer any possibilities for backing up
databases/tables, other than copying the tables manually one by one using
the facilities of phpMyAdmin.

What I would ideally like to have is a PHP routine that that makes an exact
copy of a specified table. I could then easily use that to createl copies of
all the relevant tables in the database.

I thought this would be a relatively easy task, but this does not appear to
be the case. Or maybe I am blind. I can see in phpMyAdmin the SQL statements
needed to create a new empty table and copy the content from the original
table. I could of course copy this code into PHP, but what then if I change
a table? Then I need to change this code as well.

Is there a smarter way?

Flemming

Re: How do I copy a table for backup and restore purposes

Flemming wrote:

> I thought this would be a relatively easy task, but this does not appear
> to be the case. Or maybe I am blind. I can see in phpMyAdmin the SQL
> statements needed to create a new empty table and copy the content from
> the original table. I could of course copy this code into PHP, but what
> then if I change a table? Then I need to change this code as well.

You can try with the following SQL statement:

CREATE TABLE backuptable SELECT * FROM table_to_backup

You can use it easilly both from phpMyAdmin and from your custom made php script.

--

//Aho

Re: How do I copy a table for backup and restore purposes

> You can try with the following SQL statement:
>
> CREATE TABLE backuptable SELECT * FROM table_to_backup
>

Thank you for that suggestion. It works and creates a nice copy of all data
in a similar table.

However, it does not copy the index. Maybe there is an equally elegant way
to ensure that the index is copied as well? Or maybe I don't need it if I do
the restore by the following SQL statements:

TRUNCATE TABLE table_to_backup
INSERT INTO table_to_backup SELECT * FROM backuptable

Any suggestions as to whether this will work?

Regards

Flemming

Re: How do I copy a table for backup and restore purposes

On 22 Nov, 19:31, "Flemming" wrote:
> Hi there,
>
> I have a small database running in mySQL on a web-hotel. I use PHP to access
> the database.
>
> Unfortunately, the web-hotel does not offer any possibilities for backing up
> databases/tables, other than copying the tables manually one by one using
> the facilities of phpMyAdmin.
>
> What I would ideally like to have is a PHP routine that that makes an exact
> copy of a specified table. I could then easily use that to createl copies of
> all the relevant tables in the database.
>
> I thought this would be a relatively easy task, but this does not appear to
> be the case. Or maybe I am blind. I can see in phpMyAdmin the SQL statements
> needed to create a new empty table and copy the content from the original
> table. I could of course copy this code into PHP, but what then if I change
> a table? Then I need to change this code as well.
>
> Is there a smarter way?
>
> Flemming

phpmyadmin has an export tab in the database view. This will export
all the tables, complete with instructions to create the relevant
indexes

Re: How do I copy a table for backup and restore purposes

Post removed (X-No-Archive: yes)

Re: How do I copy a table for backup and restore purposes

>>
>> Is there a smarter way?
>>
>> Flemming
>
> phpmyadmin has an export tab in the database view. This will export
> all the tables, complete with instructions to create the relevant
> indexes

Thank you for making we aware of the possibility in phpmyadmin to backup a
full database in one go. I missed that. It is certainly helpful to me, even
though it's not via PHP.

Flemming

Re: How do I copy a table for backup and restore purposes

>>Is there a smarter way?
>>
>>Flemming
>>
>
>
> I'd run a separate command to create your new table, then use
>
> SELECT * INTO NewTable FROM OldTable;
>
> Of course I'd do a regular backup of your entire database first, just for
> precautionary measures.
>
Thank you your reply.

Is there a way to create (an empty) NewTable in SQL with indexes and all
from OldTable?

Flemming

Re: How do I copy a table for backup and restore purposes

Flemming wrote:
>>>
>>> Is there a smarter way?
>>>
>>> Flemming
>>
>> phpmyadmin has an export tab in the database view. This will export
>> all the tables, complete with instructions to create the relevant
>> indexes
>
> Thank you for making we aware of the possibility in phpmyadmin to backup
> a full database in one go. I missed that. It is certainly helpful to me,
> even though it's not via PHP.

You could download phpmyadmin and check the code they use to do it and then
you can write your own GPL code that uses the code from phpmyadmin.


--

//Aho

Re: How do I copy a table for backup and restore purposes

>>>>
>>>> Is there a smarter way?
>>>>
>>>> Flemming
>>>
>>> phpmyadmin has an export tab in the database view. This will export
>>> all the tables, complete with instructions to create the relevant
>>> indexes
>>
>> Thank you for making we aware of the possibility in phpmyadmin to backup
>> a full database in one go. I missed that. It is certainly helpful to me,
>> even though it's not via PHP.
>
> You could download phpmyadmin and check the code they use to do it and
> then
> you can write your own GPL code that uses the code from phpmyadmin.
>

Thank you for that suggestion. That must be the ultimate solution. However,
it would have been easier if someone could point at a CopyTable (using PHP
and mySQL) function somewhere out there on the web.

I'm still hoping for the ultimate tip.

Flemming

Re: How do I copy a table for backup and restore purposes

Post removed (X-No-Archive: yes)

Re: How do I copy a table for backup and restore purposes

>>Is there a way to create (an empty) NewTable in SQL with indexes and all
>>from OldTable?
>>
>>Flemming
>>
>
> I usually create my own "config" files, that I use similar to restoring
> from a
> backup. As an example, I'll create a file named OldTable.sql that includes
> the
> SQL commands for creating the table and adding its indexes, and using a
> command
> like this to create the table...
>
> mysql -u username -p database_name < /path/to/OldTable.sql
>
> If I need to create a similar or duplicate table, I can copy that to a
> NewTable.sql file, and edit that with whatever changes I needed. Then add
> that
> one using..
>
> mysql -u username -p database_name < /path/to/NewTable.sql
>
> That way you can duplicate a table design a lot easier.
>
Thank you for another helpful suggestion. I'll keep that in mind and use it
in the future.

Flemming