mysql+keepalived 主主高可用集群配置
前言*在mysql高可用配置中，我们会看到有很多的方法，每种方法都有各自优缺点,那今天我们来看参看一下Mysql+Keepalived高可用配置。一、安装环境：
系统版本：CentOS6.0 x86_64
Mysql版本：mysql-5.1.61
Mysqlserver_1: 192.9.117.140
Mysqlserver_2: 192.9.117.141
Keepalived-VIP：192.9.117.142
二、正式安装:
在两台服务器上分别安装mysql，这里直接采用yum安装，如下：
[*]yum install –y mysql mysql-devel mysql-server mysql-libs
安装完后，配置MySQL配置文件，mysql采用主主模式：
1) 192.9.117.140的配置文件如下：vi /etc/my.cnf
[*]
[*]
[*]datadir=/data/mysql
[*]
[*]socket=/var/lib/mysql/mysql.sock
[*]
[*]user=mysql
[*]
[*]# Disabling symbolic-links is recommended to prevent assorted security risks
[*]
[*]symbolic-links=0
[*]
[*]log-bin=mysql-bin
[*]
[*]server-id = 1
[*]auto_increment_offset=1
[*]
[*]auto_increment_increment=2
[*]
[*]
[*]
[*]log-error=/var/log/mysqld.log
[*]
[*]pid-file=/var/run/mysqld/mysqld.pid
[*]
[*]master-host =192.9.117.141
[*]
[*]master-user=tongbu
[*]
[*]master-pass=123456
[*]
[*]master-port =3306
[*]
[*]master-connect-retry=60
[*]
[*]replicate-do-db =map
2) 192.9.117.141的配置文件如下：vi /etc/my.cnf
[*]
[*]
[*]datadir=/data/mysql
[*]
[*]socket=/var/lib/mysql/mysql.sock
[*]
[*]user=mysql
[*]
[*]# Disabling symbolic-links is recommended to prevent assorted security risks
[*]
[*]symbolic-links=0
[*]
[*]log-bin=mysql-bin
[*]
[*]server-id = 2
[*]auto_increment_offset=2
[*]
[*]auto_increment_increment=2
[*]
[*]
[*]
[*]log-error=/var/log/mysqld.log
[*]
[*]pid-file=/var/run/mysqld/mysqld.pid
[*]
[*]master-host =192.9.117.140
[*]
[*]master-user=tongbu
[*]
[*]master-pass=123456
[*]
[*]master-port =3306
[*]
[*]master-connect-retry=60
[*]
[*]replicate-do-db =map
[*]
[*]如上设置bin-log文件，并都设置对方为自己的主服务器，配置同步的数据库为map
三、配置MySQL：
1) 在两台mysql数据库服务器里面设置权限，分别执行如下命令：
grant replication slave on *.* to 'tongbu'@'%'>
然后在141执行：
[*]show master status;
[*]
[*]+------------------+----------+--------------+------------------+
[*]
[*]| File
[*]|Position | Binlog_Do_DB | Binlog_Ignore_DB |
[*]
[*]+------------------+----------+--------------+------------------+
[*]
[*]| mysql-bin.000002 |
[*]
[*]106 |
[*]|
[*]|
[*]
[*]+------------------+----------+--------------+------------------+
[*]
[*]1 row in set (0.00 sec)
2) 在192.9.117.140上将192.9.117.141设为自己的主服务器执行如下命令：
[*]change master to master_host='192.9.117.141',master_user='tongbu',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=106;
[*]
[*]然后启动start slave;
[*]注意这里写的bin-log参数是在141查看到的，即是对方的参数。
3) 然后在140执行：
[*]show master status;
[*]
[*]+------------------+----------+--------------+------------------+
[*]
[*]| File
[*]|Position | Binlog_Do_DB | Binlog_Ignore_DB |
[*]
[*]+------------------+----------+--------------+------------------+
[*]
[*]| mysql-bin.000003 |
[*]
[*]445|
[*]|
[*]|
[*]
[*]+------------------+----------+--------------+------------------+
[*]
[*]1 row in set (0.00 sec)
[*]
4） 在192.9.117.141上将192.9.117.140设为自己的主服务器执行如下命令：
[*]change master to master_host='192.9.117.140',master_user='tongbu',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=445;
[*]然后启动start slave;
5) MySQL同步测试配置完毕，我们会发现在任何一台mysql上更新同步的数据库里面的数据，都会同步到另一台mysql。
四、安装Keepalived:
[*]tar zxf keepalived-1.2.1.tar.gz
[*]
[*]cd keepalived-1.2.1 &&./configure --with-kernel-dir=/usr/src/kernels/2.6.18-164.el5-i686 &&make && make install
[*]
[*]DIR=/usr/local/ ;cp $DIR/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/ && cp $DIR/etc/sysconfig/keepalived /etc/sysconfig/ && mkdir -p /etc/keepalived && cp $DIR/sbin/keepalived /usr/sbin/
[*]
[*]if
[*]
[*][ $? -eq 0 ];then
[*]
[*]echo "Keepalived system server config success!"
[*]
[*]else
[*]echo "Keepalived system server config failed ,please check keepalived!"
[*]exit 0
[*]
[*]fi
五、配置keepalived：
1) 创建vi keepalived.conf文件，内容如下：
[*]! Configuration File for keepalived
[*]
[*]global_defs {
[*]
[*]notification_email {
[*]
[*]wgkgood@139.com
[*]
[*]}
[*]notification_email_from wgkgood@139.com
[*]
[*]smtp_server 127.0.0.1
[*]
[*]smtp_connect_timeout 30
[*]
[*]router_id LVS_DEVEL
[*]}
[*]# VIP1
[*]
[*]vrrp_instance VI_1 {
[*]
[*]state BACKUP
[*]
[*]interface eth0
[*]
[*]lvs_sync_daemon_inteface eth0
[*]
[*]virtual_router_id 151
[*]
[*]priority 90
[*]
[*]advert_int 5
[*]
[*]nopreempt
[*]
[*]authentication {
[*]
[*]auth_type PASS
[*]
[*]auth_pass 2222
[*]
[*]}
[*]virtual_ipaddress {
[*]
[*]192.9.117.142
[*]}
[*]}
[*]
[*]virtual_server 192.9.117.142 3306 {
[*]
[*]delay_loop 6
[*]
[*]lb_algo wrr
[*]
[*]lb_kind DR
[*]
[*]persistence_timeout 60
[*]
[*]protocol TCP
[*]
[*]real_server 192.9.117.140 3306 {
[*]
[*]weight 100
[*]
[*]notify_down /data/sh/mysql.sh
[*]
[*]TCP_CHECK {
[*]
[*]connect_timeout 10
[*]
[*]nb_get_retry 3
[*]
[*]delay_before_retry 3
[*]
[*]connect_port 3306
[*]
[*]}
[*]}
[*]}
2) 141 keepalived同样如上配置，注意keepalived配置文件里面只添加一台mysql服务器ip地址，代表只使用这一台读写。
修改为Realserver192.9.117.141，并且设置优先级为90，都是BACKUP模式，并且nopreempt不抢占即可。
配置完后启动keepalived测试，可以先停止一台mysql，然后查看本地的keepalived是否停止，并且另外一台keepalived 已经变成了MASTER，如果是那就测试成功。
如上需要设置检查脚本/data/sh/mysql.sh,脚本内容为:
pkill keepalived
下图为keepalived.conf部分配置
MysqlCluster
mysql+keepalived 主主高可用集群配置
Updated Jul 30, 2012 by chij...@gmail.com
Introduction
介绍如何配置mysql的双机热备,任何一台挂掉,都不会影响另外一台,当然,如果一台挂掉以后,没有重新起来,而另外一台也挂掉,将导致整个挂掉.
Details
详细介绍了,如何配置两台高可用mysql集群,测试环境如下:
[*]两台vm,系统都是centos6 64
[*]两台vm的 eth0 ip 地址分别是: (master1) 172.16.200.31 (master2)172.16.200.32
[*]两台vm均需要安装 keepalived ,虚拟的ip都是 172.16.200.30
[*]客户连接的ip地址为 172.16.200.30
[*]两台 mysql server 均充当 master 和 salve
详细步骤
[*]安装基本的软件,以下操作,两台机完全一模一样,都需要做,yum 安装 keepalived 需要 epel 的源
yum install keepalived mysql-server -y
[*]开始配置mysql,以下需要在两台mysql server里面配置
在/etc/my.cnf 文件中, 字段里面,添加如下
log-bin=mysql-bin server
-id=1 slave
-skip-errors=all
slave-skip-errors 表示跳过错误,否则如果错误,则不能继续同步
[*]开始配置mysql master1 172.16.200.31
# 创建用来同步的账号MySQL> grant replication slave on *.* to 'replication'@'%'># 查看状态 mysql
> show master status;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000006 | 106 | | |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)
[*]在master1 172.16.200.31 上,将 172.16.200.32 设为自己的主服务器
mysql> change master to master_host='172.16.200.32',master_user='replication',master_password='replication';Query OK, 0 rows affected (0.05 sec)# 启动 slave mysql
> start slave;Query OK, 0 rows affected (0.00 sec)# 查看 slave 状态 show slave status
\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.1.201Master_User: replicationMaster_Port: 3306Connect_Retry: 60Master_Log_File: MySQL-bin.000003Read_Master_Log_Pos: 374Relay_Log_File: MySQL-master2-relay-bin.000002Relay_Log_Pos: 235Relay_Master_Log_File: MySQL-bin.000003Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 374Relay_Log_Space: 235Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 01 row in set (0.00 sec)
[*]将172.16.200.32 设为 172.16.200.31 的主服务器, 以下操作,针对 172.16.200.32
# 创建用户MySQL> grant replication slave on *.* to '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)
[*]在master 2, 172.16.200.32 上，将 172.16.200.31 设为自己的主服务器
mysql >change master to master_host='172.16.200.31',master_user='replication',master_password='replication';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 eventMaster_Host: 192.168.1.202Master_User: replicationMaster_Port: 3306Connect_Retry: 60Master_Log_File: MySQL-bin.000003Read_Master_Log_Pos: 374Relay_Log_File: MySQL-master1-relay-bin.000002Relay_Log_Pos: 235Relay_Master_Log_File: MySQL-bin.000003Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 374Relay_Log_Space: 235Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 01 row in set (0.00 sec)
[*]MySQL同步测试
如上述均正确配置，现在任何一台MySQL上更新数据都会同步到另一台MySQL， 测试的方法就是登陆任何一台mysql,然后创建数据库,接着登陆另外一台数据库,查看是否已经同步
配置 keepalived
配置 keepalived ,也是采用 rpm yum 安装的方式,不采用自己编译的方法.安装完成以后,默认的
页:
[1]