MySQL主从复制简单介绍、重建、中断处理-----给开发、测试人员的培训

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

前几天给开发做了MySQL主从复制相关的培训,主要是让他们了解一下相关的情况,对中断进行简单的处理。

本文主要对MySQL主从复制进行简单的介绍,包括原理简介、搭建步骤、状态检查、同步中断及排查、备库重建。

欢迎转载,请注明作者、出处。

作者:张正
blog:http://space.itpub.net/26355921 
QQ:176036317
如有疑问,欢迎联系。



MySQL主从复制

MySQL主从复制、搭建、状态检查、中断排查及备库重做

2014/8/27

 


本文档主要对MySQL主从复制进行简单的介绍,包括原理简介、搭建步骤、状态检查、同步中断及排查、备库重建。

 


 TOC \o "1-3" \h \z \u 一、MySQL主从复制概述... 

1、主从复制简介... 

2、主从复制原理、机制... 

3、主从复制原理图... 

二、MySQL主从复制搭建... 

1Master端配置部署... 

2Slave端配置部署... 

3、建立主从同步... 

三、主从复制状态检查及异常处理... 

1、主从复制状态检查... 

2IO_thread异常... 

3sql_thread异常... 

4、主从复制延迟... 

一、MySQL主从复制概述

1、主从复制简介

MySQL主从复制就是将一个MySQL实例(Master)中的数据实时复制到另一个MySQL实例(slave)中,而且这个复制是一个异步复制的过程。

实现整个复制操作主要由三个进程完成的,其中两个进程在Slavesql_threadIO_thread),另外一个进程在 MasterIO进程)上。

2、主从复制原理、机制

要实施复制,首先必须打开Master端的binary logbin-log)功能,否则无法实现。因为整个复制过程实际上就是SlaveMaster端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。

复制的基本过程如下:

1)、Slave上面的IO_thread连接上Master,并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容;

2)、Master接收到来自SlaveIO_thread的请求后,通过负责复制的IO进程根据请求信息读取制定日志指定位置之后的日志信息,返回给Slave IO_thread。返回信息中除了日志所包含的信息之外,还包括本次返回的信息已经到Master端的bin-log file的以及bin-log pos

3)、SlaveIO_thread接收到信息后,将接收到的日志内容依次添加到Slave端的relay-log文件的最末端,并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中,以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log的哪 个位置开始往后的日志内容,请发给我”;

4)、SlaveSql_thread检测到relay-log中新增加了内容后,会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容,并在本数据库中执行。

 3、主从复制原理图


 


 

 

二、MySQL主从复制搭建

MySQL主从复制搭建主要步骤有:Master端配置部署、Slave端配置部署、建立主从同步

 

1、Master端配置部署

         a、 配置参数:

[mysqld]

server-id=101  # 这个要保证一个主从复制环境中,不要有相同的server-id

log-bin=/data/mysql6001/binlog/mysql-bin.log

log-bin-index=/data/mysql6001/binlog/mysql-bin.index

expire_logs_days=30

         b、 创建用户,并赋予权限:

         GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY PASSWORD '******';

       

2、Slave端配置部署

         a、 配置参数:

[mysqld]

server-id=102 

relay-log=/data/mysql6001/relaylog/mysql-relay-bin.log

relay-log-index=/data/mysql6001/relaylog/mysql-relay-bin.index

relay_log_purge=on

 

3、建立主从同步

(重建备库也是使用该方法)

 

         建立主从同步可以从主库上导出数据,也可以从已有的从库上导出数据,然后再导入到新的从库中,change master to建立同步。

3.1 、导出数据

在主库上导出数据:

mysqldump -u*** -p*** -S /data/mysql6001/mysql.sock --default-character-set=utf8 -q --single-transaction --master-data -A  >  /tmp/all_database.sql

(或者)在从库上导出数据:

mysqldump -u*** -p*** -S /data/mysql6001/mysql.sock --default-character-set=utf8 -q --single-transaction --dump-slave -A  >  /tmp/all_database.sql

NOTES

--master-data--dump-slave导出的备份中,会包含master_log_filemaster_log_pos信息。

3.2 、从库导入数据

mysql -u*** -p*** --default-character-set=utf8 < all_database.sql

NOTES

此处导入脚本,就已经在从库中执行了以下操作:

change_master_to

master_log_file=' mysql-bin.000xxx',

master_log_pos=xxxxxx;

3.3 、从库与主机建立同步

以下为建立主从同步最基本的6个项:

change master to

master_host='xxx.xxx.xxx.xxx',     # 主库IP

master_port=6001,              # 主库mysqld的端口

master_user='repl',              # 主库中创建的有REPLICATION SLAVE 权限的用户

master_password='xxxxxxxx',      # 该用户的密码

master_log_file=' mysql-bin.000xxx', # 已在导入时指定了

master_log_pos=xxxxxx;          #已在导入时指定了

指定与主库同步的基本信息后,就可以启动slave进程了:(IO_threadsql_thread

start slave;

三、主从复制状态检查及异常处理

1、主从复制状态检查

主库查看binlog情况:

show master status\G

*************************** 1. row ***************************

            File: mysql-bin.000303

        Position: 18711563

    Binlog_Do_DB:

Binlog_Ignore_DB:

 

在从库上主要是使用以下命令查看从库与主库的同步状态:

show slave status\G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.43.128    #主库IP

                  Master_User: repl             #主库复制的用户

                  Master_Port: 6001             #主库mysqld端口

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000303   #io_thread读取主库master_log_file

          Read_Master_Log_Pos: 18711563        # io_thread读取主库master_log_pos

               Relay_Log_File: mysql-relay-bin.000900

                Relay_Log_Pos: 18711709

        Relay_Master_Log_File: mysql-bin.000303  #sql_thread执行主库的master_log_file

             Slave_IO_Running: Yes             #关键的,io_thread是否running

            Slave_SQL_Running: Yes             #关键的,sql_thread是否running

              Replicate_Do_DB:

          Replicate_Ignore_DB:

           Replicate_Do_Table:

       Replicate_Ignore_Table:

      Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table:

                   Last_Errno: 0

                   Last_Error:

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 18711563       #sql_thread执行主库的master_log_pos

              Relay_Log_Space: 18711908

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File:

           Master_SSL_CA_Path:

              Master_SSL_Cert:

            Master_SSL_Cipher:

               Master_SSL_Key:

        Seconds_Behind_Master: 0               #从库的延迟

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 101

1 row in set (0.00 sec)

 

2、IO_thread异常

         IO_thread异常,状态往往是Slave_IO_Running: Connecting NO

         IO_thread是向Master发送请求读取master binlog,如果处于Connecting状态,说明无法正确地与Master进行连接,可能的原因有:

         a网络不通(是否打开防火墙)

         b复制用户的密码不对

         c指定的master_port端口不对

         dmaster上的mysql-bin.xxxxxx被误删

         e主库磁盘空间满了

         通过show slave status\G可以看到相关错误信息,例如:

show slave status\G

Last_IO_Errno: 2003

Last_IO_Error: error connecting to master 'repl@192.168.43.128:3306' - retry-time: 60      

            retries: 86400

或者通过错误日志看到相关信息,如:

140828 15:47:20 [ERROR] Slave I/O: error connecting to master 'repl@192.168.43.128:3306' -  

              retry-time: 60  retries: 86400, Error_code: 2003

140828 15:47:21 [Note] Event Scheduler: Loaded 0 events

140828 15:47:21 [Note] /home/mysql/mysql/bin/mysqld: ready for connections.

3、sql_thread异常

         sql_thread发生异常,状态就会变为Slave_SQL_Running: NO

         sql_thread发生异常的情况非常多,发生异常后,需要通过以下方法排查和解决:

         a、对比主库和从库的二进制日志的情况:

主库:

show master status\G

            File: mysql-bin.000303

        Position: 18711563

从库:

show slave status\G

               Master_Log_File: mysql-bin.000303   --IO_thread

          Read_Master_Log_Pos: 18711563          --IO_thread

          Relay_Master_Log_File: mysql-bin.000303   --sql_thread

           Exec_Master_Log_Pos: 18711163          --sql_thread

 

         b、通过show slave status\G查看错误信息:

show slave status\G

Last_SQL_Errno: 1062

Last_SQL_Error: Error 'Duplicate entry '1' for key 'PRIMARY'' on query. Default database:

             'test'. Query: 'insert into test values(1,2,3,4,5,6)'

 

         c、 通过错误日志查看错误信息:

140828 16:27:51 [ERROR] Slave SQL: Error 'Duplicate entry '1' for key 'PRIMARY'' on query.

              Default database: 'test'. Query: 'insert into test values(1,2,3,4,5,6)',

              Error_code: 1062

140828 16:27:51 [Warning] Slave: Duplicate entry '1' for key 'PRIMARY' Error_code: 1062

140828 16:27:51 [ERROR] Error running query, slave SQL thread aborted. Fix the problem,

              and restart the slave SQL thread with "SLAVE START". We stopped at log

              'mysql-bin.000303' position 18711163

 

         根据这些报错信息,往往就能够定位到发生异常的原因。如果我们了解产生异常的具体事件,而且能够掌控,可以通过设置sql_slave_skip_counter参数来跳过当前错误。

set global sql_slave_skip_counter=1;

         或者使用slave_skip_errors参数(read only variable),指定跳过某种类型的错误:

参数文件中设置:

slave_skip_errors=1062     #跳过1062错误

         遇到错误时,不要一通百度后,然后根据看起来很类似的操作直接来进行操作。因为网上大部分解决sql_thread异常的方法是:

         a直接set global sql_slave_skip_counter=n; n设置很大的值,即:跳过所有错误),

         b设置slave_skip_errors=all;  跳过所有类型的错误

         c直接查看主库的binlog,然后在从库上直接执行change master to

         这些方法都会导致主从数据不一致。

 

         如果发现从库与主库差异太大,无法通过手动操作数据修改重新建立同步。可以参考上述"MySQL主从复制搭建" 重新搭建从库。

4、主从复制延迟

主从复制延迟,可能的原因有:

a主从同步延迟与系统时间的关系,查看主从两台机器间系统时间差

b主从同步延迟与压力、网络、机器性能的关系,查看从库的iocpumem及网络            

c主从同步延迟与lock锁的关系(myisam表读时会堵塞写),尽量避免使用myisam      表。         一个实例里面尽量减少数据库的数量。

d、主从复制发生异常而中断,过很久之后才发现复制异常。可通过查看masterslavestatus估算相差的日志。如果相差太大,则可以考虑重做从库。

最后附上本文的完整文档,需要忽悠开发或测试人员的,赶紧get了:
MySQL主从复制.docx
本文转自ITPUB博客84223932的博客,原文链接:MySQL主从复制简单介绍、重建、中断处理-----给开发、测试人员的培训,如需转载请自行联系原博主。
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
相关文章
mysql主从复制概述和配置
【10月更文挑战第22天】MySQL 主从复制是一种将主服务器的数据复制到一个或多个从服务器的技术,实现读写分离,提高系统性能和可用性。主服务器记录变更日志,从服务器通过 I/O 和 SQL 线程读取并应用这些变更。适用于读写分离、数据备份和恢复、数据分析等场景。配置步骤包括修改配置文件、创建复制用户、配置从服务器连接主服务器并启动复制进程。
185 1
【02】写一个注册页面以及配置打包选项打包安卓apk测试—开发完整的社交APP-前端客户端开发+数据联调|以优雅草商业项目为例做开发-flutter开发-全流程-商业应用级实战开发-优雅草央千澈
【02】写一个注册页面以及配置打包选项打包安卓apk测试—开发完整的社交APP-前端客户端开发+数据联调|以优雅草商业项目为例做开发-flutter开发-全流程-商业应用级实战开发-优雅草央千澈
【02】写一个注册页面以及配置打包选项打包安卓apk测试—开发完整的社交APP-前端客户端开发+数据联调|以优雅草商业项目为例做开发-flutter开发-全流程-商业应用级实战开发-优雅草央千澈
10个必备Python调试技巧:从pdb到单元测试的开发效率提升指南
在Python开发中,调试是提升效率的关键技能。本文总结了10个实用的调试方法,涵盖内置调试器pdb、breakpoint()函数、断言机制、logging模块、列表推导式优化、IPython调试、警告机制、IDE调试工具、inspect模块和单元测试框架的应用。通过这些技巧,开发者可以更高效地定位和解决问题,提高代码质量。
202 8
10个必备Python调试技巧:从pdb到单元测试的开发效率提升指南
《docker高级篇(大厂进阶):1.Docker复杂安装详说》包括:安装mysql主从复制、安装redis集群
《docker高级篇(大厂进阶):1.Docker复杂安装详说》包括:安装mysql主从复制、安装redis集群
89 14
docker高级篇(大厂进阶):安装mysql主从复制
docker高级篇(大厂进阶):安装mysql主从复制
117 24
java版药品不良反应智能监测系统源码,采用SpringBoot、Vue、MySQL技术开发
基于B/S架构,采用Java、SpringBoot、Vue、MySQL等技术自主研发的ADR智能监测系统,适用于三甲医院,支持二次开发。该系统能自动监测全院患者药物不良反应,通过移动端和PC端实时反馈,提升用药安全。系统涵盖规则管理、监测报告、系统管理三大模块,确保精准、高效地处理ADR事件。
C语言因高效灵活被广泛应用于软件开发。本文探讨了优化C语言程序性能的策略,涵盖算法优化、代码结构优化、内存管理优化、编译器优化、数据结构优化、并行计算优化及性能测试与分析七个方面
C语言因高效灵活被广泛应用于软件开发。本文探讨了优化C语言程序性能的策略,涵盖算法优化、代码结构优化、内存管理优化、编译器优化、数据结构优化、并行计算优化及性能测试与分析七个方面,旨在通过综合策略提升程序性能,满足实际需求。
83 1
用C语言开发游戏的实践过程,包括选择游戏类型、设计游戏框架、实现图形界面、游戏逻辑、调整游戏难度、添加音效音乐、性能优化、测试调试等内容
本文探讨了用C语言开发游戏的实践过程,包括选择游戏类型、设计游戏框架、实现图形界面、游戏逻辑、调整游戏难度、添加音效音乐、性能优化、测试调试等内容,旨在为开发者提供全面的指导和灵感。
66 2
MySQL主从复制原理和使用
本文介绍了MySQL主从复制的基本概念、原理及其实现方法,详细讲解了一主两从的架构设计,以及三种常见的复制模式(全同步、异步、半同步)的特点与适用场景。此外,文章还提供了Spring Boot环境下配置主从复制的具体代码示例,包括数据源配置、上下文切换、路由实现及切面编程等内容,帮助读者理解如何在实际项目中实现数据库的读写分离。
209 1
MySQL主从复制原理和使用
【技术开发】接口管理平台要用什么技术栈?推荐:Java+Vue3+Docker+MySQL
该文档介绍了基于Java后端和Vue3前端构建的管理系统的技术栈及功能模块,涵盖管理后台的访问、登录、首页概览、API接口管理、接口权限设置、接口监控、计费管理、账号管理、应用管理、数据库配置、站点配置及管理员个人设置等内容,并提供了访问地址及操作指南。

热门文章

最新文章