Keepalived+mysql master-Slave Implementation of database redundancy

Keepalived+mysql Master-Slave Implementation of database redundancy

First, install MySQL, and set up the primary and standby server

1. Copy the following code into a shell script file with the suffix. SH (the script is easy to read)

# vi/etc/scripts/mysql.sh

##/bin/bash

RPM-UVH http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

Yum-y install MySQL mysql-server mysql-devel

#定义一个数值Level (Set database master and slave)

echo "Enter the" Master "(1) or" Slave "= (2)"

Read level

#定义一个数值mysql (a specific database that needs to be synchronized and all databases need to be synchronized, delete this and the code that follows)

echo "Enter the Sync database"

Read MySQL

Cat >/etc/my.cnf <<eof

#做一些数据库优化

[MySQL]

# CLIENT #

Port = 3306

Socket =/var/lib/mysql/mysql.sock

[Mysqld]

# General #

user = MySQL

Default-storage-engine = InnoDB

Socket =/var/lib/mysql/mysql.sock

Pid-file =/var/lib/mysql/mysql.pid

# MyISAM #

Key-buffer-size = 32M

Myisam-recover = Force,backup

# SAFETY #

Max-allowed-packet = 16M

Max-connect-errors = 1000000

# DATA STORAGE #

DataDir =/var/lib/mysql/

# BINARY LOGGING #

Log-bin =/var/lib/mysql/mysql-bin

Expire-logs-days = 14

Sync-binlog = 1

# REPLICATION #

Relay-log =/var/lib/mysql/relay-bin

Slave-net-timeout = 60

# CACHES and LIMITS #

Tmp-table-size = 32M

Max-heap-table-size = 32M

Query-cache-type = 0

Query-cache-size = 0

Max-connections = 500

Thread-cache-size = 50

Open-files-limit = 65535

Table-definition-cache = 4096

Table-open-cache = 4096

# INNODB #

Innodb-flush-method = O_direct

Innodb-log-files-in-group = 2

Innodb-log-file-size = 128M

Innodb-flush-log-at-trx-commit = 2

innodb-file-per-table = 1

Innodb-buffer-pool-size = 2G

# LOGGING #

Log-error =/var/lib/mysql/mysql-error.log

Log-queries-not-using-indexes = 1

Slow-query-log = 1

Slow-query-log-file =/var/lib/mysql/mysql-slow.log

Srver-id = $Level

#需要同步整个数据库就将下面两段代码删掉

Binlog-do-db = $mysql

Replicate-do-db = $mysql

Log-slave-updates

Sync_binlog = 1

Auto_increment_offset = $Level

Auto_increment_increment = 2

Eof

Service mysqld Restart

Chkconfig mysqld on

2. Add Script Execution permissions

# chmod/etc/scripts/mysql.sh

Second, installation keepalived

1. Copy the following code to the shell script file with the suffix named. sh

# vi/etc/scripts/keepalived.sh

##!/bin/bash

Yum Install Keepalived-y

#定义keepalived的虚拟IP

echo "VIP:"

Read VIP

#定义主服务器, or back up the server

echo "MASTER OR BACKUP:"

Read State

#定义优先级

echo "Priority: (master:100 backup:99)"

Read priority

#定义虚拟路由, the main thing.

echo "virtual_router_id:"

Read virtual_router_id

Cat >/etc/keepalived/keepalived.conf <<eof

#! Configuration File for Keepalived

Global_defs {

#router_id MYSQL-HA1 #修改为自己的主机名

}

Vrrp_script Chk_haproxy {

Script "/etc/keepalived/chk_slave.sh"

Interval 2

Weight 2

}

Vrrp_instance Vi_1 {

State $state #都修改成BACKUP

Interface eth0

virtual_router_id $virtual _router_id #默认51 master and slave are modified to 60

Priority $priority modified to 80 on LVS on #在mysql-HA2

Advert_int 1

# nopreempt #不抢占资源, meaning it won't take the Lord back after he's alive

Authentication {

Auth_type PASS

Auth_pass 1111

}

Track_script {

Chk_haproxy

}

virtual_ipaddress {

$VIP

}

}

Eof

echo "Enter The database Password"

Read password

Cat/etc/keepalived/check_slave.sh <<eof

##/bin/bash

mysql=$ (mysql-uroot-p$password-n-s-e "SELECT 10")

If [$?-ne 0] | | ["$Mysql"-ne "ten"];then

Service keepalived Stop

Exit 1

Else

Slavestatus= ($ (mysql-uroot-p$password-e "show slave status\g;" | grep "_running" |awk ' {print $NF} '))

if ["$SlaveStatus [0]" = "No"] | | ["${slavestatus[1]}" = "No"];then

Service keepalived Stop

Fi

Fi

Eof

2. Add Script Execution permissions

# chmod/etc/scripts/keepalived.sh

This article from "Meteor Yu" blog, declined reproduced!

Keepalived+mysql master-Slave Implementation of database redundancy