MySQL的备份与恢复是数据库的基本操作之一，此文对此做一个详细的总结。
一，MySQL登录的介绍（备份与恢复的前提条件是登录到MySQL服务器，因此，是应该介绍的）
MySQL登录方式有如下几种方式：
（1），命令行（Windows下叫cmd）登录
MySQL命令行登录方式，主要是mysql -hip -P端口 -uroot -p 这里需要注意如下：
如果是默认的3306端口，-P可以省略，非3306端口必须指定。
如果是本地登录，-h参数也可以省略。非本地登录，必须指定登录ip。
-p （-小p）这个参数是指定密码的，通常不建议把密码跟在参数后面，为了服务器的安全。
参数后可以加空格也可以不加空格，通常为了美观大方，是不建议加空格的。比如，下面这个示例，我是登录到192.168.0.17服务器上的MySQL服务。-u root 这里是有空格的，但也可以不加空格
[root@slave1 ~]# mysql -h192.168.0.17 -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.23-log MySQL Community Server (GPL)
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> ^DBye
（2），数据库图形化管理工具（navicat，sqlyog，MySQL Workbench，MySQL ODBC Connector，phpMyAdmin等等工具）
图形化管理工具通常都是安装在Windows系统下，只是因为Linux一般没有安装图形化界面，而MySQL通常是安装在Linux系统下的，因此，MySQL数据库必须也应该能够提供远程访问的。比如，navicat的远程访问：
（3），MySQL的客户端 （Linux下一般是不需要安装MySQL客户端的，命令行就够用了，这里指的是Windows系统的MySQL客户端）
二， SQL文件的种类
很多同学可能还有疑问，SQL文件还有种类吗？是的，有mysqldump程序备份出来的SQL文件，也有单独建库建表的SQL文件，比如下面的dump文件：
-- MySQL dump 10.13 Distrib 5.7.23, for linux-glibc2.12 (x86_64)
-- Host: 192.168.0.17 Database: emp
-- ------------------------------------------------------
-- Server version 5.7.23-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
。。。。。。。。略略略
这样的SQL脚本建表SQL文件；
create table if not exists DEPT
DEPTNO int(2) not null,
DNAME varchar(14),
LOC varchar(13)
alter table DEPT add constraint PK_DEPT primary key (DEPTNO);
create table if not exists EMP
EMPNO int(4) not null,
ENAME varchar(10),
JOB varchar(9),
MGR int(4),
HIREDATE date,
SAL int(7 ),
COMM int(7 ),
DEPTNO int(2)
alter table EMP add constraint PK_EMP primary key (EMPNO);
alter table EMP add constraint FK_DEPTNO foreign key (DEPTNO) references DEPT (DEPTNO);
insert into DEPT (DEPTNO, DNAME, LOC) values (10, 'ACCOUNTING', 'NEW YORK');
insert into DEPT (DEPTNO, DNAME, LOC) values (20, 'RESEARCH', 'DALLAS');
insert into DEPT (DEPTNO, DNAME, LOC) values (30, 'SALES', 'CHICAGO');
insert into DEPT (DEPTNO, DNAME, LOC) values (40, 'OPERATIONS', 'BOSTON');
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7369, 'SMITH', 'CLERK', 7902, str_to_date('17 12 1980', '%d %m %Y'), 800,null,20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7499, 'ALLEN', 'SALESMAN', 7698, str_to_date('20 02 1981', '%d %m %Y'),1600, 300, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7521, 'WARD', 'SALESMAN', 7698, str_to_date('22 02 1981', '%d %m %Y'),1250, 500, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7566, 'JONES', 'MANAGER', 7839, str_to_date('02 04 1981', '%d %m %Y'),2975, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7654, 'MARTIN', 'SALESMAN', 7698, str_to_date('28 09 1981', '%d %m %Y'),1250, 1400, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7698, 'BLAKE', 'MANAGER', 7839, str_to_date('01 05 1981', '%d %m %Y'),2850, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7782, 'CLARK', 'MANAGER', 7839, str_to_date('09 06 1981', '%d %m %Y'),2450, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7788, 'SCOTT', 'ANALYST', 7566, str_to_date('19 04 1987', '%d %m %Y'),3000,null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7839, 'KING', 'PRESIDENT', null, str_to_date('17 11 1981', '%d %m %Y'),5000,null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7844, 'TURNER', 'SALESMAN', 7698, str_to_date('08 09 1981', '%d %m %Y'),1500, 0, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7876, 'ADAMS', 'CLERK', 7788, str_to_date('23 05 1987', '%d %m %Y'),1100,null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7900, 'JAMES', 'CLERK', 7698, str_to_date('03 12 1981', '%d %m %Y'),950,null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7902, 'FORD', 'ANALYST', 7566, str_to_date('03 12 1981', '%d %m %Y'),3000,null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7934, 'MILLER', 'CLERK', 7782, str_to_date('23 01 1982', '%d %m %Y'),1300,null, 10);
create table if not exists salgrade (
grade numeric primary key,
losal numeric,
hisal numeric
insert into salgrade values (1, 700, 1200);
insert into salgrade values (2, 1201, 1400);
insert into salgrade values (3, 1401, 2000);
insert into salgrade values (4, 2001, 3000);
insert into salgrade values (5, 3001, 9999);
三，mysqldump备份
MySQLdump备份仅仅出于安全需要验证一次密码root密码，此命令通过不同的参数组合指定需要的备份范围。
详细的主要参数的含义如下：
-A --all-databases：导出全部数据库
2 -Y --all-tablespaces：导出全部表空间
3 -y --no-tablespaces：不导出任何表空间信息
4 --add-drop-database每个数据库创建之前添加drop数据库语句。
5 --add-drop-table每个数据表创建之前添加drop数据表语句。(默认为打开状态，使用--skip-add-drop-table取消选项)
6 --add-locks在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(默认为打开状态，使用--skip-add-locks取消选项)
7 --comments附加注释信息。默认为打开，可以用--skip-comments取消
8 --compact导出更少的输出信息(用于调试)。去掉注释和头尾等结构。可以使用选项：--skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys
9 -c --complete-insert：使用完整的insert语句(包含列名称)。这么做能提高插入效率，但是可能会受到max_allowed_packet参数的影响而导致插入失败。
10 -C --compress：在客户端和服务器之间启用压缩传递所有信息
11 -B--databases：导出几个数据库。参数后面所有名字参量都被看作数据库名。
12 --debug输出debug信息，用于调试。默认值为：d:t:o,/tmp/
13 --debug-info输出调试信息并退出
14 --default-character-set设置默认字符集，默认值为utf8
15 --delayed-insert采用延时插入方式（INSERT DELAYED）导出数据
16 -E--events：导出事件。
17 --master-data：在备份文件中写入备份时的binlog文件，在恢复进，增量数据从这个文件之后的日志开始恢复。值为1时，binlog文件名和位置没有注释，为2时，则在备份文件中将binlog的文件名和位置进行注释
18 --flush-logs开始导出之前刷新日志。请注意：假如一次导出多个数据库(使用选项--databases或者--all-databases)，将会逐个数据库刷新日志。除使用--lock-all-tables或者--master-data外。在这种情况下，日志将会被刷新一次，相应的所以表同时被锁定。因此，如果打算同时导出和刷新日志应该使用--lock-all-tables 或者--master-data 和--flush-logs。
19 --flush-privileges在导出mysql数据库之后，发出一条FLUSH PRIVILEGES 语句。为了正确恢复，该选项应该用于导出mysql数据库和依赖mysql数据库数据的任何时候。
20 --force在导出过程中忽略出现的SQL错误。
21 -h --host：需要导出的主机信息
22 --ignore-table不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名。例如：--ignore-table=database.table1 --ignore-table=database.table2 ……
23 -x --lock-all-tables：提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭--single-transaction 和--lock-tables 选项。
24 -l --lock-tables：开始导出前，锁定所有表。用READ LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB，--single-transaction是一个更好的选择，因为它根本不需要锁定表。请注意当导出多个数据库时，--lock-tables分别为每个数据库锁定表。因此，该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。
25 --single-transaction：适合innodb事务数据库的备份。保证备份的一致性，原理是设定本次会话的隔离级别为Repeatable read，来保证本次会话（也就是dump）时，不会看到其它会话已经提交了的数据。
26 -F：刷新binlog，如果binlog打开了，-F参数会在备份时自动刷新binlog进行切换。
27 -n --no-create-db：只导出数据，而不添加CREATE DATABASE 语句。
28 -t --no-create-info：只导出数据，而不添加CREATE TABLE 语句。
29 -d --no-data：不导出任何数据，只导出数据库表结构。
30 -p --password：连接数据库密码
31 -P --port：连接数据库端口号
32 -u --user：指定连接的用户名。
举例使用：
a、导出整个数据库(包括数据库中的数据）
mysqldump -u username -p dbname > dbname.sql
b、导出数据库结构（不含数据）
mysqldump -u username -p -d dbname > dbname.sql
c、导出数据库中的某张数据表（包含数据）
mysqldump -u username -p dbname tablename > tablename.sql
d、导出数据库中的某张数据表的表结构（不含数据）
mysqldump -u username -p -d dbname tablename > tablename.sql
比如，全库备份命令为；
[root@slave1 ~]# mysqldump -uroot -p -h192.168.0.17 -A >alll.sql
Enter password:
输入正确的密码就开始备份数据库了，凡是备份数据库的时候都会锁表锁库，这里需要注意哦。
[root@slave1 ~]# ls -alh all.sql
-rw-r--r-- 1 root root 793K Feb 13 16:54 all.sql
备份指定的数据库比如，我在192.168.0.17这个服务器上的数据库有test数据库，我想要备份test和mysql这两个数据库，那么，命令应该如下：
[root@slave1 ~]# mysqldump -uroot -p -h192.168.0.17 test EMP DEPT >empdept.sql
Enter password:
此时不能加-B参数，第一个参数只写数据库名称，然后后面跟的是你要备份的表名称，注意，表名称是区分大小写的
备份出的文件内容如下：
[root@slave1 ~]# cat empdept.sql
-- MySQL dump 10.13 Distrib 5.7.23, for linux-glibc2.12 (x86_64)
-- Host: 192.168.0.17 Database: test
-- ------------------------------------------------------
-- Server version 5.7.23-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
-- Table structure for table `EMP`
DROP TABLE IF EXISTS `EMP`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `EMP` (
`EMPNO` int(4) NOT NULL,
`ENAME` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
`JOB` varchar(9) COLLATE utf8_unicode_ci DEFAULT NULL,
`MGR` int(4) DEFAULT NULL,
`HIREDATE` date DEFAULT NULL,
`SAL` int(7) DEFAULT NULL,
`COMM` int(7) DEFAULT NULL,
`DEPTNO` int(2) DEFAULT NULL,
PRIMARY KEY (`EMPNO`),
KEY `FK_DEPTNO` (`DEPTNO`),
CONSTRAINT `FK_DEPTNO` FOREIGN KEY (`DEPTNO`) REFERENCES `DEPT` (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
-- Dumping data for table `EMP`
LOCK TABLES `EMP` WRITE;
/*!40000 ALTER TABLE `EMP` DISABLE KEYS */;
INSERT INTO `EMP` VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20),(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30),(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30),(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20),(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30),(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30),(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10),(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20),(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10),(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30),(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20),(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30),(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20),(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
/*!40000 ALTER TABLE `EMP` ENABLE KEYS */;
UNLOCK TABLES;
-- Table structure for table `DEPT`
DROP TABLE IF EXISTS `DEPT`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `DEPT` (
`DEPTNO` int(2) NOT NULL,
`DNAME` varchar(14) COLLATE utf8_unicode_ci DEFAULT NULL,
`LOC` varchar(13) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
-- Dumping data for table `DEPT`
LOCK TABLES `DEPT` WRITE;
/*!40000 ALTER TABLE `DEPT` DISABLE KEYS */;
INSERT INTO `DEPT` VALUES (10,'ACCOUNTING','NEW YORK'),(20,'RESEARCH','DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON');
/*!40000 ALTER TABLE `DEPT` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2022-02-15 21:36:30
此时，我们应该可以看到，默认dump备份的时候是会锁表的哦。----LOCK TABLES `DEPT` WRITE;
恢复就比较简单了，
mysql -uroot -p <备份的文件
输入正确的密码，即可恢复啦，不过最好在恢复前看一下有没有建库建表语句，如果没有，请自己添加上去。
MySQLdump备份操作的时候，加了-B参数后，备份文件中多了Create database和use database的命令
加-B参数的好处：
加上-B参数后，导出的数据文件中已存在创建库和使用库的语句，不需要手动在原库是创建库的操作，在恢复过程中不需要手动建库，可以直接还原恢复
--compact：去掉备份文件中的注释，适合调试，生产场景不适用
-A：备份所有库---这个建议磁盘空间比较充足的时候做这件事情
-F：刷新binlog日志
--master-data：在备份文件中增加binlog日志文件名及对应的位置点
-x --lock-all-tables：锁表
-l：只读锁表---保持事务的一致性
-d：只备份表结构---这个一般是不用的
-t：只备份数据---备份出的文件里只有插入语句
--single-transaction：适合innodb事务数据库的备份
InnoDB表在备份时，通常启用选项--single-transaction来保证备份的一致性，原理是设定本次会话的隔离级别为Repeatable read，来保证本次会话（也就是dump）时，不会看到其它会话已经提交了的数据。
-d参数，只备份表结构
mysqldump -uroot -p'123456' -d mytest stusent > /mnt/studentDesc_bak.sql
-t参数，只备份数据
mysqldump -uroot -p'123456' -t mytest stusent > /mnt/studentData_bak.sql
