MariaDB+Keepalived double main high availability configuration MySQL-HA

Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Construction of high availability MySQL-HA using keepalived, to guarantee the consistency of two sets of MySQL data, and then use keepalived to achieve the virtual VIP, the service control function with the keepalived to achieve MySQL fault automatic switching.
The hardware topology as follows:
VIP: 192.168.1.200
mysql1:192.168.1.201
mysql2:192.168.1.202
Operating system: CentOS release 6.3 (32 bit)
MySQL version: MariaDB 5.5.31 Stable
Download the address (64 please download version 64):
Keepalived version: Version 1.2.7
Download address: http://www.keepalived.org/software/keepalived-1.2.7.tar.gz
One, the Centos runtime environment configuration:
Execution:
rpm -qa|grep mysql
rpm -e mysql
yum -y remove mysql-server mysql
yum -y remove php-mysql
MySQL comes with the removal system
yum -y install yum-fastestmirror
yum -y update
Updating the system software,
rm -rf /etc/localtime
ln -s /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
yum install -y ntp
ntpdate -d cn.pool.ntp.org
date
Set the time zone and time synchronization system
#Disable SeLinux
if [ -s /etc/selinux/config ]; then
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
fi
ldconfig
Close the safety enhancement
cat >>/etc/security/limits.conf<<eof
* soft nproc 65535
* hard nproc 65535
* soft nofile 65535
* hard nofile 65535
eof
cat >>/etc/sysctl.conf<<eof
fs.file-max=65535
eof
The maximum number of file handles modifications
Install the software.
yum -y install gcc gcc-c++ gcc-g77 ncurses-devel bison libaio-devel cmake libnl* libpopt* popt-static openssl-devel
Two, the installation of maridDB:
1, Download software source code package
[root@localhost down]# wget
2, Compiler configuration, improve performance
CFLAGS="-O3"
CXX=gcc
CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti"
3, Start the MariaDB installation
Add MySQL user and user group
[root@localhost down]# groupadd mysql
[root@localhost down]# useradd -s /sbin/nologin -M -g mysql mysql
Unzip the MariaDB
[root@localhost down]# tar -zxvf mariadb-5.5.31
[root@localhost down]# cd mariadb-5.5.31
The installation to the /usr/local/mariamysql directory:
[root@localhost down]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mariamysql -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DENABLED_LOCAL_INFILE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci  -DWITH_DEBUG=0 -DBUILD_CONFIG=mysql_release -DFEATURE_SET=community -DWITH_EMBEDDED_SERVER=OFF && make && make install
Copy the configuration files to/etc/my.cnf
[root@localhost down]# cp /usr/local/mariamysql/support-files/my-huge.cnf /etc/my.cnf
Set mariamysql as a system service
[root@localhost down]# cp /usr/local/mariamysql/support-files/mysql.server /etc/init.d/mariamysql
Initializing mariaDB database:
[root@localhost down]# /usr/local/mariamysql/scripts/mysql_install_db --basedir=/usr/local/mariamysql/ --datadir=/usr/local/mariamysql/data/ --user=mysql
This mariaDB installation is complete.
Three, the installation of keepalived:
1, Download the source code package:
wget http://www.keepalived.org/software/keepalived-1.2.7.tar.gz
2, Installation:
[root@localhost down]# tar -zxvf keepalived-1.2.7.tar.gz
[root@localhost down]# cd keepalived-1.2.7
[root@localhost keepalived-1.2.7]# ./configure --prefix=/usr/local/keepalived
The installation to the /usr/local/keepalived directory;
At this point the keepalived is installed.
Four, 201 and 202 respectively in the two machines were repeated two, three installed mariaDB and keepalived.
Five, 201 database server configuration:
1, Setting the mariaDB database configuration file:
[root@localhost /]# vi /etc/my.cnf
Ensure that the following parameters in /etc/my.cnf, if there is no need to manually add, and restart the MySQL service.
[mysqld]
log-bin=mysql-bin
#Start binary file
server-id=1
#The server ID
After starting the mariaDB server settings
[root@localhost /]# service mariamysql start
2, Log on to MySQL, then increased synchronization in a special account for, as follows:
[root@localhost /]# /usr/local/mariamysql/bin/mysql -uroot -p #The initial password is empty to Enter password: directly enter

MariaDB [(none)]> grant replication slave on *.* to 'backup'@'192.168.1.202' identified by 'backup'; flush privileges;


Display master status:
MariaDB [(none)]> show master status;
Record the File and Position in the 202 set from 201 synchronous.

Six, 202 database server configuration:
[root@localhost /]# vi /etc/my.cnf
Ensure that the following parameters in /etc/my.cnf, if there is no need to manually add, and restart the MySQL service.
[mysqld]
log-bin=mysql-bin
#Start binary file
Server-id=10 (here to set with 201 different)
#The server ID
After starting the mariaDB server settings.
[root@localhost /]# service mariamysql start
Log in to the database:
[root@localhost /]# /usr/local/mariamysql/bin/mysql -uroot -p
Input:
MariaDB [(none)]> change master to master_host='192.168.1.201',master_user='backup',master_password='backup',master_log_file='mysql-bin.000010',master_log_pos=245;
Note: the above 245 in 201 top down Position, mysql-bin.000010 corresponding to File 201 of the above record
After the success of the implementation, the input command to display the state from the library:
MariaDB [(none)]> show slave status \G;

Slave_IO_Running: Yes           
Slave_SQL_Running: Yes
Two showed Yes shows success from 201 synchronous data.
So far 201. 202 for client server data from a set of success!
Seven, 201 and 202 are master and slave:
1, 202 machine add an account specifically for synchronous data:
MariaDB [(none)]> grant replication slave on *.* to 'backup'@'192.168.1.201' identified by 'backup'; flush privileges;
2, Display 202 as the main library status:
MariaDB [(none)]> show master status;
3, In the 201 database server.:
MariaDB [(none)]> change master to master_host='192.168.1.202',master_user='backup',master_password='backup',master_log_file='mysql-bin.000005',master_log_pos=5005;
Note: the above 5005 in 202 top down Position, mysql-bin.000005 corresponding to File 202 of the above record
Display status:
MariaDB [(none)]> show slave status \G;
Slave_IO_Running: Yes           
Slave_SQL_Running: Yes
Two showed Yes shows success from 202 synchronous data.
So far 201, 202 interaction from set successfully!
You can try on the two servers any increase in a database, and create a table, add some data have a look, mutual synchronization state is successful!
The first 201 in the above:
MariaDB [(none)]> create database mysqltest;
MariaDB [(none)]> use mysqltest;
MariaDB [mysqltest]> create table user(id int(5),name char(10));
MariaDB [mysqltest]> insert into user values (00001,'zhangsan');
Check in 202.:
MariaDB [(none)]> use mysqltest;
MariaDB [mysqltest]> select * from user;
Will find 201 the above data is automatically synchronized to 202 above.
Also in 202.:
MariaDB [mysqltest]> insert into user values (00002,'wander');
Check in 201.:
MariaDB [mysqltest]> select * from user;
Each master-slave structure set
Note: if the synchronization is not successful, first of all to ensure that the server on port 3306 open.
CentOS can use service iptables stop to close the firewall.
Eight, using keepalived to achieve high availability
Keepalived realization of virtual IP, the service functions of monitoring keepalived own to achieve MySQL fault automatic switching,
1, Keepalived settings:
201 server above, edit the keeplaived.conf configuration file:
[root@localhost /]# vi /usr/local/keepalived/etc/keepalived/keepalived.conf
The configuration file is as follows:
! Configuration File for keepalived

global_defs {
  router_id mysql-ha
}

vrrp_instance VI_1 {
   state BACKUP
   interface eth0
   virtual_router_id 201
priority 100 #Priority, high priority campaign for master
   advert_int 1
nopreempt #Set to not grab note: this configuration can be set on a backup host, and the host priority than another one, another do not set this option
   authentication {
       auth_type PASS
       auth_pass 123456
   }
   virtual_ipaddress {
       192.168.1.200
   }
}

virtual_server 192.168.1.200 3306 {
   delay_loop 2
   lb_algo rr
   lb_kind DR
   persistence_timeout 60
   protocol TCP
   real_server 192.168.1.201 3306 {
       weight 1
       notify_down /usr/local/keepalived/etc/keepalived/mysql.sh
       TCP_CHECK {
           connect_port 3306
           connect_timeout 3
           nb_get_retry 2
           delay_before_retry 1
       }
   }
}
~              
         
Switching MySQL service is stopped after the script editor: mysql.sh
[root@localhost /]# vi /usr/local/keepalived/etc/keepalived/mysql.sh
The contents are as follows:
#!/bin/bash
pkill keepalived
2, To start the 201 above keepalived
[root@localhost /]# /usr/local/keepalived/sbin/keepalived -f /usr/local/keepalived/etc/keepalived/keepalived.conf -D
See:
There will be three keepalived process after a successful start-up
This is the Ping is already available in any local machine above the Ping 192.168.1.200 found by 192.168.1.200, and the IP can connect to the database server.
3, In the 202 machine above 1, 2 steps repeat,
When configuring the keepalived.conf file to the
Real_server 192.168.1.201 3306 to real_server 192.168.1.202 3306
Virtual_router_id 201 to virtual_router_id 202
Priority 100 to priority 90
Remove the nopreempt
So MariaDB+Keepalived double main high availability configuration MySQL-HA set.
Can try to stop the 201 above mariaDB
[root@localhost /]# service mariamysql stop;
Will find the connection 192.168.1.200 or can be connected up, keepalived will automatically switch to the 202 server to the above. So, when a database server fails, another server can immediately switch over, ensure high availability.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Dana at December 05, 2013 - 9:27 PM