MySQL高可用方案——双主

MySQL的高可用方案有很多种，双主、MHA、MMM等等，这里只是写下最简单的双主这种高可用方案。

网站建设哪家好，找成都创新互联公司！专注于网页设计、网站建设、微信开发、小程序定制开发、集团企业网站建设等服务项目。为回馈新老客户创新互联还提供了嘉定免费建站欢迎大家使用！

一、配置MySQL互为主从

1、环境准备

系统	IP	主机名	服务
Centos 7.5	192.168.20.2	mysql01	MySQL+keepalived
Centos 7.5	192.168.20.3	mysql02	MySQL+keepalived

注：MySQL已部署完成，可参考博文Centos部署MySQL 5.7进行部署。

2、开启二进制日志及中继日志

#主机mysql01配置文件如下：
[root@mysql01 ~]# cat /etc/my.cnf 
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port=3306
server_id=1          #server_id必须唯一
socket=/usr/local/mysql/mysql.sock
log-error=/usr/local/mysql/data/mysqld.err
binlog_format = mixed     #指定二进制格式
log-bin=/usr/local/mysql/data/log_bin            #指定二进制日志文件
relay-log=/usr/local/mysql/data/relay-bin        #指定中继日志
relay-log-index=relay-bin.index
auto_increment_increment=2
auto_increment_offset=1

#主机mysql02配置文件如下：
[root@mysql02 ~]# cat /etc/my.cnf 
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port=3306
server_id=2 
socket=/usr/local/mysql/mysql.sock
log-error=/usr/local/mysql/data/mysqld.err
binlog_format = mixed
log-bin=/usr/local/mysql/data/log_bin
relay-log=/usr/local/mysql/data/relay-bin
relay-log-index=relay-bin.index
auto_increment_increment=2
auto_increment_offset=2

注：mysql01和mysql02只有server-id和auto_increment_offset不同

mysql中有自增长字段，在做数据库的主主同步时需要设置自增长的两个相关配置：auto_increment_offset和auto_increment_increment。 auto-increment-increment表示自增长字段每次递增的量，其默认值是1。它的值应设为整个结构中服务器的总数，我这里用到两台服务器，所以值设为2。 auto-increment-offset是用来设定数据库中自动增长的起点(即初始值)，因为这两能服务器都设定了一次自动增长值2，所以它们的起点必须得不同，这样才能避免两台服务器数据同步时出现主键冲突。

关于“binlog_format = mixed”配置项，是用来定义二进制日志的格式的，有以下三个值可选，如下：
STATEMENT：基于sql语句来记录二进制日志，比如有些sql语句可能会影响上百条数据的改动，那么也只是记录一条sql语句。优点：可以减少二进制日志的大小，减少日志写入的I/O量。缺点：需要进行数据恢复时，某些自定义的存储过程或函数可能会失效，数据可能无法恢复。
ROW：基于行来记录二进制日志，如果某一条SQL语句影响了多行数据，那么将会记录多条二进制日志，优点：可以通过二进制日志来精准的恢复数据。缺点：当发生变化的数据量较大时，会给磁盘I/O带来一定的压力。
mixed：基于混合模式来记录二进制日志。MySQL自行判断是基于行还是基于sql语句来记录日志，建议采用这种格式，如果基于sql语句来记录就可以精准记录数据的变化，那么就会基于sql语句，如果sql语句中包含存储过程或环境变量等，那么就会基于行来记录。

关于二进制日志的更多介绍，可以参考MySQL的官方文档。

注：可以在my.cnf文件中添加“binlog_do_db=数据库名”配置项（可以添加多个）来指定要同步的数据库

3、将mysql02设置为mysql01的从服务器

1）防火墙放行3306端口的流量（两台主机都需要放行3306端口，如果防火墙没有开启，则可忽略）

[root@mysql01 ~]# firewall-cmd --add-port=3306/tcp --permanent
[root@mysql01 ~]# firewall-cmd --reload

2）mysql01上创建授权用户

[root@mysql01 ~]# mysql -uroot -p123.com
mysql> grant replication slave on *.* to rep@'192.168.20.%' identified by '123.com';

3）查看mysql01的当前binlog状态信息

mysql> show master status\G
*************************** 1. row ***************************
             File: log_bin.000001     #这个值会用到
         Position: 609         #这个值会用到
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

4）在mysql02上指定mysql01为master，并开启slave功能

#指定master地址
mysql> change master to master_host='192.168.20.2',
    -> master_user='rep',
    -> master_password='123.com',
    -> master_log_file='log_bin.000001',    #必须和master上查看到的名字一样
    -> master_log_pos=609;      #同上，这个值也是在master上查看到的
#启动slave功能
mysql> start slave;
#确定配置成功
mysql> show slave status\G            #查看slave状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.20.2
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log_bin.000001
          Read_Master_Log_Pos: 609
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 318
        Relay_Master_Log_File: log_bin.000001
             Slave_IO_Running: Yes               # 这个值必须为Yes
            Slave_SQL_Running: Yes             # 这个值也必须为Yes
#只要上面两个值为yes，则表示主从没有问题，
#其中，IO线程是去master上面读取二进制日志到本地的中继日志中；
SQL线程是将本地的中继日志中的内容转换为sql语句并执行。

4、将mysql01设置为mysql02的从服务器

#主机mysql02上操作如下：
mysql> grant replication slave on *.* to rep@'192.168.20.%' identified by '123.com';
mysql> flush privileges;
mysql> show master status\G          #获取所需的file和Position
*************************** 1. row ***************************
             File: log_bin.000002
         Position: 609
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

#主机mysql01上操作如下：
#指定mysql02为master
mysql> change master to master_host='192.168.20.3',
    -> master_user='rep',
    -> master_password='123.com',
    -> master_log_file='log_bin.000002',
    -> master_log_pos=609;
mysql> start slave;       #启动slave
mysql> show slave status\G          #查看slave状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.20.3
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log_bin.000002
          Read_Master_Log_Pos: 609
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 318
        Relay_Master_Log_File: log_bin.000002
                #确保下面两个值为yes
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

5、测试主主同步

1）主机mysql01创建测试数据


mysql> create database test;
mysql> use test
mysql> create table t1(id int,name varchar(4));
mysql> insert into t1 values(1,'a'),(2,'b');
#确认mysql01的数据
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set (0.00 sec)

2）确认mysql02已经同步并插入新的数据

#以下操作在主机mysql02上进行
mysql> select * from t1;        #确定数据已同步
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
#插入数据测试
mysql> insert into t1 values(3,'c'),(4,'d');

mysql> select * from t1;        #确定最新数据
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+

3）确定mysql01可以同步mysql02的数据

#在mysql01上查询，是否同步mysql02主机上的数据
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
4 rows in set (0.00 sec)

至此，现在任何一台MySQL上更新数据都会同步到另一台MySQL，MySQL同步完成。

注：若主MySQL服务器已经存在，只是后期业务拓展才搭建从服务器，在配置数据库同步前应先将MySQL服务器的要同步的数据库拷贝到从服务器上（如先在主MySQL上备份数据库，再用备份再从MySQL服务器上恢复）。

二、配置keepalived高可用

1、安装keepalived

两个节点都需要执行以下命令，以便安装keepalived。

[root@mysql01 ~]# yum -y install keepalived

2、配置防火墙放行相关流量

注：两台主机都需要执行以下命令，以便放行相关流量。224.0.0.18是keepalived的组播地址，使用的是vrrp协议。

[root@mysql02 ~]# firewall-cmd --direct --permanent --add-rule ipv4 filter OUTPUT 0 --in-interface ens33 --destination 224.0.0.18 --protocol vrrp -j ACCEPT
[root@mysql02 ~]# firewall-cmd --direct --permanent --add-rule ipv4 filter INPUT 0 --in-interface ens33 --destination 224.0.0.18 --protocol vrrp -j ACCEPT
[root@mysql02 ~]# firewall-cmd --reload

2、修改主机mysql01的keepalived配置文件

[root@mysql01 ~]# cat /etc/keepalived/keepalived.conf 
! Configuration File for keepalived

global_defs {
   router_id mysql-01      #此处的值必须唯一
}

vrrp_instance VI_1 {
    state BACKUP       #指定角色为backup，两台MySQL服务器的角色均为backup，设置backup将根据优先级决定主从

    interface ens33      #指定承载虚拟IP的网卡
    virtual_router_id 51         #指定组，同一个集群内的值必须一致。并且不可和局域网中的其他组冲突
    priority 100          #优先级范围为：0~100
    advert_int 1     #发vrrp包的时间间隔，即多久进行一次master选举（可认为是健康检查时间间隔）
    nopreempt             #不抢占，即允许一个priority比较低的节点作为master，     
    authentication {          #认证区域
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {         #VIP区域，指定vip地址
        192.168.20.20
    }
}

virtual_server 192.168.20.20 3306 {    #设置虚拟服务器，需要指定虚拟IP地址和服务端口，IP与端口之间用空格隔开
    delay_loop 2         #设置运行情况检查时间，单位是秒
    lb_algo rr      #设置后端调度算法
    lb_kind DR    #设置lvs实现负载均衡的机制，有NAT、TUN、DR三个模式，DR模式效率最高
    persistence_timeout 60     #会话保持时间，单位是秒
    protocol TCP     #指定转发协议类型，有TCP和UDP两种

    real_server 192.168.20.2 3306 {          #配置服务节点，这里指定的也就是本机的真实IP
        weight 1     #设置权重
    notify_down /etc/keepalived/bin/mysql.sh    #检测到real_server的MySQL服务宕机后执行的脚本。
    TCP_CHECK {
        connect_port 3306    #健康检查端口
        connect_timeout 3       #连接超时时间
        retry 3    #重试次数
        delay_before_retry 3      #重连间隔时间
     }
   }
}
#准备指定的脚本
[root@mysql01 keepalived]# pwd
/etc/keepalived
[root@mysql01 keepalived]# mkdir bin
[root@mysql01 keepalived]# vim bin/mysql.sh
#!/bin/bash
pkill keepalived    #停止keepalived服务
[root@mysql01 keepalived]# chmod +x bin/mysql.sh    #赋予脚本执行权限
[root@mysql01 ~]# systemctl start keepalived        #启动keepalived服务
#确定ens33网卡有虚拟Ip

[root@mysql01 ~]# ip a show ens33    #必须使用ip a命令才可以查看到，ifconfig命令查看不到
2: ens33:  mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:c0:39:80 brd ff:ff:ff:ff:ff:ff
    inet 192.168.20.2/24 brd 192.168.20.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.20.20/32 scope global ens33     #可以看到指定的VIP已经绑定到ens33上
       valid_lft forever preferred_lft forever
    inet6 fe80::659e:9312:318a:e52b/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever

#将keepalived的配置文件发送到mysql02主机上
[root@mysql01 ~]# scp /etc/keepalived/keepalived.conf root@192.168.20.3:/etc/keepalived/

3、修改主机mysql02的keepalived配置文件

#修改msyql01发送来的配置文件
[root@mysql02 keepalived]# cat /etc/keepalived/keepalived.conf 
! Configuration File for keepalived

global_defs {
   router_id mysql-02         #更改router_id，此处在热备组中必须要唯一
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 90             #更改优先级
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.20.20
    }
}

virtual_server 192.168.20.20 3306 {
    delay_loop 2
    lb_algo rr
    lb_kind DR
    persistence_timeout 60
    protocol TCP

    real_server 192.168.20.3 3306 {        #更改为本机的IP地址及监听端口
        weight 1
    notify_down /etc/keepalived/bin/mysql.sh
    TCP_CHECK {
        connect_port 3306
        connect_timeout 3
        retry 3
        delay_before_retry 3
     }
   }
}
#准备所需脚本
[root@mysql01 keepalived]# pwd
/etc/keepalived
[root@mysql02 keepalived]# mkdir bin
[root@mysql02 keepalived]# vim bin/mysql.sh
#!/bin/bash
pkill keepalived
[root@mysql02 keepalived]# chmod +x bin/mysql.sh 
#启动keepalived
[root@mysql02 ~]# systemctl start keepalived

至此，即可实现MySQL的双主效果（只要VIP所在的节点，MySQL服务端口无法连接，那么VIP将切换至另一台节点，即使宕机的mysql服务器恢复，也不会对VIP进行抢占）。虽然有两台MySQL数据库，但是其使用keepalived提供的虚拟IP地址来对外提供服务，不管这个虚拟Ip地址落在哪台服务器上，都可以保证数据的一致性，因为它们互为主从，并且keepalived的状态都为backup，也设置了不抢占（减少VIP的切换次数），这样可以大大的避免keepalived的脑裂问题。

分享标题：MySQL高可用方案——双主
文章分享：http://chengdu.cdxwcx.cn/article/jesccj.html

甜橘子，专注成都网站制作网站设计与营销型网站建设与优化

首页

网站建设

网站制作案例

解决方案

网站设计报价

网站制作动态

关于我们

联系我们

成都网站建设设计将想法与焦点和您一起共享

MySQL高可用方案——双主

一、配置MySQL互为主从

1、环境准备

2、开启二进制日志及中继日志

3、将mysql02设置为mysql01的从服务器

1）防火墙放行3306端口的流量（两台主机都需要放行3306端口，如果防火墙没有开启，则可忽略）

2）mysql01上创建授权用户

3）查看mysql01的当前binlog状态信息

4）在mysql02上指定mysql01为master，并开启slave功能

4、将mysql01设置为mysql02的从服务器

5、测试主主同步

1）主机mysql01创建测试数据

2）确认mysql02已经同步并插入新的数据

3）确定mysql01可以同步mysql02的数据

二、配置keepalived高可用

1、安装keepalived

2、配置防火墙放行相关流量

2、修改主机mysql01的keepalived配置文件

3、修改主机mysql02的keepalived配置文件

其他资讯

驱动备份-创新互联

默认监控key与中文释义-创新互联

指针c语言c语言中*p什么意思？-创新互联

网吧电脑主机想买一台组装电脑，有哪个平台比较靠谱？-创新互联

jQuery实现动态生成表格并为行绑定单击变色动作的方法-创新互联

甜橘子，专注成都网站制作网站设计与营销型网站建设与优化

成都网站建设设计 将想法与焦点和您一起共享

MySQL高可用方案——双主

一、配置MySQL互为主从

1、环境准备

2、开启二进制日志及中继日志

3、将mysql02设置为mysql01的从服务器

1）防火墙放行3306端口的流量（两台主机都需要放行3306端口，如果防火墙没有开启，则可忽略）

2）mysql01上创建授权用户

3）查看mysql01的当前binlog状态信息

4）在mysql02上指定mysql01为master，并开启slave功能

4、将mysql01设置为mysql02的从服务器

5、测试主主同步

1）主机mysql01创建测试数据

2）确认mysql02已经同步并插入新的数据

3）确定mysql01可以同步mysql02的数据

二、配置keepalived高可用

1、安装keepalived

2、配置防火墙放行相关流量

2、修改主机mysql01的keepalived配置文件

3、修改主机mysql02的keepalived配置文件

其他资讯

驱动备份-创新互联

默认监控key与中文释义-创新互联

指针c语言c语言中*p什么意思？-创新互联

网吧电脑主机想买一台组装电脑，有哪个平台比较靠谱？-创新互联

jQuery实现动态生成表格并为行绑定单击变色动作的方法-创新互联

成都网站建设设计将想法与焦点和您一起共享