云计算平台(数据篇)-MySql高可用平台搭建Master-Master

使用keepalived营造高可用MySQL-HA

 

介绍利用keepalived创设高可用MySQL-HA,保险两台MySQL数据的1致性,然后用keepalived完结虚拟IP,通过keepalived自带的服务监察和控制成效来兑现MySQL故障时自动切换。

AD:

 

关于MySQL-HA,目前有八种搞定方案,比方heartbeat、drbd、mmm、共享存储,可是它们各有利弊。heartbeat、drbd配置较为复杂,供给和谐写脚本能力兑现MySQL自动切换,对于不会脚本语言的人的话,那的确是一种脑裂难点;对于mmm,生产条件中十分少有人用,且mmm
管理端供给独自运维1台服务器上,若是想完成高可用,就得对mmm管理端做HA,那样确实又充实了硬件开垦;对于共享存款和储蓄,个人以为MySQL数据照旧放在地面较为安全,存款和储蓄设备毕竟存在单点隐患。使用MySQL双master+keepalived是一种极度好的化解方案,在MySQL-HA情状中,MySQL互为主从涉嫌,那样就确认保障了两台MySQL数据的一致性,然后用keepalived实现虚拟IP,通过keepalived自带的劳动监督成效来贯彻MySQL故障时自动切换。

上边,小编把就要上线的贰个生育境遇中的架构与大家享受一下,看一下以此架构中,MySQL-HA是何许兑现的,情况拓扑如下

MySQL-VIP:192.168.1.90

MySQL-master1:192.168.1.91

MySQL-master2:192.168.1.92

 

OS版本:CentOS 5.4

MySQL版本:5.0.89

Keepalived版本:1.1.20

一、MySQL master-master配置

[[email protected] ~]# mysql -uroot

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.0.77 Source distribution



Type 'help;' or '\h' for help. Type '\c' toclear the buffer.



mysql> GRANT ALL PRIVILEGES ON *.* [email protected]'%';

Query OK, 0 rows affected (0.01 sec)



mysql> use mysql;

Reading table information for completion oftable and column names

You can turn off this feature to get aquicker startup with -A



Database changed

mysql> update user setPassword=password('bee') where user='root';

Query OK, 4 rows affected (0.01 sec)

Rows matched: 4 Changed: 4 Warnings: 0

mysql> show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| MySQL-bin.000001 | 328 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

1、修改MySQL配置文件

[mysqld]

log-bin=MySQL-bin

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

server-id = 2

replicate-same-server-id = 0

auto-increment-increment = 2

auto-increment-offset = 2



replicate-ignore-db=test

replicate-ignore-db=mysql

slave-skip-errors=all

# Default to using old password format forcompatibility with mysql 3.x

# clients (those using the mysqlclient10compatibility package).

old_passwords=1



# Disabling symbolic-links is recommended toprevent assorted security risks;

# to do so, uncomment this line:

# symbolic-links=0

[replication]

master-host=192.168.1.91

master-user=replication

master-password=replication

master-port=3306

master-connect-retry=60



[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

两台MySQL均如要开启binlog日志功效,开启方法:在MySQL配置文件[MySQLd]段中加上log-bin=MySQL-bin选项

两台MySQL的server-ID不能够同一,私下认可景况下两台MySQL的serverID皆以1,需将个中一台修改为二就可以

二、将1九贰.16八.一.20壹设为1九二.168.一.20二的主服务器

在1玖贰.168.一.20一上新建授权用户

MySQL> grant replication slave on *.* to 'replication'@'%' identified by 'replication';

Query OK, 0 rows affected (0.00 sec)



MySQL> show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| MySQL-bin.000003 | 374 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

在1九二.16八.一.20第22中学将1九二.168.一.20壹设为自个儿的主服务器

MySQL> change master to master_host='192.168.1.201',master_user='replication',master_password='replication',master_log_file='MySQL-bin.000003',master_log_pos=374;

Query OK, 0 rows affected (0.05 sec)



MySQL> start slave;

Query OK, 0 rows affected (0.00 sec)



MySQL> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.201

Master_User: replication

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: MySQL-bin.000003

Read_Master_Log_Pos: 374

Relay_Log_File: MySQL-master2-relay-bin.000002

Relay_Log_Pos: 235

Relay_Master_Log_File: MySQL-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 374

Relay_Log_Space: 235

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

1 row in set (0.00 sec)

三、将192.16八.一.20贰设为1玖二.16八.一.20壹的主服务器

在1玖二.168.一.20二上新建授权用户

MySQL> grant replication slave on *.* to 'replication'@'%' identified by 'replication';

Query OK, 0 rows affected (0.00 sec)



MySQL> show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| MySQL-bin.000003 | 374 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

在1九二.168.1.201上,将1玖二.16捌.一.20二设为本身的主服务器

MySQL> change master to master_host='192.168.1.202',master_user='replication',master_password='replication',master_log_file='MySQL-bin.000003',master_log_pos=374;

Query OK, 0 rows affected (0.05 sec)



MySQL> start slave;

Query OK, 0 rows affected (0.00 sec)



MySQL> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.202

Master_User: replication

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: MySQL-bin.000003

Read_Master_Log_Pos: 374

Relay_Log_File: MySQL-master1-relay-bin.000002

Relay_Log_Pos: 235

Relay_Master_Log_File: MySQL-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 374

Relay_Log_Space: 235

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

1 row in set (0.00 sec)

4、MySQL同步测试

如上述均不利配置,今后别的一台MySQL上革新数据都会联手到另一台MySQL,MySQL同步在此不再演示

二、keepalived安装及安顿

壹、19贰.16八.一.20一服务器上keepalived安装及配置

安装keepalived

#tar zxvf keepalived-1.1.20.tar.gz

#cd keepalived-1.1.20

#./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.18-164.el5-i686

#make && make install

[[email protected] ~]#/usr/local/keepalived/sbin/keepalived -D

[[email protected] ~]# ps -aux|grep keepalived

Warning: bad syntax, perhaps a bogus '-'?See /usr/share/doc/procps-3.2.7/FAQ

root 4101 0.3 0.1 35828 632 ? Ss 15:41 0:00/usr/local/keepalived/sbin/keepalived -D

root 4102 0.7 0.4 39988 1620 ? S 15:41 0:00/usr/local/keepalived/sbin/keepalived -D

root 4103 0.5 0.3 39988 1092 ? S 15:41 0:00/usr/local/keepalived/sbin/keepalived -D

root 4106 0.0 0.2 61136 716 pts/0 R+ 15:41 0:00 grep keepalived

配置keepalived

ln -sv /usr/src/kernels/2.6.18-194.el5-x86_64//usr/src/linux

大家自身在新建一个安顿文件,暗中同意情形下keepalived运营时会去/etc/keepalived目录下找配置文件

#mkdir /etc/keepalived

#vi /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

notification_email {

[email protected]

}

notification_email_from [email protected]

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_id MySQL-ha

}



vrrp_instance VI_1 {

state BACKUP #两台配置此处均是BACKUP

interface eth0

virtual_router_id 51

priority 100 #优先级,另一台改为90

advert_int 1

nopreempt #不抢占,只在优先级高的机器上设置即可,优先级低的机器不设置

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

192.168.1.200

}

}



virtual_server 192.168.1.200 3306 {

delay_loop 2 #每个2秒检查一次real_server状态

lb_algo wrr #LVS算法

lb_kind DR #LVS模式

persistence_timeout 60 #会话保持时间

protocol TCP

real_server 192.168.1.201 3306 {

weight 3

notify_down /usr/local/MySQL/bin/MySQL.sh #检测到服务down后执行的脚本

TCP_CHECK {

connect_timeout 10 #连接超时时间

nb_get_retry 3 #重连次数

delay_before_retry 3 #重连间隔时间

connect_port 3306 #健康检查端口

}

}

编写检查测试服务down后所要实行的台本

#vi /usr/local/MySQL/bin/MySQL.sh

#!/bin/sh

pkill keepalived

#chmod +x /usr/local/MySQL/bin/MySQL.sh

注:此脚本是地点配置文件notify_down选项所用到的,keepalived使用notify_down选项来检查real_server的劳务场地,当开掘real_server服务故障时,便触发此脚本;大家得以看出,脚本就三个指令,通过pkillkeepalived强制杀死keepalived进度,从而达成了MySQL故障自动转变。其余,大家不要操心八个MySQL会同有的时候候提供数据更新操作,因为每台MySQL上的keepalived的配置内部唯有本机MySQL的IP+VIP,而不是两台MySQL的IP+VIP

启动keepalived

#/usr/local/keepalived/sbin/keepalived –D

#ps -aux | grep keepalived

测试

找壹台局域网PC,然后去ping MySQL的VIP,那时候MySQL的VIP是足以ping的通的

悬停MySQL服务,看keepalived健检程序是或不是会接触大家编辑的剧本

2、1玖二.16八.一.202上keepalived安装及安顿

安装keepalived

#tar zxvf keepalived-1.1.20.tar.gz

#cd keepalived-1.1.20

#./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.18-164.el5-i686

#make && make install

配置keepalived

那台配置和方面基本一样,但有多个地点不一致:优先级为90、无抢占设置、real_server为本机IP

#mkdir /etc/keepalived

#vi /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

notification_email {

[email protected]

}

notification_email_from [email protected]

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_id MySQL-ha

}



vrrp_instance VI_1 {

state BACKUP

interface eth0

virtual_router_id 51

priority 90

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

192.168.1.200

}

}



virtual_server 192.168.1.200 3306 {

delay_loop 2

lb_algo wrr

lb_kind DR

persistence_timeout 60

protocol TCP

real_server 192.168.1.202 3306 {

weight 3

notify_down /usr/local/MySQL/bin/MySQL.sh

TCP_CHECK {

connect_timeout 10

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

}

编排检查评定服务down后所要实施的脚本

#vi /usr/local/MySQL/bin/MySQL.sh

#!/bin/sh

pkill keepalived

#chmod +x /usr/local/MySQL/bin/MySQL.sh



启动keepalived

#/usr/local/keepalived/sbin/keepalived –D

#ps -aux | grep keepalived

测试

甘休MySQL服务,看keepalived健检程序是或不是会触发大家编辑的脚本

三、测试

MySQL远程登入测试

小编们找一台设置有MySQL客户端的windows,然后登入VIP,看是或不是能登入,在登陆之两台MySQL服务器都要授权允许从远程登入

MySQL> grant all privileges on *.* to 'root'@'%' identified by '123456';

Query OK, 0 rows affected (0.00 sec)



MySQL> flush privileges;

Query OK, 0 rows affected (0.00 sec)

运用客户端登陆VIP测试

C:\MySQL\bin>MySQL.exe -uroot -p123456 -h192.168.1.200 -P3306

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 224

Server version: 5.0.89-log Source distribution



Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.



MySQL>

● keepalived故障转移测试

※在windows客户端直接去ping
VIP,然后停业19②.16八.一.20一上的keepalived,不荒谬情状下VIP就能切换成1九二.16八.一.202上边去

※开启1玖二.16八.一.20壹上的keepalived,关闭192.16八.1.20二上的keepalived,看是或不是能自行切换,常常情状下VIP又会属于1玖二.168.一.201

注:keepalived切换速度依旧要命块的,整个切换进度只需一-3秒

● MySQL故障转移测试

※在1玖2.16捌.壹.20一上关闭MySQL服务,看VIP是或不是会切换成1玖二.16捌.一.202上

※开启19二.16八.一.20一上的MySQL和keepalived,然后关门192.168.壹.20二上的MySQL,看VIP是还是不是会切换成1九二.16八.1.20一上

下边是用windows客户端连接的MySQL的VIP,在切换时笔者实践了一个MySQL查询命令,从推行show
databases到展示出结果时间为3-5秒(我们能够看到下边有个谬误提醒,可是并非忧虑,因为大家的keepalived切换差不离为3秒左右,那3秒左右VIP是何人都不属于的)

MySQL> show databases;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 592

Current database: *** NONE ***



+--------------------+

| Database |

+--------------------+

| information_schema |

| MySQL |

| test |

+--------------------+

3 rows in set (9.01 sec)

后话:红尘万事万物,都不抱有相对的公正无私,就像是上边的MySQL-HA一样,keepalived只可以成功对330陆的健检,不过做不到诸如像MySQL复制中的slave-SQL、slave-IO进程的反省。所以要想做到一些缜密的健检,还得须求借助额外的监察和控制工具,比如nagios,然后用nagios完结短信、邮件报告警察方,从而能够有效地消除难点。

 

mysql> GRANT REPLICATION SLAVE ON *.*

-> TO ‘repl’@’%.mydomain.com’ IDENTIFIED BY ‘slavepass’;

要是你未有备份主服务器,这里是三个开立备份的飞快程序。全体手续都应有在主服务器主机上进行。

 

  1. 发生该语句:

 

mysql> FLUSH TABLES WITH READ LOCK;

  1. 如故加锁时,施行该命令(或它的变体):

 

shell> tar zcf /tmp/backup.tar.gz /var/lib/mysql

  1. 产生该语句并且保障记录了后来用到的输出:

 

mysql>SHOW MASTER STATUS;

  1. 释放锁:

 

mysql> UNLOCK TABLES;

四个可选拔的主意是,转储主服务器的SQL来代替后边步骤中的2进制复制。要那样做,你可以在主服务器上采取mysqldump
–master-data,未来装载SQL转储到到你的从服务器。

 

 

 

 

介绍利用keepalived创设高可用MySQL-HA,保险两台MySQL数据的1致性,然后用keepalived完毕虚拟IP,通过keepalived自带…

MySQL  Master-Master方案

事先的小说介绍了什么样安顿MySQL双主互备,见http://www.linuxidc.com/Linux/2013-05/83784.htm
 
澳门金沙vip,这里介绍怎么着同盟前者达成Keepalived双机热备
 
系统意况:CentOS 6.3
x64
 
MySQL版本:mysql-5.6.10 
 
Keepalived版本:keepalived-1.2.7
 
 
 
MySQL-VIP:192.168.7.253 
 
MySQL-master1:192.168.7.201 
 
MySQL-master2:192.168.7.249 
 
 
 
一.在MySQL-master一:192.16八.7.20一服务器上keepalived安装及配置
 
 
 
编写翻译安装,实际以本机kernel版本为准
 
 
 
# wget
 
# tar zxvf keepalived-1.2.7.tar.gz
 
# cd keepalived-1.2.7
 
# ./configure –prefix=/usr/local/keepalived
–with-kernel-dir=/usr/src/kernels/2.6.32-
 
279.el6.x86_64
 
# make && make install
 
 
 
新建一个布局文件,暗许keepalived运维会去/etc/keepalived目录下搜索配置文件
 
 
 
# mkdir /etc/keepalived
 
 
 
# vi /etc/keepalived/keepalived.conf

一、      意况设置

与前者同样

 

 
global_defs {
 
 
 
    notification_email {
 
 
 
    lzyangel@126.com
 
 
 
    }
 
 
 
    notification_email_from lzyangel@126.com
 
 
 
    smtp_server stmp.126.com 
 
 
 
    smtp_connect_timeout 30
 
 
 
    router_id MySQL-ha
 
 
 
    }
 
 
 
vrrp_instance VI_1{
 
 
 
    # 两台配置此处均是BACKUP
 
 
 
    state BACKUP
 
 
 
    # 注意网卡接口 
 
 
 
    interface eth0
 
 
 
    virtual_router_id 51
 
 
 
    # 优先级,另1台改为90
 
 
 
    priority 100 
 
 
 
    advert_int 1
 
   
 
    # 不主动抢占财富
 
 
 
    nopreempt   
 
 
 
    authentication {
 
 
 
    auth_type PASS
 
 
 
    auth_pass 1111
 
 
 
    }
 
 
 
    virtual_ipaddress {
 
 
 
    192.168.7.253
 
 
 
    }
 
 
 
    }
 
 
 
 
 
 
 
virtual_server 192.168.7.253 3306 {
 
 
 
    # 每一个二秒检查贰回real_server状态
 
 
 
    delay_loop 2 
 
 
 
    # LVS算法
 
 
 
    lb_algo wrr   
 
 
 
    # LVS模式   
 
 
 
    lb_kind DR
 
 
 
    # 会话保持时间   
 
 
 
    persistence_timeout 60 
 
 
 
    protocol TCP
 
 
 
    real_server 192.168.7.201 3306 {
 
 
 
    weight 3
 
 
 
    # 检验到服务down后实行的脚本
 
 
 
    notify_down /etc/rc.d/keepalived.sh 
 
 
 
    TCP_CHECK {
 
 
 
    # 连接超时时间
 
 
 
    connect_timeout 10
 
 
 
    # 重连次数 
 
 
 
    nb_get_retry 3       
 
 
 
    # 重连间隔时间   
 
 
 
    delay_before_retry 3
 
 
 
    # 健检端口 
 
 
 
    connect_port 3306     
 
 
 
    }
 
 
 
    }