mysql集群搭建（PXC）

忘是亡心i 2022-02-01 13:57 325阅读 0赞

基于虚拟机搭建mysql集群，使用docker进行安装，并对docker-compose方式进行了尝试。

0、tips

## 安装docker
    yum install -y docker 
    ## 安装docker-compose
    curl -L "https://github.com/docker/compose/releases/download/1.23.1/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose
    chmod +x /usr/local/bin/docker-compose
    ## docker-compose 指定db.yml 启动 可以单独启动一个节点
    docker-compose -f db.yml up db1
    ## 安装iptables
    yum install -y iptables iptables-services
    ## docker容器内时区
    date +"%Z %z" 或者 date -R
    ## iptables 动态开放端口
    iptables -I INPUT -p tcp --dport 5306 -j ACCEPT
    ## 保存iptables 规则
    service iptables save
    ## 查看ipables状态
    service iptables status
    ## 查看规则
    iptables --n -L
    #＃docker镜像准备,拉取pxc以及haproxy
    docker pull percona/percona-xtradb-cluster 
    # 镜像重命名
    docker tag percona/percona-xtradb-cluster pxc 
    ## 拉取haproxy镜像
    docker pull haproxy

1、安装pxc

### 创建网络
    docker network create --subnet=172.18.0.0/16 net1
    # 创建5个数据卷 
    docker volume create --name v1 
    docker volume create --name v2 
    docker volume create --name v3 
    docker volume create --name v4 
    docker volume create --name v5
    查看数据卷v1
    docker inspect v1
    删除数据卷
    docker rm -v v1
    ## 停止容器时需要先删除数据卷
    
    ## 启动数据节点 
     #创建第1个MySQL节点 
     docker run -d -p 30001:3306 -e MYSQL_ROOT_PASSWORD=db123456 -e CLUSTER_NAME=JWSPXC -e XTRABACKUP_PASSWORD=db123456 -v v1:/var/lib/mysql -v backup:/data --privileged --name=db1 --net=net1 --ip 172.18.10.11 pxc 
     #创建第2个MySQL节点 
     docker run -d -p 30002:3306 -e MYSQL_ROOT_PASSWORD=db123456 -e CLUSTER_NAME=JWSPXC -e XTRABACKUP_PASSWORD=db123456 -e CLUSTER_JOIN=db1 -v v2:/var/lib/mysql -v backup:/data --privileged --name=db2 --net=net1 --ip 172.18.10.12 pxc 
     #创建第3个MySQL节点 
     docker run -d -p 30003:3306 -e MYSQL_ROOT_PASSWORD=db123456 -e CLUSTER_NAME=JWSPXC -e XTRABACKUP_PASSWORD=db123456 -e CLUSTER_JOIN=db1 -v v3:/var/lib/mysql -v backup:/data --privileged --name=db3 --net=net1 --ip 172.18.10.13 pxc 
     #创建第4个MySQL节点 
     docker run -d -p 30004:3306 -e MYSQL_ROOT_PASSWORD=db123456 -e CLUSTER_NAME=JWSPXC -e XTRABACKUP_PASSWORD=db123456 -e CLUSTER_JOIN=db1 -v v4:/var/lib/mysql -v backup:/data --privileged --name=db4 --net=net1 --ip 172.18.10.14 pxc 
     #创建第5个MySQL节点 
     docker run -d -p 30005:3306 -e MYSQL_ROOT_PASSWORD=db123456 -e CLUSTER_NAME=JWSPXC -e XTRABACKUP_PASSWORD=db123456 -e CLUSTER_JOIN=db1 -v v5:/var/lib/mysql -v backup:/data --privileged --name=db5 --net=net1 --ip 172.18.10.15 pxc
     
     ## 连接db1 执行，用于ha监控mysql集群使用，查看健康状况
     create user 'haproxy'@'%' identified by '';
     flush privileges;

2、安装haproxy

2.1 制作haproxy:v0.1 (文件名为 Dockerfile）

## dockefile 
    FROM haproxy
    MAINTAINER destiny <xieyue86@163.com>
    
    ENV LANG en_US.utf8
    RUN cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
    RUN apt-get update && apt-get install keepalived -y
    RUN apt-get install net-tools -y && apt-get install iputils-ping -y && apt-get install vim -y

## 执行命令
    docker build -t haproxy:v0.1 .

2.2 创建haproxy

### haproxy.cfg keepalived.conf 配置文件如下
     # 创建第1个Haproxy负载均衡服务器 
     docker run -it -d -p 31011:8888 -p 31021:3306 -v /root/soft/haproxy/haproxy.cfg:/usr/local/etc/haproxy/haproxy.cfg --name h1 --privileged --net=net1 --ip 172.18.10.21 haproxy:v0.1 
     # 创建第2个Haproxy负载均衡服务器 
     docker run -it -d -p 31012:8888 -p 31022:3306 -v /root/soft/haproxy/haproxy.cfg:/usr/local/etc/haproxy/haproxy.cfg --name h2 --privileged --net=net1 --ip 172.18.10.22 haproxy:v0.1  
    ## 执行
    docker cp keepalived.conf h1:/etc/keepalived/ 
    docker cp keepalived.conf h2:/etc/keepalived/ 
    # 进入h1容器，启动Haproxy 
     docker exec -it h1 bash 
     service keepalived start
    # 进入h2容器，启动Haproxy 
     docker exec -it h2 bash 
     service keepalived start

haproxy.cfg

global 
       #工作目录 
       chroot /usr/local/etc/haproxy 
       #日志文件，使用rsyslog服务中local5日志设备（/var/log/local5），等级info 
       log 127.0.0.1 local5 info 
       #守护进程运行 
       daemon 
    defaults 
       log global 
       mode http 
       #日志格式 
       option httplog 
       #日志中不记录负载均衡的心跳检测记录 
       option dontlognull 
       #连接超时（毫秒） 
       timeout connect 5000 
       #客户端超时（毫秒） 
       timeout client 50000 
       #服务器超时（毫秒） 
       timeout server 50000 
    #监控界面   
    listen admin_stats 
       #监控界面的访问的IP和端口 
       bind 0.0.0.0:8888 
       #访问协议 
       mode http 
       #URI相对地址 
       stats uri /dbs 
       #统计报告格式 
       stats realm Global\ statistics 
       #登陆帐户信息 
       stats auth admin:ha123456 
       #数据库负载均衡 
    listen proxy-mysql 
       #访问的IP和端口 
       bind 0.0.0.0:3306 
       #网络协议 
       mode tcp 
       #负载均衡算法（轮询算法） 
       #轮询算法：roundrobin 
       #权重算法：static-rr 
       #最少连接算法：leastconn 
       #请求源IP算法：source 
       balance roundrobin 
       #日志格式 
       option tcplog 
       #在MySQL中创建一个没有权限的haproxy用户，密码为空。Haproxy使用这个账户对MySQL数据库心跳检测 
       #create user 'haproxy'@'%' identified by '' 
       option mysql-check user haproxy 
       server MySQL_1 172.18.10.11:3306 check weight 1 maxconn 2000 
       server MySQL_2 172.18.10.12:3306 check weight 1 maxconn 2000 
       server MySQL_3 172.18.10.13:3306 check weight 1 maxconn 2000 
       server MySQL_4 172.18.10.14:3306 check weight 1 maxconn 2000 
       server MySQL_5 172.18.10.15:3306 check weight 1 maxconn 2000 
       #使用keepalive检测死链 
       option tcpka
       ##启动　haproxy
      ##  haproxy -f /usr/local/etc/haproxy/haproxy.cfg

keepalived.conf

vrrp_instance VI_1 {
        state  MASTER
        interface  eth0
        virtual_router_id  51
        priority  100
        advert_int  1
        authentication {
            auth_type  PASS
            auth_pass  123456
        }
        virtual_ipaddress {
            172.18.10.5
        }
    }

haproxy.conf

stream { 
        upstream ha8888 { 
            server 172.18.10.5:8888; 
        } 
        
        upstream db3306 { 
            server 172.18.10.5:3306; 
        } 
        server { 
            listen 8888; 
            proxy_connect_timeout 5s; 
            proxy_timeout 5s; 
            proxy_pass ha8888; 
        } 
        server { 
            listen 3306; 
            proxy_connect_timeout 5s; 
            proxy_timeout 5s; 
            proxy_pass db3306; 
        } 
    }

3. nginx 安装

1.下载nginx
    cd ~ && wget http://nginx.org/download/nginx-1.12.2.tar.gz
    tar -xvf  nginx-1.12.2.tar.gz
    编译之前执行
    yum -y install make zlib zlib-devel gcc-c++ libtool  openssl openssl-devel
    yum -y install perl-devel perl-ExtUtils-Embed
    yum -y install gperftools 
    yum -y install GeoIP GeoIP-devel GeoIP-data
    yum -y install libxml2 libxml2-devel libxslt-devel
    2.编译nginx
    cd nginx-1.12.2
    ./configure  --with-select_module --with-poll_module --with-threads --with-file-aio --with-http_ssl_module --with-http_realip_module  --with-http_addition_module --with-http_xslt_module   --with-http_xslt_module=dynamic --with-http_geoip_module  --with-http_geoip_module=dynamic  --with-http_gunzip_module  --with-http_gzip_static_module  --with-stream --with-pcre --with-debug
    
    3.默认安装目录为 /usr/local/nginx
    cd /usr/local/nginx

4.生成私钥和证书

### step1 生成证书私钥
    openssl genrsa -des3 -out server.key 1024    
    ### step2 生成证书私钥
    openssl req -new -key server.key -out server.csr
    cp server.key server.key.org
    openssl rsa -in server.key.org -out server.key
    ### step3 签发数字证书，默认格式PEM
    #也可以openssl req -new -x509 -key cert.key -out cert.pem -days 3650 直接生成自签名证书
    openssl x509 -req -days 3650 -in server.csr  -signkey server.key  -out server.crt

nginx.conf 配置文件 包含https stream转发模块

## 定义nginx的用户和用户组
    user root;
    ## nginx 进程数，设置为cpu总核数
    worker_processes  1;
    ## 日志类型
    #error_log  logs/error.log;
    #error_log  logs/error.log  notice;
    #error_log  logs/error.log  info;
    ## 进程文件
    pid        logs/nginx.pid;
    ## 
    worker_rlimit_nofile 65535;
    events {
        worker_connections  1024;
        use epoll;
    }
    http {
        include       mime.types;
        default_type  application/octet-stream;
    
        #log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
        #                  '$status $body_bytes_sent "$http_referer" '
        #                  '"$http_user_agent" "$http_x_forwarded_for"';
        
        #access_log  logs/access.log  main;
        limit_conn_zone $binary_remote_addr zone=perip：10m; 
        limit_conn_zone $server_name zone=perserver：10m 
        sendfile        on;
        #tcp_nopush     on;
        #keepalive_timeout  0;
        keepalive_timeout  65;
        #gzip  on;
        server {
            listen       80;
            server_name  localhost;
            #charset koi8-r;
            #单个客户端ip与服务器的连接数．
            limit_conn perip 10;  
            ＃限制与服务器的总连接数
            limit_conn perserver 100;　　
            #access_log  logs/host.access.log  main;
            location / {
                root   html;
                index  index.html index.htm;
            }
            ## 静态文件分离
            location ~.*\.(js|css|ico|png|jpg|eot|svg|ttf|woff){
                root /home/app;
                expires 30d;
            }
    
            #error_page  404              /404.html;
            # redirect server error pages to the static page /50x.html
            #
            error_page   500 502 503 504  /50x.html;
            location = /50x.html {
                root   html;
            }
    
            # proxy the PHP scripts to Apache listening on 127.0.0.1:80
            #
            #location ~ \.php$ {
            #    proxy_pass   http://127.0.0.1;
            #}
    
            # pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000
            #
            #location ~ \.php$ {
            #    root           html;
            #    fastcgi_pass   127.0.0.1:9000;
            #    fastcgi_index  index.php;
            #    fastcgi_param  SCRIPT_FILENAME  /scripts$fastcgi_script_name;
            #    include        fastcgi_params;
            #}
    
            # deny access to .htaccess files, if Apache's document root
            # concurs with nginx's one
            #
            #location ~ /\.ht {
            #    deny  all;
            #}
        }
    
    
        # another virtual host using mix of IP-, name-, and port-based configuration
        #
        #server {
        #    listen       8000;
        #    listen       somename:8080;
        #    server_name  somename  alias  another.alias;
    
        #    location / {
        #        root   html;
        #        index  index.html index.htm;
        #    }
        #}
    
    
        # HTTPS server
        server {
            listen       443 ssl;
            server_name  xieyue.destiny.com;  
            ssl_certificate      /usr/local/nginx/conf/ssl/server.crt;
            ssl_certificate_key  /usr/local/nginx/conf/ssl/server.key;
            ssl_session_cache    shared:SSL:1m;
            ssl_session_timeout  5m;
            ssl on;
            ssl_ciphers  HIGH:!aNULL:!MD5;
            ssl_prefer_server_ciphers  on;
            
            location / {
                root   html;
                index  index.html index.htm;
            }
            location /static/ {
                root /home/static/;
            }
        }
    }  
    
    stream {
    
        upstream db5306 {
            server 172.18.10.5:3306;
        }
    
        upstream ha8888 {
            server 172.18.10.5:8888;
        }
        
        server {
            listen 8888;
            proxy_connect_timeout 5s;
            proxy_timeout 5s;
            proxy_pass ha8888;
        }
    
        server {
            listen 3309;
            proxy_connect_timeout 5s;
            proxy_timeout 5s;
            proxy_pass db5306;          
    
        }
    }

### 5.docker-compose 安装 ###

step1: 
     mkdir  -p data/{v1,v2,v3,v4,v5,bakcup}
     chmod -R 777 data
    step2:
      data目录下创建配置文件

haproxy.cfg

global 
       #工作目录 
       chroot /usr/local/etc/haproxy 
       #日志文件，使用rsyslog服务中local5日志设备（/var/log/local5），等级info 
       log 127.0.0.1 local5 info 
       #守护进程运行 
       daemon 
    defaults 
       log global 
       mode http 
       #日志格式 
       option httplog 
       #日志中不记录负载均衡的心跳检测记录 
       option dontlognull 
       #连接超时（毫秒） 
       timeout connect 5000 
       #客户端超时（毫秒） 
       timeout client 50000 
       #服务器超时（毫秒） 
       timeout server 50000 
    #监控界面   
    listen admin_stats 
       #监控界面的访问的IP和端口 
       bind 0.0.0.0:8888 
       #访问协议 
       mode http 
       #URI相对地址 
       stats uri /dbs 
       #统计报告格式 
       stats realm Global\ statistics 
       #登陆帐户信息 
       stats auth admin:ha123456 
       #数据库负载均衡 
    listen proxy-mysql 
       #访问的IP和端口 
       bind 0.0.0.0:3306 
       #网络协议 
       mode tcp 
       #负载均衡算法（轮询算法） 
       #轮询算法：roundrobin 
       #权重算法：static-rr 
       #最少连接算法：leastconn 
       #请求源IP算法：source 
       balance roundrobin 
       #日志格式 
       option tcplog 
       #在MySQL中创建一个没有权限的haproxy用户，密码为空。Haproxy使用这个账户对MySQL数据库心跳检测 
       #create user 'haproxy'@'%' identified by '' 
       option mysql-check user haproxy 
       server MySQL_1 db1:3306 check weight 1 maxconn 2000 
       server MySQL_2 db2:3306 check weight 1 maxconn 2000 
       server MySQL_3 db3:3306 check weight 1 maxconn 2000 
       server MySQL_4 db4:3306 check weight 1 maxconn 2000 
       server MySQL_5 db5:3306 check weight 1 maxconn 2000 
       #使用keepalive检测死链 
       option tcpka

keepalived.conf

docker-ha.yml

version : '2'
    services:
      db1:
        container_name: db1
        image: pxc
        privileged: true
        networks:
          - net1
        environment:
          - "CLUSTER_NAME=JWSPXC"
          - "XTRABACKUP_PASSWORD=db123456"
          - "MYSQL_ROOT_PASSWORD=db123456"
          - "TZ=Asia/Shanghai"
        ports:
          - "30001:3306"
        volumes:
           - ./data/v1:/var/lib/mysql
           - ./data/backup:/data
      db2:
        container_name: db2
        image: pxc
        privileged: true
        networks:
          - net1
        environment:
          - "CLUSTER_NAME=JWSPXC"
          - "XTRABACKUP_PASSWORD=db123456"
          - "TZ=Asia/Shanghai"
          - "CLUSTER_JOIN=db1"
        ports:
          - "30002:3306"
        volumes:
           - ./data/v2:/var/lib/mysql
           - ./data/backup:/data
        depends_on:
           - db1
      db3:
        container_name: db3
        image: pxc
        privileged: true
        networks:
          - net1
        environment:
          - "CLUSTER_NAME=JWSPXC"
          - "XTRABACKUP_PASSWORD=db123456"
          - "TZ=Asia/Shanghai"
          - "CLUSTER_JOIN=db1"
        ports:
          - "30003:3306"
        volumes:
           - ./data/v3:/var/lib/mysql
           - ./data/backup:/data
        depends_on:
           - db1
      db4:
        container_name: db4
        image: pxc
        privileged: true
        networks:
          - net1
        environment:
          - "CLUSTER_NAME=JWSPXC"
          - "XTRABACKUP_PASSWORD=db123456"
          - "TZ=Asia/Shanghai"
          - "CLUSTER_JOIN=db1"
        ports:
          - "30004:3306"
        volumes:
           - ./data/v4:/var/lib/mysql
           - ./data/backup:/data
        depends_on:
           - db1
      db5:
        container_name: db5
        image: pxc
        privileged: true
        networks:
          - net1
        environment:
          - "CLUSTER_NAME=JWSPXC"
          - "XTRABACKUP_PASSWORD=db123456"
          - "TZ=Asia/Shanghai"
          - "CLUSTER_JOIN=db1"
        ports:
          - "30005:3306"
        volumes:
           - ./data/v5:/var/lib/mysql
           - ./data/backup:/data
        depends_on:
           - db1
      h1:
        container_name: h1
        image: haproxy:v0.1
        privileged: true
        networks:
          - net1
        ports:
          - "31011:8888"
          - "31021:3306"
        volumes:
           - ./data/haproxy.cfg:/usr/local/etc/haproxy/haproxy.cfg
           - ./data/keepalived.conf:/etc/keepalived/
        command: service keepalived start
    
      h2:
        container_name: h2
        image: haproxy:v0.1
        privileged: true
        networks:
          - net1
        ports:
          - "31012:8888"
          - "31022:3306"
        volumes:
           - ./data/haproxy.cfg:/usr/local/etc/haproxy/haproxy.cfg
           - ./data/keepalived.conf:/etc/keepalived/
        command: service keepalived start
    
    networks:
      net1:
        driver: bridge

execute

1、docker-compose -f docker-ha.yml up db1
    2、docker-compose -f docker-ha.yml up db2 db3 db4 db5
    3、docker-compose -f docker-ha.yml up h1 h2
    
    1执行完之后连接数据库创建用户后在执行2和3
    连接db1后执行
     create user 'haproxy'@'%' identified by '';
     flush privileges;