配置MySQL主从复制(一主一从)

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 配置MySQL主从复制(一主一从)

前言

MySQL主从复制简介

MySQL主从复制的目的是实现数据库冗余备份,将master数据库的数据定时同步到slave库中,一旦master数据库宕机,可以将Web应用数据库配置快速切换到slave数据库,确保Web应用有较高的可用性。

MySQL主从同步是一个异步复制的过程,要实现复制,首先需要在master上开启bin-log日志功能,bin-log日志用于记录在master库执行的增删改更新操作的sql语句,整个过程需要开启3个线程,分别是master开启I/O线程,slave开启I/O线程和SQL线程。

主从复制大致流程

  • slave上执行slave start,slave I/O线程会通过在master创建的授权用户连接到master,并请求master从指定的文件和位置发送bin-log日志内容。
  • master接收到slave的IO线程请求后,master的IO线程根据slave发送的指定bin-log日志position点之后的内容,然后返回给slave的IO线程。
  • 返回的信息中除了bin-log日志内容外,还有master最新的bin-log文件名以及在bin-log中的下一个指定更新position点。
  • slave的IO线程接收到信息后,将接收到的日志内容依次添加到slave端的relay-log文件的末端,并将读取到的master端的bin-log文件名和position点记录到master.info文件中,以便在下一次读取的时候能告知master从相应的bin-log文件名及最后一个position点开始发起请求
  • slave SQL线程检测到relay-log中内容有更新,会立刻解析relay-log的内容,将解析后的SQL语句在slave中执行,执行成功后主库和从库数据一致。

准备

  • 主节点IP:192.168.0.11
  • 从节点IP:192.168.0.10
  • 待同步的数据库:test

步骤

配置主节点

  1. vim /etc/my.cnf
  2. [mysqld]字段中添加:
log-bin=master-bin
binlog-format=ROW
server-id=1
  1. 重启mysqld:
systemctl restart mysqld
  1. 登入mysql,创建用于同步的账户tongbu,注意修改IP段和密码:
create user 'tongbu'@'192.168.%' identified by '123456';
  1. 授权,注意修改IP段和密码:
grant replication slave on *.* to 'tongbu'@'192.168.%';
  1. 查看master状态:
# 注意File字段和Position字段
show master status;

配置从节点

  1. vim /etc/my.cnf
  2. 在[mysqld]字段中添加:
log-bin=master-bin
binlog-format=ROW
server-id=2
  1. 重启mysql
  2. 登入mysql,配置从库连接主库,注意修改主库的IP,用于同步的账户名和密码,master_log_file为show master status;中的File值,master_log_posshow master status;中的Position
change master to master_host='192.168.0.11',master_user='tongbu',master_password='123456',master_log_file='master-bin.000001',master_log_pos=154;
  1. 启用从库:
start slave;
  1. 查看从库状态
# 注意看 Slave_IO_Running和Slave_SQL_Running是否为yes
show slave status\G

测试

在主库创建一个数据库:create database test123;

在数据库中新建一个表:create table t0(id int,name varchar(20));

查看从库有没有新建的表

只同步部分库或表

方式一:限制master端

在/etc/my.cnf中添加:binlog-do-db=test(多个数据库用逗号隔开,不加这一行默认同步所有库)

方式二:限制slave端

在/etc/my.cnf中添加:replicate-do-db=test

指定需要同步的表:replicate-do-table=test.test_table

解决主从复制问题

如果发生网络异常导致主从同步错误,或者在已有数据基础上搭建主从复制,需要解决数据不一致问题。

主从数据差别不大时的不同步问题

  1. master端执行:flush tables with read lock;,将数据库设置为全局读锁。
  2. slave端:
  1. 停止IO及SQL线程,stop slave;
  2. 将同步错误的SQL跳过一次,set global sql_slave_skip_counter=1;
  3. 启动slave,start slave;
  1. master端执行:unlock tables;解锁。

主从数据差别很大时的不同步问题

  1. master端执行:flush tables with read lock;,将数据库设置为全局读锁。
  2. 使用mysqldump、mysqlpump、xtrabackup等工具对master数据库进行完整备份
  3. 将完整数据导入到从库
  4. 重新配置主从关系
  5. master端执行:unlock tables;解锁。

主键冲突,错误代码1062

情况:从库上执行show slave status\G,报错last_error:1062,sql线程已停止工作。

原因:从库上执行了写操作,然后在主库上执行了相同的SQL语句,主键冲突,主从复制状态就会报错。

解决:

  1. 利用perconna-toolkit工具中的py-slave-restart命令在从库跳过错误(因为主从库有相同的数据)。

主库更新数据,从库找不到而报错,错误代码1032

在从库执行delete删除操作,再在主库执行更新操作,由于从库已经没有该数据,导致主从数据不一致。

解决方法:在从库执行show slave status,根据错误信息所知道的binlog文件和position号,在主库上通过mysqlbinlog命令查找在主库执行的哪条SQL语句导致的主从报错。把从库上丢失的这条数据补上,然后执行跳过错误。如果从库丢失了很多数据,需要考虑重新配置主从环境。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
21天前
|
存储 SQL 关系型数据库
MySQL体系结构与配置
MySQL体系结构与配置
35 0
|
14天前
|
弹性计算 关系型数据库 MySQL
centos7 mysql安装及配置
本文详细介绍了在阿里云服务器ECS上通过yum源安装MySQL 8.0.12的过程,包括更新yum源、下载并安装MySQL源、解决安装过程中可能遇到的问题等步骤。此外,还介绍了如何启动MySQL服务、设置开机自启、配置登录密码、添加远程登录用户以及处理远程连接异常等问题。适合初学者参考,帮助快速搭建MySQL环境。
92 8
centos7 mysql安装及配置
|
23小时前
|
关系型数据库 MySQL Java
MySQL主从复制实现读写分离
MySQL主从复制(二进制日志)、 Sharding-JDBC实现读写分离
MySQL主从复制实现读写分离
|
14天前
|
SQL 关系型数据库 MySQL
MySQL----配置双主双从
本文档详细介绍了如何在四台服务器上配置MySQL的双主双从架构。首先,通过关闭防火墙和SELinux确保网络通信畅通无阻。接着,设置各服务器的主机名和本地Host,确保名称解析正确。然后,通过YUM安装MySQL并修改初始密码。接下来,逐步配置四个节点(master01、master02、slave01、slave02),包括修改配置文件、创建用户和授权等步骤,实现主从复制。最后,通过SQL命令验证主从同步是否成功。
|
20天前
|
SQL 关系型数据库 MySQL
说一下MySQL主从复制的原理?
【8月更文挑战第24天】说一下MySQL主从复制的原理?
45 0
|
21天前
|
存储 关系型数据库 MySQL
实战!MySQL主从复制一键搭建脚本分享
实战!MySQL主从复制一键搭建脚本分享
27 2
|
22天前
|
关系型数据库 MySQL Linux
在Linux中,如何配置数据库服务器(如MySQL或PostgreSQL)?
在Linux中,如何配置数据库服务器(如MySQL或PostgreSQL)?
|
22天前
|
SQL 关系型数据库 MySQL
在Linux中,mysql 如何减少主从复制延迟?
在Linux中,mysql 如何减少主从复制延迟?
|
29天前
|
SQL 负载均衡 关系型数据库
*配置MySQL集群
【8月更文挑战第16天】*配置MySQL集群
27 2
|
1月前
|
SQL 关系型数据库 MySQL
Nacos 1.2.1 集群搭建(二)MySQL、cluster 配置
Nacos 1.2.1 集群搭建(二)MySQL、cluster 配置
40 1

热门文章

最新文章