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

Lvs+Keepalived+Mysql半同步主主复制高可用方案1.1方案简介Lvs+keepalived作为目前比较流行的高可用解决方案，lvs提供负载均衡，keepalived作为故障转移，提高系统的可用性。但是一般的mysql高可用为了实现mysql数据的一致性，一般都是采用单点写入，本方案采用lvs+keepalived结合mysql5.6的半同步主主复制解决mysql单点问题。本方案实现的功能是当网络有问题、mysql有问题、服务器宕机、keepalived服务停止后,服务器能自动切换到备用机，当主服务器服务启动起来后会自动切换回来。1.2方案架构与说明APP应用连接数据库URL统一为10.103.124.224，在微信这个系统连接数据库的只有一个app，同一时间只有一个数据库在写并同步到另一个数据库，并且设置keepalived策略为persistence_timeout=2小时，也就是应用一旦连接会保持该连接两个小时，除非该数据库宕机会切换到另一台数据库。两个小时后会重新选择服务器，其中两台master权重不相等，权重大的相对的被连接机率也大。1.3方案优缺点优点：安装配置简单，实现方便，高可用效率好，可以根据服务与系统的可用性多方面进行切换。可以将写VIP和读VIP分别进行设置，为读写分离做准备。可以在后面添加多个从服务器，并做到负载均衡。缺点：在当前所用数据库发生宕机而切换数据库时可能会导致当前事务丢失。1.4方案实战1.4.1适用场景这个方案适用于只有两台数据库服务器并且还没有实现数据库的读写分离的情况，读和写都配置VIP。这个方案能够便于单台数据库的管理维护以及切换工作。比如进行大表的表结构更改、数据库的升级等都是非常方便的。1.4.2实战环境介绍服务器IPVIP系统MysqlKeepalivedMaster110.103.124.22110.103.124.224Redhat664bit5.6.101.2.7Master210.103.124.22310.103.124.224Redhat664bit5.6.101.2.71.4.3Mysql的安装和配置在master1、master2服务器都进行安装，先卸载旧版本再安装：rpm-emysql-server-5.1.52-1.el6_0.1.x86_64--nodepsrpm-emysql-5.1.52-1.el6_0.1.x86_64rpm-emysql-libs-5.1.52-1.el6_0.1.x86_64--nodepsrpm-ivhMySQL-server-5.6.10-1.el6.x86_64.rpmrpm-ivhMySQL-client-5.6.10-1.el6.x86_64.rpmrpm-ivhMySQL-shared-5.6.10-1.el6.x86_64.rpmln–s/usr/my.cnf/etc/my.cnf1.4.4Mysql的主主同步配置10.103.124.223:/etc/my.cnf配置：[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=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_pool_size=1Ginnodb_additional_mem_pool_size=20Minnodb_log_buffer_size=64Minnodb_flush_log_at_trx_commit=0innodb_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=3000rpl_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_size=1Ggtid_mode=ONenforce-gtid-consistency=truerelay_log_recovery=1max_relay_log_size=1Grelay_log_purge=1slave-skip-errors=allskip-name-resolvecharacter_set_server=utf8[client]character_set_client=utf8rpl_semi_sync_master_enabled=1rpl_semi_sync_master_timeout=3000rpl_semi_sync_slave_enabled=1character_set_server=utf8[client]character_set_client=utf8先注释上述5个参数然后登入mysqlSETPASSWORD=PASSWORD('123456');INSTALLPLUGINrpl_semi_sync_masterSONAME'semisync_master.so';连接10.103.124.223数据库后执行：installpluginrpl_semi_sync_masterSONAME'semisync_master.so';installpluginrpl_semi_sync_slaveSONAME'semisync_slave.so';grantreplicationslaveon*.*to'repl'@'10.103.124.221'identifiedby'repl';FLUSHPRIVILEGES;changemastertomaster_host='10.103.124.221',master_user='repl',master_password='repl',master_port=3306,master_log_file='binlog.000001',master_log_pos=120;startslave;10.103.124.221:/etc/my.cnf配置：[mysqld]datadir=/var/lib/mysqlport=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_concurrency=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=0innodb_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=3000rpl_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=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-resolvecharacter_set_server=utf8[client]character_set_client=utf8连接10.103.124.221数据库后执行：installpluginrpl_semi_sync_masterSONAME'semisync_master.so';installpluginrpl_semi_sync_slaveSONAME'semisync_slave.so';grantreplicationslaveon*.*to'repl'@'10.103.124.223'identifiedby'repl';FLUSHPRIVILEGES;changemastertomaster_host='10.109.149.7',master_user='repl',master_password='repl',master_port=3306,master_log_file='binlog.000001',master_log_pos=465;startslave;serviceiptablesstatusserviceiptablesstopchkconfigiptablesoff清除linux防火墙，让3306能让其他机器连接1.4.5Lvs的安装和配置在master1、master2服务器都进行安装：rpm-ivhipvsadm-1.25-9.el6.x86_64.