MySQL数据备份与SQL语句

MySQL数据备份与SQL语句

1.mysql数据库备份与恢复

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#             //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
 

全量备份

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;  //查看库，然后进入库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| shaoyaobang            |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [(none)]> use shaoyaobang;
//库中表里面有下面这些内容
MariaDB [shaoyaobang]> 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)

[root@localhost ~]# mkdir mysqldump
[root@localhost ~]# cd mysqldump/
[root@localhost mysqldump]# ls
[root@localhost mysqldump]# mysqldump -u root -p1 --all-databases > all-20210826.sql   //全量备份完成
[root@localhost mysqldump]# ls
all-20210826.sql
//上面已经全量备份，删除库中的shaoyaobang
MariaDB [(none)]> show databases;  
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| shaoyaobang            |
+--------------------+
4 rows in set (0.000 sec)
//删除成功
MariaDB [(none)]> drop database shaoyaobang;   
Query OK, 1 row affected (0.004 sec)

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

还原全量备份

[root@localhost mysqldump]# mysql -u root -p1 < all-20210826.sql 
[root@localhost mysqldump]# 


MariaDB [(none)]> show databases;  //登录数据库已经还原
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| shaoyaobang            |
+--------------------+
4 rows in set (0.000 sec)
//进入库中查看内容还原成功
MariaDB [(none)]> use shaoyaobang;
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

MariaDB [student]> 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)

2. 差异备份与恢复

mysql差异备份

开启MySQL服务器的二进制日志功能
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
user = mysql
pid-file = /tmp/mysql.pid
skip-name-resolve

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

[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!

对数据库进行完全备份

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| shaoyaobang                |
+--------------------+
4 rows in set (0.00 sec
MariaDB [(none)]> show tables from shaoyaobang;
+--------------------+
| Tables_in_shaoyaobang |
+-----------------------+
| student               |
+-----------------------+
1 rows in set (0.01 sec)
MariaDB [(none)]> select * from shaoyaobang.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)
//完全备份
[root@localhost ~]# mysqldump -uroot -syb123 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-22010826.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure
[root@localhost ~]# ls
all-22010826.sql   all-20210826.sql
模拟误删数据
MariaDB [(none)]> drop database shaoyaobang;
Query OK, 1 row affected (0.004 sec
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

mysql差异备份恢复

//刷新创建新的二进制日志
[root@localhost ~]# mysqladmin -uroot -psyb123 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll /opt/data/
总用量 188552
-rw-r-----. 1 mysql mysql       56 8月  20 10:11 auto.cnf
-rw-r-----. 1 mysql mysql      996 8月  21 14:54 ib_buffer_pool
-rw-r-----. 1 mysql mysql 79531776 8月  21 16:17 ibdata1
-rw-r-----. 1 mysql mysql 30331268 8月  21 16:18 ib_logfile0
-rw-r-----. 1 mysql mysql 803389648 8月 21  10:11 ib_logfile1
-rw-r-----. 1 mysql mysql 24582512 8月  21 14:54 ibtmp1
-rw-r-----. 1 mysql mysql     8234 8月  21 08:45 linux-node1.com.err
-rw-r-----. 1 mysql mysql    75650 8月  21 14:54 localhost.err
drwxr-x---. 2 mysql mysql     4086 8月  21 16:16 mysql
-rw-r-----. 1 mysql mysql      915 8月  21 16:18 mysql_bin.000001
-rw-r-----. 1 mysql mysql      256 8月  21 16:18 mysql_bin.000002
-rw-r-----. 1 mysql mysql       45 8月  21 16:18 mysql_bin.index
drwxr-x---. 2 mysql mysql     789 8月  21 10:11 performance_schema

恢复完全备份

[root@localhost ~]# mysql -uroot -psyb123 < all-20210826.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| shaoyaobang        |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [(none)]> use shaoyaobang;
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
MariaDB [shaoyaobang]> show tables;
+-----------------------+
| Tables_in_shaoyaobang |
+-----------------------+
| student               |
+-----------------------+
1 row in set (0.000 sec)
MariaDB [(shaoyaobang)]> 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)

3. group by

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

//查看表的内容
MariaDB [shaoyaobang]> 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 [shaoyaobang]> select avg(age) from student;  
+----------+
| avg(age) |
+----------+
|  21.8182 |
+----------+
1 row in set (0.000 sec)
//也可以进行求和
MariaDB [shaoyaobang]> select sum(age) from student;  
+----------+
| sum(age) |
+----------+
|      240 |
+----------+
1 row in set (0.000 sec)
 //也可以进行计数
MariaDB [shaoyaobang]> select count(age) from student; 
+------------+
| count(age) |
+------------+
|         11 |
+------------+
1 row in set (0.000 sec)

4. 内连接 INNER JOIN

内连接（INNER JOIN）有两种，显式的和隐式的，返回连接表中符合连接条件和查询条件的数据行。（所谓的链接表就是数据库在做查询形成的中间表）。

内连接使用比较运算符（包括=、>、<、<>、>=、<=、!> 和!<）进行表间的比较操作，查询与连接条件相匹配的数据。根据所使用的比较方式不同，内连接分为等值连接、自然连接和自连接三种。

语句1：隐式的内连接，没有INNER JOIN，形成的中间表为两个表的笛卡尔积。
SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAME
FROM CUSTOMERS C,ORDERS O
WHERE C.ID=O.CUSTOMER_ID;

语句2：显示的内连接，一般称为内连接，有INNER JOIN，形成的中间表为两个表经过ON条件过滤后的笛卡尔积。
SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAME
FROM CUSTOMERS C INNER JOIN ORDERS O ON C.ID=O.CUSTOMER_ID;
语句3和语句4的查询结果。

5. inner join、left join和right join

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

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

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

MariaDB [shaoyaobang]> select * from student1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | shao |   10 |
|  2 | yao  |   20 |
|  3 | bang |   30 |
+----+------+------+
3 rows in set (0.000 sec)
MariaDB [shaoyaobang]> 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)

内连（inner join）

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

左连（left join）

MariaDB [shaoyaobang]> select student1.*,student.* from student1 left join student on student1.age=student.age;
+----+------+------+------+-------+------+
| id | name | age  | id   | name  | age  |
+----+------+------+------+-------+------+
|  3 | bang |  30  |   2  | jerry | 23   | 
|  1 | shao |  10  | NULL | NULL  | NULL |
|  2 | yao  |  20  | NULL | NULL  | NULL |
+----+------+------+------+-------+------+
3 rows in set (0.001 sec)

右连（right join）

MariaDB [shaoyaobang]> select student1.*,student.* from student1 right join student on shenlongfei1.age=student.age;
+------+------+------+----+-------------+------+
| id   | name | age  | id | name        | age  |
+------+------+------+----+-------------+------+
|    3 | bang |   30 |  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)