MySQL复制以及调优

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 本文介绍了MySQL自带复制方案的实现及其注意事项。复制方案能提供数据备份、负载均衡与分布式数据管理的优势。文章详细描述了复制步骤:主库(master)记录更改到二进制日志,发送同步消息给从库(slave),从库接收后将日志复制到本地并执行。实现复制包括配置主库的server-id和二进制日志、创建复制账号、初始化主库数据、设置从库参数及开启复制。此外,还探讨了三种日志格式(row、statement、mixed)的特点及选择建议,并分析了主从复制延迟的优化方法,如控制事务大小、优化日志传输和多线程还原日志等。最后,文中列出了搭建过程中需要注意的关键点。

一. 简介

MySQL自带复制方案,带来好处有:

数据备份。

负载均衡。

分布式数据。

概念介绍:

主机(master):被复制的数据库。

从机(slave):复制主机数据的数据库。

复制步骤:

(1). master记录更改的明细,存入到二进制日志(binary log)。

(2). master发送同步消息给slave。

(3). slave收到消息后,将master的二进制日志复制到本地的中继日志(relay log)。

(4). slave重现中继日志中的消息,从而改变数据库的数据。

下面放一张经典的图片来说明这一过程:

二. 实现复制

实现复制有以下步骤:

1.设置MySQL主库的二进制日志以及server-id

MySQL配置文件一般存放在/etc/my.cnf

ini

代码解读

复制代码

# 在[mysqld]下面添加配置选项
[mysqld]
server-id=1
log-bin=mysql-bin.log

server-id是数据库在整个数据库集群中的唯一标示,必须保持唯一。

重启MySQL。

注:如果MySQL配置文件中已经配置过此文件,则可以跳过此步。

2.新建复制账号

在主库里面新建用于从库复制主库数据的账号,并授予复制权限。

pgsql

代码解读

复制代码

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO user_name@'host' IDENTIFIED BY 'password';

3.设置MySQL主库server-id

和第二步配置一样,要注意的地方有两点:

  1. 如果不需要从库作为别的从库的主库的话,则不需要配置二进制日志。
  2. 很多时候复制并不需要复制主库的全部数据库(特别是mysql的信息配置库)。因此可以配置replicate_do_db来指定复制的数据库

4.从库初始化主库的数据

如果数据量不算大的情况下,可以使用mysqldump工具导出主库数据,然后导入到从库里面。

brainfuck

代码解读

复制代码

mysqldump --single-transaction --triggers --master-data databasename > data.sql

如果数据量大的情况下应该使用Xtrabackup去进行数据库的导出,此处不做介绍。

可能会有同学问,为什么不直接使用二进制日志进行初始化呢?

  1. 如果我们主库运行了比较长的一段时间,并不太适合使用从库根据二进制日志进行复制数据,直接使用二进制日志去初始化从库会比较耗费时间和性能。
  2. 更多的情况下,主库的二进制日志的配置项没有打开,因此也就不存在以前操作的二进制日志。

5.开启复制

从库执行下面命令

routeros

代码解读

复制代码

mysql> CHANGE MASTER TO MASTER_HOST='host',
-> MASTER_USER='user',
-> MASTER_PASSWORD='password',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=0;

注意最后的两个命令:MASTER_LOG_FILE和MASTER_LOG_POS,表示从库的从哪个二进制文件开始读取,偏移量从那里开始,这两个参数可以从我们导入的SQL里面找到。

开启复制

crmsh

代码解读

复制代码

start slave;

这时候就完成了复制,在主库更新一个数据或者新增数据在从库都可以查询到结果。

在主库上也可以查询的到复制线程的状态。

三. 复制的日志格式

MySQL复制的日志格式有三种,根据主库存放数据的方式不同有以下三种:

复制方式 特点 优点 缺点
row 基于行的格式复制,记录需要修改的每行的数据信息。 如果一个SQL修改了2w行的数据,那么就会记录2w行的日志格式 保证了数据的强一致性,且由于记录的是执行后的结果,在从库上执行还原也会比较快 日志记录数量很多,主从之间的传输需要更多的时间。
statement 基于段的日志格式复制,也就是记录下更改的SQL记录,而不是更改的行的记录。 日志记录量最小。 对于一些输出结果不确定的函数,在从库上执行一遍很可能会出现问题,如uuid,从库根据日志还原主库数据的时候需要执行一遍SQL,时间相对较慢。
mixed 混合上面两种日志格式记录记录日志,至于什么时候使用哪种日志方式由MySQL本身决定。 可以平衡上面两种日志格式的优缺点。

mysql5.7以前默认使用statement格式。

设置方式,可以在配置文件设置(首选):

ini

代码解读

复制代码

binlog_format=ROW

或临时设置全局变量(当前mysql连接有效):

routeros

代码解读

复制代码

查看日志格式
mysql > show variables like 'binlog_format';

设置日志格式
mysql > set binlog_format='row';

由于两个主从服务器一般都会放在同一个机房里面,两者之间同步的速度会会比较快,为保证强一致性,应该首选行的日志格式记录(row),保证传输素速度可以选择混合方式(mixed)。

而行的日志格式有下面三种记录方式:

记录方式 特点
minimal 只记录被修改列的数据
full 记录被修改的行的全部列的数据
noblob 特点同上,只是如果没有修改blob和text类型的列的情况下,不会记录这些列的数据(也就是大数据列)

mysql默认是full,最好修改成minimal。

ini

代码解读

复制代码

binlog_row_image=minimal

四. 主从复制延迟

由于主库和从库之间不在同一个主机上,数据同步之间不可以避免地具有延迟,解决的方法有添加缓存,业务层的跳转等待,如果非得从数据库层面去减缓延迟问题,可以从复制时候的三大步骤(主库产生日志,主从传输日志,从库还原日志内容)入手:

1.主库写入到日志的速度

控制主库的事务大小,分割大事务为多个小事务。

如插入20w的数据,改成插入多次5000行(可以利用分页的思路)

2.二进制日志在主从之间传输时间 主从之间尽量在同一个机房或地域。

日志格式改用MIXED,且设置行的日志格式未minimal,原理详见上面的日志格式介绍。

3.减少从库还原日志的时间

在MySQL5.7版本后可以利用逻辑时钟方式分配SQL多线程。

设置逻辑时钟:slave_parallel_type=‘logical_clock’;

设置复制线程个数:slave_parallel_workers=4;

五. 需要注意的地方

  1. 重启MySQL最好切换未MySQL用户再进行操作,不然文件启动后会有权限问题。
  2. 搭建好MySQL的环境后就设置好配置里的log-bin选项,这样以后如果数据库需要从库的复制,就不需要重启数据库,打断业务的进行。
  3. 需要打开主库的防火墙的对应的mysql端口。
  4. 由于从库同步主库的方式,监听主库发送的信息,而不是轮询,因此如果出现通信出现了故障,重新连接后如果主库没有进行数据更改的操作,从库不会同步数据,因此可以通过插入空事务的方式同步数据。


转载来源:https://juejin.cn/post/6844903556089135118

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
10月前
|
SQL 监控 关系型数据库
MySQL性能调优:监控和优化
MySQL性能调优:监控和优化
184 1
|
4月前
|
缓存 关系型数据库 MySQL
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
409 66
|
4月前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
785 1
|
6月前
|
存储 SQL 关系型数据库
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
MySQL调优主要分为三个步骤:监控报警、排查慢SQL、MySQL调优。 排查慢SQL:开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优: 基础优化:缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表; 表设计优化:数据类型优化、冷热数据分表等。 索引优化:考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
935 15
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
|
9月前
|
SQL 算法 关系型数据库
【MySQL】专栏合集,从基础概念到调优
【MySQL】专栏合集,从基础概念到调优
74 0
|
6月前
|
存储 缓存 关系型数据库
【MySQL调优】如何进行MySQL调优?一篇文章就够了!
MySQL调优主要分为三个步骤:监控报警、排查慢SQL、MySQL调优。 排查慢SQL:开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优: 基础优化:缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表; 表设计优化:数据类型优化、冷热数据分表等。 索引优化:考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
【MySQL调优】如何进行MySQL调优?一篇文章就够了!
|
4月前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
578 0
|
7月前
|
关系型数据库 MySQL Java
面试官:说说MySQL调优?
面试官:说说MySQL调优?
132 5
面试官:说说MySQL调优?
|
7月前
|
SQL 关系型数据库 MySQL
Mysql原理与调优-事务与MVCC
【8月更文挑战第19天】
|
8月前
|
SQL 关系型数据库 MySQL
MySQL服务器性能调优的顶级策略14
【7月更文挑战第14天】MySQL服务器性能调优的顶级策略
100 12

推荐镜像

更多