Linux Expert Zone

PERCONA XTRABACKUP:-

This post is about advantages, disadvantages, Installation and DB backup steps of percona xtrabackup/innobackupex and I will let you know about innobackupex tool which is written in Perl, It enables more functionality by integrating xtrabackup and other functions such as file copying and streaming, It perform point in time backup for Innodb, MyISAM , Archive engines.

I have picked a DB which size is 28GB to test the same. I got below results:

Backup Time Taken with innobackupex: 5 minutes
Backup Size : - 5.8 GB

and below are the findings (Advantages and Disadvantages):-

Advantages:-
1) It supports completely non-blocking backups of InnoDB/XtraDB (Which support Transactions) storage engines.
2) It supports on fly compression which help to cater disk space with (--stream=xbstream).
3) We can take incremental backup as well with this. This can be done because each InnoDB page has a log sequence number, LSN, which acts as a version number of the entire database. Every time the database is modified, this number gets incremented. An incremental backup copies all pages since a specific LSN.
4) We can take partial backup as well which means that we can take backup only for some specific tables. But for this there must be innodb_file_per_table enabled
5) Use --slave-info when we are backing up from backup slave server because it will provide binlog file name and position of the master server. It also writes this information to the xtrabackup_slave_info file.

HAProxy is an open source software which can load balance HTTP and TCP servers. All your MySQL servers have to be configured to perform Master-Master replication as load balancing involves both reading and writing to all the backends.

HAproxy over MySQL

Prepare MySQL Servers

We need to prepare the MySQL servers by creating one additional user for HAProxy. This user will be used by HAProxy to check the status of a server and health of server.

mysqlchk

We need to create a script which detects the MySQL replication role on the database node as per below:

if master (SHOW SLAVE HOSTS > 1 AND read_only = OFF),
return 'MySQL master is running.'
if slave (Slave_IO_Running = Yes AND Slave_SQL_Running = Yes AND (Seconds_Behind_Master = 0 OR Seconds_Behind_Master < SLAVE_LAG_LIMIT))
Set limit of SLAVE_LAG_LIMIT which will be bear able for your environment.
return 'MySQL slave is running. (slave lag: 0)'
else
return 'MySQL is *down*'

How to use

Create a script in any path like I created at '/opt/mysqlcheck' and make it executable.

#!/bin/bash
SLAVE_LAG_LIMIT=5
MYSQL_HOST="127.0.0.1"
MYSQL_PORT="3306"
MYSQL_USERNAME='USERNAME'
MYSQL_PASSWORD='PASSWORD'
MYSQL_BIN='/usr/bin/mysql'
MYSQL_OPTS="-q -A --connect-timeout=10"
TMP_FILE="/tmp/mysqlchk.$$.out"
ERR_FILE="/tmp/mysqlchk.$$.err"
FORCE_FAIL="/tmp/proxyoff"

preflight_check()
{
for I in "$TMP_FILE" "$ERR_FILE"; do
if [ -f "$I" ]; then
if [ ! -w $I ]; then
echo -e "HTTP/1.1 503 Service Unavailable\r\n"
echo -e "Content-Type: Content-Type: text/plain\r\n"
echo -e "\r\n"
echo -e "Cannot write to $I\r\n"
echo -e "\r\n"
exit 1
fi
fi
done
}

return_ok()
{
echo -e "HTTP/1.1 200 OK\r\n"
echo -e "Content-Type: text/html\r\n"
echo -e "Content-Length: 43\r\n"
echo -e "\r\n"
if [ $role == "master" ]; then
echo -e "<html><body>MySQL master is running.</body></html>\r\n"
elif [ $role == "slave" ]; then
echo -e "<html><body>MySQL slave is running. (Slave lag: $SLAVE_LAG)</body></html>\r\n"
else
echo -e "<html><body>MySQL is running.</body></html>\r\n"
fi
echo -e "\r\n"
# rm $ERR_FILE $TMP_FILE
exit 0
}
return_fail()
{
echo -e "HTTP/1.1 503 Service Unavailable\r\n"
echo -e "Content-Type: text/html\r\n"
echo -e "Content-Length: 42\r\n"
echo -e "\r\n"
echo -e "<html><body>MySQL is *down*.</body></html>\r\n"
echo -e "\r\n"
exit 1
}

preflight_check

if [ -f "$FORCE_FAIL" ]; then
echo "$FORCE_FAIL found" > $ERR_FILE
return_fail
fi

CMDLINE="$MYSQL_BIN $MYSQL_OPTS --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME --password=$MYSQL_PASSWORD -e"
SLAVE_IO=$(${CMDLINE} 'SHOW REPLICA STATUS' --vertical 2>/dev/null | grep Replica_IO_Running | tail -1 | awk {'print $2'})
SLAVE_SQL=$(${CMDLINE} 'SHOW REPLICA STATUS' --vertical 2>/dev/null | grep Replica_SQL_Running | head -1 | awk {'print $2'})
echo $CMDLINE

if [[ "${SLAVE_IO}" == "Yes" ]] && [[ "${SLAVE_SQL}" == "Yes" ]]; then
role='slave'
SLAVE_LAG=$(${CMDLINE} 'SHOW REPLICA STATUS' --vertical 2>/dev/null | grep Seconds_Behind_Source | tail -1 | awk {'print $2'})
if [[ $SLAVE_LAG = 0 ]]; then
return_ok
elif [ $SLAVE_LAG -lt $SLAVE_LAG_LIMIT ] ; then
return_ok
fi
else
role='master'
READ_ONLY=$($CMDLINE 'SHOW GLOBAL VARIABLES LIKE "read_only"' --vertical 2>/dev/null | tail -1 | awk {'print $2'})
echo "$READ_ONLY"
[[ "${READ_ONLY}" == "OFF" ]] && return_ok
fi

return_fail

Create a service in /etc/xinetd.d/ and accordingly change /etc/service with port number and service name.

service mysqlchk
{
flags = REUSE
socket_type = stream
port = 9300
wait = no
server = /root/mysqlchk
user = mysql
log_on_failure += USERID
disable = no
only_from = 0.0.0.0/0 # recommended to put the IPs that need
# to connect exclusively (security purposes)
per_source = UNLIMITED # Recently added (May 20, 2010)
}

restart xinetd service
Install HAProxy on the your selected node.
Update require information (SLAVE_LAG_LIMIT is in seconds):
SLAVE_LAG_LIMIT=5
MYSQL_HOST="localhost"
MYSQL_PORT="3306"
MYSQL_USERNAME='root'
MYSQL_PASSWORD='password'
You are good to go. Now ensure in HAProxy you have 2 listeners (3307 for write, 3308 for read) and use option tcp-check & tcp-check expect to distinguish master/slave similar to example below:

Linux Expert Zone

Pages

Monday, June 19

Advantages and Disadvantages of Percona Xtrabackup, Installation and Steps to take DB backup through innobackupex tool

How To Use HAProxy For MySQL Load Balancing

mysqlchk

How to use