MySQL High Availability with Keepalived and HAProxy

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

LB_diag

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 where 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

global_defs {
   notification_email {
     mysql-user@mydomain.com
   }
   notification_email_from svr1@mydomain.com
   smtp_server localhost
   smtp_connect_timeout 30
}
vrrp_instance VRRP1 {
    state MASTER
#   Specify the network interface to which the virtual address is assigned
    interface eth0
#   The virtual router ID must be unique to each VRRP instance that you define
    virtual_router_id 71
#   Set the value of priority higher on the master server than on a backup server
    priority 200
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1066
    }
virtual_ipaddress {
192.168.2.111
192.168.2.112
}
}

Load Balancer #2 Configuration


global_defs {
   notification_email {
     mysql-user@mydomain.com
   }
   notification_email_from svr2@mydomain.com
   smtp_server localhost
   smtp_connect_timeout 30
}

vrrp_instance VRRP1 {
    state BACKUP
#   Specify the network interface to which the virtual address is assigned
    interface eth0
#   The virtual router ID must be unique to each VRRP instance that you define
    virtual_router_id 71
#   Set the value of priority higher on the master server than on a backup server
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1066
    }
virtual_ipaddress {
192.168.2.111
192.168.2.112
}
}

HAProxy Configuration on Load balancer #1 and #2 :


  global
        daemon
        maxconn 256

    defaults
        mode http
        timeout connect 5000ms
        timeout client 50000ms
        timeout server 50000ms

# MYSQL Configuration 

listen write_connections 192.168.2.111:3306
mode tcp
balance roundrobin
option httpchk
server mysql_db1 192.168.2.105:3306 check port 9200
server mysql_db2 192.168.2.106:3306 check port 9200 backup 

listen read_connections 192.168.2.112:3306
mode tcp
balance leastconn
option httpchk
server mysql_db3 192.168.2.107: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!!

Basics of MySQL Administration and best practices

Following are the few best practices and basic commands for MySQL Administration.

MySQL Access and credential security

shell> mysql -u testuser -pMyP@ss0rd
mysql: [Warning] Using a password on the command line interface can be insecure.

By looking at OS cmd’s history using history cmd other os users can see/get MySQL user password easily. It always good to not use a password on the command line interface. Another option for securing password while automating MySQL scripts is a use of mysql_config_editor. For more info on this check out my blog post about credential security.

Consider of having following implementation for Strong access policy.

  • use of  validate_password plugin for a strong password policy.
  • Limit the user access by specifying IP or IP range in a hostname.
  • Do not grant accessive privileges to user/s.
  • Have separate users for different operations like backup user with required backup privileges only.
  • Avoid giving FILE and super privileges to remote users.
  • For public network communication between client and server use SSL connection method.

Replication

  •  IF EXISTS and IF NOT EXISTS use while creating DB objects.

Most common problem for replication break or errors is that OBJECT already exists on SLAVE. By using IF EXISTS and IF NOT EXISTS while creating database objects we can avoid.

  • Use of GTID and crash-safe replication.
  • Keep your slave in read-only mode.
  • Run your backups and query optimization on SLAVE. This will avoid unnecessary load on MASTER.

Logging

Logs are great significance for admin. Following types of logs, you can enable for MySQL servers.

  • Binary log: Extra copy of your database transactions.
  • Relay log:  By default enable and get created when you setup replication.
  • General log: To log MySQL client tool commands.
  • Slow query log: Log slow queries taking more time for execution.
  • Error / MySQL server log: Record NOTES, WARNINGS and ERROR for MySQL server.
  • Audit Log:  Log user info and activities like executes queries by a user/s along with source IP, timestamp, target database etc.
  • To maintain these logs like purging OLD logs using logrotate. Check MYSQL SERVER LOG MAINTENANCE for more info.

 MySQL STARTUP- SHUTDOWN

Always check MySQL error log for STARTUP- SHUTDOWN and make sure for clean STARTUP/ SHUTDOWN.

Basic commands for MySQL Administration

MySQL database and table creation

CREATE IF NOT EXISTS DATABASE:

CREATE IF NOT EXISTS  DATABASE test_db ;
Use test_db ;

CREATE TABLE:

CREATE IF NOT EXISTS TABLE t1 (id int(11) primary key auto_incremnet ,uname varchar(50), comments text);

INSERT INTO TABLE:

INSERT INTO t1 (id, uname, comments) VALUES(101,’lalit’,’mysql DBA’);

MySQL Database Users

CREATE USER:

  1. The CREATE USER statement creates new MySQL accounts.
CREATE USER IF NOT EXISTS 'local_user1'@'localhost' IDENTIFIED BY 'mypass'; (Remote connection restricted for this user)

If you specify only a username part of the account name, a host name part of ‘%’ is used.

CREATE USER IF NOT EXISTS 'remote_user1'@'%' IDENTIFIED BY 'mypass';

(Remote connection enabled for this user)

  1. User details are getting stored under user table.
SELECT user,host FROM mysql.user;

 

RENAME USER:

RENAME USER 'abc'@'localhost' TO 'xyz'@'%';

DROP USER:

 DROP IF EXISTS USER 'remote_user1'@'%’;

User password management:

  1. Change/Update user password.
ALETR USER IF EXISTS 'remote_user1'@'%' IDENTIFIED BY 'mypass';
  1. Password expire user account
ALTER USER IF EXISTS 'remote_user1'@'%' PASSWORD EXPIRE;
  1. Locked User account
ALTER USER IF EXISTS 'remote_user1'@'%' ACCOUNT LOCK;

 

MySQL Database Users Access Restrictions using privileges.

Grant privileges to a user:

Privileges can be granted on database/s, table/s and related objects to it.

Example.

Case1:  Grant all privileges on ‘db1’ database to user ‘remote_user1’@’%’

GRANT ALL PRIVILEGES ON db1.* TO 'remote_user1'@'%';

Case2: Grant selected privileges on ‘db1’ database to user ‘remote_user1’@’%’

GRANT SELECT, INSERT, UPDATE, DELETE ON db1.* TO 'remote_user1'@'%';

Case3. Grant SELECT privilege single table access to user ‘remote_user1’@’%’

GRANT SELECT ON db1.table1 TO 'remote_user1'@'%';

Ref: http://dev.mysql.com/doc/refman/5.7/en/grant.html

Revoking privileges from user:

Example:

REVOKE SELECT, INSERT, UPDATE, DELETE ON db1.* FROM 'remote_user1'@'%';

Ref: http://dev.mysql.com/doc/refman/5.7/en/revoke.html

Check User Privileges using SHOW GRANTS command:

Example:

SHOW GRANTS FOR 'mysqldba'@'localhost';

SHOW GRANTS; (It will display the privileges granted to the current account)

SHOW GRANTS FOR 'remote_user1'@'%';

Ref: http://dev.mysql.com/doc/refman/5.7/en/show-grants.html

MySQL monitoring

Check database size:

Information_schema (Metadata)

SELECT table_schema "Data Base Name",    sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",    sum( data_free )/ 1024 / 1024 "Free Space in MB"FROM information_schema.TABLESGROUP BY table_schema ;

Check Active users:

show processlist ;

InnoDB Engine Status:

SHOW STATUS;

SHOW ENGINE INNODB STATUS;
  1. Performance schema: Live statistics

Example:

– Enable Locking related instruments (if it’s not enabled):

UPDATE performance_schema.setup_instruments SET ENABLED=’YES’, TIMED=’YES’ WHERE NAME=’wait/lock/metadata/sql/mdl’;

SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_SCHEMA=’test’ AND OBJECT_NAME LIKE ‘t_’;

  1. MySQL Enterprise monitor
  2. Customized scripts

Check Database objects info:

Databases:

SHOW DATABASES;

Select Database:

Use db_name;

Tables in Database:

SHOW TABLES;

SELECT TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA = 'test_db';

ROUTINES:

select * from ROUTINES where ROUTINE_SCHEMA='db_name’;

INDEX:

select TABLE_NAME,INDEX_NAME,COLUMN_NAME,INDEX_TYPE  from information_schema.STATISTICS where TABLE_SCHEMA = 'db_name';

View:

select * from information_schema.VIEWS where TABLE_SCHEMA = 'db_name';

Mysqldump  Backup-Restore:

Require privileges: mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the –single-transaction option is not used. Certain options might require other privileges as noted in the option descriptions.

Backup:

Full Database backup:

mysqldump -u root  -p --single-transaction --databases db1  --routines > db1_fullbkp.sql

 OR

mysqldump -u root  -p --single-transaction  --databases db1 --routines | gzip >  db1_fullbkp.sql.gz

 

Single table backup:

mysqldump -u  -h  -p --single-transaction db_name table_name --routines > db1_full.sql

Ref : http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

Restore:

To reload a dump file, you must have the privileges required to execute the statements that it contains, such as the appropriate CREATE privileges for objects created by those statements.

mysql -u username -p db_name < db1_fullbkp.sql

OR

gunzip < db1_fullbkp.sql.gz | mysql -u username -p db_name

MySQL Replication:

  1. Create replication user on MASTER with replication privileges.
CREATE USER [IF NOT EXISTS] 'rpluser'@'%' IDENTIFIED BY 'rpluser1234';GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'rpluser'@'%';
  1. On SLAVE: setup replication as follows:
CHANGE MASTER TO MASTER_HOST='<MASTER_IP',MASTER_USER='rpluser',MASTER_PASSWORD='rpluser1234',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;
  1. Start slave
START SLAVE;
  1. Check slave status
SHOW SLAVE STATUS;

Slave_IO_Running and Slave_SQL_Running column value should be ‘YES’

MYSQL service [Linux]

MySQL SHUTDOWN steps:

shell> sudo service mysqld stop

MySQL STARTUP steps:

shell> sudo service mysqld start

All Set !!