通过keepalived搭建mysql双主高可用集群

来自linux中国网wiki
跳到导航 跳到搜索

通过keepalived搭建mysql双主高可用集群

pre

先细看原来的pl脚本 实在不行就换

可以参考 << 构建高可用linux服务器>>p274

问题 在已有的主从上 怎么加不影响数据呢

主主切换时,从服务器不受影响 MHA(Master High Availability)完全符合你的要求。

配置主主

db1 到db2的主从

os  centos7
192.168.30.200  VIP 

192.168.30.77  DB1 master 
0192.168.30.78   DB2  slave 


192.168.30.73 slave2 

192.168.30.74


1.修改mysql配置文件 

#on master 
server-id = 1
log-bin = /data/apps/mysql/binlog/mysql-bin
relay-log = /data/apps/mysql/binlog/mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%


#on slave 
server-id = 2
log-bin = /data/apps/mysql/binlog/mysql-bin
relay-log = /data/apps/mysql/binlog/mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%


2.手动同步数据库
mysql > FLUSH TABLES WITH READ LOCK;

然后在另一个窗口mysqldump 导出数据 


3.创建复制用户并授权

on db1 
mysql> grant replication slave on *.* to 'repl_user'@'192.168.30.78' identified  by 'repl_passwd';

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      335 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+



在db2中把db1设为自己的主服务器
mysql> change master to  master_host = '192.168.30.77', master_user='repl_user', master_password='repl_passwd', master_log_file='mysql-bin.000004', master_log_pos=335;

mysql> start slave;

mysql> show slave status\G;


配置从db2 到db1的mysql 主从复制 和上面的过程完全一样

创建复制用户并授权

mysql> grant replication slave on *.* to 'repl_user'@'192.168.30.77' identified  by 'repl_passwd';

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      335 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

在db1中把db2设为自己的主服务器
on db1

change master to  master_host = '192.168.30.78', master_user='repl_user', master_password='repl_passwd', master_log_file='mysql-bin.000004', master_log_pos=335;

mysql> start slave;

mysql> show slave status\G;

step 2 配置keepalived 实现mysql 双主高可用

Keepalived安装和配置

第一种

! Configuration File forkeepalived
global_defs {
notification_email {
[email protected]
 }
notification_email_from  [email protected]
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MYSQL_HA      #标识,双主相同
 }
vrrp_instance VI_1 {
 state BACKUP           #两台都设置BACKUP
 interface eth0
 virtual_router_id 51       #主备相同
 priority 100           #优先级,backup设置90
 advert_int 1
 nopreempt             #不主动抢占资源,只在master这台优先级高的设置,backup不设置
 authentication {
 auth_type PASS
 auth_pass 1111
 }
 virtual_ipaddress {
 192.168.30.200
 }
}
virtual_server 192.168.30.200 3306 {
 delay_loop 2
 #lb_algo rr              #LVS算法,用不到,我们就关闭了
 #lb_kind DR              #LVS模式,如果不关闭,备用服务器不能通过VIP连接主MySQL
 persistence_timeout 50  #同一IP的连接60秒内被分配到同一台真实服务器
 protocol TCP
 real_server 192.168.30.77 3306 {   #检测本地mysql,backup也要写检测本地mysql
 weight 3
 notify_down /etc/keepalived/mysql.sh    #当mysq服down时,执行此脚本,杀死keepalived实现切换
 TCP_CHECK {
 connect_timeout 3    #连接超时
 nb_get_retry 3       #重试次数
 delay_before_retry 3 #重试间隔时间
  }
}


#db2 
! Configuration File forkeepalived
global_defs {
notification_email {
[email protected]
 }
notification_email_from  [email protected]
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MYSQL_HA      #标识,双主相同
 }
vrrp_instance VI_1 {
 state BACKUP           #两台都设置BACKUP
 interface eth0
 virtual_router_id 51       #主备相同
 priority 90          #优先级,backup设置90
 advert_int 1
 #nopreempt             #不主动抢占资源,只在master这台优先级高的设置,backup不设置
 authentication {
 auth_type PASS
 auth_pass 1111
 }
 virtual_ipaddress {
 192.168.30.200
 }
}
virtual_server 192.168.30.200 3306 {
 delay_loop 2
 #lb_algo rr              #LVS算法,用不到,我们就关闭了
 #lb_kind DR              #LVS模式,如果不关闭,备用服务器不能通过VIP连接主MySQL
 persistence_timeout 50  #同一IP的连接60秒内被分配到同一台真实服务器
 protocol TCP
 real_server 192.168.30.78 3306 {   #检测本地mysql,backup也要写检测本地mysql
 weight 3
 notify_down /etc/keepalived/mysql.sh    #当mysq服down时,执行此脚本,杀死keepalived实现切换
 TCP_CHECK {
 connect_timeout 3    #连接超时
 nb_get_retry 3       #重试次数
 delay_before_retry 3 #重试间隔时间
  }
}




root@master ~]# vi /etc/keepalived/mysql.sh
#!/bin/bash
pkill keepalived
[root@master ~]# chmod +x /etc/keepalived/mysql.sh
[root@master ~]# /etc/init.d/keepalived start

MySQL高可用性之Keepalived+Mysql(双主热备)



第二种 perl 脚本

mkdir -p /etc/keepalived/mysqlcheck/

yum install  perl-DBI perl-DBD-MySQL -y


测试

主从同步

1.在远程客户端通过vip 登录测试 
mysql> show variables like "%hostname%";
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| hostname      | localhost.localdomain |
+---------------+-----------------------+

mysql> show variables like "server_id%";
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| server_id      | 1     |
| server_id_bits | 32    |
+----------------+-------+

从上面可见 通过vip 登录,此时登录了db1服务器 


2.数据复制功能测试 

mysql> create database repldb;

mysql> use repldb;
Database changed

mysql> create table repl_table(id int ,email varchar(80), password varchar(40) not null);

mysql> show tables;
+------------------+
| Tables_in_repldb |
+------------------+
| repl_table       |
+------------------+
mysql> insert into repl_table (id,email,password) values(1,"[email protected]","password");


然后登录db2 看是不是有这个数据 和这个表 

mysql> use repldb;
Database changed
mysql> show tables;
+------------------+
| Tables_in_repldb |
+------------------+
| repl_table       |
+------------------+

mysql> select * from repl_table;
+------+-----------+----------+
| id   | email     | password |
+------+-----------+----------+
|    1 | [email protected] | password |
+------+-----------+----------+


或者 直接登录db2 邂逅执行数据库的读,学操作,看数据能不能 迅速同步到db1中 

mysql故障转移

我的思考 如果mysql 挂了 就关掉keepalived


http://debugo.com/keepalived-mysql/ http://duyunlong.blog.51cto.com/1054716/1310405

但是还是不够呀 要检查一下 mysql 主从状态呀


stop db1 mysql 连接不上vip 了 200 ip 还在db1 呢 尴尬


问题解决

1.Slave_IO_Running: Connecting

systemctl stop firewalld.service

参考

good have perl

通过配置keepalived实现MySQL双主互备高可


pkill keepalived,主要作用是如果本机的mysql挂掉了,那么同时会杀死本机的keepalived,这样另外一台就会接替他工作,虚拟IP也会被另一台接管 http://duyunlong.blog.51cto.com/1054716/1310405


http://lizhenliang.blog.51cto.com/7876557/1362313


利用keepalived构建高可用MySQL-HA mysql down 直接关掉keepalived 的 http://database.51cto.com/art/201012/237204.htm



Install and setup MySQL HA using Percona XtraDB Multi-Master and HAProxy https://wiki.infn.it/progetti/cloud-areapd/mysql_high_availability



安全相关

centos7 关闭防火墙和selinux

1、直接关闭防火墙

systemctl stop firewalld.service #停止firewall

systemctl disable firewalld.service #禁止firewall开机启动


2、设置 iptables service

yum -y install iptables-services


systemctl restart iptables.service #重启防火墙使配置生效

systemctl enable iptables.service #设置防火墙开机启动



CentOS 7   #关闭selinux


sed -i 's/SELINUX=enforcing/SELINUX=disabled/'  /etc/sysconfig/selinux

/etc/sysconfig/selinux
SELINUX=disabled




#设置 SELinux 状态
setenforce 0



#获取 SELinux 状态
getenforce


这个目录又是什么呢 
/etc/selinux/config

[http://www.jianshu.com/p/d6414b5295b8  centos7 关闭防火墙和selinux]