3.3 步骤
实现此案例需要按照如下步骤进行。
步骤一:搭建一主一从结构
因为数据的查询和存储分别访问不同的数据库服务器,所以要通过主从同步来保证负责读访问的服务与负责写访问的服务器数据一致。
1)配置主数据库服务器
- [root@mysql56 ~]# yum -y install mysql-server mysql
- [root@mysql56 ~]# systemctl start mysqld
- //启用binlog日志
- [root@mysql56 ~]# vim /etc/my.cnf.d/mysql-server.cnf
- [mysqld]
- server-id=56
- log-bin=mysql56
- :wq
- [root@mysql56 ~]# systemctl restart mysqld
- //用户授权
- [root@mysql56 ~]# mysql
- mysql> create user repluser@"%" identified by "123qqq...A";
- Query OK, 0 rows affected (0.11 sec)
- mysql> grant replication slave on *.* to repluser@"%" ;
- Query OK, 0 rows affected (0.08 sec)
- //查看日志信息
- mysql> show master status;
- +----------------+----------+--------------+------------------+-------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
- +----------------+----------+--------------+------------------+-------------------+
- | mysql56.000001 | 667 | | | |
- +----------------+----------+--------------+------------------+-------------------+
- 1 row in set (0.00 sec)
2)配置从数据库服务器
2)安装mycat软件
3)定义客户端连接mycat服务使用用户及密码:
5)在mycat58主机运行数据库服务
6)启动mycat服务
7) 连接mycat服务
步骤三:配置读写分离
1)添加数据源:连接mycat服务后做如下操作
2)配置数据库服务器添加plja用户
3)创建集群,连接mycat服务后做如下配置:
4)指定主机角色
5)修改读策略
- //指定server-id 并重启数据库服务
- [root@mysql57 ~]# yum -y install mysql-server mysql
- [root@mysql57 ~]# systemctl start mysqld
- [root@mysql57 ~]# vim /etc/my.cnf.d/mysql-server.cnf
- [mysqld]
- server-id=57
- :wq
- [root@mysql57 ~]# systemctl restart mysqld
- //管理员登陆,指定主服务器信息
- [root@mysql57 ~]# mysql
- mysql> change master to master_host="192.168.88.56" , master_user="repluser" , master_password="123qqq...A" , master_log_file="mysql56.000001",master_log_pos=667;
- Query OK, 0 rows affected, 8 warnings (0.54 sec)
- //启动slave进程
- mysql> start slave;
- Query OK, 0 rows affected, 1 warning (0.03 sec)
- //查看状态信息
- mysql> show slave status \G
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for source to send event
- Master_Host: 192.168.88.56
- Master_User: repluser
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: mysql56.000001
- Read_Master_Log_Pos: 667
- Relay_Log_File: mysql57-relay-bin.000002
- Relay_Log_Pos: 322
- Relay_Master_Log_File: mysql56.000001
- Slave_IO_Running: Yes //IO线程
- Slave_SQL_Running: Yes //SQL线程
- Replicate_Do_DB:
- Replicate_Ignore_DB:
- Replicate_Do_Table:
- Replicate_Ignore_Table:
- Replicate_Wild_Do_Table:
- Replicate_Wild_Ignore_Table:
- Last_Errno: 0
- Last_Error:
- Skip_Counter: 0
- Exec_Master_Log_Pos: 667
- Relay_Log_Space: 533
- Until_Condition: None
- Until_Log_File:
- Until_Log_Pos: 0
- Master_SSL_Allowed: No
- Master_SSL_CA_File:
- Master_SSL_CA_Path:
- Master_SSL_Cert:
- Master_SSL_Cipher:
- Master_SSL_Key:
- Seconds_Behind_Master: 0
- Master_SSL_Verify_Server_Cert: No
- Last_IO_Errno: 0
- Last_IO_Error:
- Last_SQL_Errno: 0
- Last_SQL_Error:
- Replicate_Ignore_Server_Ids:
- Master_Server_Id: 56
- Master_UUID: e0ab8dc4-0109-11ee-87e7-525400ad7ed3
- Master_Info_File: mysql.slave_master_info
- SQL_Delay: 0
- SQL_Remaining_Delay: NULL
- Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
- Master_Retry_Count: 86400
- Master_Bind:
- Last_IO_Error_Timestamp:
- Last_SQL_Error_Timestamp:
- Master_SSL_Crl:
- Master_SSL_Crlpath:
- Retrieved_Gtid_Set:
- Executed_Gtid_Set:
- Auto_Position: 0
- Replicate_Rewrite_DB:
- Channel_Name:
- Master_TLS_Version:
- Master_public_key_path:
- Get_master_public_key: 0
- Network_Namespace:
- 1 row in set, 1 warning (0.00 sec)
步骤二:配置mycat服务器
1)拷贝软件到mycat58主机
- [root@server1 ~]# scp /linux-soft/s3/mycat2-1.21-release-jar-with-dependencies.jar root@192.168.88.58:/root/
- [root@server1 ~]# scp /linux-soft/s3/mycat2-install-template-1.21.zip root@192.168.88.58:/root/
- //安装jdk
- [root@mycat58 upload]# yum -y install java-1.8.0-openjdk.x86_64
- //安装解压命令
- [root@mycat58 upload]# which unzip || yum -y install unzip
- //安装mycat
- [root@mycat58 upload]# unzip mycat2-install-template-1.21.zip
- [root@mycat58 upload]# mv mycat /usr/local/
- //安装依赖
- [root@mycat58 upload]# cp mycat2-1.21-release-jar-with-dependencies.jar /usr/local/mycat/lib/
- //修改权限
- [root@mycat58 upload]# chmod -R 777 /usr/local/mycat/
- [root@mycat58 ~]# vim /usr/local/mycat/conf/users/root.user.json
- {
- "dialect":"mysql",
- "ip":null,
- "password":"654321", 密码
- "transactionType":"proxy",
- "username":"mycat" 用户名
- }
- :wq
- 定义连接的数据库服务器
- [root@mycat58 ~]# 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?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8", 连接本机的数据库服务
- "user":"plj", 用户名
- "weight":0
- }
- :wq
- [root@mycat58 ~]# yum -y install mysql-server mysql 安装软件
- [root@mycat58 ~]# systemctl start mysqld 启动服务
- [root@mycat58 ~]# 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)
- mysql> exit 断开连接
- Bye
- [root@mycat58 ~]#
- [root@mycat58 ~]# /usr/local/mycat/bin/mycat help
- Usage: /usr/local/mycat/bin/mycat { console | start | stop | restart | status | dump }
- [root@mycat58 ~]# /usr/local/mycat/bin/mycat start
- Starting mycat2...
- //半分钟左右 能看到端口
- [root@mycat58 ~]# netstat -utnlp | grep 8066
- tcp6 0 0 :::8066 :::* LISTEN 57015/java
- [root@mycat58 ~]#
- [root@mycat58 ~]# mysql -h127.0.0.1 -P8066 -umycat -p654321
- mysql> show databases;
- +--------------------+
- | `Database` |
- +--------------------+
- | information_schema |
- | mysql |
- | performance_schema |
- +--------------------+
- 3 rows in set (0.11 sec)
- Mysql>
- //连接mycat服务
- [root@mycat58 ~]# mysql -h127.0.0.1 -P8066 -umycat -p654321
- //添加mysql56数据库服务器
- MySQL> /*+ mycat:createdatasource{
- "name":"whost56", "url":"jdbc:mysql://192.168.88.56:3306","user":"plja","password":"123456"}*/;
- Query OK, 0 rows affected (0.25 sec)
- //添加mysql57数据库服务器
- Mysql>/*+ mycat:createdatasource{
- "name":"rhost57", "url":"jdbc:mysql://192.168.88.57:3306","user":"plja","password":"123456"}*/;
- //查看数据源
- mysql> /*+mycat:showDataSources{}*/ \G
- *************************** 1. row ***************************
- NAME: whost56
- USERNAME: plja
- PASSWORD: 123456
- MAX_CON: 1000
- MIN_CON: 1
- EXIST_CON: 0
- USE_CON: 0
- MAX_RETRY_COUNT: 5
- MAX_CONNECT_TIMEOUT: 30000
- DB_TYPE: mysql
- URL: jdbc:mysql://192.168.88.56:3306?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true
- WEIGHT: 0
- INIT_SQL:
- INIT_SQL_GET_CONNECTION: true
- INSTANCE_TYPE: READ_WRITE
- IDLE_TIMEOUT: 60000
- DRIVER: {
- CreateTime:"2023-06-02 17:01:14",
- ActiveCount:0,
- PoolingCount:0,
- CreateCount:0,
- DestroyCount:0,
- CloseCount:0,
- ConnectCount:0,
- Connections:[
- ]
- }
- TYPE: JDBC
- IS_MYSQL: true
- *************************** 2. row ***************************
- NAME: rhost57
- USERNAME: plja
- PASSWORD: 123456
- MAX_CON: 1000
- MIN_CON: 1
- EXIST_CON: 0
- USE_CON: 0
- MAX_RETRY_COUNT: 5
- MAX_CONNECT_TIMEOUT: 30000
- DB_TYPE: mysql
- URL: jdbc:mysql://192.168.88.57:3306?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8&autoReconnect=true
- WEIGHT: 0
- INIT_SQL:
- INIT_SQL_GET_CONNECTION: true
- INSTANCE_TYPE: READ_WRITE
- IDLE_TIMEOUT: 60000
- DRIVER: {
- CreateTime:"2023-06-02 17:01:14",
- ActiveCount:0,
- PoolingCount:0,
- CreateCount:0,
- DestroyCount:0,
- CloseCount:0,
- ConnectCount:0,
- Connections:[
- ]
- }
- TYPE: JDBC
- IS_MYSQL: true
- *************************** 3. row ***************************
- NAME: prototypeDs
- USERNAME: plj
- PASSWORD: 123456
- MAX_CON: 1000
- MIN_CON: 1
- EXIST_CON: 0
- USE_CON: 0
- MAX_RETRY_COUNT: 5
- MAX_CONNECT_TIMEOUT: 3000
- DB_TYPE: mysql
- URL: jdbc:mysql://localhost:3306/mysql?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true
- WEIGHT: 0
- INIT_SQL:
- INIT_SQL_GET_CONNECTION: true
- INSTANCE_TYPE: READ_WRITE
- IDLE_TIMEOUT: 60000
- DRIVER: {
- CreateTime:"2023-06-02 17:01:14",
- ActiveCount:0,
- PoolingCount:0,
- CreateCount:0,
- DestroyCount:0,
- CloseCount:0,
- ConnectCount:0,
- Connections:[
- ]
- }
- TYPE: JDBC
- IS_MYSQL: true
- 3 rows in set (0.00 sec)
- mysql>
- //添加的数据源以文件的形式保存在安装目录下
- [root@mycat58 conf]# ls /usr/local/mycat/conf/datasources/
- prototypeDs.datasource.json rhost57.datasource.json whost56.datasource.json
- [root@mycat58 conf]#
- //在master服务器添加
- [root@mysql56 ~]# 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)
- mysql>exit
- [root@mysql56 ~]#
- //在slave服务器查看是否同步成功
- [root@mysql57 ~]# mysql -e 'select user , host from mysql.user where user="plja"'
- +------+------+
- | user | host |
- +------+------+
- | plja | % |
- +------+------+
- [root@mysql57 ~]#
- [root@mycat58 ~]# mysql -h127.0.0.1 -P8066 -umycat -p654321
- //创建集群
- mysql>/*!mycat:createcluster{
- "name":"rwcluster",
- "masters":["whost56"],
- "replicas":["rhost57"]
- }*/ ;
- Mysql>
- //查看集群信息
- mysql> /*+ mycat:showClusters{}*/ \G
- *************************** 1. row ***************************
- NAME: rwcluster
- SWITCH_TYPE: SWITCH
- MAX_REQUEST_COUNT: 2000
- TYPE: BALANCE_ALL
- WRITE_DS: whost56
- READ_DS: whost56,rhost57
- WRITE_L: io.mycat.plug.loadBalance.BalanceRandom$1
- READ_L: io.mycat.plug.loadBalance.BalanceRandom$1
- AVAILABLE: true
- *************************** 2. row ***************************
- NAME: prototype
- SWITCH_TYPE: SWITCH
- MAX_REQUEST_COUNT: 200
- TYPE: BALANCE_ALL
- WRITE_DS: prototypeDs
- READ_DS: prototypeDs
- WRITE_L: io.mycat.plug.loadBalance.BalanceRandom$1
- READ_L: io.mycat.plug.loadBalance.BalanceRandom$1
- AVAILABLE: true
- 2 rows in set (0.00 sec)
- mysql>
- //创建的集群以文件的形式保存在目录下
- [root@mycat58 conf]# ls /usr/local/mycat/conf/clusters/
- prototype.cluster.json rwcluster.cluster.json
- [root@mycat58 conf]#
- //修改master角色主机仅负责写访问
- [root@mycat58 ~]# 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?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true",
- "user":"plja",
- "weight":0
- }
- :wq
- //修改slave角色主机仅负责读访问
- [root@mycat58 ~]# 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?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true",
- "user":"plja",
- "weight":0
- }
- :wq
- [root@mycat58 ~]# 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"
- }
- :wq
- //重启mycat服务
- [root@mycat58 ~]# /usr/local/mycat/bin/mycat restart
- Stopping mycat2...
- Stopped mycat2.
- Starting mycat2...
- [root@mycat58 ~]#