备份应用的场景包括:系统崩溃、硬件故障、用户错误、升级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截取事务是非常方便的。