LinuxQuestions.org
Support LQ: Use code LQ3 and save $3 on Domain Registration
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices

Reply
 
Search this Thread
Old 09-17-2009, 08:38 AM   #1
winairmvs
Member
 
Registered: Aug 2009
Posts: 42

Rep: Reputation: 16
Connecting to central Mysql database server


I didn't know how to phrase the title to properly frame the question, but here it goes:

We are hoping to build a central database server so that we can take all the singular instances of mysql off our web servers and have the data stored in one place (we also run a number of syslogging servers that use mysql, netflow servers that use mysql, and many other servers that have mysql tables that we would like to consolidate as well). Now I have run into the problem that requires all the customer web sites to change the configuration for their connection to the mysql server from a local connection to a remote connection, which is something we are trying to avoid. I am wondering if there is a software suite or a function of mysql itself that allows you to present the remote database information as local databases to a server, allowing customer configurations to remain intact, as if the mysql server never moved off the local machine? Or a different question, can you configure mysql server to take local requests and forward them to a central mysql server?

Thanks in advance.

Last edited by winairmvs; 09-17-2009 at 08:39 AM.
 
Old 09-17-2009, 08:46 AM   #2
EricTRA
Guru
 
Registered: May 2009
Location: Gibraltar, Gibraltar
Distribution: Fedora 20 with Awesome WM
Posts: 6,805
Blog Entries: 1

Rep: Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290
Hello,

If you're using MySQL 5 you might have a look at the master-master replication. That way you can have a local instance of your database, remaining on your web/application server, and synchronized both ways. This allows you to maintain your local configuration, whilst having an exact copy on a centralized MySQL server. Any maintenance you do on the centralized server gets replicated instantly to the local database instance. Not sure if it's a solution you might consider but it looks like this would do the trick.

Kind regards,

Eric
 
Old 09-17-2009, 10:30 AM   #3
winairmvs
Member
 
Registered: Aug 2009
Posts: 42

Original Poster
Rep: Reputation: 16
Quote:
Originally Posted by EricTRA View Post
Hello,

If you're using MySQL 5 you might have a look at the master-master replication. That way you can have a local instance of your database, remaining on your web/application server, and synchronized both ways. This allows you to maintain your local configuration, whilst having an exact copy on a centralized MySQL server. Any maintenance you do on the centralized server gets replicated instantly to the local database instance. Not sure if it's a solution you might consider but it looks like this would do the trick.

Kind regards,

Eric
That would work with two servers, but I have a couple dozen that need to connect back, and I really don't want a local copy of 24+ server's databases stored locally on every machine. Thanks anyways!
 
Old 09-17-2009, 11:04 AM   #4
EricTRA
Guru
 
Registered: May 2009
Location: Gibraltar, Gibraltar
Distribution: Fedora 20 with Awesome WM
Posts: 6,805
Blog Entries: 1

Rep: Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290
Hello again,

Actually you can set up master-master replication with as many hosts as you want, be it 4, 12, 24. The difference is that they would replicate in a circle. You can read a pretty good example setup with 4 nodes here. I'm not a MySQL guru but it might be possible to configure database synchronization depending on host basis, since you do replication on a database level. In the config you have to indicate which databases you'll be synchronizing.

Maybe there are some MySQL gurus here on LinuxQuestions and if there are I'm sure they'll pickup on the challenge.

Kind regards,

Eric
 
Old 09-17-2009, 11:34 AM   #5
winairmvs
Member
 
Registered: Aug 2009
Posts: 42

Original Poster
Rep: Reputation: 16
Quote:
Originally Posted by EricTRA View Post
Hello again,

Actually you can set up master-master replication with as many hosts as you want, be it 4, 12, 24. The difference is that they would replicate in a circle. You can read a pretty good example setup with 4 nodes here. I'm not a MySQL guru but it might be possible to configure database synchronization depending on host basis, since you do replication on a database level. In the config you have to indicate which databases you'll be synchronizing.

Maybe there are some MySQL gurus here on LinuxQuestions and if there are I'm sure they'll pickup on the challenge.

Kind regards,

Eric
Interesting article, thanks for the link. It seems to be a bit of an administrative challenge to get something like that working, and probably more difficult to troubleshoot. I would much rather have two servers replicating to each other to create a load balanced database system, and have the ability to connect a web server to the system on the fly.

Last edited by winairmvs; 09-17-2009 at 11:42 AM.
 
Old 09-17-2009, 11:44 AM   #6
EricTRA
Guru
 
Registered: May 2009
Location: Gibraltar, Gibraltar
Distribution: Fedora 20 with Awesome WM
Posts: 6,805
Blog Entries: 1

Rep: Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290Reputation: 1290
It is indeed an administrative task to perform but once it's up and running very easy to administer and since setting it up consumes time one doesn't easily forget how it works so troubleshooting is easy also. I've got master-master replication working on two server constantly for about 4 months now without any problem or failure. I've had three other server connected for a while to do some testing and all worked great. But I have to admit that all databases were replicated to all servers.

Kind regards,

Eric
 
Old 09-17-2009, 01:29 PM   #7
jlinkels
Senior Member
 
Registered: Oct 2003
Location: Bonaire
Distribution: Debian Lenny/Squeeze/Wheezy/Sid
Posts: 4,087

Rep: Reputation: 492Reputation: 492Reputation: 492Reputation: 492Reputation: 492
You are not alone with this problem. As it seems, the caveat is that you want to change the current connection to another host over TCP. That is difficult, because when you connect MySQL to localhost it uses a Unix socket, and not a TCP connection to localhost. Setting up a tunnel is therefor useless.

I found this article: http://bugs.mysql.com/bug.php?id=31577 where a solution is explained at the one but last post using socat.

jlinkels
 
Old 09-17-2009, 02:40 PM   #8
winairmvs
Member
 
Registered: Aug 2009
Posts: 42

Original Poster
Rep: Reputation: 16
Quote:
Originally Posted by jlinkels View Post
You are not alone with this problem. As it seems, the caveat is that you want to change the current connection to another host over TCP. That is difficult, because when you connect MySQL to localhost it uses a Unix socket, and not a TCP connection to localhost. Setting up a tunnel is therefor useless.

I found this article: http://bugs.mysql.com/bug.php?id=31577 where a solution is explained at the one but last post using socat.

jlinkels
Ah, I didn't even think about the socket connection. That was a very helpful read, thanks jlinkels! One commenter mentioned using mysql proxy to facilitate the connection. Can someone elaborate on how that would work for a mysql newbie like myself?
 
Old 09-17-2009, 02:50 PM   #9
jlinkels
Senior Member
 
Registered: Oct 2003
Location: Bonaire
Distribution: Debian Lenny/Squeeze/Wheezy/Sid
Posts: 4,087

Rep: Reputation: 492Reputation: 492Reputation: 492Reputation: 492Reputation: 492
This is what I think it the proposed solution:

[23 Apr 15:35] Marc Perkel
Here's a work around until MySQL stops ignoring the REAL BUG!!!

socat
UNIX-LISTEN:/var/lib/mysql/mysql.sock,fork,reuseaddr,unlink-early,user=mysql,group=mysql,m
ode=777 TCP:mysql.example.com:3306 &


As far as I understood proxy was not an option. This proposal by Marc Perkel is easy to setup and to test. Apparently you have to find and install socat Dunno what distro you are using on your web server machines, but it is an installable pacakge in Debian, I would be surprised if it was not available on other.

jlinkels
 
Old 09-18-2009, 08:04 AM   #10
winairmvs
Member
 
Registered: Aug 2009
Posts: 42

Original Poster
Rep: Reputation: 16
Socat RPM list

Quote:
Originally Posted by jlinkels View Post
This is what I think it the proposed solution:

[23 Apr 15:35] Marc Perkel
Here's a work around until MySQL stops ignoring the REAL BUG!!!

socat
UNIX-LISTEN:/var/lib/mysql/mysql.sock,fork,reuseaddr,unlink-early,user=mysql,group=mysql,m
ode=777 TCP:mysql.example.com:3306 &


As far as I understood proxy was not an option. This proposal by Marc Perkel is easy to setup and to test. Apparently you have to find and install socat Dunno what distro you are using on your web server machines, but it is an installable pacakge in Debian, I would be surprised if it was not available on other.

jlinkels
Just for everyone's benefit, there is a list of socat rpm's at these sites:

http://dag.wieers.com/rpm/packages/socat/
http://dries.ulyssis.org/rpm/packages/socat/info.html

I was able to get this working on redhat 5.3 without much trouble.
 
  


Reply

Tags
connector, forwarding, mysql, odbc, socket, tcp


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
Postfix connecting through MySQL to user database/directories MangaManiac Debian 3 01-13-2006 03:00 AM
connecting to a MySQL database hsa Linux - General 3 09-16-2005 06:34 AM
probs with connecting to mysql database using php ankscorek Linux - Software 4 08-16-2005 07:40 PM
connecting to MYSQL remote database opsraja Linux - Software 1 02-18-2005 05:52 AM
Connecting Mysql Database on remote server akamol Programming 4 10-16-2003 11:46 AM


All times are GMT -5. The time now is 02:46 AM.

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