热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

MySQL数据备份与同步

前段时间使用MySQL作为数据存储做了一个小项目。项目上线运行了几十天之后,数据已经越来越多,达到了100多M。用mysqldump每天备份全量数据然后

前段时间使用MySQL作为数据存储做了一个小项目。项目上线运行了几十天之后,数据已经越来越多,达到了100多M。用mysqldump每天备份全量数据然后传输到另外一台机器上这种方式进行数据备份,久而久之越来越慢。于是开始研究如何利用mysql的主从同步功能实现自动备份。如果实现自动备份,主从服务器之间只需要在有数据更新时同步一点增量数据,不会在备份时占用大量的CPU和内网的网络带宽资源了。介绍主从同步之前,还是先从基础的mysqldump备份开始讲起。

mysqldump

mysqldump是mysql数据库提供的一个数据备份工具。顾名思义,mysqldump可以把mysql数据库导出成sql语句文件,并保存到磁盘上。mysqldump命令产生的.sql文件包含一系列SQL INSERT语句,可以用来进行数据恢复。

假定我们在星期日下午1点进行了备份,此时负荷较低。下面的命令可以完全备份所有数据库中的所有表:

1shell> mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql

使用mysqldump进行数据备份,至少有两个问题:

1 mysqldump运行时,需要消耗一定的计算资源。而且数据库越大,消耗的计算资源也就越多,因此可能会造成系统在备份时运行效率低,容易造成用户卡死。

2 对mysqldump备份的数据进行恢复,会丢掉从备份点开始的更新数据。

为了解决第2点的问题, mysql文档中给出了一个解决办法。那就是利用mysqlbinlog二进制文件保存增量的数据。采用全量mysqldump+增量mysqlbinlog的方式进行数据恢复。

下面介绍mysqlbinlog

mysqlbinlog

 

mysqlbinlog就是mysql的二进制数据文件。在对mysql进行一些配置之后,mysql会把数据库的更新操作都记录在一个文件中。mysqlbinlog可以在mysqld的--bin-log选项或者在配置文件(my.cnf或者my.ini)中打开。

[mysqld]

log-bin=mysql-bin   //[必须]启用二进制日志

 

在启用了二进制日志以后,在mysql的数据目录下,会出现一些以数字为结尾的文件,例如:

-rw-rw---- 1 guilhem  guilhem   1277324 Nov 10 23:59 mysql-bin.000001

-rw-rw---- 1 guilhem  guilhem         4 Nov 10 23:59 mysql-bin.000002

 

 

这些文件就是二进制的日志文件。每次mysql启动都会增加一个文件。

 

下面回到上节提出的问题,如何采用全量mysqldump+增量mysqlbinlog的方式进行数据恢复?

方法其实很简单,在每次使用mysqldump进行全量数据备份时,用--flush-logs选项:

 

mysqldump --single-transaction --flush-logs --master-data=2 > backup.sql

 

在使用这样的语句进行备份之后,mysql就会关闭原来的二进制日志文件,开启一个新的二进制日志文件。比如,新开启的二进制日志文件为 mysql-bin.000003。 那么在进行数据恢复的时候,你可以利用backup.sql进行全量恢复+ mysql-bin.000003进行增量同步。

数据恢复的方法也很简单。

 

1cat backup.sql | mysql -uroot -ppassword
2mysqlbinlog mysql-bin.000003 | mysql -uroot -ppassword

mysqlbinlog是一个读取 mysql二进制日志输出sql语句的命令行工具。使用方法可以从http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/client-side-scripts.html#mysqlbinlog 查到。

还记得上文提出的mysqldump备份的两个问题吗,现在第二个问题解决了,第一个问题还没有解决

“ mysqldump运行时,需要消耗一定的计算资源。而且数据库越大,消耗的计算资源也就越多,因此可能会造成系统在备份时运行效率低,容易造成用户卡死。”

下文中我们利用mysql主从同步来解决这个问题。

主从同步

主从同步的含义非常简单。通过一定的设置,让两台或者多台mysql服务器的数据保持一致。设置的方法网上已经有很多方法了,推荐这篇帖子http://369369.blog.51cto.com/319630/790921

设置成主从同步之后,基本上就免去了每天全量备份之苦。而且一但主数据库出问题,可以马上切换到从数据库进行服务,大大减少了故障恢复的时间。

 

我讲一讲我在配置中遇到的2个问题:

 

1 在从服务器上 show slave status时,显示 Slave_SQL_Running: No. 错误的原因是 mysql 数据库的db表已经存在,不能再建立。

 

错误的原因是这样的, 我在每台数据上都运行了 mysql_install_db这个命令安装了 mysql test info_schema这3个数据库。当我主从同步开始时,主数据库要向从数据库同步建立mysql数据库的操作。而从数据库已经建立了mysql数据库。

 

我解决的方法是在配置文件里指明写二进制文件的数据库名称。只有真正需要同步的业务数据库才写二进制文件。

主数据库:

[mysqld]

binlog-do-db=exampledb

 

从数据库:

[mysqld]

replicate-do-db=exampledb

 

2 我的主数据库已经运行有一段时间了。在从服务器设置master_log_pos的时候设置成主服务器的当前日志位置。结果同步时也出现了Slave_SQL_Running: No. 错误的原因是: 执行Insert语句时数据表没有建立。

 

错误的原因也很简单,我在从数据库里面还没有建立对应的数据库,而同步的操作为插入数据。

 

解决的方法是通过mysqldump对主数据库进行一次全量数据备份,并且在从数据库中恢复这个备份之后才开始进行主从同步。


转载于:https://www.cnblogs.com/martinjinyu/articles/3750422.html


推荐阅读
  • 本文详细介绍了如何在Linux系统上安装和配置Smokeping,以实现对网络链路质量的实时监控。通过详细的步骤和必要的依赖包安装,确保用户能够顺利完成部署并优化其网络性能监控。 ... [详细]
  • 本文深入探讨了Linux系统中网卡绑定(bonding)的七种工作模式。网卡绑定技术通过将多个物理网卡组合成一个逻辑网卡,实现网络冗余、带宽聚合和负载均衡,在生产环境中广泛应用。文章详细介绍了每种模式的特点、适用场景及配置方法。 ... [详细]
  • 使用Vultr云服务器和Namesilo域名搭建个人网站
    本文详细介绍了如何通过Vultr云服务器和Namesilo域名搭建一个功能齐全的个人网站,包括购买、配置服务器以及绑定域名的具体步骤。文章还提供了详细的命令行操作指南,帮助读者顺利完成建站过程。 ... [详细]
  • Windows服务与数据库交互问题解析
    本文探讨了在Windows 10(64位)环境下开发的Windows服务,旨在定期向本地MS SQL Server (v.11)插入记录。尽管服务已成功安装并运行,但记录并未正确插入。我们将详细分析可能的原因及解决方案。 ... [详细]
  • 本文将介绍如何编写一些有趣的VBScript脚本,这些脚本可以在朋友之间进行无害的恶作剧。通过简单的代码示例,帮助您了解VBScript的基本语法和功能。 ... [详细]
  • PyCharm下载与安装指南
    本文详细介绍如何从官方渠道下载并安装PyCharm集成开发环境(IDE),涵盖Windows、macOS和Linux系统,同时提供详细的安装步骤及配置建议。 ... [详细]
  • 本文详细介绍如何使用Python进行配置文件的读写操作,涵盖常见的配置文件格式(如INI、JSON、TOML和YAML),并提供具体的代码示例。 ... [详细]
  • This guide provides a comprehensive step-by-step approach to successfully installing the MongoDB PHP driver on XAMPP for macOS, ensuring a smooth and efficient setup process. ... [详细]
  • 深入理解 SQL 视图、存储过程与事务
    本文详细介绍了SQL中的视图、存储过程和事务的概念及应用。视图为用户提供了一种灵活的数据查询方式,存储过程则封装了复杂的SQL逻辑,而事务确保了数据库操作的完整性和一致性。 ... [详细]
  • 本文介绍了一款用于自动化部署 Linux 服务的 Bash 脚本。该脚本不仅涵盖了基本的文件复制和目录创建,还处理了系统服务的配置和启动,确保在多种 Linux 发行版上都能顺利运行。 ... [详细]
  • 使用 Azure Service Principal 和 Microsoft Graph API 获取 AAD 用户列表
    本文介绍了一段通用代码示例,该代码不仅能够操作 Azure Active Directory (AAD),还可以通过 Azure Service Principal 的授权访问和管理 Azure 订阅资源。Azure 的架构可以分为两个层级:AAD 和 Subscription。 ... [详细]
  • Explore a common issue encountered when implementing an OAuth 1.0a API, specifically the inability to encode null objects and how to resolve it. ... [详细]
  • 在当前众多持久层框架中,MyBatis(前身为iBatis)凭借其轻量级、易用性和对SQL的直接支持,成为许多开发者的首选。本文将详细探讨MyBatis的核心概念、设计理念及其优势。 ... [详细]
  • 在使用 DataGridView 时,如果在当前单元格中输入内容但光标未移开,点击保存按钮后,输入的内容可能无法保存。只有当光标离开单元格后,才能成功保存数据。本文将探讨如何通过调用 DataGridView 的内置方法解决此问题。 ... [详细]
  • 本文介绍了如何通过 Maven 依赖引入 SQLiteJDBC 和 HikariCP 包,从而在 Java 应用中高效地连接和操作 SQLite 数据库。文章提供了详细的代码示例,并解释了每个步骤的实现细节。 ... [详细]
author-avatar
L-Darin_209
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有