0

In order to lower system usage I'm planning to use another server to store my databases. so how can I redirect MySQL queries to another server or make MySQL to store databases on another server?

Zim3r
  • 1,384
  • 5
  • 23
  • 45

3 Answers3

1

You can use something like mysql proxy link1 link2 for it. But it's not very well supported. Additionally, decrease in performance is somewhat significant. If possible, I would suggest you to simply tell your application to connect to the actual mysql server with a choice. For example, with PHP, there is mysqlnd_ms which allows multiple mysql servers to be added and it load balances among them.

Grumpy
  • 2,939
  • 17
  • 23
  • Thank you, I'll have you advice but what do you think about forwarding all connections on port 3306 to a remote server. is that possible? – Zim3r Dec 22 '12 at 11:25
  • @Zim3r you can opt to just forward everything through something like haproxy as well. But you should still expect some decrease in performance as you are creating additional task though less than mysql proxy. – Grumpy Dec 22 '12 at 12:50
  • I don't see a reason here to use something as intelligent as mysqlProxy, looking at the fact, that here we just need to blindly forward all the packets to next server from one IP to another. – Napster_X Dec 22 '12 at 19:17
1

Other thing is to use HAProxy in tcp-mode

dr-evil
  • 377
  • 1
  • 5
1

The best way is to forward all the traffic from 3306 port to the 3306 port of destination IP address where you are running mysql.

You can use different packet forwarding applications like iptables, packet filter etc, according to the operating system you are using.

As this works at the packet layer and not really at the application layer, so you will skip the over head of using application to do the redirection. This will be significantly faster than using traditional applications like MysqlProxy.

MysqlProxy is useful when you need the application level understanding, like if you want to redirect mysql write requests to one server and read requests to other servers, but in this scenario, using mysql proxy will be a big extra over head.

Napster_X
  • 3,333
  • 16
  • 20
  • How would port forwarding make it possible to split the traffic? – Zoredache Dec 23 '12 at 10:50
  • I don't think the question was to split the traffic. The question was to just forward the local Mysql traffic to a remote server. For splitting the traffic, one will have to use MysqlProxy. – Napster_X Dec 23 '12 at 11:18