Mysql + LVS + KeepAlived 主主同步
1、IP地址规划msyql-1 192.168.1.219 vip 192.168.1.230
mysql-2 192.168.1.220 vip 192.168.1.230
2、安装mysql
3、配置mysql的主主同步
建立mysql同步账户
# mysql -uroot -pmysqlpassword
mysql> GRANT ALL privileges ON *.* TO syncsql@'192.168.1.220' IDENTIFIED BY '123456'; mysql-1上执行
mysql> GRANT ALL privileges ON *.* TO syncsql@'192.168.1.219' IDENTIFIED BY '123456'; mysql-2上执行
配置my.cnf
# vi /etc/my.cnf
log-bin=mysql-bin
server-id = 2 mysql-2上更改，不能和mysql-1重复
在mysql-2同步mysql-1
mysql> show master status; 在mysql-1上执行
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000013 | 106|
| |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> change master to 在mysql-2上执行
-> master_host='192.168.1.219',
-> master_user='syncsql',
-> master_password='123456',
-> master_log_file='mysql-bin.000013',
-> master_log_pos=106;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.219
Master_User: syncsql
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000013
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000147
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000013
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 106
Relay_Log_Space: 552
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
同样在mysql-1上执行同步
mysql> change master to 在mysql-2上执行
-> master_host='192.168.1.219',
-> master_user='syncsql',
-> master_password='123456',
-> master_log_file='mysql-bin.000006',
-> master_log_pos=106;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.220
Master_User: syncsql
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000160
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 106
Relay_Log_Space: 552
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
可以分别在mysql-1和msyql-2上添加一些数据做测试主主是否同步
mysql> grant all privileges on *.* to 'root'@'%' identified by 'mysqlpassword' with grant option; 开启root的远程登录，不然vip地址无法用客户端连接
mysql> flush privileges;
4、安装lvs mysql-1、mysql-2都需要安装
# yum install kernel-devel
# ln -s /usr/src/kernels/2.6.32-504.23.4.el6.x86_64/ /usr/src/linux
# yum install libnl* libpopt* popt-static
# wget http://www.linuxvirtualserver.org/software/kernel-2.6/ipvsadm-1.26.tar.gz
# tar zxvf ipvsadm-1.26.tar.gz
# cd ipvsadm-1.26
# make
# make install
5、安装keepalived mysql-1、mysql-2都需要安装
# wget http://www.keepalived.org/software/keepalived-1.2.19.tar.gz
# tar zxvf keepalived-1.2.19.tar.gz
# cd keepalived-1.2.19
# ./configure --prefix=/usr/local/keepalived
# make
# make install
# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
# mkdir /etc/keepalived
6、配置keepalived
# vim /etc/keepalived/keepalived.conf mysql-1配置
! Configuration File for keepalived
global_defs {
notification_email {
keepalive@163.com
}
notification_email_fromkeepalived@mysql-1.com
smtp_server localhost
smtp_connect_timeout 30
router_id LVS1
}
vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 51
priority 180
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.1.230
}
}
virtual_server 192.168.1.230 3306 {
delay_loop 6
lb_algo rr
lb_kind DR
persistence_timeout 20
protocol TCP
sorry_server 192.168.1.220 3306
real_server 192.168.1.219 3306 {
weight 3
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}
# vim /etc/keepalived/keepalived.conf mysql-2keepalived.conf配置
global_defs {
notification_email {
keepalived@163.com
}
notification_email_from root@localhost.com
smtp_serverlocalhost
smtp_connect_timeout 30
router_id LVS1
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 150
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.1.230
}
}
virtual_server 192.168.1.230 3306 {
delay_loop 6
lb_algo rr
lb_kind DR
persistence_timeout 20
protocol TCP
sorry_server 192.168.1.220 3306
real_server 192.168.1.219 3306 {
weight 3
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}
8、配置realserver mysql-1 mysql-2都做
# vim /etc/rc.d/init.d/realserver.sh
#!/bin/bash
# description: Config realserver lo and apply noarp
SNS_VIP=192.168.1.230
/etc/rc.d/init.d/functions
case "$1" in
start)
ifconfig lo:0 $SNS_VIP netmask 255.255.255.255 broadcast $SNS_VIP
/sbin/route add -host $SNS_VIP dev lo:0
echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore
echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce
echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore
echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce
sysctl -p >/dev/null 2>&1
echo "RealServer Start OK"
;;
stop)
ifconfig lo:0 down
route del $SNS_VIP >/dev/null 2>&1
echo "0" >/proc/sys/net/ipv4/conf/lo/arp_ignore
echo "0" >/proc/sys/net/ipv4/conf/lo/arp_announce
echo "0" >/proc/sys/net/ipv4/conf/all/arp_ignore
echo "0" >/proc/sys/net/ipv4/conf/all/arp_announce
echo "RealServer Stoped"
;;
*)
echo "Usage: $0 {start|stop}"
exit 1
esac
# chmod 755 /etc/rc.d/init.d/realserver.sh
# chmod 755 /etc/rc.d/init.d/functions
9、启动keepalived和realserver mysql-1和mysql-2都做
# /etc/init.d/keepalived start
# /etc/init.d/realserver.sh start
# chkconfig --add keepalived
# chkconfig keepalived on
# echo "/etc/init.d/realserver.sh start" >>/etc/rc.d/rc.local
10、验证
# ipvsadm -ln
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
-> RemoteAddress:Port Forward Weight ActiveConn InActConn
TCP192.168.1.230:3306 rr persistent 20
-> 192.168.1.219:3306 Local 3 2 0
停掉mysql-1上的mysql
# service mysqld stop
# ipvsadm -ln
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
-> RemoteAddress:Port Forward Weight ActiveConn InActConn
TCP192.168.1.230:3306 rr persistent 20
-> 192.168.1.220:3306 Route 1 1 0
# tail -f /var/log/messages mysql-1日志
Jul 27 09:48:20 scentos Keepalived_healthcheckers: TCP connection to :3306 failed !!!
Jul 27 09:48:20 scentos Keepalived_healthcheckers: Removing service :3306 from VS :3306
Jul 27 09:48:20 scentos Keepalived_healthcheckers: Lost quorum 1-0=1 > 0 for VS :3306
Jul 27 09:48:20 scentos Keepalived_healthcheckers: Adding sorry server :3306 to VS :3306
Jul 27 09:48:20 scentos Keepalived_healthcheckers: Removing alive servers from the pool for VS :3306
Jul 27 09:48:20 scentos Keepalived_healthcheckers: Remote SMTP server :25 connected.
Jul 27 09:48:40 scentos Keepalived_healthcheckers: SMTP alert successfully sent.
# tail -f /var/log/messages mysql-2日志
Jul 27 09:48:31 localhost Keepalived_healthcheckers: TCP connection to :3306 failed !!!
Jul 27 09:48:31 localhost Keepalived_healthcheckers: Removing service :3306 from VS :3306
Jul 27 09:48:31 localhost Keepalived_healthcheckers: Lost quorum 1-0=1 > 0 for VS :3306
Jul 27 09:48:31 localhost Keepalived_healthcheckers: Adding sorry server :3306 to VS :3306
Jul 27 09:48:31 localhost Keepalived_healthcheckers: Removing alive servers from the pool for VS :3306
Jul 27 09:48:31 localhost Keepalived_healthcheckers: Remote SMTP server :25 connected.
Jul 27 09:48:31 localhost Keepalived_healthcheckers: SMTP alert successfully sent.
# mysql -uroot -p -h 192.168.1.230 连接vip230看是否可以操作数据库
页:
[1]