Remote Database Table Copier
(Page 1 of 7 )
While most sites use PHP for little more than selecting and displaying information dynamically on various web pages, the language provides a very robust and flexible interface for databases in general and MySQL specifically, and that interface can be used for much more than simple information display. This fact is not news to most PHP developers, since the database connectivity offered by PHP is widely touted as one of its greatest strengths. Unfortunately, much of the functionality provided for database access is rarely used.
One place where PHP’s advanced database access features are used are in any of the several existing script-based MySQL database administration tools such as phpMyAdmin and mysql-admin. These applications attempt to provide a convenient script-based alternative to MySQL’s native command line access.
In addition to these tools, the advanced database functionality in PHP affords the developer a great deal of latitude in developing highly useful tools for database manipulation across the internet. This brings me to the real subject of this column -- the script-based server-to-server database table copier.
I would guess that most developers who lack an infinite amount of time to study and play with the tools of the trade approach technology on an as-needed basis. They look for solutions to specific problems as they are needed, and never get an opportunity to explore the possibilities made available through the growing variety of tools available. Fortunately, I recently had a problem that allowed me to explore some concepts that I had thought about, but never been able to test or explore due to the familiar time constraints, which led to the database copier.
The problem seemed reasonably simple: I had built a demonstration of a web-based application on one of my servers, allowing my client to rent the space there until they were able to set up their own hosting arrangement. This was not a problem, though the site was collecting more and more data the longer they waited to move. When the day finally came, the new ISP informed me that, for security reasons, I could not be given shell access to the web space, that I could have only FTP access to the web root.
How was I to build tables? "Through our convenient web interface."
How was I to load the legacy data? "Through our convenient web interface."
Load thousands of rows through the web interface? I think not.
Opportunity to tinker? I think so.
My solution was something that I had imagined while looking through the PHP manual and wondering why you would ever need to use the connection identifier returned by the mysql_connect() function. You would need it if you need to connect to more than one database server or database in the context of a single script--and that would be terribly useful if you ever needed to copy information from one database to another, even if they were on separate servers.
So, I had a plan: I would create a single script-based tool for the sole purpose of copying data from a MySQL database on any host to a database on a separate host, and do so without having to dump the data to a text file from the source database to be uploaded to the target. As the project progressed, it occurred to me that I would need a few more features to be really useful, like the ability to build target tables on the fly, based on the structure of the source tables.
The purpose of this article is to describe the methods used to create the database copying tool, and outline features of the technologies used in its creation. Specifically, I focus on the following topics: MySQL permissions for remote connections; PHP support for simultaneous connections to multiple databases; building dynamic INSERT and CREATE TABLE statements with PHP.
The result of that labor was an interesting insight into a number of different possibilities and limitations of PHP, MySQL, and the internet. . . and a script-based tool for copying databases from one server to another.
Note: Throughout the article, I will refer to two databases as the "source" and "target" databases, meaning the databases that the data will be copied from and to respectively. Also, I often use the term "database" to describe both databases and database servers (see previous sentence). I believe the context will make my usage clear, and I will try to make the distinction explicit where necessary.
Next: The Permissions Problem >>
More MySQL Articles
More By Stephen Junker
/ 12 
