Use Cisco CSS 11503 to load balance (or primary/secondary) MySQL databases port 3306

I'm looking for an example config, to use the Cisco CSS 11503 load balancer to send traffic TCP on port 3306 to 2 different MySQL databases. In other words, to load balance between two MySQL servers.

I'd also maybe even prefer to send all traffic to DB1 unless it's down, then send traffic to DB2 instead.

ldorazioAsked:
Who is Participating?
 
H_HarryConnect With a Mentor Commented:
Have you a basic understanding of how to do it? (So I know how much detail to go in to).
Basically you need to create the circuit (VLAN),  the services which define the relevant IP addresses and port number, then apply them to a rule and point it to the VIP.

**** GLOBAL ****
ip route 0.0.0.0 0.0.0.0 X.X.X.X <- Your Default gateway
 
**** CIRCUIT ****
circuit VLAN1
ip address X.X.X.X 255.255.255.0 <- Subnet for VLAN with correct maske
 
**************** SERVICE ****************
service SQLServer1
ip address X.X.X.X <- IP Address of first SQL server
port 3306
protocol TCP <- Or UDP Dependant on the traffic
active
service SQLServer2
ip address X.X.X.X <- IP Address of second SQL Server
port 3306
protocol TCP <- Or UDP
active
**************** OWNER ******************
owner L3-Owner
content L3-Rule
add service SQLServer1
add service SQLServer2
vip address X.X.X.X <- Virtual IP address to use.
balance
aca active  
 
 
 
If there is no valid config on the device when you logon to it, it will run an automated config script with prompts asking for the relevant data - select Layer 3 load balancing and simply answer the relevant questions when prompted and it will auto-generate most of the configuration for you.
 
 
0
 
Nothing_ChangedConnect With a Mentor Commented:
assuming your circuits nad interfaces and all are configured, this content configuration would accomplish your goal. Your database servers need to be "behind" the CSS for this to work, in other words there can be no network path to the database servers from the clients other than through the CSS.

This will use IP address of 10.1.1.0/24 for the services, 10.1.2.0/24 for the VIP, change these to match your address scheme. The keepalives will be a TCP connect to port 3306 terminated with a RST, they will run every 10 seconds, two failures will mark the server as dead, the wait 10 seconds before retrying the keepalive again.

This config will balance between the two servers equally via round robin, and stick clients to a server once they balance there.

service db1
  ip address 10.1.1.11
  protocol tcp
  port 3306
  keepalive type tcp
  keepalive frequency 10
  keepalive maxfailure 2
  keepalive retryperiod 10
  active

service db2
  ip address 10.1.1.12
  protocol tcp
  port 3306
  keepalive type tcp
  keepalive frequency 10
  keepalive maxfailure 2
  keepalive retryperiod 10
  active

owner MYSQL
  content MYSQL-database
    vip address 10.1.2.11
    port 3306
    protocol tcp
    add service db1
    add service db2
    advanced-balance sticky-srcip  
    active



This config will balance to one server only, roll clients over to the secondary server if the first one tanks, and moves clients back to the primary server once it is back up for 20 seconds. If you want to manually move clients instead of automatically, remove the two persistence commands. Moving clients back to the primary would require suspending the second server, then deactivating and reactivating the content rule.

persistence reset remap

service db1
  ip address 10.1.1.11
  protocol tcp
  port 3306
  keepalive type tcp
  keepalive frequency 10
  keepalive maxfailure 2
  keepalive retryperiod 20
  active

service db2
  ip address 10.1.1.12
  protocol tcp
  port 3306
  keepalive type tcp
  keepalive frequency 10
  keepalive maxfailure 2
  keepalive retryperiod 10
  active

owner MYSQL
  content MYSQL-database
    vip address 10.1.2.11
    port 3306
    protocol tcp
    add service db1
    advanced-balance sticky-srcip  
    no persistent
    primarySorryServer db2
    active




 


 
0
 
Nothing_ChangedCommented:
doh, didnt see the other response since i was typing, sorry.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
ldorazioAuthor Commented:

This one with the "Sorry Server" looks like it might be perfect, I hadn't thought at all about using that....

I don't completely understand this however:
  "Your database servers need to be "behind" the CSS for this to work... no network path... other than through the CSS."

My web servers are: 10.7.2.101 and 10.7.2.102
My DB servers are: 10.3.3.201 and 10.3.3.202

Currently the web servers can get to 10.3.3.x through the firewall...

I THINK it would be the VIP would be within the web server network (like 10.7.2.200) and then the services would point at 10.3.3.201 and 202, but I'm not sure?

Do I have to "turn off" access to 10.3.3.x through the firewall also?

Thanks.

0
 
H_HarryCommented:
Hi,
"I don't completely understand this however:
  "Your database servers need to be "behind" the CSS for this to work... no network path... other than through the CSS."

It means that you have to ensure that all the traffic comes through the CSS and there is no other path for the data to take i.e. Via another router, switch etc - which will obviously negate the load balancer.
If the web server can get to the DB server through the firewall and bypass the CSS then yes you would need to prevent this and re-think the topology to prevent the data circumventing the CSS. (assuming the traffic coming from the web servers to the DB server is what you want load balanced)
 
 
0
 
Nothing_ChangedCommented:
yep, you need to ensure that the css stays in path between your db servers and the clients or app servers using them. typically, people use the CSS as a layer three boundary (as a router essentially) between two VLANs, one that contains the servers being balanced, and the other one  being either a transit to the rest of the network, or where your client nodes reside.

Reason being that the CSS balances with destination NAT. It takes a connection in and NATs it back out to one of the services configured for the content rule. If the service has another path back to the client other than thru the CSS, he will try to answer the client directly. But the client has no clue he is talking to that actual service, he thinks he is talking to a host with the IP address of the VIP in the content rule, so he ignores the response. Almost like a broken assymetrical route, sort of.

So, keeping the CSS "between" the clients and servers is the simple solution. If that's not possible, you can artificially engineer that path to be taken anyway using source groups, however that takes a lot more CPU resources on the CSS, and isn't as efficient network-wise.
0
 
ldorazioAuthor Commented:
Thank you, I will have to setup another VLAN / subnet to test this. I have the problem now that it gets to and through the CSS, but then it's going straight back to the web from DB just as you both said.

This probably won't get to be tested until next week now...
0
 
Nothing_ChangedCommented:
Any luck so far?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.