MySQL主从读写分离搭建-ShardingSphere-Proxy
MySQL主从读写分离搭建搭建MySQL主从数据库准备配置MySQL主数据库配置MySQL从数据库验证主从数据库搭建ShardingSphere-Proxy，实现数据库读写分离准备配置ShardingSphere-Proxy运行ShardingSphere-Proxy连接数据库进行测试
搭建MySQL主从数据库
准备
下载MySQL免安装版本：mysql-5.7.32-winx64.zip
解压两份mysql-5.7.32-winx64.zip，分别到目录：MySQL主数据库目录D:\Other\mysql-5.7.32-winx64和MySQL从数据库目录D:\Other\mysql-5.7.32-winx64-slave
配置MySQL主数据库
进入D:\Other\mysql-5.7.32-winx64目录，创建my.ini文件，文件内容如下：
[mysql]
default-character-set=utf8mb4
[mysqld]
# 设置端口
port=3506
# 设置mysql的安装目录
basedir=D:/Other/mysql-5.7.32-winx64
# 设置mysql的数据存放目录
datadir=D:/Other/mysql-5.7.32-winx64/data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的Latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
performance_schema_max_table_instances=400
table_definition_cache=400
table_open_cache=256
## 永久设置时区时间
default-time_zone = '+8:00'
# 设置服务器唯一的id，默认是1，我们设置ip最后一段，slave设置203，不能和canal的slaveId重复
server-id=202
# 启用二进制日志
log-bin=mysql-bin
# 选择ROW（行）模式
binlog-format=ROW
# 忽略写入binlog的库
binlog-ignore-db = mysql,information_schema初始化一个新的数据目录：D:\Other\mysql-5.7.32-winx64\bin执行：
mysqld --initialize该命令将会在数据目录下生成一个“.err”文件，一般为计算机名，该文件用于记录命令的执行日志，日志中会打印随机生成的root@localhost用户的临时密码。
或者执行如下命令：
mysqld --initialize --console该命令会命令日志打印到控制台上。
或者
mysqld --initialize-insecure该命令将设置一个空的root@localhost用户密码.
启动MySQL主数据库：
D:\Other\mysql-5.7.32-winx64\bin\mysqld.exe --defaults-file=D:\Other\mysql-5.7.32-winx64\my.ini控制台登录MySQL主数据库，进入D:\Other\mysql-5.7.32-winx64\bin目录，并执行：
mysql -uroot -hlocalhost -P3506创建主从同步用户slave，密码为：password123
CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'password123';向slave用户授予主从同步权限：
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';刷新权限：
flush privileges;查看关键信息
查看两个关键信息，File和Position字段的值后面将会用到，在后面的操作完成之前，需要保证主数据库不能做任何操作，否则将会引起状态变化，File和Position字段的值变化。
show master status;结果如下图：
退出主数据库：
exit
配置MySQL从数据库
进入D:\Other\mysql-5.7.32-winx64-slave目录，创建my.ini文件，文件内容如下：
[mysql]
default-character-set=utf8mb4
[mysqld]
# 设置端口
port=3507
# 设置mysql的安装目录
basedir=D:/Other/mysql-5.7.32-winx64-slave
# 设置mysql的数据存放目录
datadir=D:/Other/mysql-5.7.32-winx64-slave/data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的Latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
performance_schema_max_table_instances=400
table_definition_cache=400
table_open_cache=256
# 永久设置时区时间
default-time_zone = '+8:00'
# 设置服务器唯一的id，默认是1，我们设置ip最后一段，slave设置203
server-id=203
# 启用二进制日志
log-bin=mysql-bin
# 忽略因复制出现的所有错误
slave-skip-errors = all注意：从数据库的log-bin值mysql-bin必须和主数据库的值保持一致。
初始化一个新的数据目录：D:\Other\mysql-5.7.32-winx64-slave\bin执行：
mysqld --initialize-insecure启动MySQL从数据库：
D:\Other\mysql-5.7.32-winx64-slave\bin\mysqld.exe --defaults-file=D:\Other\mysql-5.7.32-winx64-slave\my.ini配置从数据库同步：
进入D:\Other\mysql-5.7.32-winx64-slave\bin目录，执行命令登录从数据库：
mysql -uroot -hlocalhost -P3507执行主从同步命令：
change master to master_host='localhost', master_port=3506, master_user='slave', master_password='password123', master_log_file='mysql-bin.000002', master_log_pos= 400;其中master_log_file和master_log_pos的值来自主数据库命令
show master status;的查询结果。查看主从同步状态：
show slave status \G;可以看到Slave_IO_Running和Slave_SQL_Running都是No，因为我们还没有开启主从复制过程。
开启主从复制：
start slave;再次执行
show slave status \G;就会发现Slave_IO_Running和Slave_SQL_Running的值已经是Yes了，说明主从复制已经开启。停止主从同步的命令：
stop slave;重置之前的配置信息：
reset slave;修改主数据库：
change master to ......
验证主从数据库
登录主数据库：
D:\Other\mysql-5.7.32-winx64\bin\mysql -uroot -hlocalhost -P3507创建数据库test：
CREATE DATABASE test;分别登录主、从数据库检查发现都已经有test数据库：
show databases;主从数据库搭建完成。
搭建ShardingSphere-Proxy，实现数据库读写分离
准备
下载ShardingSphere-Proxy：apache-shardingsphere-5.0.0-alpha-shardingsphere-proxy-bin.tar.gz和MySQL驱动：mysql-connector-java-8.0.22.zip
解压apache-shardingsphere-5.0.0-alpha-shardingsphere-proxy-bin.tar.gz到D:\Other\apache-shardingsphere-5.0.0-alpha-shardingsphere-proxy-bin，并解压mysql-connector-java-8.0.22.zip文件中的mysql-connector-java-8.0.22.jar文件到D:\Other\shardingsphere\apache-shardingsphere-5.0.0-alpha-shardingsphere-proxy-bin\lib\目录。
配置ShardingSphere-Proxy
修改配置文件，配置主从数据库连接信息：D:\Other\shardingsphere\apache-shardingsphere-5.0.0-alpha-shardingsphere-proxy-bin\conf\config-replica-query.yaml
#
# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements. See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to You under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License. You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#
######################################################################################################
#
# If you want to connect to MySQL, you should manually copy MySQL driver to lib directory.
#
######################################################################################################
schemaName: replica_query_db
dataSourceCommon:
username: root
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
maintenanceIntervalMilliseconds: 30000
dataSources:
primary_ds:
url: jdbc:mysql://127.0.0.1:3506/test?serverTimezone=UTC&useSSL=false
replica_ds_0:
url: jdbc:mysql://127.0.0.1:3507/test?serverTimezone=UTC&useSSL=false
# replica_ds_1:
# url: jdbc:mysql://127.0.0.1:3306/demo_replica_ds_1?serverTimezone=UTC&useSSL=false
rules:
- !REPLICA_QUERY
dataSources:
pr_ds:
name: pr_ds
primaryDataSourceName: primary_ds
replicaDataSourceNames:
- replica_ds_0
# - replica_ds_1
修改配置文件：D:\Other\shardingsphere\apache-shardingsphere-5.0.0-alpha-shardingsphere-proxy-bin\conf\server.yaml，配置客户端连接shardingsphere-proxy的信息
#
# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements. See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to You under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License. You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#
######################################################################################################
#
# If you want to configure governance, authorization and proxy properties, please refer to this file.
#
######################################################################################################
#
#governance:
# name: governance_ds
# registryCenter:
# type: ZooKeeper
# serverLists: localhost:2181
# props:
# retryIntervalMilliseconds: 500
# timeToLiveSeconds: 60
# maxRetries: 3
# operationTimeoutMilliseconds: 500
# overwrite: false
authentication:
users:
root:
password: root
sharding:
password: sharding
authorizedSchemas: sharding_db
props:
max-connections-size-per-query: 1
acceptor-size: 16 # The default value is available processors count * 2.
executor-size: 16 # Infinite by default.
proxy-frontend-flush-threshold: 128 # The default value is 128.
# LOCAL: Proxy will run with LOCAL transaction.
# XA: Proxy will run with XA transaction.
# BASE: Proxy will run with B.A.S.E transaction.
proxy-transaction-type: LOCAL
proxy-opentracing-enabled: false
proxy-hint-enabled: false
query-with-cipher-column: true
sql-show: false
check-table-metadata-enabled: false
运行ShardingSphere-Proxy
进入D:\Other\shardingsphere\apache-shardingsphere-5.0.0-alpha-shardingsphere-proxy-bin\bin目录，启动并指定端口为3508：
start.bat 3508
连接数据库进行测试
navicat连接ShardingSphere-Proxy会存在问题，建议使用idea。在idea中以连接MySQL的方式连接到ShardingSphere-Proxy监听的端口。测试连接结果如下：