查看“通过keepalived搭建mysql双主高可用集群”的源代码


通过keepalived搭建mysql双主高可用集群
== pre==
先细看原来的pl脚本  实在不行就换 

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

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

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

==配置主主==
=== db1 到db2的主从===
<pre>
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;

</pre>


===配置从db2 到db1的mysql 主从复制  和上面的过程完全一样===
<pre>
创建复制用户并授权

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;
</pre>

== step 2  配置keepalived 实现mysql 双主高可用 ==
[[Keepalived安装和配置]]

=== 第一种 ===
<pre>! Configuration File forkeepalived
global_defs {
notification_email {
test@sina.com
 }
notification_email_from  admin@test.com
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 {
test@sina.com
 }
notification_email_from  admin@test.com
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
</pre>

[http://lizhenliang.blog.51cto.com/7876557/1362313 MySQL高可用性之Keepalived+Mysql（双主热备）]




=== 第二种 perl 脚本===
<pre>
mkdir -p /etc/keepalived/mysqlcheck/

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

</pre>


==测试==

===主从同步===
<pre>
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,"evan@1.cn","password");


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

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

mysql> select * from repl_table;
+------+-----------+----------+
| id   | email     | password |
+------+-----------+----------+
|    1 | evan@1.cn | password |
+------+-----------+----------+


或者 直接登录db2 邂逅执行数据库的读，学操作，看数据能不能 迅速同步到db1中 
</pre>
===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

== 参考==

[http://blog.csdn.net/mydriverc2/article/details/50964543  good  have perl]

[https://linux.cn/blog-16475-5961.html  通过配置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




==安全相关==
<pre>
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]
</pre>
 [[category:mysql]]