阅读 989

MySQL架构的优化

  • 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实现读写分离及负载均衡

关注下面的标签,发现更多相似文章
评论