Hello,

Do not know if this will work or make sense, but here goes:

I am using PHP and MySQL with multiple databases. I would like to know if I can setup MySQL security feature that can assign specific port numbers to each database. Example:

Database A (15 tables) Port 27001
Database B (9 tables) Port 27002
Database C (16 tables) Port 27003

Is this possible and/or can it be done? Thinking of it in a network security and user access mind of thinking.

Hi, karnac,

First, giving the MySQL Documentation a good going over is essential in this case. I would particularly recommend Starting and Troubleshooting the MySQL Server. (Note that I've linked to the section for version 5.5. If you're using a different version, please make sure you're looking at the correct documentation.)

Having said that, I don't believe you can associate a port number with an individual database inside a running mysqld daemon. A daemon is prepared to listen to one port, and one port only. In order to use multiple ports, you would have to set up multiple daemons, with the associated multiple base & data directories.

Finally, find any *.cnf files in your mysql directories to look at pre-start configuration files. Researching these will give other clues about general and daemon-specific settings.

Good luck!
dafydd

Indeed, a mysql daemon is associated with one physical database; what you are technically talking about there is multiple schemas (often loosely referred to as databases ...) inside a single physical db.
If you want separate ports, you'll have to install a new copy of mysql for each database...

I had to do this awhile ago actually as part of a muliple master/single slave replication setup. The answer I believe you are looking for is here http://dev.mysql.com/doc/refman/5.0/...qld-multi.html . Mysqld_multi is quite easy to use, though it does take a little practice to get everything going how you expect it too. If you need any help with the implementation, I can probably help you.

Karnac01,

Im not exactly sure I understood what you had in mind when you asked your q. You said you wanted to have a SECURITY Feature that can assign ports to each of a database. I do have rather some suggestion.

IF Databases A,B, and C exist on one database server machine,
THEN I have, in my experience, created multiple Database SERVERS on one machine, i compiled mysql 4.0.16 and 5.5.8 on a single machine but prefixed 4___ and 5___ to every configuration on the installation. This means that while the machine is on, Mysql4 and Mysql5 is up, and to access it Mysql4 i used the port 43306. Mysql5 is port 53306. The solution involved compiling the source of the mysql, and prefixing 4 or 5 on all the directories, and even ports.

i.e. mysql4 compile configuration

./configure --prefix=/usr/local/mysql4 --exec-prefix=/usr/local/mysql4 --with-unix-socket-path=/tmp/mysql4.sock --with-mysql-user=mysql --disable-maintainer-mode --without-debug --without-bench --without-comment --with-tcp-port=43306


i.e. mysql5 compile configuration

file CMakeCache.txt:
CMAKE_INSTALL_PREFIX:PATH=/usr/local/mysql5
MYSQL_DATADIR:PATH=/usr/local/mysql5/data
MYSQL_TCP_PORT:UNINITIALIZED=53306
MYSQL_UNIX_ADDR:UNINITIALIZED=/tmp/mysql5.sock
SYSCONFDIR:PATH=/etc/mysql5


IF Databases A,B, and C exists on Different machines,
THEN its up to the PHP to connect to each machine. Have different mysql connection handles for each of the server, and connect, just as you would on separate and independent database servers.

I would create classes of database connections which i can reuse.



OTHER Suggestions.

I am not familiar with mysql proxy, but, maybe with port forwarding, it could work.

How do you see this improve network security and user access? I'm not the specialist here and like to learn from it.

Would port forwarding be of any use? I'm not sure if you can associate port numbers with specific databases but it will provide additional security.
http://dev.mysql.com/doc/refman/5.5/...s-and-ssh.html
http://enginsite.com/tunnel_howto.htm

I think I get it - the security concern is that user A has access to user B's tables and you don't trust grant statements*...

In which case I would suggest creating virtual machines and place the LAMP's on seperate virtual machines anyway. Installation would be easier and there is no concern that user A could ever elevate permissions and access user B's data.

*