Mysql主从复制读写分离

Mysql主从复制，读写分离

主从复制

一、核心概念与用途

1. 核心作用

• 数据冗余：主库数据自动同步到从库，提高数据安全性。
• 读写分离：读请求分发到从库，写请求集中到主库，提升并发性能。
• 负载均衡：多个从库分担读压力，适合读多写少的场景。
• 高可用性：主库故障时，可快速切换到从库继续服务。

2. 复制类型

• 基于二进制日志（Binlog）：最常用，基于主库的二进制日志复制（推荐）。
• 基于 GTID（全局事务标识符）：更可靠，自动跟踪事务，简化复制配置（MySQL 5.6+）。
• 基于语句：复制 SQL 语句（早期方法，可能导致主从不一致）。

二、工作原理（基于 Binlog）

1. 三大核心组件

• 主库（Master）：
  • 二进制日志（Binlog）：记录所有变更（增删改）的日志文件。
  • Binlog Dump 线程：将 Binlog 发送给从库。
• 从库（Slave）：
  • I/O 线程：从主库接收 Binlog，写入中继日志（Relay Log）。
  • SQL 线程：从中继日志读取事件，在从库执行，实现数据同步。

2. 复制流程

主库                       从库
│                          │
│  1. 执行写操作（INSERT/UPDATE/DELETE）│
│  2. 记录变更到 Binlog       │
│  3. Binlog Dump 线程监听 Binlog │
│                          │
│                          │  4. 从库 I/O 线程连接主库
│                          │  5. 请求 Binlog 事件
│←─────────────────────────│
│  6. 发送 Binlog 事件       │
│─────────────────────────→│
│                          │  7. 写入中继日志（Relay Log）
│                          │  8. SQL 线程读取 Relay Log
│                          │  9. 在从库执行 SQL 语句

三、配置步骤（基于 GTID）

1. 主库配置（my.cnf）

[mysqld]
server-id = 1              # 唯一标识（1-4294967295）
log-bin = mysql-bin        # 启用 Binlog
binlog-do-db = your_db     # 指定需要复制的数据库（可选）
gtid_mode = ON             # 启用 GTID
enforce_gtid_consistency = ON  # 强制 GTID 一致性

• 重启主库

  并创建复制用户：

  CREATE USER 'repl_user'@'%' ID
  

msyql主从复制详解

四、配置步骤（基于 GTID，续）

1. 主库配置（my.cnf）

[mysqld]
server-id = 1              # 唯一标识（1-4294967295）
log-bin = mysql-bin        # 启用 Binlog
binlog-do-db = your_db     # 指定需要复制的数据库（可选）
gtid_mode = ON             # 启用 GTID
enforce_gtid_consistency = ON  # 强制 GTID 一致性
log-slave-updates = ON     # 从库更新也记录到 Binlog（用于级联复制）
binlog

msyql主从复制详解

四、配置步骤（基于 GTID，续）

1. 主库配置（my.cnf）

[mysqld]
server-id = 1              # 唯一标识（1-4294967295）
log-bin = mysql-bin        # 启用 Binlog
binlog-do-db = your_db     # 指定需要复制的数据库（可选）
gtid_mode = ON             # 启用 GTID
enforce_gtid_consistency = ON  # 强制 GTID 一致性
log-slave-updates = ON     # 从库更新也记录到 Binlog（用于级联复制）
binlog_format = ROW        # 推荐使用 ROW 格式（更安全）
expire-logs-days = 10      # Binlog 过期天数，避免占满磁盘

• 重启主库

  后，创建复制用户：

  CREATE USER 'repl_user'@'%' IDENTIFIED BY 'password';
  GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
  FLUSH PRIVILEGES;
  

2. 从库配置（my.cnf）

[mysqld]
server-id = 2              # 必须与主库不同
relay-log = mysql-relay-bin  # 中继日志名称
log-bin = mysql-bin        # 从库也记录 Binlog（可选，用于级联复制）
gtid_mode = ON
enforce_gtid_consistency = ON
read_only = ON             # 从库设为只读（可选，但推荐）

• 重启从库。

3. 同步主库数据到从库

两种常用方法：

1. mysqldump 逻辑备份 ：

主库导出数据

mysqldump -u root -p --all-databases --single-transaction --master-data=2 > dump.sql

将 dump.sql 传输到从库并导入

mysql -u root -p < dump.sql

2. **物理备份（xtrabackup）**：```bash
# 主库备份
xtrabackup --backup --target-dir=/backup/# 传输备份到从库并恢复
xtrabackup --prepare --target-dir=/backup/
xtrabackup --copy-back --target-dir=/backup/

4. 配置从库连接主库

在从库执行：

CHANGE MASTER TOMASTER_HOST='主库IP',MASTER_USER='repl_user',MASTER_PASSWORD='password',MASTER_AUTO_POSITION=1;  # 使用 GTID 模式START SLAVE;  # 启动复制线程

5. 验证复制状态

SHOW SLAVE STATUS\G;

• 关键参数：
  • Slave_IO_Running: Yes
  • Slave_SQL_Running: Yes
  • Seconds_Behind_Master: 0（延迟秒数）

五、复制模式

1. 一主一从

主库 → 从库

• 特点：结构简单，用于读写分离或备份。

2. 一主多从

    → 从库1
主库 → 从库2→ 从库3

• 特点：适合读多写少场景，多个从库分担读压力。

3. 级联复制（主→从→从）

主库 → 从库1 → 从库2→ 从库3

• 特点：减轻主库负担，适合大规模集群。

4. 双主复制（互为主从）

主库1 ⇄ 主库2

• 特点：双向复制，需解决冲突问题（慎用）。

六、监控与维护

1. 监控命令

-- 查看从库状态
SHOW SLAVE STATUS\G;-- 查看主库 Binlog 信息
SHOW MASTER STATUS;-- 查看复制延迟
SELECT UNIX_TIMESTAMP() - UNIX_TIMESTAMP(Last_Heartbeat) AS delay FROM information_schema.replica_host_status;

2. 常见问题与修复

• 问题 1：IO 线程失败（网络问题）

  • 解决：检查主从网络连通性，确保防火墙开放 3306 端口。

• 问题 2：SQL 线程失败（主从数据不一致）

  • 解决:

    STOP SLAVE;
    SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;  # 跳过一个错误
    START SLAVE;
    

• 问题 3：复制延迟过高

  • 优化:

    • 从库硬件升级（更快的磁盘、更多内存）。
    • 避免从库执行大事务。
    • 调整参数：sync_binlog=0、innodb_flush_log_at_trx_commit=2。

七、安全与性能优化

1. 安全建议

• 复制账户仅开放 REPLICATION SLAVE 权限。
• 主从服务器间使用 SSL 加密（需配置）。
• 定期备份从库数据。

2. 性能优化

• 参数调整：

  innodb_flush_log_at_trx_commit = 2  # 减少磁盘IO（性能↑，安全性↓）
  sync_binlog = 0                     # 同上
  binlog_cache_size = 2M              # Binlog 缓存
  max_binlog_size = 1G                # 单个 Binlog 文件大小
  

• 硬件优化：

  • 主库使用 SSD 存储 Binlog。
  • 从库增加内存以缓存更多数据。

八、高可用方案

1. 半同步复制（MySQL 5.5+）

• 确保事务至少写入一个从库的 Relay Log 才返回成功，提高数据安全性。

2. MGR（MySQL Group Replication）

• 组复制，自动选举主库，支持多主写入（MySQL 5.7+）。

3. 第三方工具

• Orchestrator：自动检测主库故障并切换。
• ProxySQL：实现读写分离和负载均衡。
• Keepalived：主从切换的 VIP 解决方案。

九、总结

• 适用场景：读写分离、数据备份、高可用架构。
• 核心配置：GTID 模式、唯一 server-id、复制账户权限。
• 监控重点：IO/SQL 线程状态、复制延迟。
• 优化方向：网络、硬件、参数调整。

读写分离

使用mycat进行读写分离

mycat简介

MyCAT 是基于 Java 开发的开源数据库中间件，定位为「数据库代理层」，可视为 MySQL 的 “分布式增强版”。它介于应用程序和数据库之间，负责拦截、解析应用的 SQL 请求，并根据规则路由到后端多个数据库实例，实现数据的分布式处理。

示例

环境

在mycat 机器上操作[root@bogon ~]# ls
anaconda-ks.cfg          mycat2-1.21-release-jar-with-dependencies.jar  mysql-connector-java-8.0.18.jar
jdk-8u171-linux-x64.rpm  mycat2-install-template-1.20.zip###所需依赖 和mycat2包

在mycat 机器上操作[root@bogon ~]# unzip mycat2-install-template-1.20.zip  ###解压zip
[root@bogon ~]# mv ./mycat  /usr/local/   ###将这文件移动到 /usr/local下
[root@bogon ~]# rpm -ivh jdk-8u171-linux-x64.rpm  ###rpm安装软件包
[root@bogon ~]# cp mycat2-1.21-release-jar-with-dependencies.jar /usr/local/mycat/lib/  ###移动到mycat lib下
[root@bogon ~]# cp mysql-connector-java-8.0.18.jar /usr/local/mycat/l   ### 移动到mycat lib下
##给mycat下的bin执行权限
[root@bogon bin]# ln -sf /usr/local/mycat/bin/* /usr/local/bin/  ###软连接方便操作

在主从复制环境上 操作
master mysql
mysql> create user 'mycat'@'%' identified by 'pwd123'; ### 创建一个用户名为mycat 这个用户用于给mycat服务器连接我们使用
Query OK, 0 rows affected (0.03 sec)mysql> grant all on *.* to 'mycat'@'%';  ###给权限
Query OK, 0 rows affected (0.02 sec)  mysql> alter user 'mycat'@'%' identified with mysql_native_password by 'pwd123';  ###加密密码
Query OK, 0 rows affected (0.02 sec)mysql> flush privileges;  ### 刷新权限
Query OK, 0 rows affected (0.01 sec)

vim 编译/usr/local/mycat/conf/datasources/prototypeDs.datasource.json

代码块解释

{"dbType":"mysql","idleTimeout":60000,"initSqls":[],"initSqlsGetConnection":true,"instanceType":"READ_WRITE","maxCon":1000,"maxConnectTimeout":3000,"maxRetryCount":5,"minCon":1,"name":"prototypeDs","password":"pwd123","type":"JDBC","url":"jdbc:mysql://192.168.10.101:3306/mysql?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8","user":"mycat","weight":0
}
~                                                                                                               
~                                      

核心配置字段解析

1. 连接基本信息

"dbType":"mysql",
"url":"jdbc:mysql://192.168.10.101:3306/mysql?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
"user":"mycat",
"password":"pwd123",

• dbType：数据库类型（这里是 MySQL）。

• url

  ：JDBC 连接 URL，包含：

  • 主机地址：192.168.10.101
  • 端口号：3306
  • 数据库名：mysql（默认系统库，建议替换为实际业务库）
  • 参数：
    • useUnicode=true：启用 Unicode 字符集
    • serverTimezone=Asia/Shanghai：设置时区为上海
    • characterEncoding=UTF-8：使用 UTF-8 编码

• user/password：数据库用户名和密码。

2. 连接池参数

"maxCon":1000,
"minCon":1,
"idleTimeout":60000,

• maxCon：最大连接数（1000），高并发场景需谨慎设置，避免数据库过载。
• minCon：最小空闲连接数（1），确保至少有一个连接保持活跃。
• idleTimeout：连接空闲超时时间（60000 毫秒 = 60 秒），超时后连接会被关闭。

3. 连接行为配置

"maxConnectTimeout":3000,
"maxRetryCount":5,
"initSqls":[],
"initSqlsGetConnection":true,

• maxConnectTimeout：连接超时时间（3 秒），超过此时间未建立连接则失败。
• maxRetryCount：连接失败时的最大重试次数（5 次）。
• initSqls：获取连接时执行的初始化 SQL 列表（空数组表示不执行）。
• initSqlsGetConnection：是否在每次获取连接时执行初始化 SQL（此处为true）。

4. 实例类型与权重

"instanceType":"READ_WRITE",
"weight":0,

• instanceType：实例类型为READ_WRITE，表示可读写（适用于主库）。
• weight：权重为 0（通常用于读写分离场景），此处可能表示不参与负载均衡（需结合具体框架逻辑）。

5. 其他配置

"type":"JDBC",

• type：连接类型为 JDBC，基于 Java 标准数据库连接 API。

潜在问题与优化建议

1. 数据库选择：
   • 当前连接的是 MySQL 系统库（mysql），建议修改为实际业务数据库（如your_database_name）。
2. 连接池大小：
   • maxCon=1000过高，可能导致数据库资源耗尽。建议根据业务流量调整（通常 50-200）。
3. 安全风险：
   • 明文存储密码（password），建议使用加密方式或配置中心管理敏感信息。
4. 超时设置：
   • maxConnectTimeout=3000ms对高延迟网络可能过短，可根据实际情况调整（如 5000-10000ms）。
5. 权重配置：
   • weight=0可能导致此数据源不被使用，若为主库应设置合理权重（如100）。

典型使用场景

这个配置适用于需要连接 MySQL 数据库的 Java 应用，例如：

• MyBatis/MyBatis-Plus 框架的数据源配置
• Spring Boot 项目的数据库连接池配置
• 中间件（如 MyCAT）的后端数据库连接配置

若需进一步优化或适配特定框架，可根据实际需求调整参数。

编辑一个配置文件用于客户端连接mycat的身法验证

{// 数据库方言，指定使用MySQL语法和驱动"dialect": "mysql",// 数据库服务器IP地址，当前为null，需补充为实际IP或主机名（如"192.168.1.100"）"ip": null,// 数据库登录密码（明文存储，生产环境建议加密）"password": "123456",// 事务类型：使用XA分布式事务协议// XA协议支持跨数据库的强一致性事务（通过两阶段提交实现）"transactionType": "xa",// 数据库用户名（使用root权限过高，建议创建专用用户）"username": "root"
}                                                                        
~                             

mycat start

[root@bogon datasources]# mycat start
Starting mycat2...
mycat2 is already running.
[root@bogon datasources]# ss -tnl | grep 8066
LISTEN 0      4096                    *:8066             *:*          

增加mycat数据源

[root@localhost ~]# mysql -uroot -p123456 -P8066 -h192.168.10.104
###小p密码 大p端口号 -hip地址mysql> /*+ mycat:createDataSource{ "name":"master","url":"jdbc:mysql://192.168.10.101:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true","instanceType":"WRITE","user":"mycat","password":"pwd123"} */;
Query OK, 0 rows affected (0.02 sec)mysql> /*+ mycat:createDataSource{ "name":"slave1","url":"jdbc:mysql://192.168.10.102:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true","instanceType":"READ","user":"mycat","password":"pwd123"} */;
Query OK, 0 rows affected (0.02 sec)mysql> /*+ mycat:createDataSource{ "name":"slave2","url":"jdbc:mysql://192.168.10.103:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true","instanceType":"READ","user":"mycat","password":"pwd123"} */;
Query OK, 0 rows affected (0.01 sec)
[root@bogon datasources]# ll /usr/local/mycat/conf/datasources/
总计 16
-rw-r--r--. 1 root root 477  6月 6日 17:27 master.datasource.json
-rw-r--r--. 1 root root 423  6月 6日 17:03 prototypeDs.datasource.json
-rw-r--r--. 1 root root 476  6月 6日 17:27 slave1.datasource.json
-rw-r--r--. 1 root root 476  6月 6日 17:27 slave2.datasource.json
####！！！！！！！！！如果数据配置有误可以使用#/*+ mycat:resetConfig{} */ ; 重置

创建集群

mysql> /*! mycat:createCluster{"name":"cls01","masters":["master"],"replicas":["slave1","slave2"]} */;
Query OK, 0 rows affected (0.03 sec)
[root@bogon clusters]# ls
cls01.cluster.json  prototype.cluster.json
{// 集群类型：主从复制模式"clusterType": "MASTER_SLAVE",// 心跳检测配置"heartbeat": {"heartbeatTimeout": 1000,         // 心跳超时时间（毫秒）"maxRetryCount": 3,               // 最大重试次数"minSwitchTimeInterval": 300,     // 最小切换时间间隔（毫秒）"showLog": false,                 // 是否显示心跳日志"slaveThreshold": 0.0             // 从库延迟阈值（秒），0表示不限制},// 主库列表（通常只有1个主库）"masters": ["master"                          // 主库标识，需与数据源配置对应],"maxCon": 2000,                       // 最大连接数"name": "cls01",                      // 集群名称// 读负载均衡策略"readBalanceType": "BALANCE_ALL_READ",// 所有读请求均参与负载均衡"balance": 1,                         // 负载均衡模式（1=所有读请求分发到从库）// 从库列表"replicas": ["slave1",                        // 从库1标识"slave2"                         // 从库2标识],// 故障切换策略"switchType": "SWITCH"                // 自动切换模式（当主库故障时自动切换）
}

验证 链接 mycat 创建数据库

mysql> create database testaaaaa;  ###链接mycat创建库
mysql> stop slave;  
Query OK, 0 rows affected, 1 warning (0.01 sec)  ###链接自己数据库关闭从模式

客户端链接mycat 访问数据库进行验证