mysql互为主从复制MM+keepalived高可用

mysql双主MM+keepalived高可用方案

一.安装单机mysql服务器

1.资源分配

服务器名	IP	VIP		MYSQL版本
Master	10.15.107.177	10.15.107.188	5.7
Slave	10.15.107.171	10.15.107.188	5.7

2.先在单机上安装配置https://blog.csdn.net/xyang81/article/details/51759200

二.mysql互为主从复制配置

1.Master上的配置

1.1修改my.cnf

1）修改/etc/my.cnf配置文件,主从复制必须开启binlog和不同的server-id.

 

[mysqld]

server-id=1       #不同id

log-bin = mysql-bin          #binlog文件

binlog_format=mixed                  #binlog日志格式

2) service mysqld restart #重启mysql

 

1.2授权复制专用用户

 

mysql>grant replication slave,replication client on *.* to repl@'10.15.%'  identified by 'Mysql123!'; 

mysql>flush privileges;

 

 

 

1.3锁主库表

 

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.11 sec)

 

1.4记录主库信息

 

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000002 |     1551 |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.02 sec)

 

要记住file和position.

1.5逻辑快照

(注:如2台DB为新安装,没数据同步,可略过)

按需要同步全库或几个DB(重复dump单个DB).

 

mysqldump --master-data -uroot -p -R --all-databases  >master_repl.sql   #全库

mysqldump --master-data -uroot -p -R testdb >master_repl.sql   #单个数据库.

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

 

2.Slave上的配置

2.1 slave上的my.cnf

 

[mysqld]

server-id=2       #不同id

log-bin=mysql-bin          #binlog文件

binlog_format=mixed                  #binlog日志格式

 

2.2 授权复制专用用户

 

mysql>grant replication slave,replication client on *.* to repl@'10.15.%'  identified by 'Mysql123!'; 

mysql>flush privileges;

 

2.3导入主库数据

 

mysql -uroot -p -A <master_repl.sql                                 #针对主库全库导出

mysql -uroot -p -A test_for_repl <master_repl.sql           #相应数据库名需提前创建

 

2.4change master 同步

 

mysql> change master to master_host='10.15.107.177',master_user='repl',master_password='Mysql123!',MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=1551;

mysql> start slave;

Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G

*************************** 1. row ***************************

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

                   ……           

1 row in set (0.00 sec)

 

 

如果2台mysql是克隆出来,出现如下错误:

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

改文件或删除 /data/mysql5/auto.cnf,再重启mysql :

vi /data/mysql5/auto.cnf

[auto]

server-uuid=6ab71063-60f9-11e5-85e1-000c292a1fc5.

2.5为主库准备

 

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

mysql>  show master status;

+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000002 |     1762 |              |                  |                   |

1 row in set (0.00 sec)

mysql> unlock table;

Query OK, 0 rows affected (0.00 sec)

 

3.master的互为主从

 

mysql> change master to master_host='10.15.107.171',master_user='repl',master_password='Mysql123!',MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=1762;

Query OK, 0 rows affected, 2 warnings (0.07 sec)

mysql> start slave;

Query OK, 0 rows affected (0.08 sec)

mysql> show slave status\G

*************************** 1. row ***************************

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

 

4. MySQL同步测试

如上述均正确配置，现在任何一台MySQL上更新数据都会同步到另一台MySQL 

三.keepalived安装配置

3.1下载

keepalived-1.2.19.tar.gz

http://www.keepalived.org/software/keepalived-1.2.19.tar.gz

3.2安装

yum install  gcc gcc-c++ make openssl-devel kernel-devel ncurses-devel

tar zxf keepalived-1.2.19.tar.gz

./configure --disable-fwmark --prefix=/opt/keepalived

make && make install

mkdir /etc/keepalived     

cp /opt/keepalived/sbin/keepalived /usr/sbin/

cp /opt/keepalived/etc/sysconfig/keepalived /etc/sysconfig/

cp /opt/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/

cp /opt/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/ --add by lhf 

chmod a+x /etc/init.d/keepalived  #添加执行权限                                    

chkconfig keepalived on  #设置开机启动                                     

service keepalived start #启动                                         

service keepalived stop  #关闭                                         

service keepalived restart  #重启 

grant all on *.* to'root'@'10.15.%.%' identified by 'Mysql123!';

flush privileges;

3.3master的keepalived.conf

Interface的 配置查看路径/etc/sysconfig/network-scripts

/etc/keepalived/目录下keepalived.conf的配置,主从配置文件的区别priority，real_server不同。主机要配置nopreempt 

global_defs {

   notification_email {

     sfdevops@163.com

   }

   notification_email_from sfdevops@163.com

   smtp_server 127.0.0.1

   smtp_connect_timeout 30

   router_id db_master

}

vrrp_script chk_http_port {

  script " /etc/keepalived/mysql_keepalived.sh "                      #在这里添加脚本链接

   interval 3                       #脚本执行间隔

   weight -20                       #脚本结果导致的优先级变更

}

vrrp_instance VI_NODE {

    state BACKUP                    #都是BACKUP 

    interface eth0                   #注意接口

    virtual_router_id 100               #vrrp组名,2节点相同

    priority 110                   #优先级控制MS角色

                 advert_int 2

                 nopreempt                         #master必须不抢占

    authentication {

        auth_type PASS

        auth_pass 123456

    }

    track_script {

        chk_http_port                    #添加脚本执行

    }

    virtual_ipaddress {

        10.15.107.188                      #VIP

    }

}

virtual_server 10.15.107.188 3306 {                #VIP

          delay_loop 2

          lb_algo rr

          lb_kind DR

          persistence_timeout 60

          protocol TCP

   real_server 10.15.107.177 3306 {               #RS的ip地址

          weight 3

          TCP_CHECK {

        connect_timeout 10

        nb_get_retry 3

        delay_before_retry 3

        connect_port 3306

      }

   }

}

 

3.4slave的keepalived.conf

  --此处为/etc/keepalived/目录下keepalived.conf的配置

global_defs {

   notification_email {

     sfdevops@163.com

   }

   notification_email_from sfdevops@163.com

   smtp_server 127.0.0.1

   smtp_connect_timeout 30

   router_id db_master

}

vrrp_script chk_http_port {

  script "/etc/keepalived/mysql_keepalived.sh"                       #在这里添加脚本链接

   interval 3                        #脚本执行间隔

   weight  -20                        #脚本结果导致的优先级变更

}

vrrp_instance VI_NODE {

    state BACKUP

    interface eth0                     #注意接口

    virtual_router_id 100               #vrrp组名,2节点相同

    priority 100                     #优先级低

    advert_int 2

    authentication {

        auth_type PASS

        auth_pass 123456

    }

    track_script {

        chk_http_port                    #添加脚本执行

    }

    virtual_ipaddress {

        10.15.107.188                  #VIP地址

    }

}

 

virtual_server 10.15.107.188 3306 {                 #VIP

          delay_loop 2

          lb_algo rr

          lb_kind DR

          persistence_timeout 60

          protocol TCP

   real_server 10.15.107.171 3306 {               #对应RS的IP

          weight 3

      TCP_CHECK {

        connect_timeout 10

        nb_get_retry 3

        delay_before_retry 3

        connect_port 3306

      }

   }

} 

3.5健康检测脚本

cat /etc/keepalived/mysql_keepalived.sh

 

#!/bin/bash

if [ $(ps -C mysqld --no-header | wc -l) -eq 0 ]; then

     service mysqld  restart

fi

sleep 2

if [ $(ps -C mysqld --no-header | wc -l) -eq 0 ]; then

  /etc/init.d/keepalived stop

   rm -f /var/lock/subsys/keepalived

fi

 

 

chmod a+x /opt/mysql5/mysql_keepalived.sh           #必须授权才能执行成功.

3.6keepalived测试

1.监控keepalived日志

tailf  /var/log/messages

2.停掉master上的mysql或keepalived服务,都会自动切换VIP到另一台主机上.

停掉mysql时：

3.在局域网内ping vip是否能ping通

4.用mysql客户端连接ip为vip是否能连接上