第二十三天:mysql数据备份及还原

这篇具有很好参考价值的文章主要介绍了第二十三天:mysql数据备份及还原。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

一、备份类型

完全备份,部分备份
  完全备份:整个数据集
  部分备份:只备份数据子集,如部分库或表
完全备份、增量备份、差异备份
  增量备份:仅备份最近一次完全备份或增量备份(如果存在增量)以来变化的数据,备份较快,还原复杂
  差异备份:仅备份最近一次完全备份以来变化的数据,备份较慢,还原简单
冷、温、热备份
  冷备:读、写操作均不可进行,数据库停止服务
  温备:读操作可执行;但写操作不可执行
  热备:读、写操作均可执行
物理和逻辑备份
  物理备份:直接复制数据文件进行备份,与存储引擎有关,占用较多的空间,速度快
  逻辑备份:从数据库中"导出"数据另存而进行的备份,与存储引擎无关,占用空间少,速度慢,可能丢失精度
1、备份什么
  数据
  二进制日志、InnoDB的事务日志
  用户帐号,权限设置,程序代码(存储过程、函数、触发器、事件调度器)
  服务器的配置文件
2、备份工具
cp, tar等复制归档工具:物理备份工具,适用所有存储引擎;只支持冷备;完全和部分备份
LVM的快照:先加读锁,做快照后解锁,几乎热备;借助文件系统工具进行备份
mysqldump:逻辑备份工具,适用所有存储引擎,对MyISAM存储引擎进行温备;支持完全或部
分备份;对InnoDB存储引擎支持热备,结合binlog的增量备份
xtrabackup:由Percona提供支持对InnoDB做热备(物理备份)的工具,支持完全备份、增量备份
MariaDB Backup: 从MariaDB 10.1.26开始集成,基于Percona XtraBackup 2.3.8实现
mysqlbackup:热备份, MySQL Enterprise Edition 组件
mysqlhotcopy:PERL 语言实现,几乎冷备,仅适用于MyISAM存储引擎,使用LOCK TABLES、
FLUSH TABLES和cp或scp来快速备份数据库
 
二、mysqldump 备份工具

1、mysqldump 说明

逻辑备份工具:
mysqldump, mydumper, phpMyAdmin
Schema和数据存储在一起、巨大的SQL语句、单个巨大的备份文件
mysqldump是MySQL的客户端命令,通过mysql协议连接至mysql服务器进行备份
命令格式:
mysqldump [OPTIONS] database [tables]   #支持指定数据库和指定多表的备份,但数据库本身定义不备份
mysqldump [OPTIONS] -B DB1 [DB2 DB3...] #支持指定数据库备份,包含数据库本身定义也会备份
mysqldump [OPTIONS] -A [OPTIONS]        #备份所有数据库,包含数据库本身定义也会备份

2、mysqldump 常见通用选项:

-u, --user=name     User for login if not current user
-p, --password[=name]  Password to use when connecting to server
-A, --all-databases #备份所有数据库,含create database
-B, --databases db_name…  #指定备份的数据库,包括create database语句
-E, --events:#备份相关的所有event scheduler
-R, --routines:#备份所有存储过程和自定义函数
--triggers:#备份表相关触发器,默认启用,用--skip-triggers,不备份触发器
--default-character-set=utf8 #指定字符集
--master-data[=#]:#注意:MySQL8.0.26版以后,此选项变为--source-data
#此选项须启用二进制日志
#1:所备份的数据之前加一条记录为CHANGE MASTER TO语句,非注释,不指定#,默认为1,适合于主从复
制多机使用
#2:记录为被注释的#CHANGE MASTER TO语句,适合于单机使用,适用于备份还原
#此选项会自动关闭--lock-tables功能,自动打开-x | --lock-all-tables功能(除非开启--
single-transaction)
-F, --flush-logs #备份前滚动日志,锁定表完成后,执行flush logs命令,生成新的二进制日志文件,配合-A 或 -B 选项时,会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新,可通过和--
single-transaction或-x,--master-data 一起使用实现,此时只刷新一次二进制日志
--compact        #去掉注释,适合调试,节约备份占用的空间,生产不使用
-d, --no-data    #只备份表结构,不备份数据,即只备份create table 
-t, --no-create-info #只备份数据,不备份表结构,即不备份create table 
-n,--no-create-db #不备份create database,可被-A或-B覆盖
--flush-privileges #备份mysql或相关时需要使用
-f, --force       #忽略SQL错误,继续执行
--hex-blob        #使用十六进制符号转储二进制列,当有包括BINARY,VARBINARY,BLOB,BIT的数据类型的列时使用,避免乱码
-q, --quick     #不缓存查询,直接输出,加快备份速度

mysqldump的MyISAM存储引擎相关的备份选项:

MyISAM不支持事务,只能支持温备;不支持热备,所以必须先锁定要备份的库,而后启动备份操作
-x,--lock-all-tables #加全局读锁,锁定所有库的所有表,同时加--single-transaction或--lock-tables选项会关闭此选项功能,注意:数据量大时,可能会导致长时间无法并发访问数据库
-l,--lock-tables #对于需要备份的每个数据库,在启动备份之前分别锁定其所有表,默认为on,--skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
#注:以上选项对InnoDB表一样生效,实现温备,但不推荐使用
mysqldump的InnoDB存储引擎相关的备份选项:
InnoDB 存储引擎支持事务,可以利用事务的相应的隔离级别,实现热备,也可以实现温备但不建议用
--single-transaction
#此选项Innodb中推荐使用,不适用MyISAM,此选项会开始备份前,先执行START TRANSACTION指令开启事务
#此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表(目前只有InnoDB可以); 转储不保证与其他存储引擎保持一致。 在进行单事务转储时,要确保有效的转储
文件(正确的表内容和二进制日志位置),没有其他连接应该使用以下语句:ALTER TABLE,DROP TABLE,RENAME TABLE,TRUNCATE TABLE,此选项和--lock-tables(此选项隐含提交挂起的事务)选
项是相互排斥,备份大型表时,建议将--single-transaction选项和--quick结合一起使用

3、生产环境实战备份策略

InnoDB建议备份策略
mysqldump -uroot -p123456 -A -F -E -R --triggers --single-transaction --masterdata=2 --flush-privileges --default-character-set=utf8 --hex-blob
>${BACKUP}/fullbak_${BACKUP_TIME}.sql
#新版8.0.26以上
mysqldump -uroot -p123456 -A -F -E -R --triggers --single-transaction --sourcedata=2 --flush-privileges --default-character-set=utf8 --hex-blob
>${BACKUP}/fullbak_${BACKUP_TIME}.sq

4、特定数据库的备份脚本

[root@centos8 ~]#cat mysql_backup.sh 
#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup
DB=hellodb
PASS=magedu
[ -d $DIR ] || mkdir $DIR
mysqldump -uroot -p "$PASS" -F -E -R --triggers  --single-transaction --masterdata=2 --default-character-set=utf8 -q  -B $DB | gzip > 
${DIR}/${DB}_${TIME}.sql.gz

5、分库备份并压缩

第二十三天:mysql数据备份及还原

 6、分库备份的实战脚本

[root@centos8 ~]#cat backup_db.sh 
#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup
PASS=magedu
[ -d "$DIR" ] || mkdir $DIR
for DB in `mysql -uroot -p "$PASS" -e 'show databases' | grep -Ewv 
"^Database|.*schema$"`;do
 mysqldump -F --single-transaction --master-data=2 --default-characterset=utf8 -q -B $DB | gzip >  ${DIR}/${DB}_${TIME}.sql.gz
done

7、利用二进制日志,还原数据库最新状态

#二进制日志独立存放
[mysqld]
log-bin=/data/mysql/mysql-bin 
#完全备份,并记录备份的二进制位置
mysqldump  -uroot -pmagedu -A -F --default-character-set=utf8  --singletransaction --master-data=2 | gzip > /backup/all_`date +%F`.sql.gz
#修改数据库
insert students (name,age,gender)value('mage',20,'M');
insert students (name,age,gender)value('wang',22,'M');
#损坏数据库
rm -rf /var/lib/mysql/*
#还原
cd /backup
gzip -d all_2019-11-25.sql.gz
#CentOS 8 需要事先生成数据库相关文件,CentOS7 不需要执行此步
mysql_install_db  --user=mysql

8、mysqldump 和二进制日志结合实现差异(增量)备份 

[root@centos8 ~]#mysqldump -uroot -p -A -F --single-transaction --master-data=2 
|gzip > /backup/all-`date +%F`.sql.gz
#观察上面备份文件中记录的二进制文件和位置,定期将其之后生成的所有二进制日志进行复制备份
[root@centos8 ~]#cp /var/lib/mysql/mariadb-bin.000003 /backup #假设mariadbbin.000003是后续生成的二进制日志
[root@centos8 ~]#mysqlbinlog backup/mariadb-bin.000003 > /backup/inc.sql

 文章来源地址https://www.toymoban.com/news/detail-825406.html

 

三、xtrabackup 备份工具

1、xtrabackup 工具介绍

Percona 公司
官网:www.percona.com
    percona-server
    InnoDB --> XtraDB
Xtrabackup备份工具
  percona提供的mysql数据库备份工具,惟一开源的能够对innodb和xtradb数据库进行热备的工具
  手册:https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html
  下载: https://www.percona.com/downloads/ 
xtrabackup 特点:
  备份还原过程快速、可靠
  备份过程不会打断正在执行的事务
  能够基于压缩等功能节约磁盘空间和流量
  自动实现备份检验
  开源,免费
xtrabackup工具文件组成
  Xtrabackup2.2 版之前包括4个可执行文件:
    innobackupex: Perl 脚本
    xtrabackup: C/C++,编译的二进制程序
    xbcrypt: 加解密
    xbstream: 支持并发写的流文件格式 
说明:
xtrabackup 是用来备份 InnoDB 表的,不能备份非 InnoDB 表,和 MySQL Server 没有交互innobackupex 脚本用来备份非 InnoDB 表,同时会调用 xtrabackup 命令来备份 InnoDB 表,还会和MySQL Server 发送命令进行交互,如加全局读锁(FTWRL)、获取位点(SHOW SLAVE STATUS)等。即innobackupex是在 xtrabackup 之上做了一层封装实现的
备份过程:

第二十三天:mysql数据备份及还原

 

备份生成的相关文件
使用innobackupex备份时,其会调用xtrabackup备份所有的InnoDB表,复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件,同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命名的目录中,在备份时,innobackupex还会在备份目录中创建如下文件:
xtrabackup_info:文本文件,innobackupex工具执行时的相关信息,包括版本,备份选项,备份时长,备份LSN(log sequence number日志序列号),BINLOG的位置
xtrabackup_checkpoints:文本文件,备份类型(如完全或增量)、备份状态(如是否已经为prepared状态)和LSN范围信息,每个InnoDB页(通常为16k大小)都会包含一个日志序列号LSN。
LSN是整个数据库系统的系统版本号,每个页面相关的LSN能够表明此页面最近是如何发生改变的xtrabackup_binlog_info:文本文件,MySQL服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置,可利用实现基于binlog的恢复
backup-my.cnf:文本文件,备份命令用到的配置选项信息
xtrabackup_logfile:备份生成的二进制日志文件
 
2、xtrabackup 安装
在EPEL源中 
yum install percona-xtrabackup

3、xtrabackup 用法

xtrabackup工具备份和还原,需要三步实现
1. 备份:对数据库做完全或增量备份
2. 预准备: 还原前,先对备份的数据,整理至一个临时目录
3. 还原:将整理好的数据,复制回数据库目录中 
xtrabackup 选项参考:
https://www.percona.com/doc/percona-xtrabackup/LATEST/genindex.html
备份: 
innobackupex [option] BACKUP-ROOT-DIR

第二十三天:mysql数据备份及还原

 Prepare预准备:

innobackupex --apply-log [option] BACKUP-DIR
选项说明: 
--apply-log:#一般情况下,在备份完成后,数据尚且不能用于恢复操作,因为备份的数据中可能会包含尚
未提交的事务或已经提交但尚未同步至数据文件中的事务。因此,此时数据文件仍处理不一致状态。此选项作
用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态
--use-memory:#和--apply-log选项一起使用,当prepare 备份时,做crash recovery分配的内存
大小,单位字节,也可1MB,1M,1G,1GB等,推荐1G
--export:#表示开启可导出单独的表之后再导入其他Mysql中
--redo-only:#此选项在prepare base full backup,往其中合并增量备份时候使用,但不包括对最
后一个增量备份的合并
还原: 
innobackupex --copy-back [选项] BACKUP-DIR
innobackupex --move-back [选项] [--defaults-group=GROUP-NAME] BACKUP-DIR
选项说明: 

第二十三天:mysql数据备份及还原

 还原注意事项:

1. datadir 目录必须为空。除非指定innobackupex --force-non-empty-directorires选项指定,否则--copy-back选项不会覆盖
2. 在restore之前,必须shutdown MySQL实例,不能将一个运行中的实例restore到datadir目录中
3. 由于文件属性会被保留,大部分情况下需要在启动实例之前将文件的属主改为mysql,这些文件将属于创建备份的用户, 执行chown -R mysql:mysql /data/mysql,以上需要在用户调用innobackupex之前完成 

4、利用 xtrabackup 实现完全备份及还原

第二十三天:mysql数据备份及还原

 5、利用xtrabackup8.0 完全,增量备份及还原MySQL8.0

第二十三天:mysql数据备份及还原

 第二十三天:mysql数据备份及还原

 第二十三天:mysql数据备份及还原

 6、xtrabackup单表导出和导入

第二十三天:mysql数据备份及还原

 

第二十三天:mysql数据备份及还原

 

 

到了这里,关于第二十三天:mysql数据备份及还原的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • MySQL 数据备份和数据恢复

    目录 一、数据备份 1、概述 2、MySQLdump命令备份 1)备份单个数据库中的所有表 2) 备份数据中某个或多个表 3) 备份所有数据库 4)备份多个库 5) 只备份一个表或多个表结构 二、数据恢复 三、数据备份与恢复应用 1、概述 数据备份是数据库管理员非常重要的工作之一。系统意

    2024年02月11日
    浏览(39)
  • Mysql之数据备份

    3.查看通用查询日志是否开启 4.查看二进制日志是否开启 5.查看慢查询日功能是否开启 6.查看慢查询时间设置 7.在数据库中设置开启慢查询的方法 达到事务一致性(每次重启会重做) 确保日志的持久性,防止在发生故障,脏页未写入磁盘。重启数据库会进行redo log执行重做,

    2024年02月09日
    浏览(32)
  • mysql(四)数据备份

    目录 前言 一、概述 二、备份的类型 (一)物理与逻辑角度  (二)数据库备份策略角度 三、常见的备份方法 四、完整备份 (一)打包数据库文件备份 (二)备份工具备份 五、增量备份 六、操作 (一)完整备份  (二)使用工具备份mysqldump   (三)增量修改 总结 在生

    2024年02月15日
    浏览(35)
  • MySQL 数据备份

    物理备份: 在文件系统层面,直接拷贝数据库相关文件(数据文件、索引文件和日志文件等)完成备份 在对MySQL数据进行备份的时候,主要备份以下内容: 用户信息: 例如MySQL的账号信息,账号的权限等。这些都是存放再mysql这个数据库中的。 业务数据: 相关的业务数据库

    2024年02月05日
    浏览(41)
  • MySQL数据备份

    备份的是建表,建库,插入,更新等操作所执行SQL语句,适用于中小型数据库,效率相对较低。 本质:导出的是SQL语句文件 优点:不论是什么存储引擎,都可以用mysqldump备成SQL语句 缺点:速度较慢,导入时可能会出现格式不兼容的突发情况,无法做增量备份和累计增量备份

    2024年01月23日
    浏览(53)
  • mysql数据备份并重置

    1.备份mysql数据 mysqldump -uroot --single-transaction -R -E --databases lc2 cpm a10_goods self_warehouse /mnt/vdc1/var/lib/mysql/datadir/lc2_cpm_a10_goods_self_warehouse.sql -p Y6B2wb 2.初始化mysql mysqld --initialize; 3.修改配置文件 z mysql cd /etc/mysql/mysql.conf.d vim mysql.cnf 配置文件如下: 4.分配数据可执行权限 chown mysql:

    2024年02月14日
    浏览(39)
  • mysql 数据备份和还原

    MySQL 数据备份和还原有哪些方式?你可以使用以下几种方式: 使用 mysqldump 命令来备份和还原数据。这是一种逻辑备份方法,将数据以 SQL 格式导出。你可以使用以下命令来备份和还原数据: mysqldump -u username -p database_name backup_file.sql mysql -u username -p password database_name backup_fi

    2024年02月11日
    浏览(39)
  • Mysql 数据备份(详细教程)

    为什么要备份 数据是企业生存的命脉 什么是备份 将数据另外保存一份 备份到哪里 通常采用异地保存 什么时候备份 备份的窗口期,通常是业务压力最低点 如何备份 备份方法 物理备份 逻辑备份 备份策略 完整备份 增量备份 差异备份 备份三要素 BW:完成备份需要的时间 R

    2024年02月06日
    浏览(41)
  • Mysql 日志管理 数据备份

    1.错误日志 2.通过查询日志 3.二进制日志(binlog) 4.慢查询日志 在企业中,数据的价值至关重要,数据保障了企业业务的正常运行。因此,数据的安全性及数据的可靠性是运维的重中之重,任何数据的吊事都可能对企业产生严重的后果。  通常情况下,造成数据丢失的原因有一

    2024年02月10日
    浏览(54)
  • MySQL数据备份与恢复

    备份的主要目的: 备份的主要目的是:灾难恢复,备份还可以测试应用、回滚数据修改、查询历史数据、审计等。 日志: MySQL 的日志默认保存位置为: ##配置文件 ##通用查询日志,用来记录MySQL的所有连接和语句,默认是关闭的 ##二进制日志(binlog),用来记录所有更新了数据

    2024年02月11日
    浏览(44)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包