通过keepalived搭建mysql双主高可用集群
docker>Evan2017年7月14日 (五) 10:25的版本 （→安全相关）
通过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 双主高可用
第一种
! 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
参考
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]
