Remote Database Table Copier - The Permissions Problem (
Page 2 of 7 )
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 
