mysql进阶

mysql进阶

目录

• mysql进阶
  • 二进制格式mysql安装
  • 数据库密码破解
  • 多表联合查询
    • 什么是多表联合查询
    • 交叉连接
      • 笛卡尔积
      • 交叉连接
    • 内连接
    • 外连接
      • 左连接
      • 右连接
    • 分组查询
      • GROUP BY单独使用
      • GROUP BY 与 GROUP_CONCAT()
      • GROUP BY 与聚合函数
      • GROUP BY 与 WITH ROLLUP
      • 子查询
  • mysql数据库备份与恢复
    • 数据库常用备份方案
    • mysql备份工具mysqldump
    • mysql数据恢复
    • 差异备份与恢复
      • 备份
      • 恢复

二进制格式mysql安装

//下载二进制格式的mysql软件包
[root@z1 src]# ls       
mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz

//创建用户和组
[root@z1 src]# useradd -M -r -s /sbin/nologin mysql
[root@z1 src]# id mysql
uid=994(mysql) gid=991(mysql) groups=991(mysql)

//解压软件至/usr/local/
[root@z1 src]# tar xf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@z1 src]# ls /usr/local/
apache    bin    httpd    lib64                                sbin
apr       etc    include  libexec                              share
apr-util  games  lib      mysql-5.7.38-linux-glibc2.12-x86_64  src

[root@z1 src]# cd /usr/local/
[root@z1 local]# ln -sv mysql-5.7.38-linux-glibc2.12-x86_64/ mysql
[root@z1 local]# ll
total 0
drwxr-xr-x. 13 root root 152 Jul 21 22:15 apache
drwxr-xr-x.  6 root root  58 Jul 21 22:09 apr
drwxr-xr-x.  5 root root  43 Jul 21 22:10 apr-util
drwxr-xr-x.  2 root root   6 May 19  2020 bin
drwxr-xr-x.  2 root root   6 May 19  2020 etc
drwxr-xr-x.  2 root root   6 May 19  2020 games
drwxr-xr-x. 14 root root 164 Jul 21 22:42 httpd
drwxr-xr-x.  2 root root   6 May 19  2020 include
drwxr-xr-x.  2 root root   6 May 19  2020 lib
drwxr-xr-x.  3 root root  17 Jul 15 09:07 lib64
drwxr-xr-x.  2 root root   6 May 19  2020 libexec
lrwxrwxrwx.  1 root root  36 Jul 27 17:41 mysql -> mysql-5.7.38-linux-glibc2.12-x86_64/
drwxr-xr-x.  9 root root 129 Jul 27 17:39 mysql-5.7.38-linux-glibc2.12-x86_64
drwxr-xr-x.  2 root root   6 May 19  2020 sbin
drwxr-xr-x.  5 root root  49 Jul 15 09:07 share
drwxr-xr-x.  2 root root   6 May 19  2020 src

//修改目录/usr/local/mysql的属主属组
[root@z1 local]# chown -R mysql.mysql* /usr/local/mysql
[root@z1 local]# ll
lrwxrwxrwx.  1 mysql mysql  36 Jul 27 17:41 mysql -> mysql-5.7.38-linux-glibc2.12-x86_64/

//添加环境变量
[root@z1 ~]# cd /usr/local/mysql
[root@z1 mysql]# ls
bin  docs  include  lib  LICENSE  man  README  share  support-files

[root@z1 bin]# echo 'export PATH=$PATH:/usr/local/mysql/bin' > /etc/profile.d/mysql.sh
[root@z1 bin]# source /etc/profile.d/mysql.sh 
[root@z1 bin]# which mysql
/usr/local/mysql/bin/mysql

[root@z1 mysql]# ln -sv /usr/local/mysql/include/  /usr/include/mysql
'/usr/include/mysql' -> '/usr/local/mysql/include/'

[root@z1 mysql]# vim /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib
[root@z1 mysql]# ldconfig

[root@z1 mysql]# vim /etc/man_db.conf 
MANDATORY_MANPATH                       /usr/man
MANDATORY_MANPATH                       /usr/share/man
MANDATORY_MANPATH                       /usr/local/share/man
MANDATORY_MANPATH                       /usr/local/httpd/man
MANDATORY_MANPATH                       /usr/local/mysql/man  //添加这行

//建立数据存放目录
[root@z1 local]# mkdir /opt/data
[root@z1 local]# chown -R mysql.mysql
[root@z1 local]# chown -R mysql.mysql /opt/data/

//初始化数据库
[root@z1 local]# mysqld --initalize --user mysql --datadir /opt/data/
2022-07-27T10:15:31.521088Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 0ba825ed-0d95-11ed-ba6f-000c29b27139.
2022-07-27T10:15:31.522158Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-27T10:15:31.659833Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-27T10:15:31.659849Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-27T10:15:31.660344Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-27T10:15:31.724571Z 1 [Note] A temporary password is generated for root@localhost: 11etgZd3XD:8
//这个命令的最后会生成一个临时密码，此处密码是11etgZd3XD:8


//启动mysql  绝对路径启动 不推荐
[root@z1 mysql]# /usr/local/mysql/support-files/mysql.server start  
[root@z1 mysql]# ss -antl
State   Recv-Q  Send-Q    Local Address:Port     Peer Address:Port  Process  
LISTEN  0       128             0.0.0.0:22            0.0.0.0:*              
LISTEN  0       128             0.0.0.0:111           0.0.0.0:*              
LISTEN  0       128                [::]:22               [::]:*              
LISTEN  0       80                    *:3306                *:*              
LISTEN  0       128                [::]:111              [::]:*  

//启动mysql   6版本启动方式
[root@z1 mysql]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld               
[root@z1 mysql]# vim /etc/init.d/mysqld           //配置服务启动脚本
basedir=/usr/local/mysql
datadir=/opt/data/                
[root@z1 mysql]# service mysqld start
Starting MySQL. SUCCESS! 
[root@z1 mysql]# ss -antl
State   Recv-Q  Send-Q    Local Address:Port     Peer Address:Port  Process  
LISTEN  0       128             0.0.0.0:22            0.0.0.0:*              
LISTEN  0       128             0.0.0.0:111           0.0.0.0:*              
LISTEN  0       128                [::]:22               [::]:*              
LISTEN  0       80                    *:3306                *:*              
LISTEN  0       128                [::]:111              [::]:*     

//启动mysql   7以后版本启动方式
[root@z1 ~]# vim mysqld.service   
[Unit]
Description=mysql server daemon
After=network.target sshd-keygen.target
[Service]
Type=forking
ExecStart=/usr/local/mysql/support-files/mysql.server start
ExecStop=/usr/local/mysql/support-files/mysql.server stop
ExecReload=/bin/kill -HUP $MAINPID
[Install]
WantedBy=multi-user.target

[root@z1 ~]# mv mysqld.service /usr/lib/systemd/system/
[root@z1 ~]# systemctl daemon-reload
[root@z1 ~]# systemctl start mysqld
[root@z1 ~]# ss -antl
State   Recv-Q  Send-Q    Local Address:Port     Peer Address:Port  Process  
LISTEN  0       128             0.0.0.0:22            0.0.0.0:*              
LISTEN  0       128             0.0.0.0:111           0.0.0.0:*              
LISTEN  0       128                [::]:22               [::]:*              
LISTEN  0       80                    *:3306                *:*              
LISTEN  0       128                [::]:111              [::]:*    

//使用临时密码登录
[root@z1 ~]# mysql -uroot -p11etgZd3XD:8
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.38

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.01 sec)     

数据库密码破解

[root@z1 ~]# vim /etc/my.cnf    //更改配置文件
skip-grant-tables        //增加这一行
[root@z1 ~]# systemctl restart mysqld
[root@z1 ~]# mysql              
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> update user set authentication_string = password('654321'); where User = 'root' and Host = 'localhost';           //更改密码
Query OK, 3 rows affected, 1 warning (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 1

[root@z1 ~]# vim /etc/my.cnf
skip-grant-tables    //删除这一行

[root@z1 ~]# mysql -uroot -p654321   //用更改之后的密码登录
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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 中，多表查询主要有交叉连接、内连接、外连接、分组查询与子查询等5种。

交叉连接

笛卡尔积

交叉连接（CROSS JOIN）：有两种，显式的和隐式的2种，一般用来返回连接表的笛卡尔积。
笛卡尔积（笛卡尔积）是指两个集合 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 子句：用来设置交叉连接的查询条件。

查询 tb_students_info 表中的数据，SQL 语句和运行结果如下：

mysql> SELECT * FROM tb_students_info;
+----+-------+------+------+--------+-----------+
| id | name  | age  | sex  | height | course_id |
+----+-------+------+------+--------+-----------+
|  1 | dany  |   25 | m    |    160 |         1 |
|  2 | green |   23 | m    |    158 |         2 |
|  3 | henry |   23 | w    |    160 |         1 |
|  4 | jane  |   22 | m    |    188 |         3 |
|  5 | jim   |   24 | w    |    155 |         2 |
+----+-------+------+------+--------+-----------+
5 rows in set (0.00 sec)

查询 tb_course 表中的数据，SQL 语句和运行结果如下：

mysql> SELECT * FROM tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | java        |
|  2 | mysql       |
+----+-------------+
2 rows in set (0.00 sec)

使用 CROSS JOIN 查询出两张表中的笛卡尔积，SQL 语句和运行结果如下：

mysql> SELECT * FROM tb_course CROSS JOIN tb_students_info;
+----+-------------+----+-------+------+------+--------+-----------+
| id | course_name | id | name  | age  | sex  | height | course_id |
+----+-------------+----+-------+------+------+--------+-----------+
|  1 | java        |  1 | dany  |   25 | m    |    160 |         1 |
|  2 | mysql       |  1 | dany  |   25 | m    |    160 |         1 |
|  1 | java        |  2 | green |   23 | m    |    158 |         2 |
|  2 | mysql       |  2 | green |   23 | m    |    158 |         2 |
|  1 | java        |  3 | henry |   23 | w    |    160 |         1 |
|  2 | mysql       |  3 | henry |   23 | w    |    160 |         1 |
|  1 | java        |  4 | jane  |   22 | m    |    188 |         3 |
|  2 | mysql       |  4 | jane  |   22 | m    |    188 |         3 |
|  1 | java        |  5 | jim   |   24 | w    |    155 |         2 |
|  2 | mysql       |  5 | jim   |   24 | w    |    155 |         2 |
+----+-------------+----+-------+------+------+--------+-----------+
10 rows in set (0.00 sec)

实列
查询 tb_course 表中的 id 字段和 tb_students_info 表中的 course_id 字段相等的内容， SQL 语句和运行结果如下：

mysql> SELECT * FROM tb_course CROSS JOIN tb_students_info WHERE tb_students_info.course_id = tb_course.id;
+----+-------------+----+-------+------+------+--------+-----------+
| id | course_name | id | name  | age  | sex  | height | course_id |
+----+-------------+----+-------+------+------+--------+-----------+
|  1 | java        |  1 | dany  |   25 | m    |    160 |         1 |
|  2 | mysql       |  2 | green |   23 | m    |    158 |         2 |
|  1 | java        |  3 | henry |   23 | w    |    160 |         1 |
|  2 | mysql       |  5 | jim   |   24 | w    |    155 |         2 |
+----+-------------+----+-------+------+------+--------+-----------+
4 rows in set (0.00 sec)

如果在交叉连接时使用 WHERE 子句，MySQL 会先生成两个表的笛卡尔积，然后再选择满足 WHERE 条件的记录。因此，表的数量较多时，交叉连接会非常非常慢。一般情况下不建议使用交叉连接。

内连接

语法：SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句];

• 字段名：需要查询的字段名称。
• <表1><表2>：需要内连接的表名。
• INNER JOIN ：内连接中可以省略 INNER 关键字，只用关键字 JOIN。
• ON 子句：用来设置内连接的连接条件。

例 1
在 tb_students_info 表和 tb_course 表之间，使用内连接查询学生姓名和相对应的课程名称，SQL 语句和运行结果如下

mysql> SELECT s.name,c.course_name FROM tb_students_info 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)

外连接

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

外连接可以分为左外连接和右外连接2种

左连接

语法：SELECT <字段名> FROM <表1> LEFT OUTER JOIN <表2> <ON子句>;

• 字段名：需要查询的字段名称。
• <表1><表2>：需要左连接的表名。
• LEFT OUTER JOIN：左连接中可以省略 OUTER 关键字，只使用关键字 LEFT JOIN。
• ON 子句：用来设置左连接的连接条件，不能省略。

例 1
在进行左连接查询之前，我们先查看 tb_course 和 tb_students_info 两张表中的数据。SQL 语句和运行结果如下:

mysql> SELECT * FROM tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | Mysql       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | HTML        |
+----+-------------+
6 rows in set (0.01 sec)

mysql> SELECT * FROM tb_students_info;
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  1 | dany   |   25 | m    |    160 |         1 |
|  2 | Green  |   23 | m    |    158 |         2 |
|  3 | Henry  |   23 | w    |    185 |         1 |
|  4 | Jane   |   22 | m    |    162 |         3 |
|  5 | Jim    |   24 | w    |    175 |         2 |
|  6 | John   |   21 | w    |    172 |         4 |
|  7 | Lily   |   22 | m    |    165 |         4 |
|  8 | Susan  |   23 | m    |    170 |         5 |
|  9 | Thomas |   22 | w    |    178 |         5 |
| 10 | Tom    |   23 | w    |    165 |         5 |
| 11 | Liming |   22 | m    |    180 |         7 |
+----+--------+------+------+--------+-----------+
11 rows in set (0.00 sec)

在 tb_students_info 表和 tb_course 表中查询所有学生姓名和相对应的课程名称，包括没有课程的学生，SQL 语句和运行结果如下:

mysql> SELECT s.name,c.course_name FROM tb_students_info s LEFT 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++         |
| Liming | NULL        |
+--------+-------------+
11 rows in set (0.00 sec)

右连接

语法：SELECT <字段名> FROM <表1> RIGHT OUTER JOIN <表2> <ON子句>;

• 字段名：需要查询的字段名称。
• <表1><表2>：需要右连接的表名。
• RIGHT OUTER JOIN：右连接中可以省略 OUTER 关键字，只使用关键字 RIGHT JOIN。
• ON 子句：用来设置右连接的连接条件，不能省略。

例 2
在 tb_students_info 表和 tb_course 表中查询所有课程，包括没有学生的课程，SQL 语句和运行结果如下:

mysql> SELECT s.name,c.course_name FROM tb_students_info 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++         |
| NULL   | HTML        |
+--------+-------------+
11 rows in set (0.01 sec)

分组查询

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

GROUP BY单独使用

单独使用 GROUP BY 关键字时，查询结果会只显示每个分组的第一条记录

mysql> SELECT `name`,`sex` FROM tb_students_info GROUP BY sex;
+-------+------+
| name  | sex  |
+-------+------+
| dany  | m    |
| Henry | w    |
+-------+------+
2 rows in set (0.00 sec)

结果中只显示了两条记录，这两条记录的 sex 字段的值分别为“女”和“男”

GROUP BY 与 GROUP_CONCAT()

GROUP BY 关键字可以和 GROUP_CONCAT() 函数一起使用。GROUP_CONCAT() 函数会把每个分组的字段值都显示出来。

根据 tb_students_info 表中的 sex 字段进行分组查询，使用 GROUP_CONCAT() 函数将每个分组的 name 字段的值都显示出来。SQL 语句和运行结果如下：

mysql> select 'sex',group_concat(name) from tb_students_info group by sex;
+-----+-----------------------------------+
| sex | group_concat(name)                |
+-----+-----------------------------------+
| sex | dany,Green,Jane,Lily,Susan,Liming |
| sex | Henry,Jim,John,Thomas,Tom         |
+-----+-----------------------------------+
2 rows in set, 1 warning (0.01 sec)

下面根据 tb_students_info 表中的 age 和 sex 字段进行分组查询。SQL 语句和运行结果如下：

mysql> select age,sex,group_concat(name) from tb_students_info group by age,sex;4
+------+------+--------------------+
| age  | sex  | group_concat(name) |
+------+------+--------------------+
|   21 | w    | John               |
|   22 | m    | Jane,Lily,Liming   |
|   22 | w    | Thomas             |
|   23 | m    | Green,Susan        |
|   23 | w    | Henry,Tom          |
|   24 | w    | Jim                |
|   25 | m    | dany               |
+------+------+--------------------+
7 rows in set (0.00 sec)

GROUP BY 与聚合函数

GROUP BY 关键字经常和聚合函数一起使用
|聚合函数| 作用|
|-|-|-|
|COUNT|用来统计记录的条数|
|SUM|来计算字段值的总和|
|AVG|用来计算字段值的平均值|
|MAX|用来查询字段的最大值|
|MIN|用来查询字段的最小值|

下面根据 tb_students_info 表的 sex 字段进行分组查询，使用 COUNT() 函数计算每一组的记录数。SQL 语句和运行结果如下：

mysql> select sex,count(sex) from tb_students_info group by sex;
+------+------------+
| sex  | count(sex) |
+------+------------+
| m    |          6 |
| w    |          5 |
+------+------------+
2 rows in set (0.00 sec)

下面根据 tb_students_info 表的 age 字段进行分组查询，使用 sum() 函数计算每一组的记录数。SQL 语句和运行结果如下：

mysql> select age,sum(age) from tb_students_info group by sex;
+------+----------+
| age  | sum(age) |
+------+----------+
|   25 |      137 |
|   23 |      113 |
+------+----------+
2 rows in set (0.00 sec)

GROUP BY 与 WITH ROLLUP

WITH POLLUP 关键字用来在所有记录的最后加上一条记录，这条记录是上面所有记录的总和，即统计记录数量

下面根据 tb_students_info 表中的 sex 字段进行分组查询，并使用 WITH ROLLUP 显示记录的总和：

mysql> select sex,group_concat(name) from tb_students_info group by sex with rollup;
+------+-------------------------------------------------------------+
| sex  | group_concat(name)                                          |
+------+-------------------------------------------------------------+
| m    | dany,Green,Jane,Lily,Susan,Liming                           |
| w    | Henry,Jim,John,Thomas,Tom                                   |
| NULL | dany,Green,Jane,Lily,Susan,Liming,Henry,Jim,John,Thomas,Tom |
+------+-------------------------------------------------------------+
3 rows in set (0.00 sec)

子查询

子查询指将一个查询语句嵌套在另一个查询语句中

语法：WHERE <表达式> <操作符> (子查询)
其中，操作符可以是比较运算符和 IN、NOT IN、EXISTS、NOT EXISTS 等关键字。

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

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

例 1
使用子查询在 tb_students_info 表和 tb_course 表中查询学习 Java 课程的学生姓名，SQL 语句和运行结果如下:

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

结果显示，学习 Java 课程的只有 Dany 和 Henry。上述查询过程也可以分为以下 2 步执行，实现效果是相同的。

mysql数据库备份与恢复

数据库常用备份方案

数据库备份方案：

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

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
 
 
//备份整个数据库(全备)
[root@z1 ~]# mysqldump --all-databases > all--$(date '+%Y%d%m%H%M%S').sql
//备份zdz库的tb_course表
[root@z1 ~]# mysqldump zdz tb_course > tb_course-$(date '+%Y%d%m%H%M%S').sql
//备份zdz库
[root@z1 ~]# mysqldump --databases zdz > zdz-$(date '+%Y%d%m%H%M%S').sql

mysql数据恢复

//模拟误删zdz数据库
mysql> drop database zdz;
Query OK, 2 rows affected (0.01 sec)

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

//恢复zdz数据库
//第一种恢复方式
[root@z1 ~]# mysql < zdz-20223007033739.sql 
//第二种恢复方式   恢复文件必须绝对路径或者在/root下
mysql> source zdz-20223007033739.sql

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zdz                |
+--------------------+



//误删表tb_course的数据
mysql> delete from tb_course where id = 5 or id = 6;
Query OK, 2 rows affected (0.00 sec)

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

//恢复表tb_course的数据
//第一种恢复方式
[root@z1 ~]# mysql zdz < tb_course-20223007033431.sql
或者
//第二种恢复方式   恢复文件必须绝对路径或者在/root下
mysql> source tb_course-20223007033431.sql    

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


//模拟删除整个数据库
mysql> drop database zdz;
Query OK, 2 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

//恢复整个数据库
//第一种恢复方式
[root@z1 ~]# mysql < all--20223007033914.sql 
//第二种恢复方式   恢复文件必须绝对路径或者在/root下
mysql> source all--20223007033914.sql 

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

差异备份与恢复

备份

开启MySQL服务器的二进制日志功能

[root@z1 ~]# vim /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

server-id=10         //添加这二行
log-bin=mysql_bin

对数据库进行完全备份

//完全备份
[root@z1 ~]# mysqldump --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs  > all-chayi.sql
[root@z1 ~]# ls
all--20223007033914.sql  tb_course-20223007033431.sql
all-chayi.sql            zdz-20223007033739.sql

//增加新内容
mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | Mysql       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | HTML        |
+----+-------------+
mysql> insert tb_course(course_name) values('zz'),('zd');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> delete from tb_course where id = 4;
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | Mysql       |
|  3 | Python      |
|  5 | C++         |
|  6 | HTML        |
|  7 | zz          |
|  8 | zd          |
+----+-------------+
7 rows in set (0.00 sec)

恢复

模拟误删数据

mysql> drop database zdz;
Query OK, 2 rows affected (0.02 sec)

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

刷新创建新的二进制日志

[root@z1 ~]# ll /opt/data/
total 123016
-rw-r-----. 1 mysql mysql       56 Jul 27 18:15 auto.cnf
-rw-------. 1 mysql mysql     1676 Jul 27 18:15 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 18:15 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 18:15 client-cert.pem
-rw-------. 1 mysql mysql     1676 Jul 27 18:15 client-key.pem
-rw-r-----. 1 mysql mysql      579 Jul 30 04:37 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 30 04:51 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 30 04:51 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 27 18:15 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 30 04:40 ibtmp1
drwxr-x---. 2 mysql mysql     4096 Jul 30 04:18 mysql
-rw-r-----. 1 mysql mysql      844 Jul 30 04:49 mysql_bin.000003
-rw-r-----. 1 mysql mysql       19 Jul 30 04:40 mysql_bin.index
-rw-r-----. 1 mysql mysql        5 Jul 30 04:37 mysql.pid
drwxr-x---. 2 mysql mysql     8192 Jul 27 18:15 performance_schema
-rw-------. 1 mysql mysql     1676 Jul 27 18:15 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jul 27 18:15 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 18:15 server-cert.pem
-rw-------. 1 mysql mysql     1676 Jul 27 18:15 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jul 27 18:15 sys
-rw-r-----. 1 mysql mysql    53900 Jul 30 04:37 z1.err

//刷新创建新的二进制日志
[root@z1 ~]# mysqladmin -uroot -p654321 flush-logs
[root@z1 ~]# ll /opt/data/
total 123020
-rw-r-----. 1 mysql mysql       56 Jul 27 18:15 auto.cnf
-rw-------. 1 mysql mysql     1676 Jul 27 18:15 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 18:15 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 18:15 client-cert.pem
-rw-------. 1 mysql mysql     1676 Jul 27 18:15 client-key.pem
-rw-r-----. 1 mysql mysql      579 Jul 30 04:37 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 30 04:51 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 30 04:51 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 27 18:15 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 30 04:40 ibtmp1
drwxr-x---. 2 mysql mysql     4096 Jul 30 04:18 mysql
-rw-r-----. 1 mysql mysql      891 Jul 30 04:54 mysql_bin.000003
-rw-r-----. 1 mysql mysql      154 Jul 30 04:54 mysql_bin.000004
-rw-r-----. 1 mysql mysql       38 Jul 30 04:54 mysql_bin.index
-rw-r-----. 1 mysql mysql        5 Jul 30 04:37 mysql.pid
drwxr-x---. 2 mysql mysql     8192 Jul 27 18:15 performance_schema
-rw-------. 1 mysql mysql     1676 Jul 27 18:15 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jul 27 18:15 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 18:15 server-cert.pem
-rw-------. 1 mysql mysql     1676 Jul 27 18:15 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jul 27 18:15 sys
-rw-r-----. 1 mysql mysql    53900 Jul 30 04:37 z1.err

恢复完全备份

[root@z1 ~]# mysql < all-chayi.sql 

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

恢复差异备份

[root@z1 ~]# ll /opt/data/
total 124040
-rw-r-----. 1 mysql mysql       56 Jul 27 18:15 auto.cnf
-rw-------. 1 mysql mysql     1676 Jul 27 18:15 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 18:15 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 18:15 client-cert.pem
-rw-------. 1 mysql mysql     1676 Jul 27 18:15 client-key.pem
-rw-r-----. 1 mysql mysql      579 Jul 30 04:37 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 30 05:01 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 30 05:01 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 27 18:15 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 30 04:40 ibtmp1
drwxr-x---. 2 mysql mysql     4096 Jul 30 04:59 mysql
-rw-r-----. 1 mysql mysql      891 Jul 30 04:54 mysql_bin.000003
-rw-r-----. 1 mysql mysql   857925 Jul 30 04:59 mysql_bin.000004
-rw-r-----. 1 mysql mysql       38 Jul 30 04:54 mysql_bin.index
-rw-r-----. 1 mysql mysql        5 Jul 30 04:37 mysql.pid
drwxr-x---. 2 mysql mysql     8192 Jul 27 18:15 performance_schema
-rw-------. 1 mysql mysql     1676 Jul 27 18:15 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jul 27 18:15 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 18:15 server-cert.pem
-rw-------. 1 mysql mysql     1676 Jul 27 18:15 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jul 27 18:15 sys
-rw-r-----. 1 mysql mysql    53900 Jul 30 04:37 z1.err
drwxr-x---. 2 mysql mysql      118 Jul 30 04:59 zdz

//检查误删数据库的位置在什么地方
mysql> show binlog events in 'mysql_bin.000003';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000003 |   4 | Format_desc    |        10 |         123 | Server ver: 5.7.38-log, Binlog ver: 4 |
| mysql_bin.000003 | 123 | Previous_gtids |        10 |         154 |                                       |
| mysql_bin.000003 | 154 | Anonymous_Gtid |        10 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 219 | Query          |        10 |         290 | BEGIN                                 |
| mysql_bin.000003 | 290 | Table_map      |        10 |         344 | table_id: 140 (zdz.tb_course)         |
| mysql_bin.000003 | 344 | Write_rows     |        10 |         395 | table_id: 140 flags: STMT_END_F       |
| mysql_bin.000003 | 395 | Xid            |        10 |         426 | COMMIT /* xid=480 */                  |
| mysql_bin.000003 | 426 | Anonymous_Gtid |        10 |         491 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 491 | Query          |        10 |         562 | BEGIN                                 |
| mysql_bin.000003 | 562 | Table_map      |        10 |         616 | table_id: 140 (zdz.tb_course)         |
| mysql_bin.000003 | 616 | Delete_rows    |        10 |         659 | table_id: 140 flags: STMT_END_F       |
| mysql_bin.000003 | 659 | Xid            |        10 |         690 | COMMIT /* xid=482 */                  |
| mysql_bin.000003 | 690 | Anonymous_Gtid |        10 |         755 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 755 | Query          |        10 |         844 | drop database zdz                     |
| mysql_bin.000003 | 844 | Rotate         |        10 |         891 | mysql_bin.000004;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)


//使用mysqlbinlog恢复差异备份
[root@z1 ~]# mysqlbinlog --stop-position=755 /opt/data/mysql_bin.000003 | mysql -uroot -p654321

mysql> select * from tb_course;      
+----+-------------+ 
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | Mysql       |
|  3 | Python      |
|  5 | C++         |
|  6 | HTML        |
|  7 | zz          |
|  8 | zd          |
+----+-------------+
7 rows in set (0.00 sec)   //确定恢复到增加之后的数据