十七、MySQL--高可用+读写分离+基于gtid+半同步的主从复制集群

目录

目录

项目布局:

项目名称：

        搭建一个高可用基于binlog+gtid实现读写分离半同步主从复制

项目环境：

        2台MySQL服务器：

        2台中间件服务器（不需要安装MySQL）：

        1台client客服机（需要安装MySQL，用来模拟访问）：

项目步骤：

        1.先将搭建好2台MySQL服务器上的主从复制

        （默认2台机器MySQL通过备份使数据一致）

①、在master上的/etc/my.cnf文件内配置：

②、在slave上的/etc/my.cnf文件内配置：

③、在master上创建一个给slave使用的授权账号

④、在slave上的MySQL内修改配置（失败的话stop slave再试试）

⑤、依然在slave上输入start slave开启slave，输入show slave status\G文本查看

⑥、验证主从服务是否生效

⑦、验证半同步是否生效

        2.配置2台中间件服务器的mysqlrouter实现读写分离功能

        （默认都安装好了mysqlrouter）

①、配置MASTER上的/etc/mysqlrouter/mysqlrouter.conf

②、配置BACKUP上的/etc/mysqlrouter/mysqlrouter.conf和MASTER一样的配置

③、验证是否生效

        3.配置两台中间件的高可用功能keepalived

        （默认都安装好了keepalived）

①、配置第一个实例vip

②、配置第二实例vip

        验证是否生效

         4.集群搭建成功

---

---

项目布局:

项目名称：

        搭建一个高可用基于binlog+gtid实现读写分离半同步主从复制

项目环境：

        2台MySQL服务器：

        master：192.168.2.129  CentOS Linux release 7.9.2009 (Core)、MySQL5.7.34

        slave：192.168.2.204 CentOS Linux release 7.9.2009 (Core)、MySQL5.7.34

        2台中间件服务器（不需要安装MySQL）：

        master：192.168.2.100 CentOS Linux release 7.9.2009 (Core)、MySQL Router  Ver 8.0.28 for Linux on x86_64 (MySQL Community - GPL)、Keepalived v1.3.5 (03/19,2017)

        slave：192.168.126 CentOS Linux release 7.9.2009 (Core)、MySQL Router  Ver 8.0.28 for Linux on x86_64 (MySQL Community - GPL)、Keepalived v1.3.5 (03/19,2017)

        1台client客服机（需要安装MySQL，用来模拟访问）：

         client：192.168.2.128 CentOS Linux release 7.9.2009 (Core)、MySQL5.7.34

项目步骤：

        1.先将搭建好2台MySQL服务器上的主从复制

        （默认2台机器MySQL通过备份使数据一致）

①、在master上的/etc/my.cnf文件内配置：

[mysqld]
socket=/data/mysql/mysql.sock
log_bin #启动数据库更新二进制日志记录，日志文件名前缀为默认，可自己设置
server_id = 1  #主机为1，唯一标识
gtid-mode=on #主库生成的是GTID事务，从库也只能是GTID事务。为off则为匿名事务
enforce-gtid-consistency=on#只允许执行可以使用 GTID 安全记录的语句来强制执行 GTID 一致性
rpl_semi_sync_master_enabled=1#打开半同步模式
rpl_semi_sync_master_timeout=1000 #设置超时时间为1s，超过1s切换为异步模式
port = 3306 #MySQL端口
open_files_limit = 8192 #最大并发量
innodb_buffer_pool_size = 512M #innodb缓存池大小
character-set-server=utf8 #字符集格式

#配置后记得重启mysql服务，使其生效

②、在slave上的/etc/my.cnf文件内配置：

[mysqld]
socket=/data/mysql/mysql.sock
log_bin #开启二进制日志
server_id = 2 #主机为2，唯一标识
gtid-mode=on #修改匿名事务为GTID事务
log_slave_updates=on #从服务器从主服务器拿的二进制日志，更新到自己的二进制日志
enforce-gtid-consistency=on #一致性
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8

#配置后记得重启mysql服务，使其生效

③、在master上创建一个给slave使用的授权账号

grant all on *.* to 'sc'@'%' identified by '123456'

④、在slave上的MySQL内修改配置（失败的话stop slave再试试）

CHANGE MASTER TO MASTER_HOST='192.168.2.129', #填你自己的masterIP地址

MASTER_USER='sc', #刚才授权的账户

MASTER_PASSWORD='123456', #账户的密码

MASTER_PORT=3306,#端口号

MASTER_AUTO_POSITION = 1; #gtid从1开始，不需要修改

⑤、依然在slave上输入start slave开启slave，输入show slave status\G文本查看

sc@(none) 23:57  mysql>show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.2.129
                  Master_User: sc
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000007
          Read_Master_Log_Pos: 390
               Relay_Log_File: slave-relay-bin.000003
                Relay_Log_Pos: 525
        Relay_Master_Log_File: master-bin.000007
             Slave_IO_Running: Yes  #
            Slave_SQL_Running: Yes #这两行都为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: 390
              Relay_Log_Space: 947
              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: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 45f9f58e-8a77-11ec-a98d-000c293c0ce3
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 45f9f58e-8a77-11ec-a98d-000c293c0ce3:5
            Executed_Gtid_Set: 45f9f58e-8a77-11ec-a98d-000c293c0ce3:1-5,
89592241-8a7e-11ec-bd5b-000c29248fa7:1-3
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

⑥、验证主从服务是否生效

在master上创建个库，在slave上查看也能看到即为成功

⑦、验证半同步是否生效

首先在slave上输入stop slave，然后再master上创建个库，如果会卡1s后恢复正常即为生效，再次开启半同步只需要在slave上输入start slave。

        2.配置2台中间件服务器的mysqlrouter实现读写分离功能

        （默认都安装好了mysqlrouter）

①、配置MASTER上的/etc/mysqlrouter/mysqlrouter.conf

bind_address = 0.0.0.0 #本机上的所有IPV4地址都可访问
bind_port = 7001 #端口
mode = read-write #读写功能都有
destinations = 192.168.2.129:3306 #目的IP为master机的3306端口
max_connections = 65535
max_connect_errors = 100
client_connect_timeout = 9
[routing:read_only]
bind_address = 0.0.0.0 #本机上的所有IPV4地址都可访问
bind_port = 7002 #端口
mode = read-only  #只读
destinations = 192.168.2.204:3306 #目的IP为slave的3306端口
max_connections = 65535
max_connect_errors = 100
client_connect_timeout = 9

#刷新服务使其配置生效

②、配置BACKUP上的/etc/mysqlrouter/mysqlrouter.conf和MASTER一样的配置

bind_address = 0.0.0.0
bind_port = 7001
mode = read-write
destinations = 192.168.2.129:3306
max_connections = 65535
max_connect_errors = 100
client_connect_timeout = 9
[routing:read_only]
bind_address = 0.0.0.0
bind_port = 7002
mode = read-only
destinations = 192.168.2.204:3306
max_connections = 65535
max_connect_errors = 100
client_connect_timeout = 9

刷新服务使其配置生效

③、验证是否生效

使用client机器(IP:192.168.2.128)： 

[root@sc-mysql ~]# mysql -h 192.168.2.100 -P 7001 -usc -p'123456'

[root@sc-mysql ~]# mysql -h 192.168.2.100 -P 7002 -usc -p'123456'

#输入show processlist；显示Host都为中间件MASTER的IP地址即为成功

[root@sc-mysql ~]# mysql -h 192.168.2.126 -P 7001 -usc -p'123456'

[root@sc-mysql ~]# mysql -h 192.168.2.126 -P 7002 -usc -p'123456'

#输入show processlist；显示Host都为中间件BACKUP的IP地址即为成功
#我在master和slave都创建了sc账户且密码都为'123456'，可以从任意IP连过来。

        3.配置两台中间件的高可用功能keepalived

        （默认都安装好了keepalived）

①、配置第一个实例vip

        MASTER：

global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   router_id LVS_DEVEL
   vrrp_skip_check_adv_addr
#   vrrp_strict  #注释掉这一个参数
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}

vrrp_instance VI_1 {
    state MASTER #第一个实例配置为主
    interface ens33 #绑定网卡，使用ip add查看网卡
    virtual_router_id 151 #唯一vrid
    priority 120 #权限设置高一些
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111 #默认密码即可
    }
    virtual_ipaddress {
        192.168.2.16 #vip虚拟IP
}
}

#之后的行全部删掉，我们只使用keepalived的高可用功能，并不需要keepalived的负载均衡

#记得刷新keepalived服务，使配置生效

        BACKUP：

global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   router_id LVS_DEVEL
   vrrp_skip_check_adv_addr
#   vrrp_strict
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}

vrrp_instance VI_1 {
    state BACKUP #设置为备用
    interface ens33 #网卡名
    virtual_router_id 151 #和MASTER须一致，不然会产生脑裂现象
    priority 100 #权限比MASTER低
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111 #密码默认即可
    }
    virtual_ipaddress {
        192.168.2.16 #和MASTER同一个vip
}
}

#之后的行全部删掉，我们只使用keepalived的高可用功能，并不需要keepalived的负载均衡

#记得刷新keepalived服务，使配置生效

        验证是否生效

        在MASTER端输入ip add       

        在BACKUP端输入ip add

#在MASTER端出现，在BACKUP不出现即为正常，同时出现称之为脑裂现象，但影响不大

②、配置第二实例vip

        MASTER：在最后面添加实例vrrp_instance VI_2

        vrrp_instance VI_2 {
    state BACKUP #设置为备用
    interface ens33
    virtual_router_id 153 #需与实例1的151不同 
    priority 100 #优先级设为100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.2.18 3 #需与实例1的vip区别开来，单独设置
}
}

#刷新keepalived服务，使配置生效

        BACKUP:在最后面添加实例vrrp_instance VI_2

vrrp_instance VI_2 {
    state MASTER #设置为主
    interface ens33
    virtual_router_id 153 #与MASTER机器一致
    priority 120 #优先级设为120，使这台BACKUP机器作主
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.2.18 #和MASTER机器保持一致
}
}

#刷新keepalived服务，使配置生效

        验证是否生效

        在BACKUP机器上看到

        在MASTER机器还是之前

 

         4.集群搭建成功

client使用vip1连接MASTER的7001端口，达到连接master的MySQL数据库效果

client使用vip1连接MASTER的7002端口，达到连接slave的MySQL数据库效果

client使用vip2来连接BACKUP的7001端口，达到连接master的MySQL数据库效果

client使用vip2来连接BACKUP的7002端口，达到连接slave的MySQL数据库效果 

 #判断连接的是master还是slave可以看command中是否有一条Binlog Dump GTID的描述，有的为master，没有则为slave。