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.
One problem with this database table copying scheme is that you need to have remote access permissions on at least one of the two servers you’re going to access, unless the two databases are on the same server. Most ISPs will set up MySQL databases so they can only be accessed from a specific account from the local server (localhost), and with good reason. This makes perfect sense for most hosting situations where the databases are used to serve data to web pages from a local machine.
However, in this case, either one or both of the MySQL servers will require permission for a remote login from the machine that’s running the database copier script. It doesn’t matter whether it’s the source, the target, or a completely separate machine that’s running the script; you will need to be able to set up a remote access permission on whatever machines you need to access that are not running the script.
To copy data, you will need the following permissions.
Permission
Database
Reason
SELECT
Source
Read source data
INSERT
Target
Insert rows in target table
DELETE
Target
Option to clear table before copying
CREATE
Target
Option to create table before copying
MySQL has an excellent security system that can be set up to specify the commands allowed for a given user from a specific host address, and the databases and tables accessible to that user. In addition, each logon is password protected.
To set up a remote logon permission to a MySQL server, you will need to have permission to grant rights to other users on your MySQL server. (If you’re using an ISP, it’s unlikely that you have grant rights on the server, unless you have a private server space and are running your own MySQL server.) The syntax of the grant command for MySQL is as follows:
GRANT [command list] on [database].[table] to [user]@[host] identified by ‘[password]’;
If I were accessing a database at some other server from
my personal domain (junkman.org) for access to all tables in the ‘demo’ database, the command on the remote MySQL server would look something like this:
GRANT SELECT, INSERT ON demo.* TO demo@junkman.org IDENTIFIED BY ‘devshed’;
Keep in mind that you will need to grant remote access
permissions on the server that will NOT be running the copier script, because the server that’s running the script will be connecting to localhost, which should already have appropriate permissions to select, insert, delete, and create. Also, the user name specified is only for the purpose of authentication on the MySQL server, and is not related to any UNIX or NT authentication on the machine where the server is running.
I would strongly recommend reading the MySQL manual (available in a variety of formats from www.mysql.com) on the subject of permissions and security. Do not take the subject lightly, or make exceptions for ease of use (i.e.: GRANT ALL_PRIVILEGES ON *.* TO root@*;). You (and your clients) would hate to log in someday and find all of your data corrupted or missing!
For experts only: If you need to copy data between ISP-hosted server on which you only have FTP permissions, you can do the following: 1) Set up a MySQL server on your home machine, if you haven’t already 2) Allow remote access permissions to the local MySQL server from both ISPs. 3) Set up your router or firewall to allow requests on port 3306 to your local server 4) Run the script at the source ISP to copy data from the source to your local server 5) Run the script at the destination ISP to copy the data from your local server to the target server.
/ 14 
