wxsheng 发表于 2018-10-8 10:11:45

配置Keepalived实现mysql双主高可用

  http://ywliyq.blog.51cto.com/11433965/1856964
  本文为南非蚂蚁的书籍《循序渐进linux-第二版》-8.3.6的读笔记
  在DB1和DB2上安装Keepalived软件
  先安装编译工具
  # yum install gcc* gcc-c++ autoconf automake
  上传keepalived-1.2.19.tar.gz至/server/tools目录下
  # cd /server/tools
  # tar zxvf keepalived-1.2.19.tar.gz
  # cd keepalived-1.2.19
  # ./configure --sysconf=/etc --with-kernel-dir=/usr/src/kernels/2.6.32-504.el6.x86_64/
  # make
  # make install
  # ln -s /usr/local/sbin/keepalived /sbin/
  # chkconfig --add keepalived
  # chkconfig --level 35 keepalived on
  # cd ../
  安装完成后,进入DB1的配置过程
  DB1服务器上/etc/keepalived/keepalived.conf的配置内容,其中绿色字体为新增部分
  ! Configuration File for keepalived
  global_defs {
  notification_email {
  acassen@firewall.loc
  failover@firewall.loc
  sysadmin@firewall.loc
  }
  notification_email_from Alexandre.Cassen@firewall.loc
  smtp_server 192.168.200.1
  smtp_connect_timeout 30
  router_id LVS_DEVEL
  }
  vrrp_scripts check_mysqld {
  scripts "/etc/keepalived/mysqlcheck/check_slave.pl 127.0.0.1"   #检测mysql复制状态的脚本
  interval 2
  weight 21
  }
  vrrp_instance HA_1 {
  state BACKUP       #在DB1和DB2上均配置为BACKUP
  interface eth0
  virtual_router_id 80
  priority 90
  advert_int 2
  nopereempt         #不抢占模式,只在优先级高的机器上设置即可,优先级低的机器上不设置
  authentication {
  auth_type PASS
  auth_pass qweasdzxc
  }
  track_script {
  check_mysqld
  }
  virtual_ipaddress {
  10.24.24.110/24 dev eth0      #mysql的对外服务IP,即VIP
  }
  }
  vrrp_instance VI_1 {
  state MASTER
  interface eth0
  virtual_router_id 51
  priority 100
  advert_int 1
  authentication {
  auth_type PASS
  auth_pass 1111
  }
  virtual_ipaddress {
  192.168.200.16
  192.168.200.17
  192.168.200.18
  }
  }
  -----------------------------------------
  # mkdir /etc/keepalived/mysqlcheck/
  其中,/etc/keepalived/mysqlcheck/check_slave.pl文件的内容如下:
  # vim /etc/keepalived/mysqlcheck/check_slave.pl
  #!/usr/bin/perl -w
  use DBI;
  use DBD::mysql;
  # CONFIG VARIABLES
  $SBM = 120;
  $db = "ywadmin";
  $host = $ARGV;
  $port = 3306;
  $user = "root";
  $pw = "jzh0024";
  # SQL query
  $query = "show slave status";
  $dbh = DBI->connect("DBI:mysql:$db:$host:$port", $user, $pw, { RaiseError => 0,PrintError => 0 });
  if (!defined($dbh)) {
  exit 1;
  }
  $sqlQuery = $dbh->prepare($query);
  $sqlQuery->execute;
  $Slave_IO_Running ="";
  $Slave_SQL_Running = "";
  $Seconds_Behind_Master = "";
  while (my $ref = $sqlQuery->fetchrow_hashref()) {
  $Slave_IO_Running = $ref->{'Slave_IO_Running'};
  $Slave_SQL_Running = $ref->{'Slave_SQL_Running'};
  $Seconds_Behind_Master = $ref->{'Seconds_Behind_Master'};
  }
  $sqlQuery->finish;
  $dbh->disconnect();
  if ( $Slave_IO_Running eq "No" || $Slave_SQL_Running eq "No" ) {
  exit 1;
  } else {
  if ( $Seconds_Behind_Master > $SBM ) {
  exit 1;
  } else {
  exit 0;
  }
  }
  只需要修改红色的数据库名\数据库端口\用户名和密码即可
  添加可执行权限
  # chmod +x /etc/keepalived/mysqlcheck/check_slave.pl
  将keepalived.conf和check_slave.pl文件复制到DB2服务器对应的位置,将DB2上的keepalived.conf中的priority值修改为90,同时去掉nopreempt选项
  DB2上
  # mkdir /etc/keepalived/mysqlcheck/
  # cd /etc/keepalived/mysqlcheck/
  上传check_slave.pl脚本
  # rz
  # ll
  total 4
  -rw-r--r-- 1 root root 919 Sep 12 14:57 check_slave.pl
  # chmod +x check_slave.pl
  完成所有配置后,分别启动DB1和DB2上启动keepalived服务,正常情况下VIP地址应该运行在DB1服务器上
  # /etc/init.d/keepalived start
  Starting keepalived:                                       
  # /etc/init.d/keepalived start
  Starting keepalived:                                       
  ======================================================
  8.3.7 测试mysql主从同步功能
  首先在DB1,DB2上添加远程访问授权;
  DB1上授权

  mysql> grant all on *.* to 'root'@'10.24.24.%'>  Query OK, 0 rows affected (0.00 sec)
  mysql> flush privileges;
  Query OK, 0 rows affected (0.00 sec)
  mysql> select user,host from mysql.user;
  DB2上授权

  mysql> grant all on *.* to 'root'@'10.24.24.%'>  Query OK, 0 rows affected (0.00 sec)
  mysql> flush privileges;
  Query OK, 0 rows affected (0.00 sec)
  mysql> select user,host from mysql.user;
  1.在远程客户端通过VIP登录测试
  
  # mysql -uroot -p -h 10.24.24.110
  Enter password:
  mysql> show variables like "%hostname%";
  +---------------+-------+
  | Variable_name | Value |
  +---------------+-------+
  | hostname      | DB1   |
  +---------------+-------+
  1 row in set (0.00 sec)
  mysql> show variables like "%server_id%";
  +---------------+-------+
  | Variable_name | Value |
  +---------------+-------+
  | server_id   | 1   |
  +---------------+-------+
  1 row in set (0.00 sec)
  从SQL输出结果看,可以通过VIP登录,并且登录到了DB1服务器
  2.数据复制功能测试

  # mysql -uroot -p -h 10.24.24.110
  Enter password:
  mysql> create database repldb;
  Query OK, 1 row affected (0.00 sec)
  mysql> use repldb;
  Database changed
  mysql> create table repl_table(id int,email varchar(86),password varchar(40) not null);
  Query OK, 0 rows affected (0.00 sec)
  mysql> show tables;
  +------------------+
  | Tables_in_repldb |
  +------------------+
  | repl_table       |
  +------------------+
  1 row in set (0.00 sec)
  mysql> insert into repl_table (id,email,password) values(1,"ywliyq@163.com","qwessd");
  Query OK, 1 row affected (0.00 sec)
  mysql> select * from repl_table;
  +------+----------------+----------+

  |>  +------+----------------+----------+
  |    1 | ywliyq@163.com | qwessd   |
  +------+----------------+----------+
  1 row in set (0.00 sec)
  在DB2数据库中查询数据是否已经同步
  mysql> show databases;
  +--------------------+
  | Database         |
  +--------------------+
  | information_schema |
  | db01               |
  | db02               |
  | mysql            |
  | repldb             |
  | ywadmin            |
  +--------------------+
  6 rows in set (0.00 sec)
  mysql> use repldb;
  Database changed
  mysql> show tables;
  +------------------+
  | Tables_in_repldb |
  +------------------+
  | repl_table       |
  +------------------+
  1 row in set (0.00 sec)
  mysql> select * from repl_table;
  +------+----------------+----------+

  |>  +------+----------------+----------+
  |    1 | ywliyq@163.com | qwessd   |
  +------+----------------+----------+
  1 row in set (0.00 sec)
  证明在其他mysql客户端登录写入VIP的数据已经同步到DB2数据库中。
  ======================================================
  8.3.8 测试keepalived实现mysql故障切换
  为了测试keepalived实现的故障切换功能,需要模拟一些故障。
  比如,可以断开DB1主机的网络、关闭DB1主机、关闭DB1上mysql服务等各种操作实现;
  这里停止DB1服务器的网络连接,模拟DB1的mysql故障;
  由于在DB1、DB2服务器上都添加了监控MYSQL运行状态的脚本 check_slave.pl,因此当关闭DB1的MYSQL日志接收功能后,keepalived会立刻检测到,接着执行切换操作
  测试过程如下:
  1.停止DB1服务器的网络连接
  
  在远程mysql客户端以VIP地址登录到mysql系统中,不要退出这个连接;
  中断DB1服务器的网络连接;
  # /etc/init.d/network stop
  2.在mysql远程客户端测试
  继续在刚才打开的远程mysql连接中执行命令
  mysql> show variables like "%hostname%";
  +---------------+-------+
  | Variable_name | Value |
  +---------------+-------+
  | hostname      | DB2   |
  +---------------+-------+
  1 row in set (0.00 sec)
  mysql> show variables like "%server_id%";
  +---------------+-------+
  | Variable_name | Value |
  +---------------+-------+
  | server_id   | 2   |
  +---------------+-------+
  1 row in set (0.00 sec)

  接着重新开启DB1的网络连接,发现keepalived将不再执行切换操作了,因为上面将keepalived配置为抢占模式了。
  此时,mysql服务将一直在DB2服务器上运行,每次切换的代价很大,因而关闭了keepalived的主动抢占模式。
  http://ywliyq.blog.51cto.com/11433965/1856964

页: [1]
查看完整版本: 配置Keepalived实现mysql双主高可用