MySQL high availability solutions

MySQL high availability solutions

Mysql-mmm is the master replication manager of mysql, which provides the following functions:
High Availability (similar to keepalived's vip Floating Technology)
Only one database write operation is provided at the same time to ensure Database Consistency.
Upgrade slave to master to continue dual-master Architecture
 
First, set up the database master and slave architecture: similar figure: (not described)
Monitor the current synchronization status for the monitor machine in each database:
Grant replivation client to 'user @ 'IP' identified by 'Password' to create an mmm-monitor account, which is mainly used for monitoring and synchronization.
Grant super, replication client, process on *. * to 'user' @ 'IP' identified by 'Password'
Create an mmm-agent account for various client operations
 
For Mysql-mmm

------------------------------------------ Split line ------------------------------------------

Free in http://linux.bkjia.com/

The username and password are both www.bkjia.com

The specific download directory is available in/July 6,/July 20,/MySQL high availability solutions/

For the download method, see

------------------------------------------ Split line ------------------------------------------

Because the epel method is used, you need to change the upload list in/etc/yum. repos. d/epel. repo to baseurl.
 
========================================================== ==========================================

Configure the mmm_common.conf file: All nodes have the same configuration, including the monitoring end.

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 // synchronous account
    replication_password    ccd
Agent_user ccd // larger permission
    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 is a single write
</role>
 
<role reader>
    Hosts b1,db2
Ips 172.20.138.250 // you can have multiple ip addresses. Each database can have a single vip address.
Mode balanced // read as Server Load balancer
</role>
========================================================== ==========================================
Modify different db values in the mmm_agent.conf File
 
Finally, configure mmm_mon.conf.
The main changes include:
Ping_ips is the real ip address of the monitored host,
Monitor is changed to the account assigned by the database
Monitor_assword Password
 
Start the service in/etc/init. d/and enter the following command in the monitoring server:
Mmm_control checks check all Database Synchronization and Running Conditions
Mmm_control show to check the vip binding status, similar to ipvsadm.
 
Mysql-mmm is not suitable for mysql master-slave high load
Verify that the vip can be redirected, but the read ip can also write data .... If read/write splitting is added, the read vip does not have the write function.
 
Mysql-proxy for load balancing and read/write splitting
The current mysql-proxy version is 0.8.5aplha.
 
You must install lua before installing mysql-proxy.
Download the lua source file from the official website.
Yum-y install readline-devel ncurses-devel
Install readline-devel and ncurses-devel. Otherwise, readline cannot be found during installation.
Tar zxvf luaxxxxx
Make linux
Make install
In this case, you can enter the compilation mode to indicate that lua is successfully installed.
 
Install the required dependency package
Yum install gcc * gcc-c ++ * autoconf * automake * zlib * libxml * ncurses-devel * libmcrypt * libtool * flex * pkgconfig *
 
Download the latest mysql-proxy source file from the mysql-proxy website.
The installation is very simple and can be used after decompression
Add mysql-proxy to the corresponding environment variable
Export PATH = $ PATH:/usr/local/mysql-proxy
Source/etc/profile
 
 
Modify the configuration file of mysql-proxy
Common command parameters are as follows:
-- Daemon is started in daemon mode.
-- Admin-address =: 4401 the default port is 4401.
-Help to view all commands
-Default-file: configures the startup configuration file.
-- Proxy-backend-addresses = $ host: $ port multiple IP addresses are separated by commas (,).
-- Proxy-address =: 3307
-- Proxy-backend-addresses =: 3306 port of the mysql host
-- Proxy-read-only-backend-address = xxx13306 port of the read-only mysql host
-- Proxy-read-only-backend-address = xxx23306 you can specify ports for multiple read-only mysql hosts.
-- Proxy-lua-script =/usr/local/xxx/rw-splitting.lua use the system-only lua script for read/write splitting
 
To use the rw. split. lua script that comes with the system, you need to modify two parameters.
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:
Mysql-proxy-defaults-file =/etc/mysql-proxy.cnf
 
You can use the sysbench tool to test the database.
 
Use keepalived to achieve high availability of mysql Master/Slave
Configure the keepalived. conf file:
Vrrp_instatance HA_1 {
State BACKUP # Set to slave or master
Interface eth0
Virtual_router_id 80 // The virtual route ID configured for the master and slave nodes must be the same
Priority 100 # configure different priorities
Advert_int 1 # configure the synchronization check period between the master and slave nodes
Authenication {
Auth_type PASS
Auth_pass 1111
}
Virtual_ipaddress {
Vip/24 dev eth0
}
}
Virtual_server XXXXXXXX 3306 {
Delay_loop 2
Lb_algo wrr // The lvs algorithm is weighted round robin.
Lb_kind DR // lvs mode: 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 {
Same as above
}
}
 
The script to bind the real serverloop port of DR to the vip address is:
#! /Bin/bash
SNS_VIP = 172.138.20.244
./Etc/rc. d/init. d/functions
Case "$1" in
Start)
Ifconfig lo: 0 $ SNS_VIP netmask?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: $0 {start | stop }"
 
Exit 1
 
Esac
 
Exit 0
 
Authorize a remote host to connect to the vip through vip
Grant allprivileges to on *. * to xxxx identifiedby xxxxx
Flushprivileges
This enables High Availability of mysql databases.
 
There are two ways to use keepalived: one is to use the keepalived plus script to implement vip switching, and the other is to use keepalived + lvs to achieve high Database Availability.
MySQL high availability architecture-MMM Configuration
High Availability of MySQL: Keepalived + MySQL (dual-master hot standby)
MySQL high availability solution Cluster)
Use KeepAlived to build a MySQL high-availability environment
Set up MySQL high-availability architecture MHA in CentOS 6.4
This article permanently updates the link address: