数据库性能优化普遍采用集群方式,oracle集群软硬件投入昂贵,今天花了一天时间搭建基于mysql的集群环境。
1、主要思路
简单说,实现mysql主备复制-->利用mycat实现负载均衡。
比较了常用的读写分离方式,推荐mycat,社区活跃,性能稳定。
2、测试环境
MYSQL版本:Server version: 5.5.53,到官网可以下载WINDWOS安装包。注意:确保mysql版本为5.5以后,以前版本主备同步配置方式不同。
linux实现思路类似,修改my.cnf即可。
1)A主mysql。192.168.110.1:3306, 用户root,密码root。操作系统:win7 x64,内存:4g
安装路径:C:\Program Files\MySQL\MySQL Server 5.5\bin
2)B备mysql。192.168.110.2:3306, 用户root,密码root。操作系统:win2003 x64,内存:1g
安装路径:C:\Program Files\MySQL\MySQL Server 5.5\bin
3)A主、B备的mysql中创建sync_test数据库。
3、实现mysql主备复制
主要思路:A主mysql开启日志,B备mysql读取操作日志,同步执行。
一般为主备同步,主主同步不推荐使用。
3.1 配置A主mysql。
1)修改my.ini。需要在log-bin="C:/Program Files/MySQL/MySQL Server 5.5/log/mysql-bin.log"的相关位置创建log目录,以及mysql-bin.log文件。
- [mysqld]
- server-id=1 #主机标示,整数
- port=3306
- log-bin="C:/Program Files/MySQL/MySQL Server 5.5/log/mysql-bin.log" #确保此文件可写
- read-only=0 #主机,读写都可以
- binlog-do-db=sync_test #需要备份数据库,多个写多行
- binlog-ignore-db=mysql #不需要备份的数据库,多个写多行
2)允许MYSQL远程访问
- #登录mysql console
- 进入%home%/bin,执行mysql -uroot -proot
- #授权。允许root用户,从192.168.110.*的IP范围 远程访问A主mysql
- mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.110.*' IDENTIFIED BY 'root' WITH GRANT OPTION;
- #生效。该操作很重要!!!
- mysql>FLUSH PRIVILEGES;
3)重启A主mysql数据库
- 进入%home%/bin,执行mysql -uroot -proot
- mysql>net stop mysql;
- mysql>net start mysql;
4)查看主mysql日志状态
- mysql> show master status\G;
- *************************** 1. row ***************************
- File: mysql-bin.000003
- Position: 107
- Binlog_Do_DB: sync_test
- Binlog_Ignore_DB: mysql
- 1 row in set (0.00 sec)
- ERROR:
- No query specified
3.2 配置B备mysql
1)修改my.ini。需要在log-bin="C:/Program Files/MySQL/MySQL Server 5.5/log/mysql-bin.log"的相关位置创建log目录,以及mysql-bin.log文件。
- [mysqld]
- # add for sycn test
- server-id=2 #从机标识
- log-bin="C:/Program Files/MySQL/MySQL Server 5.5/log/mysql-bin.log" #确保此文件可写
- #master-host="192.168.110.1" #主机Ip
- #master-user=root #数据库访问用户名
- #master-pass=root #数据库访问密码
- #master-port=3306 #主机端口
- #master-connect-retry=60 #如果从服务器发现主服务器断掉,重新连接的时间差(秒)
- replicate-do-db=sync_test #只复制某个库
- replicate-ignore-db=mysql #不复制某个库
2)重启B备mysql数据库
- 进入%home%/bin,执行mysql -uroot -proot
- mysql>net stop mysql;
- mysql>net start mysql;
3)配置B备数据库的数据来源,核实高亮处的状态是否正常。
- mysql>change master to master_host='192.168.110.1',master_port='3306',master_user='root',master_password='root';
- mysql>slave start;
- mysql>show slave status\G;
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for master to send even
- Master_Host: 192.168.110.1
- Master_User: root
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: mysql-bin.000003
- Read_Master_Log_Pos: 107
- Relay_Log_File: wjt-1c698d8a032-relay-bin.00001
- Relay_Log_Pos: 253
- Relay_Master_Log_File: mysql-bin.000003
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
- Replicate_Do_DB: sync_test
- Replicate_Ignore_DB: mysql
- 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: 107
- Relay_Log_Space: 565
- 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: 1
- 1 row in set (0.00 sec)
- ERROR:
- No query specified
3.3 验证同步配置结果。
1)A主mysql:使用navicat工具,在sync_test库中创建sync_table表,并添加一些数据。
2)B备mysql:使用navicat工具,查看sync_test库,可以看到sync_table表和数据已被同步。
4、实现读写分离
主要思路:使用mycat中间件,转发sql指令到后端mysql节点。mycat不负责数据库同步。
4.1 安装mycat
1)mycat是什么?可以认为它是一个数据库访问中间件,但更像f5、ngnix等产品,具备访问路由、多表分表分片操作等功能。总之很强大
下载:http://www.mycat.io/ ,本文使用的是:1.6-RELEASE
mycat使用java编写,因此可以有linux、windows等多个版本可下载。
2)解压Mycat-server-1.6-RELEASE-20161012170031-win.tar,到D:\dev-bin\mycat目录
3)确保java环境为jdk1.7以上,否则mycat将不支持。
4)安装完毕
4.2 配置mycat
1)server.xml。配置访问用户及权限。修改高亮处信息,其中admin、user为访问mycat的用户,TESTDB为mycat虚拟的数据库,供上层应用访问。
- <user name="admin">
- <property name="password">admin</property>
- <property name="schemas">TESTDB</property>
- <!-- 表级 DML 权限设置 -->
- <!--
- <privileges check="false">
- <schema name="TESTDB" dml="0110" >
- <table name="tb01" dml="0000"></table>
- <table name="tb02" dml="1111"></table>
- </schema>
- </privileges>
- -->
- </user>
- <user name="user">
- <property name="password">user</property>
- <property name="schemas">TESTDB</property>
- <property name="readOnly">true</property>
- </user>
2)schema.xml。这部分不太好理解,精简了一下,主要分schema、dataNode、dataHost三个主要配置。
<scheme>节点定义了mycat的虚拟数据库为TESTDB, balance="1":write操作路由到A机,读操作路由到B。
- <?xml version="1.0"?>
- <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
- <mycat:schema xmlns:mycat="http://io.mycat/">
- <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
- <!-- 这里不配置,代表所有的表分片到dn1节点-->
- </schema>
- <dataNode name="dn1" dataHost="localhost1" database="sync_test" />
- <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
- writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
- <heartbeat>select user()</heartbeat>
- <!-- can have multi write hosts -->
- <writeHost host="hostM1" url="192.168.110.1:3306" user="root" password="root">
- <!-- can have multi read hosts -->
- <readHost host="hostS2" url="192.168.110.2:3306" user="root" password="root" />
- </writeHost>
- </dataHost>
- </mycat:schema>
4.3 启动mycat
1)启动mycat
- D:\dev-bin\mycat\bin>startup_nowrap.bat
后台信息如下:
- D:\dev-bin\mycat\bin>startup_nowrap.bat
- D:\dev-bin\mycat\bin>REM check JAVA_HOME & java
- D:\dev-bin\mycat\bin>set "JAVA_CMD=C:\Program Files (x86)\Java\jdk1.7.0_13/bin/java"
- D:\dev-bin\mycat\bin>if "C:\Program Files (x86)\Java\jdk1.7.0_13" == "" goto noJavaHome
- D:\dev-bin\mycat\bin>if exist "C:\Program Files (x86)\Java\jdk1.7.0_13\bin\java.exe" goto mainEntry
- D:\dev-bin\mycat\bin>REM set HOME_DIR
- D:\dev-bin\mycat\bin>set "CURR_DIR=D:\dev-bin\mycat\bin"
- D:\dev-bin\mycat\bin>cd ..
- D:\dev-bin\mycat>set "MYCAT_HOME=D:\dev-bin\mycat"
- D:\dev-bin\mycat>cd D:\dev-bin\mycat\bin
- #如果启动失败,请修改D:\dev-bin\mycat\bin\startup_nowrap.bat文件中的以下参数。默认占用内存为2G
- D:\dev-bin\mycat\bin>"C:\Program Files (x86)\Java\jdk1.7.0_13/bin/java" -server -Xms512m -Xmx512m -XX:MaxPermSize=64M -XX:+AggressiveOpts -XX:MaxDirectMemorySize=768m -DMYCAT_HOME=D:\
- p "..\conf;..\lib\*" io.mycat.MycatStartup
- 。。。。。。。。。。。。。。。。。。。。。。。。。。。
- MyCAT Server startup successfully. see logs in logs/mycat.log #启动成功将看到如下信息。
如日志中出现192.168.110.2 not connected 等信息,请允许B备mysql远程访问。
- #登录mysql console
- 进入%home%/bin,执行mysql -uroot -proot
- #授权。允许root用户,从192.168.110.*的IP范围 远程访问Bmysql
- mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.110.*' IDENTIFIED BY 'root' WITH GRANT OPTION;
- #生效。该操作很重要!!!
- mysql>FLUSH PRIVILEGES;
4.4 测试读写分离。
1、验证是否同步
1)使用navicat连接mycat,操作方式和连接物理mysql库一致,用户admin,密码admin,端口8066。
2)在TESTDB虚拟库中,创建新表test2,增加一些数据
3)查看A节点、B节点数据已同步。
2、关闭B备mysql的同步,验证读写分离。
- mysql> slave stop;
- Query OK, 0 rows affected (0.00 sec)
- mysql> show slave status\G;
- *************************** 1. row ***************************
- Slave_IO_State:
- Master_Host: 192.168.110.1
- Master_User: root
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: mysql-bin.000003
- Read_Master_Log_Pos: 478
- Relay_Log_File: wjt-1c698d8a032-relay-bin.00001
- Relay_Log_Pos: 624
- Relay_Master_Log_File: mysql-bin.000003
- Slave_IO_Running: No
- Slave_SQL_Running: No
- Replicate_Do_DB: sync_test
- Replicate_Ignore_DB: mysql
- 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: 478
- Relay_Log_Space: 936
- 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: NULL
- 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: 1
- 1 row in set (0.00 sec)
- ERROR:
- No query specified
3)使用navicat连接mycat,操作方式和连接物理mysql库一致,用户admin,密码admin,端口8066。
a.连接成功后,将看到TESTDB数据库和test数据表。
b.在test表中添加一些数据,保存。
c.执行select * from test查看test操作,将看到数据未更新。
原因:mycat将查询sq路由到B,因此读取的结果集不一致。
最后,mycat使用可以参见官网的权威指南学习。双主双备架构待后续更新。
相关推荐
《MyCat测试报告:单机与多机集群的性能比较》 MyCat,作为一个流行的开源分布式数据库系统,常被用于解决大数据场景下的高并发问题。本测试报告旨在探究单台MyCat与多台MyCat集群在性能上的差异,并对比前端使用...
【标题】"mycat2 install-template(mycat2-install-template-1.20.zip)" 指的是MyCat 2的一个安装模板文件,版本号为1.20,存储在一个名为“mycat2-install-template-1.20.zip”的压缩包内。MyCat是开源的、基于...
MyCat2映射关系概述 MyCat2是一款开源的数据库中间件,可以实现数据库的分布式处理、读写分离、负载均衡等功能。在MyCat2中,数据源、集群和逻辑表是三个核心概念。 数据源(DataSource)是指实际的数据库实例,...
标题所涉及的知识点为“Mycat高可用负载均衡集群实现”,这是使用HAProxy和Keepalived技术来达成MyCat服务器集群的高可用性和负载均衡的方案。下面详细说明这一知识点。 首先,Mycat是一个数据库中间件,它可以实现...
【标题】"mycat2 install-template(mycat2-install-template-1.21.zip)" 指的是MyCat 2的一个安装模板文件,版本为1.21,通常包含了安装MyCat 2所需的所有配置文件和脚本,用于简化部署过程。 【描述】虽然描述...
《深入解析mycat2:基于mycat2-1.21-release-jar-with-dependencies.jar的分布式数据库中间件》 mycat2是一款开源的、高性能的、基于Java开发的分布式数据库中间件,它旨在解决大数据分布式存储和处理的问题。mycat...
【标题】"mycat2-1.21" 指的是Mycat数据库中间件的一个特定版本,即1.21版。Mycat是一款开源的、基于Java开发的分布式数据库系统,常用于大数据环境下的数据库集群管理,能够实现数据库的水平扩展,提升系统的并发...
MySQL中间件MyCat是一款在大数据环境下广泛使用的数据库管理和分发工具,特别针对MySQL数据库系统设计,旨在实现数据库的读写分离和水平扩展。MyCat 1.6.5是其一个稳定版本,专为Windows环境提供了免安装的便捷性。...
《Mycat数据库分片中间件详解》 Mycat,作为一款开源的分布式数据库中间件,被广泛应用于大数据量、高并发的互联网环境中。它实现了数据库的分片功能,可以将一个大表拆分成多个小表,分散在不同的数据库服务器上,...
【MyCat从入门到放弃】 MyCat是一款开源的分布式数据库中间件,它的设计目标是让大数据分布式处理变得简单。在大型互联网应用中,面对高并发、海量数据的挑战,单机数据库往往无法满足需求,因此MyCat应运而生。本...
### 关系型数据的分布式处理系统MyCAT:深入解析与应用 #### 1. MyCAT 概述 ##### 1.1 背景 随着信息技术的快速发展,传统的数据库技术面临着越来越大的挑战。特别是当数据库应用扩展到互联网级别时,集中式...
在Windows环境下安装Mycat,首先需要理解Mycat是什么。Mycat是一个开源的数据库分片中间件,主要用于解决大数据场景下的数据存储和查询问题。它基于Java编写,能够支持多种数据库系统,如MySQL、Oracle、DB2、SQL ...
Mycat 发展到目前的版本,已经不是一个单纯的 MySQL 代理了,它的后端可以支持 MySQL、SQL Server、 Oracle、DB2、PostgreSQL 等主流数据库,也支持 MongoDB 这种新型 NoSQL 方式的存储,未来还会支持更 多类型的...
Proxy和Mycat是两种常用的中间件,用于实现这些功能。本篇文章将深入探讨proxy和Mycat的对比测试,以及如何在MySQL主从架构中进行测试。 首先,我们来看proxy。Proxy通常指的是数据库代理服务器,它位于应用服务器...
Mycat作为一款开源的分布式数据库中间件,被广泛应用于大数据处理和高并发场景,为企业提供了数据库分库分表、读写分离等解决方案。然而,在实际生产环境中,Mycat的使用往往会遇到一些常见问题。以下是对这些问题的...
MySQL分布式数据库中间件Mycat是一款广泛应用于大数据处理和高并发场景的重要工具,它通过将数据分布到多个物理节点上,实现了数据的水平扩展。在实际应用中,Mycat的性能调优对于系统的整体效率至关重要。本指南将...
《Mycat2数据迁移工具——Yugong:轻松实现MySQL到Mycat的无缝迁移》 在IT行业中,数据库管理是一项至关重要的任务,尤其是在大数据时代,数据量的爆炸性增长使得传统的单一数据库系统面临挑战。Mycat作为一款开源...
【Mycat多租户解决方案】是针对SAAS(Software as a Service)应用设计的一种高效且资源节省的数据隔离策略。在传统的多租户模式中,每个租户通常需要单独的数据库实例,这不仅消耗大量资源,还增加了管理和运维的...
《Mycat权威指南》是一本深度解析Mycat数据库系统的中文著作,旨在为读者提供全面、清晰的Mycat使用指南。Mycat作为一款开源的分布式数据库系统,其核心价值在于解决大数据环境下传统单机数据库无法满足的高并发、高...