Month: April 2017

MySQL High Availability with Keepalived and HAProxy

In this blog post, we are going to implement load balancer solution for MySQL high availability by integrating it with Keepalived, HAProxy, xinetd software components.

High availability databases use an architecture that is designed to continue to function normally even when there are hardware or network failures within the system.
Why we need this?
Let’s take a scenario were we have MySQL Multi-Master / MASTER-SLAVE replication setup for high availability. In the case of Hardware/Network failure on MASTER, In order to failover to a next available server, we need to manually do the configuration changes for client connections.In this case, downtime is expected since manual failover will take some times. To solve this we can integrate load balancer with MySQL to take care of this manual work and do automatic failover connections.
To avoid such downtimes and for the maximum high availability of the database, we can integrate load balancer with MySQL to take care of this manual work and do automatic failover connections.
Advantages:

Almost ZERO DOWNTIME for DB maintenance activities like database patching/upgrades, configuration changes that need DB restart etc.
Easy Read-Write load distribution.
Automatic Failover.
Easy to setup and manage.

Load Balancer is a set of integrated software components that provide for balancing IP traffic across a set of real servers. It consists of two main technologies to monitor cluster members and cluster services: Keepalived and HAProxy.
Keepalived: It uses Linux virtual server (LVS) to perform load balancing and failover tasks on.
HAProxy: It performs load balancing and high-availability services to TCP and HTTP applications.
xinetd: “Extended Internet daemon” is an open-source super-server daemon which runs on many Unix-like systems and manages Internet-based connectivity.xinetd runs constantly and listens on all ports for the services it manages. When a connection request arrives for one of its managed services, xinetd starts up the appropriate server for that service.
VIP:  Virtual IP addresses (or VIPs) allow you to use multiple IPs on a single physical network interface.
Keepalive Configuration:
Load Balancer #1 Configuration

#Configuration File for keepalived

global_defs {
notification_email {
mysql-dba@domain.com
}
notification_email_from mysql-dba@domain.com
smtp_server localhost
smtp_connect_timeout 30
router_id LVS_DEVEL
}

vrrp_instance VI_1 {
state MASTER
interface ens160
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.2.111
192.168.2.112
}
}

Load Balancer #2 Configuration

global_defs {
notification_email {
mysql-dba@domain.com
}
notification_email_from mysql-dba@domain.com
smtp_server localhost
smtp_connect_timeout 30
router_id LVS_DEVEL
}

vrrp_instance VI_1 {
state BACKUP
interface ens160
virtual_router_id 51
priority 99
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.2.111
192.168.2.112
}
}

Validate VIP setup:

HAProxy Configuration on Load balancer #1 and #2 :

# HAProxy configuration – haproxy-db.cfg

global
log 127.0.0.1 local0
log 127.0.0.1 local1 debug
maxconn 45000 # Total Max Connections.
daemon
nbproc 1 # Number of processing cores.
defaults
timeout server 86400000
timeout connect 86400000
timeout client 86400000
timeout queue 1000s

# [MYSQL Configuration]

listen write_dbs 192.168.2.111:3306
mode tcp
balance roundrobin # Load Balancing algorithm
option httpchk
server DB01 192.168.2.105:3306 check port 9200
server DB02 192.168.2.106:3306 check port 9200 backup

listen read_dbs 192.168.2.112:3306
mode tcp
balance leastconn
option httpchk
server DB03 192.168.2.107:3306 check port 9200
server DB04 192.168.2.108:3306 check port 9200
server DB02 192.168.2.106:3306 check port 9200

Note: Add all IP and hostname entries in /etc/hosts file.
xinetd configuration on all mysql server:
/etc/xinetd.d/mysqlchk

# default: on
# description: mysqlchk
service mysqlchk
{
flags = REUSE
socket_type = stream
port = 9200
wait = no
user = nobody
server = /opt/mysqlchk
log_on_failure += USERID
disable = no
only_from = 0.0.0.0/0
per_source = UNLIMITED

NOTE: Create following script on all mysql server to monitor mysql status.
mysqlchk.stauts script [/opt/mysqlchk.stauts] : The purpose of this script is make haproxy capable of monitoring mysql properly

# It is recommended that a low-privileged-mysql user is created to be used by
# this script. Something like this:
#mysql> CREATE USER IF NOT EXISTS ‘mysqlchkuser’@’localhost’ IDENTIFIED BY ‘P@ssword#567’;
# mysql> GRANT show databases on *.* TO ‘mysqlchkuser’@’localhost’;
# mysql> flush privileges;

MYSQL_HOST=”localhost”
MYSQL_PORT=”3306″
MYSQL_USERNAME=”mysqlchkusr”
MYSQL_PASSWORD=”P@ssword#567″

TMP_FILE=”/opt/tmp/mysqlchk.out”
ERR_FILE=”/opt/tmp/mysqlchk.err”

#
# We perform a simple query that should return a few results.
#
mysql –host=$MYSQL_HOST –port=$MYSQL_PORT –user=$MYSQL_USERNAME –password=$MYSQL_PASSWORD -e”show databases;” > $TMP_FILE 2> $ERR_FILE

#
# Check the output. If it is not empty then everything is fine and we return
# something. Else, we just do not return anything.
#
if [ “$(/bin/cat $TMP_FILE)” != “” ]
then
# mysql is fine, return http 200
/bin/echo -e “HTTP/1.1 200 OKrn”
/bin/echo -e “Content-Type: Content-Type: text/plainrn”
/bin/echo -e “rn”
/bin/echo -e “MySQL is running.rn”
/bin/echo -e “rn”
else
# mysql is fine, return http 503
/bin/echo -e “HTTP/1.1 503 Service Unavailablern”
/bin/echo -e “Content-Type: Content-Type: text/plainrn”
/bin/echo -e “rn”
/bin/echo -e “MySQL is *down*.rn”
/bin/echo -e “rn”
fi

Testing:

Test MySQL connection with VIP address.
Stop MySQL Active MASTER, and connect to MySQL.It should connect to backup MySQL server specified in HAProxy configuration.
Test Keepalived and HAProxy failover.

All Set!!

Percona Live 2017

So glad to have had a successful Percona Live last week. Continuent were Diamond Sponsors and now that we are back into a company and not part of VMware we have a little more freedom to get back into the MySQL community.
I had two primary sessions, both on the replicator/ But one was looking specifically at the replicator and how we get data into Big Data targets, the other on general problems of replicating between heterogeneous sources. After the first of those, David from Percona interviewed me to understand a bit more about what I was talking about
I was also on the keynote panel where we discussed a variety of different topics and you can see the full video of that through the link.
 

Best places to look for MySQL help

This is not a resource list post. It is actually a question for you: where do you go, when you’re looking for MySQL help?
For the last few months I been feeling an itch – having some MySQL experience, I would really like to spend some time helping people solve their MySQL issues for free. But I’ve been blocked on the fact that I don’t really know what is the best place to do this.
Naturally, first place to look for MySQL help is Google. But what do you do when Mr. Google can’t help? Stackexchange? Quora? Percona forums? Maybe some mailing lists ? Do you just leave comments on blog posts that are somewhat on-topic?
Please leave a comment and/or upvote an existing one.
I really appreciate it.
The post Best places to look for MySQL help appeared first on Speedemy.

From Percona Live 2017: Thank You, Attendees!

Percona Live 2017From everyone at Percona and Percona Live 2017, we’d like to send a big thank you to all our sponsors, exhibitors, and attendees at this year’s conference. This year’s conference was an outstanding success! The event brought the open source database community together, with a technical emphasis on the core topics of MySQL, MariaDB, MongoDB, PostgreSQL, […]

Installing Hortonworks Data Platform 2.5 on Microsoft Azure

I presented this topic to the Big Data Meetup in Nottingham on Thursday but sometimes people prefer a blog to a presentation, so I’ve fashioned this article from the slides… This article assumes the following: You know what Azure is You know what Hortonworks Data Platform is You have an account to go on Microsoft…

Percona Live 2017: Beringei – Facebook’s Open Source, In-Memory Time Series Database (TSDB)

BeringeiSo that is just about a wrap here at Percona Live 2017 – except for the closing comments and prize giveaway. Before we leave, I have one more session to highlight: Facebook’s Beringei. Beringei is Facebook’s open source, in-memory time series database. Justin Teller, Engineering Manager at Facebook, presented the session. According to Justin, large-scale monitoring […]

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569