中间件mycat对pxc集群的分片处理

安装第二个pxc集群

作为mycat的第二个分片

直接拷贝其中的一个虚拟机，然后还原到最初的状态，这样会小很多，启动改一下IP和基础配置，然后再次拷贝这个虚拟机两份改IP重启即可

正常安装pxc集群即可

 

主节点的配置记录

grastate.dat文件中记录着启动的bootstrap，当有节点以外退出时，为了保持数据一致性，pxc集群会认定最后一个节点的数据是最新的 safe_to_bootstrap 的值会改为1，把配置safe_to_bootstrap值为1当做主节点启动即可

当集群所有节点都意外退出时可以手动修改 safe_to_bootstrap的值为1，然后把这个节点当做主节点启动

[root@node6 ~]# cat /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid:    a61559b1-f3c9-11e8-be12-666937c43234

seqno:   -1

safe_to_bootstrap: 0

Mysql中间件

安装mycat

Mycat是java开发的，要安装java环境

rpm –ivh jdk-8u181-linux-x64.rpm

# 加入环境变量

# vim /etc/profile

export JAVA_HOME=/usr/java/jdk1.8.0_181-amd64

export PATH=$JAVA_HOME/bin:$PATH

下载mycat1.6.5版本

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

 

 

mycat配置

逻辑库：chinasoft

表：t_user

创建数据表，用于保存切分数据

create table t_user(

id int(10) unsigned not null,

username varchar(200) not null,

password varchar(2000) not null,

tel char(11) character not null,

locked tinyint(1) unsigned not null default 0,

primary key (id) using btree,

index indx_username(username) using btree,

unique index unq_username(username) using btree);

schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <!-- 配置数据表 -->
    <schema name="chinasoft" checkSQLschema="false" sqlMaxLimit="100">
        <table name="t_user" dataNode="dn1,dn2" rule="mod-long" />
    </schema>
    <!-- 配置分配关系 -->
    <dataNode name="dn1" dataHost="pxc-cluster" database="chinasoft" />
    <dataNode name="dn2" dataHost="pxc-cluster02" database="chinasoft" />
    <!-- 配置连接关系 pxc-cluster-->
    <dataHost name="pxc-cluster" maxCon="1000" minCon="10" balance="2"
              writeType="1" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="W1" url="10.11.0.210:3306" user="admin"
                   password="Abc_123456">
            <readHost host="W1R1" url="10.11.0.212:3306" user="admin" password="Abc_123456" />
            <readHost host="W1R2" url="10.11.0.215:3306" user="admin" password="Abc_123456" />
        </writeHost>
        <writeHost host="W2" url="10.11.0.212:3306" user="admin"
                   password="Abc_123456">
            <readHost host="W2R1" url="10.11.0.210:3306" user="admin" password="Abc_123456" />
            <readHost host="W2R2" url="10.11.0.215:3306" user="admin" password="Abc_123456" />
        </writeHost>
    </dataHost>
    <!-- 配置连接关系 pxc-cluster02-->
    <dataHost name="pxc-cluster02" maxCon="1000" minCon="10" balance="2"
              writeType="1" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="W1" url="10.11.0.216:3306" user="admin"
                   password="Abc_123456">
            <readHost host="W1R1" url="10.11.0.217:3306" user="admin" password="Abc_123456" />
            <readHost host="W1R2" url="10.11.0.218:3306" user="admin" password="Abc_123456" />
        </writeHost>
        <writeHost host="W2" url="10.11.0.217:3306" user="admin"
                   password="Abc_123456">
            <readHost host="W2R1" url="10.11.0.216:3306" user="admin" password="Abc_123456" />
            <readHost host="W2R2" url="10.11.0.218:3306" user="admin" password="Abc_123456" />
        </writeHost>
    </dataHost>

</mycat:schema>

rule.xml

Server.xml
<user name="admin" defaultAccount="true">
        <property name="password">Abc_123456</property>
        <property name="schemas">chinasoft</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>

    <user name="user">
        <property name="password">user</property>
        <property name="schemas">chinasoft</property>
        <property name="readOnly">true</property>
    </user>

第二个pxc集群的部署

 

[root@node6 ~]# cat /etc/my.cnf

[client]

socket=/var/lib/mysql/mysql.sock

 

[mysqld]

server-id=216

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=pxc-cluster02

wsrep_cluster_address=gcomm://10.11.0.216,10.11.0.217,10.11.0.218

wsrep_node_name=pxc6

wsrep_node_address=10.11.0.216

wsrep_sst_method=xtrabackup-v2

wsrep_sst_auth= admin:Abc_123456

pxc_strict_mode=ENFORCING

binlog_format=ROW

default_storage_engine=InnoDB

innodb_autoinc_lock_mode=2

 

 

************************************

 

[root@node7 ~]# cat /etc/my.cnf

[client]

socket=/var/lib/mysql/mysql.sock

 

[mysqld]

server-id=217

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=pxc-cluster02

wsrep_cluster_address=gcomm://10.11.0.216,10.11.0.217,10.11.0.218

wsrep_node_name=pxc7

wsrep_node_address=10.11.0.217

wsrep_sst_method=xtrabackup-v2

wsrep_sst_auth= admin:Abc_123456

pxc_strict_mode=ENFORCING

binlog_format=ROW

default_storage_engine=InnoDB

innodb_autoinc_lock_mode=2

 

************************************

 

[root@node8 ~]# cat /etc/my.cnf

[client]

socket=/var/lib/mysql/mysql.sock

 

[mysqld]

server-id=218

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=pxc-cluster02

wsrep_cluster_address=gcomm://10.11.0.216,10.11.0.217,10.11.0.218

wsrep_node_name=pxc8

wsrep_node_address=10.11.0.218

wsrep_sst_method=xtrabackup-v2

wsrep_sst_auth= admin:Abc_123456

pxc_strict_mode=ENFORCING

binlog_format=ROW

default_storage_engine=InnoDB

innodb_autoinc_lock_mode=2

 

 

# 启动第二个集群

[root@node6 ~]# systemctl start mysql@bootstrap.service

[root@node6 ~]# cat /var/log/mysqld.log |grep pass

2018-12-03T17:05:12.810880Z 1 [Note] A temporary password is generated for root@localhost: 6khOemMy,f.T

 

# 登录mysql重置root密码

alter user 'root'@'localhost' identified by 'root';

 

# 添加同步用户，启动从节点root密码和admin的账号信息就同步到了从节点

create user 'admin'@'localhost' identified by 'Abc_123456';

grant reload,lock tables,replication client,process on *.* to 'admin'@'localhost';

flush privileges;

 

# 启动从节点

systemctl start mysql

Mycat连接使用8066端口

使用mycat插入数据

use chinasoft;

select * from t_user;

 

insert into t_user(id,username,password,tel,locked)

values(

1,

'jack',

hex(AES_ENCRYPT('123456','helloworld')),

'13312345678',

false

);

插入两条数据，可以看到落到了不同的分片中

insert into t_user(id,username,password,tel,locked)

values(

2,

'tom',

hex(AES_ENCRYPT('123456','helloworld')),

'13312345678',

false

);

 

通过mycat可以查询汇总到一起