设为首页 收藏本站
查看: 778|回复: 0

[经验分享] mysql高可用案例

[复制链接]

尚未签到

发表于 2018-10-9 06:48:05 | 显示全部楼层 |阅读模式
  1.MHA+LVS
  http://www.chocolee.cn/archives/276
  http://dbaplus.cn/news-11-754-1.html
  2. 一步一步打造MySQL高可用平台
  http://www.jianshu.com/p/bc50221972ca?from=jiantop.com
  代理层功能
  1、授权认证模型;
  2、SQL拦截;
  3、负载均衡;
  4、读写分离;
  5、高可用;
  6、大SQL隔离;
  拓展思想:为了保证数据一致性,MySQL复制中,常常会在Master上使用sync_binlog参数保证binlog持久化,保证数据一致性。但这种方式对磁盘I/O会造成10~20%的影响。但是还有另外一个思路,就是使用MySQL半同步复制来保证数据一致性,MySQL半同步复制是在从服务器的内存中处理数据并进行发聩,虽然也会造成性能影响,但是相对于对Master造成的磁盘I/O的影响来说,反而是个更好的方法。据《高性能MySQL》 第三版中10.9的测试,写入远程的内存(一台从库的反馈)比写入本地的磁盘(写入并刷新)要更快。使用半同步复制相比主在主库上进行强持久化的性能有两倍的改善。
  mha实现功能
  1.ssh验证
  自己也需要验证
  2.backup master && slave 设置read only
  3.lvs
  lvs 虚拟ip只有read ip,write ip在mha配置文件指定
  4.write and read 是怎么控制的?
  在Master上绑定写VIP,mha控制的
  5.为什么backup master 比slave更早的成为master,什么控制的?
  通过Failover脚本在Backup Master上绑定WVIP,提升其为主库
  6.当MHA把Master切换到了Backup Master上后,LVS如何处理分发在Backup Master上的读操作?
  解释:由于Keepalived会通过脚本定期监控Backup Master的状态,包括同步、SQL线程、I/O线程,所以当Backup Master升级为主库后,这些状态都将消失,Keepalived将自动将Backup Master剔除出负载均衡集群。
  效果
  write 两个master会变成这样
  注意back master 变成master后有两个虚拟ip
  [root@mysql-02 mysql]# ifconfig
  eth0      Link encap:Ethernet  HWaddr 00:0C:29:62:39:F8
  inet addr:10.0.0.124  Bcast:10.0.0.255  Mask:255.255.255.0
  inet6 addr: fe80::20c:29ff:fe62:39f8/64 Scope:Link
  UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
  RX packets:236889 errors:0 dropped:0 overruns:0 frame:0
  TX packets:69213 errors:0 dropped:0 overruns:0 carrier:0
  collisions:0 txqueuelen:1000
  RX bytes:221270914 (211.0 MiB)  TX bytes:5949360 (5.6 MiB)
  eth0:1    Link encap:Ethernet  HWaddr 00:0C:29:62:39:F8
  inet addr:10.0.0.131  Bcast:10.0.0.255  Mask:255.255.255.0
  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:16436  Metric:1
  RX packets:244 errors:0 dropped:0 overruns:0 frame:0
  TX packets:244 errors:0 dropped:0 overruns:0 carrier:0
  collisions:0 txqueuelen:0
  RX bytes:22781 (22.2 KiB)  TX bytes:22781 (22.2 KiB)
  lo:Rvip   Link encap:Local Loopback
  inet addr:10.0.0.132  Mask:0.0.0.0
  UP LOOPBACK RUNNING  MTU:16436  Metric:1
  普通的slave只有一个read虚拟ip
  #!/bin/bash
  vip=10.0.0.132
  open() {
  ifconfig lo:Rvip ${vip}/32 up
  sysctl -w net.ipv4.conf.lo.arp_announce=2
  sysctl -w net.ipv4.conf.lo.arp_ignore=1
  sysctl -w net.ipv4.conf.all.arp_announce=2
  sysctl -w net.ipv4.conf.all.arp_ignore=1
  }
  close() {
  ifconfig lo:Rvip down
  sysctl -w net.ipv4.conf.lo.arp_announce=0
  sysctl -w net.ipv4.conf.lo.arp_ignore=0
  sysctl -w net.ipv4.conf.all.arp_announce=0
  sysctl -w net.ipv4.conf.all.arp_ignore=0
  }
  case $1 in
  start)
  open
  ;;
  stop)
  close
  ;;
  *)
  echo "Usage: $0 need argument  [start|stop]"
  ;;
  esac
  具体配置,不想再看第二遍了
  mysql-01 master
  mysql-02 slave

  mysql-03,mysql-04 slave操作与mysql-02操作相同,由于这两台只做从库,只需要修改server>  配置ssh免密码登陆
  配置master到所有node
  配置manager到所有node
  配置backup master到所有node
  配置slave到所有node
  在数据库中创建mha管理用户
  在master上创建管理用户
  在从库检查是否同步
  安装MHA
  lvs-02安装manager
  配置MHA
  manager MHA 配置文件路径: /etc/mha
  [root@lvs-02 app1]# cat app1.conf
  [server default]
  manager_workdir=/etc/mha/app1
  manager_log=/etc/mha/app1/manager.log
  master_binlog_dir= /dbdata/data
  ssh_user=root
  user=mha
  password=mhapwd
  repl_user=rep
  repl_password=reppasswd
  secondary_check_script= masterha_secondary_check -s 10.0.0.126 -s 10.0.0.123
  ping_interval=3
  master_ip_failover_script= /etc/mha/app1/master_ip_failover
  #shutdown_script= /script/masterha/power_manager
  #report_script= /script/masterha/send_report
  #master_ip_online_change_script= /etc/mha/master_ip_failover
  [server1]
  hostname=10.0.0.123
  port=3306
  candidate_master=1
  [server2]
  hostname=10.0.0.124
  port=3306
  candidate_master=1
  #check_repl_delay=0
  [server3]
  hostname=10.0.0.125
  port=3306
  no_master=1
  [server4]
  hostname=10.0.0.126
  port=3306
  no_master=1
  故障转移脚本
  cat master_ip_failover
  #!/usr/bin/env perl
  use strict;
  use warnings FATAL => 'all';
  use Getopt::Long;
  my (
  $command,          $ssh_user,        $orig_master_host, $orig_master_ip,
  $orig_master_port, $new_master_host, $new_master_ip,    $new_master_port
  );
  my $vip = '10.0.0.131/24';  # Virtual IP
  my $key = "1";
  my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
  my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";
  $ssh_user = "root";
  GetOptions(
  'command=s'          => \$command,
  'ssh_user=s'         => \$ssh_user,
  'orig_master_host=s' => \$orig_master_host,
  'orig_master_ip=s'   => \$orig_master_ip,
  'orig_master_port=i' => \$orig_master_port,
  'new_master_host=s'  => \$new_master_host,
  'new_master_ip=s'    => \$new_master_ip,
  'new_master_port=i'  => \$new_master_port,
  );
  exit &main();
  sub main {
  print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
  if ( $command eq "stop" || $command eq "stopssh" ) {
  # $orig_master_host, $orig_master_ip, $orig_master_port are passed.
  # If you manage master ip address at global catalog database,
  # invalidate orig_master_ip here.
  my $exit_code = 1;
  #eval {
  #    print "Disabling the VIP on old master: $orig_master_host \n";
  #    &stop_vip();
  #    $exit_code = 0;
  #};
  eval {
  print "Disabling the VIP on old master: $orig_master_host \n";
  #my $ping=`ping -c 1 10.0.0.13 | grep "packet loss" | awk -F',' '{print $3}' | awk '{print $1}'`;
  #if ( $ping le "90.0%" && $ping gt "0.0%" ){
  #$exit_code = 0;
  #}
  #else {
  &stop_vip();
  # updating global catalog, etc
  $exit_code = 0;
  #}
  };
  if ($@) {
  warn "Got Error: $@\n";
  exit $exit_code;
  }
  exit $exit_code;
  }
  elsif ( $command eq "start" ) {
  # all arguments are passed.
  # If you manage master ip address at global catalog database,
  # activate new_master_ip here.
  # You can also grant write access (create user, set read_only=0, etc) here.
  my $exit_code = 10;
  eval {
  print "Enabling the VIP - $vip on the new master - $new_master_host \n";
  &start_vip();
  $exit_code = 0;
  };
  if ($@) {
  warn $@;
  exit $exit_code;
  }
  exit $exit_code;
  }
  elsif ( $command eq "status" ) {
  print "Checking the Status of the script.. OK \n";
  `ssh $ssh_user\@$orig_master_ip \" $ssh_start_vip \"`;
  exit 0;
  }
  else {
  &usage();
  exit 1;
  }
  }
  # A simple system call that enable the VIP on the new master
  sub start_vip() {
  `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
  }
  # A simple system call that disable the VIP on the old_master
  sub stop_vip() {
  `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
  }
  sub usage {
  print
  "Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
  }
  # the end.
  backup master & slave 设置read_only防止被写
  set global read_only=1;
  检查并启动mha
  检查SSH情况:masterha_check_ssh --conf=/etc/mha/app1/app1.conf
  检查复制情况:masterha_check_repl --conf=/etc/mha/app1/app1.conf
  启动mha
  当有slave节点宕掉的情况是启动不了的,加上--ignore_fail_on_start即使有节点宕掉也能启动mha
  检查mysql-01虚拟IP
  安装lvs,keepalived
  ipvsadm-1.26适用于内核2.6.28及之后的内核版本。
  CentOS5.X安装LVS,使用1.2.4版本,不要用1.2.6.
  lsmod |grep ip_vs 出现了ip_vs等信息,证明安装成功了。
  backup master & slave配置arp抑制及绑定vip
  #!/bin/bash
  vip=10.0.0.132
  open() {
  ifconfig lo:Rvip ${vip}/32 up
  sysctl -w net.ipv4.conf.lo.arp_announce=2
  sysctl -w net.ipv4.conf.lo.arp_ignore=1
  sysctl -w net.ipv4.conf.all.arp_announce=2
  sysctl -w net.ipv4.conf.all.arp_ignore=1
  }
  close() {
  ifconfig lo:Rvip down
  sysctl -w net.ipv4.conf.lo.arp_announce=0
  sysctl -w net.ipv4.conf.lo.arp_ignore=0
  sysctl -w net.ipv4.conf.all.arp_announce=0
  sysctl -w net.ipv4.conf.all.arp_ignore=0
  }
  case $1 in
  start)
  open
  ;;
  stop)
  close
  ;;
  *)
  echo "Usage: $0 need argument  [start|stop]"
  ;;
  esac
  配置keepalived
  [root@lvs-01 keepalived]# cat keepalived.conf
  ! Configuration File for keepalived
  global_defs {
  notification_email {
  test@gmail.com
  }
  notification_email_from  alert-noreply@test.com.cn
  smtp_server 127.0.0.1
  smtp_connect_timeout 30
  router_id blade1
  }
  # db master server.
  vrrp_instance VI_1 {
  state MASTER
  interface eth0
  virtual_router_id 51
  priority 200
  advert_int 5
  authentication {
  auth_type PASS
  auth_pass 123qwe
  }
  virtual_ipaddress {
  10.0.0.132/24
  }
  }
  # VIP 10.0.0.132
  virtual_server 10.0.0.132 3306 {
  delay_loop 10
  lb_algo rr
  lb_kind DR
  nat_mask 255.255.255.0
  protocol TCP
  #sorry_server 10.0.0.124 3306
  real_server 10.0.0.124 3306 {
  weight 1
  TCP_CHECK {
  connect_port 3306
  connect_timeout 10
  nb_get_retry 3
  delay_before_retry 5
  }
  MISC_CHECK {
  misc_path "/etc/keepalived/check_slave.py 10.0.0.124 3306"
  misc_dynamic
  }
  }
  real_server 10.0.0.125 3306 {
  weight 1
  TCP_CHECK {
  connect_port 3306
  connect_timeout 10
  nb_get_retry 3
  delay_before_retry 5
  }
  MISC_CHECK {
  misc_path "/etc/keepalived/check_slave.py 10.0.0.125 3306"
  misc_dynamic
  }
  }
  real_server 10.0.0.126 3306 {
  weight 1
  TCP_CHECK {
  connect_port 3306
  connect_timeout 10
  nb_get_retry 3
  delay_before_retry 5
  }
  MISC_CHECK {
  misc_path "/etc/keepalived/check_slave.py 10.0.0.126 3306"
  misc_dynamic
  }
  }
  }
  check_slave.py文件
  #!/usr/bin/env python
  #encoding:utf-8
  import MySQLdb
  import sys
  ip=sys.argv[1]
  user='rep'
  pwd='reppasswd'
  port=int(sys.argv[2])
  sbm=200
  Slave_IO_Running = ''
  Slave_SQL_Running = ''
  Seconds_Behind_Master = ''
  e=''
  try:
  conn = MySQLdb.connect(host=ip,user=user,passwd=pwd,port=port,charset='utf8')
  cur = conn.cursor()
  cur.execute('show slave status')
  db_info = cur.fetchall()
  for n in db_info:
  Slave_IO_Running = n[10]
  Slave_SQL_Running = n[11]
  Seconds_Behind_Master = n[32]
  cur.close()
  conn.close()
  except MySQLdb.Error,e:
  print "MySQLdb Error",e
  if e == "":
  if db_info != ():
  if Slave_IO_Running == "No" or Slave_SQL_Running == "No":
  #print 'thread err'
  exit(1)
  else:
  if Seconds_Behind_Master > sbm:
  #print 'timeout err'
  exit(1)
  else:
  #print 'OK'
  exit(0)
  else:
  #print 'slave err'
  exit(1)
  else:
  #print 'db err'
  exit(1)
  启动keepalived并检查vip
  [root@lvs-01 keepalived]# /etc/init.d/keepalived start
  [root@lvs-01 keepalived]# ip addr|grep 10.0.0.132
  inet 10.0.0.132/24 scope global secondary eth0
  [root@lvs-01 keepalived]# ipvsadm -Ln
  IP Virtual Server version 1.2.1 (size=4096)
  Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
  TCP  10.0.0.132:3306 rr
  -> 10.0.0.124:3306              Route   1      0          0
  -> 10.0.0.125:3306              Route   1      0          0
  -> 10.0.0.126:3306              Route   1      0          0
  [root@lvs-01 keepalived]#
  测试
  测试read vip负载均衡
  测试从库故障被剔除,恢复被挂起
  测试keepalived高可用vip切换
  测试 write vip切换,backup master 成为master
  lvs检查新主是否在read组中被剔除
  [root@lvs-01 keepalived]# ipvsadm -Ln
  IP Virtual Server version 1.2.1 (size=4096)
  Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
  TCP  10.0.0.132:3306 rr
  -> 10.0.0.125:3306              Route   1      0          0
  -> 10.0.0.126:3306              Route   1      0          0


运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-618024-1-1.html 上篇帖子: mysql5.7 MGR集群搭建 下篇帖子: 多实例MySQL启动脚本
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表