LinuxQuestions.org
Register a domain and help support LQ
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices

Reply
 
Search this Thread
Old 08-15-2011, 03:57 PM   #1
karnac01
LQ Newbie
 
Registered: Dec 2006
Location: Florida
Distribution: Ubuntu and CentOS
Posts: 23

Rep: Reputation: 0
Assign MySql Database by port numbers


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.
 
Old 08-15-2011, 04:45 PM   #2
dafydd2277
Member
 
Registered: Mar 2010
Posts: 58

Rep: Reputation: 5
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
 
Old 08-15-2011, 06:24 PM   #3
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.5, Centos 5.10
Posts: 16,267

Rep: Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028
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...
 
Old 08-15-2011, 09:06 PM   #4
xonogenic
LQ Newbie
 
Registered: Feb 2006
Posts: 25

Rep: Reputation: 2
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.
 
Old 08-15-2011, 11:21 PM   #5
blade_x
LQ Newbie
 
Registered: Dec 2006
Posts: 3

Rep: Reputation: 0
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.
 
Old 08-16-2011, 12:46 AM   #6
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Rep: Reputation: 282Reputation: 282Reputation: 282
Quote:
Originally Posted by karnac01 View Post
... Thinking of it in a network security and user access mind of thinking.
How do you see this improve network security and user access? I'm not the specialist here and like to learn from it.
 
Old 08-16-2011, 09:01 AM   #7
pgreninger
LQ Newbie
 
Registered: Apr 2009
Location: Boston, MA
Distribution: RHEL5
Posts: 3

Rep: Reputation: 0
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
 
Old 08-20-2011, 12:14 PM   #8
gillbertiddio
Member
 
Registered: Sep 2009
Posts: 33

Rep: Reputation: 6
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.

*
Code:
 GRANT ALL ON company_A_db.* TO 'user_A' @ '192.168.1.1' IDENTIFIED BY 'water';
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Trouble when connecting to mysql database remotely via port 3306 iamsamhk Linux - Newbie 3 07-23-2011 12:25 PM
Assign fixed port to nlockmgr rwcooper Slackware 11 06-15-2010 06:33 AM
Should I store CC numbers in my database? NaCo General 7 12-30-2008 09:51 AM
trying to port a database in cliiper to mysql isamuede Linux - Software 4 07-29-2008 02:04 PM
How can i copt the MYSQL Database to a other server (Database) halvorls Linux - Server 3 07-27-2008 09:19 PM


All times are GMT -5. The time now is 09:10 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration