MySQL 主从复制:基于二进制文件复制配置详解

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

MySQL 主从复制:基于二进制文件复制配置详解

前言
主从复制是指把一个MySQL的数据库服务器作为主服务器(master),然后把master的数据复制到一个或者多个MySQL数据库服务器作为从服务器(slave)。从master到slave的复制一般是异步复制,所以从服务器的复制可以随时停止,也不会影响到主服务器的使用。可以通过配置来决定只复制哪些数据库或者哪些表的数据。

主从复制的优点
读写分离提高负载:master服务器不在负载读操作,只处理写入和更新操作,可显著提升主服务器的写操作的性能。而读操作是通过多个slave服务器来读取数据,多个slave服务器可以分散读操作的压力,减少对单机I/O和带宽的依赖,也可以提升读操作的性能。
数据安全:因为数据是异步复制过来的,可以在slave服务器上进行测试,或者进行数据分析。即不会更改master服务器的数据,也不会影响master服务器的性能
远程数据分发:当你需要在本地使用数据时,可以通过复制功能把数据复制到本地,这样就不需要访问远程master服务器
基于二进制文件(binary log)复制
原理介绍
1、master服务器把对源数据库的写入和更新操作以事件的方式记录到二进制日志文件(binary log)中。不同的操作方式会以不同的日志格式记录到文件中
2、slave服务器开启一个I/O线程连接master服务器去请求binary log,然后写入到本地的中继文件(relay log)中
3、master的开启一个log dump线程读取binary log并传送给slave的I/O线程
4、slave开启一个SQL线程读取relay log中的命令,在slave服务器上执行

线程说明参考

环境准备
操作系统 数据库 ip地址 端口 主/从
window server 2008 r2 mysql 8.0.12 10.119.173.98 3006 主
window server 2008 r2 mysql 8.0.12 10.119.173.97 3006 从
window server 2008 r2 mysql 8.0.12 10.119.173.96 3006 从
1.Master配置
1.需要设置一个唯一的 server-id ,并且设置二进制日志文件 log-bin=[file_name]。

MySQL8.0之前的版本默认log_bin=OFF是关闭日志记录的,设置了log-bin,就会开启记录日志log_bin=ON

编辑master的配置文件my.ini,在[mysqld]下添加如下内容

[mysqld]
server-id=1 #服务器id
log-bin=mysql-bin #二进制日志文件的基名字

设置需要写日志的数据库的名称,不设置默认所有数据库。一个配置项只能配置一个数据库,如果要设置写多个数据库,那么需要写多份配置项,用逗号来分割多个数据库是无效。

binlog-do-db=test

binlog-do-db=test1

binlog-do-db=test2

设置不需要写日志的数据库的名称,一个配置项只能配置一个数据库,如果要设置写多个数据库,那么需要写多份配置项,用逗号来分割多个数据库是无效。

binlog-ignore-db=mysql

日志记录格式,默认是ROW行模式。还有STATEMENT语句模式和MIXED混合模式。不同的记录格式会对binlog-do-db和binlog-ignore-db产生影响

STATEMENT模式:记录执行的SQL语句

Row模式:记录语句执行后对单个行做的修改,而不是记录执行的语句

binlog-format=ROW
binlog-format的参考
binlog-do-db的说明以及和binlog-format关系的参考

2、在InnoDB存储引擎时,为了获得最大持久性和一致性,需要增加下面两个配置

控制写入二进制日志的频率。

0表示mysql服务器依赖与操作系统来把二进制文件写入到磁盘中,这种情况下性能最好。但是在断电或者操作系统崩溃时,服务器可能提交了未同步到二进制文件的事务。

1表示在提交事务前先把二进制文件写入到磁盘中,可能会对性能有印象

sync_binlog=1

控制事物提交时ACID的遵守的严格性和性能之间的平衡。默认1时,表示每次事务提交时记录日志的同时马上写入磁盘

innodb_flush_log_at_trx_commit=1
sync_binlog的参考
innodb_flush_log_at_trx_commit的参考

3、在master上给slave创建一个拥有复制权限的账号repl,密码是123456

CREATE USER 'repl'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON . TO 'repl'@'%';
2.Slave配置
1、需要给slave设置一个唯一的 server-id ,值不要和master和其他slave重复。如果需要只复制特定的数据库或者排除特定的数据库,可以使用 replicate-do-db 和 replicate-ignore-db 配置。

当启用slave的log-bin日志记录,配合log-slave-updates=ON时,slave会在复制master的数据时也记录到自己的二进制日志中。这样方便以后的主从切换,数据恢复。甚至可以实现更复杂的多级复制拓扑,比如:master->slave->slave

编辑slave的配置文件my.ini,在[mysqld]下添加如下内容

[mysqld]
server-id=2 #服务器id
log-bin=mysql-bin #二进制日志文件的基名字

当开启了log-bin时,并且log-slave-updates=ON时slave复制master的数据时也记录到自己的二进制日志中,默认值ON

log-slave-updates=ON

中继日志的基名称

relay-log=mysql-relay

设置需要复制的数据库的名称,不设置默认所有数据库。一个配置项只能配置一个数据库,如果要设置写多个数据库,那么需要写多份配置项,用逗号来分割多个数据库是无效,效果同binlog-do-db。

replicate-do-db=test

设置不需要复制的数据库的名称,一个配置项只能配置一个数据库,如果要设置写多个数据库,那么需要写多份配置项,用逗号来分割多个数据库是无效,效果同binlog-ignore-db。

replicate-ignore-db=mysql

告诉slave服务器在启动时不开启复制功能,默认值OFF

skip-slave-start=OFF
其他slave服务器配置一样,只有server-id不同
slave的配置参考

3.配置和开启复制功能
1、查询master当前的状态

因为是InnoDB,首先在master上执行FLUSH TABLES WITH READ LOCK;语句,关闭所有打开的表,刷新缓存。并且用全局读锁锁住所有的表。
然后在master上执行下面sql获取binary log的文件名称和当前写入的坐标

SHOW MASTER STATUS;
File Position Binlog_Do_DB Binlog_Ignore_DB
mysql-bin.000001 115 test mysql

2、复制master库的数据到slave库 如果在slave开启复制之前,master的test库已经有数据了,那么需要先把master的数据快照dump下来,然后复制到slave服务器。如果是新库没有数据要复制,可以跳过此步骤到下一步。 使用mysqldump工具命令如下:

mysqldump -h[IP地址] -P[端口号] -u[用户名] -p[密码] --databases test > dump.sql
mysqldump -h10.119.173.98 -P3006 -uroot -p123456 --databases test > dump.sql
复制数据快照参考

3、释放master上的全局读锁

UNLOCK TABLES;
4、导入master的test库数据到slave服务器,导入第2步中dump.sql文件。如果是新库没有数据导入,则跳过此步骤,进行到第5步。

mysql -h[IP地址] -P[端口号] -u[用户名] -p[密码] < dump.sql
mysql -h10.119.173.96 -P3006 -uroot -p123456 < dump.sql
5、在slave服务器上配置slave要复制的文件和开始的坐标

CHANGE MASTER TO
MASTER_HOST='10.119.173.98',
MASTER_PORT=3006,
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=115;
MASTER_LOG_FILE=3.配置复制功能第1步查询出来的File
MASTER_LOG_POS=3.配置复制功能第1步查询出来的Position
CHANGE MASTER TO参考

6.开启复制功能
在slave服务器上执行START SLAVE语句,开启slave的复制功能。

START SLAVE;
7.查看从库的复制状态信息

SHOW SLAVE STATUS
Slave_IO_Running和Slave_SQL_Running都=Yes,就表明复制功能正常运行

Slave_IO_Running:从master读取二进制文件,写入到slave的中继日志的I/O线程。yes表示正常
Slave_SQL_Running:从中继日志中读取新的命令执行到slave的库中的SQL线程。yes表示正常
Master_Log_File:表示I/O线程当前正在读取的master的二进制文件名称
Read_Master_Log_Pos:表示I/O线程当前正在读取的master的二进制文件中的位置信息
Relay_Log_File:当前在执行的中继日志名称
Relay_Log_Pos:当前在执行的中继日志中执行到的位置
Last_Errno:最后一次复制失败的错误日志号
Last_Error:最后一次复制失败的错误日志

当slave服务器停止了复制功能,要重新开始时,要从停止前读取的位置开始继续复制命令。第5步中MASTER_LOG_FILE=Master_Log_File的值,MASTER_LOG_POS=Read_Master_Log_Pos的值

SHOW SLAVE STATUS参考

这样就配置好了一个master->slave的主从复制,多个从库的配置可以重复【3.配置和开启复制功能的4-7步】即可。

后记
如果想配置成复杂的多级复制拓扑,比如A->B->C,那么先配置A作为master,B作为slave,B复制A的数据。然后在配置B作为master,C作为slave,C复制B的数据。

原文地址https://www.cnblogs.com/xyyz120/p/12760725.html

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
8天前
|
SQL 负载均衡 关系型数据库
mysql主从复制,从搭建到使用
mysql主从复制,从搭建到使用
21 1
|
8天前
|
Prometheus 监控 关系型数据库
数据库同步革命:MySQL GTID模式下主从配置的全面解析
数据库同步革命:MySQL GTID模式下主从配置的全面解析
22 0
|
8天前
|
SQL 监控 关系型数据库
探秘MySQL主从复制的多种实现方式
探秘MySQL主从复制的多种实现方式
12 0
|
13天前
|
SQL 关系型数据库 MySQL
mysql 主从复制与读写分离
mysql 主从复制与读写分离
|
2天前
|
运维 关系型数据库 MySQL
Serverless 应用引擎产品使用合集之如何配置MySQL的白名单IP
阿里云Serverless 应用引擎(SAE)提供了完整的微服务应用生命周期管理能力,包括应用部署、服务治理、开发运维、资源管理等功能,并通过扩展功能支持多环境管理、API Gateway、事件驱动等高级应用场景,帮助企业快速构建、部署、运维和扩展微服务架构,实现Serverless化的应用部署与运维模式。以下是对SAE产品使用合集的概述,包括应用管理、服务治理、开发运维、资源管理等方面。
|
2天前
|
分布式计算 DataWorks 关系型数据库
DataWorks操作报错合集之数据源同步时,使用脚本模式采集mysql数据到odps中,使用querySql方式采集数据,在脚本中删除了Reader中的column,但是datax还是报错OriginalConfPretreatmentUtil - 您的配置有误。如何解决
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
|
3天前
|
存储 DataWorks 关系型数据库
DataWorks产品使用合集之在使用数据集成中的同步任务从mysql同步表到oss,存储为csv时,最终生成的文件中没有表头,这个属性可以在哪里配置么
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
|
4天前
|
SQL 存储 关系型数据库
Mysql配置及常见命令
mysql 语法规范 SQL不区分大小写,但是建议关键字大写,表名、列名要小写; 每个句子写完,需要以分号结尾;
16 3
|
8天前
|
SQL Oracle 关系型数据库
mysql和oracle 命令行执行sql文件 数据库执行sql文件 执行sql语句
mysql和oracle 命令行执行sql文件 数据库执行sql文件 执行sql语句
|
8天前
|
关系型数据库 MySQL Linux
mysql 将select结果导出文件 linux
mysql 将select结果导出文件 linux