MySQL high-availability scenarios for mysql-mmm, Mysql-proxy, and keepalived

Mysql-mmm is the MySQL master Replication Manager that implements the following functions:

High Availability ( VIP Floating technology similar to keepalived)

Only one database write operation is provided at the same time to ensure database consistency.

upgrade slave to master, continuation of dual-master architecture

First set up a database Master master, master-slave Architecture: Similar diagram: (no longer narrated)

Monitor the current synchronization situation for the Monitor's machine in each database :

Grant replivation client to ' [email protected] ' IP ' identified ' password ' creates a mmm-monitor account, primarily for monitoring and synchronization

Grant Super, replication client, process on * * to ' user ' @ ' IP ' identified by ' password '

Create a mmm-agent account for various operations on the client

mysql-mmm for mysql-mmm download URL http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

because the way to use Epel is to change the mirrorlist in/etc/yum.repos.d/epel.repo to BaseURL

===========================================================================

Configure mmm_common.conf files: All nodes have the same configuration, including the monitoring side

Active_master_role writer

Cluster_interface eth0
Pid_path/var/run/mysql-mmm/mmm_agentd.pid
bin_path/usr/libexec/mysql-mmm/
Replication_user CCD//Sync Account
Replication_password CCD
Agent_user CCD//large-bit permissions
Agent_password CCD
IP 172.20.138.212
Mode Master
Peer DB2
IP 172.20.138.209
Mode Master
Peer DB1
<role writer>
Hostsdb1,db2
IPs 172.20.138.100
Mode exclusive//exclusive for single write
</role>

<role reader>
Hosts B1,DB2
IPs 172.20.138.250//can have multiple IPs, each database can have a VIP individually
Mode balanced//read for load Balancing
</role>
============================================================================
 to modify different db values in the mmm_agent.conf file
finally configure the mmm_mon.conf
The main areas of change are:
Ping_ips is the real IP of the monitored host ,
Monitor modifies the account assigned to the database
Monitor_assword Password
 start the service in/etc/init.d/and enter the command in the monitoring server:
Mmm_control checks Check all database synchronization and operating conditions
Mmm_control show View VIP bindings, similar to Ipvsadm
Mysql-mmm not suitable for MySQL master-slave high load
verify that the VIP can jump, but found that read IP also has the ability to write data .... If you add read/write separation, you can realize that the VIP read does not have the function of writing
Mysql-proxy for load balancing and read-write separation
The current version of Mysql-proxy is 0.8.5aplha
need to install LUA before installing mysql-proxy
Download lua source files to the website
Yum-y Install Readline-devel Ncurses-devel
installation Readline-devel and the Ncurses-devel otherwise the installation will appear ReadLine a problem that can't be found
Tar zxvf luaxxxxx
Make Linux
Make install
This time input Lua can enter the compilation mode representation Lua Installation Successful
Install the required dependency packages
Yum install gcc* gcc-c++* autoconf*automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig*
Download the latest mysql-proxy source file from mysql-proxy website
Installation is easy to use after decompression
 Add Mysql-proxy to the corresponding environment variable
Export PATH = $PATH:/usr/local/mysql-proxy
Source/etc/profile
Modify The Mysql-proxy configuration file
The common command parameters are as follows:
--daemon starts in daemon mode
--admin-address=:4401 default port is 4401
-help View All commands
-default-file can configure the startup configuration file
--proxy-backend-addresses= $host: $port multiple comma-separated
--proxy-address=:3307
--proxy-backend-addresses=:3306 The port of the MySQL host
--proxy-read-only-backend-address=xxx13306 read-only MySQL host port
--proxy-read-only-backend-address=xxx23306 can specify ports for multiple read-only MySQL hosts
--proxy-lua-script=/usr/local/xxx/rw-splitting.lua using a system-only Lua script for read and write separation functions
two parameters need to be modified using the system's own Rw.split.lua script
Min_idle_connections=1
Max_idle_connections = 3
Startup script:
#!/bin/bash
Mode=$1
If [-Z "$mode"]; then
Mode= "Start"
Fi
Case $mode in
' Start ')
Mysql-proxy--daemon--proxy-address=:3307--proxy-backend-addresses=172.20.138.209:3306;172.20.138.212:3306-- proxy-read-only-backend-addresses=172.20.138.212:3306--proxy-lua-script=/usr/local/mysql-proxy/share/doc/ Mysql-proxy/rw-splitting.lua
;;
' Stop ')
Killall Mysql-proxy
;;
Esac
Exit 0
Or use the configuration method to start
Vim/etc/mysql-proxy.cnf
Admin-username = CCD
Admin-password = CCD
Daemon = True
KeepAlive = True
Proxy-backend-addresses =172.20.138.209:3306;172.20.138.212:3306
Proxy-read-only-backend-addresses =172.20.138.212:3306
Proxy-lua-script =/usr/local/proxy-mysql/share/doc/mysql-proxy/rw-splitting.lua
Admin-lua-script =/usr/local/proxy-mysql/share/doc/mysql-proxy/admin-sql.lua
Log-file =/usr/local/proxy-mysql/cn.log
Log-level = Debug
Modify the corresponding script to:
Mysql-proxy–defaults-file =/etc/mysql-proxy.cnf
tests for a database can be tested using the sysbench tool
 High Availability of MySQL master /master with keepalived 
To configure the keepalived.conf file:
Vrrp_instatance ha_1{
State BACKUP # is configured as slave, or it can be master
Interface eth0
VIRTUAL_ROUTER_ID// master-slave configuration of the virtual route identity needs the same
Priority # Configure different priorities
Advert_int 1 # Configure the cycle of synchronization checks between primary and standby
authenication{
Auth_type PASS
Auth_pass 1111
}
virtual_ipaddress {
VIP/24 Dev eth0
}
}
Virtual_server xxxxxx 3306{
Delay_loop 2
Lb_algo WRR//lvs algorithm for weighted polling
Lb_kind Dr//lvs mode for Dr mode
Protocol TCP
Reall_server xxxx 3306 {
Weight 1
Tcp_check {
Connect_timeout 3
Nb_get_retry 3
Delay_before_retry 3
Connect_port 3306
}
Real server xxxx 3306{
Ibid .
}
}
DR 's real serverloop Port needs to bind The VIP's address script as:
#!/bin/bash
sns_vip=172.138.20.244
./etc/rc.d/init.d/functions
Case "$" in
Start
Ifconfig lo:0 $SNS _vip netmask255.255.255.255 broadcast $SNS _VIP
/sbin/route add-host $SNS _vip Dev lo:0
echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore
echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce
echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore
echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce
Sysctl-p >/dev/null 2>&1
echo "Realserver Start OK"
;;
Stop
Ifconfig lo:0 Down
Route del $SNS _VIP >/dev/null2>&1
echo "0" >/proc/sys/net/ipv4/conf/lo/arp_ignore
echo "0" >/proc/sys/net/ipv4/conf/lo/arp_announce
echo "0" >/proc/sys/net/ipv4/conf/all/arp_ignore
echo "0" >/proc/sys/net/ipv4/conf/all/arp_announce
echo "Realserver stoped"
;;
*)
echo "Usage: $ {start|stop}"
Exit 1
Esac
Exit 0
authorized remote host can connect to VIP via VIP
Grant allprivileges to On *. * to xxxx Identifiedby xxxxx
Flushprivileges
This allows for high availability of MySQL databases
There are two ways to use keepalived to implement VIP switching for keepalived plus scripting , and the other is Keepalived+lvs the way to achieve high availability of the database
================================================================================

This article from "East God to a dozen five" blog, declined reprint!
MySQL high-availability scenarios for mysql-mmm, Mysql-proxy, and keepalived