MySQL备份的几种常用
发布网友
发布时间:2022-04-21 15:26
我来回答
共2个回答
热心网友
时间:2022-04-07 18:11
数据备份是数据容灾的最后一道防线,即便有着两地三中心的架构,备份也依然重要。如果备份出问题,备份时影响了交易业务,备份数据无法恢复,这些也是企业难以承受的。所以选择合适的备份工具尤为重要。
每个企业级数据库都会有配套的备份工具,MEB(MySQL Enterprise Backup)就是MySQL企业版中非常重要的工具之一,是为企业级客户提供的数据备份方案。
Xtrabackup一直作为MEB 开源版备胎而存在,从MySQL 8.0开始情况可能会变得有所不同。
在 MySQL 8.0的Backup Lock、Redo Log Archiving、Page Tracking等新特性的加持下,MEB备份/恢复体验会更好,目前xtrabackup还不支持这些特性。
MySQL 企业版还有哪些功能?
特性1:Backup Lock
8.0之前使用xtrabackup或MEB做物理备份,为了保证备份时InnoDB引擎表与其他引擎数据文件、及binlog日志的一致性会上全局读锁,再拷贝非InnoDB文件,这期间MySQL会变成只读,数据无法写入。表数量越多,可能加上时间越长,如果使用的xtrabackup 不小心没加rsync参数,逐个拷贝frm文件,锁定时间会更长,对业务影响较大。
我曾遇到过部署在虚拟机的实例有12000多张表,当时使用的xtrabackup,备份脚本中没加rsync参数,结果锁了十几分钟,而MEB就没有这样的问题。
MySQL 8.0支持轻量级备份锁 LOCK INSTANCE FOR BACKUP,数据字典也重构了由InnoDB存储。若不创建非InnoDB表,MEB默认使用备份锁获取binlog日志一致性位置,并阻止DDL操作,但不影响DML操作。
只有InnoDB表,仅上备份锁
请点击输入图片描述
若有非InnoDB表,上全局锁
请点击输入图片描述
特性2:Redo Log Archiving
MEB能做到在线热备,备份时不影响数据库读写,这是利用了InnoDB事务日志,在备份期间持续监视redo log的变化,读取增量变化,写入到ibbackup_logfile,也就不需要上锁来保障备份一致性。(对非InnoDB的文件需要上读锁拷贝)
如果备份期间数据库写入负载特别大,而写入ibbackup_logfile速度较慢,redo log size也不大,很可能会出现ibbackup_logfile的写入速度跟不上redo log记录生成速度,redo log 空间不够时需要覆写日志文件,那么来不及写入ibbackup_logfile的记录会丢失,导致备份失败。
MEB 4.1对此做了优化,将redo log处理线程拆分成多线程分工合作,提高处理redo log的效率,降低了redo log覆写造成备份失败的概率,但redo log新增速度和ibbackup_logfile写入速度悬殊太大,问题依然会发生。
MySQL 8.0.17支持了redo log archiving 彻底解决了此问题,备份前设置innodb_redo_log_archive_dirs,指定redo log归档目录。MEB备份时自动开启日志归档,当checkpoint时会将旧记录归档到此目录,后续从归档文件中读取redo日志记录,避免了覆写可能导致的redo记录丢失。
请点击输入图片描述
注意:innodb_redo_log_archive_dirs 不能在数据目录下,目录权限要求是700
特性3:Page Tracking
Page Tracking 是为优化增量备份效率,减少不必要的数据页扫描。
增量备份当前有3种扫描模式:
page-track:利用LSN精确跟踪上次备份之后被修改页面,仅复制这些页面,效率最快。
optimistic:扫描上次备份之后被修改的InnoDB 数据文件中,找出并拷贝修改的页面。依赖系统时间,使用存在*。
full-scan:扫描所有InnoDB数据文件,找出并拷贝自上次备份之后修改的页面,效率最慢
1、利用page-track增量备份,需先安装备份组件
mysql> INSTALL COMPONENT "file://component_mysqlbackup";
2、在全备前开启page-track
SELECT mysqlbackup_page_track_set(true);
3、全备之后,做增量备份时指定若满足page tracking条件,默认会使用page-track模式,否则会使用full-scan模式,也可以指定--incremental=page-track。
mysqlbackup --incremental-backup-dir=backup_incr --trace=3 --incremental=page-track --incremental-base=history:last_full_backup backup
incremental-base有3种选择
last_backup:基于前一次备份做增备,前一次备份可能是增备,也可能是全备。这种方式全备之间可能会有多个增备,每次增量可能比较小,但恢复时需要逐个合并。
last_full_backup:基于前一次全备做增备。这种方式增备会越往后体积可能越大,但恢复时只需要合并最后一次增量备份。
dir:基于前一次的备份目录,前一次备份可能是增备,也可能是全备。
测试对比full-scan 和page-track ,在变更页小于总体50%的情况下 ,备份效率至少能有1倍的速度提升。
page-track 模式 磁盘读写均衡,说明读写的都是修改页面。
请点击输入图片描述
full-scan模式 磁盘读写差别很大,说明读了很多未修改的页面。
请点击输入图片描述
热心网友
时间:2022-04-07 19:29
1.mysqlmp
在日常工作中,我们会使用mysqlmp命令创建sql格式的转储文件来备份数据库。或者我们把数据导出后做数据迁移,主备搭建等操作。mysqlmp是一个逻辑备份工具,复制原始的数据库对象定义和表数据产生一组可执行的SQL语句。 默认情况下,生成insert语句,也能生成其它分隔符的输出或XML格式的文件。
shell> mysqlmp [arguments] > file_name
我们简单的来看一下日常的用法:
备份所有的数据库:
shell> mysqlmp --all-databases > mp.sql (不包含INFORMATION_SCHEMA,performance_schema,sys,如果想要导出的话还要结合--skip-lock-tables和--database一起用)
备份指定的数据库:
shell> mysqlmp --databases db1 db2 db3 > mp.sql
当我们只备份一个数据的时候可以省去 --databases 直接写成:mysqlmp test > mp.sql 不过有一些细微的差别,如果不加的话,数据库转储输出不包含创建数据库和use语句,所以可以不加这个参数直接导入到其它名字的数据库里
当然我们也可以只备份某个表 :
mysqlmp --user [username] --password=[password] [database name] [table name] table_name.sql
了解了简单的一些用法后我们再着重的看一下几个参数:
--master-data 获取备份数据的Binlog位置和Binlog文件名,用于通过备份恢复的实例之间建立复制关系时使用,该参数会默认开启。
--mp-slave 用于在slave上mp数据,建立新的slave。因为我们在使用mysqlmp时会锁表,所以大多数情况下,我们的导出操作一般会在只读备库上做,为了获取主库的Relay_Master_Log_File和Exec_Master_Log_Pos,需要用到这个参数,不过这个参数只有在5.7以后的才会有
–no-data, -d 不导出任何数据,只导出数据库表结构
刚刚我们说过在使用mysqlmp的时候会锁表,我们来详细的看一下它的锁机制。
我们开两个窗口,在第一个里面执行mysqlmp -uroot -pxxxxx --master-data=2 --databases dbname > /tmp/dbnamedate +%F.sql
然后第二个窗口登陆进去,使用show process的命令可以看到目前mp的session正在执行
1.png
SELECT /*!40001 SQL_NO_CACHE */ * FROM table_name; 可以看到这条sql正在以no_cache的模式查询数据。
然后我们在同样的表上执行一下select,发现被阻塞了。光标一直不返回。
2.png
一般遇到这种文件,我们会想是不是有锁呢?
为了验证我们查看一下锁的信息,可以发现mp的进程实际上是加了锁的。
3.png
我们把具体的general_log打开,然后看一下当时的操作:
4.png
4101044 Query FLUSH /*!40101 LOCAL */ TABLES
4101044 Query FLUSH TABLES WITH READ LOCK (关闭所有打开的表,同时对于所有数据库中的表都加一个读锁,直到显示地执行unlock tables,该操作常常用于数据备份的时候。)
4101044 Query SHOW MASTER STATUS(这是因为我用了--master-data=2)
所以这个时候表就会被锁住。
如果我不加--master-data参数(mysqlmp -uroot -pxx --databases db > /tmp/dbnamedate +%F.sql) mysql会显示的对每一张要备份的表执行
LOCK TABLES table_name1 READ,LOCK TABLES table_name2 READ
并且也不会有读的阻塞。
那有没有不锁的方法,其实也是有的,就是使用--single-transaction把备份的操作放在一个事务里去进行
带上--single-transaction参数的mysqlmp备份过程:
如果是5.6版本的mysql
三种mysql备份方式让你轻松备份数据mysql三种备份方式
定时备份是一种最常见的备份方式,它可以根据用户设定的时间计划进行备份,如每天、每周、每月等。通过定时备份,可以保证数据在最短时间内得到恢复,并且不会因为疏忽忘记备份而导致数据损失。使用定时备份需要以下步骤:Step 1:创建备份脚本 备份脚本可以是一个shell脚本或bat脚本,内容包括连接MySQL数据库、...
mysql数据库备份和还原的常用命令小结
一、备份常用命令 1. 使用`mysqldump`工具进行逻辑备份:`mysqldump -u 用户名 -p 数据库名 > 备份文件.sql`解释:`mysqldump`是MySQL提供的逻辑备份工具,可以通过导出数据库或表的结构及数据到SQL文件的方式备份。命令中的`-u`参数用于指定用户名,`-p`参数用于提示输入密码,`数据库名`是需要备份...
深入了解MySQL三种备份策略数据安全备份不可少mysql三种备份
1. 定期备份MySQL数据。数据备份的最佳实践是每天备份MySQL数据。如果您拥有高流量和敏感的数据,可能会考虑使用每小时或每分钟备份。2. 储存备份数据的位置应该安全。数据备份必须存储在安全的地方,如加密的云存储或备份设备上,这样未经授权的访问才能避免。最好将自动备份存储在远程地点,以防服务器上发...
MySQL数据库备份和还原的常用命令小结
备份MySQL数据库,主要有以下几种方式:全面备份整个数据库:使用`mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql`删除表备份:添加`--add-drop-table`参数,如`mysqldump --add-drop-table -uusername -ppassword databasename > backupfile.sql`压缩备份:`mysqldump -hh...
浅谈MySQL数据库备份的几种方法
mysql常见的备份方式有:mysqldump、mysqlhotcopy、BACKUP TABLE 、SELECT INTOOUTFILE,又或者备份二进制日志(binlog),还可以是直接拷贝数据文件和相关的配置文件。MyISAM表是保存成文件的形式,因此相对比较容易备份,上面提到的几种方法都可以使用。Innodb 所有的表都保存在同一个数据文件 ibdata1中(也可能是多个文件,...
MySQL几种方法的数据库备份
MySQL数据库算是经常使用的数据库中最好使用的数据库了,对于备份的操作也不例外。所以今天分享一下MySQL数据库的备份的几种方式。方式一:使用命令行的方式。命令行的方式较为快捷。仅仅须要在命令行中使用mysqldump命令就可以,默认情况下该命令在mysql的bin文件夹中(前提须要将bin文件夹加入到path中),...
MySQL中备份的几种方式
mysqldump常用的备份参数-R --events --triggers=true --single-transaction --master-data=2-R(--routines): 导出存储过程以及自定义函数--events: 导出事件--triggers=true:导出触发器.默认开启,用--skip-triggers禁用--single-transaction:该选项在导出数据之前提交一个BEGIN SQL 语句,...
如何备份mysql queries
一、MySQL备份类型 1.热备份、温备份、冷备份 (根据服务器状态)热备份:读、写不受影响;温备份:仅可以执行读操作;冷备份:离线备份;读、写操作均中止;2.物理备份与逻辑备份 (从对象来分)物理备份:复制数据文件;逻辑备份:将数据导出至文本文件中;3.完全备份、增量备份、差异备份 (从数据...
MySQL三重备份保障你的数据安全mysql三备份
MySQL三重备份指的是对MySQL数据库进行三份备份,分别存储在主机本地、备份服务器、云存储服务器中,以保证在任何一种情况下都不会出现数据丢失的问题。其原理如下所述:1.本地备份 本地备份指的是将备份数据存储在主机本地磁盘上。这种备份方式速度较快,同时也方便进行数据恢复。因此,在我们进行数据...
MySQL备份的几种常用
mysqlbackup --incremental-backup-dir=backup_incr --trace=3 --incremental=page-track --incremental-base=history:last_full_backup backupincremental-base有3种选择 last_backup:基于前一次备份做增备,前一次备份可能是增备,也可能是全备。这种方式全备之间可能会有多个增备,每次增量可能比较小,...