MySQL 下全量备份与还原|学习笔记

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 快速学习 MySQL 下全量备份与还原

开发者学堂课程【MySQL 实操课程:MySQL 下全量备份与还原】学习笔记,与课程紧密联系,让用户快速学习知识。  

课程地址:https://developer.aliyun.com/learning/course/717/detail/12826


MySQL 下全量备份与还原

 

内容介绍

一、MySQL 全量备份

二、MySQL 全量还原

三、可视化操作备份

 

这节课进行讲解使用锁定机制备份表的基本步骤,上节课进行讲解的物理备份还原,这节课进行讲解逻辑备份还原。

 

一、MySQL 全量备份

1、使用锁定机制备份表的基本步骤

(1)使用 LOCK TABLES 命令锁定某表或多个表这里的锁表就是防止在备份期间,存在数据写入的请求,此时是不能写入的,只有等备份完成后进行解锁,才能完成数据的写入请求。

(2)拷贝对应的文件

(3)使用 UNLOCK TABLES 解锁已拷贝完的表。

2、mysqldump

对于逻辑请求该如何操作可以运用 mysqldump 命令

(1)Mysqldump 可以在 MySQL 安装目录中找到 , 对于 mysqldump 在前面讲过,他的缺点是储存效率比较低,备份比较浪费时间,但是他的优点就是通用性比较强,因为它保存的是 SQL 语句,而 sql 语句,只要不包含一些个性化的语句或者mysql 特有的语法,是可以导入到其他数据库的,就是进行数据库的切换。该程序用于转储数据库或搜集数据库进行备份或将数据转移到另个支持 SQL 语句的

服务器(不一定是一个 MySQL 服务器)备份后产生的备份文件是一个文本文件 ,文件内容为创建表和往表中插入数据的 SQL 语句。

(2)备份库: #mysqldump -h10.241.12.120 -uroot -p database_name>/var/log/database_ name.bak.20110908.sql

:-h 后为备份服务器的 ip,如果是备份本机的话就不需要加 ip,然后加上用户名密码和参数 database_name,再导出到指定目录下。

(3)备份表 :#mysqldump -h10.241.12.120 -uroot -p database_name-talbe_name> var/log/database_name-talbe_name.bak.20110908.sql:备份表就是在 database 后加上表名,下面可以看一个备份 rds 的实战案例,在备份 rds 之前先演示本地的备份。

3、mysqldump 备份实战

(1)备份 itlead 数据库下的表 user

(2)备份数据库 itlead

(3)备份所有库。

4、案例演示

备份 rds 前先进行本地的备份,还是首先打开 Shell 窗口,再在 bin 目录下找到mysqldump 文件:

[root@iZwz9bize6nk8hug8jOvywZ aliyun]# cd /usr/local/mysql/bin

[root@iZwz9bize6nk8hug8jOvywZ bin]# ll

-rwxr-xr-x mysql mysql 9485037 Jun 2 20:56 mysqldump

cd 到 mysql 创建文件夹backup,然后使用 mysqldump 命令,使用前先备份aliyun 数据库,因为是本地所以不加-h参数,指定 root 用户,数据库名为 aliyun,导到 /data/mysql/backup/aliyun.sql:

[root@iZwz9bize6nk8hug8jOvywZ mysql]# /usr/local/mysql/bin/mysqldump -uroot -p aliyun > /data/mysql/backup/aliyun. sql

可以再 aliyun 下打开文件进行查看,

[root@iZwz9bize6nk8hug8jOvywZ mysql]# cd /data/mysql/backup/

[root@iZwz9bize6nk8hug8jOvywZ backup]# ll

[root@iZwz9bize6nk8hug8jOvywZ backup]# vi aliyun.sql

DROP TABLE IF EXISTS ‘book’ //如果存在表则删除

CREATE TABLE ‘book’{  //删除后再执行创建表语句

}

LOCK TABLES ‘book’ WRITE; //再写入前进行锁表,设置为可读不可写

INSERT INTO ‘book’ VALUES(’…’); //建表后往表中写入数据

UNLOCK TABLES; //然后进行解表

打开文件后可以看到文件中是很多注释和建表语句,这就是表的执行过程,就是把表放进去重新执行一遍。

如果将 mysql 表删除:

mysql> show tables;

| book       |

|company    |

|dept        |

|emp        |

|school      |

|school1      |

|school_index |

|user         |

|user_bak    |

mysql> drop table school;

mysql> drop table school1;

mysql> delete from user;

mysql> delete * from user;

mysql> show tables;

| book        |

|company    |

|dept         |

|emp         |

|school_index |

|user         |

|user_bak    |

 

二、MySQL 全量还原

1、远程还原,本地还原(不用加-h)

2、还原库

: mysq> mysql -h10.241.12.120 -uroot -p database_name < /var/log/database_ name.bak.20110908.sql

然后下面进行还原,需要使用 mysql 指令,首先进入 mysql 将 aliyun. sql 文件导入到 aliyun:

[root@iZwz9bize6nk8hug8jOvywZ ~]# /usr/local/mysql/bin/mysql -uroot -p aliyun < /data/mysql/backup/aliyun. sql

执行后查看是否执行成功:

mysql> show tables;

| book       |

|company    |

|dept         |

|emp         |

|school       |

|school1      |

|school_index |

|user         |

|user_bak    |

mysql> select * from school;

|  id | name        |

|1001 | yizhong     |

|1002 | erzhong     |

|1003 | sanzhong   |

说明执行成功。这就是针对数据库物理的还原,也可针对逻辑进行还原,

3、还原表

:mysql -h10.241.12.120 -uroot -p database_name < /var/log/database_name-talbe_name.bak.20110908. sql

表的还原也是通过 mysqldump 语句,然后指定数据库下面的哪一个表。这里演示备份 aliyun 数据库下面的 school 表,首先使用 mysqldump,-p 参数后跟上数据库名,然后是表名,导出到 /data/mysql/backup/aliyun_school.sql

[root@iZwz9bize6nk8hug8jOvywZ ~]# /usr/local/mysql/bin/mysql -uroot -p aliyun school > /data/mysql/backup/aliyun. sql

如果表 school 不小心删除了:

mysql> delect from school;

mysql> select * from school;

此时查看 school 表是没有的,接着就可以执行还原,还原库和表都可以,这里还原表:

[root@iZwz9bize6nk8hug8jOvywZ ~]# /usr/local/mysql/bin/mysql -uroot -p aliyun < /data/mysql/backup/aliyun_school.sql

然后进行查询:

mysql> select * from school;

|  id | name         |

|1001 | yizhong      |

|1002 | erzhong      |

|1003 | sanzhong     |

可以查看到表的数据,说明还原成功。这里注意导出这个表的文件的时候,是指定了库名加表名的,还原时就可以不用指定库名加表名,只需要指定库名就可以了,基于全量备份与还原在自由的主机下面通过 ecs 去安装 mysql,下面进行讲解锁表的操作,比如在这期间还原了数据后还是有可能丢失数据,而此时查询数据为3条,如果再写入数据:

mysql> insert into school values(1004,’sizhong’);

mysql> select * from school;

|  id | name        |

|1001 | yizhong     |

|1002 | erzhong     |

|1003 | sanzhong    |

|1004 | sizhong      |

此时数据为4条,然后再进行还原的话数据只能看见3条:

[root@iZwz9bize6nk8hug8jOvywZ ~]# /usr/local/mysql/bin/mysql -uroot -p aliyun < /data/mysql/backup/aliyun_school.sql

mysql> select * from school;

|  id | name         |

|1001 | yizhong      |

|1002 | erzhong      |

|1003 | sanzhong    |

就是在备份期间写入的数据肯定是没办法还原的,所以此时可以 lock 锁定表school,进行备份后再写入数据:

mysql> lock table school write;

mysql> insert into school values(1004,’sizhong’);

这时是可以写入数据的,因为在当前的操作线程中,但是如果是在以下中进行写入数据的会被阻塞的:

[root@iZwz9bize6nk8hug8jOvywZ ~]# /usr/local/mysql/bin/mysql -uroot -p

mysql> use itlead;

mysql> use aliyun;

mysql> show tables;

mysql> insert into school values(1005,’wuzhong’);

一般是不会进行锁表后再写入数据的,但是在其他的客户端可能会遇到其他线程进行写入的工作,这个时候就可以通过锁表让其他线程无法进行写数据。

此时一解锁后就会自动执行写入数据操作:

mysql> unlock tables;

mysql> select * from school:

|  id | name        |

|1001 | yizhong     |

|1002 | erzhong      |

|1003 | sanzhong    |

|1004 | sizhong      |

|1005 | wuzhong     |

这里需要注意为了防止在备份期间不会有新的数据写入表,这就是全量备份与还原。

下面讲解 rds for mysql 如何进行数据库的备份与还原,首先进行 rds 服务的连接,这里可以远程客户端进行访问内网地址,这里举例为 aliyun 测试数据库,打开库中的 user 表可以看到5条数据:

[root@iZwz9bize6nk8hug8jOvywZ ~]# /usr/local/mysql/bin/mysql -hrm-wz9c2e2q42u426f3z.mysql.rds.aliyuncs.com -uroot -p

mysql> show databases;

mysql> use aliyun;

mysql> show tables;

mysql> select * from school;

|  id | name        |

|   1 | zhangsan    |

|   2 | lisi           |

|   3 | wangwu     |

|   4 | zhaoliu      |

|   5 | sunqi        |

现在要备份表也是通过 mysqldump,要加上 -h 参数因为是属于远程的备份,然后再加上域名 rds for mysql,然后是用户名,密码,因为这里默认的做了一个配置的参数,所以这里要加上 --single-transaction,将 aliyun user 文件导入到然后回车输入密码:

[rooteiZwz9bize6nk8hug8j0vywZ ~]# /usr/local/mysql/bin/mysqldump -hrm- wz9c2e2q42u426f3z.mysql.rds.aliyuncs.com -uroot -p --single-transaction=set-gtid-purged OFF aliyun user > /data/mysql/aliyun_user.sql

然后进行查看可以看到 aliyun 文件与之前是一样的:

[root@iZwz9bize6nk8hug8jOvywZ ~]# cd /data/mysql

[root@iZwz9bize6nk8hug8jOvywZ mysql]# ll

[root@iZwz9bize6nk8hug8jOvywZ mysql]# vi aliyun_user.sql

紧接着删除 user 文件后,可以 mysql 进行还原,与导出相比就少加了两个参数,直接用 mysql -h 加上域名 -u,指向文件就可以了:

mysql> select * from user;

[rooteiZwz9bize6nk8hug8j0vywZ mysql]# /usr/local/mysql/bin/mysql -hrm wz9c2e2q42u426f3z . mysql. rds . aliyuncs .com -uroot -p aliyun < /data/ mysql/aliyun_user. sql

回车执行后进行查看是否执行成功:

mysql> select * from school;

|  id | name        |

|   1 | zhangsan    |

|   2 | lisi           |

|   3 | wangwu     |

|   4 | zhaoliu      |

|   5 | sunqi        |

说明还原成功,这就是基于 rds for mysql 进行还原的用法

 

三、可视化操作备份

当然 rds 中还提供了可视化的操作方式,如下提供的备份与恢复

图片1.png

在备份示例中选择快照备份就备份了当前的快照:

图片2.png

也可以备份 MySQL 数据,或者实例,这也是要开通相关的服务才能使用的,备份方式为物理备份和逻辑备份。还可以进行备份的设置,日志备份,本地日志设置,数据备份,在创建实例后会提示任务会在多少时间后进行启动,也可以实时查看备份任务进度,备份完后就会生成对应的实例在数据备份中,备份时是需要一些时间的,因为是基于整个实例的备份:

图片3.png

此时备份完成后进行刷新查看实例:

图片4.png

实例备份完成后直接点击恢复可以进行实例的恢复,这里可以申请另外一个 rds 实例进行还原,然后指定还原到对应实例就可以。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2月前
|
存储 关系型数据库 MySQL
mysql数据库备份与恢复
MySQL数据库的备份与恢复是确保数据安全性和业务连续性的关键操作。
77 4
|
1月前
|
存储 SQL 关系型数据库
Mysql学习笔记(二):数据库命令行代码总结
这篇文章是关于MySQL数据库命令行操作的总结,包括登录、退出、查看时间与版本、数据库和数据表的基本操作(如创建、删除、查看)、数据的增删改查等。它还涉及了如何通过SQL语句进行条件查询、模糊查询、范围查询和限制查询,以及如何进行表结构的修改。这些内容对于初学者来说非常实用,是学习MySQL数据库管理的基础。
128 6
|
10天前
|
关系型数据库 MySQL Linux
Linux环境下MySQL数据库自动定时备份实践
数据库备份是确保数据安全的重要措施。在Linux环境下,实现MySQL数据库的自动定时备份可以通过多种方式完成。本文将介绍如何使用`cron`定时任务和`mysqldump`工具来实现MySQL数据库的每日自动备份。
28 3
|
10天前
|
监控 关系型数据库 MySQL
Linux环境下MySQL数据库自动定时备份策略
在Linux环境下,MySQL数据库的自动定时备份是确保数据安全和可靠性的重要措施。通过设置定时任务,我们可以每天自动执行数据库备份,从而减少人为错误和提高数据恢复的效率。本文将详细介绍如何在Linux下实现MySQL数据库的自动定时备份。
24 3
|
1月前
|
SQL 关系型数据库 MySQL
Mysql学习笔记(三):fetchone(), fetchmany(), fetchall()详细总结
MySQL中用于数据检索的`fetchone()`, `fetchmany()`, `fetchall()`函数的功能、SQL语句示例和应用场景。
54 3
Mysql学习笔记(三):fetchone(), fetchmany(), fetchall()详细总结
|
1月前
|
SQL Ubuntu 关系型数据库
Mysql学习笔记(一):数据库详细介绍以及Navicat简单使用
本文为MySQL学习笔记,介绍了数据库的基本概念,包括行、列、主键等,并解释了C/S和B/S架构以及SQL语言的分类。接着,指导如何在Windows和Ubuntu系统上安装MySQL,并提供了启动、停止和重启服务的命令。文章还涵盖了Navicat的使用,包括安装、登录和新建表格等步骤。最后,介绍了MySQL中的数据类型和字段约束,如主键、外键、非空和唯一等。
70 3
Mysql学习笔记(一):数据库详细介绍以及Navicat简单使用
|
1月前
|
关系型数据库 MySQL 数据库
Mysql学习笔记(四):Python与Mysql交互--实现增删改查
如何使用Python与MySQL数据库进行交互,实现增删改查等基本操作的教程。
61 1
|
1月前
|
安全 关系型数据库 MySQL
MySQL用户备份
【10月更文挑战第2天】MySQL用户备份
53 3
|
1月前
|
存储 关系型数据库 MySQL
MySQL如何备份?
【10月更文挑战第2天】MySQL如何备份?
95 3
|
3月前
|
存储 关系型数据库 MySQL
MySQL备份与恢复
MySQL备份与恢复
59 0