Mysql 主主复制+keepalived 做 HA

最近做哈尔滨的项目，用到 MySQL 主主复制。

网络配置：

  Mysql1  10.0.6.19

  Mysql2  10.0.6.20

大部分配置都和主从一样，不再复述。

1、Mysql 安装

  目前使用版本  MySQL-server-community-5.1.56-1.rhel5.x86_64

2、修改配置文件

    Mysql1  配置文件 /etc/my.cnf

   

[client]
port		= 3306
socket		= /e01/mysql/mysql.sock

[mysqld]
port		= 3306
socket		= /e01/mysql/mysql.sock
back_log = 128
max_connections = 2048
max_connect_errors = 10
table_open_cache = 2048
max_allowed_packet = 2048M
binlog_cache_size = 1M
expire-logs-days=30
max_heap_table_size = 64M
read_buffer_size = 32M
read_rnd_buffer_size = 32M
sort_buffer_size = 32M
join_buffer_size = 32M
thread_cache_size = 64
thread_concurrency = 32
query_cache_size = 512M
query_cache_type = 1
query_cache_limit = 2M
ft_min_word_len = 2
default-storage-engine = INNODB
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 512M
log-bin=mysql-bin
binlog_format=mixed
sync_binlog=1
log-slave-updates
slow_query_log
long_query_time = 10
key_buffer_size = 256M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
innodb_additional_mem_pool_size = 32M
innodb_buffer_pool_size = 1G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
datadir=/e01/mysql
user=mysql
character-set-server=gbk
lower_case_table_names=1
skip-name-resolve
old_passwords=1
skip-slave-start
max_user_connections=1024
auto_increment_offset = 1  
#自增序列从1开始，默认auto 字段为奇数
auto_increment_increment = 2
innodb_file_per_table=1
log-error=/var/log/mysqld.log
tmpdir=/tmp/mysqldata
server-id=1

replicate-wild-do-table=mq4test.%
replicate-wild-ignore-table=mysql.%
relay-log=mysql-relay-bin

#skip-grant-tables


[mysqldump]
quick
max_allowed_packet = 2048M
[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

  Mysql2 配置文件与  Mysql1的大部分一致，唯一的区别如下：

auto_increment_offset = 2  #自增序列从2开始，默认auto 字段为偶数
auto_increment_increment = 2

2、主从同步授权

 

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

3、 在Mysql1 上启动从

change master to master_host='10.0.6.20', master_user='repluser', master_password='repluser', master_log_file='mysql-bin.000006', master_log_pos=339 ;
start slave;

4、 在Mysql2 上启动从

change master to master_host='10.0.6.19', master_user='repluser', master_password='repluser', master_log_file='mysql-bin.000007', master_log_pos=339 ;
start slave;

6、做好MySQL 的主主后，开始安装 keepalvied 

  

#安装IPVSADM
yum install kernel-devel
ln -s /usr/src/kernels/2.6.18-308.el5-x86_64/ /usr/src/linux
make
make install
#验证
ipvsadm
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
-> RemoteAddress:Port           Forward Weight ActiveConn InActConn

lsmod | grep ip_vs
ip_vs                 122113  0

# keepalived 安装
#keepalived 1.22 编译错误的解决方法
./configure -prefix=/usr/local/keepalive
make
make install
mkdir /etc/keepalived/
ln -s /usr/local/keepalive/etc/keepalived/keepalived.conf  /etc/keepalived/
ln -s /usr/local/keepalive/etc/rc.d/init.d/keepalived  /etc/rc.d/init.d/
ln -s /usr/local/keepalive/etc/sysconfig/keepalived  /etc/sysconfig/
ln -s /usr/local/keepalive/sbin/keepalived  /usr/sbin/

7、配置 keepalived 

vim /etc/keepalived/keepalived.conf

#Configuration File for keepalived
global_defs {
router_id MySQL-ha
}

vrrp_instance VI_1 {
state BACKUP   #Mysql1,Mysql2上面的配置都是 BACKUP
interface eth0
lvs_sync_daemon_inteface eth0
virtual_router_id 78   # 
Mysql1,Mysql2 必须配置一致
priority 100   #优先级 
Mysql1 设置100 ,Mysql2 设置 90
advert_int 1
nopreempt   # 不抢占VIP，优先级高的配置该参数
authentication {
auth_type PASS
auth_pass 1111
}

virtual_ipaddress {
10.0.6.21 dev eth0 label eth0:1   #虚拟IP VIP
}
}

virtual_server 10.0.6.21 3306 {
delay_loop 2
lb_algo wrr
#                lb_kind DR
lb_kind NAT     #lvs 模式，使用 NAT
persistence_timeout 60
protocol TCP
real_server 10.0.6.19 3306 {
weight 1
notify_down /etc/keepalived/scripts/when_db_down.sh   #检测到服务down 后执行脚本
notify_up /etc/keepalived/scripts/when_db_up.sh

TCP_CHECK {   #检测服务的方式
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
#                        MISC_CHECK {     
 #检测服务的方式
#                                misc_path "/etc/keepalived/scripts/pingM.sh"
#                                misc_timeout 5
#                        }
}
}

8、脚本

    when_db_down.sh 

#!/bin/sh

if [ -f /var/run/vrrp.pid ]; then

pkill keepalived
#only run health check when mysql goes down

keepalived -C -D -d -S 0 -f /etc/keepalived/keepalived.conf

fi

when_db_up.sh 

#!/bin/sh

pkill keepalived

#/etc/keepalived/scripts/startMySQL.sh

keepalived -D -d -S 0 -f /etc/keepalived/keepalived.conf --vrrp_pid /var/run/vrrp.pid

9，启动keepalived 测试。略。