开发者学堂课程【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 中还提供了可视化的操作方式，如下提供的备份与恢复：
在备份示例中选择快照备份就备份了当前的快照：
也可以备份 MySQL 数据，或者实例，这也是要开通相关的服务才能使用的，备份方式为物理备份和逻辑备份。还可以进行备份的设置，日志备份，本地日志设置，数据备份，在创建实例后会提示任务会在多少时间后进行启动，也可以实时查看备份任务进度，备份完后就会生成对应的实例在数据备份中，备份时是需要一些时间的，因为是基于整个实例的备份：
此时备份完成后进行刷新查看实例：
实例备份完成后直接点击恢复可以进行实例的恢复，这里可以申请另外一个 rds 实例进行还原，然后指定还原到对应实例就可以。
