MySQL 8 备份与恢复

Wesley13
• 阅读 807

备份应用的场景包括:系统崩溃、硬件故障、用户错误、升级MySQL Installation、传输MySQL Installation到另一台机器、设置复制等。

Slave Server备份

在备份Slave 数据库时,应该备份Master info 和 Relay log info repository,如果在备份时,Slave 正在复制 LOAD DATA语句应该备份slave_load_tmpdir 系统变量指定目下的SQL_LOAD*文件。

备份和恢复策略的例子

假设数据存储在InnoDB存储引擎上,支持事务和自动崩溃恢复。也假设在崩溃时,MySQL有负载。如果没有负载,可能恢复是不需要的。

一种情况:操作系统崩溃或电源故障,MySQL Server磁盘上的数据在数据库重启后可用。InnoDB上的数据文件可能由于崩溃而不一致。InnoDB读它的日志发现还未刷新到磁盘上的pending 事务或者 noncommited 事务。InnoDB自动回滚未提交的事务,刷新已提交的事务到数据文件中。

另一种情况:如果是文件系统崩溃或者硬件文件,假设MySQL磁盘数据在重启后不可用。这时就需要使用备份恢复。

建立备份策略

假设现在是周日下午一点,做包含所有InnoDB表的全库备份,比如:

shell> mysqldump --all-databases --master-data --single-transaction > backup_sunday_1_PM.sql

注:在MySQL 8.0 中默认情况下只有mysql schema下的两张日志表使用CSV存储引擎,其他表全部使用InnoDB存储引擎,在做备份时,mysqldump工具只会备份mysql.general_log、mysql_slow_log这两张日志的定义,而不会备份它们的数据。

这里使用--single-transaction,会在备份开始时,获取一个全局读锁(FLUSH TABLES WITH READ LOCK),在获取二进制日志的坐标位置后释放锁。如果在FLUSH语句执行时,有长时间运行的更新,备份操作可能要等它们完成。--single-transaction使用读一致性保证mysqldump看到的数据不会改变。这就需要没有其他语句破环读一致性,比如ALTER TABLE, DROP TABLE, RENAME TABLE,TRUNCATE TABLE等。

相对于连续的全备,一种有效的做法是:先做全备,然后做增量备份。增量备份更小,花时间也更短。但增量备份给恢复带来一些麻烦,比如:恢复是不能单纯的只应用一个全备,还需要应用增量备份。

在使用增量备份时,可以使用mysqldump工具提供的--flush-logs选项。这个选项会在备份时刷新二进制日志,这样mysqldump的备份中就包含刷新的二进制日志之前的所有数据。在做恢复时,在应用完全备后,可以方便的应用全备后生成的二进制日志。通过mysqldump工具的--maser-data可以知道全备之后的增量备份(二进制日志文件名)。

可以优化前面的备份脚本,比如:

shell> mysqldump --single-transaction --flush-logs --master-data=2  --all-databases > backup_sunday_1_PM.sql

假设现在是周一下午一点,做增量备份,比如:

shell> mysql'admin flush-logs

注:经二进制日志拷贝到安全位置。

假设现在是周二下午一点,做增量备份,比如:

shell> mysql'admin flush-logs

注:经二进制日志拷贝到安全位置。

补充:

为节约磁盘空间,在mysqldump全备后可以通过--delete-master-logs 选项删除二进制日志。但是在复制环境中,如果是Master Server,这样做可能对于没有完全接收二进制日志的Slave有影响。如果是在Slave Server端,可以考虑使用该选项。因为现在是在Slave端做的备份,可以修改之前的全备脚本如下:

shell> mysqldump --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > backup_sunday_1_PM.sql

注:mysqldump支持备份例程、事件、触发器,如果需要备份,可以添加--routines、--events 选项。

使用备份进制恢复

假设周三上午八点,发生了灾难性崩溃,需要使用备份进行恢复,首选进行全备恢复,比如:

shell> mysql < backup_sunday_1_PM.sql

现在数据已经恢复到周日下午一点,需要做增量恢复,比如:

shell> mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql

现在数据已经恢复到周二下午一点,但是到周三上午八点的数据还是没有恢复。所以MySQL建议:将日志文件放在一个安全的地方,比如RAID地盘 SAN 等,不同于存放数据目录的磁盘。

如果日志文件没有损坏,可以通过继续应用增量备份,将数据恢复到崩溃发生的时间点,比如:

shell> mysqlbinlog gbichot2-bin.000009 | mysql

备份策略总结

在操作系统崩溃或者电源故障的情况下,InnoDB可以自动恢复,但是为了能够睡的更好,可以完成下面的建议:

将日志文件存放在安全的位置,同时与数据目录在不同的磁盘设备上。这样做也可以做到磁盘的负载均衡,提升了性能。

定期做全量备份

定期做增量备份

注:这里做增量备份是每天做的,所有二进制日志文件大小应该要能够容纳至少一天的业务数据。

mysqldump 工具

A dump file can be used in several ways:
• As a backup to enable data recovery in case of data loss.
• As a source of data for setting up replication slaves.
• As a source of data for experimentation:
• To make a copy of a database that you can use without changing the original data.
• To test potential upgrade incompatibilities.

mysqldump工具根据是否添加 --tab 选项产生不同的DUMP文件:

没有 --tab 选项:产生一个SQL文件

添加 --tab 选项:产生两种类型文件:数据文件、数据库对象定义文件

导出SQL 格式的DUMP文件:

--databases 选项:将选项后面的参数作为数据库名称;如果没有这个选项,最第一个参数作为数据库名称,后面的参数作为表名称。

--databases选项、--all-databases选项 会在DUMP文件中添加CREATE DATABASE ... IF NOT EXISTS语句和USE DATABASE语句。

如果GTID开启,在导入时可能会报如下错误:

ERROR 3546 (HY000) at line 24: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED

解决方法:在导出时指定--set-gtid-purged=off 或者 comment。或者再导入前,删除DUMP文件中SET GTID_PURGED语句。

使用mysqldump工具导出Delimited-Text 格式的数据文件

在使用 mysqldump --tab=tab_dir选项时,可能会遇到下面的错误:

mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'

说明:--secure-file-priv 变量控制LOAD DATA类型的操作目录,有三个取值:' '、null、dir。

注:使用--tab 选项,最好是在本地。如果是远程使用这个选项:txt文件在远程主机(server),sql文件在本地主机(client)。

mysqldump --tab 一些格式化输出:

• --fields-terminated-by=str
The string for separating column values (default: tab).
• --fields-enclosed-by=char
The character within which to enclose column values (default: no character).
• --fields-optionally-enclosed-by=char
The character within which to enclose non-numeric column values (default: no character).
• --fields-escaped-by=char

The character for escaping special characters (default: no escaping).
• --lines-terminated-by=str
The line-termination string (default: newline).

比如:

mysqldump --tab=./ --fields-terminated-by=, --fields-optionally-enclosed-by='"' --lines-terminated-by=0x0d0a -uroot -poracle test

导入mysqldump --tab 选项导出的DUMP文件:

先导入SQL文件:

cat *.sql | mysql -uroot -poracle test

在导入TXT文件:

使用mysqlimport工具:

mysqlimport -uroot -poracle --fields-terminated-by=, --fields-optionally-enclosed-by='"' --lines-terminated-by=0x0d0a test /usr/local/mysql-8.0.18-linux-glibc2.12-x86_64/data/dump/*.txt

使用LOAD DATA语句:

mysql> USE db1;

mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1

FIELDS TERMINATED BY ','

FIELDS ENCLOSED BY '"'

LINES TERMINATED BY '\r\n';

导出存储程序:

--routines(存储过程和函数)

--events(Event Scheduler events)

--triggers(触发器默认会随着定义的表而导出)

跳过的话:--skip-routines --skip-events --skip-triggers

分离表定义和表数据:

mysqldump -uroot -poracle --no-data --databases test > test-no-data.dump

mysqldump -uroot -poracle --no-create-info --databases test > test-only-data.dump

使用mysqldump工具测试升级可能的不兼容性:

比如,先在升级后的数据库系统导入数据库定义:

mysqldump -uroot -poracle --all-databases --no-data --routines --events > dump-defs.sql

mysql -uroot -poracle < dump-defs.sql

如果测试升级后的系统没有问题,可以再导入数据:

mysqldump -uroot -poracle --all-databases --no-create-info > dump-data.sql

mysql -uroot -poracle < dump-defs.sql

时间点恢复(增量恢复)

假设人为误删除了一张大表,现在做恢复:

如果之前做了全备,比如通过如下语句:

mysqldump -uroot -poracle --all-databases --master-data=2 --single-transaction --routines --events > all.dump

现在先做全备的恢复,比如:

mysql -uroot -poracle < all.dump

可能会报下面的错误:

ERROR 3546 (HY000) at line 26: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED

出现这个错误的原因:DUMP文件中设置的GTID_PURGED变量值与现在系统中GTID_EXECUTED变量值有重叠。

如果MySQL Server开启了GTID模式,备份还原到源系统,可能会产生这个错误。

解决错误的一些做法:

将全备中SET @@GLOBAL.GTID_PURGED语句删除。

或者在备份时,添加--set-gtid-purged=off 或者 comment,比如:

mysqldump -uroot -poracle --all-databases --master-data=2 --set-gtid-purged=comment --single-transaction --routines --events > all.dump

注:之前MySQL版本没有--set-gtid-purged=comment,这个参数在不需要在DUMP中设置GTID,同时又希望知道DUMP文件包含的事务信息时可以使用这个选项。

在完成全备恢复后,可以使用mysqlbinlog工具应用二进制日志做增量恢复:

mysqlbinlog --skip-gtids --exclude-gtids='bb65156d-500b-11ea-8a8f-080027b78051:63-65' /usr/local/mysql/log/log-bin.000018 | mysql -uroot -poracle

注:二进制日志中在每个事务前有一个与之相关的GTID,如果二进制日志不做处理,会与系统现在的GTID冲突,这样系统会不再执行相同GTID的事务。

在MySQL文档中给出了两种做增量恢复方法:

通过--stop-datetime 、--start-datetime选项做基于时间的恢复,这种恢复对于同一时间有大量事务的系统,对于日志的截取比较困难。

另一种是通过--stop-position、--start-position 选项基于events位置做的,与上一种方法一样,两段截取。

对于现在的系统如果开启的GTID事务模式,使用GTID截取事务是非常方便的。

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Stella981 Stella981
3年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Easter79 Easter79
3年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Stella981 Stella981
3年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
Python进阶者 Python进阶者
9个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这