网站定制化价格,海拉尔网站设计,中国红河网,dux2.0支持Wordpress环境准备
先准备一套MySQL主从服务器#xff0c;可参考MySQL主从配置配置MyCat服务
资源下载
网盘链接: https://pan.baidu.com/s/1cLTMH_e1-6loc_gF9ZNHTg?pwda63n 提取码: a63n
MyCat配置
# 1#xff09;安装mycat软件
//安装jdk
[rootmycat58 upload]# yum -y insta…环境准备
先准备一套MySQL主从服务器可参考MySQL主从配置配置MyCat服务
资源下载
网盘链接: https://pan.baidu.com/s/1cLTMH_e1-6loc_gF9ZNHTg?pwda63n 提取码: a63n
MyCat配置
# 1安装mycat软件
//安装jdk
[rootmycat58 upload]# yum -y install java-1.8.0-openjdk.x86_64
//安装解压命令
[rootmycat58 upload]# which unzip || yum -y install unzip
//安装mycat
[rootmycat58 upload]# unzip mycat2-install-template-1.21.zip
[rootmycat58 upload]# mv mycat /usr/local/
//安装依赖
[rootmycat58 upload]# cp mycat2-1.21-release-jar-with-dependencies.jar /usr/local/mycat/lib/
//修改权限
[rootmycat58 upload]# chmod -R 777 /usr/local/mycat/
# 2) 定义客户端连接mycat服务使用用户及密码
[rootmycat58 ~]# vim /usr/local/mycat/conf/users/root.user.json
{dialect:mysql,ip:null,password:654321, 密码transactionType:proxy,username:mycat 用户名
}
# 定义连接的数据库服务器
[rootmycat58 ~]# vim /usr/local/mycat/conf/datasources/prototypeDs.data
{dbType:mysql,idleTimeout:60000,initSqls:[],initSqlsGetConnection:true,instanceType:READ_WRITE,maxCon:1000,maxConnectTimeout:3000,maxRetryCount:5,minCon:1,name:prototypeDs,password:123456, 密码type:JDBC,url:jdbc:mysql://localhost:3306/mysql?useUnicodetrueserverTimezoneAsia/ShanghaicharacterEncodingUTF-8, 连接本机的数据库服务user:plj, 用户名weight:0
}
# 3在mycat58主机运行数据库服务
[rootmycat58 ~]# yum -y install mysql-server mysql 安装软件
[rootmycat58 ~]# systemctl start mysqld 启动服务
[rootmycat58 ~]# mysql 连接服务
mysql create user plj% identified by 123456; 创建plj用户
Query OK, 0 rows affected (0.05 sec)
mysql grant all on *.* to plj% ; 授予权限
Query OK, 0 rows affected (0.39 sec)
# 4启动mycat服务
[rootmycat58 ~]# /usr/local/mycat/bin/mycat help
Usage: /usr/local/mycat/bin/mycat { console | start | stop | restart | status | dump }
[rootmycat58 ~]# /usr/local/mycat/bin/mycat start
Starting mycat2...
# 半分钟左右 能看到端口
[rootmycat58 ~]# netstat -utnlp | grep 8066
tcp6 0 0 :::8066 :::* LISTEN 57015/java
# 4连接mycat服务
[rootmycat58 ~]# mysql -h127.0.0.1 -P8066 -umycat -p654321
mysql show databases;
--------------------
| Database |
--------------------
| information_schema |
| mysql |
| performance_schema |
--------------------
3 rows in set (0.11 sec)配置读写分离
# 连接mycat服务
[rootmycat58 ~]# mysql -h127.0.0.1 -P8066 -umycat -p654321
# 添加mysql53数据库服务器
MySQL /* mycat:createdatasource{
name:whost56, url:jdbc:mysql://192.168.88.53:3306,user:plja,password:123456}*/;
Query OK, 0 rows affected (0.25 sec)
# 添加mysql54数据库服务器
Mysql/* mycat:createdatasource{
name:rhost57, url:jdbc:mysql://192.168.88.54:3306,user:plja,password:123456}*/;
# 查看数据源
mysql /* mycat:showDataSources{}*/\G
*************************** 1. row ***************************NAME: whost56USERNAME: pljaPASSWORD: 123456MAX_CON: 1000MIN_CON: 1EXIST_CON: 0USE_CON: 0MAX_RETRY_COUNT: 5MAX_CONNECT_TIMEOUT: 30000DB_TYPE: mysqlURL: jdbc:mysql://192.168.88.53:3306?serverTimezoneAsia/ShanghaiuseUnicodetruecharacterEncodingUTF-8autoReconnecttrueWEIGHT: 0INIT_SQL:
INIT_SQL_GET_CONNECTION: trueINSTANCE_TYPE: WRITEIDLE_TIMEOUT: 60000DRIVER: {CreateTime:2023-09-15 09:05:48,ActiveCount:0,PoolingCount:1,CreateCount:1,DestroyCount:0,CloseCount:7,ConnectCount:7,Connections:[{ID:225590323, ConnectTime:2023-09-15 09:05:49, UseCount:7, LastActiveTime:2023-09-15 09:11:13, LastKeepTimeMillis:2023-09-15 11:45:49}]
}TYPE: JDBCIS_MYSQL: true
*************************** 2. row ***************************NAME: rhost57USERNAME: pljaPASSWORD: 123456MAX_CON: 1000MIN_CON: 1EXIST_CON: 0USE_CON: 0MAX_RETRY_COUNT: 5MAX_CONNECT_TIMEOUT: 30000DB_TYPE: mysqlURL: jdbc:mysql://192.168.88.54:3306?serverTimezoneAsia/ShanghaiuseUnicodetruecharacterEncodingUTF-8autoReconnecttrueWEIGHT: 0INIT_SQL:
INIT_SQL_GET_CONNECTION: trueINSTANCE_TYPE: READIDLE_TIMEOUT: 60000DRIVER: {CreateTime:2023-09-15 09:05:48,ActiveCount:0,PoolingCount:1,CreateCount:1,DestroyCount:0,CloseCount:1,ConnectCount:1,Connections:[{ID:790341182, ConnectTime:2023-09-15 09:09:50, UseCount:1, LastActiveTime:2023-09-15 09:09:50, LastKeepTimeMillis:2023-09-15 11:45:50}]
}TYPE: JDBCIS_MYSQL: true
*************************** 3. row ***************************NAME: prototypeDsUSERNAME: pljPASSWORD: 123456MAX_CON: 1000MIN_CON: 1EXIST_CON: 0USE_CON: 0MAX_RETRY_COUNT: 5MAX_CONNECT_TIMEOUT: 3000DB_TYPE: mysqlURL: jdbc:mysql://127.0.0.1:3306/mysql?serverTimezoneAsia/ShanghaiuseUnicodetruecharacterEncodingUTF-8autoReconnecttrueWEIGHT: 0INIT_SQL:
INIT_SQL_GET_CONNECTION: trueINSTANCE_TYPE: READ_WRITEIDLE_TIMEOUT: 60000DRIVER: {CreateTime:2023-09-15 09:05:48,ActiveCount:0,PoolingCount:1,CreateCount:2,DestroyCount:1,CloseCount:44,ConnectCount:44,Connections:[{ID:995139711, ConnectTime:2023-09-15 09:05:49, UseCount:6, LastActiveTime:2023-09-15 09:06:51, LastKeepTimeMillis:2023-09-15 11:45:48}]
}TYPE: JDBCIS_MYSQL: true
3 rows in set (0.01 sec)
# 添加的数据源以文件的形式保存在安装目录下
[rootmycat58 conf]# ls /usr/local/mycat/conf/datasources/
prototypeDs.datasource.json rhost57.datasource.json whost56.datasource.json
# 在53服务器上配置数据库服务器添加plja用户
# 在master服务器添加
[rootmysql56 ~]# mysql
mysql create user plja% identified by 123456;
Query OK, 0 rows affected (0.06 sec)
mysql grant all on *.* to plja%;
Query OK, 0 rows affected (0.03 sec)
mysqlexit
[rootmysql56 ~]#
# 在slave服务器查看是否同步成功
[rootmysql57 ~]# mysql -e select user , host from mysql.user where userplja
------------
| user | host |
------------
| plja | % |
------------
# 创建集群,连接mycat服务后做如下配置
[rootmycat58 ~]# mysql -h127.0.0.1 -P8066 -umycat -p654321
//创建集群
mysql/*!mycat:createcluster{
name:rwcluster,
masters:[whost56],
replicas:[rhost57]
}*/ ;
mysql /* mycat:showClusters{}*/\G
*************************** 1. row ***************************NAME: rwclusterSWITCH_TYPE: SWITCH
MAX_REQUEST_COUNT: 2000TYPE: BALANCE_ALL_READWRITE_DS: whost56READ_DS: rhost57WRITE_L: io.mycat.plug.loadBalance.BalanceRandom$1READ_L: io.mycat.plug.loadBalance.BalanceRandom$1AVAILABLE: true
*************************** 2. row ***************************NAME: prototypeSWITCH_TYPE: SWITCH
MAX_REQUEST_COUNT: 200TYPE: BALANCE_ALLWRITE_DS: prototypeDsREAD_DS: prototypeDsWRITE_L: io.mycat.plug.loadBalance.BalanceRandom$1READ_L: io.mycat.plug.loadBalance.BalanceRandom$1AVAILABLE: true
2 rows in set (0.01 sec)
# 创建的集群以文件的形式保存在目录下
[rootmycat58 conf]# ls /usr/local/mycat/conf/clusters/
prototype.cluster.json rwcluster.cluster.json
# 指定主机角色
//修改master角色主机仅负责写访问
[rootmycat58 ~]# vim /usr/local/mycat/conf/datasources/whost56.datasource.json
{dbType:mysql,idleTimeout:60000,initSqls:[],initSqlsGetConnection:true,instanceType:WRITE, 仅负责写访问logAbandoned:true,maxCon:1000,maxConnectTimeout:30000,maxRetryCount:5,minCon:1,name:whost56,password:123456,queryTimeout:0,removeAbandoned:false,removeAbandonedTimeoutSecond:180,type:JDBC,url:jdbc:mysql://192.168.88.56:3306?serverTimezoneAsia/ShanghaiuseUnicodetruecharacterEncodingUTF-8autoReconnecttrue,user:plja,weight:0
}
# 修改slave角色主机仅负责读访问
[rootmycat58 ~]# vim /usr/local/mycat/conf/datasources/rhost57.datasource.json
{dbType:mysql,idleTimeout:60000,initSqls:[],initSqlsGetConnection:true,instanceType:READ,仅负责读访问logAbandoned:true,maxCon:1000,maxConnectTimeout:30000,maxRetryCount:5,minCon:1,name:rhost57,password:123456,queryTimeout:0,removeAbandoned:false,removeAbandonedTimeoutSecond:180,type:JDBC,url:jdbc:mysql://192.168.88.57:3306?serverTimezoneAsia/ShanghaiuseUnicodetruecharacterEncodingUTF-8autoReconnecttrue,user:plja,weight:0
}
# 修改读策略
[rootmycat58 ~]# vim /usr/local/mycat/conf/clusters/rwcluster.cluster.json
{clusterType:MASTER_SLAVE,heartbeat:{heartbeatTimeout:1000,maxRetryCount:3,minSwitchTimeInterval:300,showLog:false,slaveThreshold:0.0},masters:[whost56],maxCon:2000,name:rwcluster,readBalanceType:BALANCE_ALL_READ,replicas:[rhost57],switchType:SWITCH
}
# 重启mycat服务
[rootmycat58 ~]# /usr/local/mycat/bin/mycat restart
Stopping mycat2...
Stopped mycat2.
Starting mycat2...测试配置
1.连接mycat服务建库
2.指定存储数据使用的集群
3.连接mycat服务建表
4.客户端连接mycat服务执行select 或 insert[rootmycat58 ~]# mysql -h127.0.0.1 -P8066 -umycat -p654321
mysql create database testdb;
Query OK, 0 rows affected (0.30 sec)
mysql exit
Bye
//指定testdb库存储数据使用的集群
[rootmycat58 ~]# vim /usr/local/mycat/conf/schemas/testdb.schema.json
{customTables:{},globalTables:{},normalProcedures:{},normalTables:{},schemaName:testdb,targetName:rwcluster, 添加此行,之前创建的集群名rwclustershardingTables:{},views:{}
}[rootmycat58 ~]# /usr/local/mycat/bin/mycat restart
//连接mycat服务建表插入记录
[rootclient50 ~]# mysql -h192.168.88.58 -P8066 -umycat -p654321
mysql create table testdb.user (name varchar(10) , password varchar(10));
Query OK, 0 rows affected (0.45 sec)
mysql insert into testdb.user values(yaya,123456);
Query OK, 1 row affected (0.20 sec)
mysql select * from testdb.user;
----------------
| name | password |
----------------
| yaya | 123456 |
----------------
1 row in set (0.01 sec)
# 测试读写分离
# 在从服务器本机插入记录数据仅在从服务器有主服务器没有
[rootmysql54 ~]# mysql -e insert into testdb.user values (yayaA,654321)
[rootmysql54 ~]# mysql -e select * from testdb.user
-----------------
| name | password |
-----------------
| yaya | 123456 |
| yayaA | 654321 |
-----------------
# 主服务器数据不变日志偏移量不不变
[rootmysql53 ~]# mysql -e select * from testdb.user
----------------
| name | password |
----------------
| yaya | 123456 |
----------------
[rootmysql53 ~]# mysql -e show master status
-----------------------------------------------------------------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
-----------------------------------------------------------------------------
| mysql56.000002 | 4514 | | | |
-----------------------------------------------------------------------------
[rootmysql53 ~]#
# 客户端连接mycat服务读/写数据
[rootclient50 ~]# mysql -h192.168.88.58 -P8066 -umycat -p654321
mysql select * from testdb.user; 查看到的是2条记录的行
-----------------
| name | password |
-----------------
| yaya | 123456 |
| yayaA | 654321 |
-----------------
2 rows in set (0.04 sec)
mysql insert into testdb.user values(yayaB,123456); 插入记录
Query OK, 1 row affected (0.06 sec)
mysql select * from testdb.user;
-----------------
| name | password |
-----------------
| yaya | 123456 |
| yayaB | 123456 |
-----------------
2 rows in set (0.01 sec)
mysql
# 在主服务器查看数据和日志偏移量
[rootmysql53 ~]# mysql -e select * from testdb.user
-----------------
| name | password |
-----------------
| yaya | 123456 |
| yayaB | 123456 |
-----------------
[rootmysql53 ~]# mysql -e show master status
-----------------------------------------------------------------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
-----------------------------------------------------------------------------
| mysql56.000002 | 4807 | | | |
-----------------------------------------------------------------------------
# 客户端连接mycat服务查看到的是3条记录
[rootclient50 ~]# mysql -h192.168.88.58 -P8066 -umycat -p654321 -e select * from testdb.user
mysql: [Warning] Using a password on the command line interface can be insecure.
-----------------
| name | password |
-----------------
| yaya | 123456 |
| yayaA | 654321 |
| yayaB | 123456 |
-----------------
