Remote Database Table Copier (
Page 1 of 7 )
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, focusing 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.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.
/ 14 
