MYSQL数据库集群方案-PXC

2019-09-25  本文已影响0人  日常琐事一如年少模样

一、安装Percona数据库

1. 离线安装Percona

2. 在线安装Percona

3. 开放防火墙端口

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

4. 修改MySQL配置文件

vi /etc/my.cnf

[mysqld]
character_set_server = utf8
bind-address = 0.0.0.0
#跳过DNS解析
skip-name-resolve

service mysql restart

5. 禁止开机启动MySQL

chkconfig mysqld off

6. 初始化MySQL数据库

二、创建PXC集群

1. 删除MariaDB程序包

yum -y remove mari*

2. 开放防火墙端口

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

3. 关闭SELINUX

vi /etc/selinux/config

把SELINUX属性值设置成disabled

reboot

4. 离线安装PXC

5. 创建PXC集群

6. PXC节点启动与关闭

三、安装MyCat

1. JDK安装与配置

2. 创建数据表

3. MyCat安装与配置

  1. 下载MyCat

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

  2. 上传MyCat压缩包到虚拟机

  3. 安装unzip程序包，解压缩MyCat

    yum install unzip
unzip MyCAT压缩包名称

  4. 开放防火墙8066和9066端口，关闭SELINUX

  5. 修改MyCat的bin目录中所有.sh文件的权限

    chmod -R 777 ./*.sh

  6. MyCat启动与关闭

    #cd MyCat的bin目录
./startup_nowrap.sh #启动MyCat
ps -aux #查看系统进程
kill -9 MyCat进程编号

  7. 修改server.xml文件，设置MyCat帐户和虚拟逻辑库

    <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
     <system>
         <property name="nonePasswordLogin">0</property>
         <property name="useHandshakeV10">1</property>
         <property name="useSqlStat">0</property>
         <property name="useGlobleTableCheck">0</property>
         <property name="sequnceHandlerType">2</property>
         <property name="subqueryRelationshipCheck">false</property>
         <property name="processorBufferPoolType">0</property>
         <property name="handleDistributedTransactions">0</property>
         <property name="useOffHeapForMerge">1</property>
         <property name="memoryPageSize">64k</property>
         <property name="spillsFileBufferSize">1k</property>
         <property name="useStreamOutput">0</property>
         <property name="systemReserveMemorySize">384m</property>
         <property name="useZKSwitch">false</property>
     </system>
    <!--这里是设置的admin用户和虚拟逻辑库-->
     <user name="admin" defaultAccount="true">
         <property name="password">Abc_123456</property>
         <property name="schemas">test</property>
     </user>
</mycat:server>

  1. 修改schema.xml文件，设置数据库连接和虚拟数据表

    <?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
     <!--配置数据表-->
     <schema name="test" checkSQLschema="false" sqlMaxLimit="100">
         <table name="t_user" dataNode="dn1,dn2" rule="mod-long" />
     </schema>
     <!--配置分片关系-->
     <dataNode name="dn1" dataHost="cluster1" database="test" />
     <dataNode name="dn2" dataHost="cluster2" database="test" />
     <!--配置连接信息-->
     <dataHost name="cluster1" maxCon="1000" minCon="10" balance="2" 
                writeType="1" dbType="mysql" dbDriver="native" switchType="1"  
                slaveThreshold="100">
         <heartbeat>select user()</heartbeat>
         <writeHost host="W1" url="192.168.99.151:3306" user="admin" 
                     password="Abc_123456">
             <readHost host="W1R1" url="192.168.99.159:3306" user="admin" 
                        password="Abc_123456" />
             <readHost host="W1R2" url="192.168.99.215:3306" user="admin" 
                        password="Abc_123456" />
         </writeHost>
         <writeHost host="W2" url="192.168.99.159:3306" user="admin" 
                     password="Abc_123456">
             <readHost host="W2R1" url="192.168.99.151:3306" user="admin" 
                        password="Abc_123456" />
             <readHost host="W2R2" url="192.168.99.215: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>
         <writeHost host="W1" url="192.168.99.121:3306" user="admin"
                    password="Abc_123456">
             <readHost host="W1R1" url="192.168.99.122:3306" user="admin" 
                        password="Abc_123456" />
             <readHost host="W1R2" url="192.168.99.123:3306" user="admin" 
                        password="Abc_123456" />
         </writeHost>
         <writeHost host="W2" url="192.168.99.122:3306" user="admin"
                    password="Abc_123456">
             <readHost host="W2R1" url="192.168.99.121:3306" user="admin" 
                        password="Abc_123456" />
             <readHost host="W2R2" url="192.168.99.123:3306" user="admin" 
                        password="Abc_123456" />
         </writeHost>
     </dataHost>
</mycat:schema>

  2. 修改rule.xml文件，把mod-long的count值修改成2

    <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
 <property name="count">2</property>
</function>

  3. 重启MyCat

  4. 向t_user表写入数据，感受数据的切分

    USE test;
#第一条记录被切分到第二个分片
INSERT INTO t_user(id,username,password,tel,locked) VALUES(1,"A",HEX(AES_ENCRYPT('123456','HelloWorld')));
#第二条记录被切分到第一个分片
INSERT INTO t_user(id,username,password,tel,locked) VALUES(2,"B",HEX(AES_ENCRYPT('123456','HelloWorld')));

4. 配置父子表

  1. 在conf目录下创建customer-hash-int文件，内容如下：

    101=0
102=0
103=0
104=1
105=1
106=1

  2. 在rule.xml文件中加入自定义<function>和<tableRule>

    <function name="customer-hash-int"
     class="io.mycat.route.function.PartitionByFileMap">
 <property name="mapFile">customer-hash-int.txt</property>
</function>

    <tableRule name="sharding-customer">
 <rule>
     <columns>sharding_id</columns>
     <algorithm>customer-hash-int</algorithm>
 </rule>
</tableRule>

  3. 修改schema.xml文件，添加父子表定义

    <table name="t_customer" dataNode="dn1,dn2" rule="sharding-customer">
 <childTable name="t_orders" primaryKey="ID" joinKey="customer_id"   
                parentKey="id"/>
</table>

  4. 在MyCat上执行如下SQL：

    USE test;
CREATE TABLE t_customer(
 id INT UNSIGNED PRIMARY KEY,
    username VARCHAR(200) NOT NULL,
    sharding_id INT NOT NULL
);
CREATE TABLE t_orders(
 id INT UNSIGNED PRIMARY KEY,
    customer_id INT NOT NULL,
    datetime TIMESTAMP DEFAULT CURRENT_TIMSTAMP
);

  5. 向t_customer表和t_orders表写入数据，查看字表数据跟随父表切分到同一个分片

5. 创建双机热备的MyCat集群

  1. 用两个虚拟机实例，各自部署MyCat

  2. 用一个虚拟机实例部署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

  3. 用另外一个虚拟机同样按照上述操作安装Haproxy

  4. 在某个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

  5. 在另外一个Haproxy虚拟机上，按照上述方法部署Keepalived

  6. 使用MySQL客户端连接192.168.99.133，执行增删改查数据

四、Sysbench基准测试

1. 安装Sysbench

2. 执行测试

五、tpcc-mysql 压力测试

1. 准备工作

2. 安装tpcc-mysql

六、导入数据

1. 生成1000万条数据

import java.io.FileWriter
import java.io.BufferedWriter

class Test {
    def static void main(String[] args) {
        var writer=new FileWriter("D:/data.txt")
        var buff=new BufferedWriter(writer)
        for(i:1..10000000){
            buff.write(i+",测试数据\n")
        }
        buff.close
        writer.close
    }
}

2. 执行文件切分

3. 准备数据库

4. 执行Java程序，多线程导入数据

import org.eclipse.xtend.lib.annotations.Accessors
import java.io.File
import java.sql.DriverManager

class Task implements Runnable{
    @Accessors
    File file;
    
    override run() {
        var url="jdbc:mysql://192.168.99.131:8066/test"
        var username="admin"
        var password="Abc_123456"
        var con=DriverManager.getConnection(url,username,password)
        var sql='''
            load data local intfile '/home/data/«file.name»' ignore into table t_test 
            character set 'utf8' 
            fields terminated by ',' optionally enclosed by '\"' 
            lines terminated by '\n' (id,name);
        '''
        var pst=con.prepareStatement(sql);
        pst.execute
        con.close
        LoadData.updateNum();
    }
}

import com.mysql.jdbc.Driver
import java.sql.DriverManager
import java.util.concurrent.LinkedBlockingQueue
import java.util.concurrent.ThreadPoolExecutor
import java.util.concurrent.TimeUnit
import java.io.File

class LoadData {
    var static int num=0;
    var static int end=0;
    var static pool=new ThreadPoolExecutor(1,5,60,TimeUnit.SECONDS,new LinkedBlockingQueue(200))
    def static void main(String[] args) {
        DriverManager.registerDriver(new Driver)
        var folder=new File("/home/data")
        var files=folder.listFiles
        end=files.length //线程池结束条件
        files.forEach[one|
            var task=new Task();
            task.file=one;
            pool.execute(task)
        ]
    }
    synchronized def static updateNum(){
        num++;
        if(num==end){
            pool.shutdown();
            println("执行结束")
        }
    }
}

七、大数据归档

1. 安装TokuDB

2. 配置Replication集群

3. 创建归档表

CREATE TABLE t_purchase (
    id INT UNSIGNED PRIMARY KEY,
    purchase_price DECIMAL(10,2) NOT NULL,
    purchase_num INT UNSIGNED NOT NULL,
    purchase_sum DECIMAL (10,2) NOT NULL,
    purchase_buyer INT UNSIGNED NOT NULL,
    purchase_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    company_id INT UNSIGNED NOT NULL,
    goods_id INT UNSIGNED NOT NULL,
    KEY idx_company_id(company_id),
    KEY idx_goods_id(goods_id)
)engine=TokuDB;

4. 配置Haproxy+Keepalived双机热备

5. 准备归档数据

6. 执行数据归档

本篇文章由一文多发平台ArtiPub自动发布

上一篇 下一篇

猜你喜欢

热点阅读