一.mysql读写分离:缓解主服务器的压力1.概念:主服务器写数据,从服务器读数据2.实现方法:客户端分离: 开发手动分离地址服务端分离: 数据库与应用之间加一个中间件,分离读写请求mysql-proxy,mysql-route,maxscaleamoeba,cobar,mycat2atlas,kingshard,vitees3.mycat配置方法:冷配置:直接改配置文件热配置:命令配置4.操作步骤主从-->users-->datasources-->cluster-->主备配置读写分离-->shecma配置逻辑库1.搭建主从服务器56,572.安装授权mycat,安装mysql(原型库,存储mycat运行的数据)mycat2-1.21-release-jar-with-dependencies.jar :运行依赖包mycat2-install-template-1.21.zip:软件包yum -y install java-1.8.0-openjdk.x86_64cp mycat2-1.21-release-jar-with-dependencies.jar /usr/local/mycat/lib/chmod +x /usr/local/mycat/bin/*chmod -R 777 /usr/local/mycat3.定义客户端连接mycat服务使用的用户及密码/usr/local/mycat/conf/users/root.user.json{"dialect":"mysql","ip":null,"password":"654321", 密码"transactionType":"proxy","username":"mycat" 用户名}4.配置本机用户和datasources数据源地址,mycat机器的mysql创建plj用户并授权/usr/local/mycat/conf/datasources/prototypeDs.data{...."password":"123456", 密码"type":"JDBC",...."user":"plj", 用户名"weight":0}----mysql> create user plj@"%" identified by "123456"; //创建plj用户mysql> grant all on *.* to plj@"%" ; //授予权限5.启动mycat服务并登录(8066)/usr/lcoal/mycat/bin/mycat startmysql -h127.0.0.1 -P8066 -umycat -p6543216.mycat下,配置主从的用户数据源地址,mysql56,主机器上创建主从数据源的用户并授权/*+ mycat: createdatasource{"name":"whost56", #master角色主机名"url":"jdbc:mysql://192.168.88.56:3306","user":"plja","password":"123456"} */;/*+ mycat: createdatasource{"name":"rhost57", #slave角色主机名"url":"jdbc:mysql://192.168.88.57:3306","user":"plja","password":"123456"} */;----mysql> create user plja@"%" identified by "123456"; //创建plj用户mysql> grant all on *.* to plja@"%" ; //授予权限热配置,用户/数据源/集群/逻辑库相关命令/*+ mycat: showusers{} / showdatasources{} / showschemas{} */;/*+ mycat: createuser{"name":"xx","password":"xx"...} / createdatasource{"name":"xx"} / createschema{"name":"xx"}*/;/*+ mycat: dropuser{"name":"xx"} /dropdatasource{"name":"xx"} /dropschema{"name":"xx"}*/;---集群/*+ mycat: showclusters{} */;/*! mycat: createcluster{"name":"xx"...} */;/*! mycat: dropcluster{"name":"xx"...} */;7.mycat下,主从服务器加入集群mysql -h127.0.0.1 -P8066 -umycat -p654321/*! mycat:createcluster{"name":"rwcluster","masters":["whost56"], #master角色主机名"replicas":["rhost57"] #slave角色主机名}*/ ;8.调整配置文件修改主从的读写操作(之前配置的name=这里的配置文件名称)vim /usr/local/mycat/conf/datasources/whost56.datasource.json"instanceType":"WRITE" #分配写vim /usr/local/mycat/conf/datasources/rhost57.datasource.json "instanceType":"READ" #分配读vim /usr/local/mycat/conf/clusters/rwcluster.cluster.json "instanceType":"BALANCE_ALL_READ" BALANCE_ALL:获取集群中所有数据源BALANCE_ALL_READ:读请求都会被均衡地分配到所有的从库BALANCE_READ_WRITE:获取集群中允许读写的数据,单允许读的数据优先BALANCE_NONE:获取集群中允许写的数据重启服务 /usr/local/mycat/bin/mycat restart9.登录mycat,创建testdb库,生成testdb.schema.json文件,修改配置文件,逻辑库关联集群真实库mysql -h127.0.0.1 -P8066 -umycat -p654321create database testdb;vim /usr/local/mycat/conf/schemas/testdb.schema.json"schemaName":"testdb","targetName":"rwcluster", #集群名称10.登录mycat,创建表,写记录测试,完成数据的读写分离mysql -h127.0.0.1 -P8066 -umycat -p654321create table testdb.user(name char(10),password char(10));insert into testdb.user values("yaya","123456");
2.mysql读写分离:缓解主服务器的压力
1.概念:主服务器写数据,从服务器读数据
2.实现方法:
客户端分离: 开发手动分离地址
服务端分离: 数据库与应用之间加一个中间件,分离读写请求
mysql-proxy,mysql-route,maxscale
amoeba,cobar,mycat2
atlas,kingshard,vitees
3.mycat配置方法:
冷配置:直接改配置文件
热配置:命令配置
4.操作步骤
主从-->users-->datasources-->cluster-->主备配置读写分离-->shecma配置逻辑库
1.搭建主从服务器56,57
2.安装授权mycat,安装mysql(原型库,存储mycat运行的数据)
mycat2-1.21-release-jar-with-dependencies.jar :运行依赖包
mycat2-install-template-1.21.zip:软件包
yum -y install java-1.8.0-openjdk.x86_64
cp mycat2-1.21-release-jar-with-dependencies.jar /usr/local/mycat/lib/
chmod +x /usr/local/mycat/bin/*
chmod -R 777 /usr/local/mycat
3.定义客户端连接mycat服务使用的用户及密码
/usr/local/mycat/conf/users/root.user.json
{
"dialect":"mysql",
"ip":null,
"password":"654321", 密码
"transactionType":"proxy",
"username":"mycat" 用户名
}
4.配置本机用户和datasources数据源地址,mycat机器的mysql创建plj用户并授权
/usr/local/mycat/conf/datasources/prototypeDs.data
{
....
"password":"123456", 密码
"type":"JDBC",
....
"user":"plj", 用户名
"weight":0
}
----
mysql> create user plj@"%" identified by "123456"; //创建plj用户
mysql> grant all on *.* to plj@"%" ; //授予权限
5.启动mycat服务并登录(8066)
/usr/lcoal/mycat/bin/mycat start
mysql -h127.0.0.1 -P8066 -umycat -p654321
6.mycat下,配置主从的用户数据源地址,mysql56,主机器上创建主从数据源的用户并授权
/*+ mycat: createdatasource{
"name":"whost56", #master角色主机名
"url":"jdbc:mysql://192.168.88.56:3306",
"user":"plja",
"password":"123456"
} */;
/*+ mycat: createdatasource{
"name":"rhost57", #slave角色主机名
"url":"jdbc:mysql://192.168.88.57:3306",
"user":"plja",
"password":"123456"
} */;
----
mysql> create user plja@"%" identified by "123456"; //创建plj用户
mysql> grant all on *.* to plja@"%" ; //授予权限
热配置,用户/数据源/集群/逻辑库相关命令
/*+ mycat: showusers{} / showdatasources{} / showschemas{} */;
/*+ mycat: createuser{"name":"xx","password":"xx"...} / createdatasource{"name":"xx"} / createschema{"name":"xx"}*/;
/*+ mycat: dropuser{"name":"xx"} /dropdatasource{"name":"xx"} /dropschema{"name":"xx"}*/;
---集群
/*+ mycat: showclusters{} */;
/*! mycat: createcluster{"name":"xx"...} */;
/*! mycat: dropcluster{"name":"xx"...} */;
7.mycat下,主从服务器加入集群
mysql -h127.0.0.1 -P8066 -umycat -p654321
/*! mycat:createcluster{
"name":"rwcluster",
"masters":["whost56"], #master角色主机名
"replicas":["rhost57"] #slave角色主机名
}*/ ;
8.调整配置文件修改主从的读写操作(之前配置的name=这里的配置文件名称)
vim /usr/local/mycat/conf/datasources/whost56.datasource.json
"instanceType":"WRITE" #分配写
vim /usr/local/mycat/conf/datasources/rhost57.datasource.json
"instanceType":"READ" #分配读
vim /usr/local/mycat/conf/clusters/rwcluster.cluster.json
"instanceType":"BALANCE_ALL_READ"
BALANCE_ALL:获取集群中所有数据源
BALANCE_ALL_READ:读请求都会被均衡地分配到所有的从库
BALANCE_READ_WRITE:获取集群中允许读写的数据,单允许读的数据优先
BALANCE_NONE:获取集群中允许写的数据
重启服务 /usr/local/mycat/bin/mycat restart
9.登录mycat,创建testdb库,生成testdb.schema.json文件,修改配置文件,逻辑库关联集群真实库
mysql -h127.0.0.1 -P8066 -umycat -p654321
create database testdb;
vim /usr/local/mycat/conf/schemas/testdb.schema.json
"schemaName":"testdb",
"targetName":"rwcluster", #集群名称
10.登录mycat,创建表,写记录测试,完成数据的读写分离
mysql -h127.0.0.1 -P8066 -umycat -p654321
create table testdb.user(name char(10),password char(10));
insert into testdb.user values("yaya","123456");
