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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

H_HarryCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nothing_ChangedCommented:
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
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Switches / Hubs

From novice to tech pro — start learning today.