Lvs+Keepalived+Mysql半同步主主复制高可用方案.docx

1、Lvs+Keepalived+Mysql半同步主主复制高可用方案Lvs+Keepalived+Mysql半同步主主复制高可用方案1.1 方案简介Lvs+keepalived作为目前比较流行的高可用解决方案，lvs提供负载均衡，keepalived作为故障转移，提高系统的可用性。但是一般的mysql高可用为了实现mysql数据的一致性，一般都是采用单点写入，本方案采用lvs+keepalived结合mysql5.6的半同步主主复制解决mysql单点问题。本方案实现的功能是当网络有问题、mysql有问题、服务器宕机、keepalived服务停止后,服务器能自动切换到备用机，当主服务器服务启动起来后

2、会自动切换回来。1.2 方案架构与说明 APP应用连接数据库URL统一为10.103.124.224，在微信这个系统连接数据库的只有一个app，同一时间只有一个数据库在写并同步到另一个数据库，并且设置keepalived策略为persistence_timeout=2小时，也就是应用一旦连接会保持该连接两个小时，除非该数据库宕机会切换到另一台数据库。两个小时后会重新选择服务器，其中两台master权重不相等，权重大的相对的被连接机率也大。1.3 方案优缺点优点： 安装配置简单，实现方便，高可用效率好，可以根据服务与系统的可用性多方面进行切换。 可以将写VIP和读VIP分别进行设置，为读写分离做

3、准备。 可以在后面添加多个从服务器，并做到负载均衡。缺点： 在当前所用数据库发生宕机而切换数据库时可能会导致当前事务丢失。1.4 方案实战1.4.1 适用场景这个方案适用于只有两台数据库服务器并且还没有实现数据库的读写分离的情况，读和写都配置VIP。这个方案能够便于单台数据库的管理维护以及切换工作。比如进行大表的表结构更改、数据库的升级等都是非常方便的。1.4.2 实战环境介绍服务器IPVIP系统Mysql KeepalivedMaster110.103.124.22110.103.124.224Redhat6 64bit5.6.101.2.7Master210.103.124.22310.1

4、03.124.224Redhat6 64bit5.6.101.2.71.4.3 Mysql的安装和配置在master1、master2服务器都进行安装，先卸载旧版本再安装：rpm -e mysql-server-5.1.52-1.el6_0.1.x86_64 -nodepsrpm -e mysql-5.1.52-1.el6_0.1.x86_64rpm -e mysql-libs-5.1.52-1.el6_0.1.x86_64 -nodepsrpm -ivh MySQL-server-5.6.10-1.el6.x86_64.rpmrpm -ivh MySQL-client-5.6.10-1.el

5、6.x86_64.rpmrpm -ivh MySQL-shared-5.6.10-1.el6.x86_64.rpmln s /usr/f /etc/f 1.4.4 Mysql的主主同步配置10.103.124.223:/etc/f配置：mysqld datadir=/var/lib/mysqlport=3306user=rootserver_id=1join_buffer_size = 1Gskip-external-lockingkey_buffer_size = 2Gmax_allowed_packet = 10Mtable_open_cache = 512sort_buffer_size

6、 = 20Mread_buffer_size = 20Mread_rnd_buffer_size = 32Mmyisam_sort_buffer_size = 256Mthread_cache_size = 512query_cache_size = 1024Mthread_concurrency = 128slow_query_log = ONslow_launch_time = 2innodb_flush_log_at_timeout = 2max_connections = 1024tmp_table_size=1Gmax_heap_table_size=1Ginnodb_buffer_

7、pool_size=1Ginnodb_additional_mem_pool_size=20Minnodb_log_buffer_size=64Minnodb_flush_log_at_trx_commit=0 innodb_lock_wait_timeout=30innodb_thread_concurrency=8default-storage-engine=myisamsql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLESrpl_semi_sync_master_enabled=1rpl_semi_sync_master_timeout=3

8、000rpl_semi_sync_slave_enabled=1log_bin=/var/lib/mysql/binlogbinlog-do-db=masterslavebinlog-ignore-db=mysqlreplicate-do-db=masterslavebinlog_format=ROWslave-parallel-workers=2auto_increment_increment=2auto_increment_offset=1#skip_slave_startlog_slave_updates#read_onlyexpire_logs_days=1max_binlog_siz

9、e=1Ggtid_mode=ONenforce-gtid-consistency=truerelay_log_recovery=1max_relay_log_size=1Grelay_log_purge=1slave-skip-errors=allskip-name-resolvecharacter_set_server=utf8clientcharacter_set_client=utf8rpl_semi_sync_master_enabled=1rpl_semi_sync_master_timeout=3000rpl_semi_sync_slave_enabled=1character_s

10、et_server=utf8clientcharacter_set_client=utf8先注释上述5个参数然后登入mysqlSET PASSWORD = PASSWORD(123456);INSTALL PLUGIN rpl_semi_sync_master SONAME semisync_master.so;连接10.103.124.223数据库后执行：install plugin rpl_semi_sync_master SONAME semisync_master.so;install plugin rpl_semi_sync_slave SONAME semisync_slave.s

11、o;grant replication slave on *.* to repl10.103.124.221 identified by repl;FLUSH PRIVILEGES;change master to master_host=10.103.124.221,master_user=repl,master_password=repl,master_port=3306,master_log_file=binlog.000001,master_log_pos=120;start slave;10.103.124.221:/etc/f配置：mysqld datadir=/var/lib/m

12、ysqlport=3306user=rootserver_id=2join_buffer_size = 1Gskip-external-lockingkey_buffer_size = 2Gmax_allowed_packet = 10Mtable_open_cache = 512sort_buffer_size = 20Mread_buffer_size = 20Mread_rnd_buffer_size = 32Mmyisam_sort_buffer_size = 256Mthread_cache_size = 512query_cache_size = 1024Mthread_concu

13、rrency = 128slow_query_log = ONslow_launch_time = 2innodb_flush_log_at_timeout = 2max_connections = 1024tmp_table_size=1Gmax_heap_table_size=1Ginnodb_buffer_pool_size=1Ginnodb_additional_mem_pool_size=20Minnodb_log_buffer_size=64Minnodb_flush_log_at_trx_commit=0 innodb_lock_wait_timeout=30innodb_thr

14、ead_concurrency=8default-storage-engine=myisamsql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLESrpl_semi_sync_master_enabled=1rpl_semi_sync_master_timeout=3000rpl_semi_sync_slave_enabled=1log_bin=/var/lib/mysql/binlogbinlog-do-db=masterslavebinlog-ignore-db=mysqlreplicate-do-db=masterslavebinlog_fo

15、rmat=ROWslave-parallel-workers=2auto_increment_increment=2auto_increment_offset=2#skip_slave_startlog_slave_updates#read_onlyexpire_logs_days=1max_binlog_size=1Ggtid_mode=ONenforce-gtid-consistency=truerelay_log_recovery=1max_relay_log_size=1Grelay_log_purge=1slave-skip-errors=allskip-name-resolvech

16、aracter_set_server=utf8clientcharacter_set_client=utf8连接10.103.124.221数据库后执行：install plugin rpl_semi_sync_master SONAME semisync_master.so;install plugin rpl_semi_sync_slave SONAME semisync_slave.so;grant replication slave on *.* to repl10.103.124.223 identified by repl;FLUSH PRIVILEGES;change maste

17、r to master_host=10.109.149.7,master_user=repl,master_password=repl,master_port=3306,master_log_file=binlog.000001,master_log_pos=465;start slave;service iptables statusservice iptables stopchkconfig iptables off清除linux防火墙，让3306能让其他机器连接1.4.5 Lvs的安装和配置在master1、master2服务器都进行安装：rpm -ivhipvsadm-1.25-9.e

18、l6.x86_64.rpmrpm -ivh popt-devel-1.13-7.el6.x86_64.rpmchkconfig ipvsadm onvi /usr/local/bin/lvs_real#!/bin/bash#description : start realserverVIP=10.103.124.224/etc/rc.d/init.d/functionscase $1 instart)echo start LVS of REALServer/sbin/ifconfig lo:0 $VIP broadcast $VIP netmask 255.255.255.255 upecho

19、 1 /proc/sys/net/ipv4/conf/lo/arp_ignoreecho 2 /proc/sys/net/ipv4/conf/lo/arp_announceecho 1 /proc/sys/net/ipv4/conf/all/arp_ignoreecho 2 /proc/sys/net/ipv4/conf/all/arp_announce;stop)/sbin/ifconfig lo:0 downecho close LVS Directorserverecho 0 /proc/sys/net/ipv4/conf/lo/arp_ignoreecho 0 /proc/sys/ne

20、t/ipv4/conf/lo/arp_announceecho 0 /proc/sys/net/ipv4/conf/all/arp_ignoreecho 0 /proc/sys/net/ipv4/conf/all/arp_announce;*)echo Usage: $0 start|stopexit 1esac保存退出，执行以下命令：chmod +x /etc/rc.d/init.d/functionschmod +x lvs_reallvs_real startecho which lvs_real /etc/rc.local1.4.6 Keepalived的安装在master1、mast

21、er2服务器都进行安装：./configure -prefix=/usr/local/keepalived-1.2.7make & make installln -s /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/keepalivedln -s /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/keepalivedmkdir /etc/keepalivedln -s /usr/local/keepalived/sbin/keepaliv

22、ed /usr/sbin/keepalived1.4.7 Keepalived的配置master1的/etc/keepalived/keepalived.conf配置：global_defs router_id LVS_MASTERvrrp_instance VI_1 state MASTER interface eth4 #改成自己网卡的名字 virtual_router_id 51 priority 150 advert_int 1 authentication auth_type PASS auth_pass 1111 virtual_ipaddress 10.103.124.224 v

23、irtual_server 10.103.124.224 3306 delay_loop 6 lb_algo rr lb_kind DR nat_mask 255.255.255.0 persistence_timeout 7200 protocol TCP real_server 10.103.124.223 3306 weight 1 TCP_CHECK connect_timeout 3 nb_get_retry 3 delay_before_retry 3 connect_port 3306 real_server 10.103.124.221 3306 weight 1 TCP_CH

24、ECK connect_timeout 3 nb_get_retry 3 delay_before_retry 3 connect_port 3306 Master2的/etc/keepalived/keepalived.conf配置：global_defs router_id LVS_MASTERvrrp_instance VI_1 state BACKUP interface eth4 #改成自己网卡的名字 virtual_router_id 51 priority 100 advert_int 1 authentication auth_type PASS auth_pass 1111

25、virtual_ipaddress 10.103.124.224 virtual_server 10.103.124.224 3306 delay_loop 6 lb_algo rr lb_kind DR nat_mask 255.255.255.0 persistence_timeout 7200 protocol TCP real_server 10.103.124.223 3306 weight 1notify_down /root/when_db_down.sh #检测到服务down后执行的脚本 TCP_CHECK connect_timeout 3 nb_get_retry 3 de

26、lay_before_retry 3 connect_port 3306 real_server 10.103.124.221 3306 weight 1notify_down /root/when_db_down.sh #检测到服务down后执行的脚本 TCP_CHECK connect_timeout 3 nb_get_retry 3 delay_before_retry 3 connect_port 3306 1.4.8高可用方案测试方案搭建好以后就要进行全方位的可靠性测试了，看看是否达到了我们的预期效果，大致测试步骤如下： 停掉master1上的mysql，看看能否自动切换到maste

27、r2。 插入master1的数据看能否同步到master2数据库。 启动master上的mysql，看是否能切换回master。 启动master上的keepalived，看VIP是否会迁移回master上。 重启master的系统，看看切换过程是否OK1.5 监控与备份1.5.1mysql 宕机：Keepalived在检测到数据库宕机后可以执行指定脚本，上面配置的是/root/when_db_down.sh，其中脚本内容如下：Ssh root10.103.124.221 service mysql restart;1.5.2每5分钟清理mysql sleep或lock进程 ：echo dat

28、e killing mysql sleep process. /app/crontab.logfor id in mysql -u root -ppassword, -e show processlist|grep -i -E sleep|locked | awk if($6100)print $1doecho killing pid $id /app/crontab.logmysql -u root -ppassword, -e kill $iddonecrontab 添加每5分钟执行：*/5 0 0 0 0 /root/kill_sleep_process.sh1.5.3 磁盘每小时检测用

29、量，如超80%则发邮件通知： a=df -l |awk -F print $5|xargs echo |awk -F print $2;a=$a%;if $a -gt 80 ; then sendEmail -f mail -t chembo -s -u EmergencyServer Disk Is Full -xu mail -xp Password -m Disk space is full! chembo do sth.ficrontab 添加每1小时执行：0 */1 0 0 0 /root/check_disk_use.sh1.5.4 数据库大表每天凌晨4点进行归档，备份并删除三个月前的数据： 微信系统中需要进行归档有数据库有： 微信用户消息分词日志表：WX_USER_KEY_WORD； 微信用户APP待推送消息表；WX_APP_SEND_POOL； 微信用户后端待推送消息表：WX_SEND_POOL； 微信用户推送消息日志表：WX_SEND_LOG； 微信用户消息日志表：WX_USER_MESSAGE_LOG； 微信附件表：WX_ATTACH_INFO； 归档脚本：threeMonthBeforeDate=date -