Using Keepalived to build master-master MySQL Replication

Using Keepalived to build master-master MySQL Replication

Overview:

MySQL-HA has many implementation solutions. It is common to use master + keepalived to implement MySQL-HA. The two MySQL instances have a master-slave relationship and use Keepalived to configure virtual IP addresses, when one of the MySQL Databases goes down, the application can automatically switch to another MySQL database to ensure high system availability. In the actual production environment, it is also deployed through this solution, and I believe there should be something worth learning from;

2. Environment Description

Steps for setting up Master-master replication for MySQL (see the link)

Host Name	IP address	Virtual ip (VIP)
Mysql01	192.168.47.149	192.168.47.140
Mysql02	192.168.47.148	192.168.47.140

Set the corresponding host file on the two databases

Vim/etc/hosts
127.0.0.1 Mysql01
Localhost Mysql01
192.168.47.149 Mysql01
192.168.47.148 Mysql02

Install Keepalived

3.1 # yum install keepalived-y

3.2 modify the parameter file in Mysql01

Cat/etc/keepalived. conf

#! Configuration File for keepalived

Global_defs {

Router_id Mysql01 # change it to your host name

}

################## Part 1 ###################

Vrrp_instance VI_1 {

State BACKUP # all changed to BACKUP

Interface eth0

Virtual_router_id 60 # The default 51 master and slave are changed to 60

Priority 100 # priority (between 1 and), and the other is changed to 90. The priority of the slave node must be lower than that of the master node.

Advert_int 1

Nopreempt # do not seize resources, that is, it will not take the master back after it is active

Authentication {

# Set verification information. The two nodes must be consistent.

Auth_type PASS

Auth_pass 1111

}

Virtual_ipaddress {

192.168.47.140

}

}

################## Part 2 ###################

Virtual_server 192.168.47.140 3306 {

Delay_loop 6

Lb_algo wrr # LVS Algorithm

Lb_kind DR # LVS Mode

Nat_mask 255.255.255.0

Persistence_timeout 50 # session persistence time

Protocol TCP

Real_server 192.168.47.149 3306 {

Weight 1

Notify_down/usr/local/script/mysql. sh # Check the script executed after the service is down.

TCP_CHECK {

Connect_timeout 10 # connection timeout

Nb_get_retry 3 # Number of reconnections

Connect_port 3306 # Health Check Port

}

}

}

3.3 modify the parameter file in Mysql02

Cat/etc/keepalived. conf

#! Configuration File for keepalived

Global_defs {

Router_id Mysql02 # change it to your host name

}

################## Part 1 ###################

Vrrp_instance VI_1 {

State BACKUP # all changed to BACKUP

Interface eth0

Virtual_router_id 60 # The default 51 master and slave are changed to 60

Priority 80 # modify to 100 on mysql-ha1 LVS

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1111

}

Virtual_ipaddress {

192.168.47.140

}

}

################## Part 2 ###################

Virtual_server 192.168.47.140 3306 {

Delay_loop 6

Lb_algo wrr

Lb_kind DR

Nat_mask 255.255.255.0

Persistence_timeout 50

Protocol TCP

Real_server 192.168.47.1403306 {

Weight 1

Notify_down/usr/local/script/mysql. sh # judge the database status

TCP_CHECK {

Connect_timeout 10

Nb_get_retry 3

Connect_port 3306

}

}

}

Parameter description:

Virtual_ipaddress: virtual IP address (VIP)

Interface: the name of the NIC device.

Virtual_server VIP: and Mysql service port

Real_server: Real Server address

3.4 create the following scripts on two mysql Databases respectively to determine the database status

Vim/usr/local/script/mysql. sh

#! /Bin/bash

/Etc/init. d/keepalived stop

/Etc/init. d/keepalived start

3.5 common keepalived scripts

Start the keepalived service: service keepalived start

View keepalived service: service keepalived status

Stop the keepalived service: service keepalived stop

3.6 Test

A) use the PS command to check the service running status

Ps-ef | grep keepalived

Ps-ef | grep mysql

B) Check the system logs and make sure that keepalived runs correctly.

Less/var/log/message

Tail-f/var/log/messages

C) view the running status of keepalived by ip

Note: Start mysql and then start keepalived.

Load Nginx in Ubuntu for high-performance WEB Server 5 --- MySQL master/Master Synchronization

Production Environment MySQL master/Master synchronization primary key conflict handling

MySQL Master/Slave failure error Got fatal error 1236

MySQL master-slave replication, implemented on a single server

Build a MySQL proxy server for read/write splitting + Master/Slave Synchronization

MySQL 5.5 master-slave bidirectional Synchronization

MySQL 5.5 master-slave synchronization troubleshooting

MySQL master-slave replication asynchronous semi-sync instance

This article permanently updates the link address: