CentOS 7 安裝 Percona XtraDB Cluster

  • 預計安裝在 CentOS Linux release 7.7.1908 (Core) (PVE6 Template upgrade) 環境下
  • 配置 node 如下:
    • ct-percona1 192.168.11.252 (Master)
    • ct-percona2 192.168.11.202
yum install epel-release
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm
yum install percona-xtrabackup Percona-XtraDB-Cluster-server-56
  • 要開啟 firewalld 允許 3306, 4444, 4567, 4568
  • 要關閉 selinux
  • 第一台 mysql 要建立 sstuser 讓 my.cnf 內的 wsrep_sst_auth 可以正確設定
  • 調整 firewalld
    firewall-cmd --add-service=mysql
    firewall-cmd --add-port=4444/tcp 
    firewall-cmd --add-port=4567/tcp 
    firewall-cmd --add-port=4568/tcp 
    firewall-cmd --permanent --add-service=mysql
    firewall-cmd --permanent --add-port=4444/tcp 
    firewall-cmd --permanent --add-port=4567/tcp 
    firewall-cmd --permanent --add-port=4568/tcp 
      * 關閉 SELinux <code sh>
    vi /etc/selinux/config
    :
    SELINUX=disabled
    :

    更改後要重新開機才會生效

  • /etc/my.cnf@ct-percona1
    [mysqld]
    server_id=1
    wsrep_provider=/usr/lib64/libgalera_smm.so
    wsrep_cluster_address="gcomm://"
    wsrep_sst_auth=sstuser:**password**
    wsrep_provider_options="gcache.size=4G"
    wsrep_cluster_name=Percona
    wsrep_sst_method=xtrabackup-v2
    wsrep_node_name=db_01
    wsrep_slave_threads=4
    log_slave_updates
    innodb_locks_unsafe_for_binlog=1
    innodb_autoinc_lock_mode=2
    expire-logs-days = 30
    #max_binlog_files = 20
    :
    :
  • /etc/my.cnf@ct-percona2
    [mysqld]
    server_id=2
    wsrep_provider=/usr/lib64/libgalera_smm.so
    wsrep_cluster_address="gcomm://192.168.11.252"
    wsrep_sst_auth=sstuser:**password**
    wsrep_provider_options="gcache.size=4G"
    wsrep_cluster_name=Percona
    wsrep_sst_method=xtrabackup-v2
    wsrep_node_name=db_02
    wsrep_slave_threads=4
    log_slave_updates
    innodb_locks_unsafe_for_binlog=1
    innodb_autoinc_lock_mode=2
    expire-logs-days = 30
    #max_binlog_files = 20
    :
    :
  • 更改 mysql 內 root 密碼與建立 sstuser @ct-percona1
    /usr/bin/mysqladmin -u root password 'newpassword'
    mysql -u root -p
    CREATE USER 'sstuser'@'localhost' IDENTIFIED BY '**password**';
    GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
    FLUSH PRIVILEGES;
  • 第一次啟動重新開機會比較快
    service mysql restart
  • 再來就可以查看 /var/log/mysqld.log
  • 如果正常可以連入 mysql 命令列看到以下的狀況
    SHOW global STATUS LIKE 'wsrep_cluster_size';
    +--------------------+-------+
    | Variable_name      | Value |
    +--------------------+-------+
    | wsrep_cluster_size | 2     |
    +--------------------+-------+
    1 row in set (0.00 sec)
  • 依照前面步驟安裝與設定
  • /etc/my.cnf 更改, 可參考 /etc/my.cnf@ct-percona2, 主要修改是
    • server_id=3
    • wsrep_node_name=db_03
  • /etc/my.cnf@ct-percona3
    [mysqld]
    server_id=3
    wsrep_provider=/usr/lib64/libgalera_smm.so
    wsrep_cluster_address="gcomm://192.168.11.252"
    wsrep_sst_auth=sstuser:**password**
    wsrep_provider_options="gcache.size=4G"
    wsrep_cluster_name=Percona
    wsrep_sst_method=xtrabackup-v2
    wsrep_node_name=db_03
    wsrep_slave_threads=4
    log_slave_updates
    innodb_locks_unsafe_for_binlog=1
    innodb_autoinc_lock_mode=2
    expire-logs-days = 30
    #max_binlog_files = 20
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
  • 重新開機後, 觀察 ct-percona1, ct-percona2 的 mysqld.log 可看到 ct-percona3 加入的紀錄
  • 連入 mysql 命令列看到以下的狀況
    SHOW global STATUS LIKE 'wsrep_cluster_size';
    +--------------------+-------+
    | Variable_name      | Value |
    +--------------------+-------+
    | wsrep_cluster_size | 3     |
    +--------------------+-------+
    1 row in set (0.00 sec)
  • 使用 sysbench 來進行效能測試
    yum install sysbench
  • 連入 mysql 建立 sbtest 測試資料庫
    CREATE DATABASE sbtest;
  • 建立測試資料
    sysbench --test=oltp --db-driver=mysql --mysql-engine-trx=yes --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=**username** --mysql-password=**password** --oltp-auto-inc=off --test=/usr/share/doc/sysbench/tests/db/oltp.lua --oltp-table-size=1000000 prepare
  • 開始進行測試
    sysbench --test=oltp --db-driver=mysql --mysql-engine-trx=yes --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=**username** --mysql-password=**password** --oltp-auto-inc=off --test=/usr/share/doc/sysbench/tests/db/oltp.lua --oltp-table-size=100000 --num-threads=100 run
  • 跑出來的結果
    sysbench 0.5:  multi-threaded system evaluation benchmark
    
    Running the test with following options:
    Number of threads: 100
    Random number generator seed is 0 and will be ignored
    
    
    Threads started!
    
    OLTP test statistics:
        queries performed:
            read:                            140000
            write:                           40000
            other:                           20000
            total:                           200000
        transactions:                        10000  (50.21 per sec.)
        read/write requests:                 180000 (903.82 per sec.)
        other operations:                    20000  (100.42 per sec.)
        ignored errors:                      0      (0.00 per sec.)
        reconnects:                          0      (0.00 per sec.)
    
    General statistics:
        total time:                          199.1551s
        total number of events:              10000
        total time taken by event execution: 19819.8799s
        response time:
             min:                                117.05ms
             avg:                               1981.99ms
             max:                              10106.61ms
             approx.  95 percentile:            4014.08ms
    
    Threads fairness:
        events (avg/stddev):           100.0000/5.25
        execution time (avg/stddev):   198.1988/0.57
  • 清除測試資料
    sysbench --test=oltp --db-driver=mysql --mysql-engine-trx=yes --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=**username** --mysql-password=**password** --oltp-auto-inc=off --test=/usr/share/doc/sysbench/tests/db/oltp.lua --oltp-table-size=1000000 cleanup
  • 要開啟 iptables 允許 8080(haproxy stats), 9200(mysqlchk), 13306(haproxy-mysql)
  • 調整 iptables
    vi /etc/sysconfig/iptables
    :
    :
    -A INPUT -m state --state NEW -m tcp -p tcp --dport 4568 -j ACCEPT
    -A INPUT -m state --state NEW -m tcp -p tcp --dport 8080 -j ACCEPT
    -A INPUT -m state --state NEW -m tcp -p tcp --dport 9200 -j ACCEPT
    -A INPUT -m state --state NEW -m tcp -p tcp --dport 13306 -j ACCEPT
    -A INPUT -j REJECT --reject-with icmp-host-prohibited
    :
    service iptables reload
  • 安裝 haproxy
    yum -y install haproxy xinetd
  • 設定 mysqlchk Liston 9200
    vi /etc/services
    :
    sun-as-jpda     9191/udp                # Sun AppSvr JPDA
    mysqlchk        9200/tcp                # mysqlchk
    wap-wsp         9200/tcp                # WAP connectionless session service
    :
  • 啟動 xinetd
    service xinetd restart
    chkconfig xinetd on
  • 建立檢核帳號 clustercheckuser 與設定 mysqlchk
    mysql -u root -p

    只需要一台執行即可

    GRANT PROCESS ON *.* TO 'clustercheckuser'@'localhost' IDENTIFIED BY '**password**';
    FLUSH PRIVILEGES;
    vi /etc/xinetd.d/mysqlchk
    :
            server          = /usr/bin/clustercheck
            server_args     = clustercheckuser **password**
            log_on_failure  += USERID
    :
    service xinetd reload
  • 設定 haproxy log
    vi /etc/rsyslog.conf
    :
    # Save boot messages also to boot.log
    local7.*                                                /var/log/boot.log
    
    # HAProxy log
    local2.*                       /var/log/haproxy.log
    
    
    # ### begin forwarding rule ###
    :
    service rsyslog restart
  • 設定 haproxy Liston 13306 指向三台 Percona XC
    vi /etc/haproxy/haproxy.cfg
    :
    defaults
        mode                    http
        log                     global
        #option                  httplog
        option                  tcplog
        option                  dontlognull
        #option http-server-close
        #option forwardfor       except 127.0.0.0/8
        #option                  redispatch
        retries                 3
        #timeout http-request    10s
        timeout queue           1m
        timeout connect         10s
        timeout client          1m
        timeout server          1m
        #timeout http-keep-alive 10s
        timeout check           10s
        maxconn                 3000
    
    frontend stats-front
    bind *:8080
    mode http
    default_backend stats-back
    
    frontend pxc-front
    bind *:13306
    mode tcp
    default_backend pxc-back
    
    frontend pxc-onenode-front
    bind *:3308
    mode tcp
    default_backend pxc-onenode-back
    
    backend stats-back
    mode http
    balance roundrobin
    stats enable
    stats hide-version
    stats realm Haproxy\ Statistics
    stats uri /haproxy/stats
    stats auth admin:**password**
    
    backend pxc-back
    mode tcp
    balance leastconn
    option httpchk
    server db_01 140.92.143.159:3306 check port 9200 inter 12000 rise 3 fall 3
    server db_02 140.92.143.76:3306 check port 9200 inter 12000 rise 3 fall 3
    server db_03 140.92.143.145:3306 check port 9200 inter 12000 rise 3 fall 3
    
    backend pxc-onenode-back
    mode tcp
    balance leastconn
    option httpchk
    server db_01 140.92.143.159:3306 check port 9200 inter 12000 rise 3 fall 3
    #server db_02 140.92.143.76:3306 check port 9200 inter 12000 rise 3 fall 3
    #server db_03 140.92.143.145:3306 check port 9200 inter 12000 rise 3 fall 3
    
  • 啟動 HAProxy
    service haproxy start
    chkconfig haproxy on
  • 查看 HAProxy 統計頁面 - http://140.92.143.159:8080/haproxy/stats
  • 驗證 HAProxy + Percona XtraDB Cluster
    mysql -u root -p**password** -h 127.0.0.1 -P 13306 -e "show variables like 'wsrep_node_name';"
    +-----------------+-------+
    | Variable_name   | Value |
    +-----------------+-------+
    | wsrep_node_name | db_02 |
    +-----------------+-------+

    這樣表示這次連線被 HAProxy 分配到 db_02

  • 撰寫簡單驗證程式
    vi t.pl
    #!/usr/bin/perl
    $n=10;
    $user='root';
    $pass='**password**';
    $host='127.0.0.1';
    $port=13306;
    $sql_cmd="show variables like 'wsrep_node_name';";
     
    for ($i=0;$i<$n;$i++) {
            $str=`mysql -u $user -p$pass -h $host -P $port -e "$sql_cmd"`;
            print("$str\n");
    }
    perl t.pl
    [root@kvm-pxc2 ~]# perl t.pl
    Warning: Using a password on the command line interface can be insecure.
    Variable_name   Value
    wsrep_node_name db_02
    
    Warning: Using a password on the command line interface can be insecure.
    Variable_name   Value
    wsrep_node_name db_03
    
    Warning: Using a password on the command line interface can be insecure.
    Variable_name   Value
    wsrep_node_name db_01
    :
    
    Warning: Using a password on the command line interface can be insecure.
    Variable_name   Value
    wsrep_node_name db_03
    
    Warning: Using a password on the command line interface can be insecure.
    Variable_name   Value
    wsrep_node_name db_01
    
    Warning: Using a password on the command line interface can be insecure.
    Variable_name   Value
    wsrep_node_name db_02

    這樣表示 HAProxy 有動態分配流量連上 db_01 db_02 db_03

  • 當發現 ct-percona1 關閉重起後, 其他 node 重起就連不上 Cluster 時, 會出現以下錯誤訊息
    ....gcs/src/gcs_group.cpp:void group_post_state_exchange(gcs_group_t*)():319: Reversing history:....

    可以將 /var/lib/mysql 內的 grastate.dat 與 ib_log* 刪除掉, 就可以啟動成功

    rm grastate.dat ib_log*
  • 加入一台全新的 node 時, 發現啟動後失敗無法同步原有的資料狀況時, 可以檢查 /etc/my.cnf 內
    :
    wsrep_sst_method=xtrabackup-v2
    :

    將 wsrep_sst_method 改成 xtrabackup-v2 同步速度變快, 且能成功啟動.

  • 當發現 /var/lib/mysql 內出現大量的 mysql-bin.xxxxxx 佔據磁碟空間的處理方式
    • 這是重要的系統資料紀錄, 用於資料還原, 不過可以規劃成只有多少個數量, 或是保留可還原的天數, 在 /etc/my.cnf 內加上這樣的設定參數, 重新啟動 mysql 即可依照設定刪除
    • Exp. 保留可還原 20 天內的資料

      [mysqld]
      :
      expire-logs-days = 20
      :

    • Exp. 保留 10 個資料紀錄檔

      [mysqld]
      :
      max_binlog_files = 10
      :

  • tech/perconaxc.txt
  • 上一次變更: 2021/03/13 11:37
  • jonathan