基于mysql+mycat如何搭建稳定高可用集群负载均衡主备复制读写分离操作

54次阅读

没有评论

共计 7619 个字符，预计需要花费 20 分钟才能阅读完成。

行业资讯
数据库
MySQL 数据库
基于 mysql+mycat 如何搭建稳定高可用集群负载均衡主备复制读写分离操作

自动写代码机器人，免费开通

这篇文章主要介绍基于 mysql+mycat 如何搭建稳定高可用集群负载均衡主备复制读写分离操作，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

主要思路

简单说，实现 mysql 主备复制 – 利用 mycat 实现负载均衡。

比较了常用的读写分离方式，推荐 mycat，社区活跃，性能稳定。

测试环境

MYSQL 版本：Server version: 5.5.53，到官网可以下载 WINDWOS 安装包。

注意：确保 mysql 版本为 5.5 以后，以前版本主备同步配置方式不同。

linux 实现思路类似，修改 my.cnf 即可。

A 主 mysql。192.168.110.1:3306, 用户 root，密码 root。操作系统：win7 x64，内存：4g

安装路径：C:\Program Files\MySQL\MySQL Server 5.5\bin

B 备 mysql。192.168.110.2:3306, 用户 root，密码 root。操作系统：win2003 x64，内存：1g

安装路径：C:\Program Files\MySQL\MySQL Server 5.5\bin

A 主、B 备的 mysql 中创建 sync_test 数据库

实现 mysql 主备复制

主要思路：A 主 mysql 开启日志，B 备 mysql 读取操作日志，同步执行。

一般为主备同步，主主同步不推荐使用。

配置 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

配置 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

验证同步配置结果

A 主 mysql：使用 navicat 工具，在 sync_test 库中创建 sync_table 表，并添加一些数据

B 备 mysql：使用 navicat 工具，查看 sync_test 库，可以看到 sync_table 表和数据已被同步

实现读写分离

主要思路：使用 mycat 中间件，转发 sql 指令到后端 mysql 节点。mycat 不负责数据库同步。

安装 mycat

mycat 是什么？可以认为它是一个数据库访问中间件，但更像 f5、ngnix 等产品，具备访问路由、多表分表分片操作等功能。总之很强大。

下载：http://www.mycat.io/，本文使用的是：1.6-RELEASE

解压 Mycat-server-1.6-RELEASE-20161012170031-win.tar，到 D:\dev-bin\mycat 目录

确保 java 环境为 jdk1.7 以上，否则 mycat 将不支持

安装完毕

配置 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

启动 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;

测试读写分离

验证是否同步

使用 navicat 连接 mycat，操作方式和连接物理 mysql 库一致，用户 admin，密码 admin，端口 8066

在 TESTDB 虚拟库中，创建新表 test2，增加一些数据

查看 A 节点、B 节点数据已同步

关闭 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

连接成功后，将看到 TESTDB 数据库和 test 数据表

在 test 表中添加一些数据，保存

执行 select * from test 查看 test 操作，将看到数据未更新

原因：mycat 将查询 sq 路由到 B，因此读取的结果集不一致。

最后，mycat 使用可以参见官网的权威指南学习。双主双备架构待后续更新。

以上是“基于 mysql+mycat 如何搭建稳定高可用集群负载均衡主备复制读写分离操作”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注丸趣 TV 行业资讯频道！

向 AI 问一下细节

正文完

发表至：数据库

2023-12-04

转载说明：除特殊说明外本站除技术相关以外文章皆由网络搜集发布，转载请注明出处。

怎么在sql server中通过脚本进行数据库压缩全备份