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版本
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可以查询汇总到一起
