mysql pxc 表分片_03中间件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版本

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

select user()

select user()

rule.xml

Server.xml

Abc_123456

chinasoft

user

chinasoft

true

第二个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可以查询汇总到一起