MySQL copy table to remote database with PHP

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

MySQL copy table to remote database with PHP

Post by batfastad »

Hi everyone

I have a pretty specific question here relating to the ability to copy databases and tables between servers.
We have an intranet MySQL server but one of the database tables needs to also live on our ISPs MySQL.

At the moment I export our intranet version of the table with phpMyAdmin, then import the SQL file into the remote phpMyAdmin.

I was wondering if there's a way to do this automatically with PHP?

For example there's the Copy database to option in phpMyAdmin and I had a bit of a dig around the source files to see how it was achieved but couldn't get anything.
Is there a MySQL function to let me do this in one hit?

Or would I be best off doing it manually with this process...
1) Loop through the records in the local copy, building a MySQL statement as I go
2) Truncate the remote table
3) Run the MySQL statement which INSERTS all the records

We're talking 50-100 records here so the above would work ok.

I was wondering if anyone knew of a better way to do this?

Thanks
Ben
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

If the remote database is read only, this could be easily accomplished using MySQL replication assuming the ISP host you are working with would be willing to do some config on their end.
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Post by batfastad »

Hi thanks for the reply.
We have full access to the remote database as well.

MySQL replication is something I will look into.

But you might be wondering why I don't just use only the remote database, rather than having to keep the 2 synchronised??

Well this particular database is very important to the intranet app, and the reason we run it on the intranet is so performance is fast. This database would be getting called by most of our intranet pages so if it was doing a lookup to the remote DB all the time over our intranet connection, we may as well just host the entire thing on the remote server.

Since updates to the table in question are made by only a couple of people, and only every couple of weeks I think I will try my method of truncating then running INSERT statements to import the data into the remote DB. I will try that to see what performance is like.

Failing that, MySQL replication seems to be a very powerful way of accomplishing this.

Thanks
Ben
Post Reply