我的编程空间,编程开发者的网络收藏夹
学习永远不晚

MySQL数据备份及还原(一)

短信预约 信息系统项目管理师 报名、考试、查分时间动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

MySQL数据备份及还原(一)

MySQL数据备份及还原(一)

关于删库跑路的事故现在已经屡见不鲜了,数据备份的必要性是企业数据管理极其重要的一项工作。关于数据备份、恢复也有很多场景及方法,本系列也会将主要的几种工具通过案例进行演示。

本系列将从逻辑备份及恢复开始讲起,逻辑备份的工具主要有mysqldump/mydumper等其中mydumper可以指定多线程工作,本文介绍的是mysqldump。

mysqldump是MySQL数据库自带的逻辑备份工具,属于热备工具。它的备份结果是根据设置的参数将数据库中的信息通过生成创建库、表等对象以及对应表的insert语句组成。

mysqldump 参数选项特别多,可以通过mysqldump --help 查看对应的参数及说明()

[root@testdb ~]# mysqldump --help
mysqldump  Ver 10.13 Distrib 5.7.25-28, for Linux (x86_64)
Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

mysqldump常用的选项如下

1.1  备份指定表

mysqldump可以备份指定的单个表或指定库的多个表,例如备份testdb库的test1表的表结构和数据


/usr/local/mysql5.7/bin/mysqldump -uroot -p  --socket=/tmp/mysql.sock   --master-data=2 --default-character-set=utf8   --single-transaction  testdb  test1 > test1.sql

备份后的文件主体部分如下:

 主要参数说明:

 --master-data=2          记录当前binlog信息,有1和2两个值,如果值等于1,就会添加一个CHANGE MASTER语句;如果值等于2,也会生成一个CHANGE MASTER,但是语句前添加注释,
                  如本例中所示(建议设置为2),此信息可以用作后期配置搭建主从同步用 --default-character-set=utf8 设置字符集,建议指明字符集 --single-transaction    配合RR隔离级别使用,用于保证innodb备份数据一致性,且不会锁表,此参数很重要,生产环境中一定要设置该参数 testdb            库名 test1             需要备份的表名,如果需要备份多张表 可以将表名直接罗列在后面 如 test1 test2

1.2.  备份单个数据库

mysqldump可以备份指定的数据库,可以是单个库也可以是多个库,先备份单个库,如下


 /usr/local/mysql5.7/bin/mysqldump -uroot -p  --socket=/tmp/mysql.sock   --master-data=2 --default-character-set=utf8   --single-transaction  testdb  > testdb.sql

结果如下,会将testdb库下的所有表的结构和数据都备份出来

1.3  备份多个库

备份多个数据库可以用如下命令


/usr/local/mysql5.7/bin/mysqldump -uroot -p  --socket=/tmp/mysql.sock   --master-data=2 --default-character-set=utf8   --single-transaction  --databases monitor testdb  > mul_db.sql

结果中主要信息如下,包含了创建库及切换库的内容

 参数说明(其他参数之前也说明,此处不再赘述):

--databases     后面填写需要备份的数据库名即可指定备份对应的库

1.4  备份所有的数据库

如果想备份所有的数据库,可以使用如下命令:

 
 /usr/local/mysql5.7/bin/mysqldump -uroot -p  --socket=/tmp/mysql.sock   --master-data=2 --default-character-set=utf8   --single-transaction  --all-databases   > all_db.sql

注: 备份中没有information_schema、performance_schema、sys库的信息(MySQL5.7及以上版本)

1.5  其他情况

实际使用中可能还会遇到只备份表结构、只备份数据,需要备份存储过程及事件等需求,相应参数如下:

--no-data        只备份表结构,不包含数据,可以简写为 -d
--no-create-info   只备份数据,不备份建表信息,也可以简写为-t
--routines        备份存储过程及函数,可以简写为 -R
--events         备份事件,可以简写为 -E
--triggers       备份触发器
--flush-logs      备份完成后切换日志
--flush-privileges  备份完成后刷新权限
--set-gtid-purged   开启了GTID的库需要设置该参数,值可以是ON, OFF 或 AUTO
--where           指定条件,例如每张表导出1000行的记录或者 导出每张表id<=10的记录等,可以参考历史文章查看示例
--skip-add-drop-table 不生成删除表的语句

1.6  示例脚本

备份全部数据库,包含触发器、事件、存储过程,同时刷新日志及权限的实例

 /usr/local/mysql5.7/bin/mysqldump -uroot -p  --socket=/tmp/mysql.sock   --master-data=2 --default-character-set=utf8  --routines   --triggers --events --flush-logs --flush-privileges --single-transaction  --all-databases >backup.sql

注意:备份中存在drop表的情况,如果确定需要重建表则保持默认,否则保险起见,添加--skip-add-drop-table 参数

1.7  说点特别的

mysqldump也可以生成平面文件,同时指定分隔符等,此情况比较简单,可以自行测试

1.8  使用场景

mysqldump属于逻辑备份,使用的主要场景如下:

  • 备份部分库或备份部分表
  • 数据规模较小的库
  • 有指定条件的备份等

 

模拟误删除testdb库的test1表中的一条记录

mysql> delete from test1 where name="孙权";
Query OK, 1 row affected (0.00 sec)

 

通过mysqldump备份的数据还原比较简单,将备份的文件导入数据库中即可。

3.1  恢复test1表

可以使用如下三种方式恢复:

a)  登录进数据库里用source 命令恢复


mysql> use rec;
Database changed

mysql> source test1.sql;

b)  使用 mysql -e 的方式恢复


[root@testdb ~]#   /usr/local/mysql5.7/bin/mysql --socket=/tmp/mysql.sock --port=3306 -uroot -p -e "use rec ; source  test1.sql;"
Enter password: 

c)  直接 MySQL  导入备份文件的方式

[root@testdb ~]#   /usr/local/mysql5.7/bin/mysql --socket=/tmp/mysql.sock --port=3306 -uroot -p   rec  < test1.sql
Enter password: 

注:

a) 还原命令使用起来比较方便,但是实际生产环境中还原数据时不建议直接还原至目标表里(尤其处理误删除恢复数据时),而是建议先还原至其他实例或其他库里,确认无误后再将需要还原的记录导入至目标表里;

b) 要警惕备份文件中是否有删除库或删表的指令,否则如果选择在同一实例中还原即使选择了临时恢复的库,而备份文件里有use db;及drop table的语句,则会将目标表全部删掉。

3.2 恢复误删除的记录至目标表

mysql> insert into  testdb.test1 select * from rec.test1 where  name="孙权";
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

此时记录恢复至数据库中了。

通过查看备份文件可以看到大致的流程,也可以通过开启general log的方式看具体都执行哪些SQL。

4.1  查看general log  

先查看配置

 然后开启genera-log

4.2  开始备份

我们就用之前的推荐备份脚本进行备份

/usr/local/mysql5.7/bin/mysqldump -uroot -p  --socket=/tmp/mysql.sock   --master-data=2 --default-character-set=utf8  --routines   --triggers --events --flush-logs --flush-privileges --single-transaction  --all-databases   > all_db.sql

4.3  关闭general-log

因开启general-log对数据库性能影响大(表现特别明显),因此测试完毕后关闭general log。勿忘,切记。

4.4 查看general-log内容

 后面开始备份具体库的内容

备份其他库的内容与此相同,省略。

以上内容,如需转载,请注明出处,谢谢)

 

使用mysqldump工具备份及恢复数据至此就基本介绍完毕了,如果补充或想获取原图的请联系我(关注公众号: 数据库干货铺)。

 

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

MySQL数据备份及还原(一)

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

MySQL数据备份及还原(一)

关于删库跑路的事故现在已经屡见不鲜了,数据备份的必要性是企业数据管理极其重要的一项工作。关于数据备份、恢复也有很多场景及方法,本系列也会将主要的几种工具通过案例进行演示。本系列将从逻辑备份及恢复开始讲起,逻辑备份的工具主要有mysqldump/mydumper
MySQL数据备份及还原(一)
2021-08-19

mysql数据库备份和还原

备份MySQL数据库的命令mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql 备份MySQL数据库为带删除表的格式 备份MySQL数据库为带删除表
2022-05-27

C#怎么备份及还原数据库

本篇内容介绍了“C#怎么备份及还原数据库”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!C#数据库备份及还原1.在用户的配置时,我们需要列出当
2023-06-18

mysql如何备份和还原数据库

MySQL数据库的备份和还原可以通过命令行或者图形界面工具来完成。备份数据库:使用命令行工具:可以使用mysqldump命令来备份数据库。例如,备份名为mydatabase的数据库可以使用以下命令:mysqldump -u usernam
mysql如何备份和还原数据库
2024-04-09

MySQL如何从备份表还原数据

要从备份表还原数据,可以执行以下步骤:连接到MySQL数据库。可以使用命令行工具或者MySQL客户端连接到数据库。创建一个新的表,用于存储从备份表中还原的数据。可以使用以下命令创建新表:CREATE TABLE new_table LIKE
MySQL如何从备份表还原数据
2024-04-16

【数据库】数据库备份与还原 Navicat Premium备份以及定时备份设置

✨数据库备份数据库作为信息系统的核心担当着重要的角色。尤其在一些对数据可靠性要求很高的行业如银行、证券、电信等,如果发生意外停机或数据丢失其损失会十分惨重。为此数据库管理员应针对具体的业务要求制定详细的数据库备份与灾难恢复策略,并通过模拟故障对每种可能的情况进
【数据库】数据库备份与还原 Navicat Premium备份以及定时备份设置
2014-12-22

编程热搜

目录