Mysql进阶（查询、备份、恢复）

一、多表联合查询

在关系型数据库中，表与表之间是有联系的，所以在实际应用中，经常使用多表查询。多表查询就是同时查询两个或两个以上的表。
在 MySQL 中，多表查询主要有交叉连接、内连接、外连接、分组查询与子查询等5种。

1、交叉连接(CROSS JOIN)

笛卡尔积

交叉连接(CROSS JOIN)：有两种，显式的和隐式的2种，一般用来返回连接表的笛卡尔积。
笛卡尔积（Cartesian product）是指两个集合 X 和 Y 的乘积。
例如，有 A 和 B 两个集合，它们的值如下：
A = {1,2}B = {3,4,5}
集合 A×B 和 B×A 的结果集分别表示为：
A×B={(1,3), (1,4), (1,5), (2,3), (2,4), (2,5) };
B×A={(3,1), (3,2), (4,1), (4,2), (5,1), (5,2) };
以上 A×B 和 B×A 的结果就叫做两个集合的笛卡尔积。
并且，从以上结果我们可以看出：
两个集合相乘，不满足交换率，即 A×B≠B×A。
A 集合和 B 集合的笛卡尔积是 A 集合的元素个数 × B 集合的元素个数。
多表查询遵循的算法就是以上提到的笛卡尔积，表与表之间的连接可以看成是在做乘法运算。在实际应用中，应避免使用笛卡尔积，因为笛卡尔积中容易存在大量的不合理数据，简单来说就是容易导致查询结果重复、混乱。

交叉连接语法

交叉连接的语法格式如下：
SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句];
或
SELECT <字段名> FROM <表1>, <表2> [WHERE子句];
语法说明如下：
字段名：需要查询的字段名称。
<表1><表2>：需要交叉连接的表名。
WHERE 子句：用来设置交叉连接的查询条件

注：多个表交叉连接时，在 FROM 后连续使用 CROSS JOIN 或,即可。以上两种语法的返回结果是相同的，但是第一种语法才是官方建议的标准写法。
当连接的表之间没有关系时，我们会省略掉 WHERE 子句，这时返回结果就是两个表的笛卡尔积，返回结果数量就是两个表的数据行相乘。需要注意的是，如果每个表有 1000 行，那么返回结果的数量就有 1000×1000 = 1000000 行，数据量是非常巨大的。

//先做好以下两表用于实验

mysql> show tables;
+--------------+
| Tables_in_sq |
+--------------+
| students     |
| tb_course    |
+--------------+
5 rows in set (0.00 sec)


mysql> select * from students;
+----+--------+------+-------+--------+-----------+
| id | name   | age  | sex   | height | course_id |
+----+--------+------+-------+--------+-----------+
|  1 | Dany   |   25 | man   |    160 |         1 |
|  2 | Green  |   23 | man   |    158 |         2 |
|  3 | Henry  |   23 | woman |    185 |         1 |
|  4 | jane   |   22 | man   |    162 |         3 |
|  5 | jim    |   24 | woman |    175 |         2 |
|  6 | john   |   21 | woman |    172 |         4 |
|  7 | Lily   |   22 | man   |    188 |         4 |
|  8 | Susan  |   22 | man   |    179 |         5 |
|  9 | Thomas |   22 | woman |    158 |         5 |
| 10 | Tom    |   23 | woman |    165 |         5 |
+----+--------+------+-------+--------+-----------+
10 rows in set (0.00 sec)

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
+----+-------------+
5 rows in set (0.00 sec)

两张表交叉查询

无条件查询

//出现了五十条记录，得出的结果特别的多，没有意义，所以一般会在后续添加一个条件

mysql> select * from tb_course cross join students;
+----+-------------+----+--------+------+-------+--------+-----------+
| id | course_name | id | name   | age  | sex   | height | course_id |
+----+-------------+----+--------+------+-------+--------+-----------+
|  1 | Java        |  1 | Dany   |   25 | man   |    160 |         1 |
|  2 | MySQL       |  1 | Dany   |   25 | man   |    160 |         1 |
|  3 | Python      |  1 | Dany   |   25 | man   |    160 |         1 |
|  4 | Go          |  1 | Dany   |   25 | man   |    160 |         1 |
|  5 | C++         |  1 | Dany   |   25 | man   |    160 |         1 |
|  1 | Java        |  2 | Green  |   23 | man   |    158 |         2 |
|  2 | MySQL       |  2 | Green  |   23 | man   |    158 |         2 |
|  3 | Python      |  2 | Green  |   23 | man   |    158 |         2 |
|  4 | Go          |  2 | Green  |   23 | man   |    158 |         2 |
|  5 | C++         |  2 | Green  |   23 | man   |    158 |         2 |
|  1 | Java        |  3 | Henry  |   23 | woman |    185 |         1 |
|  2 | MySQL       |  3 | Henry  |   23 | woman |    185 |         1 |
|  3 | Python      |  3 | Henry  |   23 | woman |    185 |         1 |
|  4 | Go          |  3 | Henry  |   23 | woman |    185 |         1 |
|  5 | C++         |  3 | Henry  |   23 | woman |    185 |         1 |
|  1 | Java        |  4 | jane   |   22 | man   |    162 |         3 |
|  2 | MySQL       |  4 | jane   |   22 | man   |    162 |         3 |
|  3 | Python      |  4 | jane   |   22 | man   |    162 |         3 |
|  4 | Go          |  4 | jane   |   22 | man   |    162 |         3 |
|  5 | C++         |  4 | jane   |   22 | man   |    162 |         3 |
|  1 | Java        |  5 | jim    |   24 | woman |    175 |         2 |
|  2 | MySQL       |  5 | jim    |   24 | woman |    175 |         2 |
|  3 | Python      |  5 | jim    |   24 | woman |    175 |         2 |
|  4 | Go          |  5 | jim    |   24 | woman |    175 |         2 |
|  5 | C++         |  5 | jim    |   24 | woman |    175 |         2 |
|  1 | Java        |  6 | john   |   21 | woman |    172 |         4 |
|  2 | MySQL       |  6 | john   |   21 | woman |    172 |         4 |
|  3 | Python      |  6 | john   |   21 | woman |    172 |         4 |
|  4 | Go          |  6 | john   |   21 | woman |    172 |         4 |
|  5 | C++         |  6 | john   |   21 | woman |    172 |         4 |
|  1 | Java        |  7 | Lily   |   22 | man   |    188 |         4 |
|  2 | MySQL       |  7 | Lily   |   22 | man   |    188 |         4 |
|  3 | Python      |  7 | Lily   |   22 | man   |    188 |         4 |
|  4 | Go          |  7 | Lily   |   22 | man   |    188 |         4 |
|  5 | C++         |  7 | Lily   |   22 | man   |    188 |         4 |
|  1 | Java        |  8 | Susan  |   22 | man   |    179 |         5 |
|  2 | MySQL       |  8 | Susan  |   22 | man   |    179 |         5 |
|  3 | Python      |  8 | Susan  |   22 | man   |    179 |         5 |
|  4 | Go          |  8 | Susan  |   22 | man   |    179 |         5 |
|  5 | C++         |  8 | Susan  |   22 | man   |    179 |         5 |
|  1 | Java        |  9 | Thomas |   22 | woman |    158 |         5 |
|  2 | MySQL       |  9 | Thomas |   22 | woman |    158 |         5 |
|  3 | Python      |  9 | Thomas |   22 | woman |    158 |         5 |
|  4 | Go          |  9 | Thomas |   22 | woman |    158 |         5 |
|  5 | C++         |  9 | Thomas |   22 | woman |    158 |         5 |
|  1 | Java        | 10 | Tom    |   23 | woman |    165 |         5 |
|  2 | MySQL       | 10 | Tom    |   23 | woman |    165 |         5 |
|  3 | Python      | 10 | Tom    |   23 | woman |    165 |         5 |
|  4 | Go          | 10 | Tom    |   23 | woman |    165 |         5 |
|  5 | C++         | 10 | Tom    |   23 | woman |    165 |         5 |
+----+-------------+----+--------+------+-------+--------+-----------+
50 rows in set (0.00 sec)

有条件查询

在交叉连接时使用 WHERE 子句，MySQL 会先生成两个表的笛卡尔积，然后再选择满足 WHERE 条件的记录。因此，表的数量或数据较多时，交叉连接会非常非常慢

mysql> select * from tb_course cross join students where students.course_id=tb_course.id;
+----+-------------+----+--------+------+-------+--------+-----------+
| id | course_name | id | name   | age  | sex   | height | course_id |
+----+-------------+----+--------+------+-------+--------+-----------+
|  1 | Java        |  1 | Dany   |   25 | man   |    160 |         1 |
|  2 | MySQL       |  2 | Green  |   23 | man   |    158 |         2 |
|  1 | Java        |  3 | Henry  |   23 | woman |    185 |         1 |
|  3 | Python      |  4 | jane   |   22 | man   |    162 |         3 |
|  2 | MySQL       |  5 | jim    |   24 | woman |    175 |         2 |
|  4 | Go          |  6 | john   |   21 | woman |    172 |         4 |
|  4 | Go          |  7 | Lily   |   22 | man   |    188 |         4 |
|  5 | C++         |  8 | Susan  |   22 | man   |    179 |         5 |
|  5 | C++         |  9 | Thomas |   22 | woman |    158 |         5 |
|  5 | C++         | 10 | Tom    |   23 | woman |    165 |         5 |
+----+-------------+----+--------+------+-------+--------+-----------+
10 rows in set (0.00 sec)

2、内连接

内连接（INNER JOIN）主要通过设置连接条件的方式，来移除查询结果中某些数据行的交叉连接。简单来说，就是利用条件表达式来消除交叉连接的某些数据行。
内连接使用 INNER JOIN 关键字连接两张表，并使用 ON 子句来设置连接条件。如果没有连接条件，INNER JOIN 和 CROSS JOIN 在语法上是等同的，两者可以互换。
内连接的语法格式如下：
SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句];

语法说明如下。
字段名：需要查询的字段名称。
<表1><表2>：需要内连接的表名。
INNER JOIN ：内连接中可以省略 INNER 关键字，只用关键字 JOIN。
ON 子句：用来设置内连接的连接条件。
INNER JOIN 也可以使用 WHERE 子句指定连接条件，但是 INNER JOIN … ON 语法是官方的标准写法，而且 WHERE 子句在某些时候会影响查询的性能。
多个表内连接时，在 FROM 后连续使用 INNER JOIN 或 JOIN 即可。
内连接可以查询两个或两个以上的表。为了更好的理解，暂时只讲解两个表的连接查询。

例：在 students表和 tb_course 表之间，使用内连接查询学生姓名和相对应的课程名称

mysql> select s.name,c.course_name from students s inner join tb_course c on s.course_id = c.id;
+--------+-------------+
| name   | course_name |
+--------+-------------+
| Dany   | Java        |
| Green  | MySQL       |
| Henry  | Java        |
| jane   | Python      |
| jim    | MySQL       |
| john   | Go          |
| Lily   | Go          |
| Susan  | C++         |
| Thomas | C++         |
| Tom    | C++         |
+--------+-------------+
10 rows in set (0.00 sec)

在这里的查询语句中，两个表之间的关系通过 INNER JOIN指定，连接的条件使用ON子句给出

当对多个表进行查询时，要在 SELECT 语句后面指定字段是来源于哪一张表。因此，在多表查询时，SELECT 语句后面的写法是表名.列名。另外，如果表名非常长的话，也可以给表设置别名，这样就可以直接在 SELECT 语句后面写上表的别名.列名。

3、外连接

内连接的查询结果都是符合连接条件的记录，而外连接会先将连接的表分为基表和参考表，再以基表为依据返回满足和不满足条件的记录。
外连接可以分为左外连接和右外连接2种

左外连接

左外连接又称为左连接，使用 LEFT OUTER JOIN 关键字连接两个表，并使用 ON 子句来设置连接条件。
左连接的语法格式如下：
SELECT <字段名> FROM <表1> LEFT OUTER JOIN <表2> <ON子句>;
语法说明如下:
字段名：需要查询的字段名称。
<表1><表2>：需要左连接的表名。
LEFT OUTER JOIN：左连接中可以省略 OUTER 关键字，只使用关键字 LEFT JOIN。
ON 子句：用来设置左连接的连接条件，不能省略。
上述语法中，”表1”为基表，”表2”为参考表。左连接查询时，可以查询出”表1”中的所有记录和”表2”中匹配连接条件的记录。如果”表1”的某行在”表2”中没有匹配行，那么在返回结果中，”表2”的字段值均为空值（NULL）。

//在students表中新增一条记录，以用来测试

mysql> insert into students values(11,'shiqian',20,'man',188,6);
Query OK, 1 row affected (0.00 sec)

mysql> select * from students;
+----+---------+------+-------+--------+-----------+
| id | name    | age  | sex   | height | course_id |
+----+---------+------+-------+--------+-----------+
|  1 | Dany    |   25 | man   |    160 |         1 |
|  2 | Green   |   23 | man   |    158 |         2 |
|  3 | Henry   |   23 | woman |    185 |         1 |
|  4 | jane    |   22 | man   |    162 |         3 |
|  5 | jim     |   24 | woman |    175 |         2 |
|  6 | john    |   21 | woman |    172 |         4 |
|  7 | Lily    |   22 | man   |    188 |         4 |
|  8 | Susan   |   22 | man   |    179 |         5 |
|  9 | Thomas  |   22 | woman |    158 |         5 |
| 10 | Tom     |   23 | woman |    165 |         5 |
| 11 | shiqian |   20 | man   |    188 |         6 |
+----+---------+------+-------+--------+-----------+
11 rows in set (0.00 sec)

例：在 students 表和 tb_course 表中查询所有学生姓名和相对应的课程名称，包括没有课程的学生

mysql> select s.name,c.course_name from students s left outer join tb_course c on s.`course_id`=c.`id`;
+---------+-------------+
| name    | course_name |
+---------+-------------+
| Dany    | Java        |
| Henry   | Java        |
| Green   | MySQL       |
| jim     | MySQL       |
| jane    | Python      |
| john    | Go          |
| Lily    | Go          |
| Susan   | C++         |
| Thomas  | C++         |
| Tom     | C++         |
| shiqian | NULL        |
+---------+-------------+
11 rows in set (0.00 sec)

由此可见，运行结果显示了 121条记录，name 为 shiqian 的学生目前没有课程，因为对应的 tb_course 表中没有该学生的课程信息，所以该条记录只取出了 students 表中相应的值，而从 tb_course 表中取出的值为 NULL。

右链接

右外连接又称为右连接，右连接是左连接的反向连接。使用 RIGHT OUTER JOIN 关键字连接两个表，并使用 ON 子句来设置连接条件。
右连接的语法格式如下：
SELECT <字段名> FROM <表1> RIGHT OUTER JOIN <表2> <ON子句>;
语法说明如下:
字段名：需要查询的字段名称。
表1><表2>：需要右连接的表名。
RIGHT OUTER JOIN：右连接中可以省略 OUTER 关键字，只使用关键字 RIGHT JOIN。
ON 子句：用来设置右连接的连接条件，不能省略。
与左连接相反，右连接以”表2”为基表，”表1”为参考表。右连接查询时，可以查询出”表2”中的所有记录和”表1”中匹配连接条件的记录。如果”表2”的某行在”表1”中没有匹配行，那么在返回结果中，”表1”的字段值均为空值（NULL）。

//在tb_course表中新增两条记录

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | html        |
|  7 | shell       |
+----+-------------+
7 rows in set (0.00 sec)

例：在 students 表和 tb_course 表中查询所有课程，包括没有学生的课程

mysql> select s.name,c.course_name from students s right outer join tb_course c on s.`course_id`=c.`id`;
+---------+-------------+
| name    | course_name |
+---------+-------------+
| Dany    | Java        |
| Green   | MySQL       |
| Henry   | Java        |
| jane    | Python      |
| jim     | MySQL       |
| john    | Go          |
| Lily    | Go          |
| Susan   | C++         |
| Thomas  | C++         |
| Tom     | C++         |
| shiqian | html        |
| NULL    | shell       |
+---------+-------------+
12 rows in set (0.00 sec)

可以看到，结果显示了 112条记录，名称为 Shell 的课程目前没有学生，因为对应的tb_students_info 表中并没有该学生的信息，所以该条记录只取出了 tb_course 表中相应的值，而从 students 表中取出的值为 NULL。
多个表左/右连接时，在 ON 子句后连续使用 LEFT/RIGHT OUTER JOIN 或 LEFT/RIGHT JOIN 即可。
使用外连接查询时，一定要分清需要查询的结果，是需要显示左表的全部记录还是右表的全部记录，然后选择相应的左连接和右连接。

4、分组查询

在 MySQL 中，GROUP BY 关键字可以根据一个或多个字段对查询结果进行分组。
使用 GROUP BY 关键字的语法格式如下：
GROUP BY <字段名>
其中，”字段名”表示需要分组的字段名称，多个字段时用逗号隔开。

GROUP BY单独使用

单独使用 GROUP BY 关键字时，查询结果会只显示每个分组的第一条记录。
例：根据 students 表中的 sex 字段进行分组查询

mysql> select `sex`,count(*) from students group by sex;
+-------+----------+
| sex   | count(*) |
+-------+----------+
| man   |        6 |
| woman |        5 |
+-------+----------+
2 rows in set (0.00 sec)

GROUP BY 与 GROUP_CONCAT()

GROUP BY 关键字可以和 GROUP_CONCAT() 函数一起使用。GROUP_CONCAT() 函数会把每个分组的字段值都显示出来。
例：根据 students 表中的 sex 字段进行分组查询，使用 GROUP_CONCAT() 函数将每个分组的 name 字段的值都显示出来

mysql> select `sex`,group_concat(name) from students group by sex;
+-------+------------------------------------+
| sex   | group_concat(name)                 |
+-------+------------------------------------+
| man   | Dany,Green,jane,Lily,Susan,shiqian |
| woman | Henry,jim,john,Thomas,Tom          |
+-------+------------------------------------+
2 rows in set (0.00 sec)

//结果可以看到，查询结果分为两组，sex 字段值为"女"的是一组，值为"男"的是一组，且每组的学生姓名都显示出来了

也可以根据 tb_students_info 表中的 age 和 sex 字段进行分组查询

mysql> select age,sex,group_concat(name) from students group by age,sex;
+------+-------+--------------------+
| age  | sex   | group_concat(name) |
+------+-------+--------------------+
|   20 | man   | shiqian            |
|   21 | woman | john               |
|   22 | man   | jane,Lily,Susan    |
|   22 | woman | Thomas             |
|   23 | man   | Green              |
|   23 | woman | Henry,Tom          |
|   24 | woman | jim                |
|   25 | man   | Dany               |
+------+-------+--------------------+
8 rows in set (0.00 sec)

在分组过程中，先按照 age 字段进行分组，当 age 字段值相等时，再把 age 字段值相等的记录按照 sex 字段进行分组。
多个字段分组查询时，会先按照第一个字段进行分组。如果第一个字段中有相同的值，MySQL 才会按照第二个字段进行分组。如果第一个字段中的数据都是唯一的，那么 MySQL 将不再对第二个字段进行分组。

GROUP BY 与聚合函数

在数据统计时，GROUP BY 关键字经常和聚合函数一起使用。
聚合函数包括 COUNT()，SUM()，AVG()，MAX() 和 MIN()。其中，COUNT() 用来统计记录的条数；SUM() 用来计算字段值的总和；AVG() 用来计算字段值的平均值；MAX() 用来查询字段的最大值；MIN() 用来查询字段的最小值。
例：根据 students 表的 sex 字段进行分组查询，使用 COUNT() 函数计算每一组的记录数

mysql> select sex,count(sex) from students group by sex;
+-------+------------+
| sex   | count(sex) |
+-------+------------+
| man   |          6 |
| woman |          5 |
+-------+------------+
2 rows in set (0.00 sec)

GROUP BY 与 WITH ROLLUP

WITH POLLUP 关键字用来在所有记录的最后加上一条记录，这条记录是上面所有记录的总和，即统计记录数量。
例：根据 students 表中的 sex 字段进行分组查询，并使用 WITH ROLLUP 显示记录的总和

mysql> select sex,group_concat(name) from students group by sex with rollup;
+-------+--------------------------------------------------------------+
| sex   | group_concat(name)                                           |
+-------+--------------------------------------------------------------+
| man   | Dany,Green,jane,Lily,Susan,shiqian                           |
| woman | Henry,jim,john,Thomas,Tom                                    |
| NULL  | Dany,Green,jane,Lily,Susan,shiqian,Henry,jim,john,Thomas,Tom |
+-------+--------------------------------------------------------------+
3 rows in set (0.00 sec)

5、子查询

子查询是 MySQL 中比较常用的查询方法，通过子查询可以实现多表查询。子查询指将一个查询语句嵌套在另一个查询语句中。子查询可以在 SELECT、UPDATE 和 DELETE 语句中使用，而且可以进行多层嵌套。在实际开发时，子查询经常出现在 WHERE 子句中。
子查询在 WHERE 中的语法格式如下：
WHERE <表达式> <操作符> (子查询)
其中，操作符可以是比较运算符和 IN、NOT IN、EXISTS、NOT EXISTS 等关键字。

IN | NOT IN

当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回值正好相反。

EXISTS | NOT EXISTS

用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回的值正好相反。

例：使用子查询在 students 表和 tb_course 表中查询学习 Java 课程的学生姓名

mysql> select name from students where course_id in (select id from tb_course where course_name = 'Java');
+-------+
| name  |
+-------+
| Dany  |
| Henry |
+-------+
2 rows in set (0.00 sec)

外层的 SELECT 查询称为父查询，圆括号中嵌入的查询称为子查询（子查询必须放在圆括号内）。MySQL 在处理上例的 SELECT 语句时，执行流程为：先执行子查询，再执行父查询。

子查询的功能也可以通过表连接完成，但是子查询会使 SQL 语句更容易阅读和编写。
一般来说，表连接（内连接和外连接等）都可以用子查询替换，但反过来却不一定，有的子查询不能用表连接来替换。子查询比较灵活、方便、形式多样，适合作为查询的筛选条件，而表连接更适合于查看连接表的数据。

二、数据库备份与恢复

数据库常用备份

全量备份
增量备份
差异备份

特点：
全量备份 全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。
数据恢复快。备份时间长。

增量备份 是指在一次全备份或上一次增量备份后，以后每次的备份只需备份
与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象
是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量
备份后所产生的增加和修改的文件，如此类推。没有重复的备份数据，，备份时间短
恢复数据时必须按一定的顺序进行

差异备份 备份上一次的完全备份后发生变化的所有文件。
差异备份是指在一次全备份后到进行差异备份的这段时间内
对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。

mysql备份工具mysqldump

//语法：
mysqldump [OPTIONS] database [tables …]
mysqldump [OPTIONS] –all-databases [OPTIONS]
mysqldump [OPTIONS] –databases [OPTIONS] DB1 [DB2 DB3…]
//常用的OPTIONS：
-uUSERNAME //指定数据库用户名
-hHOST //指定服务器主机，请使用ip地址
-pPASSWORD //指定数据库用户的密码
-P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307

完全备份与恢复

备份

备份整个数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sq                 |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use sq;
Database changed
mysql> show tables;
+--------------+
| Tables_in_sq |
+--------------+
| shiqian      |
| student      |
| student1     |
| students     |
| tb_course    |
+--------------+
5 rows in set (0.00 sec)

[root@mysql ~]# mysqldump -uroot -predhat --all-databases > all-202408031532.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql ~]# ls
all-202408031532.sql  anaconda-ks.cfg

//备份sq数据库中的students表和tb_course表

[root@mysql ~]# mysqldump -uroot -predhat --databases sq > sq-202408031745.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql ~]# ls
all-202408031532.sql  anaconda-ks.cfg  sq-202408031745.sql  table-202408031735.sql  table-202408031737.sql

[root@mysql ~]# mysqldump -uroot -predhat sq tb_course > table-202408031737.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

[root@mysql ~]# ls
all-202408031532.sql  anaconda-ks.cfg  table-202408031735.sql  table-202408031737.sql

恢复

模拟误删sq数据库

mysql> drop database sq;
Query OK, 5 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

恢复sq数据库

[root@mysql ~]# mysql -uroot -p  < all-202408031532.sql 
Enter password: 
[root@mysql ~]# mysql -uroot -predhat -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sq                 |
| sys                |
+--------------------+

差异备份与恢复

//编辑 /etc/my.cnf文件，开启日志功能，在最后加上

server-id=1         //设置服务器标识符
log-bin=mysql_bin    //开启二进制日志功能

[root@mysql ~]# systemctl restart mysqld   //重启mysql服务

备份

先进行完全备份

[root@mysql ~]# mysqldump -uroot -predhat --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-202408032134.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql ~]# ls
all-202408032134.sql  anaconda-ks.cfg

增加新内容

mysql> use sq
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------+
| Tables_in_sq |
+--------------+
| students     |
| tb_course    |
+--------------+
2 rows in set (0.00 sec)

mysql> select * from students;
+----+---------+------+-------+--------+-----------+
| id | name    | age  | sex   | height | course_id |
+----+---------+------+-------+--------+-----------+
|  1 | Dany    |   25 | man   |    160 |         1 |
|  2 | Green   |   23 | man   |    158 |         2 |
|  3 | Henry   |   23 | woman |    185 |         1 |
|  4 | jane    |   22 | man   |    162 |         3 |
|  5 | jim     |   24 | woman |    175 |         2 |
|  6 | john    |   21 | woman |    172 |         4 |
|  7 | Lily    |   22 | man   |    188 |         4 |
|  8 | Susan   |   22 | man   |    179 |         5 |
|  9 | Thomas  |   22 | woman |    158 |         5 |
| 10 | Tom     |   23 | woman |    165 |         5 |
| 11 | shiqian |   20 | man   |    188 |         6 |
+----+---------+------+-------+--------+-----------+
11 rows in set (0.00 sec)

mysql> insert into students values(12,'marry',23,'man',186,6);
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb_course values(7,null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | html        |
|  7 | shell       |
|  7 | NULL        |
+----+-------------+
8 rows in set (0.00 sec)

恢复

//模拟误删数据

[root@mysql ~]# mysql -uroot -predhat -e 'drop database sq;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql ~]#  mysql -uroot -predhat -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

由此可见sq数据库已被删除

//刷新创建新的二进制文件

[root@mysql ~]# ll /opt/data/
total 123004
-rw-r-----. 1 mysql mysql       56 Jul 30 16:29 auto.cnf
-rw-------. 1 mysql mysql     1680 Jul 30 16:29 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 30 16:29 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 30 16:29 client-cert.pem
-rw-------. 1 mysql mysql     1676 Jul 30 16:29 client-key.pem
-rw-r-----. 1 mysql mysql      901 Aug  3 19:49 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Aug  3 20:14 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Aug  3 20:14 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 30 16:29 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Aug  3 20:04 ibtmp1
drwxr-x---. 2 mysql mysql     4096 Aug  3 17:44 mysql
-rw-r-----. 1 mysql mysql      844 Aug  3 20:14 mysql_bin.000002
-rw-r-----. 1 mysql mysql       19 Aug  3 20:04 mysql_bin.index
-rw-r-----. 1 mysql mysql    41336 Aug  3 19:49 mysql.example.com.err
-rw-r-----. 1 mysql mysql        5 Aug  3 19:49 mysql.pid
drwxr-x---. 2 mysql mysql     8192 Jul 30 16:29 performance_schema
-rw-------. 1 mysql mysql     1680 Jul 30 16:29 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jul 30 16:29 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 30 16:29 server-cert.pem
-rw-------. 1 mysql mysql     1680 Jul 30 16:29 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jul 30 16:29 sys
[root@mysql ~]# mysqladmin -uroot -predhat flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@mysql ~]# ll /opt/data/
total 123008
-rw-r-----. 1 mysql mysql       56 Jul 30 16:29 auto.cnf
-rw-------. 1 mysql mysql     1680 Jul 30 16:29 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 30 16:29 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 30 16:29 client-cert.pem
-rw-------. 1 mysql mysql     1676 Jul 30 16:29 client-key.pem
-rw-r-----. 1 mysql mysql      901 Aug  3 19:49 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Aug  3 20:16 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Aug  3 20:16 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 30 16:29 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Aug  3 20:04 ibtmp1
drwxr-x---. 2 mysql mysql     4096 Aug  3 17:44 mysql
-rw-r-----. 1 mysql mysql      891 Aug  3 20:16 mysql_bin.000002
-rw-r-----. 1 mysql mysql      154 Aug  3 20:16 mysql_bin.000003
-rw-r-----. 1 mysql mysql       38 Aug  3 20:16 mysql_bin.index
-rw-r-----. 1 mysql mysql    41336 Aug  3 19:49 mysql.example.com.err
-rw-r-----. 1 mysql mysql        5 Aug  3 19:49 mysql.pid
drwxr-x---. 2 mysql mysql     8192 Jul 30 16:29 performance_schema
-rw-------. 1 mysql mysql     1680 Jul 30 16:29 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jul 30 16:29 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 30 16:29 server-cert.pem
-rw-------. 1 mysql mysql     1680 Jul 30 16:29 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jul 30 16:29 sys

//恢复完全备份

[root@mysql ~]# mysql -uroot -predhat < all-202408032134.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql ~]# mysql -uroot -predhat -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sq                 |
| sys                |
+--------------------+

[root@mysql ~]# mysql -uroot -predhat -e 'select * from sq.students;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+---------+------+-------+--------+-----------+
| id | name    | age  | sex   | height | course_id |
+----+---------+------+-------+--------+-----------+
|  1 | Dany    |   25 | man   |    160 |         1 |
|  2 | Green   |   23 | man   |    158 |         2 |
|  3 | Henry   |   23 | woman |    185 |         1 |
|  4 | jane    |   22 | man   |    162 |         3 |
|  5 | jim     |   24 | woman |    175 |         2 |
|  6 | john    |   21 | woman |    172 |         4 |
|  7 | Lily    |   22 | man   |    188 |         4 |
|  8 | Susan   |   22 | man   |    179 |         5 |
|  9 | Thomas  |   22 | woman |    158 |         5 |
| 10 | Tom     |   23 | woman |    165 |         5 |
| 11 | shiqian |   20 | man   |    188 |         6 |
+----+---------+------+-------+--------+-----------+
[root@mysql ~]# mysql -uroot -predhat -e 'select * from sq.tb_course;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | html        |
|  7 | shell       |
+----+-------------+

//恢复差异备份

[root@mysql ~]# mysql -uroot -predhat

mysql> show binlog events in 'mysql_bin.000002';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000002 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.37-log, Binlog ver: 4 |
| mysql_bin.000002 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysql_bin.000002 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 219 | Query          |         1 |         289 | BEGIN                                 |
| mysql_bin.000002 | 289 | Table_map      |         1 |         347 | table_id: 143 (sq.students)           |
| mysql_bin.000002 | 347 | Write_rows     |         1 |         403 | table_id: 143 flags: STMT_END_F       |
| mysql_bin.000002 | 403 | Xid            |         1 |         434 | COMMIT /* xid=531 */                  |
| mysql_bin.000002 | 434 | Anonymous_Gtid |         1 |         499 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 499 | Query          |         1 |         569 | BEGIN                                 |
| mysql_bin.000002 | 569 | Table_map      |         1 |         622 | table_id: 144 (sq.tb_course)          |
| mysql_bin.000002 | 622 | Write_rows     |         1 |         662 | table_id: 144 flags: STMT_END_F       |
| mysql_bin.000002 | 662 | Xid            |         1 |         693 | COMMIT /* xid=532 */                  |
| mysql_bin.000002 | 693 | Anonymous_Gtid |         1 |         758 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 758 | Query          |         1 |         844 | drop database sq                      |
//此处就是删除数据库的位置，对应的pos位置是758
| mysql_bin.000002 | 844 | Rotate         |         1 |         891 | mysql_bin.000003;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)

//使用MySQL恢复差异备份
[root@mysql ~]#  mysqlbinlog --stop-position=758 /opt/data/mysql_bin.000002
[root@mysql ~]# mysql -uroot -predhat -e 'select * from sq.students;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+---------+------+-------+--------+-----------+
| id | name    | age  | sex   | height | course_id |
+----+---------+------+-------+--------+-----------+
|  1 | Dany    |   25 | man   |    160 |         1 |
|  2 | Green   |   23 | man   |    158 |         2 |
|  3 | Henry   |   23 | woman |    185 |         1 |
|  4 | jane    |   22 | man   |    162 |         3 |
|  5 | jim     |   24 | woman |    175 |         2 |
|  6 | john    |   21 | woman |    172 |         4 |
|  7 | Lily    |   22 | man   |    188 |         4 |
|  8 | Susan   |   22 | man   |    179 |         5 |
|  9 | Thomas  |   22 | woman |    158 |         5 |
| 10 | Tom     |   23 | woman |    165 |         5 |
| 11 | shiqian |   20 | man   |    188 |         6 |
+----+---------+------+-------+--------+-----------+
[root@mysql ~]# mysql -uroot -predhat -e 'select * from sq.tb_course;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | html        |
|  7 | shell       |
+----+-------------+