mysql双库冗余_keepalived+mysql双主实现数据库冗余

keepalived+mysql双主实现数据库冗余

主1：172.16.114.15

主2：172.16.114.16

一、安装mysql,并设置主服务器

1、将下面的代码复制到一个后缀为.sh的shell脚本文件中(脚本很容易看懂)

# 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

#定义一个数值mysql(需要同步的具体某个数据库，需要同步所有数据库，就将这段以及后面有关代码删掉即可)

echo "Enter the Sync database"

read mysql

cat > /etc/my.cnf <

#做一些数据库优化

[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 = 1  #主1设置为1，主2设置为2

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

binlog-do-db = $mysql

replicate-do-db = $mysql

log-bin=mysql-bin   #要开起二进制日志

log-slave-updates   #同步更新

Eof

service mysqld restart

chkconfig mysqld on

2、添加脚本执行权限

# chmod  /etc/scripts/mysql.sh

3、授权mysql允许其同步数据库的帐号

(1)在两台机上开启slave复制

mysql> flush tables;

mysql>show master status\G;

***************************1. row ***************************

File: mysql-bin.000002

Position: 106

Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

ERROR:

No query specified

mysql> slave stop

-> ;

Query OK, 0 rows affected(0.00 sec)

mysql>  CHANGE MASTER TOMASTER_HOST='172.16.114.16',MASTER_PORT=3306, MASTER_USER='loyu', MASTER_PASSWORD='123456',  MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=106;

# 在主2只需修改(地址指向主1)MASTER_HOST='172.16.114.15

Query OK, 0 rows affected(0.03 sec)

mysql> start slave;

Query OK, 0 rows affected(0.00 sec)

mysql>show slave status\G;

***************************1. row ***************************

Slave_IO_State: Waiting formaster to send event

Master_Host: 172.16.114.16

Master_User: loyu

Master_Port: 3306

Connect_Retry: 10

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 106

Relay_Log_File:mysqld-relay-bin.000002

Relay_Log_Pos: 251

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

....

(2)测试

主1：

在主1上创建数据库

mysql> create databaseliuyuqing;

Query OK, 1 row affected(0.01 sec)

mysql> use liuyuqing;

Database changed

mysql> create tableloyu(number int);

Query OK, 0 rows affected(0.02 sec)

mysql> insert into loyuvalues(1011);

Query OK, 1 row affected (0.00 sec)

在主2上查看复制情况

(可以看到数据库已经同步成功)

主2：

在第主2上创建数据库

在主1上查看数据库复制情况

(可以看到数据也是同步过来的)

二、安装keepalived

1、将如下代码复制到后缀名为.sh的shell脚本文件

# vi /etc/scripts/keepalived.sh

##!/bin/bash

yum install keepalived -y

#定义keepalived的虚拟IP

echo "VIP:"

read VIP

#定义主服务器，还是备份服务器

echo "MASTER OR BACKUP:"

read state

#定义优先级

echo "priority: (MASTER:100 BACKUP:99)"

read priority

#定义虚拟路由id，主备要一样

echo "virtual_router_id:"

read virtual_router_id

cat > /etc/keepalived/keepalived.conf <

#! 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 主从都修改为60

priority $priority #在mysql-ha2上LVS上修改成80

advert_int 1

#     nopreempt #不抢占资源，意思就是它活了之后也不会再把主抢回来

authentication {

auth_type PASS

auth_pass 1111

}

track_script {

chk_haproxy

}

virtual_ipaddress {

$VIP

}

}

Eof

# 检查数据库服务状态，一旦服务停止，keepalived也停止

echo "Enter the database password"

read password

cat /etc/keepalived/check_slave.sh <

##/bin/bash

Mysql=$(mysql -uroot -p$password -N -s -e "select 10")

if [ $? -ne 0 ] || [ "$Mysql" -ne "10" ];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、添加脚本执行权限

# chmod  /etc/scripts/keepalived.sh

三、接下来就可以用自己定义的VIP来访问数据库了