MySQL数据备份

mysql ginotang 693℃ 0评论

MySQL的两种备份策略

两种备份策略指的是逻辑备份:分别是全量备份和增量备份。除了逻辑备份外,我们还可以选择通过复制原始数据来备份数据,这种方式也称为物理备份。关于逻辑备份和物理备份,它们有如下的特点:

物理备份的特点

  1. 因为是直接复制文件系统上面的数据,因此备份文件和数据文件完全一致

  2. 物理备份比逻辑备份速度要快

  3. 数据还原后不保证table层面的一致性(不同存储引擎行为不一样)

  4. 除了数据外,物理备份也同时备份了一些其他文件,例如日志文件

  5. 物理备份最好在服务器停止的情况下备份,确保数据一致性,否则需要在锁表的前提下进行备份

  6. 无法备份内存表,因为内存表没有写入硬盘

  7. 物理备份可移植性较低,例如在Linux下备份的文件还原到windows下是就可能会有问题

逻辑备份的特点

  1. 逻辑备份只能在服务器online的情况下备份,因为需要运行sql语句
  2. 逻辑备份速度比物理备份慢
  3. 不管数据库是什么引擎,保证数据在database层面和table层面的一致性
  4. 只能备份数据,不能备份配置文件或者日志文件
  5. 备份文件可能会比物理备份的大
  6. 逻辑备份可以在不锁表的情况下进行备份(针对innoDB引擎)
  7. 逻辑备份可移植性比物理备份高

逻辑备份的两种方式

可以对数据进行全量备份和增量备份,它们各有长短,最好的方式是两者结合使用,以确保数据的安全性和一致性。

全量备份的好处是数据完整,还原方便,但坏处是备份时间长;而增量备份的好处是备份时间短,缺点是数据不完整,还原比全量备份麻烦。在实际情况中,我们应该定期为数据库做全量备份,然后分时段做增量备份

使用mysqldump程序备份数据

一般来说,我们都是使用mysqldump来进行数据备份;除非你使用的是企业版的数据库,那么,你有更好的选择(mysqlbackup)。

mysqldump虽然使用简单,但是还是有很多地方需要注意的,尤其有些参数可能会造成理解上的错误。

  • 基本备份命令

    例如要备份名称为wordpress的数据库,可以使用下面的命令

    mysqldump数据默认输出到标准输出,因此要把输出重定向到指定文件中。执行完命令后,wordpress数据库的内容就会备份到wordpress.sql文件中。

  • 备份某个指定表

    mysqldump还可以单独备份某个指定的表,例如要备份wordpress下的article表

    除了用户名和密码外,mysqldump会把第一个给出的参数作为数据库,其他的则作为表来备份,因此,上面语句的意思是:备份wordpress数据库中的article表到article.sql文件。如果需要备份多个表(article、user、comment),只需要把表的名称添加上即可,例如:

  • 备份多个数据库

    通过--databases选项(短形式为-B),可以同时备份多个数据库,例如备份wordpress数据库和mysql数据库

    --databases后面跟着的参数都被当做数据库来处理,除此之外,这个选项还会在备份文件中添加上create databaseuse database命令。因此,在还原数据库的时候就不需要手动处理这些工作。

  • 备份所有数据库

    使用的选项是--all-databases(短形式为-A)

    这个选项和--databases一样,也会在备份文件中添加create databaseuse database命令。

  • 备份innoDB数据库

    innoDB数据库的特点是支持事务,通过--single-transaction选项,可以在不锁表的情况下备份innoDB数据库,确保数据一致性。另外需要注意的是,在备份过程中,任何ALTER TABLE、CREATE TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLE命令都不允许被运行。--single-transaction选项会通过start transaction新建一个事务,然后把数据库隔离级别设置为REPEATABLE READ

  • 备份myisam数据库
    --single-transaction选项不支持myisam数据库,因此,如果要确保myisam数据库备份时候的一致性,需要对数据库进行锁表操作。锁表选项是--lock-tables

  • 备份数据库以建立slave服务器

    建立主从服务器的一个关键点是binary log,因此,如果希望通过备份数据库来建立主从关系,那么如何处理binary log是关键。

    通过--flush-logs(-F)选项,可以在备份的时候同时刷新bniary log。另外,--flush-logs选项通常会结合-lock-all-tables--master-data--single-transaction选项使用,针对不同的数据库引擎,使用不同的选项。

    --master-data选项的作用是在备份文件中添加change master to命令,当该选项的值为2,则change master to命令被注释,1则没有注释。

    备份好后,需要到从服务器中还原all.sql,以及flush-logs选项生成的binary log文件。以确保数据的一致性。

通过mysqladmin增量备份数据

增量备份数据需要mysql开启binary log模式,增量数据都被储存到binary log中。做增量备份之前请确保已经做好全量备份。具体可以通过上面介绍的方法。

通过mysqladmin生成增量备份其实非常简单,通过flush-logs命令就可以创建增量备份,假设在全量备份之前,mysql数据目录内容如下:

一共有5个binary log文件,那么,在使用下面的备份命令之后,结果会怎样

然后再ls一下该目录

发现多了一个日志mysql-bin.000006,这个日志其实就是第一个增量备份,mysqladmin的flush-logs命令和mysqldump的差不多,也就是说,如果你希望在特定时候创建增量备份,就可以使用下面的命令:

结果是目录下面增加了mysql-bin.000007日志,这也是最新的增量备份数据。

通过mysqlbinlog还原增量备份

假如前面的完全备份(all.sql)是在mysql-bin.000006之前创建的,如果有一天数据意外丢失,那么你可以先把完全备份还原,然后再分别还原两个增量备份,分别是mysql-bin.000006和mysql-bin.000007。具体步骤如下:

  1. 还原完全备份

  2. 还原增量备份

    先查看一下增量备份有哪些,6和7就是我们还原的目标

    mysqlbinlog有一个选项比较重要,它就是--disable-log-bin,它可以防止还原过程产生额外的日志记录,从而避免了数据循环写入。

    接下来使用mysqlbinlog进行还原,当需要还原被意外删除的数据之前,需要先确保日志中的drop语句被移除,可以先把日志文件中的内容导出到普通的文件当中,然后把相关的drop语句删除,这样才能保证数据可以正确还原。

  3. 其他选项

    mysqlbinlog有些选项在还原数据的时候非常有用,分别是

  • –start-position和–stop-position
  • –start-datetime和–stop-datetime

    第一对选项用于指定开始还原的位置和结束还原的位置。如果只指定start-position,则数据从start-position一直还原到日志文件的末尾,相反如果只指定stop-position,那么数据从日志开头一直还原到stop-position位置。

    第二对选项则用来指定还原的起始时间,其作用和start-position类似。

  1. 使用mysqlbinlog查看日志内容

    由于binary log日志是二进制形式的,我们无法像普通文件一样查看它里面的内容,不过MySQL提供了mysqlbinlog工具来操作binary log。

    默认情况下,日志会输出到标准输出,且数据以base64方式加密,如果希望查看具体内容,可以使用--base64-output-v选项解密。–base64-output选项可以抑制加密信息的输出,而-v选项可重构日志中的sql语句

    解密后的内容部分显示如下,已经可以看到具体的sql语句操作:

    当然,通过-v选项输出的文件不能用来进行数据的还原,因为该选项会把任何insert、update语句都注释掉。

转载请注明:Pure nonsense » MySQL数据备份

喜欢 (0)
0 0 投票数
文章评分
订阅评论
提醒
guest
0 评论
内联反馈
查看所有评论
0
希望看到您的想法,请您发表评论x
()
x