准备工作
1,两台主机,3个IP
mysql-1:192.168.0.1
mysql-2:192.168.0.2
vip 192.168.0.3
2,mysql安装包
下载地址:https://dev.mysql.com/downloads/mysql/
3,keepalived安装包
下载地址:https://www.keepalived.org/download.html
4,已验证的版本兼容
CentOS release 6.7 (Final) 兼容 keepalived-1.2.24
安装mydwl
1,安装mysql软件
略
2,配置my.cnf
两台主机使用不同的 server-id
[client]
port=3306
socket=/data9/mysql/log/mysql.sock
#客户端编码
default-character-set=utf8
[mysqld]
port=3306
basedir=/data9/mysql
datadir=/data9/mysql/data
socket=/data9/mysql/log/mysql.sock
log-error=/data9/mysql/log/mysql.log
pid-file=/data9/mysql/log/mysql.pid
server-id=1
log-bin=mysql-bin
#max_binlog_size = 500M
#expire_logs_days = 15
#主从同步时,需要同步的数据库,多个数据库写多行binlog_do_db配置
#binlog_do_db=test_db
#主从同步时,不需要同步的数据库,多个数据库写多行binlog_ignore_db配置
binlog_ignore_db=mysql
binlog_ignore_db=information_schema
binlog_ignore_db=sys
binlog_ignore_db=performance_schema
#主从同步方式
binlog_format=row
#服务端编码
character-set-server=utf8
双主配置
mysql-1创建用户并授权
grant replication slave on *.* to slave@'192.168.0.2' identified by 'slave';
flush privileges;
查看mysql-1
mysql> show master status;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000002 | 154 | | mysql,information_schema,sys,performance_schema | |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)
配置mysql-2对mysql-1的从库
登录mysql-2数据库
mysql> change master to
-> master_host='192.168.0.1',
-> master_port=3306,
-> master_user='slave',
-> master_password='slave',
-> master_log_file='mysql-bin.000002',
-> master_log_pos=154;
mysql> start slave;
相同方式配置mysql-1对mysql-2的从库
keepalived
1,安装keepalived并创建shutdown.sh
[root@localhost ~]## cd /opt
[root@localhost opt]# yum -y install gcc openssl-devel popt-devel psmisc
[root@localhost opt]# tar -zxvf keepalived-2.3.1.tar.gz
[root@localhost opt]# cd keepalived-2.3.1
[root@localhost keepalived-2.3.1]# ./configure --prefix=/opt/keepalived-2.3.1
[root@localhost keepalived-2.3.1]# make && make install
[root@localhost keepalived-2.3.1]mkdir /etc/keepalived
[root@localhost keepalived-2.3.1]cp keepalived/etc/keepalived/keepalived.conf.sample /etc/keepalived/keepalived.conf
[root@localhost keepalived-2.3.1]cp keepalived/etc/init.d/keepalived /etc/init.d/
[root@localhost keepalived-2.3.1]cp keepalived/etc/sysconfig/keepalived /etc/sysconfig/
[root@localhost keepalived-2.3.1]cp bin/keepalived /usr/sbin/
[root@localhost keepalived-2.3.1]vi /etc/keepalived/shutdown.sh
#!/bin/bash
#该脚本是在mysql服务出现异常时,将keepalived应用停止,从而使虚拟vip主机自动连接到另一台mysql上
killall keepalived
[root@localhost keepalived-2.3.1]chmod +x /etc/keepalived/shutdown.sh
2,查看网卡
bond0
[root@localhost ~]# ifconfig
bond0: flags=5187<UP,BROADCAST,RUNNING,MASTER,MULTICAST> mtu 1500
3,配置keepalived
192.168.0.1:/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 127.0.0.1
smtp_connect_timeout 30
router_id LVS_MASTER
vrrp_skip_check_adv_addr
vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
}
vrrp_instance VI_1 {
state MASTER
#interface为刚才查到的本机网卡名称
interface band0
#同一网段中同一组virtual_router_id值相同。不同组virtual_router_id值唯一。
virtual_router_id 212
#字数越大,优先级越高,master必须大于backup
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 2222
}
virtual_ipaddress {
#该ip为虚拟出来的vip地址
192.168.0.3
}
}
#配置virtual_server ip为上面配置的虚拟vip地址 端口为mysql的端口
virtual_server 192.168.0.3 3306 {
delay_loop 6
lb_algo rr
lb_kind NAT
persistence_timeout 50
protocol TCP
#real_server 该配置为实际物理机ip地址 以及实际物理机mysql端口
real_server 192.168.0.1 3306 {
#当该ip 端口连接异常时,执行该脚本
notify_down /etc/keepalived/shutdown.sh
TCP_CHECK {
#实际物理机ip地址
connect_ip 192.168.0.1
#实际物理机port端口
connect_port 3306
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
}
192.168.0.2:/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 127.0.0.1
smtp_connect_timeout 30
router_id LVS_MASTER
vrrp_skip_check_adv_addr
vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
}
vrrp_instance VI_1 {
state MASTER
#interface为刚才查到的本机网卡名称
interface band0
#同一网段中同一组virtual_router_id值相同。不同组virtual_router_id值唯一。
virtual_router_id 212
#字数越大,优先级越高,master必须大于backup
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 2222
}
virtual_ipaddress {
#该ip为虚拟出来的vip地址
192.168.0.3
}
}
#配置virtual_server ip为上面配置的虚拟vip地址 端口为mysql的端口
virtual_server 192.168.0.3 3306 {
delay_loop 6
lb_algo rr
lb_kind NAT
persistence_timeout 50
protocol TCP
#real_server 该配置为实际物理机ip地址 以及实际物理机mysql端口
real_server 192.168.0.2 3306 {
#当该ip 端口连接异常时,执行该脚本
notify_down /etc/keepalived/shutdown.sh
TCP_CHECK {
#实际物理机ip地址
connect_ip 192.168.0.2
#实际物理机port端口
connect_port 3306
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
}
4,开机自启动并启动keepalived
[root@localhost keepalived-2.3.1]# systemctl enable keepalived
[root@localhost keepalived-2.3.1]# systemctl start keepalived