Linux - SoftwareThis 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
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
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?
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.
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!
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.
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.
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.
Distribution: Debian /Jessie/Stretch/Sid, Linux Mint DE
Posts: 5,195
Rep:
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.
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.
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?
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.
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:
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.