Mysql之综合实验2-LAMP+MHA+MYcat分库

1、搭建主从半同步+GTID复制
2、搭建MHA主服务器高可用
3、Mycat实现分库：wordpress库和shopxo库
4、客户通过域名可以访问到wordpress和shopxo

实验环境准备工作：
Mysql master: NAT eth0: 10.0.0.8    Rocky8.6  
Mysql slave:  NAT eth0: 10.0.0.18   Rocky8.6
Mysql slave1: NAT eth0: 10.0.0.28   Rocky8.6
Mycat:        NAT eth0: 10.0.0.38   Rocky8.6
MHA manager:  NAT eth0: 10.0.0.7    Centos7 
wordpress:    NAT eth0: 10.0.0.48   Rocky8.6
shopxo:       NAT eth0: 10.0.0.58   Rocky8.6
firewalld:    NAT eth0: 10.0.0.68   仅主机VMnet2: eth1: 192.168.0.100/24 仅主机VMnet2: eth1:0: 192.168.0.101/24
UserRoute:   仅主机VMnet2: eth1: 192.168.0.88/24 仅主机VMnet3: eth0 192.168.10.88/24
DNS master:  仅主机VMnet3: eth0 192.168.10.100/24
DNS slave:   仅主机VMnet3: eth0 192.168.10.200/24
client:      仅主机VMnet3: eth0 192.168.10.8/24

# Mysql master、mysql slave、mysql slave1、MHA配置和上边实验配置一样

# Mycat配置：
Mycat：
[root@mycat ~]# yum install -y java
[root@mycat ~]#mkdir /apps
[root@mycat ~]#tar xf Mycat-server-1.6.7.6-release-20220524173810-linux.tar.gz -C /apps/
[root@mycat ~]#cd /apps/
[root@mycat apps]#echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@mycat apps]#. /etc/profile.d/mycat.sh
[root@mycat apps]#mycat start
Starting Mycat-server...
[root@mycat apps]#ss -ntl
[root@mycat mycat]#/apps/mycat/
[root@mycat mycat]#vim conf/server.xml
 <property name="serverPort">3306</property>   #修改8066端口为3066，此行（45行）默认是注释的，可以复制到53行左右为注释的地方

# server.xml最后几行user修改如下：
    <user name="root" defaultAccount="true">
        <property name="password">123456</property>
        <property name="schemas">wordpress,shopxo</property>
    <!--    <property name="defaultSchema">wordpress</property> -->
        <!--No MyCAT Database selected 错误前会尝试使用该schema作为schema，不设置则为null,报错 -->

        <!-- 表级 DML 权限设置 -->
        <!--        
        <privileges check="false">
            <schema name="TESTDB" dml="0110" >
                <table name="tb01" dml="0000"></table>
                <table name="tb02" dml="1111"></table>
            </schema>
        </privileges>       
         -->
    </user>

    <user name="wordpress">
        <property name="password">123456</property>
        <property name="schemas">wordpress</property>
<!--        <property name="readOnly">true</property>  -->
        <property name="defaultSchema">wordpress</property>
    </user>
    <user name="shopxo">
        <property name="password">123456</property>
        <property name="schemas">shopxo</property>
<!--        <property name="readOnly">true</property>  -->
        <property name="defaultSchema">shopxo</property>
    </user>

[root@mycat ~]#cd /apps/mycat/conf/
[root@mycat conf]#vim schema.xml         
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <schema name="wordpress" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    </schema>
    <schema name="shopxo" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn2">
    </schema>
    <dataNode name="dn1" dataHost="localhost1" database="wordpress" />
    <dataNode name="dn2" dataHost="localhost1" database="shopxo" />
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="host1" url="10.0.0.8:3306" user="li" password="123456">
         <readHost host="host2" url="10.0.0.18:3306" user="li" password="123456" />
         <readHost host="host3" url="10.0.0.28:3306" user="li" password="123456" />
        </writeHost>
    </dataHost>
</mycat:schema>
[root@mycat conf]#mycat restart

# wordpress配置：
提前在数据库建立数据库wordpress，用户wordpress@'10.0.0.%'，并授权wordpress库的所有操作
yum install httpd php php-json php-mysqlnd
tar xf wordpress-5.8.4-zh_CN.tar.gz
mv wordpress/* /var/www/html/
chown -R apache. /var/www/html/
systemctl start httpd
浏览器登陆10.0.0.48 连接数据库 数据库主机填写mycat的地址10.0.0.38，用户名填写mycat server.xml里对应的用户信息

# shopxo配置：
提前在数据库建立数据库shonpx，用户shonpx@'10.0.0.%'，并授权shonpx库的所有操作
yum -y install httpd php php-mysqlnd php-json php-gd php-xml php-pecl-zip
tar xf shopxo-v2.2.3.tar.gz
mv shopxo/* /var/www/html/
chown -R apache. /var/www/html/
systemctl start httpd
浏览器登陆10.0.0.58 连接数据库 数据库主机填写mycat的地址10.0.0.38，用户名填写mycat server.xml里对应的用户信息

# 防火墙：
[root@rocky8 ~]# sysctl -p
net.ipv4.ip_forward = 1
[root@rocky8 ~]# iptables -t nat -A PREROUTING -d 192.168.0.100 -p tcp --dport 80 -j DNAT --to-destination 10.0.0.48:80
[root@rocky8 ~]# iptables -t nat -A PREROUTING -d 192.168.0.101 -p tcp --dport 80 -j DNAT --to-destination 10.0.0.58:80

# UserRoute：
[root@rocky8 ~]# iptables -t nat -A POSTROUTING -s 192.168.10.0/24 -j MASQUERADE
[root@rocky8 ~]# sysctl -p
net.ipv4.ip_forward = 1

# DNS master：提前安装好dns软件（bind和bind-utils）
[root@DNS ~]# vim /etc/named.conf
注释掉三行：
//  listen-on port 53 { 127.0.0.1; };
//  listen-on-v6 port 53 { ::1; };
// allow-query     { localhost; };
增加一行：
allow-transfer { 192.168.10.200; };   #只允许从服务器ip的请求

[root@DNS ~]# vim /etc/named.rfc1912.zones  #配置域名
zone "dayu.org" IN {
    type master;
    file "dayu.org.zone";
};
              
[root@DNS ~]# cp -p /var/named/named.localhost  /var/named/dayu.org.zone
[root@DNS ~]# vim /var/named/dayu.org.zone
$TTL 1D
@   IN SOA  wang rname.invalid. (
                    0   ; serial
                    1D  ; refresh
                    1H  ; retry
                    1W  ; expire
                    3H )    ; minimum
    NS  wang
    NS  slave
slave  A    192.168.10.200
wang    A   192.168.10.100
wordpress     A   192.168.0.101
shopxo        A   192.168.0.100

最后重启下DNS 的named服务

#192.168.10.200（从DNS）注释掉三行：
[root@DNS1 ~]# vim /etc/named.conf
//  listen-on port 53 { 127.0.0.1; };
//  listen-on-v6 port 53 { ::1; };
//  allow-query     { localhost; };

[root@DNS1 ~]# vim /etc/named.rfc1912.zones 
zone "dayu.org" IN {
    type slave;
    masters { 192.168.10.100; };
    file "slaves/dayu.org.zone";
};
最后重启下DNS 的named服务

用户测试：
[root@client ~]# curl wordpress.dayu.org
[root@client ~]# curl shopxo.dayu.org