Home arrow MySQL arrow Page 2 - Remote Database Table Copier

The Permissions Problem - MySQL

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.

  1. Remote Database Table Copier
  2. The Permissions Problem
  3. Connecting the World, Two Servers at a Time
  4. Copying the Data
  5. Adding Some Options
  6. Summary
  7. Sample Code
By: Stephen Junker
Rating: starstarstarstarstar / 14
May 07, 2001

print this article


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.






Read source data



Insert rows in target table



Option to clear table before copying



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.

>>> More MySQL Articles          >>> More By Stephen Junker

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: