MySQL mha+keepalived

A brief introduction of MHA
MHA is written in the Perl language, using a plug-in script to achieve the high availability of MySQL master-slave replication.
MHA can automatically detect if MySQL is down, if it goes down, complete the new master election in 10-30s, apply all the differences binlog logs to all slave, and switch all slave to the new master.
MHA in addition to automatically detect whether MySQL is down, but also able to switch master interactively, in the daily maintenance of the database, this feature is very useful.
Since the MHA itself is only responsible for the database master-slave switchover, the application does not know that the database master has changed. For this scenario, you can use several scripting interfaces reserved by MHA to notify the application via a virtual IP or a method that modifies the global configuration file, and the master database has changed.

MHA is also a very active project, the production environment of many users, no lack of large companies, MHA version is also very fast, MHA author is continuously updated version, the latest version has been supported Gtid.

II. deployment of the environment

Os:centos/redhat 6.x

Mysql:percona 5.5/5.6

Host (hostname)	Role	Ip	Installation
Master.mysql.com	Master	192.168.56.110	Mysq,mha node,keepalived
Slave1.mysq.com	Candidate Master (SLAVE1)	192.168.56.111	Mysql, MHA node, Keepalived
Slave2.mysql.com	Slave2	192.168.56.112	Mysql,keepalived
Mha.mysql.com	MHA Manager	192.168.56.111	MHA Manager
Vip	192.168.56.188

Third, Software Installation

Yum Source Preparation (Modify/etc/yum.repos.d/)

Epel Yum Source: http://blog.csdn.net/yiyuf/article/details/40148125

Percona Yum Source: http://blog.csdn.net/yiyuf/article/details/40148183

Turn off SELinux

#cat/etc/sysconfig/selinux

Selinux=disabled

Close Iptables

#service iptables Stop

Chkconfig iptables off

The modified/etc/hosts is added as follows:

192.168.56.110 master.mysql.com

192.168.56.112 slave1.mysql.com

192.168.56.113 slave2.mysql.com

and scp/etc/hosts to various other nodes.

MySQL Installation:

Yum-y Install percona-server-server-55.x86_64 (version 5.5)
Yum-y Install percona-server-server-56.x86_64 (version 5.6)

Build MySQL one master two from configuration (slightly)

SSH Trust configuration:

Execute on Master (192.168.56.110): Ssh-keygen (All the way to enter)

# CD ~/.ssh

#cat id_rsa.pub >authorized_keys

#chmod 600 *

Scp-r. SSH 192.168.56.111:~/

Scp-r. SSH 192.168.56.112:~/

Scp-r. SSH 192.168.56.113:~/

At this point, the master and slave of MySQL is also set up, and can run normally. Then start installing the MHA software:

1. MHA Manager node Installation (192.168.56.111):

Yum-y Install perl-dbd-mysql perl-config-tiny perl-log-dispatch perl-parallel-forkmanager-----Installing Perl related library files

Download from https://code.google.com/p/mysql-master-ha/wiki/Downloads above:

MHA Manager 0.56 rpm RHEL6

MHA Node 0.56 rpm RHEL6

MHA Manager 0.56 Tarball # # # # #下载此tar包的目的是为了copy相关的配置文件和scripts

To install on 192.168.56.111:

#rpm-IVH mha4mysql-node-0.56-0.el6.noarch.rpm

#rpm-IVH mha4mysql-manager-0.56-0.el6.noarch.rpm

2. MHA node Installation (192.168.56.110, 192.168.56.112,192.168.56.113):

# yum-y Install Perl-dbd-mysql

#rpm-IVH mha4mysql-node-0.56-0.el6.noarch.rpm

3. MHA configuration (only need to be configured on MHA Manager side):

The configuration file is divided into two by default (APPXXX.CNF, masterha_default.cnf), where MASTERHA_DEFAULT.CNF is a global profile and is especially useful when managing multiple clusters. Avoid app.conf writing too long. The app1.conf scope is master/slave structure,

Global parameter file configuration (MASTERHA_DEFAULT.CNF)

#cat/etc/masterha_default.cnf

[Server default]

User=yyf

Password=yyf

Ssh_user=root

Repl_user=replica

Repl_password=zjjzjj

master_binlog_dir=/var/lib/mysql3307

#monitor

Ping_interval=1

Shutdown_script= ""

secondary_check_script= "Masterha_secondary_check-s 192.168.56.112-s 192.168.56.113" #此处 56.112 and 56.113 must be adjusted after each switch. Example: the current 112 and 113 is slave,110 is master, if MHA occurs automatically switch, master from 110 to 112, the next time this part is modified to:-S 192.168.56.110-s 192.168.56.113. Otherwise, the next time you switch, it will cause a failure.

Master_ip_failover_script= "/root/scripts/master_ip_failover.sh" # # Click to open the link (master_ip_failover.sh)

Parameter explanation:

User	The user name used to manage MySQL. This finally requires the root user because it needs to be performed: Stop slave; Change master to, reset slave. Default: Root
Password	The password of the Admin user for MySQL. The default is empty
Ssh_user	System user currently in use for SSH login detection status
Repl_user	MySQL User for replication
Repl_password	MySQL password for the user to copy
Ping_interva L	Set MHA Manager for how long to ping the master (execute some SQL statements). When 3 pings fail, MHA manager thinks MySQL master is dead. In other words, the maximum failover time is 4 ping_interval time, and the default is 3 seconds.
Shutdown_script	Define a script that shuts down the master master
Secondary_check_script	When MHA Manager detects master failure, it does not immediately determine if the master has been down, but instead, the master cannot be considered to be unresponsive by SSH to the secondary_check_script-defined host again after a failed detection. This also prevents the phenomenon of brain fissure to some extent.
Master_ip_failover_script	When Failvoer, control the VIP transfer script. There are usually two ways to achieve this: (1) by order "/sbin/ifconfig eth1:1 192.168.56.188"; (2) combining keepalived to achieve
Master_binlog_dir	The full path used to store binary logs. This parameter is used to connect to the MySQL server via ssh when MySQL is dead on master and find the need for binary log events. This parameter can be used to help the binary log storage location that cannot be found after the master process has died. You can set multiple storage locations separated by commas such as:/var/lib/mysql,/var/lib/mysql3307

App1.conf Parameter file Introduction (acting on a group of Master/slave structures)

#cat/etc/app1.conf

[Server default]

Manager_log=/var/log/masterha/app1/app1.log

Manager_workdir=/var/log/masterha/app1

Remote_workdir=/var/log/masterha/app1

[Server1]

Candidate_master=1

Check_repl_delay=0

hostname=192.168.56.112

port=3306

[Server2]

Candidate_master=1

Check_repl_delay=0

hostname=192.168.56.110

port=3306

[Server3]

Check_repl_delay=0

hostname=192.168.56.113

Ignore_fail=1

No_master=1

port=3306

Parameter explanation:

Manager_log	Specifies the file name log file for the absolute path of the MHA Manager
Manager_workdir	Specifies that the MHA manager produces the full path of the associated state file. If not set default is/var/tmp
Remote_workdir	The full path name of the working directory on MHA node. If not present, MHA node is automatically created, and if not allowed, MHA node automatically exits unexpectedly
[Server1] [Server2] [Server3]	In each app, define the properties for each host.
Candidate_master	The function is when the design Candidate_master = 1 o'clock, this server has a higher priority promoted to the new master (condition: Open binglog, service without delay) if multiple machines are set Caddidate_master = 1, the precedence policy depends on the block name ([ SERVER_XXX]), such as: [server_1] Superior title higher than [server_2]
Check_repl_delay	By default, when a slave synchronization delay exceeds 100M relay log (requires more than 100M relay log), MHA does not select this slave as the new master for failover, because recovery takes a long time. When the Check_ is set　Repl_delay = 0, MHA ignores the synchronization delay on the selected slave. This option is particularly useful in cases where the expectation of the Candidate_master = 1 Special statement is that the machine becomes master.
Hostname	Configure the MySQL server's machine name or IP address, this configuration item is required, and can only be configured under [Server_xxx] this block.
Ignore_fail	By default, MHA Manager does not fail over master in the event of a slave failure.　When ignore_fail = 1 o'clock is set, MHA will fail over at all times when the machine is in trouble. The default is 0.
No_master	When a server with No_master = 1 is set, this server will never be promoted to the new master
Port	The port number of the DB, which is 3306:port=3307 by default

Handling of relay logs:

By default, the trunk logs from the server are automatically deleted when the SQL thread finishes executing. However, these relay logs may be used when restoring other slave servers,

Therefore, you need to disable automatic purging of the trunk log and periodically purge the old relay logs. Periodic cleanup of the relay log requires consideration of replication latency issues. Under the Ext3 file system,

Deleting large files takes a certain amount of time and can cause serious replication delays. To avoid replication delays, create a hard link for the trunk log temporarily.

The MHA node contains the pure_relay_logs command tool, which creates a hard link for the trunk log, executes the set GLOBAL relay_log_purge=1, waits a few seconds for the SQL thread to switch to the new trunk log,

Then execute set GLOBAL relay_log_purge=0.

The Pure_relay_logs parameter is as follows:

–user MySQL user name

–password mysql Password

–host MySQL server address

–port Port number

–workdir Create and delete the trunk log hard-link directory. After the script is executed successfully, the hard-linked trunk log file is deleted. The default directory is/var/tmp.

So the purge_relay_logs script removes the trunk log without blocking the SQL thread, setting the scheduled task on each slave node

Clear the relay log periodically
0 * * */usr/bin/purge_relay_logs-user=yyf-password=yyf-disable_relay_log_purge-port=3307-host=192.168.56.110 &G T;>/var/log/masterha/app1/purge_relay_logs.log 2>&1

At this point, the MHA configuration is complete. It's simple. The configuration file only needs to exist on the MHA manager side. Related tests: will be introduced in the next article, please poke open: Click to open the link

The next step is to start MHA monitoring:

[[email protected] scripts] #nohup masterha_manager--conf=/etc/app1.cnf--remove_dead_master_conf--ignore_last_ Failover </dev/null >/var/log/masterha/app1/app1.log 2>&1 &

View MHA Status:

[Email protected] ~]# Masterha_check_status--conf=/etc/app1.cnf
App1 (pid:9130) is running (0:PING_OK), master:192.168.56.110

Stop MHA Monitoring:

[Email protected] ~]# masterha_stop--conf=/etc/app1.cnf

Explain:

--conf a set of master/slave profiles that read the global configuration file at the same time

/etc/masterha_default.cnf

--remove_dead_master_conf This parameter means that when the master-slave switch occurs, the old master library's IP will be removed from the configuration file (app1.conf).

--ignore_last_failover by default, if the MHA detects a continuous outage and two downtime is less than 8 hours, failover is not performed. This limitation is to avoid the ping-pong effect. This parameter represents ignoring the file generated by the last MHA trigger switch, which, by default, MHA in the log directory, which is the/var/log/masterha/I set above. The app generates the App1.failover.complete file, and the next time you switch it, the file will not be allowed to trigger the switch if it is found in the directory, unless the file is deleted after the first switchover and is set to--ignore_last_failover for convenience.

After monitoring is turned on, you can do the relevant tests and kill the MySQL process on master (pkill MySQL). The test will not be automated failover test, in order to shorten the length of the article, related testing

Please poke open: Click on the link

Keepalived Installation:

Software Download latest version:http://www.keepalived.org/software/

Keepalived-1.2.13.tar.gz

#yum-y Install Openssl-devel

#tar-ZXVF keepalived-1.2.13.tar.gz

# CD keepalived-1.2.13

#./configure--prefix=/usr/local/keepalived

#make && make Install

#cp/usr/local/etc/rc.d/init.d/keepalived/etc/rc.d/init.d/

#cp/usr/local/etc/sysconfig/keepalived/etc/sysconfig/

#mkdir/etc/keepalived

#cp/usr/local/etc/keepalived/keepalived.conf/etc/keepalived/

#cp/usr/local/sbin/keepalived/usr/sbin/

keepalived Configuration and configuration files (configuration on Master Master 192.168.56.110)

[Email protected] ~]# cat/etc/keepalived/keepalived.conf

! Configuration File for Keepalived

Global_defs {

Notification_email {

[Email protected]

[Email protected]

[Email protected]

}

Notification_email_from [email protected]

Smtp_server 192.168.200.1

Smtp_connect_timeout 30

router_id Lvs_devel

}

Vrrp_instance Vi_1 {

State backup

Interface eth1

Nopreempt

VIRTUAL_ROUTER_ID 51

Priority 100

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1111

}

virtual_ipaddress {

192.168.56.188

}

Notify_master "/etc/keepalived/arp.sh"

}

Where router_id MySQL ha represents the name of the set keepalived Group, Bind 192.168.56.188 This virtual IP to the host's eth1 NIC, and set the status to Backup mode, set the keepalived mode to non-preemption mode (NOPREEMPT), and priority 150 indicates that the setting has a precedence of 150. The following configurations are slightly different, but they are all meant to be.

The configuration on Candidate Master (192.168.56.112) is as follows:

[Email protected] ~]# cat/etc/keepalived/keepalived.conf

! Configuration File for Keepalived

Global_defs {

Notification_email {

[Email protected]

[Email protected]

[Email protected]

}

Notification_email_from [email protected]

Smtp_server 192.168.200.1

Smtp_connect_timeout 30

router_id Lvs_devel

}

Vrrp_instance Vi_1 {

State Master

Interface eth1

Nopreempt

VIRTUAL_ROUTER_ID 51

Priority #此处很重要 must be lower than the current master precedence level. Value is smaller than Master master

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1111

}

virtual_ipaddress {

192.168.56.188

}

Notify_master "/etc/keepalived/arp.sh" # # #定义的清除arp路由的脚本.

}

At this point, turn on the master master and the keepalived process on the candidate master:

Service keepalived Start|stop|restart

#ip the Add command to view the status of the VIP's bindings:

[[Email protected] keepalived]# IP add

1:lo: <LOOPBACK,UP,LOWER_UP> MTU 65536 qdisc noqueue State UNKNOWN

Link/loopback 00:00:00:00:00:00 BRD 00:00:00:00:00:00

inet 127.0.0.1/8 Scope host Lo

INET6:: 1/128 Scope Host

Valid_lft Forever Preferred_lft Forever

2:eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> MTU Qdisc pfifo_fast State up Qlen 1000

Link/ether 08:00:27:70:b3:d8 BRD FF:FF:FF:FF:FF:FF

inet 192.168.2.40/24 BRD 192.168.2.255 Scope Global eth0

Inet6 FE80::A00:27FF:FE70:B3D8/64 Scope link

Valid_lft Forever Preferred_lft Forever

3:eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> MTU Qdisc pfifo_fast State up Qlen 1000

Link/ether 08:00:27:75:5b:94 BRD FF:FF:FF:FF:FF:FF

inet 192.168.56.110/24 BRD 192.168.56.255 Scope Global eth1

inet 192.168.56.188/32 Scope Global eth1

Inet6 FE80::A00:27FF:FE75:5B94/64 Scope link

Valid_lft Forever Preferred_lft Forever

MySQL mha+keepalived