Linux下keepalived+Mysql主主同步实现数据库高可用实践

来源：互联网发布：蒙面唱将猜猜猜知乎编辑：程序博客网时间：2024/05/16 05:37

1）：编辑mysql配置文件/etc/my.cnf，选定主主同步的数据库

[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysqlsymbolic-links=0log-bin=binlogbinlog_format=mixedserver_id=1//主备两台mysql的server_id须不同binlog-do-db=renderreplicate-do-db=render[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

2）：操作主数据库，"show master status"后记录下其file和position

grant replication slave on *.* to 'slave'@'192.168.1.90' identified by '123456';flush privileges;show master status;

3）：操作从数据库，如果show slave status正常，则主从同步机制建立。同时也在从数据库上开启master，以便主数据库也能从从数据库同步数据

change master to master_host='192.168.1.33',master_user='slave',master_password='123456',master_port=3306,master_log_file='binlog.000001',master_log_pos=207017;start slave;show slave status;grant replication slave on *.* to 'slave'@'192.168.1.33' identified by '123456';flush privileges;show master status;

4）：操作主数据库，如果show slave status正常，则主主同步机制建立完成。

master-mysql:change master to master_host='192.168.1.90',master_user='slave',master_password='123456',master_port=3306,master_log_file='mysql-bin.000014',master_log_pos=2461;start slave;show slave status;

5）：数据库主主机制建立后，还需要在两台机器上都安装keepalived，以便向外提供一个VIP，当某台机器挂的时候，不影响数据库的使用。

a）需要安装ipvsadm和keepalived：yum install ipvsadm;yum install keepalived;

b）编辑/etc/keepalived/keepalived.conf。

global_defs {   notification_email {     acassen@firewall.loc     failover@firewall.loc     sysadmin@firewall.loc   }   notification_email_from Alexandre.Cassen@firewall.loc   smtp_server 192.168.1.33   smtp_connect_timeout 30   router_id LVS_DEVEL}vrrp_script chk_mysqld{   script "/etc/keepalived/mysql.sh"   interval 3   weight -20}vrrp_instance VI_1 {    state MASTER//主备不同    interface eth1    virtual_router_id 51    priority 100//主备不同，备的比主的要小    advert_int 1    authentication {        auth_type PASS        auth_pass 1111    }    virtual_ipaddress {        192.168.1.13    }} virtual_server 192.168.1.13 3306 {    delay_loop 2    lb_algo rr    lb_kind DR    persistence_timeout 60    protocol TCP    real_server 192.168.1.90 3306{//主备两台机器的实IP不同        weight 3        notify_down /etc/keepalived/mysql.sh        TCP_CHECK{            connect_timeout 3            nb_get_retry 3            connect_port 3306        }    }}

c）编辑/etc/keepalived/mysql.sh。keepalived会定时启动mysql.sh脚本检查数据库状态，当数据库异常时，杀死当前机器的keepalived，VIP将被另一台数据库机器绑定，从而实现数据库的高可用，平滑切换。

#!/bin/bashMYSQL_OK=1function check_mysql_helth (){    mysql  -e "show status;" &>/dev/null    if [ $? = 0 ] ;then    MYSQL_OK=1             else           MYSQL_OK=0             fi                             return $MYSQL_OK           }while [ $CHECK_TIME -ne 0 ]do    let "CHECK_TIME -= 1"    check_mysql_helthif [ $MYSQL_OK = 1 ] ; then    echo "mysql ok\n"    CHECK_TIME=0    exit 0fiif [ $MYSQL_OK -eq 0 ] &&  [ $CHECK_TIME -eq 0 ]then    pkill keepalived    exit 1fisleep 1done

d）启动keepalived：/usr/sbin/keepalived -D。使用VIP（这里是192.168.1.13）连接数据库即可。

0 0