mysql學習筆記之快速搭建PXC叢集（Mycat分片）

雲小蟲2019發表於2020-03-26
MySql筆記

6 .搭建PXC叢集

參考percona官方文件：

https://www.percona.com/doc/percona-xtradb-cluster/5.7/index.html

6.1 準備工作

6.1.1 下載軟體

https://www.percona.com/downloads/Percona-XtraDB-Cluster-LATEST/

另外單獨下載一個軟體包：qpress-11-1.el7.x86_64.rpm

6.1.2 刪除MariaDB

yum -y remove mari*

6.1.3 開放埠

firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --zone=public --add-port=4444/tcp --permanent
firewall-cmd --zone=public --add-port=4567/tcp --permanent
firewall-cmd --zone=public --add-port=4568/tcp --permanent
firewall-cmd --reload

6.1.4 關閉SELinux

vi /etc/selinux/config
#把SELINUX屬性值設定成disabled
reboot

6.2 安裝軟體(install PXC)

6.2.1 線上安裝


$ sudo yum install Percona-XtraDB-Cluster-57

6.2.2本地安裝

解壓下載好的安裝包，並將 qpress 包放入一起，然後進入目錄，進行如下命令

$sudo yum localinstall  *.rpm

[^說明]: You need to have root access on the node where you will be installing Percona XtraDB Cluster (either logged in as a user with root privileges or be able to run commands with sudo

6.2.3 禁止開機自啟動

chkconfig mysqld off

6.2.4 資料庫初始化

  1. 啟動服務
$sudo service mysql start
  1. 檢視MySQL初始密碼
cat /var/log/mysqld.log | grep "A temporary password"
  1. 用root賬號登入
mysql -u root -p
  1. 修改密碼
mysql_secure_installation
  1. 建立遠端管理員賬號
CREATE USER 'YXC_admin'@'%' IDENTIFIED BY 'Yxc_123456';
GRANT all privileges ON *.* TO 'YXC_admin'@'%';
FLUSH PRIVILEGES;
#或者如下：
CREATE USER 'YXC'@'localhost' IDENTIFIED BY 'passw0rd';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON
* . * TO 'YXC'@'localhost';
mysql> FLUSH PRIVILEGES;
  1. 停止服務
$sudo service mysql stop

6.3 配置檔案

Configure all nodes,配置檔案內容：

# vim /etc/my.cnf
[client]
socket=/var/lib/mysql/mysql.sock
[mysqld]
server-id=31
#server-id=1 #PXC叢集中MySQL例項的唯一ID，不能重複，必須是數字
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin
log_slave_updates
expire_logs_days=7
character_set_server = utf8
bind-address = 0.0.0.0
#跳過DNS解析
skip-name-resolve
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
##叢集部分
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_name=pxc3-cluster #PXC叢集的名稱
wsrep_cluster_address=gcomm://192.168.56.31,192.168.56.32,192.168.56.33
wsrep_node_name=pxc31 #當前節點的名稱
wsrep_node_address=192.168.56.31 #當前節點的IP
wsrep_sst_method=xtrabackup-v2 #同步方法（mysqldump、rsync、xtrabackup）
wsrep_sst_auth= YXC_admin:Yxc_123456 #同步使用的帳戶
#SST means State Snapshot Transfer
pxc_strict_mode=ENFORCING #同步嚴厲模式
binlog_format=ROW #基於ROW複製（安全可靠）
default_storage_engine=InnoDB #預設引擎
innodb_autoinc_lock_mode=2 #主鍵自增長不鎖表

6.4 節點啟停

引導節點啟停(Bootstrap the first node)

systemctl start mysql@bootstrap.service
systemctl stop mysql@bootstrap.service
systemctl restart mysql@bootstrap.service

非引導節點啟停(add other nodes)

service start mysql
service stop mysql
service restart mysql

判斷引導節點

##cat /var/lib/mysql/grastate.dat
# GALERA saved state
version: 2.1
uuid:    4580a102-bd96-11e9-a653-2a1e935fbf67
seqno:   -1
safe_to_bootstrap: 0
#safe_to_bootstrap: 1,則為引導啟動節點

如果最後關閉的PXC節點是安全退出的，那麼下次啟動要最先啟動這個節點，而且要以引導節點啟動
如最後關閉的PXC節點不是安全退出，要先修改/var/lib/mysql/grastate.dat 檔案，把其中的 safe_to_bootstrap 屬性值設定為1，再按照引導節點啟動

6.5 狀態資訊

6.5.1 確認狀態

show status like '%wsrep%';
## wsrep:write set replication
#Percona XtraDB Cluster is based on Percona Server running with the XtraDB storage engine. It uses the Galera library, which is an implementation of the write set replication (wsrep) API developed by Codership Oy. The default and recommended data transfer method is via Percona XtraBackup.

6.5.2 狀態描述（圖片來自網上，僅供參考）

6.6 同步原理（圖片來自網上，僅供參考）

7. Mycat 分片

7.1 Mycat 安裝

（1）安裝JDK

#搜尋JDK版本
yum search jdk 
#安裝JDK1.8開發版
yum install java-1.8.0-openjdk-devel.x86_64

（2）配置環境變數

#檢視JDK安裝路徑
ls -lrt /etc/alternatives/java
vi /etc/profile
#在檔案結尾加上JDK路徑，例如export  JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.171-8.b10.el7_5.x86_64/
source  /etc/profile

（3）下載MyCat

http://dl.mycat.io/1.6.5/Mycat-server-1.6.5-release-20180122220033-linux.tar. gz

（4）上傳MyCat壓縮包到虛擬機器

（5）安裝unzip程式包，解壓縮MyCat

yum install unzip
unzip MyCAT壓縮包名稱

（6） 開放防火牆8066和9066埠，關閉SELINUX

（7） 修改MyCat的bin目錄中的許可權

chmod -R 777 ./*.sh

（8） MyCat啟動與關閉

#cd MyCat的bin目錄
./startup_nowrap.sh #啟動MyCat
ps -aux #檢視系統程式
kill -9 MyCat程式編號

7.2 Mycat 配置

7.2.1 schema.xml


<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <!--配置資料表-->
          <schema name="ecs" checkSQLschema="false" sqlMaxLimit="100">
              <table name="t_user" dataNode="dn1,dn2"  rule="mod-long" />
              <table name="t_customer" dataNode="dn1,dn2"  rule="sharding-customer" >
                <childTable name="t_orders" primaryKey="ID"
                    joinKey="customer_id" parentKey="id">
                </childTable>
              </table>
         </schema>
        <!--配置分片關係-->
          <dataNode name="dn1" dataHost="cluster1" database="ecs" />
          <dataNode name="dn2" dataHost="cluster2" database="ecs" />
        <!--配置連線資訊-->
        <dataHost name="cluster1" maxCon="1000" minCon="10" balance="2"
                          writeType="1" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="w1" url="192.168.56.21:3306" user="admin"  password="Abc_123456">
                        <!-- can have multi read hosts -->
                        <readHost host="w1r1" url="192.168.56.22:3306" user="admin" password="Abc_123456" />
                        <readHost host="w1r2" url="192.168.56.23:3306" user="admin" password="Abc_123456" />
                </writeHost>
                <writeHost host="w2" url="192.168.56.22:3306" user="admin"  password="Abc_123456">
                        <!-- can have multi read hosts -->
                        <readHost host="w2r1" url="192.168.56.21:3306" user="admin" password="Abc_123456" />
                        <readHost host="w2r2" url="192.168.56.23:3306" user="admin" password="Abc_123456" />
                </writeHost>
        </dataHost>
            <dataHost name="cluster2" maxCon="1000" minCon="10" balance="2"
                          writeType="1" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="w1" url="192.168.56.31:3306" user="admin"  password="Abc_123456">
                        <!-- can have multi read hosts -->
                        <readHost host="w1r1" url="192.168.56.32:3306" user="admin" password="Abc_123456" />
                        <readHost host="w1r2" url="192.168.56.33:3306" user="admin" password="Abc_123456" />
                </writeHost>
                <writeHost host="w2" url="192.168.56.32:3306" user="admin"  password="Abc_123456">
                        <!-- can have multi read hosts -->
                        <readHost host="w2r1" url="192.168.56.31:3306" user="admin" password="Abc_123456" />
                        <readHost host="w2r2" url="192.168.56.33:3306" user="admin" password="Abc_123456" />
                </writeHost>
        </dataHost>
</mycat:schema>

7.2.2 server.xml

 <user name="YXC_admin" defaultAccount="true">
                <property      name="password">Yxc_123456</property>
                <property name="schemas">ecs</property>
                <!-- 表級 DML 許可權設定 -->
                <!--
                <privileges check="false">
                        <schema name="TESTDB" dml="0110" >
                                <table name="tb01" dml="0000"></table>
                                <table name="tb02" dml="1111"></table>
                        </schema>
                </privileges>
                 -->
        </user>

7.2.3 rule.xml

 <tableRule name="sharding-customer">
                <rule>
                        <columns>sharding_id</columns>
                        <algorithm>customer-hash-int</algorithm>
                </rule>
 </tableRule>
<function name="customer-hash-int"
                class="io.mycat.route.function.PartitionByFileMap">
                <property name="mapFile">customer-hash-int.txt</property>
</function>

8. Haproxy +keepalived負載均衡

  1. 用一個虛擬機器例項部署Haproxy

    • 安裝Haproxy

      yum install -y haproxy

    • 編輯配置檔案

      vi /etc/haproxy/haproxy.cfg

      global
    log         127.0.0.1 local2
    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     4000
    user        haproxy
    group       haproxy
    daemon
    # turn on stats unix socket
    stats socket /var/lib/haproxy/stats
defaults
    mode                    http
    log                     global
    option                  httplog
    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
listen   admin_stats  
    bind    0.0.0.0:4001
    mode  http
    stats uri       /dbs
    stats realm  Global\ statistics
    stats auth    admin:abc123456
listen   proxy-mysql
    bind    0.0.0.0:3306  
    mode  tcp 
    balance  roundrobin
    option  tcplog       #日誌格式
    server   mycat_1  192.168.99.131:3306  check  port  8066  maxconn  2000  
    server   mycat_2  192.168.99.132:3306  check  port  8066  maxconn  2000  
    option  tcpka        #使用keepalive檢測死鏈

    • 啟動Haproxy

      service haproxy start

    • 訪問Haproxy監控畫面

      http://192.168.99.131:4001/dbs

  2. 用另外一個虛擬機器同樣按照上述操作安裝Haproxy

  3. 在某個Haproxy虛擬機器例項上部署Keepalived

    • 開啟防火牆的VRRP協議

      #開啟VRRP
firewall-cmd --direct --permanent --add-rule ipv4 filter INPUT 0 --protocol  vrrp -j ACCEPT
#應用設定
firewall-cmd --reload

    • 安裝Keepalived

      yum install -y keepalived

    • 編輯配置檔案

      vim /etc/keepalived/keepalived.conf

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

    • 啟動Keepalived

      service keepalived start

    • ping 192.168.99.133

  4. 在另外一個Haproxy虛擬機器上，按照上述方法部署Keepalived

  5. 使用MySQL客戶端連線192.168.99.133

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/69915315/viewspace-2682601/，如需轉載，請註明出處，否則將追究法律責任。

相關文章