# 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 ```shell yum -y remove mari* ``` ### 6.1.3 开放端口 ```shell 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 ```shell vi /etc/selinux/config #把SELINUX属性值设置成disabled reboot ``` ## 6.2 安装软件(install PXC) ### 6.2.1 在线安装 ```shell $ sudo yum install Percona-XtraDB-Cluster-57 ``` ### 6.2.2本地安装 解压下载好的安装包，并将 qpress 包放入一起，然后进入目录，进行如下命令 ```shell $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 禁止开机自启动 ```shell chkconfig mysqld off ``` ### 6.2.4 数据库初始化 1. 启动服务 ```shell $sudo service mysql start ``` 2. 查看MySQL初始密码 ```shelll cat /var/log/mysqld.log | grep "A temporary password" ``` 3. 用root账号登录 ```shell mysql -u root -p ``` 4. 修改密码 ```shel mysql_secure_installation ``` 5. 创建远程管理员账号 ```shell 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; ``` 6. 停止服务 ```shell $sudo service mysql stop ``` ## 6.3 配置文件 Configure all nodes,配置文件内容： ```shell # 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) ```shell systemctl start mysql@bootstrap.service systemctl stop mysql@bootstrap.service systemctl restart mysql@bootstrap.service ``` 非引导节点启停(add other nodes) ```shell service start mysql service stop mysql service restart mysql ``` 判断引导节点 ```shell ##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 确认状态 ```shell 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 ```shell #搜索JDK版本 yum search jdk #安装JDK1.8开发版 yum install java-1.8.0-openjdk-devel.x86_64 ``` （2）配置环境变量 ```shell #查看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.](http://dl.mycat.io/1.6.5/Mycat-server-1.6.5-release-20180122220033-linux.tar.gz)[gz](http://dl.mycat.io/1.6.5/Mycat-server-1.6.5-release-20180122220033-linux.tar.gz) （4）上传MyCat压缩包到虚拟机 （5）安装unzip程序包，解压缩MyCat ```shell yum install unzip unzip MyCAT压缩包名称 ``` （6） 开放防火墙8066和9066端口，关闭SELINUX （7） 修改MyCat的bin目录中的权限 ``` chmod -R 777 ./*.sh ``` （8） MyCat启动与关闭 ```shell #cd MyCat的bin目录 ./startup_nowrap.sh #启动MyCat ps -aux #查看系统进程 kill -9 MyCat进程编号 ``` ## 7.2 Mycat 配置 ### 7.2.1 schema.xml   ```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 ```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 ```xml <tableRule name="sharding-customer"> <rule> <columns>sharding_id</columns> 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 ```shell yum install -y haproxy ``` - 编辑配置文件 ```shell 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 ```shell service haproxy start ``` - 访问Haproxy监控画面 http://192.168.99.131:4001/dbs 2. 用另外一个虚拟机同样按照上述操作安装Haproxy 3. 在某个Haproxy虚拟机实例上部署Keepalived - 开启防火墙的VRRP协议 ```shell #开启VRRP firewall-cmd --direct --permanent --add-rule ipv4 filter INPUT 0 --protocol vrrp -j ACCEPT #应用设置 firewall-cmd --reload ``` - 安装Keepalived ```shell yum install -y keepalived ``` - 编辑配置文件 ```shell 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 ```shell service keepalived start ``` - ping 192.168.99.133 4. 在另外一个Haproxy虚拟机上，按照上述方法部署Keepalived 5. 使用MySQL客户端连接192.168.99.133