MySQL数据库常用架构设计

367次阅读

没有评论

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

一、MySQL 引擎

Mysql 三种存储引擎

MySQL 提供了两种存储引擎：MyISAM 和 InnoDB，MySQL4 和 5 使用默认的 MyISAM 存储引擎。从 MySQL5.5 开始，MySQL 已将默认存储引擎从 MyISAM 更改为 InnoDB。

MyISAM 没有提供事务支持，而 InnoDB 提供了事务支持。

二、常用的 MySQL 调优策略

1、硬件层相关优化

修改服务器 BIOS 设置

选择 Performance Per Watt Optimized(DAPC) 模式，发挥 CPU 最大性能。

Memory Frequency（内存频率）选择 Maximum Performance（最佳性能）

内存设置菜单中，启用 Node Interleaving，避免 NUMA 问题

2、磁盘 I / O 相关

使用 SSD 硬盘

如果是磁盘阵列存储，建议阵列卡同时配备 CACHE 及 BBU 模块，可明显提升 IOPS。

raid 级别尽量选择 raid10，而不是 raid5.

3、文件系统层优化

使用 deadline/noop 这两种 I / O 调度器，千万别用 cfq

使用 xfs 文件系统，千万别用 ext3；ext4 勉强可用，但业务量很大的话，则一定要用 xfs；

文件系统 mount 参数中增加：noatime, nodiratime, nobarrier 几个选项（nobarrier 是 xfs 文件系统特有的）；

4、内核参数优化

修改 vm.swappiness 参数，降低 swap 使用率。RHEL7/CentOS7 以上则慎重设置为 0，可能发生 OOM

调整 vm.dirty_background_ratio、vm.dirty_ratio 内核参数，以确保能持续将脏数据刷新到磁盘，避免瞬间 I / O 写。产生等待。

调整 net.ipv4.tcp_tw_recycle、net.ipv4.tcp_tw_reuse 都设置为 1，减少 TIME_WAIT，提高 TCP 效率。

5、MySQL 参数优化建议

建议设置 default-storage-engine=InnoDB，强烈建议不要再使用 MyISAM 引擎。

调整 innodb_buffer_pool_size 的大小，如果是单实例且绝大多数是 InnoDB 引擎表的话，可考虑设置为物理内存的 50% -70% 左右。

设置 innodb_file_per_table = 1，使用独立表空间。

调整 innodb_data_file_path = ibdata1:1G:autoextend，不要用默认的 10M, 在高并发场景下，性能会有很大提升。

设置 innodb_log_file_size=256M，设置 innodb_log_files_in_group=2，基本可以满足大多数应用场景。

调整 max_connection（最大连接数）、max_connection_error（最大错误数）设置，根据业务量大小进行设置。

另外，open_files_limit、innodb_open_files、table_open_cache、table_definition_cache 可以设置大约为 max_connection 的 10 倍左右大小。

key_buffer_size 建议调小，32M 左右即可，另外建议关闭 query cache。

mp_table_size 和 max_heap_table_size 设置不要过大，另外 sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size 等设置也不要过大。

三、MySQL 常见的应用架构分享

1、主从复制解决方案

这是 MySQL 自身提供的一种高可用解决方案，数据同步方法采用的是 MySQL replication 技术。MySQL replication 就是从服务器到主服务器拉取二进制日志文件，然后再将日志文件解析成相应的 SQL 在从服务器上重新执行一遍主服务器的操作，通过这种方式保证数据的一致性。

为了达到更高的可用性，在实际的应用环境中，一般都是采用 MySQL replication 技术配合高可用集群软件 keepalived 来实现自动 failover，这种方式可以实现 95.000% 的 SLA。

MySQL 数据库常用架构设计

2、MMM/MHA 高可用解决方案

MMM 提供了 MySQL 主主复制配置的监控、故障转移和管理的一套可伸缩的脚本套件。在 MMM 高可用方案中，典型的应用是双主多从架构，通过 MySQL replication 技术可以实现两个服务器互为主从，且在任何时候只有一个节点可以被写入，避免了多点写入的数据冲突。同时，当可写的主节点故障时，MMM 套件可以立刻监控到，然后将服务自动切换到另一个主节点，继续提供服务，从而实现 MySQL 的高可用。

MySQL 数据库常用架构设计

3、Heartbeat/SAN 高可用解决方案

在这个方案中，处理 failover 的方式是高可用集群软件 Heartbeat，它监控和管理各个节点间连接的网络，并监控集群服务，当节点出现故障或者服务不可用时，自动在其他节点启动集群服务。在数据共享方面，通过 SAN（Storage Area Network）存储来共享数据，这种方案可以实现 99.990% 的 SLA。

MySQL 数据库常用架构设计

4、Heartbeat/DRBD 高可用解决方案

此方案处理 failover 的方式上依旧采用 Heartbeat，不同的是，在数据共享方面，采用了基于块级别的数据同步软件 DRBD 来实现。

DRBD 是一个用软件实现的、无共享的、服务器之间镜像块设备内容的存储复制解决方案。和 SAN 网络不同，它并不共享存储，而是通过服务器之间的网络复制数据。

MySQL 数据库常用架构设计

四、MySQL 经典应用架构

MySQL 数据库常用架构设计

其中：

Dbm157 是 MySQL 主，dbm158 是 MySQL 主的备机，dbs159/160/161 是 MySQL 从。

MySQL 写操作一般采用基于 heartbeat+DRBD+MySQL 搭建高可用集群的方案。通过 heartbeat 实现对 MySQL 主进行状态监测，而 DRBD 实现 dbm157 数据同步到 dbm158。

读操作普遍采用基于 LVS+Keepalived 搭建高可用高扩展集群的方案。前端 AS 应用通过提高的读 VIP 连接 LVS，LVS 有 keepliaved 做成高可用模式，实现互备。

最后，MySQL 主的从节点 dbs159/160/161 通过 MySQL 主从复制功能同步 MySQL 主的数据，通过 lvs 功能提供给前端 AS 应用进行读操作，并实现负载均衡。