Mysql + LVS + KeepAlived 主主同步

wzslw

　　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_from  keepalived@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-2  keepalived.conf配置



global_defs {
notification_email {
keepalived@163.com
}
notification_email_from root@localhost.com
smtp_server  localhost
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

TCP  192.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

TCP  192.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[2472]: TCP connection to [192.168.1.219]:3306 failed !!!

Jul 27 09:48:20 scentos Keepalived_healthcheckers[2472]: Removing service [192.168.1.219]:3306 from VS [192.168.1.230]:3306

Jul 27 09:48:20 scentos Keepalived_healthcheckers[2472]: Lost quorum 1-0=1 > 0 for VS [192.168.1.230]:3306

Jul 27 09:48:20 scentos Keepalived_healthcheckers[2472]: Adding sorry server [192.168.1.220]:3306 to VS [192.168.1.230]:3306

Jul 27 09:48:20 scentos Keepalived_healthcheckers[2472]: Removing alive servers from the pool for VS [192.168.1.230]:3306

Jul 27 09:48:20 scentos Keepalived_healthcheckers[2472]: Remote SMTP server [0.0.0.0]:25 connected.

Jul 27 09:48:40 scentos Keepalived_healthcheckers[2472]: SMTP alert successfully sent.


　　# tail -f /var/log/messages     mysql-2日志


　　Jul 27 09:48:31 localhost Keepalived_healthcheckers[2453]: TCP connection to [192.168.1.219]:3306 failed !!!

Jul 27 09:48:31 localhost Keepalived_healthcheckers[2453]: Removing service [192.168.1.219]:3306 from VS [192.168.1.230]:3306

Jul 27 09:48:31 localhost Keepalived_healthcheckers[2453]: Lost quorum 1-0=1 > 0 for VS [192.168.1.230]:3306

Jul 27 09:48:31 localhost Keepalived_healthcheckers[2453]: Adding sorry server [192.168.1.220]:3306 to VS [192.168.1.230]:3306

Jul 27 09:48:31 localhost Keepalived_healthcheckers[2453]: Removing alive servers from the pool for VS [192.168.1.230]:3306

Jul 27 09:48:31 localhost Keepalived_healthcheckers[2453]: Remote SMTP server [0.0.0.0]:25 connected.

Jul 27 09:48:31 localhost Keepalived_healthcheckers[2453]: SMTP alert successfully sent.


　　


　　# mysql -uroot -p -h 192.168.1.230   连接vip230看是否可以操作数据库