[bigdata-66] ubuntu 14.04 + keepalived+mysql5.5 mysql双机主

1. 两个节点 192.168.17.112 smysql1 192.168.17.109 smysql2 2. 两个节点均安装mysql apt-get install mysql-client-5.5 mysql-server-5.5 root密码是tt88 3. 配置主主同步 3.1 在两个节点mysql上都执行 create user 'mmsync'@'%' identified by 'weirj33'; grant replication slave,replication client on *.* to 'mmsync'@'%' identified by 'weirj33'; 3.2 对smysql1进行配置，修改/etc/my.cnf，增加如下命令 [mysqld] log-bin=mysql-bin server-id=11 expire-logs-days=100 binlog-ignore-db=mysql binlog-ignore-db=infomation_schema binlog-ignore-db=performance_schema #5.5以上需要这一行 auto-increment-increment=2 auto-increment-offset=1 character-set-server=utf8 ? collation-server=utf8_general_ci [client] default-character-set=utf8 3.3 对smysql2创建复制帐号 create user 'mmsync'@'%' identified by 'weirj33'; grant replication slave,replication client on *.* to 'mmsync'@'%' identified by 'weirj33'; 3.4 对smysql2的/etc/my.cnf进行配置 [mysqld] log-bin=mysql-bin server-id=12 expire-logs-days=100 binlog-ignore-db=mysql binlog-ignore-db=infomation_schema auto-increment-increment=2 auto-increment-offset=2 character-set-server=utf8 ? collation-server=utf8_general_ci [client] default-character-set=utf8 ? 3.5 互告bin-log信息 重启mysql服务 3.5.1 在smysql1上执行show master status，查看bin-log文件 ? File: mysql-bin.000001 ??????? Position: 107 ??? Binlog_Do_DB: Binlog_Ignore_DB: mysql,infomation_schema 1 row in set (0.00 sec) 3.5.2 在smysql2上执行show master status查看bin-log文件 File: mysql-bin.000002 ??????? Position: 107 ??? Binlog_Do_DB: Binlog_Ignore_DB: mysql,infomation_schema 3.5.3 在smysql1上执行 change master to master_host='192.168.17.109',master_user='mmsync',master_password='weirj33',master_log_file='mysql-bin.000002',master_log_pos=107; 3.5.4 在smysql2上执行 change master to master_host='192.168.17.112',master_log_file='mysql-bin.000001',master_log_pos=107; 3.5.5 在两个mysql上均执行 start slave; 3.5.6 在两个mysql上执行 show slave status G 如果都出现 Slave_IO_Running: Yes Slave_SQL_Running: Yes 则表明正常。 4. 测试各种方式，看看双机同步如何 4.1 测试1 在smysql1创建库tt1: create database tt1; 在smysql2执行show databases，能看到tt1，符合预期。 在smysql2删除tt1:drop database tt1 在smysql1执行show databases，发现tt1不见了，符合预期。 4.2 测试2 关闭smysql1 在smysql2上执行创建database tt2 重启smysql1，执行show database，发现出现了tt2，符合预期。 关闭smysql2。 在smysql1上执行创建tt3。 然后开启smysql2，可以看到tt3存在，符合预期。 4.3 测试通过，一旦设定，就可以始终自动同步。 5.在两个节点安装lvs管理工具 apt-get install ipvsadm 执行一下 lpvsadm -ln 6. 两个节点安装和配置keepalived 6.1 安装命令 apt-get install keepalived 6.2 在smysql1下配置/etc/keepalived/keepalived.conf，内容如下 ---------------- global_defs { ? ? router_id NodeA ? } vrrp_sync_group test { ? group { ??? VI_1 ? } } vrrp_instance VI_1 { ? ? state MASTER ? interface eth0 ? virtual_router_id 51 ? priority 150 ? advert_int 1 ? authentication { ? ??? auth_type PASS ??? auth_pass 1111 ? } ? ? virtual_ipaddress { ? ??? 192.168.17.113 dev eth0 label eth0:1 ? } } virtual_server 192.168.17.113 3306 { ? delay_loop 6 ? lb_algo rr ? lb_kind DR ? persistence_timeout 20 ? protocol TCP ? sorry_server 192.168.17.109 3306 ? real_server 192.168.17.112 3306 { ??? weight 3 ??? TCP_CHECK { ????? connect_timeout 3 ????? nb_get_retry 3 ????? delay_before_retry 3 ????? connect_port 3306 ??? } ? } } ---------------- 6.3 在smysql2下配置/etc/keepalived/keepalived.conf，内容如下 ---------------- global_defs { ? router_id NodeA } vrrp_sync_group test { ? group { ??? VI_1 ? } } 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.17.113 dev eth0 label eth0:1 ? } } virtual_server 192.168.17.113 3306 { ? delay_loop 6 ? lb_algo rr ? lb_kind DR ? persistence_timeout 20 ? protocol TCP ? sorry_server 192.168.17.109 3306 ? real_server 192.168.17.112 3306 { ??? weight 3 ??? TCP_CHECK { ????? connect_timeout 3 ????? nb_get_retry 3 ????? delay_before_retry 3 ????? connect_port 3306 ??? } ? } } ---------------- 6.4 配置smysql1和smysql2的realserver vim /etc/init.d/realserver.sh 内容如下： ---------------------- #!/bin/bash # description: Config realserver lo and apply noarp SNS_VIP=10.1.55.15 /etc/rc.d/init.d/functions case "$1" in start) ? ifconfig lo:0 $SNS_VIP netmask 255.255.255.255 broadcast $SNS_VIP ? /sbin/route add -host $SNS_VIP dev lo:0 ? echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore ? echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce ? echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore ? echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce ? sysctl -p >/dev/null 2>&1 ? echo "RealServer Start OK" ? ;; stop) ? ifconfig lo:0 down ? route del $SNS_VIP >/dev/null 2>&1 ? echo "0" >/proc/sys/net/ipv4/conf/lo/arp_ignore ? echo "0" >/proc/sys/net/ipv4/conf/lo/arp_announce ? echo "0" >/proc/sys/net/ipv4/conf/all/arp_ignore ? echo "0" >/proc/sys/net/ipv4/conf/all/arp_announce ? echo "RealServer Stoped" ? ;; *) ? echo "Usage: $0 {start|stop}" ? exit 1 esac exit 0 ---------------------- 然后，在两个节点的/etc/rc.local都添加如下一句： /etc/init.d/realserver.sh start 6.5 重启两个节点 6.5.1 重启后，smysql1上执行ifconfig是这样的： ------------------ eth0????? Link encap:Ethernet? HWaddr 00:0c:29:0d:21:12 ? ????????? inet addr:192.168.17.112? Bcast:192.168.17.255? Mask:255.255.255.0 ????????? inet6 addr: fe80::20c:29ff:fe0d:2112/64 Scope:Link ????????? UP BROADCAST RUNNING MULTICAST? MTU:1500? Metric:1 ????????? RX packets:99 errors:0 dropped:0 overruns:0 frame:0 ????????? TX packets:108 errors:0 dropped:0 overruns:0 carrier:0 ????????? collisions:0 txqueuelen:1000 ????????? RX bytes:12450 (12.4 KB)? TX bytes:11823 (11.8 KB) eth0:1??? Link encap:Ethernet? HWaddr 00:0c:29:0d:21:12 ? ????????? inet addr:192.168.17.113? Bcast:0.0.0.0? Mask:255.255.255.255 ????????? UP BROADCAST RUNNING MULTICAST? MTU:1500? Metric:1 lo??????? Link encap:Local Loopback ? ????????? inet addr:127.0.0.1? Mask:255.0.0.0 ????????? inet6 addr: ::1/128 Scope:Host ????????? UP LOOPBACK RUNNING? MTU:65536? Metric:1 ????????? RX packets:10 errors:0 dropped:0 overruns:0 frame:0 ????????? TX packets:10 errors:0 dropped:0 overruns:0 carrier:0 ????????? collisions:0 txqueuelen:0 ????????? RX bytes:548 (548.0 B)? TX bytes:548 (548.0 B) lo:0????? Link encap:Local Loopback ? ????????? inet addr:192.168.17.113? Mask:255.255.255.255 ????????? UP LOOPBACK RUNNING? MTU:65536? Metric:1 ------------------ 6.5.2 在smysql2上执行ifconfig，结果是这样 ------------------ eth0????? Link encap:Ethernet? HWaddr 00:0c:29:8c:ef:e6 ? ????????? inet addr:192.168.17.109? Bcast:192.168.17.255? Mask:255.255.255.0 ????????? inet6 addr: fe80::20c:29ff:fe8c:efe6/64 Scope:Link ????????? UP BROADCAST RUNNING MULTICAST? MTU:1500? Metric:1 ????????? RX packets:171 errors:0 dropped:0 overruns:0 frame:0 ????????? TX packets:131 errors:0 dropped:0 overruns:0 carrier:0 ????????? collisions:0 txqueuelen:1000 ????????? RX bytes:18223 (18.2 KB)? TX bytes:13282 (13.2 KB) lo??????? Link encap:Local Loopback ? ????????? inet addr:127.0.0.1? Mask:255.0.0.0 ????????? inet6 addr: ::1/128 Scope:Host ????????? UP LOOPBACK RUNNING? MTU:65536? Metric:1 ????????? RX packets:16 errors:0 dropped:0 overruns:0 frame:0 ????????? TX packets:16 errors:0 dropped:0 overruns:0 carrier:0 ????????? collisions:0 txqueuelen:0 ????????? RX bytes:1184 (1.1 KB)? TX bytes:1184 (1.1 KB) lo:0????? Link encap:Local Loopback ? ????????? inet addr:192.168.17.113? Mask:255.255.255.255 ????????? UP LOOPBACK RUNNING? MTU:65536? Metric:1 ------------------ 7. 测试双机热备 7.1 两节点重启后。 7.2 在smyslq1上创建测试帐号和测试数据库 create database briandb; create user 'mytest'@'%' identified by '146'; grant all privileges on briandb.* to 'mytest'@'%' identified by '146'; create user 'mytest'@'localhost' identified by '146'; grant all privileges on briandb.* to 'mytest'@'localhost' identified by '146'; 7.3 在第三个节点上执行 mysql -h 192.168.17.113 -u mytest -p use briandb use briandb; create table t1( ?? id INT NOT NULL AUTO_INCREMENT,?? title VARCHAR(100) NOT NULL,?? PRIMARY KEY (id) ); insert into t1 (title) values('tom'); 此时，分别去检查smysql1和mysql2，数据都出来了。执行同步了。 7.4 停掉smysql1上的mysql服务。此时，在第三方节点上登录mysql -h 192.168.17.113 -u mytest -p 依然能登录。插入新数据： insert into t1 (title) values('tom'); 然后去smysql2上检查，数据被更新了。 然后，重新启动smysql1，登录它，检查也被更新了。效果符合预期。 7.5 在smysql1上执行/etc/init.d/keepalived stop，停止smysql1上的keepalived服务。 此时，在第三方节点上登录mysql -h 192.168.17.113 -u mytest -p 依然能登录。插入新数据： insert into t1 (title) values('kit'); 然后去smysql2上检查，数据被更新了。 然后，登录smysql1，检查也被更新了。效果符合预期。 在smysql1上重启keepalived，发现113又漂移回112，负责预期。 至此，一切符合预期。 双机主主同步热备自动故障切换ip漂移成功。 8. 在云上部署的重要事项： 阿里云，aws，都不支持keepalived，无论是经典网络还是专有网络，都不行，所以不要试图在它们上部署。 ucloud是可以的。