Backup MariaDB using xtrabackup on CentOS7

Percona XtraBackup is an open-source hot backup utility for MariaDB/MySQL-based servers that doesn’t lock your database during the backup. It can back up data from InnoDB, XtraDB, and MyISAM tables on MariaDB/MySQL servers.
This guide looks into how to setup xtrabackup backup system for MariaDB on CentOS7, and how to full backup an MariaDB, and then restore the DB.

First thing, ensure the minimum setup for database config file:

[root@centos7 ~]# vi /etc/my.cnf

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# This group is read by the server
#
[mysqld]
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[root@centos7 ~]# vi /etc/my.cnf.d/server.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

Start MariaDB/Mysql

[root@centos7 ~]# service mysql start

Install percona repository, and install xtrabackup

[root@centos7 ~]# yum install https://www.percona.com/redir/downloads/percona-release/redhat/latest/percona-release-0.1-3.noarch.rpm
[root@centos7 ~]# yum list|grep percona
[root@centos7 ~]# yum install percona-xtrabackup

We will create a backup directory

[root@centos7 ~]# mkdir /root/backup

Installation done. To proceed with Full backup,we need to issue the innobackupex, with the DB admin user and password, and also the backup directory.

[root@centos7 ~]# innobackupex --user=root --password=PASSWORD /root/backup

To get the backup information for a particular backup, we can check the xtrabackup_checkpoints file.

[root@centos7 backup]# cat /root/backup/2015-09-29_02-01-17/xtrabackup_checkpoints

We have done the Full backup. To restore a Full backup, we have to stop the MariaDB/Mysql server first.

[root@centos7 ~]# service mysql stop

Move the current mysql data directory to somewhere.

[root@centos7 ~]# rm -fr /var/lib/mysql_old
[root@centos7 ~]# mv /var/lib/mysql/ /var/lib/mysql_old

Issue the following command to restore.

[root@centos7 ~]# innobackupex --copy-back /root/backup/2015-09-29_02-01-17/

Change back the ownership of the mysql data directory.

[root@centos7 ~]# chown mysql:mysql /var/lib/mysql -R

And lastly, start the MariaDB/Mysql service.

[root@centos7 ~]# service mysql start

Setup MariaDB Galera Cluster 10.0 On CentOS7

This guide looks into how to setup MariaDB Galera Cluster on CentOS7. MariaDB Galera Cluster is a synchronous multi-master cluster for MariaDB. It is is an easy-to-use, high-availability solution, which provides high system uptime, no data loss and scalability for future growth. It only supports the XtraDB/InnoDB storage engines

Features:
Synchronous replication
Active-active multi-master topology
Read and write to any cluster node
Automatic membership control, failed nodes drop from the cluster
Automatic node joining
True parallel replication, on row level
Direct client connections, native MySQL look & feel

Benefits:
The above features yield several benefits for a DBMS clustering solution, including:
No slave lag
No lost transactions
Both read and write scalability
Smaller client latencies

For this setup, we have 2 nodes.(ie 192.168.1.11,192.168.1.12.) MariaDB will first setup in (.11) and start in bootstrap mode. After (.11) has started, (.12) will setup next. Once the MariaDB service on (.12) started, Galera process will sync database data and transactions from (.11) to (.12), and after in sync, MariaDB in (.12) will turn on. Both servers will be multi masters and in sync constantly.

To begin with the setup, for simplicity, we will switch off selinux and firewall.

[root@centos7-11 ~]#systemctl stop firewalld
[root@centos7-11 ~]#systemctl disable firewalld
[root@centos7-11 ~]#vi /etc/selinux/config

disabled

[root@centos7-11 ~]#reboot

We will add a repository from MariaDB.

[root@centos7-11 ~]# vi /etc/yum.repos.d/mariadb.repo

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Install the package using YUM.

[root@centos7-11 ~]# yum clean all
[root@centos7-11 ~]# yum install MariaDB-Galera-server

Setup MariaDB.(At this point,the service is still call mysql.)

[root@centos7-11 ~]# service mysql start
[root@centos7-11 ~]# mysql_secure_installation

Stop the mysql service to adjust the MariaDB config file.

[root@centos7-11 ~]# service mysql stop
[root@centos7-11 ~]# vi /etc/my.cnf

!includedir /etc/my.cnf.d

Add replication parameters to server.cnf

[root@centos7-11 ~]# vi /etc/my.cnf.d/server.cnf 

[galera]
# Mandatory settings
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=192.168.1.11
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

We can start the MariaDB on first node.(.11)

[root@centos7-11 ~]# service mysql start

By now, this node has setup to be the first master database. We shell proceed to setup the second master database.

Similarly, we will switch off selinux and firewall.

[root@centos7-12 ~]#systemctl stop firewalld
[root@centos7-12 ~]systemctl disable firewalld
[root@centos7-12 ~]vi /etc/selinux/config

disabled

[root@centos7-12 ~]reboot

We will add a repository from MariaDB.

[root@centos7-12 ~]# vi /etc/yum.repos.d/mariadb.repo

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Install the package using YUM.

[root@centos7-12 ~]# yum clean all
[root@centos7-12 ~]# yum install MariaDB-Galera-server

At this point, we just need to configure the MariaDB config files

[root@centos7-12 ~]# vi /etc/my.cnf

!includedir /etc/my.cnf.d

Add replication parameters to server.cnf

[root@centos7-12 ~]# vi /etc/my.cnf.d/server.cnf

[galera]
# Mandatory settings
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.1.11
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=192.168.1.12

#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

We can start the MariaDB on second node.(.12)

[root@centos7-12 ~]# service mysql start

And thats it, 2 node multi masters MariaDB clusters.

Setup HAProxy (loadbalancing) on CentOS7

This guide looks into how to setup HAProxy on CentOS7. HAProxy offers load balanced services to HTTP and TCP-based services, such as internet-connected services and web-based applications.

In Apr 2013, we looked into how to Setup Load-Balancing Cluster with LVS and Piranha on Centos 6. This time round for CentOS7, we will setup HAProxy Loadbalancing Cluster as this is shipped with CentOS7.

Setup:
1. HAProxy Server at 192.168.1.3
2. LB Virtual IP to use 192.168.1.3
3. Web1 at 192.168.1.9
4. Web2 at 192.168.1.10

For all the servers, lets (temporary) remove the firewall and selinux.

systemctl stop firewalld
systemctl disable firewalld
vi /etc/selinux/config

disabled

reboot

Lets start, login to HAProxy Server:

yum install haproxy

vi /etc/haproxy/haproxy.cfg

#---------------------------------------------------------------------
# Global settings
#---------------------------------------------------------------------
global
    # to have these messages end up in /var/log/haproxy.log you will
    # need to:
    #
    # 1) configure syslog to accept network log events.  This is done
    #    by adding the '-r' option to the SYSLOGD_OPTIONS in
    #    /etc/sysconfig/syslog
    #
    # 2) configure local2 events to go to the /var/log/haproxy.log
    #   file. A line like the following can be added to
    #   /etc/sysconfig/syslog
    #
    #    local2.*                       /var/log/haproxy.log
    #
    log         127.0.0.1 local2

    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     4000
    user        haproxy
    group       haproxy
    daemon

    # turn on stats unix socket
    stats socket /var/lib/haproxy/stats

#---------------------------------------------------------------------
# common defaults that all the 'listen' and 'backend' sections will
# use if not designated in their block
#---------------------------------------------------------------------
defaults
    mode                    http
    log                     global
    option                  httplog
    option                  dontlognull
    option http-server-close
    option forwardfor       except 127.0.0.0/8
    option                  redispatch
    retries                 3
    timeout http-request    10s
    timeout queue           1m
    timeout connect         10s
    timeout client          1m
    timeout server          1m
    timeout http-keep-alive 10s
    timeout check           10s
    maxconn                 3000

#---------------------------------------------------------------------
# main frontend which proxys to the backends
#---------------------------------------------------------------------
frontend LB
   bind 192.168.1.3:80
   reqadd X-Forwarded-Proto:\ http
   default_backend LB

#---------------------------------------------------------------------
# round robin balancing between the various backends
#---------------------------------------------------------------------
backend LB 192.168.1.3:80
   mode http
   stats enable
   stats hide-version
   stats uri /stats
   stats realm Haproxy\ Statistics
   stats auth admin:pass4325    # HAProxy Statistic username/password
   balance roundrobin           # Load balancing to use round-robin
   option httpchk
   option httpclose
   option forwardfor
   cookie LB insert
   server web1 192.168.1.9:80  check  # backend server.
   server web2 192.168.1.10:80 check  # backend server.

We shell enable the service and start the service:

systemctl enable haproxy
systemctl start haproxy

At Webserver create a test page.

vi /var/www/html/p.html

192.168.1.9 (To display Webserver IP address)

systemctl restart httpd

To access the statistic page, navigate to the HAProxy ip/stats,login with the username and pass as states in haproxy.cfg.

How To Install MySQL On CENTOS 7

Below instructions are steps to install mysql server on CentOS 7.

The default replacement for mysql server is MariaDB. MariaDB is a community-developed fork of the MySQL relational database management system. For whatever reasons you might like to install the previous MySQL server, this guide walk you through the process of the installation. The steps:

Setup MySQL repository

[root@localhost ~]# sudo rpm -Uvh http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm

Install Server:

[root@localhost ~]# yum install mysql-server

Start Server:

[root@localhost ~]# systemctl start mysqld

Enable Server on boot:

[root@localhost ~]# systemctl enable mysqld

Change root password:

[root@localhost ~]# mysqladmin -u root password

Install Keepalived on CentOS 7

Keepalived is a solution that provides a strong & robust health-check framework, and also implementing a Hot Standby protocol. It allows load balancing services to have HA and prevent Single Point of Failure.

The following is a set of instructions on setting up Keepalived service on CentOS7.

Assume network as below:
LB1:Loadbalancer 1:192.168.1.80
LB2:Loadbalancer 2:192.168.1.81
Vip1:Virtual IP:192.168.1.82

We want to use LB1 as the master LB, LB2 as standby. If LB1 fails, LB2 will take over as master. Whoever is the master will take over the Vip of 192.168.1.82.

To configure LB1:192.168.1.80, ssh into LB1:

[root@LB1 ~]# yum install keepalived

To allow kernel binding non-local IP into the hosts and apply the changes:

[root@LB1 ~]# echo "net.ipv4.ip_nonlocal_bind = 1" >> /etc/sysctl.conf
[root@LB1 ~]# sysctl -p

[root@LB1 ~]# vi /etc/keepalived/keepalived.conf 

! Configuration File for keepalived 

global_defs { 
   notification_email { 
        admin1@domain.com 
   } 
   notification_email_from admin@local 
   smtp_server 192.168.1.99 
   smtp_connect_timeout 30 
} 

vrrp_script chk_curl { 
    script "/usr/bin/curl http://192.168.1.80" 
    interval 2 
    weight -4 
    timeout 5 
    fall 2 
    rise 2 
}

vrrp_instance VI_1 { 
    state MASTER 
    interface eth0 
    virtual_router_id 51 
    priority 101 
    advert_int 1 
    authentication { 
        auth_type PASS 
        auth_pass 1111 
    } 
    virtual_ipaddress { 
        192.168.1.82/32 dev eth0 
    } 
    track_script { 
        chk_curl 
    } 
} 

[root@LB1 ~]# service keepalived start

Next configure LB2:192.168.1.81, ssh into LB2:

[root@LB2 ~]# yum install keepalived

To allow kernel binding non-local IP into the hosts and apply the changes:

[root@LB2 ~]# echo "net.ipv4.ip_nonlocal_bind = 1" >> /etc/sysctl.conf
[root@LB2 ~]# sysctl -p

[root@LB2 ~]# vi /etc/keepalived/keepalived.conf 

! Configuration File for keepalived 

global_defs { 
   notification_email { 
        admin1@domain.com 
   } 
   notification_email_from admin@local 
   smtp_server 192.168.1.99 
   smtp_connect_timeout 30 
} 

vrrp_script chk_curl { 
    script "/usr/bin/curl http://192.168.1.81" 
    interval 2 
    weight -4 
    timeout 5 
    fall 2 
    rise 2 
}

vrrp_instance VI_1 { 
    state MASTER 
    interface eth0 
    virtual_router_id 51 
    priority 100 
    advert_int 1 
    authentication { 
        auth_type PASS 
        auth_pass 1111 
    } 
    virtual_ipaddress { 
        192.168.1.82/32 dev eth0 
    } 
    track_script { 
        chk_curl 
    } 
} 

[root@LB2 ~]# service keepalived start

chk_curl is a checking script, in above is to check if the httpd service is functioning. 192.168.1.80 is having higher piority(101),1.80 will be master while 1.81 will be backup. If the curl fails, eg httpd down, the vip(192.168.1.82) will swing to 192.168.1.81.

This custom checking script is useful, if you have other checking criteria, you script it in. Basically vrrp_script will check the return value of the script.(eg $? in bash)

Centos 7 firewall (firewalld)

Centos 7 is using firewalld instead of iptables. Below are the steps to enable/disable firewalld.

To check the status of the firewall:

#  service firewalld status 

To disable the firewall:

#  service firewalld stop 

To start the firewall:

#  service firewalld start 

To enable firewall on boot:

#  systemctl enable firewalld

To disable firewall on boot:

#  systemctl disable firewalld

To check on how to configure firewall:

#  man firewall-cmd

To get the default zone:

#  firewall-cmd --get-default-zone

To list all services in public zone:

#  firewall-cmd --zone=public --list-all

To accept http service in public zone permanently:

#  cat /etc/firewalld/zones/public.xml
#  firewall-cmd --permanent --zone=public --add-service=http
#  firewall-cmd --reload
#  cat /etc/firewalld/zones/public.xml

To deny http service in public zone permanently:

#  cat /etc/firewalld/zones/public.xml
#  firewall-cmd --permanent --zone=public --remove-service=http
#  firewall-cmd --reload
#  cat /etc/firewalld/zones/public.xml

The GUI screen to control the firewall is available from the menu.

To install using yum:

# yum install firewall-config

To get to Firewall GUI:

Fedora : System > Administration > Firewall
RHEL7/OL7 : Applications > Sundry > Firewall

CentOS 7: "-bash: ifconfig: command not found"

After new installed Centos 7, entering network command "ifconfig", caused this error "-bash: ifconfig: command not found". This was due to "net-tools" not installed by default. Install "net-tools" will solve the problem.

# yum install net-tools
# ifconfig