Mysql数据备份和恢复

已于 2023-11-20 21:07:49 修改
阅读量191 收藏
点赞数 2
分类专栏： Mysql 文章标签： mysql 数据库
于 2023-11-20 20:02:52 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Cloud_skyen/article/details/134127443
版权

目录

        一、逻辑备份

        备份所有库

        备份多个数据库

        只备份数据库表结构

        备份表

        恢复数据库和表

        恢复全恢复单个或多个库

         恢复表

二、物理备份

        完全备份

        完全备份恢复

        增量备份

        增量备份恢复

        差异备份

        差异备份恢复

三、Mysql bin-log日志恢复数据

一、逻辑备份

备份的是建库、建表、插入等操作所执行SQL语句，适用于中小型数据库，效率相对较低。

本质：导出的是SQL语句文件

优点：不论是什么春初引擎，都可以使用mysqldump备成SQL语句

缺点：速度较慢，导入时可能出现格式不兼容的突发情况，无法做增量备份和累计增量备份。

语法：

        远程备份：mysqldump -h 服务器 -u 用户名 -p 密码 数据库名 > 备份文件名.sql

        本地备份：mysqldump -u 用户名 -p 密码 数据库名 > 备份文件名.sql

常用选项

        备份所有库

        [-A|--all-databases]

[root@haitao ~]# mysqldump -uroot -p'Haitao@123' --all-databases >all_db.sql

mysqldump -uroot -p'Haitao@123' --all-databases >all_db.sql

        备份多个数据库

        [-B|--database]

        查看我拥有的数据库

        

 [root@haitao ~]# mysqldump -uroot -p'Haitao@123' --databases xiaohan >one_db.sql

mysqldump -uroot -p'Haitao@123' --databases xiaohan >one_db.sql

        只备份数据库表结构

        [-d|--no-data]

        查看有哪些表

[root@haitao ~]# mysqldump -uroot -p'Haitao@123' -d xiaohan haitao >one_tb.sql

mysqldump -uroot -p'Haitao@123' -d xiaohan haitao >one_tb.sql

        备份表

         语法：mysqldump -u 用户名 -p 密码 数据库名 表名 表名 表名 > 备份表名.sql

[root@haitao ~]# mysqldump -uroot -p'Haitao@123' xiaohan haitao > haitao_tb.sql

mysqldump -uroot -p'Haitao@123' xiaohan haitao > haitao_tb.sql

       恢复数据库和表

        恢复全部库

  为保证数据一致性，应在恢复数据之前停止数据库对外的服务，停止binlog日志，因为binlog使用binlog日志恢复数据时也会产生binlog日志。

  首先删除数据库和表

mysql> drop database xiaohan;

mysql> drop database xiaoxi;

[root@haitao ~]# mysql -uroot -p'Haitao@123' <all_db.sql

mysql -uroot -p'Haitao@123' <all_db.sql

登录数据库查看刚刚删除的库是否有恢复成功。

        恢复单个或多个库

 mysql> drop database xiaohan;

创建一个库

mysql> create database xiaohan;

恢复单个库

[root@localhost ~]# mysql -uroot -p'Haitao@123' xiaohan <one_db.sql

mysql -uroot -p'Haitao@123' xiaohan <one_db.sql

登录数据库查看是否恢复成功

        恢复表

删除表

mysql> use xiaohan;

mysql> drop table haitao;

恢复表

[root@localhost ~]#mysql -uroot -p'Haitao@123' xiaohan <haitao_tb.sql

mysql -uroot -p'Haitao@123' xiaohan <haitao_tb.sql

验证是否恢复成功

[root@localhost ~]# mysql -p'Haitao@123'

mysql> use xiaohan;

mysql> show tables;

mysql> desc haitao;

二、物理备份

物理备份： 直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本。

安装innobackup

[root@localhost ~]# yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

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

 [root@localhost ~]# yum -y install percona-xtrabackup-24

yum -y install percona-xtrabackup-24

安装压缩工具

 [root@localhost ~]# yum -y install qpress

yum -y install qpress

创建备份目录

[root@localhost ~]# mkdir -p /xtrabackup/{full,increment,difference}

mkdir -p /xtrabackup/{full,increment,difference}

full存放完整备份

increment存放增量备份

difference存放差异备份

完全备份

每次都将所有数据（不管自第一次备份以来有没有修改过），进行一次完整的复制，备份后会清除文件的存档属性，方便日后增量备份或者差异备份进行版本比较。

特点：占用空间大，备份速度慢，但恢复时一次恢复到位，恢复速度快。

登录数据库查看我们有哪些库

[root@localhost ~]# mysql -uroot -p"Haitao@123"

mysql -uroot -p"Haitao@123"

 mysql> show databases;

show databases;

 退出数据库执行全量备份

mysql> \q;

\q;

[root@localhost ~]# innobackupex --user=root --password='Haitao@123' /xtrabackup/full/

innobackupex --user=root --password='Haitao@123' /xtrabackup/full/

 提示OK！即为成功。

 [root@localhost ~]# ll /xtrabackup/full/

ll /xtrabackup/full/

完全备份恢复

1、停止数据库

[root@localhost ~]# systemctl stop mysqld

systemctl stop mysqld

2、清理环境

[root@localhost ~]# rm -rf /var/lib/mysql/*

rm -rf /var/lib/mysql/*

 [root@localhost ~]# rm -rf /var/log/mysqld.log

rm -rf /var/log/mysqld.log

[root@localhost ~]# rm -rf /var/log/mysql-slow/slow.log

rm -rf /var/log/mysql-slow/slow.log

3、恢复数据

验证恢复

[root@localhost ~]# innobackupex --apply-log /xtrabackup/full/2023-11-20_14-08-14/

innobackupex --apply-log /xtrabackup/full/2023-11-20_14-08-14/

 恢复数据

[root@localhost ~]# innobackupex --copy-back /xtrabackup/full/2023-11-20_14-08-14/

innobackupex --copy-back /xtrabackup/full/2023-11-20_14-08-14/

 [root@localhost ~]# ll /var/lib/mysql

ll /var/lib/mysql

  4、修改权限

[root@localhost ~]# chown -R mysql.mysql /var/lib/mysql

chown -R mysql.mysql /var/lib/mysql

5、启动数据库

[root@localhost ~]# systemctl start mysqld

systemctl start mysqld

[root@localhost ~]# mysql -uroot -p'Haitao@123' 

mysql -uroot -p'Haitao@123'

 mysql> show databases;

show databases;

 

增量备份

增量备份: 每次备份上一次备份到现在产生的新数据

全量备份（周一）

[root@localhost ~]# innobackupex --user=root --password='Haitao@123' /xtrabackup/increment/

innobackupex --user=root --password='Haitao@123' /xtrabackup/increment/

增量备份（周二）
创建一个新的库

[root@localhost ~]# mysql -uroot -p'Haitao@123'

mysql -uroot -p'Haitao@123'

mysql> create database two charset utf8;

create database two charset utf8;

创建一个表

mysql> use two;

use two;

 mysql> create table day(id int,name varchar(20));

create table day(id int,name varchar(20));

插入数据

 mysql> insert into day values(1,'周二');

insert into day values(1,'周二');

mysql> \q

\q

[root@localhost ~]# innobackupex --user=root --password='Haitao@123' --incremental /xtrabackup/increment/ --incremental-basedir=/xtrabackup/increment/2023-11-20_17-02-36/

innobackupex --user=root --password='Haitao@123' --incremental /xtrabackup/increment/ --incremental-basedir=/xtrabackup/increment/2023-11-20_17-02-36/

 在周二增量备份基础上再增量备份

 [root@localhost ~]# mysql -uroot -p'Haitao@123'

mysql -uroot -p'Haitao@123'

 mysql> use two;

use two;

 mysql> insert into day values (2,'周三');

insert into day values (2,'周三');

mysql> select * from day;

select * from day;

mysql> \q

\q

[root@localhost increment]# innobackupex --user=root --password='Haitao@123' --incremental /xtrabackup/increment/ --incremental-basedir=/xtrabackup/increment/2023-11-20_17-21-49/

[root@localhost increment]# innobackupex --user=root --password='Haitao@123' --incremental /xtrabackup/increment/ --incremental-basedir=/xtrabackup/increment/2023-11-20_17-21-49/

增量备份恢复

1、停止数据库

[root@localhost ~]# systemctl stop mysqld

systemctl stop mysqld

2、清理环境

[root@localhost ~]# rm -rf /var/lib/mysql/*

rm -rf /var/lib/mysql/*

3、重演回滚redo log

先回滚到全量备份

 [root@localhost ~]# innobackupex --apply-log --redo-only /xtrabackup/increment/2023-11-20_17-02-36/

innobackupex --apply-log --redo-only /xtrabackup/increment/2023-11-20_17-02-36/

 回滚周二备份

 [root@localhost ~]# innobackupex --apply-log --redo-only /xtrabackup/increment/2023-11-20_17-02-36/ --incremental-dir=/xtrabackup/increment/2023-11-20_17-21-49/

innobackupex --apply-log --redo-only /xtrabackup/increment/2023-11-20_17-02-36/ --incremental-dir=/xtrabackup/increment/2023-11-20_17-21-49/

回滚周三备份

[root@localhost ~]# innobackupex --apply-log --redo-only /xtrabackup/increment/2023-11-20_17-02-36/ --incremental-dir=/xtrabackup/increment/2023-11-20_17-46-39/

innobackupex --apply-log --redo-only /xtrabackup/increment/2023-11-20_17-02-36/ --incremental-dir=/xtrabackup/increment/2023-11-20_17-46-39/

恢复数据 

 [root@localhost ~]# innobackupex --copy-back /xtrabackup/increment/2023-11-20_17-02-36/

innobackupex --copy-back /xtrabackup/increment/2023-11-20_17-02-36/

4、修改权限

 [root@localhost ~]# chown -R mysql.mysql /var/lib/mysql

chown -R mysql.mysql /var/lib/mysql

5、启动数据库

[root@localhost ~]# systemctl start mysqld

systemctl start mysqld

 登录数据库查看数据是否存在

[root@localhost ~]# mysql -p'Haitao@123'

mysql -p'Haitao@123'

 mysql> use two;

use two;

 mysql> select * from day;

select * from day;

差异备份

差异备份:只备份跟完整备份不一样的

备份差异备份（周四）

给day表插入数据

[root@localhost ~]# mysql -p'Haitao@123'

mysql -p'Haitao@123'

 mysql> use day;

use day;

 mysql> insert into day values (3,'周四');

insert into day values (3,'周四');

mysql> \q

\q

  创建差异备份

语法: # innobackupex --user=root --password=888 --incremental /xtrabackup --incremental-basedir=/xtrabackup/完全备份目录（周一）

[root@localhost ~]# innobackupex --user=root --password='Haitao@123' --incremental /xtrabackup/difference/ --incremental-basedir=/xtrabackup/increment/2023-11-20_17-02-36/

[root@localhost ~]# innobackupex --user=root --password='Haitao@123' --incremental /xtrabackup/difference/ --incremental-basedir=/xtrabackup/increment/2023-11-20_17-02-36/

差异备份恢复

 1、停止数据库

[root@localhost ~]# systemctl stop mysqld
 

systemctl stop mysqld

2、清理环境

[root@localhost ~]# rm -rf /var/lib/mysql/*

rm -rf /var/lib/mysql/*

3、重演回滚redo log

恢复全量的redo log

[root@localhost ~]# innobackupex --apply-log --redo-only /xtrabackup/increment/2023-11-20_17-02-36/

innobackupex --apply-log --redo-only /xtrabackup/increment/2023-11-20_17-02-36/

 恢复差异备份redo log

语法:# innobackupex --apply-log --redo-only /xtrabackup/完全备份目录（周一）--incremental-dir=/xtrabacku/某个差异备份

[root@localhost ~]# innobackupex --apply-log --redo-only /xtrabackup/increment/2023-11-20_17-02-36/ --incremental-dir=/xtrabackup/difference/2023-11-20_19-35-51/

innobackupex --apply-log --redo-only /xtrabackup/increment/2023-11-20_17-02-36/ --incremental-dir=/xtrabackup/difference/2023-11-20_19-35-51/

 恢复数据

语法:# innobackupex --copy-back /xtrabackup/完全备份目录（周一）

 [root@localhost ~]# innobackupex --copy-back /xtrabackup/increment/2023-11-20_17-02-36/

innobackupex --copy-back /xtrabackup/increment/2023-11-20_17-02-36/

4、修改权限

[root@localhost ~]# chown -R mysql.mysql /var/lib/mysql

chown -R mysql.mysql /var/lib/mysql

5、启动数据库

[root@localhost ~]# systemctl start mysqld

systemctl start mysqld

 登入数据库查看

[root@localhost ~]# mysql -p'Haitao@123'

mysql -p'Haitao@123'

 mysql> use two;

use two;

mysql> select * from day;

select * from day;

三、Mysql bin-log日志恢复数据

需要开启binlog日志功能

在配置文件/etc/my.cnf中添加

[root@localhost ~]# vim /etc/my.cnf

vim /etc/my.cnf

修改完配置文件需要重启服务

[root@localhost ~]# systemctl restart mysqld

开启后会生成binlog日志

刷新binlog日志，会出现新的binlog日志，记录之后的操作语句

mysql> flush logs;

查看binlog日志

mysqlbinlog 日志文件名

[root@localhost mysql]# mysqlbinlog mysql-bin.000001

mysqlbinlog mysql-bin.000001

进入数据库创建一个库

mysql> create database xiaoliu;

查看binlog日志

[root@localhost mysql]# mysqlbinlog mysql-bin.000002

mysqlbinlog mysql-bin.000002

删除新创建的xiaoliu库

mysql> drop database xiaoliu;

利用binlog日志恢复xiaoliu库

[root@localhost mysql]# mysqlbinlog --start-position 219 --stop-position 322 mysql-bin.000002 |mysql -uroot -p'Haitao@123'

mysqlbinlog --start-position 219 --stop-position 322 mysql-bin.000002 |mysql -uroot -p'Haitao@123'

登录数据库查看xiaoliu库是否恢复成功

Supxiaohan
关注
  • 2
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysqldump 命令 备份承 dump 格式 文件 mysqldump 备份表
u011250186的博客
01-01 330
mysqldump 命令 备份承 dump 格式 文件 mysqldump 备份表
【MySql】Mysql之备份与恢复
五彩斑斓的黑@的博客
10-05 6311
mysql的日志默认保存位置为：/usr/local/mysql/data 下面二进制日志由三种不同的记录格式①STTATEMENT（基于sql语句）每一条涉及到被修改的sql语句都会记录在binlog中缺点日志量过大，如sleep（）函数，last_insert_id()>（聚合函数）,以及user-definedfuctions(udf)、垂从复制等架构记录日志时会出现问题。
MySQL的备份及恢复
最新发布
weixin_68398469的博客
05-16 860
查看有哪些二进制日志文件：mysql> SHOW BINARY LOGS;查看当前正在使用的是哪一个二进制日志文件：mysql> SHOW MASTER STATUS;查看二进制日志内容：mysql> SHOW BINLOG EVENTS IN 'binlog.000005';
mysql常用备份与还原命令
weixin_45964650的博客
04-21 514
MySQL数据库备份还原
MySQL数据备份与还原
Lamb的博客
08-02 1834
mysqldump命令将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。mysqldump命令的工作原理很简单。它先查出需要备份的表的结构，再在文本文件中生成一个CREATE语句。然后，将表中的所有记录转换成一条INSERT语句。然后通过这些语句，就能够创建表并插入数据。mysqldump基本语法：使用root用户备份test数据库下的person表其生成的脚本如下：文件的开头会记录MySQL的版本、备份的主机名和数据库名。
MySQL 备份和还原
Sp_Tizzy的博客
07-17 3451
完全备份物理冷备份―关闭Mysqld 服务，tar命令打包 data目录，恢复就是解压tar包逻辑备份mysqldump -u-p--datebases 库1 库2 > xxx.sqlmysqldump -u -p 库1 表1 表2 >XXx.sql完全恢复mysql -u -p <xxx.sql 恢复库mysql-u -p 库名 < XXx.sql 恢复表增量备份1、开启二进制日志，设置二进制日志格式MIXED2、进行一次完全备份，可每周备份一次，通过crontab -e。
mydumper-0.15.2安装包，mysql数据备份和恢复
02-21
mydumper-0.15.2安装包，mysql数据备份和恢复
Linux下实现MySQL数据备份和恢复的命令使用全攻略
09-10
总的来说，Linux 环境下的 MySQL 数据备份和恢复是多维度的，需要综合考虑业务需求、系统性能和资源利用率。通过熟练掌握 Mysqldump、LVM 快照和 Xtrabackup，我们可以更有效地保护数据库的安全，降低因数据丢失带来...
MySQL数据备份和恢复（导出和导入）
07-02
MySQL数据备份和恢复（导出和导入）
mysql数据备份与恢复实现方法分析
09-08
本篇文章将详细探讨几种常见的MySQL数据备份与恢复方法，包括复制文件法、mysqldump工具以及select into outfile语句。 首先，复制文件法适用于MyISAM存储引擎的数据库。由于MyISAM引擎的表结构、数据和索引分别...
Mysql数据备份与恢复操作文档.md
12-17
Mysql数据备份与恢复操作文档.md
MySQL数据备份与恢复
weixin_46254171的博客
08-29 746
mysql的日志默认保存位置为：/usr/local/mysql/data 下面。
MySQL数据库备份与还原
qq_38254635的博客
02-11 9275
MySQL数据库备份
mysql数据库之备份和恢复
欲买桂花同载酒
08-08 4596
mysql数据库之备份和恢复，包含基础介绍，命令行讲解，脚本编写等一系列讲解，图文详细步骤等
java系统时间获取，存储，及获取时间戳，格式化显示
weixin_43519678的博客
01-05 237
//加载时间插件 <%@ taglib prefix=“fmt” uri=“http://java.sun.com/jsp/jstl/fmt” %> <jsp:useBean id=“dateObject” class=“java.util.Date” scope=“page”></jsp:useBean> //页面 显示时间 <jsp:setProperty property=“time” name=“dateObject” value="user.createt
mysql 不小心删库了，下面的方法能帮你找回来
zzmiaohao的博客
03-16 4857
之前习惯性用电脑删除键去删除数据库里的数据，有一天。。。库没了！！！（没有服务器快照，也没有数据库备份！！！） 然后就找专业的人，给我报价4张表=3w，你特么抢钱呢？！ 我数据库100多张，估计是看你着急，就加码。说到这里，大家慎重操作数据库，尽量别有权限，不是什么好事。 直接上干货： 通过mysqlbinlog ，得到TXT文件 ，用命令source 进去。 ...
31误删数据后除了跑路，还能怎么办？
sdaujsj1的博客
03-09 1070
文章目录31 | 误删数据后除了跑路，还能怎么办？误删行误删库 / 表延迟复制备库rm 删除数据小结上期问题时间误删行误删库 / 表延迟复制备库rm 删除数据小结上期问题时间 31 | 误删数据后除了跑路，还能怎么办？ 今天我要和你讨论的是一个沉重的话题：误删数据。 在前面几篇文章中，我们介绍了 MySQL 的高可用架构。当然，传统的高可用架构是不能预防误 删数据的，因为主库的一个 drop table 命令，会通过 binlog 传给所有从库和级联从库，进而导致整 个集群的实例都会执行这个命令。 虽然
windows，mysql导入还原数据命令，恢复sql文件数据方法
chuyang11的博客
03-24 1786
windows系统下mysql导入还原数据命令，恢复sql文件数据方法步骤
mysql表误删回复_mysql数据库数据删除怎么恢复
weixin_33420201的博客
01-14 3648
不小心把数据库删掉了,想要恢复数据，除了备份以外，还有以下方法。在mysql有时执行了错误的update或者delete时导致大量数据错误恢复的办法。执行时没有开启事务，也没有对数据进行。这时就需要使用到sqlbinlog工具。sqlbinlog需要开启，具体的打开方法就不说了使用sqlbinlog会产生bin文件，恢复就需要用到这些文件。文件中记录着数据库的所有操作。(此方法的操作是将数据库之前...
MYSQL数据备份与恢复
04-01
MySQL是一种常用的关系型数据库管理系统，它支持数据备份和恢复，这对于数据安全和数据恢复非常重要。以下是MySQL数据备份和恢复的步骤： 数据备份： 1.使用命令行工具或者图形化工具（如phpMyAdmin）登录到MySQL服务器。 2.选择要备份的数据库或者表。 3.使用MySQL的备份命令（mysqldump）将数据备份到本地磁盘或者远程服务器。例如，备份整个数据库的命令是： mysqldump -u 用户名 -p 数据库名 > 备份文件名.sql 4.在备份时，可以选择备份的选项，例如备份结构和数据，或者只备份数据等。 数据恢复： 1.同样使用命令行工具或者图形化工具登录到MySQL服务器。 2.创建一个新的数据库（如果需要）。 3.使用MySQL的恢复命令（mysql）将备份数据恢复到数据库中。例如，恢复整个数据库的命令是： mysql -u 用户名 -p 数据库名 < 备份文件名.sql 4.在恢复时，可以选择恢复的选项，例如只恢复数据或者结构和数据等。 注意事项： 1.备份和恢复时需要保证MySQL服务是运行的。 2.备份文件可以是压缩的，但是需要在恢复前解压缩。 3.备份和恢复时需要保证备份文件和恢复目标的MySQL版本是相同的。 4.备份和恢复时需要注意数据的完整性和一致性，以免数据丢失或者损坏。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值