mysql简单备份和恢复

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

版本:mysql8.0

官方文档 :MySQL :: MySQL 8.0 Reference Manual :: 7 Backup and Recovery

1.物理备份恢复

物理备份是以数据文件形式备份。这种方式效率高点,适合大型数据库备份。物理备份可冷备可热备。

使用mysqlbackup 命令进行物理备份,非常遗憾 mysqlbackup命令只在mysql企业版才有。

还可以使用第三方软件xtrabackup进行物理热备 

我的mysql版本是8.0.35

XtraBackup 2.4 与较新版本的 MySQL 5.7 兼容

XtraBackup 8.0.34 允许备份MySQL 8.0.35 和 MySQL 8.0.35 及更高版本。

1.0 准备工作 

创建一个有备份相关权限的mysql用户 ,用于XtraBackup备份

mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY '123456';
 

mysql> GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
 

mysql> GRANT SELECT ON performance_schema.log_status TO 'bkpuser'@'localhost';
 

mysql> GRANT SELECT ON performance_schema.keyring_component_status TO 'bkpuser'@'localhost';
 

mysql> GRANT SELECT ON performance_schema.replication_group_members TO 'bkpuser'@'localhost';
 

mysql> FLUSH PRIVILEGES;

查看用户权限

SHOW GRANTS FOR 'bkpuser'@'localhost'; 

1.1 Percona XtraBackup安装 

使用yum安装

1.安装Percona yum仓库

sudo yum install \ https://repo.percona.com/yum/percona-release-latest.\ noarch.rpm

2.启动仓库

 sudo percona-release enable-only tools release

3.安装 Percona XtraBackup

sudo yum install percona-xtrabackup-80

4.安装压缩算法

sudo yum install zstd 

  

1.2 完整备份

 xtrabackup --defaults-file=/etc/my8.cnf --backup --target-dir=/data/backups/ --user=bkpuser --password=123456

 xtrabackup默认会读取mysql配置文件/etc/my.cnf,但是我服务器中有多个mysql,我想要指定备份mysql就需要参数--defaults-file=指定配置文件

1.3 增量备份

在 /data/backups/完全备份的基础上进行增量备份

xtrabackup --defaults-file=/etc/my8.cnf --backup --target-dir=/data/backups/inc1 --incremental-basedir=/data/backups/ --user=bkpuser --password=123456 

在 /data/backups/inc1/增量备份的基础上进行增量备份

xtrabackup --defaults-file=/etc/my8.cnf --backup --target-dir=/data/backups/inc2 --incremental-basedir=/data/backups/inc1/ --user=bkpuser --password=123456 

1.4 压缩备份和解压

压缩 

xtrabackup --defaults-file=/etc/my8.cnf --backup --compress --compress-threads=4 --compress-zstd-level=1 --target-dir=/data/backups2/ --user=bkpuser --password=123456 

--compress:Zstandard (ZSTD)算法进行压缩

--compress-threads=4:使用四个线程进行压缩

--compress-zstd-level=1:指定压缩级别,默认1

解压 

xtrabackup --decompress --target-dir=/data/backups4/

--decompress:解压

默认解压不会删除压缩文件,他会把压缩文件和解压好的文件放在一起 

可以使用--remove-original选项解压后删除压缩文件

1.5 备份恢复

在恢复之前一定要先准备备份(Prepare backup

1.5.1 准备备份

 关于Prepare backup的说明可以参考「XtraBackup」- 备份数据的“预备”(Prepare)操作 @20210220_xtrabackup --prepare-CSDN博客

XtraBackup 复制 InnoDB 数据文件,导致数据内部不一致;然后,该阶段对文件执行崩溃恢复,以再次创建一致、可用的数据库 。

1.5.1.1 准备完整备份 

 xtrabackup --prepare  --target-dir=/data/backups/

1.5.1.1 准备增量备份 

/data/backups8/base 基础备份

/data/backups8/inc1  base为基础进行的增量备份

/data/backups8/inc2  inc2为基础进行的增量备份

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base

xtrabackup --prepare --apply-log-only --target-dir=/data/backups8/base --incremental-dir=/data/backups8/inc1

xtrabackup --prepare --target-dir=/data/backups/base --incremental-dir=/data/backups/inc2

--apply-log-only:防止回滚,增量恢复一定要加上这个选项,最后一个准备备份不用加这个选项 

 

执行完准备备份后,增量备份文件都整合到基础备份中了,恢复时直接把基础备份复制到数据目录中进行恢复。 

1.5.2 还原备份

1.关闭数据库

2.清空原数据库的数据目录

3.把备份目录复制到数据目录

xtrabackup --defaults-file=/etc/my8.cnf --copy-back --target-dir=/data/backups8/base

或者使用  rsync 或 cp 来恢复文件

3.修改数据目录的归属权

 chown mysql8:mysql8 /opt/mysql/mysql8/data -R 

4.启动数据库 

2.逻辑备份恢复

逻辑备份是把数据转成SQL格式文件备份。这种方式转换需要耗时,效率较低,适合小中型数据库。优点是灵活,可以备份指定数据库、指定表等。移植性也好,恢复时不用一定要和备份时的mysql版本一样。逻辑备份只能热备。 

2.1 使用mysqldump进行备份 

2.1.1 以 SQL 格式转储数据 

备份所以数据库

mysqldump --all-databases > dump.sql 

 备份指定数据库

mysqldump --databases db1 db2 db3 > dump.sql 

备份单个数据库

方式一: 输出包含 create database 和 use 语句。

 mysqldump --databases test > dump.sql

方式二:没有 --databases,输出不包含 create database 和 use 语句,在恢复时需要主动 create database 和 use 数据库。

 mysqldump test > dump.sql

2.1.2 以 SQL 格式恢复数据 

使用 --databases的数据备份文件数据恢复

方法一: 

 mysql < dump.sql

方法二:

mysql> source dump.sql 

如果没有使用 --databases的数据备份文件数据恢复

方法一: 

先创建数据库

mysqladmin create db1 

 再加载转储文件

mysql db1 < dump.sql 

方法二:

mysql> CREATE DATABASE IF NOT EXISTS db1;

mysql> USE db1;

mysql> source dump.sql 

2.1.3 以分隔文本格式转储数据 

备份test数据库到 /opt/mysql/mysql8/下

mysqldump -uroot -p -P 3308 -S /tmp/mysql3.sock --tab=/opt/mysql/mysql8 test 


注意:如果出现错误 

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

需要在my.cnf配置文件下[mysqld]下添加secure-file-priv = /opt/mysql/mysql8表示这个位置可以放分隔文本格式转储数据文件,记得还要用chonw更换这个目录的用户所属权成mysql用户


 这个命令将生成两个文件

mysql简单备份和恢复,数据库,数据库

这个命令还有很多其他选项指定数据存储文本的格式

mysqldump -uroot -p -P 3308 -S /tmp/mysql3.sock --tab=/opt/mysql/mysql8 --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=0x0d0a test

生成数据文本

"2","cxk"
"1","hwz" 

--fields-terminated-by=str
用于分隔列值的字符串 (默认值: tab)
--fields-enclosed-by=char
将列值括起来的字符(默认值: 无字符)
--fields-optionally-enclosed-by=char
要将非数字列括起来的字符值 (默认值:无字符)
--fields-escaped-by=char
用于转义特殊字符的字符(默认值: 否) 转义)
--lines-terminated-by=str
行终止字符串 (默认值: 换行符) 

2.1.4 以分隔文本格式数据恢复

方法一:

mysql db1 < t1.sql

mysqlimport db1 t1.txt 

方法二:

mysql> USE db1;

mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1; 文章来源地址https://www.toymoban.com/news/detail-741012.html

到了这里,关于mysql简单备份和恢复的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

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

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

相关文章

  • Mysql数据库增量备份与恢复

    使用 mysqldump 进行完全备份,备份的数据中有重复数据,备份时间与恢复时间长。 而增量备份就是备份自上一次备份之后增加或改变的文件或内容。 1、增量备份的特点: 没有重复数据,备份量不大,时间短 恢复麻烦:需要上次完全备份及完全备份之后所有的增量备份才能恢复

    2024年02月07日
    浏览(84)
  • 【数据库四】MySQL备份与恢复

    数据库备份 物理备份 :直接对数据库的 数据文件或者日志文件 进行备份. 逻辑备份 :对 数据库的库或表对象 进行备份. 备份策略 完全备份 :每次备份 都备份完整的数据库 . 是对整个数据库、数据库结构和文件结构的备份。 保存的是 备份完成时刻的数据库 。 是 差异备份与增

    2024年02月11日
    浏览(59)
  • MySQL数据库的备份与恢复

    备份的主要目的是灾难恢复。 在生产环境中,数据的安全性至关重要。 任何数据的丢失都可能产生严重的后果。 造成数据丢失的原因: 程序错误 人为操作错误 运算错误 磁盘故障 灾难(如火灾、地震)和盗窃 1)物理备份 物理备份:对数据库操作系统的物理文件(如数据

    2024年02月04日
    浏览(208)
  • 9-MySQL数据库 数据的备份与恢复

    1.date文件的备份 2.mysqldump 备份 说明: mysqldump是MySQL数据库中的一个实用程序,它主要用于转储(备份)数据库。mysqldump通过生成一个SQL脚本文件,包含从头开始重新创建数据库所必需的(如 CREATE TABLE和INSERT等),来实现数据库的备份和转储。这样,你可以在任何时候通过运

    2024年02月08日
    浏览(65)
  • MySQL-备份+日志:介质故障与数据库恢复

    本关任务: 备份数据库,然后再恢复它。 为了完成本关任务,你需要掌握: 1.MySQL的恢复机制; 2.MySQL提供的备份与恢复工具。 和大多数DBMS一样,MySQL利用备份、日志文件实现恢复。 具体理论知识在此不详细介绍。 MySQL提供了以下工具: 逻辑备份工具:mysqldump 物理备份工具

    2024年02月05日
    浏览(82)
  • 【Linux】Mysql的数据库备份及恢复

            备份就是为了防止原数据丢失,保证数据的安全。当数据库因为某些原因造成部分或 者全部数据丢失后,备份文件可以帮我们找回丢失的数据。因此,数据备份是很重要 的工作。 常见数据库备份的应用场景如下: 数据丢失应用场景: 人为操作失误造成某些数据

    2023年04月10日
    浏览(50)
  • MySQL基础(三十八)数据库备份与恢复

    物理备份 :备份数据文件,转储数据库物理文件到某一目录。物理备份恢复速度比较快,但占用空间比较大,MySQL中可以用 xtrabackup 工具来进行物理备份。 逻辑备份 :对数据库对象利用工具进行导出工作,汇总入备份文件内。逻辑备份恢复速度慢,但占用空间小,更灵活。

    2024年02月06日
    浏览(59)
  • 【七天入门数据库】第五天 MySQL的备份恢复

    【七天入门数据库】第一天 MySQL的安装部署 【七天入门数据库】第二天 数据库理论基础 【七天入门数据库】第三天 MySQL的库表操作 【七天入门数据库】第四天 数据操作语言DML 【七天入门数据库】第五天 MySQL的备份恢复 【七天入门数据库】第六天 MySQL的视图与索引 【七天

    2024年02月16日
    浏览(55)
  • mysql-DBA(1)-数据库备份恢复-导入导出-日志解释

    log: hdd data :ssd  ,备份和导出都慢,缓冲池有污染。 逻辑备份:把所有的命令转换成sql语句。 修改配置文件: -A 备份所有 -B 备份哪个数据库 --master-data=1 同步 内容: 备份参数: 1.备份成文件,里面就是sql语句 2.routine: 3.trigger 触发器 4.event: 定时任务 5.-B 数据库 1.有-B 表

    2024年03月09日
    浏览(66)
  • MySQL数据库的备份、恢复、导出、导入(bin log和mydump)

    一、使用 bin log 来恢复数据 一、bin log的三种格式 1、statement:基于SQL语句的复制(statement-based replication,SBR) 2、row:基于行的复制(row-based replication,RBR) 3、mixed:混合模式复制(mixed-based replication,MBR) 4、查看模式和更改模式 二、配置bin log策略 三、获取bin log文件列表

    2024年02月21日
    浏览(60)

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

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

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

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

二维码1

领取红包

二维码2

领红包