mysql主从同步(低版本)参数说明

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: <p style="line-height:26px; font-family:arial; padding-top:0px; padding-bottom:0px; margin-top:0px; margin-bottom:1em; clear:both; height:auto; overflow:hidden; color:rgb(109,109,109)"> 在主服务器上为从服

在主服务器上为从服务器建立一个用户:

grant replication slave on *.* to '用户名'@'主机' identified by '密码';

如果使用的是MySQL 4.0.2之前的版本,则用file权限来代替replication slave

编辑主服务器的配置文件:/etc/my.cnf

server-id=1
log-bin
binlog-do-db=需要复制的数据库名,如果复制多个数据库,重复设置这个选项即可
binlog-ignore-db=不需要复制的数据库苦命,如果复制多个数据库,重复设置这个选项即可

注意:如果你想做一个复杂点的结构:比如说,A->B->C,其中B是A的从服务器,同时B又是C的主服务器,那么B服务器除了需要打开log-bin之外,还需要打开log-slave-updates选项,你可以再B上使用“show variables like 'log%';”来确认是否已经生效。

编辑从服务器的配置文件:/etc/my.cnf

server-id=2
master-host=主机
master-user=用户名
master-password=密码
master-port=端口
replicate-do-db=需要复制的数据库名,如果复制多个数据库,重复设置这个选项即可
replicate-ignore-db=需要复制的数据库名,如果复制多个数据库,重复设置这个选项即可

配置主从服务器的my.cnf时,留心各自的server-id一定要彼此独立,不能重复,否则,会出现如下错误:

Slave: received end packet FROM server, apparent master shutdown

 

另一个需要注意的是最好在从服务器的my.cnf里设置read_only选项,防止发生意外(连接用户不能有SUPER权限,否则无效)。

记得先手动同步一下主从服务器,数据量小的话可以用mysqldump,它有一个master-data参数很有用,通过使用此参数,导出的SQL文件里会自动包含CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...;,这样创建从服务器就更方便了。

如果数据量大的话不太适合使用mysqldump(慢),如果是myisam表的话,加上--lock-all-tables参数,如果是innodb表的话,加上--single-transaction参数。

而应该采用拷贝文件的方式,请按如下操作步骤:

先在主服务器上锁定所有的表,以免在复制过程中数据发生变化:

mysql> flush tables with read lock;

然后在主服务器上查询当前二进制文件的文件名及偏移位置:

mysql > show master status;

然后停止主服务器上的MySQL服务:

shell> mysqladmin -u root shutdown

注意:如果仅是MyISAM的话,可以不停止MySQL服务,但要在复制数据文件的过程中保持只读锁,如果是InnoDB的话,必须停止MySQL服务。

再拷贝数据文件:

shell> tar -cvf /tmp/mysql-snapshot.tar .

拷贝完别忘了启动主服务上的MySQL服务了。

然后把数据文件应用到从服务器上,再次启动slave的时候使用,记得启动时加上skip-slave-start选项,使之不会立刻去连接master,再在从服务器上设置相关的二进制日志信息:

mysql> CHANGE MASTER TO
->     MASTER_HOST='master_host_name',
->     MASTER_USER='replication_user_name',
->     MASTER_PASSWORD='replication_password',
->     MASTER_LOG_FILE='recorded_log_file_name',
->     MASTER_LOG_POS=recorded_log_position;

启动从服务器上的复制线程:

mysql> start slave;

验证主从设置是否已经成功,可以输入如下命令:

mysql> show slave status\G

会得到类似下面的列表:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

如果这两个选项不全是Yes,那就说明你前面某个步骤配置错了。

应 该保证从服务器上任何数据的修改都是通过从主服务器上复制操作获取的,换句话说,从服务器应该是只读的,如果不能保证这一点,则可能造成主从数据不一致。 可以在从服务器的my.cnf里加入read-only参数来实现这一点,唯一需要注意的一点事read-only仅对没有super权限的用户有效。所 以最好核对一下连接从服务器的用户,确保其没有super权限。

从 理想角度看,主从数据库应该无故障的运转下去,可以有时候还是会出现一些莫名其妙的问题,比如说即便从未在从服务器上手动更新过数据,但还是可能遇到 “Error: 1062 Duplicate entry”错误,具体原因不详,可能是MySQL本身的问题。遇到这类问题的时候,从服务器会停止复制操作,我们只能手动解决问题,具体的操作步骤如 下:

mysql> set global sql_slave_skip_counter = 1;
mysql> start slave;

同样的操作可能需要进行多次,也可以设置自动处理此类操作,在从服务器的my.cnf里设置:

slave-skip-errors=1062

最后再唠叨一下日志的问题:时间长了,数据库服务器上的二进制文件会越来越多,清理是必要的,你可以设置自动清理,相关参数是expire_logs_days,也可以使用手动删除的方式,但这里说的手动不是指rm,而是指PURGE BINARY LOGS,删除任何日志前,最好在所有的从服务器上通过show slave status命令确认一下相关日志是否已经无用。

更详细的介绍参考官方文档:How to Set Up Replication,不喜欢英文的话可以看老叶同志的中文翻译

补充:[ERROR] Error in Log_event::read_log_event(): 'Event too big'

在使用主从复制的时候,出现的问题多半是和日志(主服务器的二进制日志,从服务器的延迟日志)相关的。比如说加入你遇到了上面的错误,你可以根据错误日志的信息在主从数据库服务器上分别执行:

mysqlbinlog 日志文件 > /dev/null

查 看错误,如果没有错误,则不会有任何输出,反之会输出错误信息,如果确定了错误是出现在主服务器二进制日志上,可以跳过适当的位置,再在从服务器上重新设 定LOG_POS,如果确定了错误是出现在从服务器延迟日志上,则可以删除从服务器的延迟日志(使用CHANGE TO MASTER的时候,除非设定了延迟日志信息,否则会自动删除延迟日志),并在从服务器上重新设定LOG_POS。期间也可以考虑手动执行不能自动执行的 SQL日志。

补充:配置的时候如果版本允许最好打开sync_binlog选项。

补充:有时候,从服务器延迟日志可能已经损坏,这时需要执行CHANGE MASTER TO设置新的日志文件信息,但是在从服务器上SHOW SLAVE STATUS会显示很多日志信息,他们的含义有所不同:

Master_Log_File:Read_Master_Log_Pos 是IO相关的日志信息
Relay_Master_Log_File:Exec_Master_Log_Pos 是SQL相关的日志信息

从服务器需要设置的是SQL相关的日志信息:

slave stop;
change master to master_log_file=’(binlog name in relay_master_log_file)’, master_log_pos=(exec_master_log_pos number);
slave start;


1) When you are using the master as a consistent snapshot, use SHOW MASTER STATUS to determine the position.
2) When you are using a slave as a consistent snapshot, use SHOW SLAVE STATUS and Exec_Master_Log_Pos.

参考链接

补充:缺省情况下,从服务器会以主机名命名延迟日志,所以一旦你修改了从服务器的主机名就会造成问题,新版MySQL会提示你这个情况:

[Warning] Neither --relay-log nor --relay-log-index were used;
so replication may break when this MySQL server acts as a slave and has his hostname changed!!
Please use '--relay-log=name-relay-bin' to avoid this problem.

设定relay-log可以避免此类问题。
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
5天前
|
canal 关系型数据库 MySQL
实时计算 Flink版产品使用问题之在进行整库同步MySQL数据到StarRocks时,遇到全量数据可以同步,但增量数据无法同步,是什么导致的
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
4天前
|
消息中间件 关系型数据库 MySQL
实时计算 Flink版操作报错合集之同步MySQL数据并EP(复杂事件处理)时,编译报错,如何解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
5天前
|
JSON 关系型数据库 MySQL
实时计算 Flink版产品使用问题之在使用CDAS语法同步MySQL数据到Hologres时,如果开启了字段类型宽容模式,MySQL中的JSON类型会被转换为什么
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
5天前
|
Oracle 关系型数据库 MySQL
实时计算 Flink版产品使用问题之整库从mysql同步到StarRocks里面,首次全量是否会对mysql造成大量资源消耗,导致影响业务服务
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
6天前
|
关系型数据库 MySQL 数据处理
实时计算 Flink版产品使用问题之第一次启动全库同步了, 然后增删改的mysql数据没有及时同步,是什么导致的
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
6天前
|
关系型数据库 MySQL Serverless
实时计算 Flink版产品使用问题之使用cdas语法同步mysql数据到sr serverless是否支持动态加表
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
4天前
|
关系型数据库 MySQL API
实时计算 Flink版操作报错合集之同步MySQL数据到另一个MySQL数据库,第一次同步后源表数据发生变化时目标表没有相应更新,且Web UI中看不到运行的任务,该怎么解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
3天前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第三篇(MySQL性能优化)
MySQL数据库进阶第三篇(MySQL性能优化)
|
1天前
|
存储 自然语言处理 关系型数据库
|
1天前
|
存储 关系型数据库 MySQL
关系型数据库mysql文件系统兼容性
【6月更文挑战第14天】
22 3