mysql读写分离测试性能方法 mysql8读写分离

转载

laokugonggao 2024-04-22 20:35:17

文章标签 mysql读写分离测试性能方法 mysql mycat xml 数据库 文章分类 MySQL 数据库

安装流程

0. 先决条件

已安装至少2个MySQL5.6+的数据库实例, 且版本一致
分别具有相同的数据库及数据结构, 主库写入, 从库读取
了解基本原理

mysql读写分离测试性能方法 mysql8读写分离_数据库

1. 主从配置

1.1 Mater主库配置

服务器唯一id : server-id=1
开启日志 : log-bin=mysql-bin
需要同步的数据库: binlog-do-db=test

1.2 Slave从库配置

服务器唯一id : server-id=2
开启日志 : log-bin=mysql-bin
需要同步的数据库: binlog-do-db=test

以下是完整配置示例

[mysqld]
# 开启FEDERATED
federated
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=D:\\mysql-8.0.18
# 设置mysql数据库的数据的存放目录
datadir=D:\\mysql-8.0.18\\data
# 允许最大连接数
max_connections=20000
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=100
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 设置log_bin_trust_routine_creators全局系统变量为1
log-bin-trust-function-creators=1
# SQL MODE
sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
# INNOD引擎缓存
innodb_buffer_pool_size=4G
# 接收数据包大小
max_allowed_packet=1024M

### 主从数据库配置核心部分
# 服务器唯一id，默认为1  主数据库和从数据库的server-id不能重复
server-id=1
# 设置同步的binary log二进制日志文件名前缀，默认为binlog
log-bin=mysql-bin

### 可选配置
# 需要主从复制的数据库
binlog-do-db=test
# 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）
binlog-ignore-db=mysql
# 为每个session分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
# 主从复制的格式（mixed,statement,row，默认格式是statement。建议是设置为row，主从复制时数据更加能够统一）
binlog_format=row
# 设置二进制日志自动删除/过期的天数，避免占用磁盘空间。默认值为0，表示不自动删除。
expire_logs_days=7
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8

[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

2. 连接主库

以我的测试环境为例: 192.168.31.234:3306 (master) , 192.168.31.235:3306 (slave)
登录主库执行语句 : show master status;
记下File, Position两个字段的值; 例如: File: mysql-bin.000002, Position: 1139955
切换到从库, 执行如下语句

change master to master_host='192.168.31.234',master_user='root',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=1139955;

启动: start slave;
查看状态: show slave status;
如果显示Slave_IO_Running: Yes, Slave_SQL_Running: Yes, 则成功

3. 创建用户

创建读写分离用户, 非必要步骤, 可使用root用户, master和slave都创建

# 创建用户并授权
CREATE USER 'mycat'@'%' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO mycat;
FLUSH PRIVILEGES;

4. 安装mycat

在第三台服务器安装mycat, 我以linux环境测试 192.168.31.196
下载mycat1.7.5: 下载
解压: tar -zxvf Mycat-server-1.6.7.5-release-20200422133810-linux.tar.gz
编辑schema.xml配置文件: sudo vim mycat/conf/schema.xml, 完整配置如下:

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
	
	<!-- 有几个数据库，就有几个schema标签 -->
    <schema name="test" dataNode="dn_test" 
            checkSQLschema="false" sqlMaxLimit="100" >
    </schema>
	
	<!-- 节点清单 -->
	<dataNode name="dn_test" dataHost="host_test" database="test" />
	
	<!-- 节点配置 -->
	<dataHost name="host_zr_oa_d2" maxCon="1000" minCon="10" balance="1"
	 		  writeType="0" dbType="mysql" dbDriver="jdbc" 
              switchType="-1" slaveThreshold="100">
			<!-- 心跳 -->
			<heartbeat>select uuid()</heartbeat>
			<!-- 写库 master -->
			<writeHost host="hostM1" user="mycat" password="123456"
                       url="jdbc:mysql://192.168.31.234:3306?useSSL=false&serverTimezone=GMT%2B8">
				<!-- 读库 slave -->
                <readHost host="hostS1" user="mycat" password="123456"
                          url="jdbc:mysql://192.168.31.235:3306?useSSL=false&serverTimezone=GMT%2B8"/>
			</writeHost>
	</dataHost>
	
</mycat:schema>

编辑server.xml配置文件: sudo vim mycat/conf/server.xml, 如下:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
	<system>
		<!-- 0为需要密码登陆、1为不需要密码登陆,默认为0,设置为1则需要指定默认账户-->
		<property name="nonePasswordLogin">0</property> 
		<property name="useHandshakeV10">1</property>
		<!-- 1为开启实时统计、0为关闭 -->
		<property name="useSqlStat">0</property> 
		<!-- 1为开启全加班一致性检测、0为关闭 --> 
		<property name="useGlobleTableCheck">0</property>  
		<property name="sequnceHandlerType">2</property>
		<!-- 子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false -->
		<property name="subqueryRelationshipCheck">false</property> 
        <!--默认为type 0--> 
        <!--0 DirectByteBufferPool-->
        <!--1 ByteBufferArena-->
        <!--2 NettyBufferPool-->
        <property name="processorBufferPoolType">0</property>
		<!--分布式事务开关-->
        <!--0为不过滤分布式事务-->
        <!--1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤）-->
        <!--2为不过滤分布式事务,但是记录分布式事务日志-->
		<!--off heap for merge/order/group/limit  1开启 0关闭-->
		<property name="handleDistributedTransactions">0</property>
		<property name="useOffHeapForMerge">1</property>
		<!--单位为m-->
        <property name="memoryPageSize">64k</property>
		<!--单位为k-->
		<property name="spillsFileBufferSize">1k</property>
		<property name="useStreamOutput">0</property>
		<!--单位为m-->
		<property name="systemReserveMemorySize">384m</property>
		<!--是否采用zookeeper协调切换 -->
		<property name="useZKSwitch">false</property>
		<!--XA Recovery Log日志路径 -->
		<!--<property name="XARecoveryLogBaseDir">./</property>-->
		<!--XA Recovery Log日志名称 -->
		<!--<property name="XARecoveryLogBaseName">tmlog</property>-->
		<!--如果为true的话 严格遵守隔离级别,不会在仅仅只有select语句的时候在事务中切换连接-->
		<property name="strictTxIsolation">false</property>
		<property name="useZKSwitch">true</property>
	</system>

	<!-- 创建mycat的root用户 -->
	<user name="root" defaultAccount="true">
		<!-- 连接mycat的密码 -->
		<property name="password">654321</property>
		<!-- 指向schemas.xml中的schemas标签的name，多个schemas用逗号分隔 -->
		<property name="schemas">dn_test</property>
	</user>

	<!-- 创建mycat的只读用户 -->
	<user name="user">
		<!-- 连接mycat的密码 -->
		<property name="password">654321</property>
		<!-- 指向schemas.xml中的schemas标签的name，多个schemas用逗号分隔 -->
		<property name="schemas">dn_test</property>
		<property name="readOnly">true</property>
	</user>
</mycat:server>

启动mycat: sudo ./usr/local/mycat/bin/mycat start
连接mycat: 8066为服务端口192.168.31.196:8066, 9066为管理端口192.168.31.196:9066

mysql读写分离测试性能方法 mysql8读写分离_mysql_02