MySQL架构的优化

Panda_XiaoXi
3,118 阅读12分钟

  • mysql的复制：

      实现在不同服务器上的分布：
  	利用二进制日志增量进行；
  	不需要太多的带宽，但是使用基于行的复制在进行大批量的更改时，会对带宽带来一定的压力，特别是跨IDC环境下进行的复制；	
  	应该分批进行
  实现数据读取的负载均衡，需要其他组件配合使用
  增加数据的安全性
  实现数据库高可用和故障切换
  实现数据库在线升级

  • mysql的二进制日志：记录了所有对MySQL数据库的数据增删查改和对表和数据库的修改

  • binlog命令行的工具进行查看

    二进制日志格式：

      基于段的日志格式： binlog_format=STATEMENT

  基于行的日志格式： binlog_format=ROW
  	binlog_row_image=[FULL|MINIMAL|NOBLOB]

  mysqlbinlog -vv 日志文件名称；
  
  混合日志格式：binlog_format=MIXED
  	根据sql语句由系统决定使用基于段还是基于行的日志格式；
  	数据量的大小由所执行的sql语句决定

    建议使用binlog_format=MIXED 或者 binlog_format=ROW并设置binlog_row_image=MINIMAL

  • 基于SQL语句的复制(SBR)

    优点： 生成的日志量少，节约网络传输IO 并不强制要求主从数据库的表定义完全相同 相比于基于行的复制方式更为灵活

    缺点： 对于非确定性事件，无法保证主从数据的一致性 对于存储过程，触发器，自定义函数进行的修改也可以造成数据不一致 相对于基于行的复制方式在从上执行时需要更多的行锁

  • 基于行的复制（PBR）

    优点：

      可以应用于任何SQL的复制包括非确定确定函数，存储过程等；
  可以减少数据库锁的使用

    缺点：

      要求主从数据库的表的结构相同，负责可能会中断复制；
  无法在从服务器上单独执行触发器

  • mysql复制的工作方式

mysql复制的工作方式

  1. 主服务器将变更写入到二进制文件中
  2. 从服务器读取主服务器的二进制日志变更写入到relay_log中
  3. 在从服务器上重放rely_log中的日志
基于日志点的复制配置的步骤：
1.在主DB服务器上建立复制账号
	
	create user 'repl' @ 'IP段' identified by 'password';
	grant replication slave on *.* to 'repl' @ 'IP段';//授权
	
2.配置主库服务器

	bin_log=mysql-bin
	server_id = 100

3.配置数据库从服务器
	
	bin_log = mysql-bin
	server_id = 101
	relay_log = mysql-relay-bin
	log_slave_update = on[可选]
	read_only = on[可选]

4.初始化从服务器数据
	
	mysqldump --master-data=2 -single-transaction
	xtrabackup --slave-info[对innodb性能不进行锁表]

5.启动复制链路
	
	change master to master_host = '主服务器的ip地址' ，
		master_user = 'repl'，
		master_password = 'password'，
		master_log_file = 'mysql_log_file_name'，
		master_log_pos = 4; 
实际的配置：

	1.配置主库：
		create user repl@'192.168.25.%' identified by '123456';
		grant replication slave on *.* to repl@'192.168.25.%';
	
	2.修改主从配置文件

	3.主数据库备份：mysqldump --single-transcation --master-data --triggers --			routines --all-databases >> all.sql

	4.将主库的生成的备份文件传递到从服务器上：scp all.sql root@192.168.15.130:/root

	5.在从服务器上执行该sql文件： mysql -uroot -p < all.sql

	6.在从服务器上配置数据链路：change master to master_host='192.168.25.33',
							master_user='repl',
							master_password='123456',
							master_log_file='mysql-bin.0000003',
							mater_log_pos=1829; 
	
	7.在从服务器上启动复制链路：start slave;
基于日志点的复制优点：
	是mysql最早的复制技术，bug较少
	对于sql查询没有任何限制
	故障处理比较容易
基于日志点的复制缺点：
	故障转移时重新获取新主的日志点的信息比较困难
基于GTID的复制配置的步骤：
	1.在主DB服务器上简历复制账号
	
		create user 'repl' @ 'IP段' identified by 'password';
		grant replication slave on *.* to 'repl' @ 'IP段';//授权
	
	2.配置主库服务器

		bin_log=mysql-bin
		server_id = 100
		gtid_mode = on
		
		enforce-gtid-constistency
			不支持以下操作：create table ... select
						  在事务中使用create temporary建立临时表
						  使用关联更新事务表和非事务表
		log-slave-updates = on[5.7版本之前加]

	3.配置数据库从服务器
	
		server_id = 101
		relay_log = mysql-relay-bin
		gtid_mode=on
		enforce-gtid-constistency
		read_only = on[可选]
		master_info_repository=table
		relay_log_info_repository=table
	
	4.初始化从服务器数据
	
		mysqldump --master-data=2 -single-transaction
		xtrabackup --slave-info

	5.启动复制链路
		
		change master to master_host = '主服务器的ip地址' ，
			master_user = 'repl'，
			master_password = 'password'，
			master_auto_position=1;

基于gtid的复制的优点：
	可以很方便的进行故障的转移
	从库不会丢失主库上的任何修改

基于gtid的复制的缺点：
	故障处理比较复杂
	对执行的SQL有一定的限制

  • 如何选择复制模式

    1. 所使用的MySQL版本
    2. 复制的架构及主从切换的方式
    3. 所使用的高可用管理组件
    4. 对应用的支持程度

  • MySQL复制拓扑结构

    MySQL5.7之前，一个从库只能有一个主库

    MySQL5.7之后支持一从多主架构

常见的拓扑的设计及设计的注意事项:

  • 一主多从的复制拓扑

一主多从的复制拓扑

优点：配置简单
	 可以用多个从库分担读负载

用途：为不同业务使用不同的从库
	 将一台从库放到远程的IDC中，用作灾备恢复
	 分担主库的读负载
  • 主-主复制拓扑

主-主复制拓扑

缺点：
	产生数据冲突而造成复制链路的中断
	耗费大量的时间
	造成数据的丢失

主主模式下的主主复制的配置的注意事项：
	两个主中所操作的表最好能够分开
	使用下面两个参数自增ID的生成
		auto_increment_increment = 2
		auto_increment_offset = 1|2
  • 主-备复制的拓扑

主-备复制的拓扑

特点：
	只有一台主服务器对外提供服务
	一台服务器处于只读状态并且只作为热备使用
	在对外提供的主库出现故障或是计划性的维护时才会进行切换,使原来的备库成为主库，而原来的主库会成为新的备库并处理只读或下线状态，待维护完成后重新上线

主备模式下的主主复制的配置的注意事项：
	确保两台服务器上的初始化的初始数据相同
	确保两台服务器上已经启动binlog并且有不同的server_id
	在两台服务器上启用log_slave_updates参数
	在初始的备库上启用read_only
  • 带从服务器的主主复制的拓扑

带从服务器的主主复制的拓扑

  • 级联复制的拓扑

    将一个主服务器配置一个从服务器通过从服务器来分发进行复制

    mysql级联复制

MySQL复制性能的优化

  • 影响主从延迟的因素
  1. 主库写入二进制日志的时间 --> 控制主库中执行事务的大小，分隔大事务
  2. 二进制日志传输时间 --> 使用mixed日志格式，设置set binlog_row_image=minimal;
  3. 默认情况下只有一个SQL线程，主上并发修改在从上变成了串行化 --> 使用多线程复制

  • 如何在MySQL5.7中配置数据库的多线程的复制[MYSQL5.6中引入多线程的复制,在MySQL5.7中可以按照逻辑时钟的方式来分配SQL线程]：

    1.stop slave //停止主从复制 2.set global slave_parallel_type='logical_clock'; //设置分配线程的方式 3.set global slave_parallel_workers=4; //设置4个复制线程 4.start slave;

MySQL复制常见的问题

  • 由于数据的损坏或者丢失所引起的主从复制错误

    主库或者从库的意外宕机所引起的错误 --> 跳过二进制日志的事件/注入空事务的方式先恢复中断的复制链路，再使用其他的方法来对比主从服务器上的数据

    主库上的二进制日志损坏 --> 通过change master命令来重新指定

    备库上的中继日志损坏

    在从库上进行数据修改造成的主从复制的错误

    不唯一的server_id或者server_uuid(server_uuid是记录在数据目录中的auto.cnf文件中，一旦存在mysql不会生成server_uuid)

    max_allow_packet设置引起的主从复制错误

MySQL主从复制无法解决的问题

  • 无法分担主数据库的写负载
  • 无法自动进行故障转移及主从切换
  • 不提供读写分离的功能

利用主从复制搭建高可用的架构

  • 高可用性指的是通过尽量缩短因日常维护操作（计划）和突发的系统崩溃（非计划）所导致的体积世家你，以提高系统和应用的可用性。

  • 实现高可用

    》避免导致系统不可用的因素，减少系统不可用的时间[服务器磁盘空间耗尽、性能低下的SQL、表结构和索引没有优化、主从数据不一致、人为的操作失误]

    》建立完善的监控及报警系统

    》对备份数据进行恢复测试

    》正确配置数据库环境

    》对不需要的数据进行归档和清理

    》增加系统冗余，保证发生系统不可用时可以尽快的恢复[避免存在单点故障、主从切换及故障转移]

      利用sun共享存储或者drdb磁盘复制解决MySQL单点故障
  利用多写集群或者NDB集群来解决MySQL单点的故障
  利用MySQL的复制来解决MySQL的单点登录的问题

MySQL数据库的MMM架构

在主库出现宕机时进行故障转移并自动配置其他从对新主的复制

提供了主，写虚拟ip，在主从服务器出现问题时可以自动迁移虚拟ip

MySQL数据库的MMM架构

  • MMM部署所需资源
名称资源 数量 说明
主DB服务器 2 用于主备模式的主主复制配置
从DB服务器 0-N 可以配置0台或多台从服务器，但建议不要太多
监控服务器 1 用于监控mysql监控集群
IP地址 2*(n+1) n为MySQL服务器的数量
监控用户 1 用于监控数据库状态的MySQL用户(replication client)
代理用户 1 用于MMM代理的MySQL用户(super,replication client,process)
复制用户 1 用于配置MySQL复制的MySQL用户(replication slave)
MMM架构的部署步骤

  1. 配置主主复制及主从同步集群

  2. 安装主从节点所需要的支持包（per依赖包）

  3. 安装及配置MMM工具集

  4. 运行MMM监控

  5. 测试

MySQL高可用架构之MMM架构的详细配置

MMM集群的优缺点：

》MMM集群的优点：

  1. 使用Perl脚本语言开发及完全开源
  2. 提供了读写VIP(虚拟IP),使服务器角色的变更对前端应用透明
  3. MMM提供了从服务器的延迟监控
  4. MMM提供了主数据库故障转移后从服务器对新主的重新同步的功能
  5. 很容易对发生故障的主数据库重新上线
  6. MMM提供了从服务器的延迟监控

》MMM集群的缺点：

  1. 发布时间比较早不支持MySQL新的复制功能
  2. 没有读负载均衡的功能
  3. 在进行主从切换时，容易造成数据丢失
  4. MMM监控服务存在单点故障

MySQL数据库的MHA架构

监控主数据库服务器是否可用

当主DB不可用时，从多个从服务器中选举出新的主数据库服务器

提供了主从切换和故障转移功能（MHA可以半同步功能结合起来使用）

  • MHA如何进行主从切换

    当主数据库出现故障时，尝试从出现故障的主数据库保存二进制日志

    从多个备选从服务器中选举出新的备选主服务器

    在备选主服务器和其它从服务器之间同步差异二进制数据

    应用从原主DB服务器上保存的二进制日志

    提升备选主DB服务器为新的主DB服务器

    迁移集群中的其它从DB做为新的主DB的从服务器

  • MHA支持GTID的复制和日志点的复制

  • MMM不支持GTID的复制

MHA架构的部署步骤

  1. 配置集群内所有服务器的SSH的免认证登录

    ssh -keygen

    ssh-copy-id -i /root/.ssh/id_rsa '-p 22 root@192.168.3.100

    ssh-copy-id -i /root/.ssh/id_rsa '-p 22 root@192.168.3.101

    ssh-copy-id -i /root/.ssh/id_rsa '-p 22 root@192.168.3.102

  2. 安装MHA-node软件包和MHA-manager软件包

  3. 安装MHA的支持包

    监控节点：yum -y install perl-Config-Tiny.noarch perl-Time-HiRes.X86_64 perl-Parallel-ForkManager perl-Log-Dispatch-Perl.noarch per-DB-MySQL ncftp

    数据节点：yum -y install perl -DBD-MySQL ncftp perl-DBI.X86

  4. 建立主从复制集群

  5. 配置MHA管理节点

  6. 使用master_check_ssh和masterha_check_repl对配置进行检验

  7. 启动并测试MHA服务

MySQL高可用架构之MHA介绍及配置

MHA集群的优缺点：

》MHA集群的优点：

  1. 使用Perl脚本语言开发及完全开源
  2. 可以支持就GTID的复制模式
  3. MHA在进行故障转移时不容易产生数据丢失
  4. 同一个监控节点可以监控多个集群

》MMM集群的缺点：

  1. 需要编写脚本或利用第三方工具（keepalive等等）来实现VIP的配置
  2. MHA启动后只会对主数据库进行监控
  3. 需要基于SSH免认证配置，存在一定的安全隐患
  4. 没有提供从服务器的读负载均衡的功能

数据库的读写分离（如何在复制集群的不同角色上，去执行不同的SQL语句）

程序实现的读写分离

优点：由开发人员控制什么样的查询再从库上来执行，因此比较灵活
	 由程序直接连接诶数据库，所以性能损耗比较少

缺点：增加了开发的工作量，使得程序代码更为复杂人为控制，
	 容易出现错误

中间件的读写分离(mysql-proxy、maxScale)

优点：中间件根据查询语法的分析，自动完成读写分离；
	 对程序透明，对已有的程序不用做任何的调整。

缺点：增加了中间层，所以对查询效率有损耗；
	 对于延迟敏感业务无法在主库上执行

实现读的负载均衡（具有相同角色的数据库，如何共同分担相同的负载）

软件：LVS、Haproxy、MaxScale

硬件：F5

maxScale插件:

maxScale插件体系结构

  1. Authentication 认证插件
  2. Protocal 协议插件
  3. Router 路由插件（readconnroute、readwritesplit）
  4. MOnitor 监控插件
  5. Filter&Logging 日志和过滤插件

maxScale实现读写分离及负载均衡

avatar
文章
阅读
粉丝
相关推荐
MySQL优化篇
627阅读
 · 
17点赞
MySQL优化（索引优化）
439阅读
 · 
15点赞
Mysql进阶之索引优化
1.3k阅读
 · 
6点赞
MySQL 索引及优化实战
1.5k阅读
 · 
3点赞
MySQL百万数据深度分页优化思路分析
14k阅读
 · 
132点赞