数据库的备份恢复和SQL语句

1. 数据库的备份与恢复

1.1 数据库常用备份方案

数据库备份方案：

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

备份方案	特点
全量备份	全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。 数据恢复快。 备份时间长
增量备份	增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份 与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象 是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量 备份后所产生的增加和修改的文件，如此类推。 没有重复的备份数据 备份时间短 恢复数据时必须按一定的顺序进行
差异备份	备份上一次的完全备份后发生变化的所有文件。 差异备份是指在一次全备份后到进行差异备份的这段时间内 对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。

1.2 mysql备份工具mysqldump

语法：
mysqldump [OPTIONS] database [tables …]
mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3…]

常用OPTIONS:
-uUSERNAME //指定数据库用户名
-hHOST //指定服务器主机IP
-pPASSWORD //指定数据库用户的密码
-P //指定数据库监听的端口号

1.3 全量备份

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| luochuran          |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.002 sec)

MariaDB [(none)]> use luochuran
Database changed
MariaDB [luochuran]> show tables;
+---------------------+
| Tables_in_luochuran |
+---------------------+
| student             |
+---------------------+
1 row in set (0.000 sec)
[root@localhost ~]# mysqldump -uroot -p123 --all-databases > all-202108262132.sql
[root@localhost ~]# ls
all-202108262132.sql  anaconda-ks.cfg
[root@localhost ~]#

全量备份数据恢复

[root@localhost ~]# mysql -uroot -p < all-202108262132.sql 
Enter password: 
[root@localhost ~]# mysql -uroot -p -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| luochuran          |
| mysql              |
| performance_schema |
+--------------------+
[root@localhost ~]#

1.4 差异备份

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

[root@localhost ~]# cat /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

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

[root@localhost ~]# service mysqld restart  #重启数据库
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

对数据库进行差异备份

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

[root@localhost ~]# mysqldump -uroot -p123 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > cyall-$(date '+%Y%m%d').sql 
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll  
总用量 2580
-rw-r--r--. 1 root root      0 8月  26 04:23 -all-20210826.sql
-rw-r--r--. 1 root root 873495 8月  26 04:26 all-20210826.sql
-rw-------. 1 root root   1023 7月  16 07:36 anaconda-ks.cfg
-rw-r--r--. 1 root root 873643 8月  26 04:58 cyall-20210826.sql

mysql> create table luo;  
ERROR 1113 (42000): A table must have at least 1 column
mysql> create table luo(name varchar(45),id int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into luo values('ded',13),('dwqd',78);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from luo;
+------+------+
| name | id   |
+------+------+
| ded  |   13 |
| dwqd |   78 |
+------+------+
2 rows in set (0.00 sec)

mysql> drop database luochuran;  
Query OK, 2 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

[root@localhost ~]# ll /opt/data/
总用量 123012
-rw-r-----. 1 mysql mysql       56 8月  25 02:34 auto.cnf
-rw-r-----. 1 mysql mysql    50126 8月  26 04:52 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 8月  26 04:30 mysql
-rw-r-----. 1 mysql mysql      788 8月  26 05:06 mysql_bin.000005
·····
[root@localhost ~]# mysqladmin -uroot -p123 flush-logs  
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll /opt/data/
总用量 123016
-rw-r-----. 1 mysql mysql       56 8月  25 02:34 auto.cnf
drwxr-x---. 2 mysql mysql     4096 8月  26 04:30 mysql
-rw-r-----. 1 mysql mysql      835 8月  26 05:08 mysql_bin.000005
-rw-r-----. 1 mysql mysql      154 8月  26 05:08 mysql_bin.000006

[root@localhost ~]# mysql -uroot -p123 < cyall-20210826.sql  
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p123
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| luochuran           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
mysql> use luochuran;
Database changed
mysql> show tables;     
+--------------------+
| Tables_in_liuqiang |
+--------------------+
| student            |
+--------------------+
1 row in set (0.00 sec)

恢复差异备份

[root@localhost ~]# mysql -uroot -p123
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 10
Server version: 5.7.34-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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> show binlog events in 'mysql_bin.000005';  #检查误删数据库的位置在什么地方
+------------------+-----+----------------+-----------+-------------+----------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                     |
+------------------+-----+----------------+-----------+-------------+----------------------------------------------------------+
| mysql_bin.000005 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.34-log, Binlog ver: 4                    |
| mysql_bin.000005 | 123 | Previous_gtids |         1 |         154 |                                                          |
| mysql_bin.000005 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                     |
| mysql_bin.000005 | 219 | Query          |         1 |         341 | use `liuqiang`; create table lq(name varchar(45),id int) |
| mysql_bin.000005 | 341 | Anonymous_Gtid |         1 |         406 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                     |
| mysql_bin.000005 | 406 | Query          |         1 |         482 | BEGIN                                                    |
| mysql_bin.000005 | 482 | Table_map      |         1 |         534 | table_id: 174 (liuqiang.lq)                              |
| mysql_bin.000005 | 534 | Write_rows     |         1 |         588 | table_id: 174 flags: STMT_END_F                          |
| mysql_bin.000005 | 588 | Xid            |         1 |         619 | COMMIT /* xid=931 */                                     |
| mysql_bin.000005 | 619 | Anonymous_Gtid |         1 |         684 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                     |
| mysql_bin.000005 | 684 | Query          |         1 |         788 | drop database liuqiang                                   |
| mysql_bin.000005 | 788 | Rotate         |         1 |         835 | mysql_bin.000006;pos=4                                   |
+------------------+-----+----------------+-----------+-------------+----------------------------------------------------------+
12 rows in set (0.00 sec)

[root@localhost ~]# mysqlbinlog --stop-position=648 /opt/data/mysql_bin.000005 | mysql -uroot -p123   
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p123 
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 12
Server version: 5.7.34-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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> use liuqiang;
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_liuqiang |
+--------------------+
| luo                |
| student            |
+--------------------+
2 rows in set (0.00 sec)

2. group by使用

group by 一般和聚合函数一起使用才有意义,比如 count sum avg等,使用group by的两个要素:
(1) 出现在select后面的字段 要么是是聚合函数中的,要么就是group by 中的.
(2) 要筛选结果 可以先使用where 再用group by 或者先用group by 再用having

MariaDB [luochuran]> select * from student;   //表的内容
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangsan    |   26 |
|  6 | zhangsan    |   20 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.001 sec)


MariaDB [luochuran]> select avg(age) from student;  //对表进去求平均值
+----------+
| avg(age) |
+----------+
|  21.8182 |
+----------+
1 row in set (0.000 sec)


MariaDB [luochuran]> select sum(age) from student;  //也可以进行求和
+----------+
| sum(age) |
+----------+
|      240 |
+----------+
1 row in set (0.000 sec)


MariaDB [luochuran]> select count(age) from student;  //也可以进行计数
+------------+
| count(age) |
+------------+
|         11 |
+------------+
1 row in set (0.000 sec)

除了上面常用函数意外还有以下函数

MariaDB [luochuran]> select max(age) from student;  //最大值
+----------+
| max(age) |
+----------+
|       50 |
+----------+
1 row in set (0.000 sec)

MariaDB [luochuran]> 

MariaDB [luochuran]> select min(age) from student; //最小值
+----------+
| min(age) |
+----------+
|        3 |
+----------+
1 row in set (0.000 sec)

3. inner join

语法

INNER JOIN 连接两个数据表的用法：
SELECT * FROM 表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号

INNER JOIN 连接三个数据表的用法：
SELECT * FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号

INNER JOIN 连接四个数据表的用法：
SELECT * FROM ((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号

INNER JOIN 连接五个数据表的用法：
SELECT * FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表5 ON Member.字段号=表5.字段号

4. inner join、left join和right join

内连接只返回满足连接条件的数据行，外连接不只列出与连接条件相匹配的行，而是列出左表（左外连接时）、右表（右外连接时）或两个表（全外连接时）中所有符合搜索条件的数据行。

外连接分为左外连接、右外链接、全外连接三种。

1）LEFT JOIN或LEFT OUTER JOIN
左向外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
2）RIGHT JOIN 或 RIGHT OUTER JOIN
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。

查看库中两张表的内容

MariaDB [luochuran]> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | luo  |   12 |
|  2 | chu  |   53 |
|  3 | ran  |   23 |
+----+------+------+
3 rows in set (0.000 sec)

MariaDB [luochuran]> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangsan    |   26 |
|  6 | zhangsan    |   20 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.000 sec)

内连（inner join）

MariaDB [luochuran]> select student1.*,student.* from student1 inner join student on student1.age=student.age;
+----+------+------+----+-------+------+
| id | name | age  | id | name  | age  |
+----+------+------+----+-------+------+
|  3 | ran  |   23 |  2 | jerry |   23 |
+----+------+------+----+-------+------+
1 row in set (0.000 sec)

左连（left join）

MariaDB [luochuran]> select student1.*,student.* from student1 left join student on student1.age=student.age;
+----+------+------+------+-------+------+
| id | name | age  | id   | name  | age  |
+----+------+------+------+-------+------+
|  3 | ran  |   23 |    2 | jerry |   23 |
|  1 | luo  |   12 | NULL | NULL  | NULL |
|  2 | chu  |   53 | NULL | NULL  | NULL |
+----+------+------+------+-------+------+
3 rows in set (0.001 sec)

右连（right join）

MariaDB [luochuran]> select student1.*,student.* from student1 right join student on student1.age=student.age;
+------+------+------+----+-------------+------+
| id   | name | age  | id | name        | age  |
+------+------+------+----+-------------+------+
|    3 | fei  |   23 |  2 | jerry       |   23 |
| NULL | NULL | NULL |  1 | tom         |   20 |
| NULL | NULL | NULL |  3 | wangqing    |   25 |
| NULL | NULL | NULL |  4 | sean        |   28 |
| NULL | NULL | NULL |  5 | zhangsan    |   26 |
| NULL | NULL | NULL |  6 | zhangsan    |   20 |
| NULL | NULL | NULL |  7 | lisi        |   50 |
| NULL | NULL | NULL |  8 | chenshuo    |   10 |
| NULL | NULL | NULL |  9 | wangwu      |    3 |
| NULL | NULL | NULL | 10 | qiuyi       |   15 |
| NULL | NULL | NULL | 11 | qiuxiaotian |   20 |
+------+------+------+----+-------------+------+
11 rows in set (0.000 sec)