一、安装Percona数据库

1. 离线安装Percona

  • 进入RPM安装文件目录,执行下面的脚本

    1
    yum localinstall *.rpm
  • 管理MySQL服务

    1
    2
    3
    systemctl start mysqld
    systemctl stop mysqld
    systemctl restart mysqld

2. 在线安装Percona

  • 使用yum命令安装

    1
    2
    yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
    yum install Percona-Server-server-57
  • 管理MySQL服务

    1
    2
    3
    service mysql start
    service mysql stop
    service mysql restart

3. 开放防火墙端口

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

4. 修改MySQL配置文件

1
vi /etc/my.cnf
1
2
3
4
5
[mysqld]
character_set_server = utf8
bind-address = 0.0.0.0
#跳过DNS解析
skip-name-resolve
1
service mysql restart

5. 禁止开机启动MySQL

1
chkconfig mysqld off

6. 初始化MySQL数据库

  • 查看MySQL初始密码

    1
    cat /var/log/mysqld.log | grep "A temporary password"
  • 修改MySQL密码

    1
    mysql_secure_installation
  • 创建远程管理员账户

    1
    mysql -u root -p
    1
    2
    3
    CREATE USER 'admin'@'%' IDENTIFIED BY 'Abc_123456';
    GRANT all privileges ON *.* TO 'admin'@'%';
    FLUSH PRIVILEGES;

二、创建PXC集群

1. 删除MariaDB程序包

1
yum -y remove mari*

2. 开放防火墙端口

1
2
3
4
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

1
vi /etc/selinux/config

把SELINUX属性值设置成disabled

1
reboot

4. 离线安装PXC

  • 进入RPM文件目录,执行安装命令

    1
    yum localinstall *.rpm
  • 参考第一章内容,修改MySQL配置文件、创建账户等操作

5. 创建PXC集群

  • 停止MySQL服务

  • 修改每个PXC节点的/etc/my.cnf文件(在不同节点上,注意调整文件内容)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    server-id=1  #PXC集群中MySQL实例的唯一ID,不能重复,必须是数字
    wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
    wsrep_cluster_name=pxc-cluster #PXC集群的名称
    wsrep_cluster_address=gcomm://192.168.99.151,192.168.99.159,192.168.99.215
    wsrep_node_name=pxc1 #当前节点的名称
    wsrep_node_address=192.168.99.151 #当前节点的IP
    wsrep_sst_method=xtrabackup-v2 #同步方法(mysqldump、rsync、xtrabackup)
    wsrep_sst_auth= admin:Abc_123456 #同步使用的帐户
    pxc_strict_mode=ENFORCING #同步严厉模式
    binlog_format=ROW #基于ROW复制(安全可靠)
    default_storage_engine=InnoDB #默认引擎
    innodb_autoinc_lock_mode=2 #主键自增长不锁表
  • 主节点的管理命令(第一个启动的PXC节点)

    1
    2
    3
    systemctl start mysql@bootstrap.service
    systemctl stop mysql@bootstrap.service
    systemctl restart mysql@bootstrap.service
  • 非主节点的管理命令(非第一个启动的PXC节点)

    1
    2
    3
    service start mysql
    service stop mysql
    service restart mysql
  • 查看PXC集群状态信息

    1
    show status like 'wsrep_cluster%' ;
  • 按照上述配置方法,创建两组PXC集群

6. PXC节点启动与关闭

  • 如果最后关闭的PXC节点是安全退出的,那么下次启动要最先启动这个节点,而且要以主节点启动
  • 如果最后关闭的PXC节点不是安全退出的,那么要先修改/var/lib/mysql/grastate.dat 文件,把其中的safe_to_bootstrap属性值设置为1,再安装主节点启动

三、安装MyCat

1. JDK安装与配置

  • 安装JDK

    1
    2
    3
    4
    #搜索JDK版本
    yum search jdk
    #安装JDK1.8开发版
    yum install java-1.8.0-openjdk-devel.x86_64
  • 配置环境变量

    1
    2
    3
    4
    5
    #查看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

2. 创建数据表

  • 在两组PXC集群中分别创建t_user数据表

    1
    2
    3
    4
    5
    6
    7
    8
    9
    CREATE TABLE t_user(
    id INT UNSIGNED PRIMARY KEY,
    username VARCHAR(200) NOT NULL,
    password VARCHAR(2000) NOT NULL,
    tel CHAR(11) NOT NULL,
    locked TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
    INDEX idx_username(username) USING BTREE,
    UNIQUE INDEX unq_username(username) USING BTREE
    );

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

    1
    2
    yum install unzip
    unzip MyCAT压缩包名称
  4. 开放防火墙8066和9066端口,关闭SELINUX

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

    1
    chmod -R 777 ./*.sh
  6. MyCat启动与关闭

    1
    2
    3
    4
    #cd MyCat的bin目录
    ./startup_nowrap.sh #启动MyCat
    ps -aux #查看系统进程
    kill -9 MyCat进程编号
  7. 修改server.xml文件,设置MyCat帐户和虚拟逻辑库

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    <?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文件,设置数据库连接和虚拟数据表

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    <?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

    1
    2
    3
    <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
    <property name="count">2</property>
    </function>
  3. 重启MyCat

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

    1
    2
    3
    4
    5
    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文件,内容如下:

    1
    2
    3
    4
    5
    6
    101=0
    102=0
    103=0
    104=1
    105=1
    106=1
  2. 在rule.xml文件中加入自定义

    1
    2
    3
    4
    <function name="customer-hash-int"
    class="io.mycat.route.function.PartitionByFileMap">
    <property name="mapFile">customer-hash-int.txt</property>
    </function>
    1
    2
    3
    4
    5
    6
    <tableRule name="sharding-customer">
    <rule>
    <columns>sharding_id</columns>
    <algorithm>customer-hash-int</algorithm>
    </rule>
    </tableRule>
  3. 修改schema.xml文件,添加父子表定义

    1
    2
    3
    4
    <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:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    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

      1
      yum install -y haproxy
    • 编辑配置文件

      1
      vi /etc/haproxy/haproxy.cfg
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      37
      38
      39
      40
      41
      42
      43
      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

      1
      service haproxy start
    • 访问Haproxy监控画面

      http://192.168.99.131:4001/dbs

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

  4. 在某个Haproxy虚拟机实例上部署Keepalived

    • 开启防火墙的VRRP协议

      1
      2
      3
      4
      #开启VRRP
      firewall-cmd --direct --permanent --add-rule ipv4 filter INPUT 0 --protocol vrrp -j ACCEPT
      #应用设置
      firewall-cmd --reload
    • 安装Keepalived

      1
      yum install -y keepalived
    • 编辑配置文件

      1
      vim /etc/keepalived/keepalived.conf
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      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

      1
      service keepalived start
    • ping 192.168.99.133

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

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

四、Sysbench基准测试

1. 安装Sysbench

  • 在线安装

    1
    2
    curl -s https://packagecloud.io/install/
    repositories/akopytov/sysbench/script.rpm.sh | sudo bash
    1
    yum -y install sysbench
  • 本地安装

2. 执行测试

  • 准备测试库

    1
    sysbench  /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=192.168.99.131 --mysql-port=3306 --mysql-user=admin --mysql-password=Abc_123456 --oltp-tables-count=10 --oltp-table-size=100000 prepare
  • 执行测试

    1
    sysbench  /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=192.168.99.131 --mysql-port=3306 --mysql-user=admin --mysql-password=Abc_123456 --oltp-test-mode=complex --threads=10 --time=300 --report-interval=10 run >> /home/mysysbench.log
  • 清理数据

    1
    sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=192.168.99.131 --mysql-port=3306 --mysql-user=admin --mysql-password=Abc_123456 --oltp-tables-count=10 cleanup

五、tpcc-mysql 压力测试

1. 准备工作

  • 修改my.cnf配置文件

    1
    vi /etc/my.cnf

    pxc_strict_mode=DISABLED

  • 修改某个Haproxy的配置文件

    1
    2
    3
    server   mysql_1  192.168.99.151:3306  check  port  3306  weight  1  maxconn  2000
    server mysql_2 192.168.99.159:3306 check port 3306 weight 1 maxconn 2000
    server mysql_3 192.168.99.215:3306 check port 3306 weight 1 maxconn 2000
  • 重新启动Haproxy

  • 安装依赖程序包

    1
    2
    yum install -y gcc
    yum install -y mysql-devel

2. 安装tpcc-mysql

  • 下载压缩包

    https://codeload.github.com/Percona-Lab/tpcc-mysql/zip/master

  • 执行安装

    1
    2
    #cd tpcc的src目录
    make
  • 执行create_table.sqladd_fkey_idx.sql两个文件

  • 执行数据初始化

    1
    ./tpcc_load -h 192.168.99.131 -d tpcc -u admin -p Abc_123456 -w
  • 执行压力测试

    1
    ./tpcc_start -h 192.168.99.131 -d tpcc -u admin -p Abc_123456 -w 1 -c 5 -r 300 -l 600 ->tpcc-output-log

六、导入数据

1. 生成1000万条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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. 执行文件切分

  • 上传data.txt文件到linux

  • 执行文件切分

    1
    split -l 1000000 -d data.txt

3. 准备数据库

  • 每个PXC分片只开启一个节点

  • 修改PXC节点文件,然后重启PXC服务

    1
    2
    3
    innodb_flush_log_at_trx_commit = 0
    innodb_flush_method = O_DIRECT
    innodb_buffer_pool_size = 200M
  • 创建t_test数据表

    1
    2
    3
    4
    CREATE TABLE t_test(
    id INT UNSIGNED PRIMARY KEY,
    name VARCHAR(200) NOT NULL
    );
  • 配置MyCat

    1
    <table name="t_test" dataNode="dn1,dn2" rule="mod-long" />
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    <dataHost name="cluster1" maxCon="1000" minCon="10" balance="0" 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"/>
    </dataHost>
    <dataHost name="cluster2" maxCon="1000" minCon="10" balance="0" 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"/>
    </dataHost>

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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();
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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

  • 安装jemlloc

    1
    yum install -y jemalloc
  • 编辑配置文件

    1
    vi /etc/my.cnf
    1
    2
    3
    4
    ……
    [mysqld_safe]
    malloc-lib=/usr/lib64/libjemalloc.so.1
    ……
  • 重启MySQL

  • 开启Linux大页内存

    1
    2
    echo never > /sys/kernel/mm/transparent_hugepage/enabled
    echo never > /sys/kernel/mm/transparent_hugepage/defrag
  • 安装TokuDB

    1
    2
    3
    4
    yum install -y Percona-Server-tokudb-57.x86_64
    ps-admin --enable -uroot -p
    service mysql restart
    ps-admin --enable -uroot -p
  • 查看安装结果

    1
    show engines ;

2. 配置Replication集群

  • 在两个TokuDB数据库上创建用户

    1
    CREATE USER 'backup'@'%' IDENTIFIED BY 'Abc_123456' ;
    1
    GRANT super, reload, replication slave ON *.* TO 'backup'@'%' ;
    1
    FLUSH  PRIVILEGES ;
  • 修改两个TokuDB的配置文件,如下:

    1
    2
    3
    4
    5
    [mysqld]
    server_id = 101
    log_bin = mysql_bin
    relay_log = relay_bin
    ……
    1
    2
    3
    4
    [mysqld]
    server_id = 102
    log_bin = mysql_bin
    relay_log = relay_bin
  • 重新启动两个TokuDB节点

  • 分别在两个TokuDB上执行下面4句SQL

    1
    2
    3
    4
    5
    6
    7
    8
    9
    #关闭同步服务
    stop slave;
    #设置同步的Master节点
    change master to master_host="192.168.99.155",master_port=3306,master_user="backup",
    master_password="Abc_123456";
    #启动同步服务
    start slave;
    #查看同步状态
    show slave status;
    1
    2
    3
    4
    5
    6
    7
    8
    9
    #关闭同步服务
    stop slave;
    #设置同步的Master节点
    change master to master_host="192.168.99.102",master_port=3306,master_user="backup",
    master_password="Abc_123456";
    #启动同步服务
    start slave;
    #查看同步状态
    show slave status;

3. 创建归档表

1
2
3
4
5
6
7
8
9
10
11
12
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双机热备

  • 在两个节点上安装Haproxy

    1
    yum install -y haproxy
  • 修改配置文件

    1
    vi /etc/haproxy/haproxy.cfg
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    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:4002
    mode tcp
    balance roundrobin
    option tcplog #日志格式
    server backup_1 192.168.99.102:3306 check port 3306 maxconn 2000
    server backup_2 192.168.99.155:3306 check port 3306 maxconn 2000
    option tcpka #使用keepalive检测死链
  • 重启Haproxy

  • 开启防火墙的VRRP协议

    1
    firewall-cmd --direct --permanent --add-rule ipv4 filter INPUT 0 --protocol vrrp -j ACCEPT
    1
    firewall-cmd --reload
  • 在两个节点上安装Keepalived

    1
    yum install -y keepalived
  • 编辑Keepalived配置文件

    1
    vim /etc/keepalived/keepalived.conf
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    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.211
    }
    }
  • 重启Keepalived

5. 准备归档数据

  • 在两个PXC分片上创建进货表

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    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)
    )
  • 配置MyCat的schema.xml文件,并重启MyCat

    1
    <table name="t_purchase" dataNode="dn1,dn2" rule="mod-long" />

6. 执行数据归档

  • 安装pt-archiver

    1
    2
    3
    yum install percona-toolkit
    pt-archiver --version
    pt-archiver --help
  • 执行数据归档

    1
    pt-archiver --source h=192.168.99.102,P=8066,u=admin,p=Abc_123456,D=test,t=t_purchase --dest h=192.168.99.102,P=3306,u=admin,p=Abc_123456,D=test,t=t_purchase --no-check-charset --where 'purchase_date<"2018-09"' --progress 5000 --bulk-delete --bulk-insert --limit=10000 --statistics

最后更新: 2019年03月18日 22:32

原始链接: https://zem12345678.github.io/2019/03/18/MySQL数据库集群-PXC方案/

× 请我吃糖~
打赏二维码