keepalived+haproxy+mycat+mysql高可用搭建配制

程序员文章站 2024-01-06 14:38:58

mysql双主同步配置：mysql1服务器的my.cnf配置：server-id=1log_bin=bin_logbinlog-do-db=ry-vueslave_parallel_type='logical_clock' slave_parallel_workers=4 relay-log=relay-bin relay-log-index=relay-bin.index replicate-do-db=ry-vueauto_increment_offse......

mysql双主同步配置：

mysql1服务器的my.cnf配置：

server-id=1

log_bin=bin_log

binlog-do-db=ry-vue

slave_parallel_type='logical_clock'

slave_parallel_workers=4

relay-log=relay-bin

relay-log-index=relay-bin.index

replicate-do-db=ry-vue

auto_increment_offset = 1

auto_increment_increment = 2

mysql2服务器的my.ini配置：

server-id=2

log_bin=bin_log

binlog-do-db=ry-vue

slave_parallel_type='logical_clock'

slave_parallel_workers=4

relay-log=relay-bin

relay-log-index=relay-bin.index

replicate-do-db=ry-vue

auto_increment_offset = 2

auto_increment_increment = 2

进入mysql1：

CREATE USER '用户名'@'MySql2服务器ip' IDENTIFIED BY '密码';
GRANT REPLICATION SLAVE ON *.* TO '用户名'@'MySql2服务器ip';
flush privileges;
然后输入
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin_log.000001 | 790 | ry-vue | | |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
我们会看到：（记住 File 和 Position）
进入mysql2：
CHANGE MASTER TO
master_host = 'MySql1服务器ip',
master_user = '用户名',
master_password = '密码',
master_log_file = 'bin_log.000001',
master_log_pos = 790;

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.233.4
                  Master_User: chenshuang
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin_log.000001
          Read_Master_Log_Pos: 790
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 318
        Relay_Master_Log_File: bin_log.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: ry-vue

mycat配置：
schema.xml配置：
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">

   <schema name="ry-vue" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn">
       
       

       
       
       
       
       
       
       
       
       
   </schema>
   
   <dataNode name="dn" dataHost="localhost" database="ry-vue" />
   
   
   <dataHost name="localhost" maxCon="1000" minCon="10" balance="1"
       writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
       <heartbeat>select user()</heartbeat>
       
       <writeHost host="hostM1" url="192.168.233.4:3306" user="root"
           password="ROOT@localhost123456">
           
            <readHost host="hostS1" url="192.168.233.5:3306" user="root"
                 password="ROOT@localhost123456" />
       </writeHost>
       <writeHost host="hostS1" url="192.168.233.5:3306" user="root"
           password="ROOT@localhost123456" />
       
   </dataHost>
   

   
</mycat:schema>

server.xml配制：
<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://org.opencloudb/">
   <system>
   <property name="defaultSqlParser">druidparser</property>
       
   
   
       
       
       
       
       
       
       
   </system>
   <user name="root">
       <property name="password">123456</property>
       <property name="schemas">ry-vue</property>
   </user>

   
   
   

</mycat:server>

haproxy配制：
haproxy.cfg配制:
global
    log 127.0.0.1 local1
    maxconn 4096
    chroot /usr/local/haproxy
    user root
    group root
    daemon

defaults
    log global
    option dontlognull
    retries 3
    option redispatch
    maxconn 2000

    timeout connect 5000
    timeout client 50000
    timeout server 60000
listen admin_stats
    bind 192.168.233.4:48800
    mode http
    option httplog
    stats refresh 30s
    stats uri /admin-status
    stats realm Haproxy Manager
    stats auth admin:admin
    stats hide-version
listen mycat_service
    bind 192.168.233.4:8067
    mode tcp
    option tcplog
    option httpchk OPTIONS*HTTP/1.1\r\nHost:\www
    balance roundrobin
    server mycat_4 192.168.233.4:8066 check port 48700 inter 5s rise 2 fall 3
    server mycat_5 192.168.233.5:8066 check port 48700 inter 5s rise 2 fall 3
    timeout server 60000
listen mycat_admin
    bind 192.168.233.4:9067
    mode tcp
    option tcplog
    option httpchk OPTIONS*HTTP/1.1\r\nHost:\www
    balance roundrobin
    server mycat_4 192.168.233.4:9066 check port 48700 inter 5s rise 2 fall 3
    server mycat_5 192.168.233.5:9066 check port 48700 inter 5s rise 2 fall 3
    timeout server 60000

keepalived配制：
keepalived.conf配制：
! Configuration File for keepalived

global_defs {
   router_id haproxy_01
}

vrrp_script chk_haproxy {
    script "killall -0 haproxy"
    interval 3
}

vrrp_instance VI_1 {
    state MASTER
    interface ens33
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    track_script {
        chk_haproxy
    }
    virtual_ipaddress {
        192.168.233.6
    }
}

本文地址：https://blog.csdn.net/chenshuang_com/article/details/107388793

keepalived+haproxy+mycat+mysql高可用搭建配制